mysql binlog数据恢复

什么是binlog

binlog是一个server层日志，记录数据库表的所有DDL及DML(除查询以外)操作语句的一个二进制日志。（create、alter table ...）（insert、update、delete ...）

而恢复数据则是将丢失的数据找到最后状态再次执行一次

主要作用

• 主从复制：MySQL Replication在Master端开启binlog，Master把它的二进制日志传递给slaves来达到master-slave数据一致的目的
• 数据恢复：通过mysqlbinlog工具可以恢复数据

常用命令

 

# 是否开启binlog 
show variables like 'log_%';

# 查看所有binglog日志
show master logs;

# 当前使用的日志
show master status;

# 查看日志记录
show binlog events in 'binlog.000001';

# 格式化输出日志linux命令
mysqlbinlog -v --base64-output=DECODE-ROWS binlog.000001


# 刷新日志，重新生成日志文件
flush logs;

# 重置日志
reset master;

  未开启则创建或修改/etc/my.cnf

[mysqld]
bind-address = 127.0.0.1
log-bin = /usr/local/Cellar/mysql/5.7.15/binlog
server-id = 13445
character-set-server = utf8

 

 

数据恢复

当发现数据库数据被删时，首先查询日志定位问题，思考恢复策略。数据量不大时，通过binlog找到sql进行恢复，数据量过大时可以进行时间段恢复。

• 指定SQL恢复 通过关键字进行日志搜索，找到想要的恢复数据的sql记录
• 时间段恢复 指定起始时间和结束时间进行恢复

# 日志格式化输出到文件
mysqlbinlog -v --base64-output=DECODE-ROWS binlog.000001 > ~/binlog.sql

# 搜索关键字日志格式化输出到文件
mysqlbinlog --base64-output=decode-rows -v binlog.000001 | grep -A '四川' > ~/binlog.sql

# mysqlbinlog 命令节点恢复数据
mysqlbinlog  --start-position=419 --stop-position=667 --database=test binlog.000001 |mysql -u用户 -p密码 test

通过Mysqlbinlog命令进行数据恢复
如插入的数据被删除了,找到这条数据的insert记录起始pos点和结束pos点,  事务的pos点也要包含在内

 

 

 

可选参数选项：

--start-position=419 起始pos点
--stop-position=667 结束pos点
--start-datetime="2018-7-18 17:00:00" 起始时间点
--stop-datetime="2018-7-18 17:00:00" 结束时间点
--database=test 指定数据库

常见问题

ERROR 1790 (HY000) at line 18: @@SESSION.GTID_NEXT cannot be changed by a client that owns a GTID. The client owns ANONYMOUS. Ownership is released on COMMIT or ROLLBACK.

原因: 设置的起始pos和结束pos点并不是一条完整的mysql事务开启事务结束语