运维高级作业03 分库分表

mysql_cmd='-uroot -p123456'
exclude_db='information_schema|performance_schema|sys|mysql'
bak_path=/backup/db
#将非系统数据库读出并放到零时文件dbname中
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} -B $line | gzip > ${bak_path}/${line}/${line}_$(date +%F).sql.qz
                #读出数据库中的表
                mysql ${mysql_cmd} -N -e "show tables from $line" > tbname
                #内层循环读出数据库中的表
                while read tb
                        do
                        #用mysqldump将读出的表按格式压缩
                                mysqldump ${mysql_cmd} $line $tb | gzip > ${bak_path}/${line}_${tb}_$(date +%F).sql.gz
                        done < tbname
        done < dbname
rm -rf dbname tbname