MySQL?binlog日志解析工具--binlog_inspector

IT那活兒發(fā)布于2023-01-11 13:20 / 2572人閱讀

點(diǎn)擊上方“IT那活兒”，關(guān)注后了解更多精彩內(nèi)容！！

一、簡介

1. binlog_inspector通過解釋mysql/mariadb binlog/relaylog實(shí)現(xiàn)以下三大功能:

1.1 flashback/閃回/回滾，實(shí)現(xiàn)DML的回滾到任意時間或者位置。

生成的SQL形式如下：

```sql
begin
DELETE FROM `binlog_inspector`.`emp` WHERE `id`=1
# datetime=2017-10-23_00:14:28 database=binlog_inspector table=emp binlog=mysql-bin.000012 startpos=417 stoppos=575
commit
```

1.2 前滾，把binlog/relaylog的DML解釋成易讀的SQL語句。

生成的SQL形式如下：

```sql
begin
# datetime=2017-10-23_00:14:28 database=binlog_inspector table=emp binlog=mysql-bin.000012 startpos=417 stoppos=575
INSERT INTO `binlog_inspector`.`emp` (`id`,`name`,`sr`,`icon`,`points`,`sa`,`sex`) VALUES (1,張三1,華南理工大學(xué)&SCUT,X89504e47,1.1,1.1,1)
commit
```

1.3 統(tǒng)計分析，統(tǒng)計各個表的DML情況，找出大事務(wù)與長事務(wù)。

2. 以上功能均可指定任意的單庫多庫，單表多表，任意時間點(diǎn)，任意binlog位置。

2.1 支持mysql5.5及以上，也支持mariadb的binlog，支持傳統(tǒng)復(fù)制的binlog，也支持GTID的binlog。

2.2 支持直接指定文件路徑的binlog，也支持主從復(fù)制， binlog_inspector作為從庫從主庫拉binlog來過解釋。

2.3 支持目標(biāo)binlog中包含了DDL(增加與減少表字段，變化表字位置)的場景。

3. 限制

3.1 binlog格式必須為row,且binlog_row_image=full

3.2 只能回滾DML，不能回滾DDL

3.3 支持V4格式的binlog， V3格式的沒測試過

二、適用場景

1. 數(shù)據(jù)被誤操作，需要把某幾個表的數(shù)據(jù)不停機(jī)回滾到某個時間點(diǎn)

2. 數(shù)據(jù)異常，幫忙從binlog中找出這個表的某些數(shù)據(jù)是什么時間修改成某些值的

3. IO高TPS高，幫忙查出那些表在頻繁更新

4. 需要把這個表從昨晚1點(diǎn)到3點(diǎn)的更新提供給開發(fā)查問題

5. 幫忙找出某個時間點(diǎn)數(shù)據(jù)庫是否有大事務(wù)或者長事務(wù)

三、特點(diǎn)

1. 速度快。解釋512MB的binlog：

1.1 生成回滾的SQL只需要1分26秒(6線程)

1.2 生成前滾的SQL只需要1分26秒(6線程)

1.3 生成表DML統(tǒng)計信息，大事務(wù)與長事務(wù)統(tǒng)計信息只需要55秒

1.4 mysqlbinlog解釋同樣的binlog只需要36秒

2. 支持V4版本的binlog，支持傳統(tǒng)與GTID的binlog，支持mysql5.5與mairiadb5.5及以上版本的binlog，也同樣支持relaylog(結(jié)果中注釋的信息binlog=xxx startpos=xxx stoppos=xx是對應(yīng)的主庫的binlog信息)

--mtype=mariadb

3. 支持以時間及位置條件過濾，并且支持單個以及多個連續(xù)binlog的解釋。

3.1 解釋binlog的開始位置：

--start-binlog=mysql-bin.000101

--start-pos=4

3.2 解釋binlog的結(jié)束位置：

--stop-binlog=mysql-bin.000105

--stop-pos=4

3.3 解釋binlog的開始時間

--start-datetime="2018-04-21 00:00:00"

3.4 解釋binlog的結(jié)束時間

--stop-datetime="2018-04-22 11:00:00"

4. 支持以庫及表條件過濾, 以逗號分隔

--databases=db1,db2

--tables=tb1,tb2

5. 支持以DML類型(update,delete,insert)條件過濾

--sqltypes=delete,update

6. 支持分析本地binlog，也支持復(fù)制協(xié)議， binlog_inspector作為一個從庫從主庫拉binlog來本地解釋

