mysql创建备份表脚本_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