通过binlog恢复mysql数据

通过binlog恢复mysql数据

基本流程

一、确定要恢复的时间点

确定要恢复到的时间点，可以使用以下命令查看binlog相关信息；或者根据误操作的时间来决定要恢复的时间点。

# 查询 BINLOG 格式show VARIABLES like'binlog_format';# 查询 BINLOG 位置show VARIABLES like'datadir';# 查询当前数据库中 BINLOG 名称及大小showbinary logs;# 查看 master 正在写入的 BINLOG 信息show master status\G;# 通过 offset 查看 BINLOG 信息show BINLOG events in'mysql-bin.000034'limit9000,10;# 通过 position 查看 binlog 信息show BINLOG events in'mysql-bin.000034'from1742635limit10;

标准操作为：根据时间确定位置信息
例：

 mysqlbinlog --no-defaults --base64-output=decode-rows -v \--start-datetime  "2023-04-03 14:00:00" \--database beifen  mysql-bin.000002 | less

逐一分析找到对应的start-position和stop-position

二、临时库准备

主库执行flush logs，然后将相关binlog和最近一份全量备份文件拷贝至临时库,并在临时库导入最近一次全量备份文件。

在执行数据恢复前，如果操作的是生产环境，会有如下的建议：
使用 flush logs 命令，替换当前主库中正在使用的 binlog 文件，好处如下：

1. 可将误删操作，定位在一个 BINLOG 文件中，便于之后的数据分析和恢复。
2. 避免操作正在被使用的 BINLOG 文件，防止发生意外情况。

数据的恢复不要在生产库中执行，先在临时库恢复，确认无误后，再倒回生产库。防止对数据的二次伤害。

三、提取sql

根据步骤一确定的位置导出 SQL 文件，相关命令：

1. 根据position

mysqlbinlog --no-defaults --base64-output=decode-rows -v \
 --start-position "xxxxx" --stop-position "xxxxx"\
 --database=beifen  binlog_file \> /HLW/JSZX/HLWdata/xxxx.sql

其中， start_position 和 stop_position 是在步骤1中记录的binlog位置，binlog_file 是在步骤1中记录的binlog文件名，/HLW/JSZX/HLWdata/xxxx.sql是保存SQL语句的文件路径。

1. 根据时间

mysqlbinlog --no-defaults --base64-output=decode-rows -v \
 --start-datetime="2023-04-03 17:00:00" --stop-datetime="2023-04-03 19:00:00"\
 --database=beifen  mysql-bin.000002 \> /HLW/JSZX/HLWdata/xxxx.sql

1. 其它查看方式

• less & more & grep

# less
mysqlbinlog --no-defaults --base64-output=decode-rows -v \
 --start-datetime  "2023-04-03 14:00:00"\
 --database sync_test  mysql-bin.000034 |less# more grep
mysqlbinlog --no-defaults --database=jiangbei_construction \
--start-datetime="2023-04-03 14:00:00"\
/var/lib/mysql/mysql-bin.000044 |grep jb_purchase_enroll |more

• mysqlbinlog 常用参数说明： - –database 仅仅列出配置的数据库信息- –no-defaults 读取没有选项的文件, 指定的原因是由于 mysqlbinlog 无法识别 BINLOG 中的 default-- character-set=utf8 指令- –offset 跳过 log 中 N 个条目- –verbose 将日志信息重建为原始的 SQL 陈述。 - -v 仅仅解释行信息- -vv 不但解释行信息，还将 SQL 列类型的注释信息也解析出来- –start-datetime 显示从指定的时间或之后的时间的事件。 - 接收 DATETIME 或者 TIMESTRAMP 格式。- –base64-output=decode-rows 将 BINLOG 语句中事件以 base-64 的编码显示，对一些二进制的内容进行屏蔽。 - AUTO 默认参数，自动显示 BINLOG 中的必要的语句- NEVER 不会显示任何的 BINLOG 语句，如果遇到必须显示的 BINLOG 语言，则会报错退出。- DECODE-ROWS 显示通过 -v 显示出来的 SQL 信息，过滤到一些 BINLOG 二进制数据。

