mysqldump分库备份，分表备份，分库分表备份

分库备份

#!/bin/bash

#定义变量

mysql_cmd='-uroot -p123456'

exclude_db='information_schema|performance_schema|sys|mysql'

bak_path=/backup/db

[ -d ${bak_path} ] || mkdir -p ${bak_path}

#过滤备份的数据库，并过滤到指定目录

mysql ${mysql_cmd} -e 'show databases' -N | egrep -v ${exclude_db} > dbname

#循环读取

while read line

do

        mysqldump ${mysql_cmd} --set-gtid-purged=OFF -B $line | gzip > ${bak_path}/${line}_$(date +%F).sql.gz

done < dbname

#删除临时变量

rm -f dbname

 分表备份：

#!/bin/bash

mysql_cmd='-uroot -p123456'

exclude_db='information_schema|performance_schema|sys|mysql'

bak_path=/backup/db

mysql -uroot -p123456 -N -e "show tables from school" > tbname

while read tb

do

        [ -d ${bak_path}/school ] || mkdir -p ${bak_path}/school

        mysqldump ${mysql_cmd} --set-gtid-purged=OFF school $line | gzip > ${bak_path}/school/school_${line}_$(date +%F).sql.gz

done < tbname

rm -f tbname

 

 

 分库分表备份

[root@node1 ~]# vim fx.sh

#!/bin/bash

mysql_cmd='-uroot -p123456'

exclude_db='information_schema|performance_schema|sys|mysql'

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 -p123456 -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

~  

举例