【MySQL 5.7】通过binlog2sql恢复事务对表的错误操作

原创
dbprofessional 2021-01-14 15:10:52 博主文章分类：MySQL ©著作权
文章标签 mysql 文章分类 MySQL 数据库
©著作权归作者所有：来自51CTO博客作者dbprofessional的原创作品，谢绝转载，否则将追究法律责任
CentOS 7.5默认已安装python 2.7版本
[root@node01 ~]# cat /etc/redhat-release 
CentOS Linux release 7.5.1804 (Core) 

[root@node01 ~]# python -V
Python 2.7.5

通过yum安装python包管理工具
[root@node01 ~]# yum install -y python-pip

[root@node01 ~]# pip -V
pip 8.1.2 from /usr/lib/python2.7/site-packages (python 2.7)

通过git克隆项目代码到本地目录
[root@node01 ~]# git clone https://github.com/danfengcao/binlog2sql.git
Initialized empty Git repository in /root/binlog2sql/.git/
remote: Enumerating objects: 323, done.
remote: Total 323 (delta 0), reused 0 (delta 0), pack-reused 323
Receiving objects: 100% (323/323), 151.51 KiB | 245 KiB/s, done.
Resolving deltas: 100% (170/170), done.

通过pip安装binlog2sql依赖的包
[root@node01 ~]# cd binlog2sql
[root@node01 binlog2sql]# pip install -r requirements.txt
[root@node01 binlog2sql]# cd binlog2sql/
[root@node01 binlog2sql]# python binlog2sql.py --help
usage: binlog2sql.py [-h HOST] [-u USER] [-p [PASSWORD [PASSWORD ...]]]
                     [-P PORT] [--start-file START_FILE]
                     [--start-position START_POS] [--stop-file END_FILE]
                     [--stop-position END_POS] [--start-datetime START_TIME]
                     [--stop-datetime STOP_TIME] [--stop-never] [--help]
                     [-d [DATABASES [DATABASES ...]]]
                     [-t [TABLES [TABLES ...]]] [--only-dml]
                     [--sql-type [SQL_TYPE [SQL_TYPE ...]]] [-K] [-B]
                     [--back-interval BACK_INTERVAL]
                     
--start-file --起始解析文件
--stop-file  --终止解析文件
--start-position --起始解析位置
--stop-position  --终止解析位置
--start-datetime --起始解析时间，格式'%Y-%m-%d %H:%M:%S'。
--stop-datetime --终止解析时间，格式'%Y-%m-%d %H:%M:%S'。
-d --仅解析目标db的sql
-t --仅解析目标table的sql
--only-dml --仅解析dml，忽略ddl。
--sql-type --仅解析指定类型，支持insert，update，delete。
-B --生成回滚SQL

编辑数据库参数文件，修改必要的参数
[root@node01 ~]# vim /etc/my.cnf
[mysqld]
server_id = 101
log_bin = mysql-bin
max_binlog_size = 128M
binlog_format = row  
binlog_row_image = full  当binlog使用行格式时,full将记录update前后所有字段的值
binlog_rows_query_log_events = on #当binlog使用行格式时，将sql语句打印到binlog文件

[root@node01 ~]$ mysql -uroot -pabcd.1234 -hnode01 -D tpcc10
(root@node01) > select count(*) from customer;
+----------+
| count(*) |
+----------+
|   300000 |
+----------+
1 row in set (0.12 sec)

(root@node01) > delete from customer where c_state='jp'; #模拟表记录被误删除
Query OK, 319 rows affected (0.66 sec)

(root@node01) > select count(*) from customer;
+----------+
| count(*) |
+----------+
|   299681 |
+----------+
1 row in set (0.10 sec)

(root@localhost) > show master status;  #当前的binlog文件与位置
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000005 | 22258614 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

定位delete语句在binlog文件中的开始位置：22081259
(root@node01) > pager grep -i -B 5 "delete from customer where c_state='jp'"
PAGER set to 'grep -i -B 5 "delete from customer where c_state='jp'"'

(root@node01) > show binlog events in 'mysql-bin.000005';
| mysql-bin.000005 | 22080008 | Table_map      |       101 |    22080111 | table_id: 109 (tpcc10.customer)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
| mysql-bin.000005 | 22080111 | Update_rows    |       101 |    22081163 | table_id: 109 flags: STMT_END_F                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
| mysql-bin.000005 | 22081163 | Xid            |       101 |    22081194 | COMMIT /* xid=98563 */                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   |
| mysql-bin.000005 | 22081194 | Anonymous_Gtid |       101 |    22081259 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
| mysql-bin.000005 | 22081259 | Query          |       101 |    22081333 | BEGIN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    |
| mysql-bin.000005 | 22081333 | Rows_query     |       101 |    22081396 | # delete from customer where c_state='jp'                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                |
401910 rows in set (0.57 sec)
                                                                                                                                                 
定位delete语句在binlog文件中的结束位置：22258614
(root@node01) > pager grep -m 1 -B 5 "COMMIT";
PAGER set to 'grep -m 1 -B 5 "COMMIT"'
                                                                                                                                                                                                      
(root@node01) > show binlog events in 'mysql-bin.000005' from 22081259;
| mysql-bin.000005 | 22223439 | Delete_rows    |       101 |    22231380 | table_id: 109                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |
| mysql-bin.000005 | 22231380 | Delete_rows    |       101 |    22239195 | table_id: 109                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |
| mysql-bin.000005 | 22239195 | Delete_rows    |       101 |    22247354 | table_id: 109                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |
| mysql-bin.000005 | 22247354 | Delete_rows    |       101 |    22255231 | table_id: 109                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |
| mysql-bin.000005 | 22255231 | Delete_rows    |       101 |    22258583 | table_id: 109 flags: STMT_END_F                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
| mysql-bin.000005 | 22258583 | Xid            |       101 |    22258614 | COMMIT /* xid=98710 */                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   |
284976 rows in set (0.41 sec)
                                                                                
#通过binlog2sql解析binlog文件生成回滚语句
[root@node01 ~]# cd binlog2sql/binlog2sql/
[root@node01 binlog2sql]# python binlog2sql.py -hlocalhost -P3306 -uroot -pabcd.1234 -dtpcc10 -tcustomer \
--start-file='mysql-bin.000005' --only-dml --sql-type=delete --start-position=22081259 --stop-position=22258614 -B > /tmp/rollback.sql

#执行回滚语句，恢复被误删除的表记录
[root@node01 ~]# mysql -uroot -pabcd.1234 < /tmp/rollback.sql

#表记录恢复成功
[root@node01 ~]# mysql -uroot -pabcd.1234 -hnode01 -D tpcc10
(root@node01) > select count(*) from customer;
+----------+
| count(*) |
+----------+
|   300000 |
+----------+
1 row in set (0.11 sec)