MySQL の binlog ログ ファイルの詳細な紹介

この記事では、MySQL の binlog ログ ファイルについて詳しく説明します。必要な方は参考にしていただければ幸いです。

MySQL の binlog ログ ファイルには、データベース テーブルのすべての変更操作が記録されます。この記事では、MySQL binlog に関連する知識と、binlog を使用してデータベース データを復元またはフラッシュバックする方法を簡単にまとめます。

STATEMENT 形式の binlog

binlog を有効にするには、MySQL の起動時に --log-bin パラメータを渡す必要があります。または、MySQL 構成ファイル /etc/my.cnf で log_bin を設定して、binlog を有効にすることもできます。 MySQL 5.7 以降では、binlog を有効にした後、 --server-id パラメータも指定する必要があります。指定しないと、MySQL サーバーは起動に失敗します。

binlog_format は、STATEMENT、ROW、および MIXED の 3 つの形式をサポートします。MySQL 5.5 および 5.6 はデフォルトで STATEMENT になり、MySQL 5.7.7 ではデフォルトで ROW が始まります。のように SQL は、UUID()、RAND()、VERSION() およびその他の関数を使用するか、STATEMENT に基づいてストアド プロシージャ、カスタム関数を使用します。 マスターとスレーブがレプリケートされると安全ではありません (多くの人は NOW()、CURRENT_TIMESTAMP、およびこれらの関数も安全ではないと考えているかもしれませんが、実際には安全です) [doc1, ドキュメント2]。 ROW に基づくマスター/スレーブ レプリケーションは、最も安全なレプリケーション方法です。

次に、STATEMENT 形式の binlog を見てみましょう。/etc/my.cnf ファイルの変更された内容は次のとおりです。

server_id = 1
log_bin = mysql-bin
binlog_format = STATEMENT
binlog_row_image=FULL

MySQL を再起動した後、データ ディレクトリ datadir の下にあります。 /var/lib /mysql/ など、対応する binlog ファイル mysql-bin.index および mysql-bin.000001 が生成されます。 .index 接尾辞が付いたファイルには、すべての binlog ファイル名が保存されます。 mysql-bin.000001 ファイルには、binlog コンテンツが記録されます。 MySQL が起動またはログをフラッシュするたびに、シーケンス番号に従って新しいログ ファイルが作成されます。さらに、ログ ファイルのサイズが max_binlog_size を超えると、新しいログ ファイルも作成されます。

次に、binlog 関数を試してみましょう。 testdb ライブラリに hello テーブルがあり、特定の行が変更されたとします。

mysql> select * from hello;
+----+-------+
| id | name  |
+----+-------+
|  1 | Andy  |
|  2 | Bill  |
|  3 | Candy |
+----+-------+
4 rows in set (0.00 sec)

mysql> update hello set name = 'Will' where id = 3;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

binlog はバイナリ ファイルであり、これを表示するには mysqlbinlog (doc, man) コマンドを使用する必要があります。

$ sudo mysqlbinlog /var/lib/mysql/mysql-bin.000001  # 直接在 mysql 服务器上读取 binlog 文件
$ mysqlbinlog -R -h192.168.2.107 -uroot -p123456 mysql-bin.000001  # 或者，远程读取 binlog 文件

更新の実行 新しく追加された binlog ファイルの内容は次のとおりです。

# at 154
#180617 22:47:49 server id 1  end_log_pos 219 CRC32 0x4bd9d69b     Anonymous_GTID    last_committed=0    sequence_number=1    rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 219
#180617 22:47:49 server id 1  end_log_pos 302 CRC32 0x476fafc9     Query    thread_id=2    exec_time=0    error_code=0
SET TIMESTAMP=1529246869/*!*/;
SET @@session.pseudo_thread_id=2/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1075838976/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 302
#180617 22:47:49 server id 1  end_log_pos 423 CRC32 0x7f2c2c7a     Query    thread_id=2    exec_time=0    error_code=0
use `testdb`/*!*/;
SET TIMESTAMP=1529246869/*!*/;
update hello set name = 'Will' where id = 3
/*!*/;
# at 423
#180617 22:47:49 server id 1  end_log_pos 454 CRC32 0x68da744a     Xid = 12
COMMIT/*!*/;

