mysql如何利用binlog进行数据恢复详解

下面我将为您详细讲解“MySQL如何利用binlog进行数据恢复”的完整攻略。

什么是binlog

binlog即二进制日志(Binary Log)，记录MySQL服务器中执行的所有修改操作(如insert、update、delete等)。binlog是MySQL复制和数据恢复中最重要的部分之一。

数据恢复需求

当MySQL数据库中的数据遭到误删除、误更改或磁盘损坏等意外情况导致数据丢失时，需要进行数据恢复。而binlog记录了MySQL服务器的所有修改操作，所以可以利用binlog来还原出误删或误更改的数据。

如何利用binlog进行数据恢复

利用binlog进行数据恢复需要分为以下几个步骤：

1. 找到binlog所在目录

MySQL默认情况下会将binlog存放在数据目录下的binlog文件夹中，可以通过以下命令查看binlog所在目录：

SHOW VARIABLES LIKE '%log_bin%'

2. 找到操作记录所在的binlog文件

使用以下命令查看当前所有binlog文件及其保存路径：

SHOW MASTER STATUS;

可以得到类似以下结果：

+------------------+-----------+--------------+------------------+-------------------+
| File             | Position  | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+-----------+--------------+------------------+-------------------+
| mysql-bin.000003 | 552958340 |              |                  |                   |
+------------------+-----------+--------------+------------------+-------------------+

其中，File为binlog文件名，Position为操作记录偏移量。

3. 再次确认误操作需要恢复的记录

可以使用以下命令查看指定时间段内所有的修改操作(如删除操作)：

mysqlbinlog --start-datetime="2021-05-01 00:00:00" --stop-datetime="2021-05-01 23:59:59" mysql-bin.000003

4. 生成修改操作的SQL语句

使用以下命令根据操作记录生成SQL语句

mysqlbinlog --start-position=552958340 --stop-position=552959500 mysql-bin.000003 > revert.sql

其中，start-position和stop-position为操作记录的起始偏移量和结束偏移量。

5. 将SQL语句导入到MySQL中

使用以下命令将SQL语句导入到MySQL中

mysql -u root -p < revert.sql

示例说明

假设有一位用户误删了名为mydb的数据库中的student表，现在需要利用binlog进行数据恢复。

示例一

1. 查看binlog所在目录，假设目录为/var/log/mysql/binlog。

SHOW VARIABLES LIKE '%log_bin%'

结果如下：

+---------------+---------------------------+
| Variable_name | Value                     |
+---------------+---------------------------+
| log_bin       | /var/log/mysql/binlog/ |
+---------------+---------------------------+
1 row in set (0.00 sec)

1. 找到操作记录所在的binlog文件，假设文件名为mysql-bin.000003，记录偏移量为552958340。

SHOW MASTER STATUS;

结果如下：

+------------------+-----------+--------------+------------------+-------------------+
| File             | Position  | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+-----------+--------------+------------------+-------------------+
| mysql-bin.000003 | 552958340 |              |                  |                   |
+------------------+-----------+--------------+------------------+-------------------+

1. 再次确认误操作需要恢复的记录。使用以下命令查看指定时间段内的所有操作：

mysqlbinlog --start-datetime="2021-05-01 00:00:00" --stop-datetime="2021-05-01 23:59:59" /var/log/mysql/binlog/mysql-bin.000003

假设查询结果中有以下记录(只保留关键部分)：

### DELETE FROM `mydb`.`student`
### WHERE
###   @1='1' COLLATE utf8_general_ci
###   @2='张三' COLLATE utf8_general_ci
###   @3='男' COLLATE utf8_general_ci
###   @4='18' COLLATE utf8_general_ci
### ;

### at 552958340

1. 生成SQL语句。

mysqlbinlog --start-position=552958340 --stop-position=552959500 /var/log/mysql/binlog/mysql-bin.000003 > student_revert.sql

生成的student_revert.sql中包含删除student表的操作语句：

#210501  9:56:47 server id 1  end_log_pos 552959500 Delete_rows: table id 7 flags: STMT_END_F

BINLOG '
i8WDXw8AAAAAkAAAAEoAAAALAAAAMTAwNjQzMTAyNzk2ODU2MDQwMBQAAAAdAAZzdHVkZW50'
+'JAAAAAAAAABRAQM=',
BINLOG '
i8+lXw8AAAAAkAAAAEYAAAALAAAAMTAwNjQzMTAyNzk2ODU2MDQwMBQAAAAIAAQJzdHVkZW50'
+'AJMCAAAAAAABRAQM=',
...
DELETE FROM `mydb`.`student`
 WHERE
   @1='1' COLLATE utf8_general_ci
   @2='张三' COLLATE utf8_general_ci
   @3='男' COLLATE utf8_general_ci
   @4='18' COLLATE utf8_general_ci
;
### at 552958340
...

1. 导入SQL语句。

使用以下命令将SQL导入到MySQL中，重新创建student表，并将误删除的记录再次插入进去：

mysql -u root -p < student_revert.sql

示例二

如果误删的是单个记录，需要根据删除记录的唯一标识符再次生成SQL语句。

以删除id=1的记录为例，记录如下：

### DELETE FROM `mydb`.`student`
### WHERE
###   @1='1' COLLATE utf8_general_ci
### LIMIT 1
###;

### at 552972618

则需要使用以下命令生成SQL语句：

mysqlbinlog --start-position=552972618 --stop-position=552973009 /var/log/mysql/binlog/mysql-bin.000003 > student1_revert.sql

生成的student1_revert.sql中包含删除id=1学生记录的操作语句：

#210501  9:58:30 server id 1  end_log_pos 552973009 Delete_rows: table id 7 flags: STMT_END_F

BINLOG '
jM9BXg8BAAAAkgAAAEkAAAABAAAAAAAQk9UWQ==',
BINLOG '
jM9BXw8BAAAAkgAAAJcAAAABAAAAAAAQk9UWQ==',
...
DELETE FROM `mydb`.`student`
 WHERE
   @1='1' COLLATE utf8_general_ci
 LIMIT 1
;
### at 552972618
...

假如之前的记录中包含server-id，可以增加以下参数进行过滤：

--read-from-remote-server --stop-never --host=<mysql_slave_ip> --port=<mysql_slave_port> , --exclude-server-id=<server-id>

以上就是基本的MySQL利用binlog进行数据恢复攻略，希望对您有帮助。