此脚本主要是使用两个for循环把每个库的每个表列出来,然后使用mysqldump进行备份。
#!/bin/bash cat << dump *********************************************** mysql分库分表备份脚本 *********************************************** dump user=root host=127.0.0.1 passwd=123 socket=/var/lib/mysql/mysql.sock db_bak_dir=/mysql_bak dump_log=/tmp/mysql_dump.log [ -e /etc/init.d/functions ] && . /etc/init.d/functions || exit for dbname in `mysql -u$user -h$host -p$passwd -S $socket -e "show databases"|sed '1,2d'` do echo -e "\033[32m----------正在备份数据库 ${dbname}---------- \033[0m" for tablename in `mysql -u$user -p$passwd -S $socket -e "show tables from $dbname;"|sed '1d'` do mkdir -p $db_bak_dir/$(date +%F)/$dbname mysqldump -u$user -p$passwd --lock-all-tables ${dbname} ${tablename} | gzip > /$db_bak_dir/$(date +%F)/${dbname}/${dbname}_${tablename}_$(date +%F).sql.gz >> $dump_log [ $? -eq 0 ] && action " 备份 ${dbname}.${tablename} " /bin/true || action "备份 ${dbname}.${tablename} " /bin/false done echo -e "\033[32m----------数据库 ${dbname} 备份完成---------- \033[0m" done
执行结果如下所示: