技術共有

한어Русский языкEnglishFrançaisIndonesianSanskrit日本語DeutschPortuguêsΕλληνικάespañolItalianoSuomalainenLatina

1 はじめに

1.1 MySQL とは 置き場 ログ？

マイグレーション Bin Log（バイナリ ログ、バイナリ ログ）は、データベースに対して実行されたすべての変更を記録する MySQL データベースのログ ファイルです。DML操作 (INSERT、UPDATE、DELETE など)、ただしSELECTを除く待ってDQL読み取り専用操作。 Bin Log は、MySQL がレプリケーション、リカバリ、監査を行うための重要なツールです。詳細については、以下を参照してください。MySQL の DDL、DML、DQL、および DCL

1.2 Bin Logの機能と応用シナリオ

Bin Log の主な機能には、マスター/スレーブ レプリケーション、データ リカバリ、データ バックアップ、データ サブスクリプションが含まれます。

これらの具体的なケースを通じて、実際のアプリケーションにおける MySQL binlog の重要性と多様性をより明確に理解できます。 binlog 機能を適切に使用すると、システムの信頼性、復旧能力、ビジネスの応答速度が大幅に向上します。

2. Bin Logの基本概念

2.1 Bin Logの動作原理

MySQL サーバーが Bin Log 機能を有効にすると、データベースに対するすべての変更操作が実行されます。イベント記録ビンログファイルに保存されます。これらのイベントは実行順に保存され、連続した操作ログ シーケンスを形成します。データを復元またはコピーする必要がある場合、これらのイベントを再生してデータベースの状態を再作成できます。

2.2 Bin Log の 3 つの形式

MySQL Bin Log には 3 つのレコード形式があります。
以下は、MySQL Bin Log の 3 つのレコード形式を表形式にまとめたものです。

3. Bin Log の設定と管理

3.1 ビンログを有効にする

mysql> show variables like "%log_bin%";
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin                         | OFF   |
| log_bin_basename                |       |
| log_bin_index                   |       |
| log_bin_trust_function_creators | OFF   |
| log_bin_use_v1_row_events       | OFF   |
| sql_log_bin                     | ON    |
+---------------------------------+-------+
6 rows in set, 1 warning (0.00 sec)


1
2
3
4
5
6
7
8
9
10
11
12
13
14

Bin Log を有効にするには、MySQL 構成ファイル (通常はmy.cnfまたはmy.ini) を選択し、次の構成を実行します。

[mysqld]
log-bin=mysql-bin
server-id=1
1
2
3

設定を変更した後、mysql を再起動します。埋め込むSHOW VARIABLES LIKE 'log_bin'; 値の値は ON です。

mysql> show variables like "%log_bin%";
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    2
Current database: *** NONE ***