--mode=file //解釋本地binlog

--mode=repl //binlog_inspector作為slave連接到主庫拉binlog來解釋

7. 輸出的結(jié)果支持一個binlog一個文件，也可以一個表一個文件

--file-each-table

例如對于binlog mysql-bin.000101, 如果一個表一個文件，則生成的文件形式為db.tb.rollback.101.sql(回滾)，db.tb.forward.101.sql(前滾)，

否則是rollback.101.sql(回滾),forward.101.sql(前滾)

8. 輸出的結(jié)果是大家常見的易讀形式的SQL，支持表名前是否加數(shù)據(jù)庫名

--prefix-database
```sql
begin
# datetime=2017-10-23_00:14:34 database=binlog_inspector table=emp binlog=mysql-bin.000012 startpos=21615 stoppos=22822
UPDATE `binlog_inspector`.`emp` SET `sa`=1001 WHERE `id`=5;
# datetime=2017-10-23_00:14:45 database=binlog_inspector table=emp binlog=mysql-bin.000012 startpos=22822 stoppos=23930
UPDATE `binlog_inspector`.`emp` SET `name`=null WHERE `id`=5;
commit
```

否則為：

```sql
begin
# datetime=2017-10-23_00:14:34 database=binlog_inspector table=emp binlog=mysql-bin.000012 startpos=21615 stoppos=22822
UPDATE `emp` SET `sa`=1001 WHERE `id`=5;
# datetime=2017-10-23_00:14:45 database=binlog_inspector table=emp binlog=mysql-bin.000012 startpos=22822 stoppos=23930
UPDATE .`emp` SET `name`=null WHERE `id`=5;
commit
```

9. 輸出結(jié)果支持是否保留事務(wù)

--keep-trx
```sql
begin
# datetime=2017-10-23_00:14:34 database=binlog_inspector table=emp binlog=mysql-bin.000012 startpos=21615 stoppos=22822
UPDATE `binlog_inspector`.`emp` SET `sa`=1001 WHERE `id`=5;
# datetime=2017-10-23_00:14:45 database=binlog_inspector table=emp binlog=mysql-bin.000012 startpos=22822 stoppos=23930
UPDATE `binlog_inspector`.`emp` SET `name`=null WHERE `id`=5;
commit
```

不保留則是這樣：

```sql
# datetime=2017-10-23_00:14:34 database=binlog_inspector table=emp binlog=mysql-bin.000012 startpos=21615 stoppos=22822
UPDATE `binlog_inspector`.`emp` SET `sa`=1001 WHERE `id`=5;
# datetime=2017-10-23_00:14:45 database=binlog_inspector table=emp binlog=mysql-bin.000012 startpos=22822 stoppos=23930
UPDATE `binlog_inspector`.`emp` SET `name`=null WHERE `id`=5;
```

如果復(fù)制因?yàn)樘貏e大的事務(wù)而中斷，則可以以不保留事務(wù)的形式生成前滾的SQL, 在從庫上執(zhí)行，然后跳過這個事務(wù)，再啟動復(fù)制，免去重建從庫的麻煩，特別是很大的庫。

10. 支持輸出是否包含時間與binlog位置信息

--extra-info

包含額外的信息則為：

```sql
# datetime=2017-10-23_00:14:34 database=binlog_inspector table=emp binlog=mysql-bin.000012 startpos=21615 stoppos=22822
UPDATE `binlog_inspector`.`emp` SET `sa`=1001 WHERE `id`=5;
# datetime=2017-10-23_00:14:45 database=binlog_inspector table=emp binlog=mysql-bin.000012 startpos=22822 stoppos=23930
UPDATE `binlog_inspector`.`emp` SET `name`=null WHERE `id`=5;
```

否則為：

```sql
UPDATE `binlog_inspector`.`emp` SET `sa`=1001 WHERE `id`=5;
UPDATE `binlog_inspector`.`emp` SET `name`=null WHERE `id`=5;
```

11. 支持生成的SQL只包含最少必須的字段, 前提下是表含有唯一索引

默認(rèn)為：

```sql
UPDATE `binlog_inspector`.`emp` SET `sa`=1001 WHERE `id`=5;
DELETE FROM `binlog_inspector` WHERE `id`=5;
```
--full-columns 則為
```sql
UPDATE `binlog_inspector`.`emp` SET `id`=5, `age`=21, `sex`=M,`sa`=1001, `name`=Danny WHERE `id`=5 and `age`=21 and `sex`=M and `sa`=900 and `name`=Danny;
DELETE FROM `binlog_inspector` WHERE `id`=5 and `age`=21 and `sex`=M and `sa`=900 and `name`=Danny;
```

