mysql binlog数据恢复

binlog的作用及开启

binlog的作用是将数据库的所有操作进行记录

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

是否开启binlog

show variables like 'log_%';

 

未开启则创建或修改/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日志 

show master logs;

查看当前使用日志

show master status;

查看日志文件操作记录

show binlog events in 'binlog.000001';

进入日志目录为之前配置的my.cnf配置log-bin目录

查询详细记录及记录pos点

mysqlbinlog -v --base64-output=DECODE-ROWS binlog.000001

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

使用mysqlbinlog命令可以日志数据恢复

mysqlbinlog  --start-position=419 --stop-position=667 --database=test binlog.000001 |mysql -u用户 -p密码 test

可选参数选项：
--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事务开启事务结束语句

 

清空所有binlog日志.重新开始记数

reset master;