+---------------------------------+--------------------------------------------------------------------------+
| Variable_name                   | Value                                                                    |
+---------------------------------+--------------------------------------------------------------------------+
| log_bin                         | ON                                                                       |
| log_bin_basename                | C:UsershiszmMySQL5.7.26databinlog       |
| log_bin_index                   | C:UsershiszmMySQL5.7.26databinlog.index |
| log_bin_trust_function_creators | OFF                                                                      |
| log_bin_use_v1_row_events       | OFF                                                                      |
| sql_log_bin                     | ON                                                                       |
+---------------------------------+--------------------------------------------------------------------------+
6 rows in set, 1 warning (0.01 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

3.2 ビンログパラメータの設定

一般的な Bin Log 構成パラメータには次のものがあります。

• log_bin: ビンログを有効にします。
• server_id: レプリケーションに使用されるサーバーの一意の識別子。
• binlog_format：Bin Logのフォーマット（STATEMENT、ROW、MIXED）を設定します。
• expire_logs_days: Bin Log ファイルの自動有効期限切れと削除の日数を設定します。
• max_binlog_size: 1 つの Bin Log ファイルの最大サイズを設定します。

3.3 Bin ログ ファイルの管理

Bin Log ファイルを管理するための一般的なコマンド:

• ビンログファイルリストの表示：

  SHOW BINARY LOGS;
  1

• ビュービンログのファイルサイズ：

  SHOW MASTER STATUS;
  1

• 古い Bin ログ ファイルを削除する：

  PURGE BINARY LOGS TO 'mysql-bin.000010';
  1

  または：

  PURGE BINARY LOGS BEFORE '2024-01-01 00:00:00';
  1

---

3.4 ビンログの内容を表示する

-- 创建一个名为 'simple_table' 的表
CREATE TABLE `simple_table` (
  `item_id` int(11) NOT NULL,  -- 项目编号
  `value` int(11) DEFAULT NULL,  -- 值
  `last_updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,  -- 最后更新时间
  PRIMARY KEY (`item_id`),  -- 设置项目编号为主键
  KEY `value_index` (`value`),  -- 为值字段创建索引
  KEY `update_time_index` (`last_updated`)  -- 为最后更新时间字段创建索引
) ENGINE=InnoDB;  -- 使用InnoDB存储引擎

-- 插入数据，将日期设置为当前日期
insert into `simple_table` values(1, 1, '2024-07-07');
insert into `simple_table` values(2, 2, '2024-07-07');
insert into `simple_table` values(3, 3, '2024-07-07');
insert into `simple_table` values(4, 4, '2024-07-07');
insert into `simple_table` values(5, 5, '2024-07-07');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

mysqlbinlog ツールを使用して、Bin Log の内容を表示します。

mysql> show binary logs;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    4
Current database: *** NONE ***

+---------------+-----------+
| Log_name      | File_size |
+---------------+-----------+
| binlog.000001 |      2411 |
+---------------+-----------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12

直接開くと文字化けします。

mysql> show binlog events in 'binlog.000001' from 0 limit 0,4G;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    6
Current database: *** NONE ***

*************************** 1. row ***************************
   Log_name: binlog.000001
        Pos: 4
 Event_type: Format_desc
  Server_id: 1
End_log_pos: 123
       Info: Server ver: 5.7.26-log, Binlog ver: 4
*************************** 2. row ***************************
   Log_name: binlog.000001
        Pos: 123
 Event_type: Previous_gtids
  Server_id: 1
End_log_pos: 154
       Info:
*************************** 3. row ***************************
   Log_name: binlog.000001
        Pos: 154
 Event_type: Anonymous_Gtid
  Server_id: 1
End_log_pos: 219
       Info: SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
*************************** 4. row ***************************
   Log_name: binlog.000001
        Pos: 219
 Event_type: Query
  Server_id: 1
End_log_pos: 765
       Info: use `d`; -- 创建一个名为 'simple_table' 的表
CREATE TABLE `simple_table` (
  `item_id` int(11) NOT NULL,  -- 项目编号
  `value` int(11) DEFAULT NULL,  -- 值
  `last_updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,  -- 最后更新时间
  PRIMARY KEY (`item_id`),  -- 设置项目编号为主键
  KEY `value_index` (`value`),  -- 为值字段创建索引
  KEY `update_time_index` (`last_updated`)  -- 为最后更新时间字段创建索引
) ENGINE=InnoDB
4 rows in set (0.01 sec)

ERROR:
No query specified

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47

3.5 mysqlbinlog ツールの使用

mysqlbinlog は、Bin Log ファイルを解析するために使用されるコマンド ライン ツールです。一般的なオプションは次のとおりです。

• --start-datetime：解析を開始する時刻を指定します。
• --stop-datetime: 解析が終了する時刻を指定します。
• --start-position：解析を開始する位置を指定します。
• --stop-position: 解析を終了する位置を指定します。

たとえば、特定の期間のビン ログを表示するには、次のようにします。

mysqlbinlog --start-datetime="2024-07-01 00:00:00" --stop-datetime="2024-07-01 12:00:00" binlog.000001
1

3.6 ビンログの解析と再生

データ回復のために、Bin Log イベントを MySQL サーバーに再生できます。

mysqlbin logbinlog.000001 | mysql -u root -p
1

---

4. レプリケーションでの Bin Log の適用

4.1 マスター/スレーブ レプリケーションの原理

MySQL のマスター/スレーブ レプリケーションの基本原理は、マスター データベースが Bin Log を記録し、スレーブ データベースがこれらのログを読み取って再生してデータ同期を実現することです。具体的な手順は次のとおりです。

1. メイン ライブラリは書き込み操作を実行し、これらの操作を Bin Log に記録します。
2. スレーブ ライブラリはメイン ライブラリに接続し、Bin Log を読み取り、それを自身のデータに適用します。

4.2 マスター/スレーブ レプリケーションの構成

1. メインライブラリを設定する：

   [mysqld]
   log-bin=mysql-bin
   server-id=1
   1
   2
   3

2. スレーブライブラリの設定：

   [mysqld]
   server-id=2
   relay-log=relay-bin
   1
   2
   3

3. プライマリ データベースにレプリケーション ユーザーを作成する：

   CREATE USER 'replica'@'%' IDENTIFIED BY 'password';
   GRANT REPLICATION SLAVE ON *.* TO 'replica'@'%';
   1
   2

4. スレーブデータベースでレプリケーションを構成する：

   CHANGE MASTER TO
       MASTER_HOST='主库IP',
       MASTER_USER='replica',
       MASTER_PASSWORD='password',
       MASTER_LOG_FILE='binlog.000001',
       MASTER_LOG_POS=0;
   START SLAVE;
   1
   2
   3
   4
   5
   6
   7

5. レプリケーションステータスを確認する：

   SHOW SLAVE STATUSG
   1

4.3 レプリケーションの監視と管理

レプリケーションのステータスは、次のコマンドで監視できます。

SHOW SLAVE STATUSG
1

一般的なステータス フィールドの説明:

• Slave_IO_Running: IO スレッドのステータス。
• Slave_SQL_Running:SQL スレッドのステータス。
• Seconds_Behind_Master: スレーブ ライブラリがマスター ライブラリから遅れている時間。

---

4.4 増分バックアップ

増分バックアップとは、最後の完全バックアップまたは最後の増分バックアップ以降のすべての変更をバックアップすることを意味します。増分バックアップは、Bin Log を使用して実行できます。

1. 完全バックアップを実行する：

   mysqldump --all-databases --master-data=2 > full_backup.sql
   1

2. 現在のビンログの位置を記録します：

   完全バックアップ ファイルで次の行を探します。

   -- CHANGE MASTER TO MASTER_LOG_FILE='binlog.000001', MASTER_LOG_POS=12345;
   1

3. バックアップ ビン ログ：

   mysqlbinlog --start-position=12345 binlog.000001 > incremental_backup.sql
   1

4.5 ビンログベースのリカバリ

1. 完全バックアップを復元する：

   mysql < full_backup.sql
   1

2. 増分バックアップを適用する：

   mysql < incremental_backup.sql
   1

---

5.最後に書く

マイグレーション Bin Logこれは、データ回復、レプリケーション、監査などのシナリオで広く使用されている強力なツールです。適切に設定して使用することで、Bin Logこれにより、MySQL データベースの信頼性と可用性が大幅に向上します。実際のアプリケーションでは、データベース管理者と開発者が Bin Log の使用スキルと最適化方法を習得することが重要です。