四、执行SQL语句

将步骤3中保存的SQL语句文件复制到恢复后的MariaDB服务器上，并使用以下命令将SQL语句导入到数据库中：

mysql -u root -p < /path/to/output/file.sql

五、实际演练

演练环境

• IP：10.3.1.14
• 数据库版本：mysql 5.7.37
• 数据库名：beifen
• 表名：test
• 初始化数据：

mysql>select*from test;+----+--------+------+--------+| id | name   | age  | salary |+----+--------+------+--------+|2| 李四   |18|1.00||3| 王五   |18|1.00|+----+--------+------+--------+2rowsinset(0.02 sec)

• 上一次备份

[root@rccccc JSZX]# /HLW/JSZX/mysql/bin/mysqldump  -uroot -p -S /HLW/JSZX/mysql/mysql.sock  beifen test > t_test.sql[root@rccccc JSZX]# ls -l |grep t_test.sql
-rw-r--r-- 1 root  root  2009 Apr  314:38 t_test.sql

模拟数据变动

写入两条数据（时间点一）

mysql>insertinto test(id,name,age,salary)values(4,'阿瑟',19,2);
Query OK,1row affected (0.04 sec)
mysql>insertinto test(id,name,age,salary)values(5,'赵六',20,3);
Query OK,1row affected (0.02 sec)
mysql>select*from test;+----+-----------+------+--------+| id | name      | age  | salary |+----+-----------+------+--------+|2| 李四      |18|1.00||3| 王五      |18|1.00||4| 阿瑟东    |19|2.00||5| 赵六      |20|3.00|+----+-----------+------+--------+

更新表某字段值（时间点二）

mysql>update test set age=99where salary <>9;
Query OK,4rows affected (0.11 sec)Rowsmatched: 4  Changed: 4Warnings: 0
mysql>select*from test;+----+-----------+------+--------+| id | name      | age  | salary |+----+-----------+------+--------+|2| 李四      |99|1.00||3| 王五      |99|1.00||4| 阿瑟东    |99|2.00||5| 赵六      |99|3.00|+----+-----------+------+--------+4rowsinset(0.01 sec)

删除数据（时间点三）

mysql>deletefrom test where id=2;
Query OK,1row affected (0.02 sec)
mysql>select*from test;+----+-----------+------+--------+| id | name      | age  | salary |+----+-----------+------+--------+|3| 王五      |99|1.00||4| 阿瑟东    |99|2.00||5| 赵六      |99|3.00|+----+-----------+------+--------+3rowsinset(0.00 sec)

按需恢复

1. 查看当前binlog信息

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 |1777||||
+------------------+----------+--------------+------------------+-------------------+
1 row inset(0.00 sec)

1. 查看日志事件

