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以後就出現了警告資訊。所以平時的備份指令碼中不需要涉及使用者名稱密碼相關資訊.