mysqld/pump、MySQL Shellでもできるって
気づいたら8月でした。（毎回言っている気がする） 今回は小ネタです。
なんか気づいたらMySQL Shellで論理バックアップができるようになったみたいです。
3行で
- MySQL Shell 8.0.21にutil.dumpSchema(), dumpInstance(), loadDump()なるものが追加された
- 並列ダンプ(スレッド数の調整)、zstdによる圧縮、進捗の表示など他にも細かい指定ができるみたい
- 論理バックアップがやりやすくなるかも
MySQL Shellによる論理バックアップ
MySQL Shell 8.0.21から論理バックアップを取得できる関数が追加されました。 これらの関数は下記のとおりです。
- dumpInstance()
接続しているデータベースサーバ全体の論理バックアップを取得する
- dumpSchemas()
接続しているデータベースサーバの指定したデータベースの論理バックアップを取得する
- loadDump()
dumpInstance()もしくはdumpSchemas()によって作成されたダンプファイルを使ってデータをリストアする
mysqldumpとの比較
MySQLの論理バックアップツールとしてもおそらく最もポピュラーなのがmysqldumpではないでしょうか。 しかし、基本的に論理バックアップは物理バックアップよりも低速です。 加えてmysqldumpはインポートもエクスポートもシングルスレッドで行われます。 そのため大規模な環境の場合mysqldumpをそのまま日々のバックアップツールとして使うのはいささか難しいでしょう。
さて、mysqldumpではよく使うと思われる--single-transaction, --events, --routines, --triggersあたりがデフォルトで有効になっていません。（もうそろそろデフォルトでONにしてくれてもいいのよ）
代わりにInnoDBでは意味のない--disable-keysなどはデフォルトでONになっています。
手慣れたDBAの方ならおそらくmy.cnfにこれらの設定を書いていることでしょう。
[mysqldump] single-transaction events routines triggers skip-disable-keys
またmysqldump自身はダンプデータを圧縮して書き込む機能がないため、ダンプファイルを圧縮したい場合はパイプで渡してgzipなどの圧縮プログラムを使う必要があります。
mysqldump | gzip -c > /path/to/dump_file.sql.gz
mysqlpumpとの比較
ところでmysql"p"umpというものをご存じでしょうか。 （このpumpってOracle Data Pumpからきているのかしら） MySQL 5.7からひっそり(?)と追加されている論理バックアップツールで並列ダンプ、圧縮、インデックスの遅延ロードなどを行ってくれます。
mysqlpumpについてはこちらの記事で紹介されていますのでご参考ください。
mysqlpumpはダンプはマルチスレッドで行えますが、ダンプファイルは1つであるためロードはシングルスレッドで行われます。 さらに、並列処理の粒度はテーブルごとであるため、非常に大きなテーブルがある場合はそのテーブルはシングルスレッドで処理されダンプに時間がかかってしまいます。
MySQL Shellによるデータダンプ/ロード
MySQL Shellによるデータダンプ/ロードには下記のような特徴があります。
- 並列処理の単位はチャンクごとなので大きなテーブルでも並列処理のメリットがある
- ダンプファイルはデフォルトでzstdにより圧縮される
- ダンプファイルは複数生成されるためデータロードも並列で行える
- セカンダリインデックスの遅延ロードがデフォルトで有効になっている
mydumperというMySQL Shell同様チャンクごとのデータダンプを行うツールがありますが、 こちらのチャンク処理が同時に行われるのは単一のテーブルのみです。 これに関して非常にわかりやすい図が下記記事に掲載されているのでぜひ参照してみてください。
https://mysqlserverteam.com/mysql-shell-dump-load-part-3-load-dump/
使用例
実際にMySQL Shellでデータのダンプ/ロードを行ってみます。
mysqlsh> util.dumpSchemas(["sbtest"], "/mysql/backup/sbtest_backup", {threads:8})
第1引数にはダンプ対象となるデータベースをリスト形式で、第2引数にはダンプファイルを保存するディレクトリを指定します。 第2引数には相対パスも利用できます。その場合mysqlshを実行した時のカレントディレクトリがベースとなります。
第3引数にはオプションをJSON形式で渡すことができます。（省略可能） 上の例ではダンプスレッドを8に指定しています。（デフォルトでは4）
詳細を確認したい場合は次のコマンドを実行します。
mysqlsh> \? util.dumpSchemas
なおutil.dumpInstance()はデータベース全体のダンプを取得するコマンドで対象のデータベースを指定しない以外はdumpSchemas()と同じように使えます。
上記のコマンドによるデータダンプファイルは下記のようになります。
[root@centos8-mysql:~]# ll /mysql/backup/sbtest_backup/ 合計 3464 -rw-r-----. 1 root root 250 8月 7 16:26 @.done.json -rw-r-----. 1 root root 526 8月 7 16:26 @.json -rw-r-----. 1 root root 240 8月 7 16:26 @.post.sql -rw-r-----. 1 root root 240 8月 7 16:26 @.sql -rw-r-----. 1 root root 426 8月 7 16:26 sbtest.json -rw-r-----. 1 root root 561 8月 7 16:26 sbtest.sql -rw-r-----. 1 root root 586 8月 7 16:26 sbtest@sbtest1.json -rw-r-----. 1 root root 804 8月 7 16:26 sbtest@sbtest1.sql -rw-r-----. 1 root root 866397 8月 7 16:26 sbtest@sbtest1@@0.tsv.zst -rw-r-----. 1 root root 168 8月 7 16:26 sbtest@sbtest1@@0.tsv.zst.idx -rw-r-----. 1 root root 586 8月 7 16:26 sbtest@sbtest2.json -rw-r-----. 1 root root 804 8月 7 16:26 sbtest@sbtest2.sql -rw-r-----. 1 root root 866458 8月 7 16:26 sbtest@sbtest2@@0.tsv.zst -rw-r-----. 1 root root 168 8月 7 16:26 sbtest@sbtest2@@0.tsv.zst.idx -rw-r-----. 1 root root 586 8月 7 16:26 sbtest@sbtest3.json -rw-r-----. 1 root root 804 8月 7 16:26 sbtest@sbtest3.sql -rw-r-----. 1 root root 866350 8月 7 16:26 sbtest@sbtest3@@0.tsv.zst -rw-r-----. 1 root root 168 8月 7 16:26 sbtest@sbtest3@@0.tsv.zst.idx -rw-r-----. 1 root root 586 8月 7 16:26 sbtest@sbtest4.json -rw-r-----. 1 root root 804 8月 7 16:26 sbtest@sbtest4.sql -rw-r-----. 1 root root 866419 8月 7 16:26 sbtest@sbtest4@@0.tsv.zst -rw-r-----. 1 root root 168 8月 7 16:26 sbtest@sbtest4@@0.tsv.zst.idx
この結果からもわかるように、メタデータ、テーブル、インデックスごとにダンプファイルが分割されています。
お次はデータロードです。
mysqlsh> util.loadDump("/mysql/backup/sbtest_backup", {threads:8})
第1引数にはインポートするダンプファイルがディレクトリを、第2引数にはオプションを渡します。
実行ログは下記のようになります。
Loading DDL and Data from '/mysql/backup/sbtest_backup' using 8 threads. Target is MySQL 8.0.21. Dump was produced from MySQL 8.0.21 Checking for pre-existing objects... Executing common preamble SQL Executing DDL script for schema `sbtest` Executing DDL script for `sbtest`.`sbtest4` Executing DDL script for `sbtest`.`sbtest3` Executing DDL script for `sbtest`.`sbtest1` Executing DDL script for `sbtest`.`sbtest2` [Worker004] sbtest@sbtest2@@0.tsv.zst: Records: 10000 Deleted: 0 Skipped: 0 Warnings: 0 [Worker003] sbtest@sbtest1@@0.tsv.zst: Records: 10000 Deleted: 0 Skipped: 0 Warnings: 0 [Worker001] sbtest@sbtest3@@0.tsv.zst: Records: 10000 Deleted: 0 Skipped: 0 Warnings: 0 [Worker005] sbtest@sbtest4@@0.tsv.zst: Records: 10000 Deleted: 0 Skipped: 0 Warnings: 0 Executing common postamble SQL 4 chunks (40.00K rows, 7.59 MB) for 4 tables in 1 schemas were loaded in 1 sec (avg throughput 7.59 MB/s) 0 warnings were reported during the load.
ベンチマーク
https://mysqlserverteam.com/mysql-shell-dump-load-part-2-benchmarks/
今回は特にベンチマークはとりませんが、こちらの記事でmysqldump, mysqlpump, mydumperとの比較がされています。 これを見ると対象テーブルによってはMySQL Shellはバイナリデータをダンプするときにbase64形式に変換するためmydumperよりも結果が悪くなっています。 これらは8.0.22以降で改善されることでしょう。 mydumperは置いておくとしてmysqldump, mysqlpumpと比較すると桁違いのパフォーマンスを発揮していることが分かります。
ちなみに上記ベンチマーク時にはInnoDB REDOログを無効にしていますが、これは手動で行う必要があります。
まとめ
MySQL Shellに新しく追加されたダンプ機能を使えば非常に高速にダンプ/ロードが可能となりそうです。 といっても最新の機能についてはバグがつきものです。 プロダクト環境で使う場合は必ず事前検証をしっかりやっておくことが必要でしょう。
今回はこの辺で。
