mysql 指定回滚_mysql回滚到指定时间点

# mysql回滚到指定时间点

> 回滚是基于全量备份+增量binlog, 如果binlog没开启，只能跑路了

场景模拟:

> 线上数据库 marketing 在1月5号做了一次全量备份 backup.0105.sql, 同时开启了binlog, binlog日志保留30天。 2月3号晚上7:38分钟左右的时候员工误操作，清空了表 user, 如何回滚?

实施步骤:

1. 创建新数据库 mysql-recovery, source backup.0105.sql 恢复1月5号的数据

2. 在原数据库下binlog目录下根据binlog生成增量sql(这里不需要指定开始位置, 如果需要的话，需要自己指定)

``` mysql

mysqlbinlog --stop-datetime='2020-02-03 19:35:00' mysql-bin.00000* > tmp.sql

```

3. 进入恢复数据库 mysql-recovery, `source tmp.sql`, 恢复数据(直接binlog命令恢复，有可能因为版本不一致导致失败)

4. 导出mysql-recovery的table1，重新导入生产库中，恢复完成

---

上面的步骤只能通过模糊时间来定位，如果时间记不住，需要找下position来恢复

1. 查看当前bin_log位置

``` linux

mysql> show master status;

+------------------+-----------+--------------+------------------+------------------------------------------------+

+------------------+-----------+--------------+------------------+------------------------------------------------+

+------------------+-----------+--------------+------------------+------------------------------------------------+

1 row in set (0.10 sec)

```

2. binlog解析(里面会包含truncate position)

``` linux

mysqlbinlog -d szty --start-datetime="2020-05-18 08:05:00" mysql-bin.000003 > abc.sql

```

``` linux

root@3103f5b8fffc:/var/lib/mysql# cat abc.sql | grep my_table

TRUNCATE TABLE `szty`.`my_table`

root@3103f5b8fffc:/var/lib/mysql# cat abc.sql | grep -C 10 my_table

COMMIT/*!*/;

# at 441648736

#200518 8:06:29 server id 1 end_log_pos 441648801 CRC32 0x65c117b5 GTIDlast_committed=483707sequence_number=483708rbr_only=no

SET @@SESSION.GTID_NEXT= 'bd46f5b8-0b6d-11e9-b97d-0242ac110002:3366901'/*!*/;

# at 441648801

##### 找到 end_log_pos(441648900)

#200518 8:06:29 server id 1 end_log_pos 441648900 CRC32 0x95273806 Querythread_id=81exec_time=0error_code=0

use `szty`/*!*/;

SET TIMESTAMP=1589789189/*!*/;

/*!\C utf8mb4 *//*!*/;

SET @@session.character_set_client=45,@@session.collation_connection=45,@@session.collation_server=33/*!*/;

##### 这里执行了truncate

TRUNCATE TABLE `szty`.`my_table`

/*!*/;

# at 441648900

#200518 8:06:29 server id 1 end_log_pos 441648965 CRC32 0xcc7e1adc GTIDlast_committed=483707sequence_number=483709rbr_only=yes

/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;

SET @@SESSION.GTID_NEXT= 'bd46f5b8-0b6d-11e9-b97d-0242ac110002:3366902'/*!*/;

# at 441648965

#200518 8:06:29 server id 1 end_log_pos 441649045 CRC32 0x2ce16af3 Querythread_id=88exec_time=0error_code=0

SET TIMESTAMP=1589789189/*!*/;

/*!\C utf8 *//*!*/;

SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;

```

找到position后，就可以参考上面场景一进行原备份+position恢复了

``` mysql

mysqlbinlog –-stop-position=441648900 mysql-bin.00000* > tmp.sql

```

## 注意点:

1. mysqlbinlog 版本不一致, 有可能会导致命令执行失败

2. 如果在docker容器里面执行, 需要注意下时差问题

3. 如果想看到具体的insert、delete操作，需要加上 -v参数, 打印结果如下所示:

``` linux

# at 557071179

#200518 8:24:03 server id 1 end_log_pos 557071251 CRC32 0x6b5ec6c6 Querythread_id=81exec_time=0error_code=0

SET TIMESTAMP=1589790243/*!*/;

/*!\C utf8mb4 *//*!*/;

SET @@session.character_set_client=45,@@session.collation_connection=45,@@session.collation_server=33/*!*/;

BEGIN

/*!*/;

# at 557071251

#200518 8:24:03 server id 1 end_log_pos 557071305 CRC32 0xeedff224 Table_map: `szty`.`my_table` mapped to number 382

# at 557071305

#200518 8:24:03 server id 1 end_log_pos 557071362 CRC32 0xebdb2648 Delete_rows: table id 382 flags: STMT_END_F

BINLOG '

I0bCXhMBAAAANgAAAMk7NCEAAH4BAAAAAAEABHN6dHkACG15X3RhYmxlAAIIDwI8AAIk8t/u

I0bCXiABAAAAOQAAAAI8NCEAAH4BAAAAAAEAAgAC//wCAAAAAAAAAAEz/AMAAAAAAAAAATRIJtvr

'/*!*/;

### DELETE FROM `szty`.`my_table`

### WHERE

### @1=2

### @2='3'

### DELETE FROM `szty`.`my_table`

### WHERE

### @1=3

### @2='4'

# at 557071362

#200518 8:24:03 server id 1 end_log_pos 557071393 CRC32 0x58ba2aef Xid = 1674630

COMMIT/*!*/;

```

## 参考

[https://blog.csdn.net/JSWANGCHANG/article/details/79500982](https://blog.csdn.net/JSWANGCHANG/article/details/79500982)

[https://blog.csdn.net/river131/article/details/72167853](https://blog.csdn.net/river131/article/details/72167853)

原文链接：https://blog.csdn.net/weixin_29746595/article/details/113153177