【DB宝43】MySQL误操作闪回恢复利器之my2sql（中）

MySQL [lhrdb]> show binlog events in 'mysql3306-bin.000004';
+----------------------+------+----------------+-----------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Log_name             | Pos  | Event_type     | Server_id | End_log_pos | Info                                                                                                                                                                                                                                                                                                                                                           |
+----------------------+------+----------------+-----------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| mysql3306-bin.000004 |    4 | Format_desc    |    573306 |         123 | Server ver: 5.7.30-log, Binlog ver: 4                                                                                                                                                                                                                                                                                                                          |
| mysql3306-bin.000004 |  123 | Previous_gtids |    573306 |         154 |                                                                                                                                                                                                                                                                                                                                                                |
| mysql3306-bin.000004 |  154 | Anonymous_Gtid |    573306 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                                                                                                                                                                                                                                                                                           |
| mysql3306-bin.000004 |  219 | Query          |    573306 |         634 | use `lhrdb`; CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `number` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `add_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '添加的时间',
  `content` json DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_name` (`number`,`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
| mysql3306-bin.000004 |  634 | Anonymous_Gtid |    573306 |         699 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                                                                                                                                                                                                                                                                                           |
| mysql3306-bin.000004 |  699 | Query          |    573306 |         780 | BEGIN                                                                                                                                                                                                                                                                                                                                                          |
| mysql3306-bin.000004 |  780 | Table_map      |    573306 |         839 | table_id: 108 (lhrdb.student)                                                                                                                                                                                                                                                                                                                                  |
| mysql3306-bin.000004 |  839 | Write_rows     |    573306 |         895 | table_id: 108 flags: STMT_END_F                                                                                                                                                                                                                                                                                                                                |
| mysql3306-bin.000004 |  895 | Xid            |    573306 |         926 | COMMIT /* xid=13 */                                                                                                                                                                                                                                                                                                                                            |
| mysql3306-bin.000004 |  926 | Anonymous_Gtid |    573306 |         991 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                                                                                                                                                                                                                                                                                           |
| mysql3306-bin.000004 |  991 | Query          |    573306 |        1072 | BEGIN                                                                                                                                                                                                                                                                                                                                                          |
| mysql3306-bin.000004 | 1072 | Table_map      |    573306 |        1131 | table_id: 108 (lhrdb.student)                                                                                                                                                                                                                                                                                                                                  |
| mysql3306-bin.000004 | 1131 | Write_rows     |    573306 |        1185 | table_id: 108 flags: STMT_END_F                                                                                                                                                                                                                                                                                                                                |
| mysql3306-bin.000004 | 1185 | Xid            |    573306 |        1216 | COMMIT /* xid=14 */                                                                                                                                                                                                                                                                                                                                            |
| mysql3306-bin.000004 | 1216 | Anonymous_Gtid |    573306 |        1281 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                                                                                                                                                                                                                                                                                           |
| mysql3306-bin.000004 | 1281 | Query          |    573306 |        1362 | BEGIN                                                                                                                                                                                                                                                                                                                                                          |
| mysql3306-bin.000004 | 1362 | Table_map      |    573306 |        1421 | table_id: 108 (lhrdb.student)                                                                                                                                                                                                                                                                                                                                  |
| mysql3306-bin.000004 | 1421 | Write_rows     |    573306 |        1477 | table_id: 108 flags: STMT_END_F                                                                                                                                                                                                                                                                                                                                |
| mysql3306-bin.000004 | 1477 | Xid            |    573306 |        1508 | COMMIT /* xid=15 */                                                                                                                                                                                                                                                                                                                                            |
| mysql3306-bin.000004 | 1508 | Anonymous_Gtid |    573306 |        1573 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                                                                                                                                                                                                                                                                                           |
| mysql3306-bin.000004 | 1573 | Query          |    573306 |        1654 | BEGIN                                                                                                                                                                                                                                                                                                                                                          |
| mysql3306-bin.000004 | 1654 | Table_map      |    573306 |        1713 | table_id: 108 (lhrdb.student)                                                                                                                                                                                                                                                                                                                                  |
| mysql3306-bin.000004 | 1713 | Write_rows     |    573306 |        1769 | table_id: 108 flags: STMT_END_F                                                                                                                                                                                                                                                                                                                                |
| mysql3306-bin.000004 | 1769 | Xid            |    573306 |        1800 | COMMIT /* xid=16 */                                                                                                                                                                                                                                                                                                                                            |
| mysql3306-bin.000004 | 1800 | Anonymous_Gtid |    573306 |        1865 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                                                                                                                                                                                                                                                                                           |
| mysql3306-bin.000004 | 1865 | Query          |    573306 |        1946 | BEGIN                                                                                                                                                                                                                                                                                                                                                          |
| mysql3306-bin.000004 | 1946 | Table_map      |    573306 |        2005 | table_id: 108 (lhrdb.student)                                                                                                                                                                                                                                                                                                                                  |
| mysql3306-bin.000004 | 2005 | Write_rows     |    573306 |        2100 | table_id: 108 flags: STMT_END_F                                                                                                                                                                                                                                                                                                                                |
| mysql3306-bin.000004 | 2100 | Xid            |    573306 |        2131 | COMMIT /* xid=17 */                                                                                                                                                                                                                                                                                                                                            |
+----------------------+------+----------------+-----------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
29 rows in set (0.05 sec)

8.3 解析binlog生成标准SQL

可以根据时间点解析出标准SQL：

my2sql  -user root -password lhr  -port 3306 \
-host 192.168.66.35 -databases lhrdb  -tables student \
-work-type 2sql   -start-file mysql3306-bin.000004 \
-start-datetime "2021-02-26 12:22:38" --stop-datetime "2021-02-26 12:23:16" \
-output-dir /my2sql/

也可以根据binlog的pos点解析出标准SQL：

my2sql  -user root -password lhr  -port 3306 \
-host 192.168.66.35 -databases lhrdb  -tables student \
-work-type 2sql   -start-file mysql3306-bin.000004 \
-start-pos 154 -stop-file  mysql3306-bin.000004 -stop-pos  2131 \
-output-dir /my2sql/

执行过程：

[root@lhrmy2sql my2sql]# my2sql  -user root -password lhr  -port 3306 \
> -host 192.168.66.35 -databases lhrdb  -tables student \
> -work-type 2sql   -start-file mysql3306-bin.000004 \
> -start-pos 154 -stop-file  mysql3306-bin.000004 -stop-pos  2131 \
> -output-dir /my2sql/
[2021/02/26 12:27:42] [info] events.go:208 start thread to write redo/rollback sql into file
[2021/02/26 12:27:42] [info] binlogsyncer.go:144 create BinlogSyncer with config {1113306 mysql 192.168.66.35 3306 root   utf8 false false <nil> false Local false 0 0s 0s 0 false false 0}
[2021/02/26 12:27:42] [info] binlogsyncer.go:360 begin to sync binlog from position (mysql3306-bin.000004, 154)
[2021/02/26 12:27:42] [info] events.go:58 start thread 2 to generate redo/rollback sql
[2021/02/26 12:27:42] [info] events.go:58 start thread 1 to generate redo/rollback sql
[2021/02/26 12:27:42] [info] stats_process.go:166 start thread to analyze statistics from binlog
[2021/02/26 12:27:42] [info] repl.go:15 start to get binlog from mysql
[2021/02/26 12:27:42] [info] binlogsyncer.go:777 rotate to (mysql3306-bin.000004, 154)
[2021/02/26 12:27:42] [info] com.go:57 stop to get event. StopFilePos set. currentBinlog (mysql3306-bin.000004, 2131) StopFilePos (mysql3306-bin.000004, 2131)
[2021/02/26 12:27:42] [info] repl.go:17 finish getting binlog from mysql
[2021/02/26 12:27:42] [info] events.go:183 exit thread 1 to generate redo/rollback sql
[2021/02/26 12:27:42] [info] stats_process.go:266 exit thread to analyze statistics from binlog
[2021/02/26 12:27:42] [info] events.go:183 exit thread 2 to generate redo/rollback sql
[2021/02/26 12:27:42] [info] events.go:272 finish writing redo/forward sql into file
[2021/02/26 12:27:42] [info] events.go:275 exit thread to write redo/rollback sql into file
[root@lhrmy2sql my2sql]# ll
total 12
-rw-r--r-- 1 root root 107 Feb 26 12:27 biglong_trx.txt
-rw-r--r-- 1 root root 291 Feb 26 12:27 binlog_status.txt
-rw-r--r-- 1 root root 671 Feb 26 12:27 forward.4.sql
[root@lhrmy2sql my2sql]# more biglong_trx.txt 
binlog            starttime           stoptime            startpos   stoppos    rows     duration   tables
[root@lhrmy2sql my2sql]# more binlog_status.txt 
binlog            starttime           stoptime            startpos   stoppos    inserts  updates  deletes  database        table               
mysql3306-bin.000004 2021-02-26_12:23:06 2021-02-26_12:23:06 780        2100       5        0        0        lhrdb           student             
[root@lhrmy2sql my2sql]# more forward.4.sql 
INSERT INTO `lhrdb`.`student` (`id`,`number`,`name`,`add_time`,`content`) VALUES (1233,26,'ranran','2020-07-15 19:06:03',null);
INSERT INTO `lhrdb`.`student` (`id`,`number`,`name`,`add_time`,`content`) VALUES (1232,134,'asdf','2020-07-12 11:08:41',null);
INSERT INTO `lhrdb`.`student` (`id`,`number`,`name`,`add_time`,`content`) VALUES (1231,21,'chenxi','2020-07-12 10:12:45',null);
INSERT INTO `lhrdb`.`student` (`id`,`number`,`name`,`add_time`,`content`) VALUES (1229,20,'chenxi','2020-07-11 16:20:50',null);
INSERT INTO `lhrdb`.`student` (`id`,`number`,`name`,`add_time`,`content`) VALUES (1227,18,'hanran','2020-07-06 21:55:48','{\"age\":13,\"author\":\"liuhan\"}');

可以看到，原始的SQL插入语句已经被解析出来了。