1、分库备份:
#!/bin/sh
MYUSER=root
MYPASS=123456
SOCKET=/usr/local/mysql/data/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@server002 ~]#tree /server/backup/
/server/backup/
├── opuser26
│ ├──opuser26_2015-12-29.sql.gz
│ ├──opuser26_2015-12-30.sql.gz
│ └──opuser26_2015-12-31.sql.gz
├── opuser_users26
│ ├──opuser_users26_2015-12-29.sql.gz
│ ├──opuser_users26_2015-12-30.sql.gz
│ └──opuser_users26_2015-12-31.sql.gz
└── users26
├──users26_2015-12-29.sql.gz
├──users26_2015-12-30.sql.gz
└──
users26_2015-12-31.sql.gz
3 directories, 9 files
[root@server002 ~]#
2、分库分表备份:
#!/bin/sh
USER=root
PASSWD=123456
SOCKET=/usr/local/mysql/data/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
查看备份明细:
[root@server002 scripts]# tree /server/backup/
/server/backup/
├── opuser26
│ ├──opuser26_2015-12-30
│ │├──opuser26_opuser_test_2015-12-30.sql.gz
│ │└──opuser26_opuser_test2_2015-12-30.sql.gz
│ └──opuser26_2015-12-31
│ ├── opuser26_opuser_test_2015-12-31.sql.gz
│ └── opuser26_opuser_test2_2015-12-31.sql.gz
├── opuser_products26
│ ├──opuser_products26_2015-12-30
│ │├──opuser_products26_cloudstack_2015-12-30.sql.gz
│ │├──opuser_products26_dashborad_2015-12-30.sql.gz
│ │└── opuser_products26_student_2015-12-30.sql.gz
│ └──opuser_products26_2015-12-31
│ ├──opuser_products26_cloudstack_2015-12-31.sql.gz
│ ├──opuser_products26_dashborad_2015-12-31.sql.gz
│ └──opuser_products26_student_2015-12-31.sql.gz
└── products26
├──products26_2015-12-30
│├──products26_openstack_2015-12-30.sql.gz
│├──products26_saltstack_2015-12-30.sql.gz
│├──products26_server_2015-12-30.sql.gz
│└──products26_zabbix_2015-12-30.sql.gz
└──products26_2015-12-31
├──products26_openstack_2015-12-31.sql.gz
├──products26_saltstack_2015-12-31.sql.gz
├──products26_server_2015-12-31.sql.gz
└──products26_zabbix_2015-12-31.sql.gz
9 directories, 18 files
[root@server002 scripts]#
注意:备份中的警告信息
以上各脚本在备份的时候都会提示:
Warning: Using a password on the command line interface can be insecure.
Warning: Using a password on the command line interface can be insecure.
Warning: Using a password on the command line interface can be insecure.
Warning: Using a password on the command line interface can be insecure.
解决方法：
对于 mysqldump 要如何避免出现(Warning: Using a password on the command line interface can be insecure.) 警告信息呢?
我们可以编辑配置文件，添加如下内容:
[root@server002 ~]# vim /etc/my.cnf
[client]
user=root
password=123456
....省略部分...........
[mysqldump]
user=root
password=123456
修改完配置文件后,重启mysql服务，然后只需要再次脚本中执行mysql和mysqldump 命令就不会出现警告信息了，经过查阅发现MySQL5.5（包括5.5）以前直接在备份脚本中命令行写密码是不会出现的，到5.6以后就出现了警告信息。所以平时的备份脚本中不需要涉及用户名密码相关信息.