测试数据库版本: 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)
恢复数据
通过 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
执行完成之后,我们再去查看一下表和数据,就可以看到数据已经恢复了.
我们需要提前知道我们误操作的 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
恢复之前最好在测试库中执行以下,看看恢复结果是否正确,然后再执行恢复操作.
以上就可以将数据恢复出来.
/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
然后就可以直接恢复出数据.