ROW 形式の binlog

/etc/my の binlog_format を変更します。 cnf を ROW にコピーし、MySQL を再起動します。形式が変更されると、新しい binlog ファイル mysql-bin.000002 が生成されます。

mysql> show create table hello;
+-------+-------------------------------------------------------------------------+
| Table | Create Table
+-------+-------------------------------------------------------------------------+
| hello | CREATE TABLE `hello` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 |
+-------+-------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from hello where id;
+----+------+
| id | name |
+----+------+
|  1 | Andy |
|  2 | Lily |
|  3 | Will |
+----+------+
1 row in set (0.00 sec)

mysql> update hello set name = 'David' where id = 3;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

ROW 形式でバイナリログを表示するには、 sudo mysqlbinlog -v --base64-output=DECODE-ROWS /var/lib/mysql/mysql-bin.000002 コマンドを使用する必要があります。更新実行後に新しく追加された対応するバイナリ コンテンツ:

# at 154
#180617 22:54:13 server id 1  end_log_pos 219 CRC32 0x2ce70d4d     Anonymous_GTID    last_committed=0    sequence_number=1    rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 219
#180617 22:54:13 server id 1  end_log_pos 293 CRC32 0x8183fddf     Query    thread_id=2    exec_time=0    error_code=0
SET TIMESTAMP=1529247253/*!*/;
SET @@session.pseudo_thread_id=2/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1075838976/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 293
#180617 22:54:13 server id 1  end_log_pos 346 CRC32 0x0fc7e1a4     Table_map: `testdb`.`hello` mapped to number 110
# at 346
#180617 22:54:13 server id 1  end_log_pos 411 CRC32 0xb58e729d     Update_rows: table id 110 flags: STMT_END_F
### UPDATE `testdb`.`hello`
### WHERE
###   @1=3
###   @2='Will'
### SET
###   @1=3
###   @2='David'
# at 411
#180617 22:54:13 server id 1  end_log_pos 442 CRC32 0xef964db8     Xid = 13
COMMIT/*!*/;

次の SQL が実行された場合:

mysql> insert hello (name) values ('Frank');
Query OK, 1 row affected (0.02 sec)

対応する生成されたバイナリ コンテンツ:

# at 442
#180617 22:55:47 server id 1  end_log_pos 507 CRC32 0x79de08a7     Anonymous_GTID    last_committed=1    sequence_number=2    rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 507
#180617 22:55:47 server id 1  end_log_pos 581 CRC32 0x56f9eb6a     Query    thread_id=2    exec_time=0    error_code=0
SET TIMESTAMP=1529247347/*!*/;
BEGIN
/*!*/;
# at 581
#180617 22:55:47 server id 1  end_log_pos 634 CRC32 0xedb73620     Table_map: `testdb`.`hello` mapped to number 110
# at 634
#180617 22:55:47 server id 1  end_log_pos 684 CRC32 0x525a6a70     Write_rows: table id 110 flags: STMT_END_F
### INSERT INTO `testdb`.`hello`
### SET
###   @1=4
###   @2='Frank'
# at 684
#180617 22:55:47 server id 1  end_log_pos 715 CRC32 0x09a0d4de     Xid = 14
COMMIT/*!*/;

次の SQL が実行された場合:

mysql> delete from hello where id = 2;
Query OK, 1 row affected (0.02 sec)

対応する生成された binlog コンテンツ:

