🐷

mysqlbinlog 使い方(取得から適用まで)

2024/05/30に公開

AWS

概要

最近業務でmysqlbinlogを用いて、binlogの取得、binlogから特定日時の範囲だけ取得、適用が必要な箇所の確認、復元したDBにbinlog適用、といった対応を行ったのでインターネットの方にも自分がやったことを晒しておこうかなと。mysqlの公式ドキュメントはボリューミーで見るのがなかなか大変ですしね。

公式ドキュメント

mysqlの公式ドキュメントはボリューミーで見るのがなかなか大変ですしね。

とはいえやっぱり詳細の確認、信頼できるのは公式ですよ。
今回は以下のページあたりの内容をかいつまんで記載してます。
4.6.8 mysqlbinlog — バイナリログファイルを処理するためのユーティリティー
 7.5.2 イベントの位置を使用したポイントインタイムリカバリ

前提

DBがリモートにある環境の内容です。
RDS(Aurora)使ってるよ、ってことです。（これによってコマンドが少しだけ変わるので）
Auroraの復元についての内容は記載してません。mysqlbinlogの扱いについてのみ。
binlogの出力設定についての説明はこの記事では割愛します。
上記で公式docのリンク貼ってますが、正直部分的にしか読んでないです。なので以降に紹介する内容は、あくまで私はこれでできたよ、位の内容です。利用されてる環境によってコマンドに必要なオプションが変わってくる事は大いにあると思いますが、その辺り広くカバーはしてない内容になりますのでご了承を。。

コマンド

とりあえずコマンド例を書きます。
以下全て、Auroraのライターインスタンスに接続できる環境(ec2など)での実行です。
{} で囲ってる値は環境、利用状況によって異なる値です。

binlogのファイル名確認

BINLOG_FILENAME=$(mysql -u {DB_USER} -p{DB_PASSWD} -h {DB_ENDPOINT} -e "show master logs" | grep "mysql-bin" |awk '{print $1}')

binlog取得(Aurora -> ec2上に保存)

mysqlbinlog -u {DB_USER} -p{DB_PASSWD} -h {DB_ENDPOINT} --raw --read-from-remote-server {BINLOG_FILENAME} --result-file={FILE_NAME}

特定日時の範囲分のみ出力

mysqlbinlog --start-datetime="yyyy-mm-dd HH:MM:ss" --stop-datetime="yyyy-mm-dd HH:MM:ss" --verbose {FILE_NAME}

適用箇所の確認

{3のコマンド} | grep -A 1 "# at" | head
{3のコマンド} | grep -A 1 "# at" | tail

適用するbinlogの確認

mysqlbinlog --start-position={START_POSITION} --stop-position={STOP_POSITION} {FILE_NAME}

binlogの適用

mysqlbinlog --start-position={START_POSITION} --stop-position={STOP_POSITION} {FILE_NAME} | mysql -u {DB_USER} -p{DB_PASSWD} -h {DB_ENDPOINT}

コマンド実行内容の説明

では端的に実施コマンド例を書いたところで次はそれぞれのコマンドの説明を簡単にしていきます。

1. binlogのファイル名確認

BINLOG_FILENAME=$(mysql -u {DB_USER} -p{DB_PASSWD} -h {DB_ENDPOINT} -e "show master logs" | grep "mysql-bin" |awk '{print $1}')

Auroraインスタンス上で出力されてるbinlogのファイル名確認です。
Auroraインスタンスに入ってから、SHOW MASTER LOGS; とやってるのと同じです。
grepとかawkつけて接続元からのコマンド実行でファイル名だけの取得を試みてるものです。

ちなみにAWSさんの方で、binlogをcronとシェルスクリプトを使って定期的に取得してs3に保存してみよう、といった記事があります。

記事内にあるbinlog取得のスクリプト

私もこの記事のように、シェルスクリプトとcronで定期的にbinlog取得の設定をしました。
このbinlogファイル名の確認コマンドも上記のbinlog取得のスクリプトから抜き出したものです。

2. binlog取得(Aurora -> ec2上に保存)

mysqlbinlog -u {DB_USER} -p{DB_PASSWD} -h {DB_ENDPOINT} --raw --read-from-remote-server {BINLOG_FILENAME} --result-file={FILE_NAME}

binlogをAuroraインスタンスからec2などのコマンド実行環境に持ってくる際のコマンドです。

--raw: これは基本付けた方がいいと思います。
イベントを生の (バイナリ) 形式で出力ファイルに書き込み、というオプションです。
試してる中でrawを付けないで取得したログを適用しようとしたらエラーになった、という事もあったのでbinlog取得時は基本付けましょう。

{BINLOG_FILENAME}: 1. で確認したbinlogのファイル名を指定してください。
--read-from-remote-server このオプションでリモートにあるサーバー、今回でいうAuroraインスタンスのようなサーバーからもログ取得できます。

{FILE_NAME}: ローカルに保存するファイル名です。任意に設定すればいいやつです。

3. 特定日時の範囲分のみ出力

mysqlbinlog --start-datetime="yyyy-mm-dd HH:MM:ss" --stop-datetime="yyyy-mm-dd HH:MM:ss" --verbose {FILE_NAME}

特定日時の範囲分だけ出力して、大雑把に直近のログが入ってるよな、と確認する意図のものです。

2.で取得したファイルはbinlogの全量です。
なのでそこから必要な範囲のみ日時でフィルターして、かつ見やすく出力してます。

