MySQL binlog_ignore_dbパラメータの具体的な使用法

序文：

前の記事を読んだ後、binlog はデータベースで実行されたすべての DDL および DML ステートメント (select や show などのデータクエリステートメントを除く) を記録することがわかりました。デフォルトでは、すべてのライブラリ操作が記録されることに注意してください。特定のライブラリのみに binlog の記録を許可したり、特定のライブラリを binlog の記録から除外したりするなどの代替要件がある場合、そのような要件はサポートされますか?この記事を一緒に見てみましょう。

1. binlog_do_db と binlog_ignore_db

データベースインスタンスに対して binlog が有効になっている場合は、show master status コマンドを実行し、Binlog_Do_DB および Binlog_Ignore_DB オプションを確認します。

mysql> マスターステータスを表示します。
+---------------+----------+-------------+------------------+-------------------+
| ファイル | 位置 | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+-------------+------------------+-------------------+
| binlog.000009 | 282838 | | | |
+---------------+----------+-------------+------------------+-------------------+

デフォルトでは、これら 2 つのオプションは空ですが、これら 2 つのパラメーターは何をするのでしょうか?文字通りの意味としては、一方は特定のライブラリにのみバイナリログの記録を許可し、もう一方は特定のライブラリにバイナリログの記録を除外するということでしょうか?著者は公式ドキュメントを参照し、これら 2 つのパラメータの機能について簡単に説明しました。

binlog_do_db: このパラメータは、指定されたデータベースのバイナリログのみが記録されることを意味します。デフォルトでは、すべてのログが記録されます。
binlog_ignore_db: このパラメータは、指定されたデータベースのバイナリログが記録されないことを示します。

これら 2 つのパラメータは相互に排他的です。通常は、そのうちの 1 つだけが選択され、起動コマンドラインまたは構成ファイルにのみ追加できます。次のように、複数のデータベースを別々の行に書き込むように指定します。

# binlogを記録するdb1 db2を指定します
[mysqld]
binlog_do_db = db1
binlog_do_db = db2

# db3 と db4 が binlog を記録しないようにする
[mysqld]
binlog_ignore_db = db3
binlog_ignore_db = db4

さらに、これら 2 つのパラメータの具体的な効果は、binlog 形式にも関連しています。場合によっては、binlog 形式を STATEMENT または ROW に設定すると効果が異なります。実際のアプリケーションでは、binlog_ignore_db がより広く使用されています。たとえば、特定のデータベースのデータがそれほど重要でない場合は、サーバーの書き込み負荷を軽減するために、データベースに binlog を記録させない場合があります。 binlog_ignore_db を設定するとスレーブ同期エラーが発生するというネット記事もありますが、このパラメータを設定するとどのような効果があるのでしょうか。具体的に実験してみましょう。

2. binlog_ignore_dbの具体的な効果

まず、私のテストデータベースインスタンスは 5.7.23 コミュニティバージョンで、ビジネスデータベースは testdb と logdb の 2 つです。logdb は binlog を記録しないように設定しました。具体的な実験をしてみましょう。

# binlogはROW形式です

 # 1. use db を使用しない
