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

IT那活兒發布于2023-01-11 13:20 / 2559人閱讀

點擊上方“IT那活兒”，關注后了解更多精彩內容！！

一、簡介

1. binlog_inspector通過解釋mysql/mariadb binlog/relaylog實現以下三大功能:

1.1 flashback/閃回/回滾，實現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,華南理工大學&SCUT,X89504e47,1.1,1.1,1)
commit
```

1.3 統計分析，統計各個表的DML情況，找出大事務與長事務。

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

2.1 支持mysql5.5及以上，也支持mariadb的binlog，支持傳統復制的binlog，也支持GTID的binlog。

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

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

3. 限制

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

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

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

二、適用場景

1. 數據被誤操作，需要把某幾個表的數據不停機回滾到某個時間點

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

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

4. 需要把這個表從昨晚1點到3點的更新提供給開發查問題

5. 幫忙找出某個時間點數據庫是否有大事務或者長事務

三、特點

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

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

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

1.3 生成表DML統計信息，大事務與長事務統計信息只需要55秒

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

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

--mtype=mariadb

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

3.1 解釋binlog的開始位置：

--start-binlog=mysql-bin.000101

--start-pos=4

3.2 解釋binlog的結束位置：

--stop-binlog=mysql-bin.000105

--stop-pos=4

3.3 解釋binlog的開始時間

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

3.4 解釋binlog的結束時間

--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，也支持復制協議， binlog_inspector作為一個從庫從主庫拉binlog來本地解釋

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

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

7. 輸出的結果支持一個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. 輸出的結果是大家常見的易讀形式的SQL，支持表名前是否加數據庫名

--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. 輸出結果支持是否保留事務

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

如果復制因為特別大的事務而中斷，則可以以不保留事務的形式生成前滾的SQL, 在從庫上執行，然后跳過這個事務，再啟動復制，免去重建從庫的麻煩，特別是很大的庫。

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只包含最少必須的字段, 前提下是表含有唯一索引

默認為：

```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. 支持優先使用唯一索引而不是主鍵來構建where條件

--prefer-unique-key

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

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

--insert-ignore-primary

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

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

--insert-rows=100

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

15. 支持自定義DDL語句過濾正則表達式來輸出目標DDL

--ddl-regexp

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

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

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

例如表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 創建時間
alter TABLE emp drop column updatetime
```

但binlog_inspector這時獲取到的表結構表結構如下：

```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 創建時間,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1000 DEFAULT CHARSET=utf8
```

不清楚之前的表結構，就會出現錯亂:

```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 創建時間
```

表結構信息會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": ""
}
}
}
}
```

結合上面的信息，手動修改table_columns.json，讓其也保存有DDL前的表結構：

```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": ""
}
}
}
}
```

并加上參數--table-columns=table_columns.json --only-table-columns讓binlog_inspector從table_columns.json獲取表結構信息，重新運行，生成的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,**理工大學&SCUT,1.100000023841858,1.1,1,X89504e47);
commit;
```

四、安裝與使用

1. 安裝

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

如果需要編譯，請使用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

本文原創來源：IT那活兒微信公眾號（上海新炬王翦團隊）

GPU云服務器云服務器 binlog mysql mysql binlog恢復 mysql_binlog 解析日志文件

文章版權歸作者所有，未經允許請勿轉載,若此文章存在違規行為，您可以聯系管理員刪除。

轉載請注明本文地址：http://specialneedsforspecialkids.com/yun/129710.html

[Spring cloud 一步步實現廣告系統] 15. 使用開源組件監聽Binlog 實現增量索引

摘要：不會記錄數據表的列名在接下來的實現中，我們會將自己的系統包裝成一個假的，通過開源工具來實現監聽。因為我們只需要中的內容，那么我們也就只需要通過實現接口，來自定義一個監聽器實現我們的業務即可。 MySQL Binlog簡介什么是binlog? 一個二進制日志，用來記錄對數據發生或潛在發生更改的SQL語句，并以而進行的形式保存在磁盤中。 binlog 的作用？最主要有3個用途： ...

darryrzhong 2019-08-19 10:25 評論0 收藏0

發表評論

登陸后可評論

0條評論

IT那活兒

男|高級講師

我要關注我要私信

TA的文章

消息中間件故障分析一例

閱讀 1346·2023-01-11 13:20
RAC雙節點crash回復一例

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

閱讀 1132·2023-01-11 13:20
雙節點RAC實例2 HANG 故障分析一例

閱讀 1858·2023-01-11 13:20
RAC集群節點1重啟分析一例

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

閱讀 2704·2023-01-11 13:20
oracle 12CR2打補丁報錯處理一例

閱讀 1385·2023-01-11 13:20
分布式緩存組件故障分析及監控優化

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

国产xxxx99真实实拍_久久不雅视频_高清韩国a级特黄毛片_嗯老师别我我受不了了小说

資訊專欄INFORMATION COLUMN

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

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

點擊上方“IT那活兒”，關注后了解更多精彩內容！！

本文原創來源：IT那活兒微信公眾號（上海新炬王翦團隊）

相關文章

**[Spring cloud 一步步實現廣告系統] 15. 使用開源組件監聽Binlog 實現增量索引**

發表評論

0條評論

IT那活兒

男|高級講師

TA的文章

消息中間件故障分析一例

RAC雙節點crash回復一例

ORA-600處理一例

雙節點RAC實例2 HANG 故障分析一例

RAC集群節點1重啟分析一例

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

oracle 12CR2打補丁報錯處理一例

分布式緩存組件故障分析及監控優化

最新活動

資訊專欄INFORMATION COLUMN

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

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

點擊上方“IT那活兒”，關注后了解更多精彩內容！！

本 文 原 創 來 源：IT那活兒微信公眾號（上海新炬王翦團隊）

相關文章

發表評論

0條評論

男|高級講師

TA的文章

最新活動

本文原創來源：IT那活兒微信公眾號（上海新炬王翦團隊）