MySQL sub-library sub-table backup script
Backup database script
[root@*** script] # cat store_backup.sh
#! / bin/sh
MYUSER=root
MYPASS=qwe123
SOCKET=/data/3306/mysql.sock
MYLOGIN= "mysql-u$MYUSER-p$MYPASS-S $SOCKET"
MYDUMP= "mysqldump-u$MYUSER-p$MYPASS-S$SOCKET-B"
DATABASE= "$($MYLOGIN-e" show databases; "| egrep-vi" Data | _ schema | mysql ")"
For dbname in $DATABASE
Do
MYDIR=/server/backup/$dbname
[!-d $MYDIR] & & mkdir-p $MYDIR
$MYDUMP $dbname | gzip > $MYDIR/$ {dbname} _ $(date +% F) .sql.gz
Done
[root@*** script] # sh store_backup.sh
[root@*** script] # tree / server/backup/
/ server/backup/
├── test
│ └── test_2017-06-21.sql.gz
└── wordpress
└── wordpress_2017-06-21.sql.gz
2 directories, 2 files
Mysql sub-library sub-table backup script
[root@*** script] # cat mysql_table.sh
#! / bin/sh
USER=root
PASSWD=qwe123
SOCKET=/data/3306/mysql.sock
MYLOGIN= "mysql-u$USER-p$PASSWD-S$SOCKET"
MYDUMP= "mysqldump-u$USER-p$PASSWD-S$SOCKET"
DATEBASE= "$($MYLOGIN-e" show databases; "| egrep-vi" Data | _ schema | mysql ")"
For dbname in $DATEBASE
Do
TABLE= "$($MYLOGIN-e" use $dbname;show tables; "| sed '1d')"
For tname in $TABLE
Do
MYDIR=/server/backup/$dbname/$ {dbname} _ $(date +% F)
[!-d $MYDIR] & & mkdir-p $MYDIR
$MYDUMP $dbname $tname | gzip > $MYDIR/$ {dbname} _ ${tname} _ $(date +% F) .sql.gz
Done
Done
Tips:
MySQL 5.6warning message command line interface can be insecure repair
Enter the password on the command line and you will be prompted with these security warnings.
Warning: Using a password on the command line interface can be insecure.
Note: either mysql-u root-pPASSWORD or mysqldump-u root-pPASSWORD will output such a warning.
1. For mysql
Change mysql-uroot-pPASSWORD to mysql-uroot-p by entering the password.
2. Mysqldump is more troublesome, which is usually written in scripts scripts.
Solution:
How to avoid the appearance of mysqldump (Warning: Using a password on the command line interface can be insecure.) Where's the warning message?
Vim / data/3306/my.cnf
[mysqldump]
User=your_backup_user_name
Password=your_backup_password
After you modify the configuration file, you only need to execute the mysqldump script. User name and password information is not required in the backup script.
