MySQL误删除（误更新）恢复

最新推荐文章于 2023-09-29 11:28:56 发布

冰阔落_Louis

最新推荐文章于 2023-09-29 11:28:56 发布

阅读量1.7k

点赞数

文章标签： mysql 数据库

本文链接：https://blog.csdn.net/qq_29431123/article/details/130685611

版权

一、前言

二、基于mysqlbinlog工具的误更新恢复

三、基于binlog2sql的误更新恢复

一、前言

上周五，应用侧反应一个表在update时由于使用了左外连接。被误更新，导致全表数据丢失。反馈问题时间是上午11：30，误更新时间是10：20左右，此MYSQL数据库业务量较大，一小时的binlog日志量在2G左右。

解决思路：首先需要对这个表进行锁表，避免再有其他的更新，增加恢复量。然后开始考虑使用mysqlbinlog对日志筛选、转换、回滚语句，但是将近4G的binlog需要处理，工作量巨大。后来听同事说有专门的binlog解析工具binlog2sql，基于Python编写的。于是网上搜索安装包，安装的过程中又遇到了一些权限和依赖的问题。好在最后成功安装了binlog2sql，生成了该表的全部回滚语句成功进行了恢复。但是由于之前没有Mysql误更新恢复的经验，第一次安装binlog2sql也遇到很多坑，导致核心业务表在误更新后下午18：00才恢复，造成业务长时间中断。于是梳理处理过程、模拟案例，希望下次遇到同样问题能缩短恢复时间。

二、基于mysqlbinlog工具的误更新恢复

1、测试环境

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.26 |
+-----------+
1 row in set (0.00 sec)

mysql>

2、测试数据

mysql> select count(*) from test;
+----------+
| count(*) |
+----------+
| 10000 |
+----------+
1 row in set (0.05 sec)

mysql>

3、模拟误更新

mysql> show master status;
+-----------------+----------+--------------+------------------+--------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------------+----------+--------------+------------------+--------------------------------------------+
| mysqlbin.000001 | 156 | | | d83239cd-ce27-11ec-b08e-080027e2598e:1-186 |
+-----------------+----------+--------------+------------------+--------------------------------------------+
1 row in set (0.00 sec)

mysql> flush logs; --先切换下binlog
Query OK, 0 rows affected (0.01 sec)

mysql> show master status;
+-----------------+----------+--------------+------------------+--------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------------+----------+--------------+------------------+--------------------------------------------+
| mysqlbin.000002 | 156 | | | d83239cd-ce27-11ec-b08e-080027e2598e:1-186 |
+-----------------+----------+--------------+------------------+--------------------------------------------+
1 row in set (0.00 sec)

mysql>
mysql> update employees.test set salary=20000; --这里不会写左连接的更新，就用忘写where条件的误更新模拟
Query OK, 10000 rows affected (0.09 sec)
Rows matched: 10000 Changed: 10000 Warnings: 0

4、锁表

mysql> lock table employees.test read; --先锁表
Query OK, 0 rows affected (0.00 sec)

mysql>

5、筛选binlog中的update记录

[root@test2 log]# mysqlbinlog --no-defaults -v -v --base64-output=DECODE-ROWS mysqlbin.000002 | grep 'UPDATE `employees`.`test`' | more | wc -l

20000