# at 715
#180617 22:56:44 server id 1  end_log_pos 780 CRC32 0x9f52450e     Anonymous_GTID    last_committed=2    sequence_number=3    rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 780
#180617 22:56:44 server id 1  end_log_pos 854 CRC32 0x0959bc8d     Query    thread_id=2    exec_time=0    error_code=0
SET TIMESTAMP=1529247404/*!*/;
BEGIN
/*!*/;
# at 854
#180617 22:56:44 server id 1  end_log_pos 907 CRC32 0x2945260f     Table_map: `testdb`.`hello` mapped to number 110
# at 907
#180617 22:56:44 server id 1  end_log_pos 956 CRC32 0xc70df255     Delete_rows: table id 110 flags: STMT_END_F
### DELETE FROM `testdb`.`hello`
### WHERE
###   @1=2
###   @2='Bill'
# at 956
#180617 22:56:44 server id 1  end_log_pos 987 CRC32 0x0c98f18e     Xid = 15
COMMIT/*!*/;

binlog 増分リカバリを使用する

MySQL 論理バックアップは、通常、完全バックアップと増分バックアップを組み合わせて使用​​します。 mysqldump 定期的にデータベースを完全にバックアップし、binlog を使用して増分データを保存します。データを復元する場合、mysqldump によってバックアップされたデータはバックアップ時点に復元されます。データベースがバックアップ時点から現在までに増分変更された場合、binlog 内の増分データは mysqlbinlog を通じてデータベースに復元されます。次に、mysqldump を使用してデータベースを次の場所に復元したと仮定します。

mysql> select * from hello;
+----+------+
| id | name |
+----+------+
|  1 | Andy |
|  2 | Lily |
|  3 | Will |
+----+------+
3 rows in set (0.00 sec)

その後に実行された SQL:

update hello set name = 'David' where id = 3;
insert hello (name) values ('Frank');
delete from hello where id = 2;

STATEMENT または ROW のいずれを使用しても、mysqlbinlog コマンドはデータベースに binlog を段階的に復元できます [doc] ] 。

binlog を観察すると、最初の更新 hello set name = 'David' where id = 3; から最後の delete from hello where id = 2; までの時間が「2018-06」から始まっていることがわかります。 -17 22:54:13」から「2018-06-17 22:56:44」に変更するため、リカバリ時点に基づくと、コマンドは次のようになります:

$ sudo mysqlbinlog --start-datetime="2018-06-17 22:54:13" --stop-datetime="2018-06-17 22:56:44" mysql-bin.000002 | mysql -uroot -p123456

binlog'イベント位置番号は「154」から「956」までですが、位置ポイントの指定には --start-position と --stop-position が使用されることに注意してください。 range は論理的に start <=position < stop に対応するため、ポイントインタイム リカバリに基づくと、コマンドは次のようになります。

$ sudo mysqlbinlog --start-position=154 --stop-position=957 mysql-bin.000002 | mysql -uroot -p123456

いずれかの方法で実行すると、データは次の場所に復元できます。

mysql> select * from hello;
+----+-------+
| id | name  |
+----+-------+
|  1 | Andy  |
|  3 | David |
|  4 | Frank |
+----+-------+
3 rows in set (0.00 sec)<p><strong> binlog2sql</strong></p>
<p>binlog2sql を使用したフラッシュバック (Dianping.com の DBA である Cao Danfeng が作成)。 binlog2sql は、MySQL binlog から必要な SQL を解析します。オプションに応じて、元の SQL、ロールバック SQL、主キーを削除した INSERT SQL などを取得できます。 binlog2sql では、基礎となる実装は python-mysql-replication に依存しており、これにより MySQL レプリケーション プロトコルと binlog 形式の解析が完了します。 </p>
<pre class="brush:php;toolbar:false">$ python binlog2sql/binlog2sql.py -h192.168.2.107 -uroot -p123456 --start-position=154 --stop-position=957 --start-file='mysql-bin.000002'
UPDATE `testdb`.`hello` SET `id`=3, `name`='David' WHERE `id`=3 AND `name`='Will' LIMIT 1; #start 4 end 411 time 2018-06-17 22:54:13
INSERT INTO `testdb`.`hello`(`id`, `name`) VALUES (4, 'Frank'); #start 442 end 684 time 2018-06-17 22:55:47
DELETE FROM `testdb`.`hello` WHERE `id`=2 AND `name`='Bill' LIMIT 1; #start 715 end 956 time 2018-06-17 22:56:44

