MariaDBのバイナリログ

以前、MariaDBでレプリケーションを作成しました。その際にバイナリログの出力をしました。

バイナリログは文字通りバイナリ、言い換えると人間には読むことのできない形式で保存されているログです。

これは前出のレプリケーションやデータの復旧時に用います。

今回は、MariaDBのバイナリログの設定項目を確認しながら、dumpデータとバイナリログを使ってロールフォワードでデータベースを復旧する場合の流れを確認します。

ログの種類の整理

MariaDBには多くの種類のログがあります。

それらのログは大きくわけてふたつに役目が分かれます。ひとつは人間が目視して稼働状態を確認するもの、もうひとつがデータベースが内部で用いるものです。

バイナリログは後者となっていて、プレーンテキストではないのでそのまま開いても人間が解読するのは難しいものとなっています。ただ、あとで紹介するようにバイナリログを可視化するツールもあります。

ここで、主なログの種類を整理しておきます。すべてのログがデフォルトで出力されるわけではありません。

プレーンテキストのログには次のようなものがあります。

general log
データベースにおけるあらゆるログを保存するログです。SELECT文を含むステートメントを誰がいつ発行したかといった記録などが入ります。

general logの出力や確認方法は以前の記事を参考にして下さい。
error_log
いわゆるエラーログです
slow query log
時間のかかかったクエリを記録しておくためのログです。テーブル設計の見直しなどパフォーマンス改善のために用います。

システム用のログには次のようなものがあります。

innodb log
Innodb Redo log はInnodbがテーブルデータを更新したりクラッシュ時に復旧に使うログです。
myisam log
MyIsamストレージエンジンにおいてのすべての変更が記録されるログです。
relay log
レプリケーションのレプリカが更新情報を保管しておくためのログファイルです。
binlog
binlog(バイナリログ)です。このあと詳しく説明していきます。

バイナリログ

バイナリログはデータベースへの変更の履歴を記録しておくログです。

これを使ってレプリケーションを生成したり、万が一の事体に備えてバックアップとして保存しておいたり、変更履歴を後から確認したりすることができます。

レプリケーションについては、先のリンク先で紹介していますので、ここではバックアップ用途と履歴確認の方法を紹介していきたいと思います。

まず、バイナリログはMariaDBではデフォルトでは出力されません。

設定ファイルの log_bin の項目に保存先を指定する事で出力されるようになります。

バイナリログは複数のファイルに分割されて保存されますが、そのサイズの目安を決めるのが max_binlog_size です。デフォルトは1Gとなっていてこれは指定可能な最大の値です。

変更の履歴を残すバイナリログは、データベースが存在し続ける限り増え続けます。そのため、保持期限を決めておく必要があります。その日数をしていするのが expire_log_days となります。

binlog_format はバイナリログのフォーマットです。STATEMENT を指定するとSQLステートメントを記録します。これはサイズが小さく押さえられますが、不整合が起きる可能性があります。

そのため行単位で変更データを記録する ROW というモードど、両方を用いる MIXED というモードがあります。

サイズの問題が心配なら log_bin_compress というバイナリデータを圧縮するパラメータも存在します。ただし圧縮作業のオーバーヘッドが発生します。

50-server.cnf

log-bin = /var/log/mysql/mysql-bin.log
expire_log_days = 10
binlog_format=MIXED
# ログの圧縮をしたい場合
# log_bin_compress=1

バックアップ用の構成

有事の際に復元できるように、バックアップ用の構成にする場合はバイナリログとは別にdumpを定期実行しなければいけません。

たとえば1日1回、サーバーの負荷の少ない時間帯やメンテナンス時間を設けてdumpをとります。

障害が発生した場合は直近のdumpデータと、そこから障害発生時間までのバイナリログを使って障害発生直前の状態に戻します。

注意が必要なのは、dumpデータとバイナリデータの出力場所です。

通常のデータベースと同じディスク上にある場合、ディスク障害が発生した時にはバックアップデータまで失われますので適切にコピーをするか、または最初から別のディスクに出力しておくようにします。

レプリケーションを用いた場合と、この形式でバックアップを保存した場合の大きな違いはつぎのような点です。

たとえば間違えて意図しないテーブルの truncate を実行してしまった場合、レプリケーションでは変更が伝わってしまえば元には戻せません。が、dumpデータとバイナリログを使えば、誤ってSQLを実行する前の状態へ戻すことができます。

一方で、ディスク障害等が発生した場合は、レプリケーションを構築していればデータベース参照先を差し替えるだけで復旧できるのに対し、dumpデータからの復旧をする場合は作業時間が必要です。