[root@test2 log]# mysqlbinlog --no-defaults -v -v --base64-output=DECODE-ROWS mysqlbin.000002 | grep -C 10 'UPDATE `employees`.`test`' | more
# at 262646
#220613 9:25:59 server id 11 end_log_pos 270842 CRC32 0x0f4fcdb0 Update_rows: table id 81
# at 270842
#220613 9:25:59 server id 11 end_log_pos 279038 CRC32 0x57ac6088 Update_rows: table id 81
# at 279038
#220613 9:25:59 server id 11 end_log_pos 287234 CRC32 0x4c698df0 Update_rows: table id 81
# at 287234
#220613 9:25:59 server id 11 end_log_pos 295430 CRC32 0xc1e70954 Update_rows: table id 81
# at 295430
#220613 9:25:59 server id 11 end_log_pos 301706 CRC32 0xea6a387f Update_rows: table id 81 flags: STMT_END_F
### UPDATE `employees`.`test`
### WHERE
### @1=10001 /* INT meta=0 nullable=0 is_null=0 */
### @2=60117 /* INT meta=0 nullable=0 is_null=0 */
### @3='1986:06:26' /* DATE meta=0 nullable=0 is_null=0 */
### @4='1987:06:26' /* DATE meta=0 nullable=0 is_null=0 */
### SET
### @1=10001 /* INT meta=0 nullable=0 is_null=0 */
### @2=20000 /* INT meta=0 nullable=0 is_null=0 */
### @3='1986:06:26' /* DATE meta=0 nullable=0 is_null=0 */
### @4='1987:06:26' /* DATE meta=0 nullable=0 is_null=0 */
### UPDATE `employees`.`test`
### WHERE
### @1=10001 /* INT meta=0 nullable=0 is_null=0 */
### @2=62102 /* INT meta=0 nullable=0 is_null=0 */
### @3='1987:06:26' /* DATE meta=0 nullable=0 is_null=0 */
### @4='1988:06:25' /* DATE meta=0 nullable=0 is_null=0 */
### SET
### @1=10001 /* INT meta=0 nullable=0 is_null=0 */
### @2=20000 /* INT meta=0 nullable=0 is_null=0 */
### @3='1987:06:26' /* DATE meta=0 nullable=0 is_null=0 */
### @4='1988:06:25' /* DATE meta=0 nullable=0 is_null=0 */
### UPDATE `employees`.`test`
### WHERE
。。。。

如果误更新后立马发现了，立即对该表进行锁表，没有其他的更新，则可以使用基于事务的，及根据binlog里面的更新开始前的‘# at 295430’ 关键字和事务结束的COMMIT关键字截取筛选
如果误更新后一段时间后发现了，这期间又有很多其他的更新，需要倒叙的方式根据‘# at xxx’ 依次回滚
这两种情况对于没有切换binlog日志的误更新很合适，如果误更新后没有立即发现，然后对误更新的表进行了很多其他的更新，而且还切换了binlog日志，这种情况就都不合适了，可以使用下面的第二种方法，binlog2sql的分析工具，自动生成所有的回滚语句。

由于我们在误更新后换进行了其他的更新所以需要使用基于表的。

6、对筛选出来的误更新记录进行处理

sed '/WHERE/{:a;N;/SET/!ba;s/$[^\n]*$\n$.*$\n$.*$/\3\n\2\n\1/}' update2.sql | sed 's/### //g;s/\/\*.*/,/g' | sed /@4/s/,//g | sed '/WHERE/{:a;N;/@4/!ba;s/,/AND/g};s/#.*//g;s/COMMIT,//g' | sed '/^$/d' > rollback.sql
--这一步是把update语句提取出来，去掉多余的标识符这里面的@4要替换成该表的最后一列，一般表有几列就是@几
[root@test2 log]# more rollback.sql
UPDATE `employees`.`test`
SET
@1=10001 ,
@2=60117 ,
@3='1986:06:26' ,
@4='1987:06:26'
WHERE
@1=10001 AND
@2=20000 AND
@3='1986:06:26' AND
@4='1987:06:26'
UPDATE `employees`.`test`
SET
@1=10001 ,
@2=62102 ,
@3='1987:06:26' ,
@4='1988:06:25'
WHERE
@1=10001 AND
@2=20000 AND
@3='1987:06:26' AND
@4='1988:06:25'
UPDATE `employees`.`test`

