通过binlog日志恢复表记录

145次阅读

没有评论

共计 5368 个字符，预计需要花费 14 分钟才能阅读完成。

1 使用 binlog 日志
1.1 问题

利用 binlog 恢复库表，要求如下：

启用 binlog 日志
创建 db1 库 tb1 表，插入 3 条记录
删除 tb1 表中刚插入的 3 条记录
使用 mysqlbinlog 恢复删除的 3 条记录

1.2 步骤

实现此案例需要按照如下步骤进行。

步骤一：启用 binlog 日志

1）调整 /etc/my.cnf 配置，并重启服务

[root@dbsvr1 ~]# vim /etc/my.cnf
[mysqld]
.. ..
log-bin-index=mysql-bin // 启用二进制日志，并指定前缀
server_id=1
binlog_format=STATEMENT // 在 Mysql5.7 中，binlog 日志格式默认为 ROW，但它不记录 sql 语句上下文相关信息。需要将 binlog 日志格式修改为 STATEMENT
.. ..
[root@dbsvr1 ~]# systemctl restart mysqld.service

2）确认 binlog 日志文件

新启用 binlog 后，每次启动 MySQl 服务都会新生成一份日志文件：

[root@dbsvr1 ~]# ls /var/lib/mysql/mysql-bin.*
/var/lib/mysql/mysql-bin.000001 /var/lib/mysql/mysql-bin.index

其中 mysql-bin.index 文件记录了当前保持的二进制文件列表：

[root@dbsvr1 ~]# cat /var/lib/mysql/mysql-bin.index
./mysql-bin.000001

重启 MySQL 服务程序，或者执行 SQL 操作“FLUSH LOGS;”，会生成一份新的日志：

[root@dbsvr1 ~]# ls /var/lib/mysql/mysql-bin.*
/var/lib/mysql/mysql-bin.000001 /var/lib/mysql/mysql-bin.index
/var/lib/mysql/mysql-bin.000002
[root@dbsvr1 ~]# cat /var/lib/mysql/mysql-bin.index
./mysql-bin.000001
./mysql-bin.000002

步骤二：利用 binlog 日志重做数据库操作

1）执行数据库表添加操作

创建 db1·库 tb1 表，表结构自定义：

mysql> CREATE DATABASE db1;
Query OK, 1 row affected (0.05 sec)
mysql> USE db1;
Database changed
mysql> CREATE TABLE tb1(
-> id int(4) NOT NULL,name varchar(24)
-> );
Query OK, 0 rows affected (0.28 sec)

插入 3 条表记录：

mysql> INSERT INTO tb1 VALUES
-> (1,’Jack’),
-> (2,’Kenthy’),
-> (3,’Bob’);
Query OK, 3 rows affected (0.12 sec)
Records: 3 Duplicates: 0 Warnings: 0

确认插入的表记录数据：

mysql> SELECT * FROM tb1;
+—-+——–+
| id | name |
+—-+——–+
| 1 | Jack |
| 2 | Kenthy |
| 3 | Bob |
+—-+——–+
3 rows in set (0.00 sec)

2）删除前一步添加的 3 条表记录

执行删除所有表记录操作：

mysql> DELETE FROM tb1;
Query OK, 3 rows affected (0.09 sec)

确认删除结果：

mysql> SELECT * FROM tb1;
Empty set (0.00 sec)

步骤三：通过 binlog 日志恢复表记录

binlog 会记录所有的数据库、表更改操作，所以可在必要的时候重新执行以前做过的一部分数据操作，但对于启用 binlog 之前已经存在的库、表数据将不适用。

根据上述“恢复被删除的 3 条表记录”的需求，应通过 mysqlbinlog 工具查看相关日志文件，找到删除这些表记录的时间点，只要恢复此前的 SQL 操作（主要是插入那 3 条记录的操作）即可。

1）查看 mysql-bin.000002 日志内容

