[toc]

# 一、my2sql簡介
可以用於MySQL誤操作閃回的工具包括my2sql、binlog2sql和MyFlash等工具，其中，個人感覺my2sql最好用。

![](https://gitee.com/lhrbest/pic/raw/master/img/20210226142725.png)

my2sql是使用go語言開發的MySQL binlog解析工具，透過解析MySQL binlog ，可以生成原始SQL、回滾SQL、去除主鍵的INSERT SQL等，也可以生成DML統計資訊。類似工具有binlog2sql、MyFlash、my2fback等，本工具基於my2fback、binlog_rollback工具二次開發而來。

my2sql的GitHub地址：https://github.com/liuhr/my2sql

**優點：**

- 功能豐富，不僅支援回滾操作，還有其他實用功能。
- 基於golang實現，速度快，全量解析1.1Gbinlog只需要1分30秒左右，當前其他類似開源工具一般要幾十分鐘。

# 二、my2sql用途
* 資料快速回滾(閃回)
* 主從切換後新master丟資料的修復
* 從binlog生成標準SQL，帶來的衍生功能
* 生成DML統計資訊，可以找到哪些表更新的比較頻繁
* IO高TPS高， 查出哪些表在頻繁更新
* 找出某個時間點資料庫是否有大事務或者長事務
* 主從延遲，分析主庫執行的SQL語句
* 除了支援常規資料型別，對大部分工具不支援的資料型別做了支援，比如json、blob、text、emoji等資料型別sql生成

# 三、產品效能對比
binlog2sql當前是業界使用最廣泛的MySQL回滾工具，下面對my2sql和binlog2sql做個效能對比。

|                          |my2sql     |binlog2sql|
|---                       |---         |---   |
|1.1G binlog生成回滾SQL      |  1分40秒   |    65分鐘  |
|1.1G binlog生成原始SQL      |  1分30秒   |     50分鐘|
|1.1G binlog生成表DML統計資訊、以及事務統計資訊     |   40秒     |不支援|

# 四、安裝
```sql
-- 申請一臺全新的主機
docker rm -f lhrmy2sql
docker run -d --name lhrmy2sql -h lhrmy2sql \
  --privileged=true lhrbest/lhrcentos76:8.0 \
  /usr/sbin/init

docker exec -it lhrmy2sql bash

-- 方法1：編譯安裝
yum install -y golang
go version
go env | grep GOPATH

mkdir -p /root/go/src
cd /root/go/src
git clone https://github.com/liuhr/my2sql.git
cd my2sql/
go build .

-- 方法2：直接下載編譯好的二進位制檔案。
-- https://github.com/liuhr/my2sql/blob/master/releases/my2sql
wget https://raw.githubusercontent.com/liuhr/my2sql/master/releases/my2sql
mv my2sql /usr/local/bin/my2sql
chmod +x /usr/local/bin/my2sql

-- 若不能下載，請新增以下解析：
echo "
13.229.188.59 github.com
199.232.4.133 raw.githubusercontent.com
" >> /etc/hosts
```

# 五、重要引數說明
> - -U	優先使用unique key作為where條件，預設false
>
> - -add-extraInfo 是否把database/table/datetime/binlogposition...資訊以註釋的方式加入生成的每條sql前，預設false
>
> ```sql
> datetime=2020-07-16_10:44:09 database=orchestrator table=cluster_domain_name binlog=mysql-bin.011519 startpos=15552 stoppos=15773
> 
> UPDATE `orchestrator`.`cluster_domain_name` SET `last_registered`='2020-07-16 10:44:09' WHERE `cluster_name`='192.168.1.1:3306'
> ```
>
> - -big-trx-row-limit n
>   transaction with affected rows greater or equal to this value is considerated as big transaction 
>   找出影響了n行資料的事務，預設500條
>
> - -databases 、 -tables 庫及表條件過濾, 以逗號分隔
>
> - -sql 要解析的sql型別，可選引數insert、update、delete，預設全部解析
>
> - -doNotAddPrifixDb
>   Prefix table name witch database name in sql,ex: insert into db1.tb1 (x1, x1) values (y1, y1)
>   預設生成insert into db1.tb1 (x1, x1) values (y1, y1)類sql，也可以生成不帶庫名的sql
>
> - -file-per-table 為每個表生成一個sql檔案
>
> - -full-columns
>   For update sql, include unchanged columns. for update and delete, use all columns to build where condition.
>   default false, this is, use changed columns to build set part, use primary/unique key to build where condition
>   生成的sql是否帶全列資訊，預設false
>
> - -ignorePrimaryKeyForInsert 生成的insert語句是否去掉主鍵，預設false
>
> - -output-dir 將生成的結果存放到制定目錄
>
> - -output-toScreen 將生成的結果列印到螢幕，預設寫到檔案
>
> - -threads  執行緒數，預設8個
>
> - -work-type   2sql表示生成原始sql，rollback表示生成回滾sql，stats表示只統計DML、事務資訊

# 六、使用案例
## 6.1 解析出標準SQL
#### 根據時間點解析出標準SQL
```sql
./my2sql  -user root -password xxxx -host 127.0.0.1   -port 3306  -work-type 2sql  \
-start-file mysql-bin.011259  -start-datetime "2020-07-16 10:20:00" -stop-datetime "2020-07-16 11:00:00" \
-output-dir ./tmpdir
```

#### 根據pos點解析出標準SQL
```sql
./my2sql  -user root -password xxxx -host 127.0.0.1   -port 3306  -work-type 2sql  \
-start-file mysql-bin.011259  -start-pos 4 -stop-file mysql-bin.011259 -stop-pos 583918266  \
-output-dir ./tmpdir
```

## 6.2 解析出回滾SQL
#### 根據時間點解析出回滾SQL
```sql
./my2sql  -user root -password xxxx -host 127.0.0.1   -port 3306  -work-type rollback  \
-start-file mysql-bin.011259  -start-datetime "2020-07-16 10:20:00" -stop-datetime "2020-07-16 11:00:00" \
-output-dir ./tmpdir
```

#### 根據pos點解析出回滾SQL
```sql
./my2sql  -user root -password xxxx -host 127.0.0.1   -port 3306  -work-type rollback  \
-start-file mysql-bin.011259  -start-pos 4 -stop-file mysql-bin.011259 -stop-pos 583918266  \
-output-dir ./tmpdir
```

## 6.3 統計DML以及大事務
#### 統計時間範圍各個表的DML運算元量，統計一個事務大於500條、時間大於300秒的事務
```sql
./my2sql  -user root -password xxxx -host 127.0.0.1   -port 3306  -work-type stats  \
-start-file mysql-bin.011259  -start-datetime "2020-07-16 10:20:00" -stop-datetime "2020-07-16 11:00:00"  \
-big-trx-row-limit 500 -long-trx-seconds 300   -output-dir ./tmpdir
```

#### 統計一段pos點範圍各個表的DML運算元量，統計一個事務大於500條、時間大於300秒的事務
```sql
./my2sql  -user root -password xxxx -host 127.0.0.1   -port 3306  -work-type stats  \
-start-file mysql-bin.011259  -start-pos 4 -stop-file mysql-bin.011259 -stop-pos 583918266  \
-big-trx-row-limit 500 -long-trx-seconds 300   -output-dir ./tmpdir
```

## 6.4 從某一個pos點解析出標準SQL，並且持續列印到螢幕

```sql
./my2sql  -user root -password xxxx -host 127.0.0.1   -port 3306  -work-type 2sql  \
-start-file mysql-bin.011259  -start-pos 4   -output-toScreen 
```

# 七、限制
* 使用回滾/閃回功能時，binlog格式必須為row,且binlog_row_image=full， DML統計以及大事務分析不受影響
* 只能回滾DML， 不能回滾DDL
* 支援指定-tl時區來解釋binlog中time/datetime欄位的內容。開始時間-start-datetime與結束時間-stop-datetime也會使用此指定的時區，
  但注意此開始與結束時間針對的是binlog event header中儲存的unix timestamp。結果中的額外的datetime時間資訊都是binlog event header中的unix timestamp
* 此工具是偽裝成從庫拉取binlog，需要連線資料庫的使用者有SELECT, REPLICATION SLAVE, REPLICATION CLIENT許可權
* MySQL8.0版本需要在配置檔案中加入default_authentication_plugin  =mysql_native_password，使用者密碼認證必須是mysql_native_password才能解析

# 八、案例演示

## 8.1 準備MySQL環境

```sql
docker rm -f mysql3306
docker run -d --name mysql3306 -h mysql3306 -p 3306:3306 \
  -v /etc/mysql/mysql3306/conf:/etc/mysql/conf.d \
  -e MYSQL_ROOT_PASSWORD=lhr -e TZ=Asia/Shanghai \
  mysql:5.7.30
  
  
docker exec -it mysql3306 bash
docker logs -f mysql3306

MySQL [(none)]>
MySQL [(none)]> create database lhrdb default character set utf8mb4;
Query OK, 1 row affected (0.05 sec)

MySQL [(none)]> flush logs;
Query OK, 0 rows affected (0.06 sec)

MySQL [(none)]>
MySQL [(none)]> select now();
+---------------------+
| now()               |
+---------------------+
| 2021-02-26 12:22:38 |
+---------------------+
1 row in set (0.05 sec)

MySQL [(none)]>
MySQL [(none)]> use lhrdb;
Database changed
MySQL [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;
Query OK, 0 rows affected (0.07 sec)

MySQL [lhrdb]> INSERT INTO `lhrdb`.`student` (`id`,`number`,`name`,`add_time`,`content`) VALUES (1233,26,'ranran','2020-07-15 19:06:03',null);
Query OK, 1 row affected (0.08 sec)

MySQL [lhrdb]> INSERT INTO `lhrdb`.`student` (`id`,`number`,`name`,`add_time`,`content`) VALUES (1232,134,'asdf','2020-07-12 11:08:41',null);
Query OK, 1 row affected (0.05 sec)

MySQL [lhrdb]> INSERT INTO `lhrdb`.`student` (`id`,`number`,`name`,`add_time`,`content`) VALUES (1231,21,'chenxi','2020-07-12 10:12:45',null);
Query OK, 1 row affected (0.06 sec)

MySQL [lhrdb]> INSERT INTO `lhrdb`.`student` (`id`,`number`,`name`,`add_time`,`content`) VALUES (1229,20,'chenxi','2020-07-11 16:20:50',null);
Query OK, 1 row affected (0.05 sec)

MySQL [lhrdb]> INSERT INTO `lhrdb`.`student` (`id`,`number`,`name`,`add_time`,`content`) VALUES (1227,18,'hanran','2020-07-06 21:55:48','{\"age\":13,\"author\":\"liuhan\"}');
Query OK, 1 row affected (0.05 sec)

MySQL [lhrdb]>
MySQL [lhrdb]> select * from student;
+------+--------+--------+---------------------+---------------------------------+
| id   | number | name   | add_time            | content                         |
+------+--------+--------+---------------------+---------------------------------+
| 1227 |     18 | hanran | 2020-07-06 21:55:48 | {"age": 13, "author": "liuhan"} |
| 1229 |     20 | chenxi | 2020-07-11 16:20:50 | NULL                            |
| 1231 |     21 | chenxi | 2020-07-12 10:12:45 | NULL                            |
| 1232 |    134 | asdf   | 2020-07-12 11:08:41 | NULL                            |
| 1233 |     26 | ranran | 2020-07-15 19:06:03 | NULL                            |
+------+--------+--------+---------------------+---------------------------------+
5 rows in set (0.05 sec)

MySQL [lhrdb]> select now();
+---------------------+
| now()               |
+---------------------+
| 2021-02-26 12:23:16 |
+---------------------+
1 row in set (0.05 sec)

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：

```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：

```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/
```

執行過程：

```sql
[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插入語句已經被解析出來了。

## 8.4 執行閃回操作

根據binlog的pos點解析出回滾SQL

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

執行過程：

```sql
[root@lhrmy2sql my2sql]# my2sql  -user root -password lhr  -port 3306 \
> -host 192.168.66.35 -databases lhrdb  -tables student \
> -work-type rollback   -start-file mysql3306-bin.000004 \
> -start-pos 154 -stop-file  mysql3306-bin.000004 -stop-pos  2131 \
> -output-dir /my2sql/
[2021/02/26 12:29:51] [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:29:51] [info] binlogsyncer.go:360 begin to sync binlog from position (mysql3306-bin.000004, 154)
[2021/02/26 12:29:51] [info] events.go:208 start thread to write redo/rollback sql into file
[2021/02/26 12:29:51] [info] events.go:58 start thread 2 to generate redo/rollback sql
[2021/02/26 12:29:51] [info] events.go:58 start thread 1 to generate redo/rollback sql
[2021/02/26 12:29:51] [info] stats_process.go:166 start thread to analyze statistics from binlog
[2021/02/26 12:29:51] [info] repl.go:15 start to get binlog from mysql
[2021/02/26 12:29:51] [info] binlogsyncer.go:777 rotate to (mysql3306-bin.000004, 154)
[2021/02/26 12:29:51] [info] com.go:57 stop to get event. StopFilePos set. currentBinlog (mysql3306-bin.000004, 2131) StopFilePos (mysql3306-bin.000004, 2131)
[2021/02/26 12:29:51] [info] repl.go:17 finish getting binlog from mysql
[2021/02/26 12:29:51] [info] stats_process.go:266 exit thread to analyze statistics from binlog
[2021/02/26 12:29:51] [info] events.go:183 exit thread 1 to generate redo/rollback sql
[2021/02/26 12:29:51] [info] events.go:183 exit thread 2 to generate redo/rollback sql
[2021/02/26 12:29:51] [info] events.go:257 finish writing rollback sql into tmp files, start to revert content order of tmp files
[2021/02/26 12:29:51] [info] rollback_process.go:15 start thread 1 to revert rollback sql files
[2021/02/26 12:29:51] [info] rollback_process.go:41 start to revert tmp file /my2sql/.rollback.4.sql into /my2sql/rollback.4.sql
[2021/02/26 12:29:51] [info] rollback_process.go:156 finish reverting tmp file /my2sql/.rollback.4.sql into /my2sql/rollback.4.sql
[2021/02/26 12:29:51] [info] rollback_process.go:25 exit thread 1 to revert rollback sql files
[2021/02/26 12:29:51] [info] events.go:270 finish reverting content order of tmp files
[2021/02/26 12:29:51] [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:29 biglong_trx.txt
-rw-r--r-- 1 root root 291 Feb 26 12:29 binlog_status.txt
-rw-r--r-- 1 root root 235 Feb 26 12:29 rollback.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 rollback.4.sql 
DELETE FROM `lhrdb`.`student` WHERE `id`=1227;
DELETE FROM `lhrdb`.`student` WHERE `id`=1229;
DELETE FROM `lhrdb`.`student` WHERE `id`=1231;
DELETE FROM `lhrdb`.`student` WHERE `id`=1232;
DELETE FROM `lhrdb`.`student` WHERE `id`=1233;
```

可以看到，回滾SQL是DELETE，已經生成。

要回滾該事務，則執行該SQL即可：

```sql
mysql -uroot -plhr -h192.168.66.35 -P3306  < rollback.4.sql 
```

## 8.5 解析binlog 統計DML、長事務與大事務分析

```sql
MySQL [lhrdb]> flush logs;
Query OK, 0 rows affected (0.06 sec)

-- sysbench建立表並插入資料
[root@lhrmy2sql my2sql]# sysbench /usr/share/sysbench/oltp_common.lua --time=100 --mysql-host=192.168.66.35 --mysql-port=3306 --mysql-user=root --mysql-password=lhr --mysql-db=lhrdb --table-size=10000 --tables=10 --threads=16 --events=999999999   prepare
sysbench 1.0.17 (using system LuaJIT 2.0.4)

Initializing worker threads...

Creating table 'sbtest8'...
Creating table 'sbtest4'...
Creating table 'sbtest2'...
Creating table 'sbtest7'...
Creating table 'sbtest1'...
Creating table 'sbtest5'...
Creating table 'sbtest3'...
Creating table 'sbtest10'...
Creating table 'sbtest6'...
Creating table 'sbtest9'...
Inserting 10000 records into 'sbtest2'
Inserting 10000 records into 'sbtest5'
Inserting 10000 records into 'sbtest8'
Inserting 10000 records into 'sbtest10'
Inserting 10000 records into 'sbtest7'
Inserting 10000 records into 'sbtest3'
Inserting 10000 records into 'sbtest9'
Inserting 10000 records into 'sbtest1'
Inserting 10000 records into 'sbtest6'
Inserting 10000 records into 'sbtest4'
Creating a secondary index on 'sbtest2'...
Creating a secondary index on 'sbtest5'...
Creating a secondary index on 'sbtest8'...
Creating a secondary index on 'sbtest7'...
Creating a secondary index on 'sbtest10'...
Creating a secondary index on 'sbtest3'...
Creating a secondary index on 'sbtest9'...
Creating a secondary index on 'sbtest4'...
Creating a secondary index on 'sbtest6'...
Creating a secondary index on 'sbtest1'...

```

使用my2sql進行解析：

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

執行過程：

```sql
[root@lhrmy2sql my2sql]# my2sql  -user root -password lhr  -port 3306 \
> -host 192.168.66.35 -databases lhrdb \
> -big-trx-row-limit 500 -long-trx-seconds 300 \
> -work-type stats   -start-file mysql3306-bin.000005 \
> -start-pos 154 -stop-file  mysql3306-bin.000005 -stop-pos  19097041 \
> -output-dir /my2sql/
[2021/02/26 12:44:51] [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:44:51] [info] stats_process.go:166 start thread to analyze statistics from binlog
[2021/02/26 12:44:51] [info] binlogsyncer.go:360 begin to sync binlog from position (mysql3306-bin.000005, 154)
[2021/02/26 12:44:51] [info] repl.go:15 start to get binlog from mysql
[2021/02/26 12:44:51] [info] binlogsyncer.go:777 rotate to (mysql3306-bin.000005, 154)
[2021/02/26 12:44:51] [info] com.go:57 stop to get event. StopFilePos set. currentBinlog (mysql3306-bin.000005, 19097041) StopFilePos (mysql3306-bin.000005, 19097041)
[2021/02/26 12:44:51] [info] repl.go:17 finish getting binlog from mysql
[2021/02/26 12:44:51] [info] stats_process.go:266 exit thread to analyze statistics from binlog
[root@lhrmy2sql my2sql]# 
[root@lhrmy2sql my2sql]# ll
total 12
-rw-r--r-- 1 root root 6311 Feb 26 12:44 biglong_trx.txt
-rw-r--r-- 1 root root 1614 Feb 26 12:44 binlog_status.txt
[root@lhrmy2sql my2sql]# more biglong_trx.txt 
binlog            starttime           stoptime            startpos   stoppos    rows     duration   tables
mysql3306-bin.000005 2021-02-26_12:33:44 2021-02-26_12:33:44 3710       522152     2716     0          [lhrdb.sbtest2(inserts=2716, updates=0, deletes=0)]
mysql3306-bin.000005 2021-02-26_12:33:44 2021-02-26_12:33:44 522217     1040659    2716     0          [lhrdb.sbtest5(inserts=2716, updates=0, deletes=0)]
mysql3306-bin.000005 2021-02-26_12:33:45 2021-02-26_12:33:45 1040724    1559166    2716     0          [lhrdb.sbtest2(inserts=2716, updates=0, deletes=0)]
mysql3306-bin.000005 2021-02-26_12:33:45 2021-02-26_12:33:45 1559231    2077673    2716     0          [lhrdb.sbtest5(inserts=2716, updates=0, deletes=0)]
mysql3306-bin.000005 2021-02-26_12:33:44 2021-02-26_12:33:44 2077738    2596180    2716     0          [lhrdb.sbtest8(inserts=2716, updates=0, deletes=0)]
mysql3306-bin.000005 2021-02-26_12:33:45 2021-02-26_12:33:45 2596245    3114687    2716     0          [lhrdb.sbtest2(inserts=2716, updates=0, deletes=0)]
mysql3306-bin.000005 2021-02-26_12:33:45 2021-02-26_12:33:45 3114752    3633194    2716     0          [lhrdb.sbtest8(inserts=2716, updates=0, deletes=0)]
mysql3306-bin.000005 2021-02-26_12:33:45 2021-02-26_12:33:45 3633259    4151701    2716     0          [lhrdb.sbtest5(inserts=2716, updates=0, deletes=0)]
mysql3306-bin.000005 2021-02-26_12:33:44 2021-02-26_12:33:44 4151766    4670208    2716     0          [lhrdb.sbtest7(inserts=2716, updates=0, deletes=0)]
mysql3306-bin.000005 2021-02-26_12:33:45 2021-02-26_12:33:45 4670273    5023855    1852     0          [lhrdb.sbtest2(inserts=1852, updates=0, deletes=0)]
mysql3306-bin.000005 2021-02-26_12:33:45 2021-02-26_12:33:45 5023920    5542362    2716     0          [lhrdb.sbtest8(inserts=2716, updates=0, deletes=0)]
mysql3306-bin.000005 2021-02-26_12:33:45 2021-02-26_12:33:45 5542427    5896009    1852     0          [lhrdb.sbtest5(inserts=1852, updates=0, deletes=0)]
mysql3306-bin.000005 2021-02-26_12:33:44 2021-02-26_12:33:44 5896074    6414517    2716     0          [lhrdb.sbtest10(inserts=2716, updates=0, deletes=0)]
mysql3306-bin.000005 2021-02-26_12:33:45 2021-02-26_12:33:45 6414582    6933024    2716     0          [lhrdb.sbtest7(inserts=2716, updates=0, deletes=0)]
mysql3306-bin.000005 2021-02-26_12:33:44 2021-02-26_12:33:44 6933089    7451531    2716     0          [lhrdb.sbtest1(inserts=2716, updates=0, deletes=0)]
mysql3306-bin.000005 2021-02-26_12:33:44 2021-02-26_12:33:44 7451596    7970038    2716     0          [lhrdb.sbtest3(inserts=2716, updates=0, deletes=0)]
mysql3306-bin.000005 2021-02-26_12:33:44 2021-02-26_12:33:44 7970103    8488545    2716     0          [lhrdb.sbtest9(inserts=2716, updates=0, deletes=0)]
mysql3306-bin.000005 2021-02-26_12:33:45 2021-02-26_12:33:45 8488610    8842192    1852     0          [lhrdb.sbtest8(inserts=1852, updates=0, deletes=0)]
mysql3306-bin.000005 2021-02-26_12:33:46 2021-02-26_12:33:46 8842257    9360700    2716     0          [lhrdb.sbtest10(inserts=2716, updates=0, deletes=0)]
mysql3306-bin.000005 2021-02-26_12:33:44 2021-02-26_12:33:44 9360765    9879207    2716     0          [lhrdb.sbtest6(inserts=2716, updates=0, deletes=0)]
mysql3306-bin.000005 2021-02-26_12:33:44 2021-02-26_12:33:44 9879272    10397714   2716     0          [lhrdb.sbtest4(inserts=2716, updates=0, deletes=0)]
mysql3306-bin.000005 2021-02-26_12:33:46 2021-02-26_12:33:46 10397779   10916221   2716     0          [lhrdb.sbtest7(inserts=2716, updates=0, deletes=0)]
mysql3306-bin.000005 2021-02-26_12:33:46 2021-02-26_12:33:46 10916457   11434899   2716     0          [lhrdb.sbtest3(inserts=2716, updates=0, deletes=0)]
mysql3306-bin.000005 2021-02-26_12:33:46 2021-02-26_12:33:46 11434964   11953406   2716     0          [lhrdb.sbtest1(inserts=2716, updates=0, deletes=0)]
mysql3306-bin.000005 2021-02-26_12:33:46 2021-02-26_12:33:46 11953642   12472084   2716     0          [lhrdb.sbtest9(inserts=2716, updates=0, deletes=0)]
mysql3306-bin.000005 2021-02-26_12:33:46 2021-02-26_12:33:46 12472149   12990591   2716     0          [lhrdb.sbtest4(inserts=2716, updates=0, deletes=0)]
mysql3306-bin.000005 2021-02-26_12:33:46 2021-02-26_12:33:46 12990656   13509098   2716     0          [lhrdb.sbtest6(inserts=2716, updates=0, deletes=0)]
mysql3306-bin.000005 2021-02-26_12:33:46 2021-02-26_12:33:46 13509163   14027606   2716     0          [lhrdb.sbtest10(inserts=2716, updates=0, deletes=0)]
mysql3306-bin.000005 2021-02-26_12:33:46 2021-02-26_12:33:46 14027671   14381253   1852     0          [lhrdb.sbtest7(inserts=1852, updates=0, deletes=0)]
mysql3306-bin.000005 2021-02-26_12:33:46 2021-02-26_12:33:46 14381489   14899931   2716     0          [lhrdb.sbtest3(inserts=2716, updates=0, deletes=0)]
mysql3306-bin.000005 2021-02-26_12:33:46 2021-02-26_12:33:46 14899996   15253579   1852     0          [lhrdb.sbtest10(inserts=1852, updates=0, deletes=0)]
mysql3306-bin.000005 2021-02-26_12:33:46 2021-02-26_12:33:46 15253644   15772086   2716     0          [lhrdb.sbtest9(inserts=2716, updates=0, deletes=0)]
mysql3306-bin.000005 2021-02-26_12:33:46 2021-02-26_12:33:46 15772151   16290593   2716     0          [lhrdb.sbtest6(inserts=2716, updates=0, deletes=0)]
mysql3306-bin.000005 2021-02-26_12:33:46 2021-02-26_12:33:46 16290658   16809100   2716     0          [lhrdb.sbtest4(inserts=2716, updates=0, deletes=0)]
mysql3306-bin.000005 2021-02-26_12:33:46 2021-02-26_12:33:46 16809165   17327607   2716     0          [lhrdb.sbtest1(inserts=2716, updates=0, deletes=0)]
mysql3306-bin.000005 2021-02-26_12:33:46 2021-02-26_12:33:46 17327843   17681425   1852     0          [lhrdb.sbtest3(inserts=1852, updates=0, deletes=0)]
mysql3306-bin.000005 2021-02-26_12:33:46 2021-02-26_12:33:46 17681490   18035072   1852     0          [lhrdb.sbtest9(inserts=1852, updates=0, deletes=0)]
mysql3306-bin.000005 2021-02-26_12:33:46 2021-02-26_12:33:46 18035137   18388719   1852     0          [lhrdb.sbtest4(inserts=1852, updates=0, deletes=0)]
mysql3306-bin.000005 2021-02-26_12:33:46 2021-02-26_12:33:46 18388784   18742366   1852     0          [lhrdb.sbtest6(inserts=1852, updates=0, deletes=0)]
mysql3306-bin.000005 2021-02-26_12:33:46 2021-02-26_12:33:46 18742431   19096013   1852     0          [lhrdb.sbtest1(inserts=1852, updates=0, deletes=0)]
[root@lhrmy2sql my2sql]# more binlog_status.txt 
binlog            starttime           stoptime            startpos   stoppos    inserts  updates  deletes  database        table               
mysql3306-bin.000005 2021-02-26_12:33:44 2021-02-26_12:33:45 522290     5895978    10000    0        0        lhrdb           sbtest5             
mysql3306-bin.000005 2021-02-26_12:33:44 2021-02-26_12:33:46 4151839    14381222   10000    0        0        lhrdb           sbtest7             
mysql3306-bin.000005 2021-02-26_12:33:44 2021-02-26_12:33:46 5896147    15253548   10000    0        0        lhrdb           sbtest10            
mysql3306-bin.000005 2021-02-26_12:33:44 2021-02-26_12:33:46 6933162    19095982   10000    0        0        lhrdb           sbtest1             
mysql3306-bin.000005 2021-02-26_12:33:44 2021-02-26_12:33:46 7451669    17681394   10000    0        0        lhrdb           sbtest3             
mysql3306-bin.000005 2021-02-26_12:33:44 2021-02-26_12:33:46 9360838    18742335   10000    0        0        lhrdb           sbtest6             
mysql3306-bin.000005 2021-02-26_12:33:44 2021-02-26_12:33:46 9879345    18388688   10000    0        0        lhrdb           sbtest4             
mysql3306-bin.000005 2021-02-26_12:33:44 2021-02-26_12:33:45 3783       5023824    10000    0        0        lhrdb           sbtest2             
mysql3306-bin.000005 2021-02-26_12:33:44 2021-02-26_12:33:45 2077811    8842161    10000    0        0        lhrdb           sbtest8             
mysql3306-bin.000005 2021-02-26_12:33:44 2021-02-26_12:33:46 7970176    18035041   10000    0        0        lhrdb           sbtest9  
```

若繼續對資料庫做壓測，繼續分析，可以統計到相關的SQL：

```sql
[root@lhrmy2sql my2sql]# sysbench /usr/share/sysbench/oltp_read_write.lua --time=60 --mysql-host=192.168.66.35 --mysql-port=3306 --mysql-user=root --mysql-password=lhr --mysql-db=lhrdb --table-size=10000 --tables=10 --threads=16 --events=999999999  --report-interval=10 --db-ps-mode=disable --forced-shutdown=1 run
sysbench 1.0.17 (using system LuaJIT 2.0.4)

Running the test with following options:
Number of threads: 16
Report intermediate results every 10 second(s)
Initializing random number generator from current time

Forcing shutdown in 61 seconds

Initializing worker threads...

Threads started!

[ 10s ] thds: 16 tps: 339.98 qps: 6815.40 (r/w/o: 4773.15/1360.70/681.55) lat (ms,95%): 70.55 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 16 tps: 325.92 qps: 6524.67 (r/w/o: 4567.13/1305.69/651.85) lat (ms,95%): 75.82 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 16 tps: 323.20 qps: 6459.69 (r/w/o: 4521.49/1291.80/646.40) lat (ms,95%): 89.16 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 16 tps: 350.30 qps: 7000.31 (r/w/o: 4899.94/1399.78/700.59) lat (ms,95%): 71.83 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 16 tps: 349.38 qps: 6995.89 (r/w/o: 4897.68/1399.44/698.77) lat (ms,95%): 73.13 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 16 tps: 341.42 qps: 6824.69 (r/w/o: 4776.47/1365.48/682.74) lat (ms,95%): 71.83 err/s: 0.00 reconn/s: 0.00
SQL statistics:
    queries performed:
        read:                            284466
        write:                           81276
        other:                           40638
        total:                           406380
    transactions:                        20319  (338.41 per sec.)
    queries:                             406380 (6768.29 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          60.0385s
    total number of events:              20319

Latency (ms):
         min:                                   19.25
         avg:                                   47.25
         max:                                  178.19
         95th percentile:                       74.46
         sum:                               960171.80

Threads fairness:
    events (avg/stddev):           1269.9375/6.81
    execution time (avg/stddev):   60.0107/0.01

[root@lhrmy2sql my2sql]# ll
total 12
-rw-r--r-- 1 root root 6311 Feb 26 12:44 biglong_trx.txt
-rw-r--r-- 1 root root 1614 Feb 26 12:44 binlog_status.txt
[root@lhrmy2sql my2sql]# rm -rf *
[root@lhrmy2sql my2sql]# my2sql  -user root -password lhr  -port 3306 \
> -host 192.168.66.35 -databases lhrdb \
> -big-trx-row-limit 500 -long-trx-seconds 300 \
> -work-type stats   -start-file mysql3306-bin.000005 \
> -start-pos 19097041 -stop-file  mysql3306-bin.000005 -stop-pos  53302083 \
> -output-dir /my2sql/
[2021/02/26 12:47:46] [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:47:46] [info] binlogsyncer.go:360 begin to sync binlog from position (mysql3306-bin.000005, 19097041)
[2021/02/26 12:47:46] [info] stats_process.go:166 start thread to analyze statistics from binlog
[2021/02/26 12:47:46] [info] repl.go:15 start to get binlog from mysql
[2021/02/26 12:47:46] [info] binlogsyncer.go:777 rotate to (mysql3306-bin.000005, 19097041)
[2021/02/26 12:47:47] [info] com.go:57 stop to get event. StopFilePos set. currentBinlog (mysql3306-bin.000005, 53302083) StopFilePos (mysql3306-bin.000005, 53302083)
[2021/02/26 12:47:47] [info] repl.go:17 finish getting binlog from mysql
[2021/02/26 12:47:47] [info] stats_process.go:266 exit thread to analyze statistics from binlog
[root@lhrmy2sql my2sql]# ll
total 12
-rw-r--r-- 1 root root  107 Feb 26 12:47 biglong_trx.txt
-rw-r--r-- 1 root root 4554 Feb 26 12:47 binlog_status.txt
[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.000005 2021-02-26_12:45:45 2021-02-26_12:46:14 19102706   35743680   990      1952     990      lhrdb           sbtest1             
mysql3306-bin.000005 2021-02-26_12:45:45 2021-02-26_12:46:14 19105598   35739197   964      1931     964      lhrdb           sbtest9             
mysql3306-bin.000005 2021-02-26_12:45:45 2021-02-26_12:46:14 19097179   35744246   956      1921     956      lhrdb           sbtest4             
mysql3306-bin.000005 2021-02-26_12:45:45 2021-02-26_12:46:14 19097653   35741997   945      1958     945      lhrdb           sbtest6             
mysql3306-bin.000005 2021-02-26_12:45:45 2021-02-26_12:46:14 19098864   35741523   989      1981     989      lhrdb           sbtest2             
mysql3306-bin.000005 2021-02-26_12:45:45 2021-02-26_12:46:14 19099338   35736472   996      2036     996      lhrdb           sbtest5             
mysql3306-bin.000005 2021-02-26_12:45:45 2021-02-26_12:46:14 19101497   35734145   1019     1994     1019     lhrdb           sbtest7             
mysql3306-bin.000005 2021-02-26_12:45:45 2021-02-26_12:46:14 19098127   35738157   1013     2023     1013     lhrdb           sbtest10            
mysql3306-bin.000005 2021-02-26_12:45:45 2021-02-26_12:46:14 19100549   35743206   993      2008     993      lhrdb           sbtest8             
mysql3306-bin.000005 2021-02-26_12:45:45 2021-02-26_12:46:14 19101023   35740880   1024     1974     1024     lhrdb           sbtest3             
mysql3306-bin.000005 2021-02-26_12:46:15 2021-02-26_12:46:44 35761728   53261637   1017     2046     1017     lhrdb           sbtest2             
mysql3306-bin.000005 2021-02-26_12:46:14 2021-02-26_12:46:44 35744890   53263965   1041     2101     1041     lhrdb           sbtest5             
mysql3306-bin.000005 2021-02-26_12:46:14 2021-02-26_12:46:44 35746573   53262280   1080     2059     1080     lhrdb           sbtest6             
mysql3306-bin.000005 2021-02-26_12:46:14 2021-02-26_12:46:44 35750416   53261071   1012     2098     1012     lhrdb           sbtest4             
mysql3306-bin.000005 2021-02-26_12:46:14 2021-02-26_12:46:44 35751625   53262754   1095     2045     1095     lhrdb           sbtest7             
mysql3306-bin.000005 2021-02-26_12:46:15 2021-02-26_12:46:44 35754518   53256021   1062     2191     1062     lhrdb           sbtest8             
mysql3306-bin.000005 2021-02-26_12:46:14 2021-02-26_12:46:44 35744415   53263322   1001     2093     1001     lhrdb           sbtest10            
mysql3306-bin.000005 2021-02-26_12:46:15 2021-02-26_12:46:45 35745364   53264439   1052     2054     1052     lhrdb           sbtest3             
mysql3306-bin.000005 2021-02-26_12:46:14 2021-02-26_12:46:44 35746099   53254338   1037     2072     1037     lhrdb           sbtest1             
mysql3306-bin.000005 2021-02-26_12:46:14 2021-02-26_12:46:44 35747782   53253220   1010     2057     1010     lhrdb           sbtest9             
mysql3306-bin.000005 2021-02-26_12:46:45 2021-02-26_12:46:45 53270228   53284649   1        3        1        lhrdb           sbtest4             
mysql3306-bin.000005 2021-02-26_12:46:45 2021-02-26_12:46:45 53286333   53291384   1        1        1        lhrdb           sbtest1             
mysql3306-bin.000005 2021-02-26_12:46:45 2021-02-26_12:46:45 53271176   53302052   4        3        4        lhrdb           sbtest8             
mysql3306-bin.000005 2021-02-26_12:46:45 2021-02-26_12:46:45 53272860   53298684   2        1        2        lhrdb           sbtest9             
mysql3306-bin.000005 2021-02-26_12:46:45 2021-02-26_12:46:45 53277911   53299327   2        5        2        lhrdb           sbtest7             
mysql3306-bin.000005 2021-02-26_12:46:45 2021-02-26_12:46:45 53264439   53295317   4        5        4        lhrdb           sbtest5             
mysql3306-bin.000005 2021-02-26_12:46:44 2021-02-26_12:46:45 53265174   53301011   2        4        2        lhrdb           sbtest6             
mysql3306-bin.000005 2021-02-26_12:46:44 2021-02-26_12:46:45 53266122   53295960   3        5        3        lhrdb           sbtest2             
mysql3306-bin.000005 2021-02-26_12:46:44 2021-02-26_12:46:45 53266857   53301486   4        13       4        lhrdb           sbtest10            
mysql3306-bin.000005 2021-02-26_12:46:45 2021-02-26_12:46:45 53270702   53298118   0        4        0        lhrdb           sbtest3             
[root@lhrmy2sql my2sql]# 
```

**About Me**
***
> ● 本文作者：小麥苗，部分內容整理自網路，若有侵權請聯絡小麥苗刪除  
> ● 本文在個人微 信公眾號（[DB寶](https://mp.weixin.qq.com/s/36ahcv7o8u67NcxGSGO5WQ)）上有同步更新  
> ● QQ群號： 230161599 、618766405，微信群私聊  
> ● 個人QQ號（646634621），微 訊號（db_bao），註明新增緣由  
> ● 於 2021年3月 在西安完成  
> ● 最新修改時間：2021年3月  
> ● 版權所有，歡迎分享本文，轉載請保留出處  
> ***
> ●小麥苗的微店： [https://weidian.com/?userid=793741433](https://weidian.com/?userid=793741433)  
> ●小麥苗出版的資料庫類叢書： [http://blog.itpub.net/26736162/viewspace-2142121/](http://blog.itpub.net/26736162/viewspace-2142121/)  
> ●小麥苗OCP、OCM、高可用、DBA學習班（Oracle、MySQL、NoSQL）： [http://blog.itpub.net/26736162/viewspace-2148098/](http://blog.itpub.net/26736162/viewspace-2148098/)  
> ●資料庫筆試面試題庫及解答： [https://mp.weixin.qq.com/s/Vm5PqNcDcITkOr9cQg6T7w](https://mp.weixin.qq.com/s/Vm5PqNcDcITkOr9cQg6T7w)  
> ***
> 使用微信客戶端掃描下面的二維碼來關注小麥苗的微信公眾號（[DB寶](https://mp.weixin.qq.com/s/Vm5PqNcDcITkOr9cQg6T7w)）及QQ群（DBA寶典）、新增小麥苗微信， 學習最實用的資料庫技術。
> ![小麥苗資訊](https://img-blog.csdnimg.cn/20191021104734877.png)
> ***</nil></nil></nil></nil>

【DB寶43】MySQL誤操作閃回恢復利器之my2sql

相關文章