【MySQL学習】バイナリログ

はじめに

MySQLのバイナリログについて学習したことを簡単にまとめます。

検証に使用した環境

DB：MySQL8.0

バイナリログとは

テーブル作成やデータ変更といったデータベースの内容の変更に伴う「イベント」がロギングされます。

バイナリログは、ステートメントまたはトランザクションの完了後、すべてのロックがリリース、またはコミットするよりも前に実行して、コミット順の記録を保証します。

コミットなしのトランザクションの場合、COMMITステートメントが受け取るまでトランザクションテーブルへの更新をキャッシュし、COMMITが実行される前にトランザクション全体をバイナリログに書き込みます。

非トランザクションテーブルへの更新の場合は、実行後すぐにバイナリログに格納されます。

2つの目的

バイナリログには2つの目的があります。

レプリケーションを使用する上で、ソースからレプリカへのデータ変更のやり取りに使用

レプリケーションを使用している場合、ソースサーバー上のバイナリログを使用してレプリカにデータ変更のレコードを送信します。
そして、レプリカは受け取ったレコードからトランザクションを再現して、ソースと同様のデータ変更をします。

特定のデータリカバリ操作に使用する

バックアップがリストアされた後、バックアップ後に記録されたバイナリログ内のイベントを再実行し、バックアップポイントから最新の状態まで復元します。

バイナリログインデックスファイル

使用されたバイナリログファイルを追跡するためにバイナリログインデックスファイルも作成します。
デフォルトでは、これはバイナリログファイルと同じベース名を持ち、拡張子は'.index'です。
動作不良に繋がるため、mysqldの動作中にこのファイルを手動で変更してはいけません。

バイナリログインデックスファイルの名前は、--log-bin-index[=file_name] オプションで変更できます。

バイナリログファイルとバイナリログ

バイナリログファイルとは、データベースイベントを格納する、番号付けされた個々のファイルを指します。
バイナリログとは、バイナリログファイルとインデックスファイルのセットをひとまとめにしたものを指します。

出力先

バイナリログファイルとバイナリログインデックスファイルのデフォルトの場所は、データディレクトリ（デフォルト：/var/lib/mysql）です。
ベース名に先頭の絶対パス名を追加して別のディレクトリを指定することで、log-binを使用して別の場所を指定できます。
※log-binは後述

バイナリログファイルのベース名と指定されたパスは、log_bin_basename システム変数として使用できます。

バイナリログに記録されるイベントの形式

イベントの形式は、バイナリロギング形式に依存し、3つの形式タイプがあります。
使用されるバイナリロギング形式は、MySQLのバージョンに依存します。
MySQL8.0では行ベースロギングがデフォルトです。

行ベースロギング

binlog-formatにROWを指定し、サーバーを起動することで使用できます。
行ベースロギングでは、個々のテーブル行がどのように影響を受けるかを示すイベントをバイナリログに書き込みます。

ステートメントベースロギング

binlog-formatにSTATEMENTを指定し、サーバーを起動することで使用できます。
MySQLのレプリケーション機能は、元はソースからレプリカへのSQLステートメントの伝播に基づいていました。

混合ベースロギング

binlog-formatにMIXEDを指定し、サーバーを起動することで使用できます。
混合形式ロギングの場合、デフォルトではステートメントベースでロギングされますが、特定の状況ではロギングモードが自動的に行ベースに切り替わります。

システム変数

log_bin

バイナリロギングを有効にします。
※MySQL8.0ではデフォルトで有効

log-bin[=base_name]

log-bin[=base_name]を使用すると、 バイナリログファイルのベース名を指定できます。

log-binを指定してない場合、デフォルトのベース名としてbinlogを使用します。
しかし以前のリリースとの互換性のため、文字列なしまたは空の文字列を指定してlog-binを指定すると、
ベース名はホストマシンの名前を使用して「host_name-bin」となってしまいます。
ホスト名が変更されても、同じバイナリログファイル名を簡単に使用できるように、ベース名を指定することを推奨されています。

