MySql的全量备份和增量备份及还原
一、工作场景
.MySql早上2点进行全量备份
.每隔2小时进行一次增量备份
二、设计思路
.利用全备份文件中记录CHANGE MASTER语句,binlog文件及其位置点信息,找出binlog文件中增量的部分。
.用mysqlbinlog命令上述的binlog文件导出为sql文件,并剔除其中drop语句
.通过全量备份和增量binlog文件的导出sql,可以恢复完成的数据
三、全量备份
.脚本
#!/bin/bash
# program :use mysqldump to Fully backup mysql data per day!
# path :全备份,路径:/data/mysql/backup/daily
BakDir=/data/mysql/backup/daily
cd
BakDirDumpFile=
Date.sql
GZDumpFile=
Date.sql.tgz/usr/local/mysql/bin/mysqldump−uroot−p123456–quick–all−databases–flush−logs–delete−master−logs–single−transaction>
DumpFile
#/bin/tar czvf
GZDumpFile
DumpFile
Last=date + “%Y-%m-%d %H:%M:%S”
logFile=/data/mysql/backup/daily/bak.log
echo 开始:
Begin结束:
Last
GZDumpFilesucc>>
logFile
四、增量备份
#!/bin/bash
BakDir=/data/mysql/backup/hour
BinDir=/usr/local/mysql/data
logFile=/data/mysql/backup/hour/bak.log
BinFile=/usr/local/mysql/data/mysql-bin.index
/usr/local/mysql/bin/mysqladmin -uroot -p123456 flush-logs
#这个是用于产生新的mysql-bin.*文件
Counter=wc -l BinFile | awk '{print $1}’
NextNum=0
#for循环用于对比Counter,
NextNum这两个值来确定文件是不是存在或最新的forfileincat
BinFile
do
base=basename $file
NextNum=expr $NextNum + 1
if [
NextNum−eq
Counter ]
then
echo
baseskip!>>
logFile
else
dest=
BakDir/
base
if(test -e
dest)thenecho
base exist! >>
logFileelsecp
BinDir/
base
BakDir
echo
basecopying>>
logFile
fi
fi
done
echo date +”%Y-%m-%d %H:%M:%S”
NextNumBakupsucc!>>
logFile
五、完全备份
# mysql -u root -p < full_bak.sql
六、增量备份
#mysqlbinlog –no-defaults /data/mysql/backup/hour/mysql-bin.000058 | mysql -uroot -p12345 -v finance
问题记录:
1)binlog 包unknown variable ‘default-character-set=utf8’
方法1:
在/etc/my.cnf 中将default-character-set=utf8修改成charater-set-server=utf8
方法2:
mysqlbinlog --no-defaults mysql-bin.00000