mysql> マスターステータスを表示します。
+---------------+----------+-------------+------------------+-------------------+
| ファイル | 位置 | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+-------------+------------------+-------------------+
| binlog.000011 | 154 | | logdb | |
+---------------+----------+-------------+------------------+-------------------+
mysql> データベースを選択します();
+------------+
| データベース() |
+------------+
| NULL |
+------------+
セット内の 1 行 (0.00 秒)
mysql> テーブル testdb.`test_tb1` を作成します ( id int 、 name varchar(30) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
クエリは正常、影響を受けた行は 0 行 (0.06 秒)

mysql> testdb.test_tb1 に値 (1001、'sdfde') を挿入します。
クエリは正常、1 行が影響を受けました (0.01 秒)

mysql> マスターステータスを表示します。
+---------------+----------+-------------+------------------+-------------------+
| ファイル | 位置 | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+-------------+------------------+-------------------+
| binlog.000011 | 653 | | logdb | |
+---------------+----------+-------------+------------------+-------------------+
セット内の 1 行 (0.00 秒)

mysql> CREATE TABLE logdb.`log_tb1` ( id int 、 name varchar(30) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
クエリは正常、影響を受けた行は 0 行 (0.05 秒)

mysql> logdb.log_tb1 に値 (1001、'sdfde') を挿入します。
クエリは正常、1 行が影響を受けました (0.00 秒)

mysql> マスターステータスを表示します。
+---------------+----------+-------------+------------------+-------------------+
| ファイル | 位置 | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+-------------+------------------+-------------------+
| binlog.000011 | 883 | | logdb | |
+---------------+----------+-------------+------------------+-------------------+
mysql> logdb.log_tb1 に値 (1002、'sdsdfde') を挿入します。
 クエリは正常、1 行が影響を受けました (0.01 秒)

mysql> マスターステータスを表示します。
+---------------+----------+-------------+------------------+-------------------+
| ファイル | 位置 | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+-------------+------------------+-------------------+
| binlog.000011 | 883 | | logdb | |
+---------------+----------+-------------+------------------+-------------------+

mysql> テーブル logdb.log_tb1 を変更し、列 c3 varchar(20) を追加します。
   クエリは正常、影響を受けた行は 0 行 (0.12 秒)
レコード: 0 重複: 0 警告: 0

mysql> マスターステータスを表示します。
+---------------+----------+-------------+------------------+-------------------+
| ファイル | 位置 | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+-------------+------------------+-------------------+
| binlog.000011 | 1070 | | logdb | |
+---------------+----------+-------------+------------------+-------------------+
# 結論: 他のライブラリは正常に記録します。logdb ライブラリは DDL を記録しますが、DML は記録しません。

# 2. データベース間で use testdb を使用します。mysql> use testdb;
テーブル名と列名の補完のためのテーブル情報の読み取り
-Aでこの機能をオフにすると起動が速くなります。

データベースが変更されました
mysql> データベースを選択します();
+------------+
| データベース() |
+------------+
|テストデータベース|
+------------+
セット内の 1 行 (0.00 秒)

mysql> マスターステータスを表示します。
+---------------+----------+-------------+------------------+-------------------+
| ファイル | 位置 | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+-------------+------------------+-------------------+
| binlog.000011 | 1070 | | logdb | |
+---------------+----------+-------------+------------------+-------------------+
セット内の 1 行 (0.00 秒)

mysql> テーブル `test_tb2` を作成します ( id int 、 name varchar(30) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
クエリは正常、影響を受けた行は 0 行 (0.05 秒)

mysql> test_tb2 に値 (1001、'sdfde') を挿入します。
クエリは正常、1 行が影響を受けました (0.04 秒)

mysql> マスターステータスを表示します。
+---------------+----------+-------------+------------------+-------------------+
| ファイル | 位置 | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+-------------+------------------+-------------------+
| binlog.000011 | 1574 | | logdb | |
+---------------+----------+-------------+------------------+-------------------+
セット内の 1 行 (0.00 秒)

mysql> CREATE TABLE logdb.`log_tb2` ( id int 、 name varchar(30) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
クエリは正常、影響を受けた行は 0 行 (0.05 秒)

mysql> マスターステータスを表示します。
+---------------+----------+-------------+------------------+-------------------+
| ファイル | 位置 | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+-------------+------------------+-------------------+
| binlog.000011 | 1810 | | logdb | |
+---------------+----------+-------------+------------------+-------------------+
セット内の 1 行 (0.00 秒)

mysql> logdb.log_tb2 に値 (1001、'sdfde') を挿入します。
クエリは正常、1 行が影響を受けました (0.01 秒)

mysql> マスターステータスを表示します。
+---------------+----------+-------------+------------------+-------------------+
| ファイル | 位置 | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+-------------+------------------+-------------------+
| binlog.000011 | 1810 | | logdb | |
+---------------+----------+-------------+------------------+-------------------+
セット内の 1 行 (0.00 秒)
# 結論: logdbライブラリはDDLも記録しますが、DMLは記録しません 

# 3. データベース間で use logdb を使用しますmysql> use logdb;
テーブル名と列名の補完のためのテーブル情報の読み取り
-Aでこの機能をオフにすると起動が速くなります。

データベースが変更されました
mysql> データベースを選択します();
+------------+
| データベース() |
+------------+
| ログデータベース |
+------------+
セット内の 1 行 (0.00 秒)

mysql> マスターステータスを表示します。
+---------------+----------+-------------+------------------+-------------------+
| ファイル | 位置 | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+-------------+------------------+-------------------+
| binlog.000011 | 1810 | | logdb | |
+---------------+----------+-------------+------------------+-------------------+
セット内の 1 行 (0.00 秒)

mysql> テーブル testdb.`test_tb3` を作成します ( id int 、 name varchar(30) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
クエリは正常、影響を受けた行は 0 行 (0.23 秒)

mysql> マスターステータスを表示します。
+---------------+----------+-------------+------------------+-------------------+
| ファイル | 位置 | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+-------------+------------------+-------------------+
| binlog.000011 | 1810 | | logdb | |
+---------------+----------+-------------+------------------+-------------------+
セット内の 1 行 (0.00 秒)

mysql> testdb.test_tb3 に値 (1001、'sdfde') を挿入します。
クエリは正常、1 行が影響を受けました (0.02 秒)

mysql> マスターステータスを表示します。
+---------------+----------+-------------+------------------+-------------------+
| ファイル | 位置 | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+-------------+------------------+-------------------+
| binlog.000011 | 2081 | | logdb | |
+---------------+----------+-------------+------------------+-------------------+
セット内の 1 行 (0.00 秒)

mysql> テーブル `log_tb3` を作成します ( id int 、 name varchar(30) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
クエリは正常、影響を受けた行は 0 行 (0.05 秒)

mysql> マスターステータスを表示します。
+---------------+----------+-------------+------------------+-------------------+
| ファイル | 位置 | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+-------------+------------------+-------------------+
| binlog.000011 | 2081 | | logdb | |
+---------------+----------+-------------+------------------+-------------------+
セット内の 1 行 (0.00 秒)

mysql> log_tb3 に値 (1001、'sdfde') を挿入します。
クエリは正常、1 行が影響を受けました (0.02 秒)

mysql> マスターステータスを表示します。
+---------------+----------+-------------+------------------+-------------------+
| ファイル | 位置 | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+-------------+------------------+-------------------+
| binlog.000011 | 2081 | | logdb | |
+---------------+----------+-------------+------------------+-------------------+
セット内の 1 行 (0.00 秒)
# 結論: logdbは他のライブラリのDDLを記録しません

# 4. 各操作はこのデータベースに入り、データベース間を行き来しませんmysql> use testdb;
テーブル名と列名の補完のためのテーブル情報の読み取り
-Aでこの機能をオフにすると起動が速くなります。

データベースが変更されました
mysql> マスターステータスを表示します。
+---------------+----------+-------------+------------------+-------------------+
| ファイル | 位置 | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+-------------+------------------+-------------------+
| binlog.000011 | 2081 | | logdb | |
+---------------+----------+-------------+------------------+-------------------+
セット内の 1 行 (0.00 秒)

mysql> テーブル `test_tb4` を作成します ( id int 、 name varchar(30) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
クエリは正常、影響を受けた行は 0 行 (0.05 秒)

mysql> test_tb4 に値 (1001,'sdfde') を挿入します。
クエリは正常、1 行が影響を受けました (0.01 秒)

mysql> マスターステータスを表示します。
+---------------+----------+-------------+------------------+-------------------+
| ファイル | 位置 | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+-------------+------------------+-------------------+
| binlog.000011 | 2585 | | logdb | |
+---------------+----------+-------------+------------------+-------------------+
セット内の 1 行 (0.00 秒)

mysql> logdb を使用します。
テーブル名と列名の補完のためのテーブル情報の読み取り
-Aでこの機能をオフにすると起動が速くなります。

データベースが変更されました
mysql> テーブル `log_tb4` を作成します ( id int 、 name varchar(30) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
クエリは正常、影響を受けた行は 0 行 (0.04 秒)

mysql> マスターステータスを表示します。
+---------------+----------+-------------+------------------+-------------------+
| ファイル | 位置 | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+-------------+------------------+-------------------+
| binlog.000011 | 2585 | | logdb | |
+---------------+----------+-------------+------------------+-------------------+
セット内の 1 行 (0.00 秒)

mysql> log_tb4 に値 (1001、'sdfde') を挿入します。
クエリは正常、1 行が影響を受けました (0.01 秒)

mysql> マスターステータスを表示します。
+---------------+----------+-------------+------------------+-------------------+
| ファイル | 位置 | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+-------------+------------------+-------------------+
| binlog.000011 | 2585 | | logdb | |
+---------------+----------+-------------+------------------+-------------------+
セット内の 1 行 (0.00 秒)
# 結論: 他のライブラリはすべて記録しますが、logdbは記録しません

同様に、binlog 形式を STATEMENT に設定して再度テストします。ここではテストプロセスの詳細については説明しませんが、STATEMENT 形式での実験結果を要約します。

操作対象のデータベースが選択されなかったため、すべてがログに記録されます。
testdb を選択し、それぞれ testdb と logdb に対して操作を実行します。すべてのライブラリが記録されます。
logdb を選択し、それぞれ testdb と logdb を操作します。すべてのライブラリが記録されるわけではありません。
ライブラリを選択し、現在のライブラリのみ操作した場合は、記録は通常通りとなり、logdb は記録されません。

こんなにたくさんの実験データに圧倒されてしまいましたか? 次のようにマインドマップの形でまとめてみましょう。

binlog_ignore_db パラメータの効果は、確かに多くの要因に関係しているようです。特にスレーブライブラリがある場合、マスターライブラリは、マスタースレーブ同期エラーが発生しやすいため、このパラメータを使用するときは特に注意する必要があります。ただし、厳格な標準に従い、現在のデータベースのみを操作する場合は、問題は発生しません。これは、標準に厳密に従い、ビジネスアカウントに単一のデータベースに対する権限のみを付与する必要があることも示しており、これによりさまざまな問題を回避できます。

要約:

読者の皆さんは、このようなパラメータを紹介する記事に興味があるでしょうか?おそらく、これらはデータベースの運用および保守担当者が最も懸念していることです。この記事では、主に binlog の binlog_ignore_db パラメータの具体的な役割について紹介します。この記事の実験環境は十分に包括的ではない可能性があります。興味のある学生は、公式ドキュメントを参照して、このパラメータについてより深く理解することができます。

上記はMySQL binlogパラメータの使用に関する詳細な内容です。MySQL binlogパラメータの詳細については、123WORDPRESS.COMの他の関連記事をご覧ください。

以下もご興味があるかもしれません: