MYSQL增量备份和全量备份脚本分享 布满荆棘的人生 2022-12-29 12:39 302阅读 0赞 mysql的全量备份与增量备份 **全量备份**:可以使用mysqldump直接备份整个库或者是备份其中某一个库或者一个库中的某个表。 **增量备份**:增量备份是针对于数据库的bin-log日志进行备份的,需要开始数据库的bin-log日志。增量备份是在全量的基础上进行操作的。增量备份主要是靠mysql记录的bin-log日志。(可以把二进制日志保存成每天的一个文件)。 **生产环境:** 建议一周或者三天进行一次全量备份,一天一次增量备份。 首先配置下mysql增量备份: server-id = 1 log\_bin = /var/log/mysql/mysql-bin.log binlog-ignore-db = sys, mysql, information\_schema, performance\_schema \#设置日志格式 binlog\_format = mixed \#设置日志路径,注意路经需要mysql用户有权限写 log-bin = /data/mysql/logs/mysql-bin.log \#设置binlog清理时间 expire\_logs\_days = 7 \#binlog每个日志文件大小 max\_binlog\_size = 100m \#binlog缓存大小 binlog\_cache\_size = 4m \#最大binlog缓存大小 max\_binlog\_cache\_size = 512m innodb\_buffer\_pool\_size=64m innodb\_thread\_concurrency = 8 1.MySQLdump增量备份 假定星期日下午1点执行全量备份,适用于MyISAM存储引擎。 \[root@test-huanqiu ~\]\# MySQLdump --lock-all-tables --flush-logs --master-data=2 -u root -p test > backup\_sunday\_1\_PM.sql 对于InnoDB将--lock-all-tables替换为--single-transaction \--flush-logs为结束当前日志,生成新日志文件; \--master-data=2 选项将会在输出SQL中记录下完全备份后新日志文件的名称, 用于日后恢复时参考,例如输出的备份SQL文件中含有: CHANGE MASTER TO MASTER\_LOG\_FILE=’MySQL-bin.000002′, MASTER\_LOG\_POS=106; 2.MySQLdump增量备份其他说明: 如果MySQLdump加上–delete-master-logs 则清除以前的日志,以释放空间。但是如果服务器配置为镜像的复制主服务器,用MySQLdump –delete-master-logs删掉MySQL二进制日志很危险,因为从服务器可能还没有完全处理该二进制日志的内容。在这种情况下,使用 PURGE MASTER LOGS更为安全。 每日定时使用 MySQLadmin flush-logs来创建新日志,并结束前一日志写入过程。并把前一日志备份,例如上例中开始保存数据目录下的日志文件 MySQL-bin.000002 , ... 1.恢复完全备份 mysql -u root -p < backup\_sunday\_1\_PM.sql 2.恢复增量备份 mysqlbinlog MySQL-bin.000002 … | MySQL -u root -p注意此次恢复过程亦会写入日志文件,如果数据量很大,建议先关闭日志功能 结合Linux的cron命令实现定时备份 比如需要在每天凌晨1:30备份某个主机上的所有数据库并压缩dump文件为gz格式 30 1 \* \* \* mysqldump -u root -pPASSWORD --all-databases | gzip > /mnt/disk2/database\_\`date '+%m-%d-%Y'\`.sql.gz mysqldump全量备份+mysqlbinlog二进制日志增量备份 1)从mysqldump备份文件恢复数据会丢失掉从备份点开始的更新数据,所以还需要结合mysqlbinlog二进制日志增量备份。 首先确保已开启binlog日志功能。在my.cnf中包含下面的配置以启用二进制日志: \[mysqld\] log-bin=mysql-bin 2)mysqldump命令必须带上--flush-logs选项以生成新的二进制日志文件: mysqldump --single-transaction --flush-logs --master-data=2 > backup.sql 其中参数--master-data=\[0|1|2\] 0: 不记录 1:记录为CHANGE MASTER语句 2:记录为注释的CHANGE MASTER语句 下面分享一下自己用过的mysqldump全量和增量备份脚本 应用场景: 1)增量备份在周一到周六凌晨3点,会复制mysql-bin.00000\*到指定目录; 2)全量备份则使用mysqldump将所有的数据库导出,每周日凌晨3点执行,并会删除上周留下的mysq-bin.00000\*,然后对mysql的备份操作会保留在bak.log文件中。 脚本实现: 1)全量备份脚本(假设mysql登录密码为123456;注意脚本中的命令路径): #!/bin/bash # Program # use mysqldump to Fully backup mysql data per week! # History # Path today=`date +%Y%m%d` whichday=`date -d $today +%w` monday=`date -d "$today -$[${whichday}-1] days" +%Y%m%d` Date=$monday BakDir=/home/mysql/backup/$Date LogFile=/home/mysql/backup/bak.log Begin=`date +"%Y年%m月%d日 %H:%M:%S"` if [ ! -d $BakDir ];then mkdir -p $BakDir && mkdir -p $BakDir/daily fi cd $BakDir DumpFile=$Date.sql GZDumpFile=$Date.sql.tgz mysqldump -uroot -phello123456 --quick --events --databases gaoke --flush-logs --delete-master-logs --single-transaction > $DumpFile /bin/tar -zvcf $GZDumpFile $DumpFile /bin/rm $DumpFile Last=`date +"%Y年%m月%d日 %H:%M:%S"` echo 开始全量备份:$Begin 结束:$Last $GZDumpFile SUCC >> $LogFile cd $BakDir/daily /bin/rm -f * 如果备份多个数据库那么: mysqldump -u root -p --databases mysql stady > /opt/mysql-stady.sql 2)增量备份脚本(脚本中mysql的数据存放路径是/home/mysql/data,具体根据自己的实际情况进行调整) #!/bin/bash # Program # use cp to backup mysql data everyday! # History # Path today=`date +%Y%m%d` whichday=`date -d $today +%w` monday=`date -d "$today -$[${whichday}-1] days" +%Y%m%d` Date=$monday BakDir=/home/mysql/backup/$Date/daily # //增量备份时复制mysql-bin.00000*的目标目录,提前手动创建这个目录 BinDir=/var/lib/mysql # //mysql的数据目录 LogFile=/home/mysql/backup/bak.log BinFile=$BinDir/mysql-bin.index # //mysql的index文件路径,放在数据目录下的 mysqladmin -uroot -phello123456 flush-logs #这个是用于产生新的mysql-bin.00000*文件 Counter=`wc -l $BinFile |awk '{print $1}'` NextNum=0 #这个for循环用于比对$Counter,$NextNum这两个值来确定文件是不是存在或最新的 for file in `cat $BinFile` do base=`basename $file` #basename用于截取mysql-bin.00000*文件名,去掉./mysql-bin.000005前面的./ NextNum=`expr $NextNum + 1` if [ $NextNum -eq $Counter ] then echo $base skip! >> $LogFile else dest=$BakDir/$base if(test -e $dest) #test -e用于检测目标文件是否存在,存在就写exist!到$LogFile去 then echo $base exist! >> $LogFile else echo $base copying >> $LogFile cp $BinDir/$base $BakDir echo `date +"%Y年%m月%d日 %H:%M:%S"` $base 新的增量备份 Bakup succ! >> $LogFile fi fi done 3)设置crontab任务,执行备份脚本。先执行的是增量备份脚本,然后执行的是全量备份脚本: [root@test-huanqiu ~]# crontab -e #每个星期1凌晨3:00执行完全备份脚本 0 3 * * 1 /bin/bash -x /root/mysqlbackup/full_backup.sh >/dev/null 2>&1 #周2-7凌晨3:00做增量备份 0 3 * * 2-7 /bin/bash -x /root/mysqlbackup/binlog_backup.sh >/dev/null 2>&1 4)手动执行上面两个脚本,测试下备份效果 [root@test-huanqiu backup]# pwd /home/mysql/backup [root@test-huanqiu backup]# mkdir daily [root@test-huanqiu backup]# ll total 4 drwxr-xr-x. 2 root root 4096 Nov 29 11:29 daily [root@test-huanqiu backup]# ll daily/ total 0 先执行增量备份脚本 [root@test-huanqiu backup]# sh /root/Mysql-DailyBak.sh [root@test-huanqiu backup]# ll total 8 -rw-r--r--. 1 root root 121 Nov 29 11:29 bak.log drwxr-xr-x. 2 root root 4096 Nov 29 11:29 daily [root@test-huanqiu backup]# ll daily/ total 8 -rw-r-----. 1 root root 152 Nov 29 11:29 mysql-binlog.000030 -rw-r-----. 1 root root 152 Nov 29 11:29 mysql-binlog.000031 [root@test-huanqiu backup]# cat bak.log mysql-binlog.000030 copying mysql-binlog.000031 copying mysql-binlog.000032 skip! 2016年11月29日 11:29:32 Bakup succ! 然后执行全量备份脚本 [root@test-huanqiu backup]# sh /root/Mysql-FullyBak.sh 20161129.sql [root@test-huanqiu backup]# ll total 152 -rw-r--r--. 1 root root 145742 Nov 29 11:30 20161129.sql.tgz -rw-r--r--. 1 root root 211 Nov 29 11:30 bak.log drwxr-xr-x. 2 root root 4096 Nov 29 11:30 daily [root@test-huanqiu backup]# ll daily/ total 0 [root@test-huanqiu backup]# cat bak.log mysql-binlog.000030 copying mysql-binlog.000031 copying mysql-binlog.000032 skip! 2016年11月29日 11:29:32 Bakup succ! 开始:2016年11月29日 11:30:38 结束:2016年11月29日 11:30:38 20161129.sql.tgz succ 5.增量备份恢复: mysqlbinlog --no-defaults --start-datetime='18-07-03 21:56:11' --stop-datetime='18-07-03 21:56:04' mysql-bin.000003 | mysql -u root -p #结束节点
还没有评论,来说两句吧...