[root@test2 log]# sed -i -r '/WHERE/{:a;N;/@4/!ba;s/(@4=.*)/\1\;/g}' rollback.sql --这一步是在where后面加;,切割update语句。
[root@test2 log]# more rollback.sql
UPDATE `employees`.`test`
SET
@1=10001 ,
@2=60117 ,
@3='1986:06:26' ,
@4='1987:06:26'
WHERE
@1=10001 AND
@2=20000 AND
@3='1986:06:26' AND
@4='1987:06:26' ;
UPDATE `employees`.`test`
SET
@1=10001 ,
@2=62102 ,
@3='1987:06:26' ,
@4='1988:06:25'
WHERE
@1=10001 AND
@2=20000 AND
@3='1987:06:26' AND
@4='1988:06:25' ;

[root@test2 log]# sed -i 's/@1/emp_no/g;s/@2/salary/g;s/@3/from_date/g;s/@4/to_date/g' rollback.sql --这一步是把@1、@2、@3等替换成列名
[root@test2 log]# more rollback.sql
UPDATE `employees`.`test`
SET
emp_no=10001 ,
salary=60117 ,
from_date='1986:06:26' ,
to_date='1987:06:26'
WHERE
emp_no=10001 AND
salary=20000 AND
from_date='1986:06:26' AND
to_date='1987:06:26' ;
UPDATE `employees`.`test`
SET
emp_no=10001 ,
salary=62102 ,
from_date='1987:06:26' ,
to_date='1988:06:25'
WHERE
emp_no=10001 AND
salary=20000 AND

7、解锁表

mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

8、执行回滚语句

mysql> source /data2/3301/log/rollback.sql
Query OK, 0 rows affected (0.01 sec)
Rows matched: 0 Changed: 0 Warnings: 0

Query OK, 0 rows affected (0.01 sec)
Rows matched: 0 Changed: 0 Warnings: 0

Query OK, 0 rows affected (0.01 sec)
Rows matched: 0 Changed: 0 Warnings: 0
。。。。。

上述方法比较适合对表的一条或几条数据的误更新，如果是全表的误更新，更新后没有其他的更新，也合适。对于全表的误更新，误更新后还有其他的更新，数量较少还能勉强恢复。如果误更新后有多次其他的更新操作，或者切换了binlog日志，这种方法就不适合了，只能使用binlog2sql工具

三、基于binlog2sql的误更新恢复

1、安装binlog2mysql
1）该工具的使用依赖以下三个包：
PyMySQL==0.7.8
wheel==0.24.0
mysql-replication==0.9
其中，每个包又会依赖其它包，所以安装这些包是一个比较麻烦的事情。
2）外网环境安装
可直接通过pip install安装，它会自动下载并安装依赖包的。
2.1.1 安装binlog2sql前先安装git和pip：
yum -y install epel-release
yum -y install git python-pip
2.1.2 安装binlog2sql：
git clone https://github.com/danfengcao/binlog2sql.git && cd binlog2sql
pip install -r requirements.txt
3）内网环境安装
可手动安装这些包，目前，这些包已下载打包，并上传到百度云盘中，大家可自行下载。
链接：https://pan.baidu.com/s/1lC3tEB27JSWAL3-ukr19oA
提取码：ange
安装步骤：
# tar xvf binlog2sql.tar.gz
# cd binlog2sql/binlog2sql_dependencies/
# tar xvf setuptools-0.6c11.tar.gz
# cd setuptools-0.6c11
# python setup.py install
# cd ..
# tar xvf pip-9.0.1.tar.gz
# cd pip-9.0.1
# python setup.py install
# cd ..
# pip install *.whl mysql-replication-0.9.tar.gz
4）安装遇到的问题

（1）权限问题，如果mysql是用root用户安装的，那么安装这些包使用root用户不会有问题，如果用mysql安装，则需要给mysql用户sudo的权限。
（2）mysql-replication包的版本问题，我的环境是mysql8.0 使用mysql-replication-0.9的版本已经不兼容了，于是下载了最新的mysql-replication。下载地址：https://pypi.org/project/mysql-replication/#files

2、模拟误删除

mysql> update test set salary=20000; --误更新
Query OK, 500 rows affected (0.04 sec)
Rows matched: 500 Changed: 500 Warnings: 0

mysql> update test set salary=salary+100; --误更新后的其他更新
Query OK, 500 rows affected (0.01 sec)
Rows matched: 500 Changed: 500 Warnings: 0

