Mysql5.7 数据恢复binlog

binlog命令

1. 查看binlog是否开启命令

mysql> show VARIABLES like '%log_bin%';
+---------------------------------+-------------------------------------------------+
| Variable_name                   | Value                                           |
+---------------------------------+-------------------------------------------------+
| log_bin                         | ON                                              |
| log_bin_basename                | D:\dev\mysql-5.7.34-winx64\data\mysql-bin       |
| log_bin_index                   | D:\dev\mysql-5.7.34-winx64\data\mysql-bin.index |
| log_bin_trust_function_creators | OFF                                             |
| log_bin_use_v1_row_events       | OFF                                             |
| sql_log_bin                     | ON                                              |
+---------------------------------+-------------------------------------------------+
6 rows in set, 1 warning (0.01 sec)

2. 查看binlog格式命令

mysql> show VARIABLES like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
1 row in set, 1 warning (0.00 sec)

3. 查看binlog位置

mysql> show VARIABLES like 'datadir';
+---------------+----------------------------------+
| Variable_name | Value                            |
+---------------+----------------------------------+
| datadir       | D:\dev\mysql-5.7.34-winx64\data\ |
+---------------+----------------------------------+
1 row in set, 1 warning (0.00 sec)

4. 查看正在写入的binlog

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000036 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

数据准备

1. 创建数据

# 建库
CREATE DATABASE `binlog_test`

# 建表
CREATE TABLE `user` (
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
	`age` INT(11) NULL DEFAULT NULL,
	`created_time` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
	PRIMARY KEY (`id`) USING BTREE
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB;

# 插入数据
INSERT INTO USER (name, age)
VALUES ('用户1', 18),('用户2', 20),('用户3', 19),('用户4', 19);

2. 删除数据

# 查看原有数据
mysql> select * from user;
+----+---------+------+---------------------+
| id | name    | age  | created_time        |
+----+---------+------+---------------------+
|  1 | 用户1   |   18 | 2022-01-18 11:55:41 |
|  2 | 用户2   |   20 | 2022-01-18 11:55:41 |
|  3 | 用户3   |   19 | 2022-01-18 11:55:41 |
|  4 | 用户4   |   19 | 2022-01-18 11:55:41 |
+----+---------+------+---------------------+
4 rows in set (0.00 sec)

# 删除数据
mysql> delete from user where id=3;
Query OK, 1 row affected (0.01 sec)

数据恢复

1. 查看binlog

# 需要先cd到binlog所在的目录再执行以下语句，如本例中 cd D:\dev\mysql-5.7.34-winx64\data
mysqlbinlog --no-defaults --base64-output=decode-rows -v --start-datetime "2022-01-18 11:00:00" --stop-datetime="2022-01-18 13:00:00"  --database binlog_test mysql-bin.000036 > D:/abel/binglog_raw.sql

• --no-defaults 读取没有选项的文件, 指定的原因是由于 mysqlbinlog 无法识别 BINLOG 中的 default-character-set=utf8 指令
• --base64-output=decode-rows 将 BINLOG 语句中事件以 base-64 的编码显示，对一些二进制的内容进行屏蔽
• --database 仅仅列出配置的数据库信息

2. 以上导出binglog文件binglog_raw.sql部分内容如下

3. 从上图可以看出binlog清楚记录了建库建表以及数据录入和删除的记录

• at 表示事件的开始位置
• 在at底下的一行 #220118 11:44:54 server id 1表示事件开始的时间

4. 现在我们尝试恢复被删除的记录（id=3）

mysqlbinlog --start-position "1102" --stop-position "1312" --database=binlog_test mysql-bin.000036 | mysql -u root -p db_name
# 最后-p后面的db_name是数据库名非密码，回车后才会要求输入密码