12. 支持優(yōu)先使用唯一索引而不是主鍵來構(gòu)建where條件

--prefer-unique-key

有時不希望使用主健來構(gòu)建wheret條件，如發(fā)生雙寫時，自增主健沖突了，這時使用非主健的唯一索引來避免生成的SQL主健沖突

13. 支持生成的insert語句不包含主健

--insert-ignore-primary

發(fā)生雙寫時，自增主健沖突了，這時使用這個參數(shù)來讓生成的insert語句不包括主健來避免生成的SQL主健沖突

14. 支持大insert拆分成小insert語句。

--insert-rows=100

對于一個insert 1000行的插入，會生成10個insert語句，每個語句插入100行

15. 支持自定義DDL語句過濾正則表達(dá)式來輸出目標(biāo)DDL

--ddl-regexp

默認(rèn)為"^s*(alter|create|rename|truncate|drop)", 大小寫不敏感

16. 支持目標(biāo)binlog中包含DDL(增減字段,變化字段位置)的情形

binlog只保存了各個字段的位置，并沒有保存各個字段的名字。在前滾與回滾的模式下, binlog_inspector需要拿到表結(jié)構(gòu)信息來生成易讀的SQL, 如果表結(jié)構(gòu)有變化, 那如何處理？

例如表tmp的DDL如下：

```sql
create table emp (name varchar(50), sr text, points float, sa decimal(10,3), sex enum("f", "m"), icon blob)
alter table emp add column id int  first
truncate table emp
alter table emp add primary key (id)
alter table emp modify id int auto_increment
alter TABLE emp add column updatetime datetime comment 更新時間, add createtime timestamp default current_timestamp comment 創(chuàng)建時間
alter TABLE emp drop column updatetime
```

但binlog_inspector這時獲取到的表結(jié)構(gòu)表結(jié)構(gòu)如下：