mysql>show binlog events in'mysql-bin.000002';+------------------+------+----------------+-----------+-------------+-------------------------------------------------------------------------------+| Log_name         | Pos  | Event_type     | Server_id | End_log_pos | Info                                                                          |+------------------+------+----------------+-----------+-------------+-------------------------------------------------------------------------------+| mysql-bin.000002|4| Format_desc    |1|123| Server ver: 5.7.37-log, Binlog ver: 4|| mysql-bin.000002|123| Previous_gtids |1|154||| mysql-bin.000002|154| Anonymous_Gtid |1|219|SET @@SESSION.GTID_NEXT='ANONYMOUS'|| mysql-bin.000002|219| Query          |1|307|BEGIN|| mysql-bin.000002|307| Query          |1|453|use`beifen`;insertinto test(id,name,age,salary)values(4,'阿瑟东',19,2)|| mysql-bin.000002|453| Xid            |1|484|COMMIT/* xid=273 */|| mysql-bin.000002|484| Anonymous_Gtid |1|549|SET @@SESSION.GTID_NEXT='ANONYMOUS'|| mysql-bin.000002|549| Query          |1|637|BEGIN|| mysql-bin.000002|637| Query          |1|780|use`beifen`;insertinto test(id,name,age,salary)values(5,'赵六',20,3)|| mysql-bin.000002|780| Xid            |1|811|COMMIT/* xid=274 */|| mysql-bin.000002|811| Anonymous_Gtid |1|876|SET @@SESSION.GTID_NEXT='ANONYMOUS'|| mysql-bin.000002|876| Query          |1|964|BEGIN|| mysql-bin.000002|964| Query          |1|1088|use`beifen`;update  test set age='99'where age ='5'|| mysql-bin.000002|1088| Query          |1|1177|COMMIT|| mysql-bin.000002|1177| Anonymous_Gtid |1|1242|SET @@SESSION.GTID_NEXT='ANONYMOUS'|| mysql-bin.000002|1242| Query          |1|1330|BEGIN|| mysql-bin.000002|1330| Query          |1|1452|use`beifen`;update test set age=99where salary <>9|| mysql-bin.000002|1452| Xid            |1|1483|COMMIT/* xid=284 */|| mysql-bin.000002|1483| Anonymous_Gtid |1|1548|SET @@SESSION.GTID_NEXT='ANONYMOUS'|| mysql-bin.000002|1548| Query          |1|1636|BEGIN|| mysql-bin.000002|1636| Query          |1|1746|use`beifen`;deletefrom test where id=2|| mysql-bin.000002|1746| Xid            |1|1777|COMMIT/* xid=286 */|+------------------+------+----------------+-----------+-------------+-------------------------------------------------------------------------------+22rowsinset(0.00 sec)

可以很明显的看到相关操作记录。

一、恢复到【写入两条数据（时间点一）】

根据日志事件，可以得到该时间点的 start-position=154 stop-position=811

1. 导出对应binlog日志

/HLW/JSZX/mysql/bin/mysqlbinlog   /HLW/JSZX/HLWdata/mysql-bin.000002 --start-position 154 --stop-position 811> t_s_test.sql

1. 恢复定期全量备份

mysql -u root -p beifen < t_test.sql

1. 通过 BINLOG导出的sql进行恢复数据到【写入两条数据（时间点一）】

mysql -u root -p beifen < t_s_test.sql

1. 登录数据库，验证数据是否按需恢复

mysql>select*from test;+----+-----------+------+--------+| id | name      | age  | salary |+----+-----------+------+--------+|2| 李四      |18|1.00||3| 王五      |18|1.00||4| 阿瑟东    |19|2.00||5| 赵六      |20|3.00|+----+-----------+------+--------+4rowsinset(0.00 sec)

二、恢复到【更新表某字段值（时间点二）】

根据日志事件，可以得到该时间点的 start-position=811 stop-position=1483

1. 导出对应binlog日志

/HLW/JSZX/mysql/bin/mysqlbinlog  --no-defaults --database=beifen  /HLW/JSZX/HLWdata/mysql-bin.000002 --start-position 811 --stop-position 1483> t_s2_test.sql

1. 通过BINLOG导出的sql进行恢复数据到【更新表某字段值（时间点二）】

mysql -u root -p beifen < t_s2_test.sql

1. 登录数据库，验证数据是否按需恢复

mysql>select*from test;+----+-----------+------+--------+| id | name      | age  | salary |+----+-----------+------+--------+|2| 李四      |99|1.00||3| 王五      |99|1.00||4| 阿瑟东    |99|2.00||5| 赵六      |99|3.00|+----+-----------+------+--------+4rowsinset(0.01 sec)

总结

恢复主要有两个步骤：

1. 在临时库中，恢复定期执行的全量备份数据；
2. 然后基于全量备份的数据点，通过 BINLOG 来恢复误操作和正常的数据.

衍生分析：

1. binlog单个存储不要太大，会影响分析效率；
2. 在全量备份定时任务脚本中，最好加上 flush logs，按天存储binlog日志，方便应急恢复。