用shell实现MySQL分库分表操作

#!/bin/bash
mysql_cmd='-uroot -p123'   #定义变量保存密码
exclude_db='information_schema|performance_schema|sys' #数据库
bak_path=/backup/db #备份路径
mysql ${mysql_cmd} -e 'show databases' -N | egrep -v "${exclude_db}" > dbname
while read line
do
        [ -d ${bak_path}/$line ] || mkdir -p ${bak_path}/$line
        mysqldump ${mysql_cmd} --set-gtid-purged=OFF -B $line | gzip > ${bak_path}/${line}/${line}_$(date +%F).sql.gz

        mysql -uroot -p123 -N -e "show tables from $line" > tbname  
        while read tb
        do
                mysqldump ${mysql_cmd} --set-gtid-purged=OFF $line $tb | gzip > ${bak_path}/$line/${line}_${tb}_$(date +%F).sql.gz
        done < tbname
done < dbname

rm -f dbname tbname