企业建站套餐,免费提供网站建设,网页升级紧急通知域名,专做装修的网站mysql 通过 binglog 恢复数据
测试数据库版本: 8.0.5
查看当前是否开启 进入数据库,查看当前是否开启了 binglog 的相关设置:
mysql show variables like log_bin%;
--------------------------------------------------------------
| Variable_name …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)
) /* xid17 */ |
| 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 /* xid18 */ |通过这个我们可以查到对应的 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)
恢复数据
全部恢复: 将整个 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执行完成之后,我们再去查看一下表和数据,就可以看到数据已经恢复了.
通过 pos 点恢复: 通过指定的 pos 点来恢复数据.
我们需要提前知道我们误操作的 pos 点,以及结束的 pos 点,然后通过指定的 pos 点来恢复数据. 上面的示例中我们开始的 pos 点是: 236 结束的 pos 点是: 889, 注意不要把删除操作的 pos 点包含进去. 所以可以执行命令:
mysqlbinlog --start-position236 --stop-position889 --databasetest /var/lib/mysql/binlog.000002 | mysql -uroot -p密码 -v test恢复之前最好在测试库中执行以下,看看恢复结果是否正确,然后再执行恢复操作. 以上就可以将数据恢复出来.
通过时间点来恢复: 通过指定的时间点来恢复数据. 我们在通过 mysqlbinlog binlog.000002 命令查看 binlog 文件内容的时候,可以看到每一条操作的时间点,我们可以通过指定时间点来恢复数据. 确认我们误操作的时间点,然后通过指定时间点来恢复数据. 执行命令:
/usr/bin/mysqlbinlog --start-datetime2024-01-03 17:22:10 --stop-datetime2024-01-03 17:33:52 --databasetest /var/lib/mysql/binlog.000002 |mysql -uroot -p密码 -v test然后就可以直接恢复出数据.