Generate rollback SQL:

$ python binlog2sql/binlog2sql.py --flashback -h192.168.2.107 -uroot -p123456 --start-position=154 --stop-position=956 --start-file='mysql-bin.000002'
INSERT INTO `testdb`.`hello`(`id`, `name`) VALUES (2, 'Bill'); #start 715 end 956 time 2018-06-17 22:56:44
DELETE FROM `testdb`.`hello` WHERE `id`=4 AND `name`='Frank' LIMIT 1; #start 442 end 684 time 2018-06-17 22:55:47
UPDATE `testdb`.`hello` SET `id`=3, `name`='Will' WHERE `id`=3 AND `name`='David' LIMIT 1; #start 154 end 411 time 2018-06-17 22:54:13

フラッシュバックの実際の原理は、まず MySQL レプリケーション プロトコルの com-binlog-dump コマンドを使用してバイナリ ログをダンプし、次にバイナリ ログを追跡します。 形式仕様に従ってバイナリログを解析し、バイナリログを SQL に変換し、これらの SQL を逆論理 SQL に変換し、最後に逆の順序で実行します。

Java 解析バイナリログ

上文中的 binlog2sql 其实底层依赖 python-mysql-replication 库，这是 Python 库。如果想使用 Java 解析 binlog 可以使用 mysql-binlog-connector-java（github）库。目前开源的 CDC 工具，如 Zendesk maxwell、Redhat debezium、LinkedIn Databus 等都底层依赖 mysql-binlog-connector-java 或者其前身 open-replicator。使用 mysql-binlog-connector-java 的示例代码如下：

BinaryLogClient client = new BinaryLogClient("192.168.2.107", 3306, "root", "123456");
client.setBinlogFilename("mysql-bin.000001");
client.setBinlogPosition(4);
client.setBlocking(false);
client.registerEventListener(event -> {
    System.out.println(event);
});
client.connect();

输出（省略部分内容）：

...
Event{header=EventHeaderV4{timestamp=1529247253000, eventType=TABLE_MAP, serverId=1, headerLength=19, dataLength=34, nextPosition=346, flags=0}, data=TableMapEventData{tableId=110, database='testdb', table='hello', columnTypes=8, 15, columnMetadata=0, 40, columnNullability={1}}}
Event{header=EventHeaderV4{timestamp=1529247253000, eventType=EXT_UPDATE_ROWS, serverId=1, headerLength=19, dataLength=46, nextPosition=411, flags=0}, data=UpdateRowsEventData{tableId=110, includedColumnsBeforeUpdate={0, 1}, includedColumns={0, 1}, rows=[
    {before=[3, Will], after=[3, David]}
]}}
...
Event{header=EventHeaderV4{timestamp=1529247347000, eventType=TABLE_MAP, serverId=1, headerLength=19, dataLength=34, nextPosition=634, flags=0}, data=TableMapEventData{tableId=110, database='testdb', table='hello', columnTypes=8, 15, columnMetadata=0, 40, columnNullability={1}}}
Event{header=EventHeaderV4{timestamp=1529247347000, eventType=EXT_WRITE_ROWS, serverId=1, headerLength=19, dataLength=31, nextPosition=684, flags=0}, data=WriteRowsEventData{tableId=110, includedColumns={0, 1}, rows=[
    [4, Frank]
]}}
...
Event{header=EventHeaderV4{timestamp=1529247404000, eventType=TABLE_MAP, serverId=1, headerLength=19, dataLength=34, nextPosition=907, flags=0}, data=TableMapEventData{tableId=110, database='testdb', table='hello', columnTypes=8, 15, columnMetadata=0, 40, columnNullability={1}}}
Event{header=EventHeaderV4{timestamp=1529247404000, eventType=EXT_DELETE_ROWS, serverId=1, headerLength=19, dataLength=30, nextPosition=956, flags=0}, data=DeleteRowsEventData{tableId=110, includedColumns={0, 1}, rows=[
    [2, Bill]
]}}

以上がMySQL の binlog ログ ファイルの詳細な紹介の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。