mysql 通过 binglog 恢复数据

mysql 通过 binglog 恢复数据

测试数据库版本: 8.0.5

查看当前是否开启
进入数据库,查看当前是否开启了 binglog 的相关设置:

mysql> show variables like 'log_bin%';
+---------------------------------+-----------------------------+
| Variable_name                   | Value                       |
+---------------------------------+-----------------------------+
| log_bin                         | ON                          |
| log_bin_basename                | /var/lib/mysql/binlog       |
| log_bin_index                   | /var/lib/mysql/binlog.index |
| log_bin_trust_function_creators | OFF                         |
| log_bin_use_v1_row_events       | OFF                         |
+---------------------------------+-----------------------------+
5 rows in set (0.04 sec)
这里状态是打开的,如果为off,则需要修改为开启状态,云厂商的数据库实例,需要修改相应参数. 部署的服务可以修改配置文件

查看所有 binlog 日志文件列表:

mysql> show master logs;
+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000001 |       157 | No        |
+---------------+-----------+-----------+
1 row in set (0.00 sec)
我这里是新建的数据库,所以只有一个binlog日志文件,如果有多个,则需要选择最新的binlog日志文件

查看最后一个 binlog 日志的编号名称及其最后一个操作事件 pos 结束点的值：show master status;
然后我们刷新日志,并且查看当前的 binlog 日志文件列表: flush logs; show master logs;
会产生一个新的日志文件.然后开始测试.

测试恢复
进入到测试库,然后新建表,并插入新的数据:

CREATE TABLE students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    age INT,
    gender VARCHAR(10),
    major VARCHAR(50)
);

INSERT INTO students (name, age, gender, major) VALUES
    ('Alice', 18, 'Female', 'Mathematics'),
    ('Bob', 19, 'Male', 'Computer Science'),
    ('Charlie', 20, 'Male', 'Physics'),
    ('Daisy', 19, 'Female', 'Chemistry');

然后可以查看到当前的 binlog 日志的事件内容:

SHOW BINLOG EVENTS IN 'binlog.000002';
-------------+
| Log_name      | Pos | Event_type     | Server_id | End_log_pos | Info                                                                                                                                                                       |
+---------------+-----+----------------+-----------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| binlog.000002 |   4 | Format_desc    |         1 |         126 | Server ver: 8.0.35, Binlog ver: 4                                                                                                                                          |
| binlog.000002 | 126 | Previous_gtids |         1 |         157 |                                                                                                                                                                            |
| binlog.000002 | 157 | Anonymous_Gtid |         1 |         236 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                                                                                                       |
| binlog.000002 | 236 | Query          |         1 |         469 | use `test`; CREATE TABLE students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    age INT,
    gender VARCHAR(10),
    major VARCHAR(50)
) /* xid=17 */ |
| binlog.000002 | 469 | Anonymous_Gtid |         1 |         548 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                                                                                                       |
| binlog.000002 | 548 | Query          |         1 |         623 | BEGIN                                                                                                                                                                      |
| binlog.000002 | 623 | Table_map      |         1 |         692 | table_id: 94 (test.students)                                                                                                                                               |
| binlog.000002 | 692 | Write_rows     |         1 |         858 | table_id: 94 flags: STMT_END_F                                                                                                                                             |
| binlog.000002 | 858 | Xid            |         1 |         889 | COMMIT /* xid=18 */                                                                                                                                                        |

通过这个我们可以查到对应的 pos 点,然后通过 pos 点进行恢复数据,现在我们将表直接删除掉,模拟误操作, 删除之后,再次刷新 binlog 日志,将之后的操作记录到新的 binlog 日志中.

flush logs;

mysql> show master logs;
+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000001 |       201 | No        |
| binlog.000002 |      1143 | No        |
| binlog.000003 |       157 | No        |
+---------------+-----------+-----------+
3 rows in set (0.00 sec)

恢复数据

1. 全部恢复: 将整个 binglog 文件内容共都进行一次重现操作.

通过 binlog 的日志文件,来恢复数据,这里我们需先检查一下 binlog 文件内容,
打开指定的 binlog 文件: mysqlbinlog binlog.000002 ,我们从文件中可以看到我们执行的删除操作也被记录在里面:

DROP TABLE `students` /* generated by server */

但是我们不能直接编辑这个文件,因为这个文件是二进制文件,我们需要通过 mysqlbinlog 工具来解析这个文件,然后生成一个可执行的 sql 文件,然后再执行这个 sql 文件,来恢复数据.

 mysqlbinlog -u root -pyour_pass  -R binlog.000002 > output.sql

然后将 sql 备份一下,
然后我们编辑这个 sql 文件,将其中的删除操作删除掉,删除的时候需要仔细检查,因为这个文件中可能有多个删除操作,我们只需要删除我们误操作的删除操作即可.
然后执行这个 sql 文件,就可以恢复数据了.

mysql -uroot -pyour_pass test < output.sql

执行完成之后,我们再去查看一下表和数据,就可以看到数据已经恢复了.

2. 通过 pos 点恢复: 通过指定的 pos 点来恢复数据.

我们需要提前知道我们误操作的 pos 点,以及结束的 pos 点,然后通过指定的 pos 点来恢复数据.
上面的示例中我们开始的 pos 点是: 236 结束的 pos 点是: 889, 注意不要把删除操作的 pos 点包含进去.
所以可以执行命令:

mysqlbinlog  --start-position=236 --stop-position=889   --database=test   /var/lib/mysql/binlog.000002 | mysql -uroot -p密码 -v test

恢复之前最好在测试库中执行以下,看看恢复结果是否正确,然后再执行恢复操作.
以上就可以将数据恢复出来.

3. 通过时间点来恢复: 通过指定的时间点来恢复数据.
   我们在通过 mysqlbinlog binlog.000002 命令查看 binlog 文件内容的时候,可以看到每一条操作的时间点,我们可以通过指定时间点来恢复数据.
   确认我们误操作的时间点,然后通过指定时间点来恢复数据.
   执行命令:

/usr/bin/mysqlbinlog --start-datetime="2024-01-03 17:22:10" --stop-datetime="2024-01-03 17:33:52" --database=test  /var/lib/mysql/binlog.000002   |mysql -uroot -p密码 -v test

然后就可以直接恢复出数据.