|
说明：恢复大表的数据误操作，binlog_format模式必须是ROW模式，可以使用binlog2sql工具
1、下载binlog2sql及安装
git clone https://github.com/danfengcao/binlog2sql.git
[root@db01 binlog2sql]# pwd
/root/binlog2sql
[root@db01 binlog2sql]# ls
binlog2sql example LICENSE README.md requirements.txt tests
安装python3环境：
yum install python3
修改requirements.txt：
[root@db01 binlog2sql]# cat requirements.txt
PyMySQL==0.7.11
wheel==0.29.0
mysql-replication==0.13
安装依赖：
把PyMySQL==0.7.11修改为：PyMySQL==0.9.3
pip3 install -r requirements.txt
pip3 show pymysql
可选：
连接mysql8.0后，升级pymysql至最新版本，上一步修改了就不用执行了
升级最新版本：
pip3 install --upgrade PyMySQL
2、准备测试数据，并解析
1、导入world库
[root@db01 ~]# mysql -uroot -p123456 < world.sql
查询city表中河南省的数据：
db01 [world]>select * from city where District='henan';
+------+--------------+-------------+----------+------------+
| ID | Name | CountryCode | District | Population |
+------+--------------+-------------+----------+------------+
| 1906 | Zhengzhou | CHN | Henan | 2107200 |
| 1934 | Luoyang | CHN | Henan | 760000 |
| 1951 | Kaifeng | CHN | Henan | 510000 |
| 1957 | Xinxiang | CHN | Henan | 473762 |
| 1967 | Anyang | CHN | Henan | 420332 |
| 1969 | Pingdingshan | CHN | Henan | 410775 |
| 1972 | Jiaozuo | CHN | Henan | 409100 |
| 2036 | Nanyang | CHN | Henan | 243303 |
| 2056 | Hebi | CHN | Henan | 212976 |
| 2060 | Xuchang | CHN | Henan | 208815 |
| 2076 | Xinyang | CHN | Henan | 192509 |
| 2102 | Puyang | CHN | Henan | 175988 |
| 2113 | Shangqiu | CHN | Henan | 164880 |
| 2140 | Zhoukou | CHN | Henan | 146288 |
| 2179 | Luohe | CHN | Henan | 126438 |
| 2186 | Zhumadian | CHN | Henan | 123232 |
| 2189 | Sanmenxia | CHN | Henan | 120523 |
| 2242 | Yuzhou | CHN | Henan | 92889 |
+------+--------------+-------------+----------+------------+
2、模拟误操作，将所有的henan的城市名修改为zhengzhou
db01 [world]>select * from city where District='henan';
+------+-----------+-------------+----------+------------+
| ID | Name | CountryCode | District | Population |
+------+-----------+-------------+----------+------------+
| 1906 | zhengzhou | CHN | Henan | 2107200 |
| 1934 | zhengzhou | CHN | Henan | 760000 |
| 1951 | zhengzhou | CHN | Henan | 510000 |
| 1957 | zhengzhou | CHN | Henan | 473762 |
| 1967 | zhengzhou | CHN | Henan | 420332 |
| 1969 | zhengzhou | CHN | Henan | 410775 |
| 1972 | zhengzhou | CHN | Henan | 409100 |
| 2036 | zhengzhou | CHN | Henan | 243303 |
| 2056 | zhengzhou | CHN | Henan | 212976 |
| 2060 | zhengzhou | CHN | Henan | 208815 |
| 2076 | zhengzhou | CHN | Henan | 192509 |
| 2102 | zhengzhou | CHN | Henan | 175988 |
| 2113 | zhengzhou | CHN | Henan | 164880 |
| 2140 | zhengzhou | CHN | Henan | 146288 |
| 2179 | zhengzhou | CHN | Henan | 126438 |
| 2186 | zhengzhou | CHN | Henan | 123232 |
| 2189 | zhengzhou | CHN | Henan | 120523 |
| 2242 | zhengzhou | CHN | Henan | 92889 |
+------+-----------+-------------+----------+------------+
3、查询所使用的binlog日志
db01 [world]>show master status;
+---------------+----------+--------------+------------------+----------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+----------------------------------------+
| binlog.000001 | 1464 | | | a13376a5-b313-11ed-9570-000c2949ba59:1 |
+---------------+----------+--------------+------------------+----------------------------------------+
db01 [world]>show binlog events in 'binlog.000001';
+---------------+------+----------------+-----------+-------------+-------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+---------------+------+----------------+-----------+-------------+-------------------------------------------------------------------+
| binlog.000001 | 4 | Format_desc | 1 | 125 | Server ver: 8.0.20, Binlog ver: 4 |
| binlog.000001 | 125 | Previous_gtids | 1 | 156 | |
| binlog.000001 | 156 | Gtid | 1 | 235 | SET @@SESSION.GTID_NEXT= 'a13376a5-b313-11ed-9570-000c2949ba59:1' |
| binlog.000001 | 235 | Query | 1 | 320 | BEGIN |
| binlog.000001 | 320 | Table_map | 1 | 384 | table_id: 116 (world.city) |
| binlog.000001 | 384 | Update_rows | 1 | 1433 | table_id: 116 flags: STMT_END_F |
| binlog.000001 | 1433 | Xid | 1 | 1464 | COMMIT /* xid=5464 */ |
+---------------+------+----------------+-----------+-------------+-------------------------------------------------------------------+
4、使用binlog2sql解析18行数据
注意：此处有坑，要想连接binlog2sql，必须新建一个用户，修改密码插件为mysql_native_password
db01 [(none)]>create user root@'192.168.0.%' identified with mysql_native_password by '123456';
db01 [(none)]>grant all on *.* to root@'192.168.0.%';
解析刚才误操作的数据：
参数--sql-type，只针对某种DML操作进行过滤，两种类型都查询的话用空格分开：如：--sql-type update delete：
[root@db01 binlog2sql]# python3 binlog2sql.py -uroot -p123456 -d world -t city --start-file='binlog.000001' --sql-type update
取其中的一条，可以看到luoyang被误修改为了zhengzhou
UPDATE `world`.`city` SET `ID`=1934, `Name`='zhengzhou', `CountryCode`='CHN', `District`='Henan', `Population`=760000 WHERE `ID`=1934 AND `Name`='Luoyang' AND `CountryCode`='CHN' AND `District`='Henan' AND `Population`=760000 LIMIT 1; #start 235 end 1433 time 2023-02-23 13:53:40
5、查询广东省的数据，并且把广东省所有的城市数据删除
db01 [world]>select * from city where District='guangdong';
+------+--------------------+-------------+-----------+------------+
| ID | Name | CountryCode | District | Population |
+------+--------------------+-------------+-----------+------------+
| 1897 | Kanton [Guangzhou] | CHN | Guangdong | 4256300 |
| 1926 | Shenzhen | CHN | Guangdong | 950500 |
| 1943 | Shantou | CHN | Guangdong | 580000 |
| 1974 | Zhangjiang | CHN | Guangdong | 400997 |
| 1993 | Shaoguan | CHN | Guangdong | 350043 |
| 2002 | Chaozhou | CHN | Guangdong | 313469 |
| 2004 | Dongwan | CHN | Guangdong | 308669 |
| 2006 | Foshan | CHN | Guangdong | 303160 |
| 2018 | Zhongshan | CHN | Guangdong | 278829 |
| 2042 | Jiangmen | CHN | Guangdong | 230587 |
| 2052 | Yangjiang | CHN | Guangdong | 215196 |
| 2072 | Zhaoqing | CHN | Guangdong | 194784 |
| 2098 | Maoming | CHN | Guangdong | 178683 |
| 2114 | Zhuhai | CHN | Guangdong | 164747 |
| 2115 | Qingyuan | CHN | Guangdong | 164641 |
| 2122 | Huizhou | CHN | Guangdong | 161023 |
| 2168 | Meixian | CHN | Guangdong | 132156 |
| 2190 | Heyuan | CHN | Guangdong | 120101 |
| 2213 | Shanwei | CHN | Guangdong | 107847 |
| 2234 | Jieyang | CHN | Guangdong | 98531 |
+------+--------------------+-------------+-----------+------------+
删除数据：
db01 [world]>delete from city where District='Guangdong';
查询数据已经空了：
db01 [world]>select * from city where District='guangdong';
Empty set (0.00 sec)
解析删除广东省城市的语句：
[root@db01 binlog2sql]# python3 binlog2sql.py -uroot -p123456 -d world -t city --start-file='binlog.000001' --sql-type delete
取其中一条：
DELETE FROM `world`.`city` WHERE `ID`=2115 AND `Name`='Qingyuan' AND `CountryCode`='CHN' AND `District`='Guangdong' AND `Population`=164641 LIMIT 1; #start 2698 end 3514 time 2023-02-23 14:58:33
3、回滚数据
1、恢复删除的广东省的城市数据：
解析出的条目，后边有事务的开始和结束位置，可以选择加以反转操作
#start 2698 end 3514 time 2023-02-23 14:58:33
[root@db01 binlog2sql]# python3 binlog2sql.py -uroot -p123456 -d world -t city --start-file='binlog.000001' --sql-type=delete --start-position=2698 --stop-position=3514 -B
参数 -B就是flashback闪回
delete语句生成了insert into语句：
INSERT INTO `world`.`city`(`ID`, `Name`, `CountryCode`, `District`, `Population`) VALUES (2234, 'Jieyang', 'CHN', 'Guangdong', 98531); #start 2698 end 3514 time 2023-02-23 14:58:33
回滚两种办法：
一、直接执行生成的语句
二、导出到文件，进入mysql中进行恢复
这里我选用第二种方法：
[root@db01 binlog2sql]# python3 binlog2sql.py -uroot -p123456 -d world -t city --start-file='binlog.000001' --sql-type=delete --start-position=2698 --stop-position=3514 -B > /root/city_delete.sql
db01 [(none)]>set sql_log_bin=0;
db01 [(none)]>use world;
db01 [world]>source /root/city_delete.sql
db01 [(none)]>set sql_log_bin=1;
db01 [world]>commit;
查询广东省的数据，都回来了
db01 [world]>select * from city where District='guangdong';
2、闪回河南省的城市数据
先解析
[root@db01 binlog2sql]# python3 binlog2sql.py -uroot -p123456 -d world -t city --start-file='binlog.000001' --sql-type UPDATE
找到开始点和结束点：
#start 235 end 1433 time 2023-02-23 13:53:40
update语句生成了新的update语句
[root@db01 binlog2sql]# python3 binlog2sql.py -uroot -p123456 -d world -t city --start-file='binlog.000001' --sql-type=update --start-position=235 --stop-position=1433 -B > /root/city_update.sql
db01 [(none)]>set sql_log_bin=0;
db01 [(none)]>use world;
db01 [world]>source /root/city_update.sql
db01 [(none)]>set sql_log_bin=1;
查询数据都回来了
db01 [world]>select * from city where District='henan';
db01 [world]>commit;
切记：生产环境恢复之后，一定要commit提交，才算更改完成。
6、基于远程的使用
例如：我的mysql服务器是192.168.0.51，我使用远程主机连接
远程访问，加上-h -P参数
[root@db02 binlog2sql]# python3 binlog2sql.py -h 192.168.0.51 -P3306 -uroot -p123456 -d world -t city --start-file='binlog.000001'
[root@db02 binlog2sql]# python3 binlog2sql.py -h 192.168.0.51 -P3306 -uroot -p123456 -d world -t city --start-file='binlog.000001' --sql-type=update --start-position=15935 --stop-position=16178 -B > /root/city_update.sql
|