MySQL 备份脚本（单库/分库分表）
MySQL数据库分库备份
数据库密码保存在/etc/my.cnf文件中，所以在执行与mysql相关的命令不需要输入密码
分库备份思路：
mysqldump db1 >db1.sql.gz
mysqldump db2 >db2.sql.gz
拿到库名
mysql -e 'show databases' |sed '1d' |grep -v "_schema"
1.注释版
#!/bin/bash
# author: xiongminghao
# mail: jasonminghao@163.com
# describe：Mysql database backup
# 定义变量
IP=$(ifconfig eth1 |awk 'NR==2{print $2}')
Path=/backup/database # 备份目录路径
Time=$(date +%F)
Ba_Path=$Path/$Time
Ba_Logs=/var/log/db_database_$Time # 定义一个存放日志的文件
Md5_Path=$Path/$Time/md5 # 定义一个存放md5校验的目录
# 创建两个目录以当天时间为命名
[ ! -d $Ba_Path ] && mkdir $Ba_Path
[ ! -d $Md5_Path ] && mkdir $Md5_Path
# 循环获取数据库名进行备份和生成MD5校验文件
for dbname in `mysql -e 'show databases' |sed '1d' |grep -v "_schema"`
do
# 判断是否有数据库已经备份了,如果备份了则continue 调出当次循环，否则进行备份
if [ ! -f $Ba_Path/${dbname}.sql.gz ];then
# 备份参数
# -B 增加use[name]语句, -R 备份存储过程和函数数据
# --master=data2 记录备份时刻的binlog位置点，2表示将其位置点注释
# --single-ransaction 对innodb引擎进行热备（对数据进行快照）
# gzip 对备份的数据进行gzip压缩
mysqldump -B -R --master-data=2 --single-transaction |gzip >$Ba_Path/${dbname}.sql.gz
RETVAL=$?
# 通过返回值判断备份是否执行成功,把执行的成功与否纪录到日志中
[ $? -eq 0 ] && echo "${dbname} successful" >>$Ba_Logs || echo "${dbname} failed" >>$Ba_Logs
# 对数据库的数据完整性做MD5校验
md5sum $Ba_Path/${dbname}.sql.gz >$Md5_Path/${dbname}_md5.log
else
# 如果备份已存在则跳出当次循环,进行下次循环
continue
fi
done
# 将备份日志结果邮件方式发送给管理员
mail -s "$HOSTNAME $IP" jasonminghao@163.com < $Ba_Logs
2.非注释版
#!/bin/bash
IP=$(ifconfig eth1 |awk 'NR==2{print $2}')
Path=/backup/database
Time=$(date +%F)
Ba_Path=$Path/$Time
Ba_Logs=/var/log/db_database_$Time
Md5_Path=$Path/$Time/md5
[ ! -d $Ba_Path ] && mkdir $Ba_Path
[ ! -d $Md5_Path ] && mkdir $Md5_Path
for dbname in `mysql -e 'show databases' |sed '1d' |grep -v "_schema"`
do
if [ ! -f $Ba_Path/${dbname}.sql.gz ];then
mysqldump -B -R --master-data=2 --single-transaction |gzip >$Ba_Path/${dbname}.sql.gz
RETVAL=$?
[ $? -eq 0 ] && echo "${dbname} successful" >>$Ba_Logs || echo "${dbname} failed" >>$Ba_Logs
md5sum $Ba_Path/${dbname}.sql.gz >$Md5_Path/${dbname}_md5.log
else
continue
fi
done
mail -s "$HOSTNAME $IP" jasonminghao@163.com < $Ba_Logs
MySQL数据库分库分表备份
数据库密码保存在/etc/my.cnf文件中，所以在执行与mysql相关的命令不需要输入密码
分库分表备份思路：
#库名db1，两张表
mysqldump db1 table01 >db1_table01.sql.gz
mysqldump db1 table02 >db1_table01.sql.gz
#库名db2，两张表
mysqldump db2 table01 >db2_table01.sql.gz
mysqldump db2 table02 >db2_table02.sql.gz
拿到表名列表
mysql -e "show tables from db1;" |sed 1d
1.注释版
#!/bin/bash
# author: xiongminghao
# mail: jasonminghao@163.com
# describe：Mysql database table backup
# 定义变量
IP=$(ifconfig eth1 |awk 'NR==2{print $2}')
Path=/backup/table # 备份目录
Time=$(date +%F)
Ba_Path=$Path/$Time
Ba_Logs=/var/log/db_table_$Time # 定义一个存放日志的文件
Md5_Path=$Path/$Time/md5 # 定义一个存放md5校验的目录
# 创建两个目录以当天时间为命名
[ ! -d $Ba_Path ] && mkdir $Ba_Path
[ ! -d $Md5_Path ] && mkdir $Md5_Path
# 循环获取数据库名
for dbname in `mysql -e 'show databases' |sed '1d' |grep -v "_schema"`
do
# 循环获取到数据库名后再次循环获取到该库名下的表，进行备份和MD5校验
for tablename in `mysql -e "show tables from $dbname" |sed '1d'`
do
# 判断是否有表已经备份了,如果备份了则continue 调出当次循环，否则进行备份
if [ ! -f $Ba_Path/${dbname}_${tablename}.sql.gz ];then
mysqldump -R --master-data=2 --single-transaction ${dbname} ${tablename} |gzip >$Ba_Path/${dbname}_${tablename}.sql.gz
RETVAL=$?
# 通过返回值判断备份是否执行成功,把执行的成功与否纪录到日志中
[ $? -eq 0 ] && echo "${dbname}_${tablename} successful" >>$Ba_Logs || echo "${dbname}_${tablename} failed" >>$Ba_Logs
# 对表的数据完整性做MD5校验
md5sum $Ba_Path/${dbname}_${tablename}.sql.gz >$Md5_Path/${dbname}_${tablename}_md5.log
else
continue
fi
done
done
# 将备份日志结果邮件方式发送给管理员
mail -s "$HOSTNAME $IP" jasonminghao@163.com < $Ba_Logs
2.非注释版
IP=$(ifconfig eth1 |awk 'NR==2{print $2}')
Time=$(date +%F)
Ba_Path=$Path/$Time
[ ! -d $Ba_Path ] && mkdir $Ba_Path
[ ! -d $Md5_Path ] && mkdir $Md5_Path
for dbname in `mysql -e 'show databases' |sed '1d' |grep -v "_schema"`
do
for tablename in `mysql -e "show tables from $dbname" |sed '1d'`
do
if [ ! -f $Ba_Path/${dbname}_${tablename}.sql.gz ];then
mysqldump -R --master-data=2 --single-transaction ${dbname} ${tablename} |gzip >$Ba_Path/${dbname}_${tablename}.sql.gz
RETVAL=$?
[ $? -eq 0 ] && echo "${dbname}_${tablename} successful" >>$Ba_Logs || echo "${dbname}_${tablename} failed" >>$Ba_Logs
md5sum $Ba_Path/${dbname}_${tablename}.sql.gz >$Md5_Path/${dbname}_${tablename}_md5.log
else
continue
fi
done
done
mail -s "$HOSTNAME $IP" jasonminghao@163.com < $Ba_Logs
