57. 脚本：mysql分库备份

5. mysql数据库分库备份

思路分析：

常规方法
mysqldump -B oldboy oldgirl test | gzip >bak.sql.gz

分库备份
mysqldump -B oldboy | gzip >bak.sql.gz
mysqldump -B oldgirl | gzip >bak.sql.gz
mysqldump -B test | gzip >bak.sql.gz

脚本

#!/bin/bash
[ -d /backup ] || mkdir /backup -p

for dname in `mysql -uroot -poldboy123 -e "show databases;" 2>/dev/null |grep -v _schema|sed 1d`
do
    mysqldump -uroot -poldboy123 -B $dbname|gzip >/backup/${dbname}.sql.gz
done

优化

#!/bin/bash
mysql="mysql -uroot -poldboy123"
mysqldump="mysqldump -uroot -poldboy123"
[ -d $path ] ||mkdir $path -p
for dbname in `$mysql -e "show databases;" 2>/dev/null|grep -v _schema|sed 1d`
do
    $mysqldump -B $dbname|gzip >$path/${dbname}_$(date +%F).sql.gz
done

---

最后更新: 2022-02-19 13:59:07