今、MySQLのバックアップを作り直すとしたら何がどう良いのかを考える旅

2025/11/14 YAPC::Fukuoka 2025
https://fortee.jp/yapc-fukuoka-2025/proposal/1ee39cf1-a581-450d-8692-a9a9f7f39b7f

Transcript

1. 今、MySQLのバックアップを作り直すとしたら何がどう 良いのかを考える旅 とある デジタル庁におけるガバメントクラウド整備のためのクラウドサービスの提供（令和5年度募集）の 調 達仕様書 2025/11/14 yoku0825
2. TL;DR MySQLのバックアップは結局xtrabackup + バイナリログが鉄板 でもmysqlshもかなりいいよ、フルバックアップも増分バックアップもmysqlshコマンドで取れるよ 日々の覚書: 最新のMySQL ShellだけでMySQLのPITR可能なバックアップを設定する(フルバックアップ編) ‐ 日々の覚書: 最新のMySQL ShellだけでMySQLのPITR可能なバックアップを設定する(増分バックアップ編) ‐ 日々の覚書: 最新のMySQL ShellだけでMySQLのPITR可能なバックアップを設定する(実践編) ‐ yoku0825/simple-mysqlsh-pitr: PoC of PITR only using MySQL Shell ‐ 1/45
3. ＼こんにちは／ yoku0825＠とある企業のDBAだったもの オラクれない ‐ ポスグれない ‐ マイエスキューエる ‐ 生息域 Twitterだったもの: @yoku0825 ‐ Blog: 日々の覚書 ‐ 日本MySQLユーザ会副代表 ‐ MySQL Casual ‐ 2/45
4. 文脈 https://x.com/pyama86/status/1928256618367746548 3/45
5. とある調達仕様書 デジタル庁におけるガバメントクラウド整備のためのクラウドサービスの提供（令和5年度募集）｜デジ 4/45
6. おことわり 個人の発表であり、会社の意見を代表するわけがありません 就職前に考えていたことからPoCの手前あたりまでのコードなので、実際のサービスとは異なるかも しれませんし同じところもあるかもしれません 参考: PITR 機能 | さくらのクラウド マニュアル 5/45
7. というか 6/45
8. というか 7/45
9. とある調達仕様書の別紙 調達仕様書 別紙1（基本事項及びマネージドサービスの技術要件詳細）（Excel／73KB） ここ からリンクされています 8/45
10. とある調達仕様書の別紙 （12）バックアップの項目「RDB」 9/45
11. とある調達仕様書の別紙 DBのバックアップを差分で取得できること。取得したバックアップはいつでもDBの復元に使用でき ること。復元時は別のサイズを選択できること。 DBのバックアップは、一定期間（例：5分）以前のどの時点にでも戻せるかたちで（Point In Timer Restore）取得できること。 DBのバックアップは、予め設定したタイミング、周期、世代で自動的に定期実行できること。 DBのバックアップイメージは、CSPの中であれば、別のシステム、別のネットワーク、別の環境から も利用できること。 CSPの外部で作成されたDBデータをインポートできること。 10/45
12. とある調達仕様書の別紙 DBのバックアップを差分で取得できること。 取得したバックアップはいつでもDBの復元に使用 できること。復元時は別のサイズを選択できること。 DBのバックアップは、一定期間（例：5分）以前の どの時点にでも戻せるかたちで（Point In Timer Restore）取得できること。 DBのバックアップは、予め設定したタイミング、周期、世代で自動的に定期実行できること。 DBのバックアップイメージは、CSPの中であれば、別のシステム、別のネットワーク、別の環境から も利用できること。 CSPの外部で作成されたDBデータをインポートできること。 11/45
13. 差分バックアップ A differential backup is a type of data backup that preserves data, saving only the difference in the data since the last full backup. Differential backup - Wikipedia 12/45
14. 増分バックアップ An incremental backup is one in which successive copies of the data contain only the portion that has changed since the preceding backup copy was made. Incremental backup - Wikipedia 13/45
15. フルバックアップ, 差分バックアップ, 増分バックアップ 14/45
16. フルバックアップ, 差分バックアップ, 増分バックアップ 15/45
17. フルバックアップ, 差分バックアップ, 増分バックアップ 16/45
18. フルバックアップ, 差分バックアップ, 増分バックアップ 17/45
19. フルバックアップ, 差分バックアップ, 増分バックアップ 18/45
20. フルバックアップ, 差分バックアップ, 増分バックアップ 19/45
21. とある調達仕様書の別紙 DBのバックアップを差分で取得できること。 取得したバックアップはいつでもDBの復元に使用 できること。復元時は別のサイズを選択できること。 DBのバックアップは、一定期間（例：5分）以前の どの時点にでも戻せるかたちで（Point In Timer Restore）取得できること。 DBのバックアップは、予め設定したタイミング、周期、世代で自動的に定期実行できること。 DBのバックアップイメージは、CSPの中であれば、別のシステム、別のネットワーク、別の環境から も利用できること。 CSPの外部で作成されたDBデータをインポートできること。 20/45
22. PITR Point In Time Recove..あれRestore? 同じものだろうと割り切る ‐ 21/45
23. PITR MySQLでPITRといえば今も昔もバイナリログ というか差分バックアップをバイナリログで兼ねることの方が多い フルバックアップの頻度を上げられない超巨大インスタンス(2桁テラバイト以上)ではxbの差分バックアップを使ってる事例もたまに聞く あとUberはRocksDBの差分バックアップを取りたくてxb周りで頑張ってるとか面白い話を読んだ ‐ フルバックアップを取った時点のバイナリログ情報を記録しておいて、それ以降のバイナリログを継続 的にコピーし続ける バイナリログ情報は古くはバイナリログファイル名とポジション ‐ 最近ではGTIDで済む ‐ コピーの方法もOSコマンドでも良かったりmysqlbinlogコマンドでリモートから吸い上げたり ‐ バイナリログは「データを初期コピーしてきたレプリカ」に「ソースが受け取った差分」を適用してレプリ ケーションを成立させる要素なので、「レプリカ」の部分が「フルバックアップからリストアしたdatadir」 に変わっても「バイナリログを適用していくことで時計の針を進める」のが成立する 22/45
24. PITR バイナリログファイル単体をコピーすることは定義的には「増分バックアップ」 フルバックアップを取った時点にアクティブだったバイナリログは「差分バックアップ」としても成立する (= フルバック アップ + バイナリログ1本でリストアできるから) ‐ 一度スイッチして2本目のバイナリログファイルに差し掛かると、それは「最後の差分バックアップからの増分バック アップ」としてしか機能しない ‐ 「差分バックアップ」と「増分バックアップ」を混ぜるとリストアが複雑になる なのでPITRまで自動でやるなら「フルバックアップ」と「バイナリログ」だけでやるのがどう考えても楽 最終的に「差分バックアップ」の時点から「本当に戻したいタイミング」までは必ず「増分バックアップ」で埋めなければならないので、捨てる なら差分の方 ‐ 23/45
25. 混ぜると複雑 24/45
26. 差分バックアップ… https://x.com/yoku0825/status/1934476845405397363 25/45
27. PITR DBのバックアップは、一定期間（例：5分）以前のどの時点にでも戻せるかたちで（Point In Timer Restore）取得できること。 例：5分 ‐ 5分に1回バイナリログをスイッチしろってことかな 実際問題、スイッチしてないバイナリログをコピーしても「今まさに書いてるイベント」の直前まではフツーに mysqlbinlog でデコードできる ‐ mysqlbinlog --read-from-remote-server --raw --stop-never を使えばストリーミングで受信し続 けることもできる ( --stop-never を使わずにストリーミングじゃないファイル指定のリモートコピーもできる ) ‐ ストリーミングじゃなくてcrondでOSコマンドでも済ませられるので「ニアリアルタイムで」と言われなくてよかった ‐ 1時間って言ってもいいのかな 26/45
28. バイナリログ ### バックアップ $ target_binlog="$(basename $(ls /path/to/backup/binlog_mysqlbinlog/*.[0-9]* | tail -1 2> /dev/null))" $ [[ -z $target_binlog ]] && target_binlog="$(mysql -uroot -sse 'SHOW BINARY LOGS' | head -1 | awk '{print $1}')" $ mysqlbinlog -uroot -R --stop-never --raw $target_binlog ### リストア ### 「このバイナリログとそれ以降」をmysqlbinlogコマンドに引数にしたい $ target_binlog="$(cat $target_diff/xtrabackup_binlog_info | awk '{print $1}')" $ binlogs=$(for n in $(seq $binlog_sequence 1000000) ; do binlog_file="$(printf '/path/to/backup/binlog_mysqlbinlog/binlog.%06d' $n)" [[ -e $binlog_file ]] || break echo $binlog_file done) ### --start-positionはGTIDが有効ならそんなに厳密でなくていいので無視 $ mysqlbinlog --stop-datetime="$target" $binlogs | mysql -uroot 27/45
29. リストアの流れ 28/45
30. MySQLのフルバックアップ コールドバックアップ MySQL Enterprise Backup Percona XtraBackup CLONE INSTANCE mysqldump (!!) MySQL Shell Dump/Load Utility MyDumper 29/45
31. MySQLの差分が取れるフルバックアップ コールドバックアップ MySQL Enterprise Backup Percona XtraBackup CLONE INSTANCE mysqldump (!!) MySQL Shell Dump/Load Utility MyDumper 30/45
32. コールドバックアップ 言わずもがな、mysqldを停止してdatadirを丸ごとコピーする tar, rsync, 稼働中のmysqldで取っても失敗することの多いケースも止まっていれば大丈夫 ‐ rsyncは確かにファイル単位で差分だけコピーすることはできるが、コピー先のファイルを上書きして しまうので差分だけリストアするようなことはできない 「差分」という用語だけに気を取られるとアリなのかと思ってしまう罠 圧縮かけられないしぱっと見でいつまでのバックアップなのかわからない(機械的に判断できない)ので普通はやらない ‐ PITRのこともあるし没 31/45
33. 論理バックアップ MySQLは「SQLだけで自分が最後に更新されたトランザクションIDを知る」ことができない あと、「今ある行」からだけでは「今ない行」が「最初からなかったのか」それとも「最近DELETEされてなくなったの か」はわからない ‐ updated_at 的なカラムがあってレコードがイミュータブルならば差分だけ取れるといえば取れるがそれはクラウド サービスがユーザーに要求できることではない ‐ 個人的には MySQL Shell 使いたかったんだけどな… 論理バックアップの良いところはリモートからパラレルで実行できるところと圧縮率が良いところ セカンダリインデックスをバックアップの中に含まないので軽量な代わりにリストアに時間がかかるが 世の中、バックアップを取る回数とリストアする回数は前者の方が圧倒的に多い とはいえリストアにかけられる時間の限界というものはあるので本当は物理バックアップと論理バックアップと両方選べるのが一番いいんだ が ‐ 「差分」の用語に呪われて没 32/45
34. ホット物理バックアップ MySQL Enterprise Backup (MEB), Percona XtraBackup (xb) ibdファイルをコピーしている間ずっとInnoDBログをコピーし続ける + リストアのステップでコピーし たibdファイルとInnoDBログを使ってクラッシュリカバリする InnoDBはLSN (Log Sequence Number) という単調増加のカウンターを使って「ページがど こまでストレージに永続化されたか」を管理している ibdファイルはデフォルト16kBの「ページ」単位に分かれて管理されており、それぞれのページヘッダには “最後にこ のページを更新したLSN” が書かれている これを頼りに「前回のバックアップの時のLSNより大きいLSNで更新されたページ」だけをバックアップ対象にできる 最近のMySQL Enterprise Backup(8.0.18とそれ以降)はもっと賢く(MySQLサーバと連携して)ページトラッキングするらしい ‐ まあ、これになりますよね… 33/45
35. ホット物理バックアップ Cloneプラグイン ローカルにdatadirを, リモートのmysqldからまるっとデータ同期を, の他に、「リモートのmysqldからローカルの 別ディレクトリにdatadirをコピー」もできる リモート物理ホットバックアップ！ （pg_basebackupがうらやましかったやつ） ‐ ibdファイルをコピーしている間ずっとInnoDBログをコピーし続ける + リストアのステップでコピーし たibdファイルとInnoDBログを使ってクラッシュリカバリする (ここはMEB, xbと一緒) ただし差分バックアップには対応していない ‐ バイナリログの切断面（ファイル名, ポジション, GTID）がなんと起動してみるまでわからない ‐ 今回は没 mysql> CLONE INSTANCE FROM user@host:port IDENTIFEID BY 'xxx' DATA DIRECTORY = '/path/to/r emote_backup'; 34/45
36. リストアの流れ 35/45
37. xtrabackup（バックアップ） 差分の時にはベースバックアップのディレクトリを指定する必要があるのと ( -- stream=xbstream では差分バックアップができない) 増分バックアップはフル/差分とは関係なく常に取り続ける想定（どうせ必要だから） ### フル $ xtrabackup -uroot --backup --target-dir="/path/to/backup/xb_full_$(date '+%Y%m%d_%H%M%S')" ### 差分 $ latest_backup="$(ls -d /path/to/backup/xb_full_* | tail -1)" $ xtrabackup -uroot --backup --incremental-basedir="$latest_backup" --target-dir="/path/to/xb_diff_$(date '+% Y%m%d_%H%M%S')" 36/45
38. xtrabackup（リストア） ### フルバックアップからのリストア $ target="2025-11-12 14:30:00" $ target_full=$(dirname $(find /path/to/backup/xb_full* -type f -name "xtrabackup_binlog_info" ! -newermt "$ target" | sort | tail -1)) $ cp -r ${target_full}/* ./ $ xtrabackup --prepare --apply-log-only --target-dir=./ ### 差分バックアップがない時は --apply-log-only をつけてはいけない ### 差分のリストア $ target_diff=$(dirname $(find /path/to/backup/xb_diff* -type f -name "xtrabackup_binlog_info" ! -newermt "$ target" | sort | tail -1)) $ xtrabackup --prepare --target-dir=./ --incremental-dir="$target_diff" 37/45
39. リストアの流れ 38/45
40. 正直に差分バックアップまで使ったリストア その後の差分バックアップがあるかどうかでフルバックアップからのリストア用オプションが変わる めんどう ‐ フルバックアップの頻度を上げられない && バイナリログの適用に時間がかかりすぎるケース以外は 差分要らないんじゃないかな それか「取るだけ取ってリストアには使わない」（） どうせ使わないんだったら論理バックアップを使ってもいいんじゃ？？ ‐ 39/45
41. mysqlsh を使うと ### フルバックアップ $ mysqlsh --login-path=backup -h ipaddr --js -- util dumpInstance /path/to/mysqlsh_full_$(date '+%Y%m%d_ %H%M%S') ### リストア対象の識別 $ target="2025-11-12 14:30:00" $ target_full=$(dirname $(find /path/to/backup/mysqlsh_full* -type f -name "@.json" ! -newermt "$target" | so rt | tail -1)) ### @.jsonからバイナリログの情報も取り出せる -> mysqlbinlogの最初のファイルを指定するのに使 える $ jq '.binlogFile, .binlogPosition, .gtidExecuted' @.json "bin.000005" 948 "aefbe16b-aedd-11f0-8a5f-02001702f486:1-2188" 40/45
42. mysqlshの更に便利機能 ベースになるフルバックアップのディレクトリを指定すると勝手に @.json からGTIDの差分を取っ て必要なバイナリログだけ吸い上げることができる しかもディレクトリ構成だけど個々のファイルはzstd圧縮されているから別途圧縮する必要がない ‐ $ latest_full=$(dirname $(find /path/to/backup/mysqlsh_full* -type f -name "@.json" ! -newermt "$target" | so rt | tail -1)) $ mysqlsh --login-path=backup -h ipaddr --js -- util dumpBinlogs /path/to/binlog_backup --since=$latest_full 41/45
43. ただし mysqlsh -- util loadBinlogs の stopBefore に指定できるのは時刻ではなくてGTIDだ け… 時刻とGTIDのマップは自分でやらないといけない そういうことフツーの人類はやらない ‐ 惜しい。そういうとこだぞ ‐ 42/45
44. とある調達仕様書の別紙 DBのバックアップを差分で取得できること。取得したバックアップはいつでもDBの復元に使用でき ること。復元時は別のサイズを選択できること。 DBのバックアップは、一定期間（例：5分）以前のどの時点にでも戻せるかたちで（Point In Timer Restore）取得できること。 DBのバックアップは、予め設定したタイミング、周期、世代で自動的に定期実行できること。 DBのバックアップイメージは、CSPの中であれば、別のシステム、別のネットワーク、別の環境から も利用できること。 CSPの外部で作成されたDBデータをインポートできること。 43/45
45. まとめ MySQLのバックアップは結局xtrabackup + バイナリログが鉄板 でもmysqlshもかなりいいよ、フルバックアップも増分バックアップもmysqlshコマンドで取れるよ 日々の覚書: 最新のMySQL ShellだけでMySQLのPITR可能なバックアップを設定する(フルバックアップ編) ‐ 日々の覚書: 最新のMySQL ShellだけでMySQLのPITR可能なバックアップを設定する(増分バックアップ編) ‐ 日々の覚書: 最新のMySQL ShellだけでMySQLのPITR可能なバックアップを設定する(実践編) ‐ yoku0825/simple-mysqlsh-pitr: PoC of PITR only using MySQL Shell ‐ 44/45
46. Any Question and/or Suggestion? 45/45