server_id

サーバーIDとして任意の番号を指定します。
※server_idを指定しないとサーバーが起動しません

MySQL8.0からは、デフォルトで1が設定されています。

max_binlog_size

バイナリログの最大サイズを指定します。
現在のログファイルサイズがこの値を超えた場合、サーバーは現在のファイルを閉じて、新しいものを開きます。（ローテーションという）

最大値およびデフォルト値は1Gバイトで最小値は4096バイトです。
また、暗号化バイナリログファイルには、max_binlog_size内で512バイトのヘッダー情報があります。

トランザクションによる一連の操作はバイナリログにひとまとまりで書き込まれるため、複数のバイナリログ間に分割されることはありません。
そのため、大きなトランザクションの場合、max_binlog_sizeより大きいバイナリログファイルが発生することもあります。

リレーログファイルの最大サイズを指定するmax_relay_log_sizeを0にしている場合、当変数の値がリレーログにも適用されます。

log_slave_updates

レプリケーションソースサーバーから受信した更新を、レプリカのバイナリログにも記録するようにする。
※デフォルトで有効

binlog_encryption

MySQL8.0.14からは、バイナリログファイルとリレーログファイルを暗号化できます。
暗号化することでupdate文やinsert文に含まれるパスワードなどを外部の攻撃者から保護できます。

暗号化するには、binlog_encryptionシステム変数をONに設定します。

binlog_encryption=ON

その他システム変数

こちら

バイナリログファイルの内容を表示する

mysqlbinlogユーティリティーを使用し、バイナリログファイルの内容を表示できます。
リカバリ操作のためにログ内のステートメントを再処理するときに利用します。

sh-4.2# mysqlbinlog binlog.000001
# The proper term is pseudo_replica_mode, but we use this compatibility alias
# to make the statement usable on server versions 8.0.24 and older.
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
・・・

バイナリログファイルを削除

RESET MASTERステートメントですべてのバイナリログファイルを削除したり、
PURGE BINARY LOGSでそのサブセットを削除したりすることができます。

手動でもファイルを削除することができますが、バイナリログインデックスファイルも安全に更新するためにも、PURGE BINARY LOGSの使用が推奨されています。

レプリケーションを使用している場合は、レプリカがバイナリログファイルを使用する必要がないことを確認するまで、ソース上の古いバイナリログファイルを削除するべきではありません。
たとえば、レプリカが3日を超えて実行されることがない場合、1日にmysqladmin flush-logsをソースで実行してから、3日以上経過したログを削除します。

注意

参照系の操作は出力されない

データを変更しないSELECTやSHOWなどのステートメントは出力されません。
問題となるクエリーを特定などには、一般クエリーログを使用します。

パフォーマンスの低下

バイナリログを有効にすると、サーバーのパフォーマンスはいくらか低下します。
しかし、MySQLではレプリケーションも使用できて、かつバイナリログはリストアに使用されるため、有効にしておくことの方が重要とされています。

出力先とバイナリログインデックスファイル内の相対パスの関係

サーバーは、使用されたバイナリログファイルを追跡するバイナリログインデックスファイルからエントリを読み取るときに、相対パスを含んでいるか確認します。
その場合、パスの相対部分は、--log-bin オプションを使用して設定された絶対パスに置き換えられます。
※バイナリログインデックスファイルに記録された絶対パスは変更されません。

このため、途中で出力先を変更していた場合、新しいパスを使用できるようにインデックスファイルを手動で編集する必要があります。

レプリケーションを使用する場合はサーバーIDを指定する

バイナリロギングが有効になっている場合、このデフォルト値でサーバーを起動できますが、server_idで明示的に指定しないと、情報メッセージが発行されます。
また、レプリケーショントポロジで使用されるサーバーの場合、サーバーごとに0以外の一意のサーバーIDを指定する必要があります。

参考文献