浅谈使用Binlog实现MySQL增量备份

09/06 20:46
阅读数 14

在写文章的时候,我一直在纠结,这个到底能不能算增量备份,因为使用 binlog 的这种方式,按照官方文档的说话,应该叫做 point-in-time ,而非正经的增量模式,但是也聊胜于无。

首先我先阐述一下,他的基本原理,就是定时制作基线,然后定时更新 binlog,形成增量数据文件,然后在必要的时候进行恢复,追溯。
下面是一个简单的流程图,首先我们来创建一个表

然后,我们来创建一个基线,并且刷新 binlog

现在我们来模拟一些业务操作,插入数据! 

好了,这一天平安过去,我们进行备!

然后,不幸的事情发生了,昨天的数据被删除了

接下来,我们进行数据恢复就好了

这里也只是深入浅出的描述一下增备的流程,实际生活中往往要比这个案例残酷的多。那么我们又该如何选择备份方案呢?

按天备份

周一 00:00 全备数据
周二 00:00 全备数据
26_01.sql.gz
26_02.sql.gz
周一增备
周二增备

binlog.000022

binlog.000023

binlog.000024

......

binlog.000033

binlog.000034

binlog.000035

......

这样做的好处,显然是恢复时间短,维护成本低,同样缺点也很明显,就是占用资源多,而且需要频繁锁表,影响用户的使用体验

按周备份

周六00:00 全备



26_01.sql.gz


周一增备
周二增备 周三增备 ...

binlog.000022

binlog.000023

binlog.000024

......

binlog.000032

binlog.000033

binlog.000034

......

binlog.000042

binlog.000043

binlog.000044

......



......

这么做的优缺点则刚好和上面案例相反,优点是占用资源少,不频繁锁表,用户体验相对好一些,不过代价就是维护成本较高,如果数据出现问题,恢复时间较长。

综上所述,这是一个非常尴尬的问题,造成这个问题的原因即复杂又简单。说简单,是因为可以高度概括为资源不足,四个字。但是细扣下来,就变成时间、空间、成本、智力投入等诸多因素的博弈问题了

最佳实践

全备份

mysqldump -B test -lF -uroot-pdafei1288  > test.sql

参数  --lock-all-tables 对于 InnoDB 将替换为  --single-transaction

该选项在导出数据之前提交一个 BEGIN SQL 语句,BEGIN 不会阻塞任何应用程序且能保证导出时数据库的一致性状态。它只适用于事务表,例如 InnoDBBDB。本选项和 --lock-tables 选项是互斥的,因为 LOCK TABLES 会使任何挂起的事务隐含提交。要想导出大表的话,应结合使用--quick 选项。

  • 参数   --flush-logs,结束当前日志,生成并使用新日志文件
  • 参数   --master-data=2,该选项将会在输出 SQL 中记录下完全备份后新日志文件的名称,用于日后恢复时参考,例如输出的备份 SQL 文件中含有:
CHANGE MASTER TOMASTER_LOG_FILE='MySQL-bin.000002', MASTER_LOG_POS=106;
  • 参数 test,该处的 test 表示数据库 test,如果想要将所有的数据库备份,可以换成参数   --all-databases
  • 参数   --databases  指定多个数据库
  • 参数   --quick-q,该选项在导出大表时很有用,它强制 MySQLdump 从服务器查询取得记录直接输出而不是取得所有记录后将它们缓存到内存中。
  • 参数   --ignore-table,忽略某个数据表,如   --ignore-tabletest.user 忽略数据库 test 里的 user

-lF,注意必须大写 F,当备份工作刚开始时系统会刷新 log 日志,产生新的 binlog 日志来记录备份之后的数据库“增删改”操作。

全恢复

mysql -uroot -pdafei1288 <test.sql

恢复指定库

mysql -uroot -pdafei1288 test1 < test1.sql

增备

环境配置

检查是否开始 binlog

show variables like'\%log_bin\%';

表示没开启,在 my.inf 主配置文件中直接添加三行

log_bin=ONlog_bin_basename=log_bin_index=

重启 mysql

表示已开启。

show binary logs; 查看 binlog 文件列表

开始增备

show master status;

当前正在记录日志的文件名是 binlog.000003

mysqladmin -uroot -pdafei1288flush-logs

生成并使用新的日志文件,再重新查看日志文件列表

恢复

mysqlbinlog --no-defaults--start-position=1082  binlog.000001   | mysql -uroot -pdafei1288

命令列表

mysqldump -B test -lF -uroot-pdafei1288  > test.sql

mysql -uroot -pdafei1288 <test.sql

show variables like'%log_bin\%';

show binary logs;

show master status;

mysqladmin -uroot -pdafei1288 flush-logs

mysqlbinlog --no-defaults--start-position=1082  binlog.000001   | mysql -uroot -pdafei1288

参考资料:

  • https://dev.mysql.com/doc/refman/8.0/en/mysqlbinlog.html
  • https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html
  • https://dev.mysql.com/doc/refman/8.0/en/point-in-time-recovery.html
  • https://www.cnblogs.com/wangke2017/p/9745183.html

本文分享自微信公众号 - 编程三分钟(coding3min)。
如有侵权,请联系 support@oschina.cn 删除。
本文参与“OSC源创计划”,欢迎正在阅读的你也加入,一起分享。

展开阅读全文
打赏
0
0 收藏
分享
加载中
更多评论
打赏
0 评论
0 收藏
0
分享
返回顶部
顶部