mysql> show master status;
+-----------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------------+----------+--------------+------------------+------------------------------------------+
| mysqlbin.000002 | 30854 | | | d83239cd-ce27-11ec-b08e-080027e2598e:1-4 |
+-----------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)

mysql> flush logs;
Query OK, 0 rows affected (0.06 sec)

mysql> flush logs; --切换了两次binlog
Query OK, 0 rows affected (0.03 sec)

mysql> show master status;
+-----------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------------+----------+--------------+------------------+------------------------------------------+
| mysqlbin.000004 | 196 | | | d83239cd-ce27-11ec-b08e-080027e2598e:1-4 |
+-----------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)

mysql>

3、生成恢复语句

建立恢复用户，并赋予权限

create user binlog2sql@'%' identified by 'binlog2sql';
create user binlog2sql@'localhost' identified by 'binlog2sql';
GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO binlog2sql@'%';
GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO binlog2sql@'localhost';
flush privileges;

python binlog2sql.py -ubinlog2sql -pbinlog2sql -ddatabase_name -t table1 table2 --start-file='mysql-bin.000007'
--start-datetime='2019-12-xx 06:00:00' --stop-datetime='2019-12-xx 11:30:00' >/tmp/binlog.txt

python binlog2sql.py --flashback -h127.0.0.1 -P 3306 -ubinlog2sql -pbinlog2sql -ddatabase_name -t table1 table2
--start-file='mysql-bin.000007' --start-position=763 --stop-position=1147 >/tmp/rollback.txt

binlog2sql有很多参数可以选。可以基于时间筛选、可以基于binlog文件筛选、可以基于postition筛选等等。

cd /soft/binlog2sql/binlog2sql
生成redo语句
python binlog2sql.py -ubinlog2sql -pbinlog2sql -demployees -ttest --start-file='mysqlbin.000002' --stop-file='mysqlbin.000004' >/data2/3301/log/binlog.txt
生成回滚语句
python binlog2sql.py --flashback -h127.0.0.1 -P3306 -ubinlog2sql -pbinlog2sql -demployees -ttest --start-file='mysqlbin.000002' --stop-file='mysqlbin.000004' >/data2/3301/log/rollback3.sql

生成成回滚语句时还遇到下面的错误：
:
File "binlog2sql.py", line 124, in <module>
only_tables=args.tables, nopk=args.nopk, flashback=args.flashback, stopnever=args.stopnever)
File "binlog2sql.py", line 38, in __init__
self.connection = pymysql.connect(**self.connectionSettings)
File "/usr/lib/python2.7/site-packages/pymysql/__init__.py", line 90, in Connect
return Connection(*args, **kwargs)
File "/usr/lib/python2.7/site-packages/pymysql/connections.py", line 690, in __init__
self.connect()
File "/usr/lib/python2.7/site-packages/pymysql/connections.py", line 939, in connect
raise exc
pymysql.err.OperationalError: (2003, "Can't connect to MySQL server on '127.0.0.1' ([Errno 111] Connection refused)")

是因为binlog2mysql默认的端口是3306，如果mysql使用了非默认端口，需要特殊指定

注意，执行binlog2sql时会访问msyql的数据字典，因此数据库必须是运行状态

4、执行恢复语句

mysql> source /data2/3301/log/rollback3.sql

冰阔落_Louis

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
打赏
1
评论
MySQL误删除（误更新）恢复

上周五，应用侧反应一个表在update时由于使用了左外连接。被误更新，导致全表数据丢失。反馈问题时间是上午11：30，误更新时间是10：20左右，此MYSQL数据库业务量较大，一小时的binlog日志量在2G左右。解决思路：首先需要对这个表进行锁表，避免再有其他的更新，增加恢复量。然后开始考虑使用mysqlbinlog对日志筛选、转换、回滚语句，但是将近4G的binlog需要处理，工作量巨大。后来听同事说有专门的binlog解析工具binlog2sql，基于Python编写的。
复制链接

扫一扫