MySQL使用binlog日志文件回滚数据

MySQL使用binlog日志文件回滚数据

1. 首先要确保binlog开启

show variables  like 'log_bin';

+-------------+-----+
|Variable_name|Value|
+-------------+-----+
|log_bin      |ON   |
+-------------+-----+

2. 查看最新的binlog文件

mysql> show master status;

+-------------+---------+------------+----------------+-----------------+
|File         |Position |Binlog_Do_DB|Binlog_Ignore_DB|Executed_Gtid_Set|
+-------------+---------+------------+----------------+-----------------+
|binlog.000010|120507544|            |                |                 |
+-------------+---------+------------+----------------+-----------------+

如果有多个binglog需要逐个恢复

3. binlog一般默认在/var/lib/mysql下，找到上述查询到的最新文件binlog.000010

4. 根据要回滚的大致时间查询定位

mysqlbinlog --no-defaults --database=tradesystem --start-datetime="2020-08-18 09:00:00" --stop-datetime="2020-08-18 13:00:00" binlog.000010

5. 根据查询到的定位生成sql

mysqlbinlog --start-position=0 --stop-position=45325593 binlog.000010 > rollback.sql

6. 执行sql恢复数据库