ここで「？」と思った方がいるかもしれません。
2. のコマンドの時点で --start-datetime, --stop-datetime といったオプションを付けて取得するのでも問題ありません。コマンド長くなるので、今回は分けてるだけです。

--verbose: 行イベントを SQL ステートメントとして再構築というオプションです。
これは「binlogの可読性を上げる」といったイメージのものなので、binlog取得時は付けない方がいいはずです。

私が携わってる環境ではAuroraは日次でスナップショットを取得してて、binlogの全量を使う想定は基本無くて、スナップショット以降のデータ埋めに利用される想定です。
なのでスナップショット取得日時以降の日時範囲を指定するようなイメージで実施してました。

4. 適用箇所の確認

開始位置の確認 "# at"に続く番号を確認

{3のコマンド} | grep -A 1 "# at" | head

終了位置の確認 "end_log_pos"に続く番号を確認

{3のコマンド} | grep -A 1 "# at" | tail

**これが一番重要？**な気もします。

binlogの特定の範囲分だけを適用する際には位置情報(オフセット)が必要です。
その位置情報を確認するためにログの必要部分だけを出力するコマンドです。

{3のコマンド}: "3. 特定日時の範囲分のみ出力" のコマンドのことです。

ここで、binlogの内容について少し記載します。
binlogのサンプルも貼ってみたので、それと見比べていただくと何となく位置については分かっていただけるかなと。

“# at xxx”: イベントの開始位置
"#240530 6:40:04": イベントの開始日時
“end_log_pos xxx”: 次のイベントの開始位置
“exec_time”: イベントの実行時間
“error_code=0”: イベントの実行結果、エラーなし

このbinlogサンプルでいうと、
開始位置: 9206084("# at 9206084")
終了位置: 9206456("end_log_pos 9206456")
として、後の適用コマンド実施によりログ内のトランザクションが適用できます。

binlogサンプル

# at 9206084
#240530  6:40:04 server id 425211111  end_log_pos 9206184 CRC32 0xf45725ea      Query   thread_id=689010        exec_time=0     error_code=0
SET TIMESTAMP=1717018804/*!*/;
BEGIN
/*!*/;
# at 9206184
#240530  6:40:04 server id 425211111  end_log_pos 9206425 CRC32 0xf8ce2916      Query   thread_id=689010        exec_time=0     error_code=0
SET TIMESTAMP=1717018804/*!*/;
UPDATE abc.results SET is_abc=0, abc_facility=0.181, status=2 WHERE abc.results.id = '123abc456'
/*!*/;
# at 9206425
#240530  6:40:04 server id 425211111  end_log_pos 9206456 CRC32 0x00e00e01      Xid = 61454943
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file

ちなみに以下の出力サンプルが、{3のコマンド} | grep -A 1 "# at" | tail といったコマンドを実行した際の出力例です。
これでイベントの日時と必要な位置情報だけ確認できるので見やすくなるかなと思います。

"## 3. 特定日時の範囲分のみ出力"でざーっと必要なログがファイル内にあるよな、と確認して、
"## 4. 適用箇所の確認"で後のコマンドで必要な情報のみを取り出す、といったイメージです。

# at 9206084
#240530  6:40:04 server id 425211111  end_log_pos 9206184 CRC32 0xf45725ea      Query   thread_id=689010        exec_time=0     error_code=0
--
# at 9206184
#240530  6:40:04 server id 425211111  end_log_pos 9206425 CRC32 0xf8ce2916      Query   thread_id=689010        exec_time=0     error_code=0
--
# at 9206425
#240530  6:40:04 server id 425211111  end_log_pos 9206456 CRC32 0x00e00e01      Xid = 61454943

grep, head, tail の部分は都合の良いようにコマンドを置き換えてもらって問題ない部分です。

5. 適用するbinlogの確認

mysqlbinlog --start-position={START_POSITION} --stop-position={STOP_POSITION} {FILE_NAME}

ここは必須ではないですが、いざ適用となるときっと緊張するもんですよね。
なので最後に適用するbinlogをザーッと眺めるためのプロセスです。

{START_POSITION}: "## 4. 適用箇所の確認"で確認した開始位置
{STOP_POSITION}: "## 4. 適用箇所の確認"で確認した終了位置

あと一つポイント
--verbose を入れてないことです。
これは上述してますが、ログを見やすくするのに付けてるようなものなので、verbose無しでバイナリ形式のログを適用するようにしましょう。

6. binlogの適用

mysqlbinlog --start-position={START_POSITION} --stop-position={STOP_POSITION} {FILE_NAME} | mysql -u {DB_USER} -p{DB_PASSWD} -h {DB_ENDPOINT}

最後にbinlogの適用コマンドです。
5. のコマンドをパイプで繋いでDBに入れ込むといったものです。
"<" とか使っても同じだと思います。
公式では上記のパイプを使った方式が紹介されてたのでこのようにしました。

適用自体はまぁあっけなく終わります。

さいごに

以上でbinlogの取得から適用までに実施するコマンド及びbinlogについて記載しました。

mysqlbinlogのコマンドオプションはまだまだ大量にありますし、利用環境によっては他にも色々と認識すべきことはあるかもしれません。
なので結局はやはり公式docと向き合う事は必要になるかなとは思います。

ただ、今回紹介した必要最低限のコマンドオプションによる実行例でも対応できる場合はありますので参考にしていただけたら幸いです。