LayerX エンジニアブログ

LayerX の エンジニアブログです。

mysqlsh (MySQL Shell): Dump and Restore in AWS Aurora

LayerX インボイス を開発しているDX事業部の @yyoshiki41(中川佳希)です。

DX事業部ではデータベースとして MySQL(Amazon Aurora)を利用しています。 今回のブログは、mysqlsh (MySQL Shell) を用いて、Dumpデータ取得とリストアを行う際に気をつける点です。

mysqldump, mysqlpump

Dumpデータ取得を行う際に、広く知られているのが mysqldump かと思います。

MySQL 5.7.8 からは、 mysqlpump という別のクライアントツールも提供されるようになりました。 主に下記のような特徴があります。

  • 並列での処理が行われる(Parallel)
  • Dump Progress がみれる
  • 圧縮方式は、LZ4 と ZLIB が使用可能(mysqlpump Ver 1.0.0 Distrib 5.7.35)
  • TABLE スキーマとINSERT 文の両方を出力する場合、INDEX を貼るクエリをデータリストア(INSERT文)の後に出力してくれる
    • INSERT毎での INDEX構築や KEY CHECKS なども不要になり、高速化が期待できる

実行例)

$ mysqlpump -uroot -p -B sandbox --set-gtid-purged=OFF --no-create-db --include-tables 'tests' --result-file=results.sql
Dump progress: n/n' tables, m/m' rows
Dump completed in x milliseconds

以下のようなファイルが出力されます。

-- テーブル作成
CREATE TABLE `tests` (
`id` varchar(36) COLLATE utf8mb4_bin NOT NULL,
`json` json DEFAULT NULL,
`version` int(11) GENERATED ALWAYS AS (json_extract(`json`,'$.version')) VIRTUAL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB
;
-- INSERT 文
INSERT INTO `tests` (`id`,`json`) VALUES (1,"{\"props\": \"val\", \"version\": \"1\"}"),(2,"{\"props\": \"val\", \"version\": \"2\"}");
-- 最後に INDEX 作成
ALTER TABLE `tests` ADD KEY `version` (`version`);

MySQL :: MySQL 5.7 Reference Manual :: 4.5.6 mysqlpump — A Database Backup Program

mysqlsh (MySQL Shell)

mysqlsh でも、Dump & Load Utility が提供されています。

また下記のブログでは mysqlsh でのバックアップ取得とリストアがベンチマークとともに紹介されています。
特に、mysqlsh が parallel で動作することとデフォルトで使用する圧縮方式 zstd により、mysqlbump, mysqlpump 以上のパフォーマンスとなることがレポートされています。

https://blogs.oracle.com/mysql/mysql-shell-dump-load-and-compressionblogs.oracle.com

AWS Aurora 環境で使ってみる

mysqlsh, Aurora(MySQL Server)のバージョンは、以下のとおりです。

$ mysqlsh --uri root@localhost:3306
...
MySQL Shell 8.0.26
...
Your MySQL connection id is 326244
Server version: 5.7.12-log MySQL Community Server (GPL)
...

いきなりですが、Dump データ取得に失敗します。

MySQL  localhost:3306 ssl  JS > util.dumpSchemas(["tests"], "/tmp")
Acquiring global read lock
ERROR: Failed to acquire global read lock: MySQL Error 1045 (28000): Access denied for user 'admin'@'%' (using password: YES)
Global read lock has been released
Util.dumpSchemas: Unable to acquire global read lock (RuntimeError)

これは AWS サポートブログ でも紹介されている、mysqldump で --master-data オプションを使用した場合と同じ原因のように見えます。 rdsadmin ユーザー以外は Super_priv を持たないため、FLUSH TABLES WITH READ LOCK が実行できずグローバルな読み取りロックを取得できないようです。

以下のようにオプションとして、 {consistent: false} を渡せば取得することは可能ですが、一貫性を保ったデータを取得するにはアプリケーションを止めるなどの必要があります。

MySQL  localhost:3306 ssl  JS > util.dumpSchemas(["tests"], "/tmp", {consistent: false})
Duration: 00:00:03s
Schemas dumped: 1
Tables dumped: 36
Uncompressed data size: 1.65 MB
Compressed data size: 342.14 KB
Compression ratio: 4.8
Rows written: 7058
Bytes written: 342.14 KB
Average uncompressed throughput: 501.88 KB/s
Average compressed throughput: 104.30 KB/s

consistent: [ true | false ] Enable (true) or disable (false) consistent data dumps by locking the instance for backup during the dump. The default is true. When true is set, the utility sets a global read lock using the FLUSH TABLES WITH READ LOCK statement (if the user ID used to run the utility has the RELOAD privilege), or a series of table locks using LOCK TABLES statements (if the user ID does not have the RELOAD privilege but does have LOCK TABLES). The transaction for each thread is started using the statements SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ and START TRANSACTION WITH CONSISTENT SNAPSHOT. When all threads have started their transactions, the instance is locked for backup (as described in LOCK INSTANCE FOR BACKUP and UNLOCK INSTANCE Statements) and the global read lock is released.

テーブル単位での Export / Import は、以下のように行えます。 exportTable 実行最終行に、 importTable コマンドを出力してくれて非常に親切です。

MySQL  localhost:3306 ssl  JS > util.exportTable("tests.table_A", "/tmp/table_A")
Gathering information - done
Preparing data dump for table `tests`.`table_A`
Data dump for table `dev_payer`.`clients` will use column `id` as an index
Running data dump using 1 thread.
NOTE: Progress information uses estimated values and may not be accurate.
Data dump for table `tests`.`table_A` will be written to 1 file
102% (2.38K rows / ~2.32K rows), 3.49K rows/s, 846.02 KB/s
Duration: 00:00:00s
Data size: 576.76 KB
Rows written: 2380
Bytes written: 576.76 KB
Average throughput: 576.76 KB/s

The dump can be loaded using:
util.importTable("/tmp/table_A", {
    "characterSet": "utf8mb4",
    "schema": "tests",
    "table": "table_A"
})

おわりに

単純なDBバックアップであれば、マネージドサービス側でサポートされていますが、
アプリケーション開発が進み、テーブル移行などが必要になってくるケースが出てくるとテーブル単位でのDumpと移行などが必要になってきます。

今回の FLUSH TABLES WITH READ LOCK や GTID などでハマることも多いかと思います。 ツールが変わっても気をつけるべき点としては、同じMySQL Server を使うならば基本的に同じだと感じました。 しかし、処理スピードやコマンドの使いやすさなど MySQL Shell の利点も多く、今後も楽しみなツールの1つです!


DX事業部では絶賛採用募集中です。 SaaS開発に興味があるという方は、ぜひ一度話を聞きに来てみてください!

herp.careers

エントリーはちょっとという方、こちらから中の話を聞くこともできます!

meety.net