58. 脚本：mysql分库分表备份

6. mysql数据库分库分表备份

思路分析

mysqldump oldboy test test1 |gzip >bak.sql.gz
1.oldboy 数据库名
2.test、test1 表名

方法2

mysqldump -B oldboy |gzip >bak.sql.gz
mysqldump oldboy test1
mysqldump oldboy test2
mysqldump oldboy test3

脚本

#!/bin/bash
path=/backup
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
    for tname in `$mysql -e "show tables from $dbname;" 2>/dev/nulll |sed 1d`
    do
        $mysqldump $dbname $tname |gzip >$path/${dbname}_${tname}.sql.gz 2>/dev/null
    done
done

也可以修改配置文件，加入如下参数，防止在脚本中加入密码

vim /etc/my.cnf

[client]
user=root
password=oldboy123

则，脚本中可以省略密码

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

for dbname in `mysql -e "show databases;" 2>/dev/null |grep -v _schema|sed 1d`
do
    for tname in `mysql -e "show tables from $dbname;" 2>/dev/nulll |sed 1d`
    do
        mysqldump $dbname $tname |gzip >$path/${dbname}_${tname}.sql.gz 2>/dev/null
    done
done

改造，跳过报错数据库

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

for dbname in `mysql -e "show databases;" 2>/dev/null |grep -v _schema|sed 1d`
do      
    for tname in `mysql -e "show tables from $dbname;" 2>/dev/nulll |sed 1d`
    do
        if [ "$dbname" = "mysql" ];then
            mysqldump --skip-lock-tables $dbname $tname |gzip >$path/${dbname}_${tname}.sql.gz 2>/dev/null
        else
            mysqldump $dbname $tname |gzip >$path/${dbname}_${tname}.sql.gz 2>/dev/null
        fi
    done
done

---

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