mariadb-binlog

mariadb-binlogは、古いバージョンだと、mysqlbinlog となります。これはバイナリログを読み込むためのツールで、データベースのインストール時にデフォルトで含まれます。

基本的な使い方はとして、まずバイナリログを出力する方法を紹介します。

バイナリログを画面へ出力するには、mysqlbinlog(mariadb-binlog) コマンドにバイナリログのファイルパスを指定するだけです。

# mysqlbinlog mysql-bin.000123
mysqlbinlog: unknown variable 'default-character-set=utf8mb4'

mysqlbinlog: unknown variable から始まるエラーがでたら、my.cnf や 50-client.cnf が影響しています。

mysqlbinlog のオプションはリンク先に表示されたものがあります。これらは先のコマンド上でファイル名の前に指定できる他、my.cnfなどの設定ファイルにも記述できます。

その際、[mysqlbinlog],[mariadb-binlog],[client],[client-server],[client-mariadb]のセクションにある項目を抽出しようとします。

ドキュメントには不明なオプションがある場合は無視されますという表記がありましたが、先の場合は client セクションの default-character-set の記述が引っかかりエラーとなってしまいました。

適用されるオプションの確認をするには --print-defaults を使います。

mysqlbinlog --print-defaults
mysqlbinlog would have been started with the following arguments:
--default-character-set=utf8mb4 --socket=/var/run/mysqld/mysqld.sock

こうしてみることによっても、default-character-set の指定を読み込んでいるのがわかります。

これは/etc/mysql/my.cnfを読み込んでしまうのが原因です。--defaults-file=で利用するオプションを記述した(先に紹介した有効なセクション名を忘れずにセットする必要があります)ファイルを指定するか、--no-defaultsでオプションファイルを読み込まない設定にします。

実際にログを表示してみると次のようになります。ここには含まれていませんが、SQLステートメントやバイナリデータが存在すれば、それらも出力されます。

# at 104873220
#230404 16:15:25 server id 1001  end_log_pos 104873251 CRC32 0x8fb7c94e         Xid = 2486657
COMMIT/*!*/;
# at 104873251
#230404 16:15:25 server id 1001  end_log_pos 104873298 CRC32 0x6c15c3c7         Rotate to mysql-bin.010764  pos: 4
DELIMITER ;

先に紹介したオプションの中にはポジションを指定する --start-position や --end-position や時刻を指定する --start-datetime や --end-datetime があります。

それらのオプションで指定するのが上記ログの下線部の部分です。# at から始まるものはポジション、#230404から始まるのは日時(2023/04/04) となっています。

ディスク障害が発生した場合ならdumpした後のバイナリログから存在するものをすべて適用させればいいですが、truncate 後など特定のSQLを発行する直前までとなると、中身を確認しながら時刻かポジションで適用範囲を見定める必要があります。

mysqlbinlog の結果はパイプでそのまま mysql へ渡すことができます。この時バイナリログを指定はスペースで区切って複数にすることも可能です。

mysqlbinlog mysql-bin.010765 | mysql -u root -p
...
mysqlbinlog mysql-bin.010766 mysql-bin.010767 | mysql -u root -p

また、-r で別のファイルに保存する事ができ、必要な場合は値に編集を加えてから通常のSQL文が記述されたファイル同様にmysqlに渡すことができます。

先の例で言えば、出力されたファイルの中から truncate文を取り除きます。

mysqlbinlog -r exp.sql mysql-bin.010765
...

mysql -u root -p<exp.sql

特定のテーブルに対して変更を加えた時のバイナリログを確認したい時 --tableオプションがありそれにテーブル名を指定すれば良さそうなのですが、うまくいきませんでした。

代替手段として、コンバート後のデータに grep 通してポジションを取得します。

mysqlbinlog --no-defaults mysql-bin.010765 | grep -B 3 TABLE_NAME

テーブル名が含まれる行には日付やポジションは含まれないので -B 3 を指定して3行前から取得しています。

-r オプションの代わりにリダイレクトを使えば複数ログのアペンドも可能です。

mysqlbinlog mysql-bin.010765>exp.sql
...
mysqlbinlog mysql-bin.010766>>exp.sql

mysql -u root -p<exp.sql

このような感じで復旧の為のデータを作成し、データを流しこむ作業をします。実際の作業では bind-addressになどを指定して他からアクセスできない状態にして行う必要があります。bind-addressを127.0.0.1にしても、同じサーバーWebアプリなどが配置されていたいるすると更新できてしまいますので注意してください。

投稿:2023/04/04 - 更新: