binlog的备份和利用binlog恢复数据

逻辑备份常用mysqldump，物理备份常用xtrabackup，但是这两种备份只能恢复到最后一次备份的那一刻，而从最后一次备份之后的操作需要恢复的话则可以用binlog来协助。mysql的binlog就像一个时间记录仪，里面包含了所有发生变化的明细，从而可以使用binlog来实现逆向操作。

一、binlog的备份

在my.cnf中一般会配置binlog的保存市场，参数expire_logs_days，那我们在结合mysqldump和xtrabackup工具做备份的时候也需要考虑将binlog做备份，可以通过脚本的方式定时将binlog复制到别的地方进行存储，mysql官方的mysqlbinlog命令，有选项"read-from-remote-server"配合选项"stop-never"可以实时的同步binlog数据到远程备份服务器，一个基本的备份命令如下：
mysqlbinlog --read-from-remote-server --raw --host=xx.xxx --port=3306 --user=repl --password=xxx --stop-never mysql-bin.000001

二、用binlog进行数据恢复

• 1、日常的增删改操作都会在binlog中留下记录，主要是以时间轴、position来标记位置，加上5.7中还会开启GTID，所以我们在用binlog来做数据恢复时也可以用这三个参数来定义误操作的位置，如我们用show binlog events先查看一段简单的binlog：

  

  可以在标记的地方看到对应的pos和GTID。

  [NOTE]
  show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];是上述命令的基本格式，其中IN 'log_name' 指定要查询的binlog文件名，如果省略此参数，则默认指定第一个binlog文件；FROM pos 指定从哪个pos起始点开始查起，如果省略此参数，则从整个文件的第一个pos点开始算；LIMIT【offset】 偏移量，默认为0；row_count 查询总条数，如果省略，则显示所有行。如查询mysql-bin.000009文件，从pos点194开始查询，查询5条记录，偏移2行是show binlog events in 'mysql-bin.000009' from 194 limit 2,5;。

  当然我们更多的还是用mysql官方的命令mysqlbinlog，可以通过 --help查看帮助信息，常用的参数有，
  -v
  --start-datetime=name和--stop-datetime=name
  --start-position=#和stop-position=#
  --skip-gtids和--include-gtids=name和--exclude-gtids=name
  如：

  mysqlbinlog --no-defaults -v --start-position=194 --stop-position=590 /database/mysql/data/mysql-bin.000009
  mysqlbinlog --no-defaults -v --start-datetime="2023-07-06 15:07:00" --stop-datetime="2023-07-06 15:09:00"  /database/mysql/data/mysql-bin.000009
  mysqlbinlog --no-defaults -v --include-gtids='0b9eb17c-14b6-11ee-b5a0-000c29824685:1-3'  /database/mysql/data/mysql-bin.000009
  mysqlbinlog --no-defaults -v --exclude-gtids='0b9eb17c-14b6-11ee-b5a0-000c29824685:3'  /database/mysql/data/mysql-bin.000009
  

• 2、恢复数据
  有了上面的结果，恢复数据只需要根据实际业务需求将结果重新导入数据库即可。比如上述中的删除操作是误操作，那我们做恢复时可以利用之前的全备加上用--exclude-gtids选项跳过误操作的GTID，在全备的基础上重新再执行一遍跳过了误操作的binlog。

  [NOTE]
  导入的方式可以在数据库中用source命令或者在数据库外使用mysql -uroot -p < XXX.sql这种。

三、binlog2sql工具

上面的例子是测试环境，binlog本身记录不多，而在生产环境中要恢复被误删的数据再重新跑一遍binlog可能时间会很长，更快的恢复方式是找到对应的误操作的binlog日志，再将操作进行逆向后的sql导入。binlog2sql则是一款专门解析binlog的开源工具。

• 1、工具下载
  这个工具是Python开发，在GitHub中可以下载对应源码，也有对应的使用说明，详细可见binlog2sql GitHub：
  git clone https://github.com/danfengcao/binlog2sql.git && cd binlog2sql
pip install -r requirements.txt

• 2、使用
  解析出标准sql：python /root/binlog2sql/binlog2sql/binlog2sql.py --user=binlog2sql --password=binlog2sql --start-file='mysql-bin.000009'
  解析回滚sql：python /root/binlog2sql/binlog2sql/binlog2sql.py --flashback --user=binlog2sql --password=binlog2sql --start-file='mysql-bin.000009'

  [NOTE]
  跟mysqlbinlog有些相似之处，也是可以通过position和datetime来进行筛选，具体的可以通过--help或者官方中的说明来了解，另外建议是创建专门的用户用于解析，用户的最小权限需求也有说明。