Linux环境下MySQL分库备份策略与自动化脚本

分库备份
就是执行一个备份语句，备份一个库。如果数据里有多个库，就是执行多条相同的语句备份数据。注意文件名不应相同，以.sql文件结尾。


/usr/local/mysql/bin/mysqldump -uroot -p'root' -B kettle|gzip > /home/dbback/db1.gz

/usr/local/mysql/bin/mysqldump -uroot -p'root' -B kettle|gzip > /home/dbback/db2.gz

如果数据库中有上百个库，可以执行下面的语句把数据打印出来

批量打印出需要备份的数据库

[root@dongjj-pc bin]# ./mysql -uroot -p'root' -e "show databases;"|grep -Evi "database|info|perfor" | sed  's#^#/usr/local/mysql/bin/mysqldump -uroot -p'root' -B #g'
mysql: [Warning] Using a password on the command line interface can be insecure.
/usr/local/mysql/bin/mysqldump -uroot -proot -B dongjj
/usr/local/mysql/bin/mysqldump -uroot -proot -B kettle
/usr/local/mysql/bin/mysqldump -uroot -proot -B mysql
/usr/local/mysql/bin/mysqldump -uroot -proot -B sys


在上述的备份添加备份数据文件的位置
[root@dongjj-pc bin]# ./mysql -uroot -p'root' -e "show databases;"|grep -Evi "database|info|perfor" | sed -r 's#^([a-z].*$)#/usr/local/mysql/bin/mysqldump -uroot -p'root' -B \1|gzip > /home/dbback/\1.sql.gz  #g' | bash
mysql: [Warning] Using a password on the command line interface can be insecure.
/usr/local/mysql/bin/mysqldump -uroot -proot -B dongjj|gzip > /home/dbback/dongjj.sql.gz  
/usr/local/mysql/bin/mysqldump -uroot -proot -B kettle|gzip > /home/dbback/kettle.sql.gz  
/usr/local/mysql/bin/mysqldump -uroot -proot -B mysql|gzip > /home/dbback/mysql.sql.gz  
/usr/local/mysql/bin/mysqldump -uroot -proot -B sys|gzip > /home/dbback/sys.sql.gz  

备份后的结果
-rw-r--r--. 1 root root 9.3M  7月  1 20:45 dongjj.sql.gz