MySQL备份之分库分表备份脚本

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以后就出现了警告信息。所以平时的备份脚本中不需要涉及用户名密码相关信息.

      本文转自027ryan  51CTO博客，原文链接：http://blog.51cto.com/ucode/1831100 ，如需转载请自行联系原作者