```sql
CREATE TABLE `emp` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(50) DEFAULT NULL,
`sr` text,
`points` float DEFAULT NULL,
`sa` decimal(10,3) DEFAULT NULL,
`sex` enum(f,m) DEFAULT NULL,
`icon` blob,
`createtime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 創(chuàng)建時間,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1000 DEFAULT CHARSET=utf8
```

不清楚之前的表結(jié)構(gòu)，就會出現(xiàn)錯亂:

```sql
begin;
# datetime=2018-02-05_10:12:41 database=binlog_inspector table=emp binlog=mysql-bin.000001 startpos=1614 stoppos=1772
INSERT INTO `binlog_inspector`.`emp` (`id`,`name`,`sr`,`points`,`sa`,`sex`) VALUES (張三1,Xe58d8ee58d97e79086e5b7a5e5a4a7e5ada62653435554,1.100000023841858,1.1,1,X89504e47);
commit;
```

binlog_inspector會輸出所有DDL的語句到ddl_info.log這個文件，有時間與位置信息，如:

```sql
datetime binlog startpos stoppos sql
2018-02-05_10:12:18 mysql-bin.000001 1115 1320 create table emp (name varchar(50), sr text, points float, sa decimal(10,3), sex enum("f", "m"), icon blob)
2018-02-05_10:15:10 mysql-bin.000001  8556       8694       alter table emp add column id int  first
2018-02-05_10:16:41 mysql-bin.000001  8759       8856       truncate table emp
2018-02-05_10:16:42 mysql-bin.000001  8921       9055       alter table emp add primary key (id)
2018-02-05_10:17:21 mysql-bin.000001  9120       9262       alter table emp modify id int auto_increment
2018-02-05_13:46:18 mysql-bin.000001  400409     400653     alter TABLE emp add column updatetime datetime comment 更新時間, add createtime timestamp default current_timestamp comment 創(chuàng)建時間
```

表結(jié)構(gòu)信息會dump到文件table_columns.json文件，如：

```json
{
"binlog_inspector.emp": {
"_/0/0": {
"database": "binlog_inspector",
"table": "emp",
"columns": [
{
"column_name": "id",
"column_type": "int"
},
{
"column_name": "name",
"column_type": "varchar"
},
{
"column_name": "sr",
"column_type": "text"
},
{
"column_name": "points",
"column_type": "float"
},
{
"column_name": "sa",
"column_type": "decimal"
},
{
"column_name": "sex",
"column_type": "enum"
},
{
"column_name": "icon",
"column_type": "blob"
},
{
"column_name": "createtime",
"column_type": "timestamp"
}
],
"primary_key": [
"id"
],
"unique_keys": [],
"ddl_info": {
"binlog": "_",
"start_position": 0,
"stop_position": 0,
"ddl_sql": ""
}
}
}
}
```

結(jié)合上面的信息，手動修改table_columns.json，讓其也保存有DDL前的表結(jié)構(gòu)：

```json
{
"binlog_inspector.emp": {
"mysql-bin.000001/8556/8694": {
"database": "binlog_inspector",
"table": "emp",
"columns": [
{
"column_name": "name",
"column_type": "varchar"
},
{
"column_name": "sr",
"column_type": "text"
},
{
"column_name": "points",
"column_type": "float"
},
{
"column_name": "sa",
"column_type": "decimal"
},
{
"column_name": "sex",
"column_type": "enum"
},
{
"column_name": "icon",
"column_type": "blob"
}
],
"primary_key": [],
"unique_keys": [],
"ddl_info": {
"binlog": "mysql-bin.000001",
"start_position": 8556,
"stop_position": 8694,
"ddl_sql": ""
}
},
"_/0/0": {
"database": "binlog_inspector",
"table": "emp",
"columns": [
{
"column_name": "id",
"column_type": "int"
},
{
"column_name": "name",
"column_type": "varchar"
},
{
"column_name": "sr",
"column_type": "text"
},
{
"column_name": "points",
"column_type": "float"
},
{
"column_name": "sa",
"column_type": "decimal"
},
{
"column_name": "sex",
"column_type": "enum"
},
{
"column_name": "icon",
"column_type": "blob"
},
{
"column_name": "createtime",
"column_type": "timestamp"
}
],
"primary_key": [
"id"
],
"unique_keys": [],
"ddl_info": {
"binlog": "_",
"start_position": 0,
"stop_position": 0,
"ddl_sql": ""
}
}
}
}
```

并加上參數(shù)--table-columns=table_columns.json --only-table-columns讓binlog_inspector從table_columns.json獲取表結(jié)構(gòu)信息，重新運(yùn)行，生成的SQL無誤了。

```sql
begin;
# datetime=2018-02-05_10:12:41 database=binlog_inspector table=emp binlog=mysql-bin.000001 startpos=1614 stoppos=1772
INSERT INTO `binlog_inspector`.`emp` (`name`,`sr`,`points`,`sa`,`sex`,`icon`) VALUES (張三1,**理工大學(xué)&SCUT,1.100000023841858,1.1,1,X89504e47);
commit;
```

四、安裝與使用

1. 安裝

https://github.com/GoDannyLai/binlog_inspector/releases中有編譯好的linux與window二進(jìn)制版本，可以直接使用，無其它依賴。

如果需要編譯，請使用GO>=1.8.3版本來編譯。使用的其中兩個依賴庫https://github.com/siddontang/go-mysql與https://github.com/dropbox/godropbox/database/sqlbuilder

有修改小部分的源碼，請使用vendor中包，或者按照 `開源庫所做的修改.txt` 中來修改https://github.com/siddontang/go-mysql與https://github.com/dropbox/godropbox/database/sqlbuilder

2. 使用

2.1 生成前滾SQL與DML報表:

./binlog_inspector --mode=repl --wtype=2sql --mtype=mysql --
threads=4 --serverid=3331 --host=127.0.0.1 --port=330 --
user=xxx --password=xxx --databases=db1,db2 --tables=tb1,tb2 
--start-binlog=mysql-bin.000556 --start-pos=107 --stop-
binlog=mysql-bin.000559 --stop-pos=4 --min-columns --file-
each-table --insert-rows=20 --keep-trx --big-trx-rows=100 --
long-trx-seconds=10 --output-dir=/home/apps/tmp --table-
columns tbs_all_def.json

2.2 生成回滾SQL與DML報表:

./binlog_inspector --mode=file --wtype=rollback --
mtype=mysql --threads=4 --host=127.0.0.1 --port=3306 --
user=xxx --password=xxx --databases=db1,db2 --tables=tb1,tb2 
--start-datetime=2017-09-28 13:00:00 --stop-
datetime=2017-09-28 16:00:00 --min-columns --file-each-
table --insert-rows=20 --keep-trx --big-trx-rows=100 --long-
trx-seconds=10 --output-dir=/home/apps/tmp --table-columns 
tbs_all_def.json /apps/dbdata/mysqldata_3306/log/mysql-
bin.000556

2.3 只生成DML報表:

./binlog_inspector --mode=file --wtype=stats --mtype=mysql -
-interval=20 --big-trx-rows=100 --long-trx-seconds=10 --
output-dir=/home/apps/tmp mysql-bin.000556

本文原創(chuàng) 來源：IT那活兒微信公眾號（上海新炬王翦團(tuán)隊）

文章版權(quán)歸作者所有，未經(jīng)允許請勿轉(zhuǎn)載,若此文章存在違規(guī)行為，您可以聯(lián)系管理員刪除。

轉(zhuǎn)載請注明本文地址：http://systransis.cn/yun/129710.html

發(fā)表評論

登陸后可評論

0條評論

IT那活兒

男|高級講師

我要關(guān)注我要私信

TA的文章

消息中間件故障分析一例

閱讀 1356·2023-01-11 13:20
RAC雙節(jié)點(diǎn)crash回復(fù)一例

閱讀 1707·2023-01-11 13:20
ORA-600處理一例

閱讀 1215·2023-01-11 13:20
雙節(jié)點(diǎn)RAC實(shí)例2 HANG 故障分析一例

閱讀 1906·2023-01-11 13:20
RAC集群節(jié)點(diǎn)1重啟分析一例

閱讀 4165·2023-01-11 13:20
CRS啟動報錯CRS-1656處理分享

閱讀 2757·2023-01-11 13:20
oracle 12CR2打補(bǔ)丁報錯處理一例

閱讀 1402·2023-01-11 13:20
分布式緩存組件故障分析及監(jiān)控優(yōu)化

閱讀 3671·2023-01-11 13:20

成人国产在线小视频_日韩寡妇人妻调教在线播放_色成人www永久在线观看_2018国产精品久久_亚洲欧美高清在线30p_亚洲少妇综合一区_黄色在线播放国产_亚洲另类技巧小说校园_国产主播xx日韩_a级毛片在线免费

資訊專欄INFORMATION COLUMN

上云采購季！| 2核2G4M爆款云服務(wù)器低至59元/年，更有多臺、長期優(yōu)惠，快來選購！

MySQL?binlog日志解析工具--binlog_inspector

點(diǎn)擊上方“IT那活兒”，關(guān)注后了解更多精彩內(nèi)容！！

本文原創(chuàng) 來源：IT那活兒微信公眾號（上海新炬王翦團(tuán)隊）

相關(guān)文章

**[Spring cloud 一步步實(shí)現(xiàn)廣告系統(tǒng)] 15. 使用開源組件監(jiān)聽Binlog 實(shí)現(xiàn)增量索引**

發(fā)表評論

0條評論

IT那活兒

男|高級講師

TA的文章

消息中間件故障分析一例

RAC雙節(jié)點(diǎn)crash回復(fù)一例

ORA-600處理一例

雙節(jié)點(diǎn)RAC實(shí)例2 HANG 故障分析一例

RAC集群節(jié)點(diǎn)1重啟分析一例

CRS啟動報錯CRS-1656處理分享

oracle 12CR2打補(bǔ)丁報錯處理一例

分布式緩存組件故障分析及監(jiān)控優(yōu)化

最新活動

資訊專欄INFORMATION COLUMN

上云采購季！| 2核2G4M爆款云服務(wù)器低至59元/年，更有多臺、長期優(yōu)惠，快來選購！

MySQL?binlog日志解析工具--binlog_inspector

點(diǎn)擊上方“IT那活兒”，關(guān)注后了解更多精彩內(nèi)容！！

本 文 原 創(chuàng) 來 源：IT那活兒微信公眾號（上海新炬王翦團(tuán)隊）

相關(guān)文章

發(fā)表評論

0條評論

男|高級講師

TA的文章

最新活動

上云采購季！| 2核2G4M爆款云服務(wù)器低至59元/年，更有多臺、長期優(yōu)惠，快來選購！

點(diǎn)擊上方“IT那活兒”，關(guān)注后了解更多精彩內(nèi)容！！

本文原創(chuàng) 來源：IT那活兒微信公眾號（上海新炬王翦團(tuán)隊）