开启binlog

这是一个mysql配置文件示例

# 开启二进制日志
log-bin = mysql-bin
# 日志格式（mixed, statement, row）
binlog_format = mixed
# 设置过期时间
expire_logs_days = 30
# server id必须唯一
server-id = 1

查看日志命令

-- 列出二进制日志
show master logs;

-- 具体查看某个日志事件
show binlog events in 'mysql-bin.000003'

show binglog events

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 查询总条数(不指定就是所有行)

启动一个测试用的Docker

docker run --name test-mysql -d \
    -v /home/fayfox/mysql/conf:/etc/mysql/mysql.conf.d/:ro \
    -v /home/fayfox/mysql/data:/var/lib/mysql \
    -e MYSQL_ROOT_PASSWORD=123456 \
    mysql

模拟数据

构建测试库

-- 建库
create database binlogtest;

-- 使用库
use binlogtest;

-- 建表
CREATE TABLE `test` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL DEFAULT '',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 初始测试数据
INSERT INTO `test`(`title`) VALUES ('a'), ('b'), ('c');

备份库

mysqldump -uroot -p -F binlogtest > ./bak.sql

-F 会在备份前先flush logs

编辑数据

UPDATE `test` SET `title` = 'dd' WHERE `id` = 3;
INSERT INTO `test`(`title`) VALUES ('e');

删表（模拟异常操作）

DROP TABLE `test`;

进行恢复

刷新日志索引

flush logs;

避免当前日志文件继续写入，使问题复杂化

导入之前的备份

利用mysql命令导入

mysql -uroot -p -D binlogtest < bak.sql

进入mysql客户端后用source命令导入

source /var/lib/mysql/bak.sql

查找问题语句位置

利用show binlog events查看

show binlog events in 'binlog.000003';

+---------------+-----+----------------+-----------+-------------+----------------------------------------------------------------------------+
| Log_name      | Pos | Event_type     | Server_id | End_log_pos | Info                                                                       |
+---------------+-----+----------------+-----------+-------------+----------------------------------------------------------------------------+
| binlog.000003 | 696 | Write_rows     |         1 |         743 | table_id: 66 flags: STMT_END_F                                             |
| binlog.000003 | 743 | Xid            |         1 |         774 | COMMIT /* xid=76 */                                                        |
| binlog.000003 | 774 | Anonymous_Gtid |         1 |         847 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                       |
| binlog.000003 | 847 | Query          |         1 |         988 | use `binlogtest`; DROP TABLE `test` /* generated by server */ /* xid=78 */ |
| binlog.000003 | 988 | Rotate         |         1 |        1032 | binlog.000004;pos=4                                                        |
+---------------+-----+----------------+-----------+-------------+----------------------------------------------------------------------------+
15 rows in set (0.00 sec)

可以看到，DROP语句从847位置开始

利用mysqlbinlog命令查看

mysqlbinlog binlog.000003

# at 774
#180717 14:59:38 server id 1  end_log_pos 847 CRC32 0x9fbb510b     Anonymous_GTID    last_committed=2    sequence_number=3    rbr_only=no    original_committed_timestamp=1531839578681339    immediate_commit_timestamp=1531839578681339    transaction_length=214
# original_commit_timestamp=1531839578681339 (2018-07-17 14:59:38.681339 UTC)
# immediate_commit_timestamp=1531839578681339 (2018-07-17 14:59:38.681339 UTC)
/*!80001 SET @@session.original_commit_timestamp=1531839578681339*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 847
#180717 14:59:38 server id 1  end_log_pos 988 CRC32 0x60ea3242     Query    thread_id=8    exec_time=0    error_code=0    Xid = 78
use `binlogtest`/*!*/;
SET TIMESTAMP=1531839578/*!*/;
DROP TABLE `test` /* generated by server */
/*!*/;

同样可以看到，DROP语句从847位置开始

执行恢复

mysqlbinlog --stop-position=847 ./binlog.000003 | mysql -uroot -p -D binlogtest