MYSQL增量备份和全量备份脚本分享

布满荆棘的人生 2022-12-29 12:39 443阅读 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；注意脚本中的命令路径）：

  1. #!/bin/bash
  2. # Program
  3. # use mysqldump to Fully backup mysql data per week!
  4. # History
  5. # Path
  6. today=`date +%Y%m%d`
  7. whichday=`date -d $today +%w`
  8. monday=`date -d "$today -$[${whichday}-1] days" +%Y%m%d`
  9. Date=$monday
  10. BakDir=/home/mysql/backup/$Date
  11. LogFile=/home/mysql/backup/bak.log
  12. Begin=`date +"%Y年%m月%d日 %H:%M:%S"`
  13. if [ ! -d $BakDir ];then
  14.     mkdir -p $BakDir && mkdir -p $BakDir/daily
  15. fi
  16. cd $BakDir
  17. DumpFile=$Date.sql
  18. GZDumpFile=$Date.sql.tgz
  19. mysqldump -uroot -phello123456 --quick --events --databases gaoke  --flush-logs --delete-master-logs --single-transaction > $DumpFile
  20. /bin/tar -zvcf $GZDumpFile $DumpFile
  21. /bin/rm $DumpFile
  22. Last=`date +"%Y年%m月%d日 %H:%M:%S"`
  23. echo 开始全量备份:$Begin 结束:$Last $GZDumpFile SUCC >> $LogFile
  24. cd $BakDir/daily
  25. /bin/rm -f *

如果备份多个数据库那么：

  1. mysqldump -u root -p --databases mysql stady > /opt/mysql-stady.sql

2）增量备份脚本（脚本中mysql的数据存放路径是/home/mysql/data，具体根据自己的实际情况进行调整）

  1. #!/bin/bash
  2. # Program
  3. # use cp to backup mysql data everyday!
  4. # History
  5. # Path
  6. today=`date +%Y%m%d`
  7. whichday=`date -d $today +%w`
  8. monday=`date -d "$today -$[${whichday}-1] days" +%Y%m%d`
  9. Date=$monday
  10. BakDir=/home/mysql/backup/$Date/daily  #                   //增量备份时复制mysql-bin.00000*的目标目录，提前手动创建这个目录
  11. BinDir=/var/lib/mysql           #                        //mysql的数据目录
  12. LogFile=/home/mysql/backup/bak.log
  13. BinFile=$BinDir/mysql-bin.index  #         //mysql的index文件路径，放在数据目录下的
  14. mysqladmin -uroot -phello123456 flush-logs        #这个是用于产生新的mysql-bin.00000*文件
  15. Counter=`wc -l $BinFile |awk '{print $1}'`
  16. NextNum=0
  17. #这个for循环用于比对$Counter,$NextNum这两个值来确定文件是不是存在或最新的
  18. for file in `cat $BinFile`
  19. do
  20.         base=`basename $file`
  21.             #basename用于截取mysql-bin.00000*文件名，去掉./mysql-bin.000005前面的./
  22.         NextNum=`expr $NextNum + 1`
  23.         if [ $NextNum -eq $Counter ]
  24.             then
  25.             echo $base skip! >> $LogFile
  26.         else
  27.             dest=$BakDir/$base
  28.             if(test -e $dest)
  29.             #test -e用于检测目标文件是否存在，存在就写exist!到$LogFile去
  30.             then
  31.             echo $base exist! >> $LogFile
  32.             else
  33.             echo $base copying >> $LogFile
  34.             cp $BinDir/$base $BakDir
  35.             echo `date +"%Y年%m月%d日 %H:%M:%S"` $base  新的增量备份 Bakup succ! >> $LogFile
  36.             fi
  37.         fi
  38. done

3）设置crontab任务，执行备份脚本。先执行的是增量备份脚本，然后执行的是全量备份脚本：

  1. [root@test-huanqiu ~]# crontab -e
  2. #每个星期1凌晨3:00执行完全备份脚本
  3. 0 3 * * 1 /bin/bash -x /root/mysqlbackup/full_backup.sh >/dev/null 2>&1
  4. #周2-7凌晨3:00做增量备份
  5. 0 3 * * 2-7 /bin/bash -x /root/mysqlbackup/binlog_backup.sh >/dev/null 2>&1

4）手动执行上面两个脚本，测试下备份效果

  1. [root@test-huanqiu backup]# pwd
  2. /home/mysql/backup
  3. [root@test-huanqiu backup]# mkdir daily
  4. [root@test-huanqiu backup]# ll
  5. total 4
  6. drwxr-xr-x. 2 root root 4096 Nov 29 11:29 daily
  7. [root@test-huanqiu backup]# ll daily/
  8. total 0
  9. 先执行增量备份脚本
  10. [root@test-huanqiu backup]# sh /root/Mysql-DailyBak.sh
  11. [root@test-huanqiu backup]# ll
  12. total 8
  13. -rw-r--r--. 1 root root 121 Nov 29 11:29 bak.log
  14. drwxr-xr-x. 2 root root 4096 Nov 29 11:29 daily
  15. [root@test-huanqiu backup]# ll daily/
  16. total 8
  17. -rw-r-----. 1 root root 152 Nov 29 11:29 mysql-binlog.000030
  18. -rw-r-----. 1 root root 152 Nov 29 11:29 mysql-binlog.000031
  19. [root@test-huanqiu backup]# cat bak.log
  20. mysql-binlog.000030 copying
  21. mysql-binlog.000031 copying
  22. mysql-binlog.000032 skip!
  23. 2016年11月29日 11:29:32 Bakup succ!
  24. 然后执行全量备份脚本
  25. [root@test-huanqiu backup]# sh /root/Mysql-FullyBak.sh
  26. 20161129.sql
  27. [root@test-huanqiu backup]# ll
  28. total 152
  29. -rw-r--r--. 1 root root 145742 Nov 29 11:30 20161129.sql.tgz
  30. -rw-r--r--. 1 root root 211 Nov 29 11:30 bak.log
  31. drwxr-xr-x. 2 root root 4096 Nov 29 11:30 daily
  32. [root@test-huanqiu backup]# ll daily/
  33. total 0
  34. [root@test-huanqiu backup]# cat bak.log
  35. mysql-binlog.000030 copying
  36. mysql-binlog.000031 copying
  37. mysql-binlog.000032 skip!
  38. 2016年11月29日 11:29:32 Bakup succ!
  39. 开始:2016年11月29日 11:30:38 结束:2016年11月29日 11:30:38 20161129.sql.tgz succ

5.增量备份恢复：

  1. 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   #结束节点

发表评论

表情：
评论列表 （有 0 条评论，443人围观）

还没有评论，来说两句吧...

相关阅读