此脚本主要是使用两个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
执行结果如下所示:
DevOps-田飞雨 》》转载请注明源地址
喜欢 (3)or分享 (0)