记录一次生产事故MYSQL执行语句错误，回滚数据

MYSQL执行语句错误，使用binlog回滚数据：

早上接到 leader 通知，说有个开发不小心将数据库的一张表数据全量使用update语句更新错误了，由于之前的备份数据是一周之前的，与用户使用存在偏差，因此需要采用mysql-binlog恢复这张表数据 ~

---

问题描述

首先，因为使用原来一周前的数据库备份数据，用户使用会有偏差，因此不能使用原来的备份数据进行恢复，那么，采取binlog的方式恢复。

因为不确定服务器上面的mysql 是否开启了binlog 以及 它使用的模式，我们先进行一次binlog相关信息的查询。
1.确保数据库开启了log_bin

show variables  like 'log_bin';

+-------------+-----+
|Variable_name|Value|
+-------------+-----+
|log_bin      |ON   |
+-------------+-----+

2.查看binlog 的模式类型

show global variables like “%binlog_format%”;
+-------------+---------+
|Variable_name|  Value  |
+-------------+---------+
|binlog_format|STATEMENT|
+-------------+---------+

好的，由此可以确定服务器该mysql库已经开启了binlog日志，且使用的是STATEMENT模式。
在这里有必要说明一下binlog 三种模式的区别(问题排查可以略过此章节)。

binlog三种日志模式区别：

ROW Level

记录的方式是行，即如果批量修改数据，记录的不是批量修改的SQL语句事件，而是每条记录被更改的SQL语句，因此，ROW模式的binlog日志文件会变得很“重”。(存每一条改动sql，数据量大)

优点：row level的binlog日志内容会非常清楚的记录下每一行数据被修改的细节。而且不会出现某些特定情况下存储过程或function，以及trigger的调用和触发器无法被正确复制的问题。

缺点：row level下，所有执行的语句当记录到日志中的时候，都以每行记录的修改来记录，这样可能会产生大量的日志内容，产生的binlog日志量是惊人的。批量修改几百万条数据，那么记录几百万行……

Statement level(默认)

记录每一条修改数据的SQL语句（批量修改时，记录的不是单条SQL语句，而是批量修改的SQL语句事件）。看上面的图解可以很好的理解row level和statement level两种模式的区别。

优点：statement模式记录的更改的SQ语句事件，并非每条更改记录，所以大大减少了binlog日志量，节约磁盘IO，提高性能。

缺点：statement level下对一些特殊功能的复制效果不是很好，比如：函数、存储过程的复制。由于row level是基于每一行的变化来记录的，所以不会出现类似问题

Mixed

实际上就是前两种模式的结合。在Mixed模式下，MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式，也就是在Statement和Row之间选择一种。

---

找到错误sql：

接下来，我们需要在服务器上面找到对应的binlog日志。

mysql> show master status;

+----------------+---------+------------+----------------+-----------------+
|File            |Position |Binlog_Do_DB|Binlog_Ignore_DB|Executed_Gtid_Set|
+----------------+---------+------------+----------------+-----------------+
|mysql-bin.000150|182954971|            |                |                 |
+----------------+---------+------------+----------------+-----------------+

我们可以看到，最新的binlog日志应该是150 ，于是我们上服务器找到对应mysql存放目录。
由于我再my.cnf文件中没有额外配置存放目录，因此binlog就在/var/lib/mysql下面。

cd /var/lib/mysql
ll
total 157584044
---略---
-rw-rw---- 1 mysql mysql 1074201869 Sep 15 00:30 mysql-bin.000148
-rw-rw---- 1 mysql mysql 1074201893 Sep 15 00:30 mysql-bin.000149
-rw-rw---- 1 mysql mysql 1074201352 Sep 15 00:30 mysql-bin.000150

这个文件理论上可以直接打开，但是vim 过后发现是乱码情况。
查阅后知道，mysqlbinlog使用的编码有bug问题，因此，只能使用mysql本身的命令。
和对应的开发对接后，发现执行命令失误的时间为2022-09-16 10点左右，然后进行排查。

mysqlbinlog --no-defaults --database=bp_wms --start-datetime='2022-09-16 10:44:20' --stop-datetime='2022-09-16 10:44:30' mysql-bin.000150

命令说明:
–no-defaults 表示使用mysql 的编码格式进行查看
–database指定库名
–start-datetime及–stop-datetime指定开始时间及结束时间
最后接mysql-bin查看日志。

以下是查询结果内容(已做数据脱敏处理):

---略---
/*!*/;
# at 542053108
#220916 10:44:24 server id 3306 end_log_pos 542054167 CRC32 0x0a71be01    Query thread_id=170164 exec_time=1 error_code=0
use `bp_wms` /*!*/;
SET TIMESTAMP=1663296264 /*!*/;
update `RECEIVE_OUT` set CREATE_USER='xx.xxx',UPDATE_USER='xx.xxx' where 1=1;
/*!*/;
# at 542054167
---略---

由此，我们可以确定当时出现错误的语句，找到问题所在，update没有加条件执行了。

解决问题并恢复数据：

我们计划恢复数据，先将一周前的备份数据重新存库，新建库表，然后使用命令， 将一周时间到错误sql日志之前的所有sql命令全部执行一遍。尽管该办法比较笨，但是确实是一个恢复的手段，目前我们能想到的办法也只有这样了。

1.新建库表，执行一周前的数据存储

grep "RECEIVE_OUT" bp_wms.sql > RECEIVE_OUT.sql

仅仅将该表数据进行获取，然后再你新建的库中执行sql。
2.再根据时间节点，将一周前到至今的binlog sql日志获取
观察之后发现有三个binlog日志，需要进行恢复。

mysqlbinlog --no-defaults --database=bp_wms --start-datetime='2022-09-11 00:00:00' --stop-datetime='2022-09-16 10:44:30' mysql-bin.000148 > /home/rollback/binlog148.sql

mysqlbinlog --no-defaults --database=bp_wms --start-datetime='2022-09-11 00:00:00' --stop-datetime='2022-09-16 10:44:30' mysql-bin.000149 > /home/rollback/binlog149.sql

mysqlbinlog --no-defaults --database=bp_wms --start-datetime='2022-09-11 00:00:00' --stop-datetime='2022-09-16 10:44:30' mysql-bin.000150 > /home/rollback/binlog150.sql

依次将三个日志的内容分别存储，再筛选出RECEIVE_OUT表的所有数据

grep "RECEIVE_OUT" binlog148.sql >/home/rollback/sql/SQL.148.sql
grep "RECEIVE_OUT" binlog149.sql >/home/rollback/sql/SQL.149.sql
grep "RECEIVE_OUT" binlog150.sql >/home/rollback/sql/SQL.150.sql

3.处理sql 日志，将里面的sql 每条sql最后添加; 使它能够执行。
4.最终执行sql 并将数据恢复成功。