[root@dbsvr1 ~]# mysqlbinlog /var/lib/mysql/mysql-bin.000002
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#170412 12:05:32 server id 1 end_log_pos 123 CRC32 0x6d8c069c Start: binlog v 4, server v 5.7.17-log created 170412 12:05:32 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG ‘
jKftWA8BAAAAdwAAAHsAAAABAAQANS43LjE3LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAACMp+1YEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
AZwGjG0=
‘/*!*/;
# at 123
#170412 12:05:32 server id 1 end_log_pos 154 CRC32 0x17f50164 Previous-GTIDs
# [empty]
# at 154
#170412 12:05:59 server id 1 end_log_pos 219 CRC32 0x4ba5a976 Anonymous_GTID last_committed=0 sequence_number=1
SET @@SESSION.GTID_NEXT= ‘ANONYMOUS’/*!*/;
# at 219
#170412 12:05:59 server id 1 end_log_pos 310 CRC32 0x5b66ae13 Query thread_id=3 exec_time=0 error_code=0
SET TIMESTAMP=1491969959/*!*/;
SET @@session.pseudo_thread_id=3/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1436549152/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
CREATE DATABASE db1
/*!*/;
# at 310
#170412 12:06:23 server id 1 end_log_pos 375 CRC32 0x2967cc28 Anonymous_GTID last_committed=1 sequence_number=2
SET @@SESSION.GTID_NEXT= ‘ANONYMOUS’/*!*/;
# at 375
#170412 12:06:23 server id 1 end_log_pos 502 CRC32 0x5de09aae Query thread_id=3 exec_time=0 error_code=0
use `db1`/*!*/;
SET TIMESTAMP=1491969983/*!*/;
CREATE TABLE tb1(
id int(4) NOT NULL,name varchar(24)
)
/*!*/;
# at 502
#170412 12:06:55 server id 1 end_log_pos 567 CRC32 0x0b8cd418 Anonymous_GTID last_committed=2 sequence_number=3
SET @@SESSION.GTID_NEXT= ‘ANONYMOUS’/*!*/;
# at 567
#170412 12:06:55 server id 1 end_log_pos 644 CRC32 0x7e8f2fa0 Query thread_id=3 exec_time=0 error_code=0
SET TIMESTAMP=1491970015/*!*/;
BEGIN
/*!*/;
# at 644
#170412 12:06:55 server id 1 end_log_pos 772 CRC32 0x4e3f728e Query thread_id=3 exec_time=0 error_code=0 // 插入表记录的起始时间点
SET TIMESTAMP=1491970015/*!*/;
INSERT INTO tb1 VALUES(1,’Jack’),(2,’Kenthy’), (3,’Bob’)
/*!*/;
# at 772
#170412 12:06:55 server id 1 end_log_pos 803 CRC32 0x6138b21f Xid = 10
// 确认事务的时间点
COMMIT/*!*/;
# at 803
#170412 12:07:24 server id 1 end_log_pos 868 CRC32 0xbef3f472 Anonymous_GTID last_committed=3 sequence_number=4
SET @@SESSION.GTID_NEXT= ‘ANONYMOUS’/*!*/;
# at 868
#170412 12:07:24 server id 1 end_log_pos 945 CRC32 0x5684e92c Query thread_id=3 exec_time=0 error_code=0
SET TIMESTAMP=1491970044/*!*/;
BEGIN
/*!*/;
# at 945
#170412 12:07:24 server id 1 end_log_pos 1032 CRC32 0x4c1c75fc Query thread_id=3 exec_time=0 error_code=0 // 删除表记录的时间点
SET TIMESTAMP=1491970044/*!*/;
DELETE FROM tb1
/*!*/;
# at 1032
#170412 12:07:24 server id 1 end_log_pos 1063 CRC32 0xccf549b2 Xid = 12
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= ‘AUTOMATIC’ /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

2）执行指定 Pos 节点范围内的 sql 命令恢复数据

根据上述日志分析，只要恢复从 2014.01.12 20:12:14 到 2014.01.12 20:13:50 之间的操作即可。可通过 mysqlbinlog 指定时间范围输出，结合管道交给 msyql 命令执行导入重做：

[root@dbsvr1 ~]# mysqlbinlog \
–start-datetime=”2017-04-12 12:06:55″ \
–stop-datetime=”2017-04-12 12:07:23″ \
/var/lib/mysql/mysql-bin.000002 | mysql -u root -p
Enter password: // 验证口令

3）确认恢复结果

mysql> SELECT * FROM db1.tb1;
+—-+——–+
| id | name |
+—-+——–+
| 1 | Jack |
| 2 | Kenthy |
| 3 | Bob |
+—-+——–+
3 rows in set (0.00 sec)

：

正文完

星哥说事-微信公众号