MySQL ShellでダンプされたデータをMySQLにロードする
はじめに
こんにちは、ソフトウェアエンジニアのshmokmtです。
弊社の開発環境およびローカル環境では動作確認しやすい状態を実現するために個人情報がマスキングされたsqlファイルをロードする運用を取っています。しかし、これはmysqldumpで出力された1つのsqlをシングルスレッドでロードするものです。そのため、データをロードする度に結構時間がかかってしまっている状態です。
mysqldumpの後継にあたるツールは既にたくさんあり、それらは一般的にマルチスレッドでデータをロードするため比較的高速に動作します。下記のベンチマークスコアを参考すると、パフォーマンスという観点では、MyDumper（zstd）[1] かMySQL Shellが候補として挙がりそうです。今回は、高速化のためのアプローチの一つとしてMySQL Shellを試してみることにしました。
対象読者
- MySQL Shellでダンプ、ロードする一連の流れをざっくり知りたい方
MySQL Shell
インストール
筆者のローカル環境はmacOS（Apple Silicon）であるため、brew経由でインストールしました。
$ brew install mysql-shell
$ mysqlsh --help
MySQL Shell 8.0.31
Copyright (c) 2016, 2022, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates.
Other names may be trademarks of their respective owners.
Usage: mysqlsh [OPTIONS] [URI]
mysqlsh [OPTIONS] [URI] -f <path> [<script-args>...]
mysqlsh [OPTIONS] [URI] --dba enableXProtocol
mysqlsh [OPTIONS] [URI] --cluster
mysqlsh [OPTIONS] [URI] -- <object> <method> [<method-args>...]
mysqlsh [OPTIONS] [URI] --import {<file>|-} [<collection>|<table> <column>]
....
インストールできました。
MySQL Shell 8.0.31で動作を確認してみます。
データをダンプする
今回はローカル環境に存在するマスキング済みのデータが格納されているMySQL5.7のコンテナ[2]に対して実施しました。また、デフォルト値である4つのスレッドを活用できるよう事前にDocker DesktopでCPUsの割当を6まで引き上げました。
$ mysqlsh root@127.0.0.1
Please provide the password for 'root@127.0.0.1':
Save password for 'root@127.0.0.1'? [Y]es/[N]o/Ne[v]er (default No): Y
MySQL Shell 8.0.31
Copyright (c) 2016, 2022, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates.
Other names may be trademarks of their respective owners.
Type '\help' or '\?' for help; '\quit' to exit.
Creating a session to 'root@127.0.0.1'
Fetching schema names for auto-completion... Press ^C to stop.
Your MySQL connection id is 153
Server version: 5.7.38-log MySQL Community Server (GPL)
No default schema selected; type \use <schema> to set one.
MySQL 127.0.0.1:3306 ssl JS >
今回はサーバー内の全てのDBをダンプする必要はなく、特定のDBをダンプすれば良いため、
util.dumpSchemasを使いました。
util.dumpSchemas(schemas, outputUrl[, options])
引数のオプション指定でドライランすることもできます。
MySQL 127.0.0.1:3306 ssl JS > util.dumpSchemas(["app_development"], "~/mysqlsh-dump", {dryRun: true})
dryRun enabled, no locks will be acquired and no files will be created.
NOTE: Backup lock is not supported in MySQL 5.7 and DDL changes will not be blocked. The dump may fail with an error if schema changes are made while dumping.
Acquiring global read lock
Global read lock acquired
Initializing - done
1 schemas will be dumped and within them 323 tables, 0 views.
Gathering information - done
All transactions have been started
Global read lock has been released
Writing global DDL files
NOTE: Could not select columns to be used as an index for table `app_development`.`admin_users_parts`. Chunking has been disabled for this table, data will be dumped to a single file.
NOTE: Could not select columns to be used as an index for table `app_development`.`roles_authorities`. Chunking has been disabled for this table, data will be dumped to a single file.
Writing DDL - done
Starting data dump
0% (0 rows / ~42.33M rows), 0.00 rows/s, 0.00 B/s uncompressed, 0.00 B/s compressed
ダンプが完了すると、詳細なデータサイズや実行時間などの情報が出力されます。
今回の場合だと圧縮なしで8GB程度のものを4分程度でダンプできました。
Dump duration: 00:04:18s
Total duration: 00:04:19s
Schemas dumped: 1
Tables dumped: 322
Uncompressed data size: 8.19 GB
Compressed data size: 1.58 GB
Compression ratio: 5.2
Rows written: 43653668
Bytes written: 1.58 GB
Average uncompressed throughput: 31.68 MB/s
Average compressed throughput: 6.13 MB/s
ダンプしたデータを確認してみましょう。出力したディレクトリ内でテーブルごとに
jsonファイルとsqlファイルとtsv（zstd圧縮）ファイルが含まれています。
jsonファイルにはMySQL Shellが利用するであろうメタデータが記録されており、
sqlファイルにはDDLが記載されていました。
tsvファイルには実際のデータが入っているようです。
デフォルトだと、デーブルデータ64MBごとにチャンク化されるようです。
$ ls | grep hoge
app_development@hoge.json
app_development@hoge.sql
app_development@hoge@@0.tsv.zst
app_development@hoge@@0.tsv.zst.idx
app_development@hoge@@1.tsv.zst
app_development@hoge@@1.tsv.zst.idx
※テーブル名は適当な名前で誤魔化しています。
データをロードする
まっさらなDBにデータを入れてみたかったため、事前に同名のDBをDROP&CREATEして、作り直しました。
ロードは次のコマンドで実行できます。
util.loadDump(url[, options])
ここでの注意点としてはデータが全てロードし終えた後にインデックスを作成することです。
レコードが挿入される度にインデックスの再構築が走るのを防ぐことができ、スループットの向上が期待できます。
MySQL Shellでは、loadIndexes と deferTableIndexes というオプションでインデックスの作成を最後に実施することができます。
MySQL 127.0.0.1:3306 ssl JS > util.loadDump("~/mysqlsh-dump", {resetProgress: true, loadIndexes: true, deferTableIndexes: "all"})
Loading DDL and Data from '~/mysqlsh-dump' using 4 threads.
Opening dump...
Target is MySQL 5.7.38-log. Dump was produced from MySQL 5.7.38-log
NOTE: Load progress file detected for the instance but 'resetProgress' option was enabled. Load progress will be discarded and the whole dump will be reloaded.
Scanning metadata - done
Checking for pre-existing objects...
Executing common preamble SQL
Executing DDL - done
Executing view DDL - done
Starting data load
1 thds loading - 2 thds indexing - 100% (8.19 GB / 8.19 GB), 5.26 MB/s, 321 / 322 tables done
Recreating indexes - done
Executing common postamble SQL
522 chunks (43.65M rows, 8.19 GB) for 322 tables in 1 schemas were loaded in 16 min 16 sec (avg throughput 12.70 MB/s)
0 warnings were reported during the load.
16分程度で完了しました。
さいごに
MySQL Shellでダンプして、ロードをするまでの一連の流れを簡単に試してみました。
開発環境の整備用途だと大きな制約を受けることはなさそうですが、本番環境のバックアップ用途として利用する場合は詳細に検証[3]した方が良いと思います。
マルチスレッドで動作するだけでなく、デフォルトでzstd圧縮してくれる点が嬉しいですね。
今回はインタラクティブな対話環境で試してみましたが、オプションを使って非インタラクティブに実行することもできますし、ログの標準出力にも対応しています。
さらにロード中のタスクを中断した場合、その中断した箇所から再開する機能もあるようです。
今回のユースケースからは外れてしまいますが、 util.checkForServerUpgrade 関数を利用することで
MySQLのアップグレード準備が出来ているかどうか確認することもできるようでこちらも便利そうな印象です。
論理バックアップツールを見直している方はMySQL Shellを検討してみてはいかがでしょうか。
-
brew経由でインストールすると、圧縮形式がgzipになりました。zstd圧縮を利用したい場合はローカルにpkg-config, CMakeなどを整備し、ソースからビルドする必要がありそうです。 https://github.com/mydumper/mydumper/pull/336 ↩︎
-
MySQL5.7はARMでサポートされていないため、エミュレートのオーバーヘッドを許容し、linux/amd64用のイメージを利用しています。
https://betterprogramming.pub/mysql-5-7-does-not-have-an-official-docker-image-on-arm-m1-mac-e55cbe093d4c ↩︎ -
LayerXさんでの事例です。AWS Auroraで試されています。
https://tech.layerx.co.jp/entry/2021/10/06/092100 ↩︎
Discussion