なからなLife

geekに憧れと敬意を抱きながら、SE、ITコンサル、商品企画、事業企画、管理会計、総務・情シス、再び受託でDB屋さんと流浪する人のブログです。

MySQL ShellのdumpInstance()、dumpSchemas()をAWS RDS&AuroraMySQLに対して使用する

※初回執筆時から、追記・修正があります。MySQL Shell 8.0.21で存在した各種制約は、MySQL Shell 8.0.22によって緩和されています。

MySQL ShellのdumpInstance()/dumpSchemas()とは

MySQLからのダンプエクスポートといえば、mysqldumpでした。しかし、シリアルに動くので、大量テーブル、大量データを対象としたdump処理には時間がかかることがネックとなることがありました。

有償拡張版のMySQL Enterpriseには、Enterprise Backupという高速バックアップを実現する機能が提供されています。
MySQLを大規模運用されている企業では、問題を解決するためにEnterpriseを採用するケースもあります。(それだけがEnterpriseの採用理由ではないだろうけど)


2020-07-21にGAした「MySQL 8.0.21」と合わせて、MySQL Shellも8.0.21になりましたが、「dumpInstance()/dumpSchemas()*1」という、ダンプコマンドが追加されています。
MySQL :: MySQL Shell 8.0 :: 7.5 Instance Dump Utility, Schema Dump Utility, and Table Dump Utility



公式ブログにベンチマークが掲載されていますが、内部的にはパラレルで動くこともあり、非常に高速に動作します。
MySQL Shell Dump & Load part 2: Benchmarks | MySQL Server Blog



これからは、これを使わない手は無いでしょう。MySQL5.7から使えますし。
MySQL5.6のExtended Suportは2021年2月に終了するので、この先対応されることも無いでしょうし、5.7以降に引き上げる計画を立てましょう)

2020.10.20追記
MySQL5.6に対応したそうです!リリースノートからは読み取れなかった(そのうちリリースノートの修正はいるかも)ですが、MySQL Server TeamのBlogに記述があります。

Dumping from MySQL 5.6
It is now possible to dump from MySQL 5.6 and load these dumps into MySQL 5.7 or 8.0. However, dumping of user accounts is not supported when dumping from MySQL 5.6.
What’s New in MySQL Shell 8.0.22 | MySQL Server Blog

2020.10.20追記ここまで

なお、MySQL ShellはX Protocol必須の機能とそうでない機能がありますが、この「dumpInstance()/dumpSchemas()」はX Protocol不要なようで、従来どおりTCP:3306ポートで接続すれば実行できます。

RDSとAuroraに対しては、そのままでは動かない件

ダメなんだろうな、と思って試してみて、やっぱりダメでした。


実際にAurora MySQL 5.7に対して実行すると

Util.dumpInstance: Unable to acquire global read lock: MySQL Error 1045 (28000): Access denied for user <使用したユーザー> (using password: YES) (RuntimeError)

というメッセージが出ます。


MySQL ShellのdumpInstance()の挙動については三谷さんの MySQL Shell dumpInstance でバックアップ中はDDL が待機させられる - mita2 database life が詳しいので、詳細はこちらを読んで頂くとして、ここで

FLUSH TABLE WITH READ LOCK と LOCK INSTANCE FOR BACKUP でロックする
MySQL Shell dumpInstance でバックアップ中はDDL が待機させられる - mita2 database life

という記述が目に止まります。(LOCK INSTANCE FOR BACKUPはMySQL8.0からのコマンド。MySQL5.7に対する実行時には使用されない。)


たしかに、FLUSH TABLE WITH READ LOCKはglobal read lockを取得します。そのことは、MySQLの公式ドキュメントにも書いてあります。

・FLUSH TABLES WITH READ LOCK
開かれているすべてのテーブルを閉じ、グローバルな読み取りロックを保持しているすべてのデータベースのすべてのテーブルをロックします。これは、特定時点のスナップショットを取得できる、Veritas または ZFS などのファイルシステムがある場合にバックアップを取得するための非常に便利な方法です。このロックを解放するには、UNLOCK TABLES を使用します。

FLUSH TABLES WITH READ LOCK は、グローバルな読み取りロックを取得しますが、テーブルロックは取得しないため、テーブルロックと暗黙的なコミットに関して LOCK TABLES および UNLOCK TABLES と同じ動作には従いません。
MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.7.6.3 FLUSH 構文

必要な権限ですが、MySQLの公式ドキュメント上、

FLUSH を実行するには、RELOAD 権限が必要です。あとで説明されているように、特定のフラッシュオプションには追加の権限が必要になることがあります。
MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.7.6.3 FLUSH 構文

とありつつも、この「FLUSH TABLES WITH READ LOCK」について、具体的に追加が必要な権限が記載されているわけではありません。




AWSのナレッジセンターのドキュメント(公開情報)には、MySQL Shell-dumpInstanceではなくmysqldumpのエラーの話として、以下のことが書いてあります。

「FLUSH TABLES WITH READ LOCK」を実行できませんでしたというエラー

mysqldump で --master-data オプションを使用してデータをエクスポートすると、次のようなエラーが表示されることがあります。

「mysqldump: 'FLUSH TABLES WITH READ LOCK' を実行できませんでした: 'user'@'%' のアクセスが拒否されました (パスワード使用: はい) (1045)」

    • master-data オプションは FLUSH TABLES WITH READ LOCK を取得します。これには、Amazon RDS マスターのユーザーが持っていない SUPER 権限が必要です。また、Amazon RDS は GLOBAL READ LOCK をサポートしていません。MySQL がログ情報を取得するために CHANGE MASTER TO ステートメントを実行すると、バイナリログファイルの名前と位置 (座標) が mysqldump ファイルに記録されます。詳細については、ER_ACCESS_DENIED_ERROR に関する MySQL のドキュメントを参照してください。

Amazon RDS for MySQL または MariaDB での mysqldump エラーの解決


「FLUSH TABLES WITH READ LOCK」には「SUPER」が必要?
MySQL本家の公式ドキュメントには、そんなこと書いてないんですけど。



よろしい、ならば試してみよう。
Linux on VirtualBoxにMySQL5.7と8.0を用意して、SUPER権限の無いユーザーから「FLUSH TABLES WITH READ LOCK」投げてみます。

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.28    |
+-----------+
1 row in set (0.00 sec)

mysql> show grants\G
*************************** 1. row ***************************
Grants for test2@%: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE ON *.* TO 'test2'@'%'
1 row in set (0.00 sec)

mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.00 sec)


通るじゃないですかやだー。
エビデンス貼らないけど、MySQL ShellからのdumpSchemas()も動いたし。




以下は、RDSおよびAurora MySQLマスターユーザー(FLUSH TABLES WITH READ LOCK取れない)と、ノーマルなMySQL5.7、MySQL8.0でrootユーザーの権限から「SUPER」を除外したユーザー (FLUSH TABLES WITH READ LOCK取れた)の権限一覧です。

DB 権限
ノーマルMySQL5.7 GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER,CREATE TABLESPACE ON *.*
AuroraMySQL5.7 GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, PROCESS, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, LOAD FROM S3, SELECT INTO S3, INVOKE LAMBDA, INVOKE SAGEMAKER, INVOKE COMPREHEND ON *.*
RDS MySQL5.7 GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, PROCESS, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER ON *.*
ノーマルMySQL8.0 GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, PROCESS, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER ON *.*
RDS MySQL8.0 GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, PROCESS, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER ON *.*

MySQL5.7のところに「CREATE TABLESPACE」の有無の差がみつかったのですが、まさかコレが原因なわけないよね、と思いつつ、これもノーマル側で権限剥がして試したところ、やはり関係なかったです。これもエビデンス貼らないけど。

MySQL8.0については、デフォルトで作成されるrootユーザーのSHOW GRANTSで3行表示されますが、別途作ったユーザーにRDS/MySQL8.0のマスターユーザーと同じ権限をつけても、dumpSchemas()が動くことを確認しています。


まあ、仕方がないですよね。色々制約ありつつも、それ以外のところがめちゃくちゃ便利なので、これ1つでRDSやAuroraを弾く理由にはならないし。
そもそもバックアップ周りは、ほぼ何も気にしなくていいのがマネージドデータベースの良いところ。


なお、AWSのドキュメントにおけるmysqldumpのエラーについては、回避方法までちゃんと記載されています。


回避方法あった!だがしかし。。。

MySQL Shellのドキュメントに、以下の記載があります。

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. 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 and the global read lock is released.
https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-utilities-dump-instance-schema.html
(DeepL翻訳)
ダンプ中にバックアップ用のインスタンスをロックすることで、一貫性のあるデータダンプを有効(true)または無効(false)にします。既定値は true です。true が設定されている場合、ユーティリティは、FLUSH TABLES WITH READ LOCK ステートメントを使用してグローバル読み取りロックを設定します。各スレッドのトランザクションは、SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READおよびSTART TRANSACTION WITH CONSISTENT SNAPSHOTステートメントを使用して開始されます。すべてのスレッドがトランザクションを開始すると、インスタンスはバックアップのためにロックされ、グローバルリードロックが解除されます。


たしかに、

MySQL  接続先データベース  JS > util.dumpSchemas(["スキーマ名"],"出力先ディレクトリ",{consistent:false})

のように、consistent:falseのオプションを指定すると、RDSやAuroraが相手でも動くようにはなりました。


しかし、「When true is set(trueが設定されているとき)」が、後ろのどの部分までにかかっているのか、これだとちょっとわからないですね。「The transaction for each thread is」以降にもかかっているのか、かかっていないのか(trueのときしか後述の挙動にならないのか)で、全然意味が違ってしまいます。

これ、「When true is set(trueが設定されているとき)」が文の最後までかかっているとなると、一貫性のあるdumpは取れなくなりそう。


その場合(consistent:falseだと「SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ and START TRANSACTION WITH CONSISTENT SNAPSHOT」が適用されず、一貫性が全く保証できない場合)、RDSやAuroraの場合、RDSならばリードレプリカを立てつつレプリケーションを停止した状態からconsistent:falseで取得、Auroraの場合は、RDSや生のMySQLをレプリカを立ててレプリケーションを切るか、スナップショットから別インスタンスを立てつつ、そこからconsistent:falseで取得する、という方法が取れると思います。


mysqldumpで---single-transaction オプション付与するとLOCK TABLE権限なくても一貫性のあるダンプが取れるのと同じような動きをしてくれているとありがたいんだけどなあ。


確かめるの大変そう。

2020.10.20追記
「FLUSH TABLES WITH READ LOCK」なしに、整合性のとれた並列Dumpができるようになった!とのことです。
なので、RDSやAuroraのように、「FLUSH TABLES WITH READ LOCK」を利用できない環境でも使えるようになったようです。

仕組みとしては、「FLUSH TABLES WITH READ LOCK」でエラーになったら、「LOCK TABLES ... READ」に切り替えて実行するようです。

Consistent Dumping without FLUSH TABLES WITH READ LOCK
The Shell performs parallel dumping using multiple threads with separate
transactions. FLUSH TABLES WITH READ LOCK is executed to synchronize transactions,
so that all threads can work on the same consistent view of the world. The lock is released immediately after the transactions are started, allowing applications to continue updating the database normally during the dump.

However, execution of that statement is often restricted by lack of privileges and not possible for users in managed cloud services (such as RDS) producing the following error:

It was still possible to perform consistent dumps (e.g. using a single thread or dumping from a read-only replica with the consistent option off). But in 8.0.22, dumpInstance was improved to allow parallel consistent dumps without FTWRL. If FTWRL fails because of missing grants, dump will automatically fallback to synchronizing transactions by locking tables with the LOCK TABLES ... READ statement.
What’s New in MySQL Shell 8.0.22 | MySQL Server Blog

これも、リリースノートから読み取れず、Blogの方にのみ書いてあったないようなので、修正入るかもです。
念のため試してみたいところです。

2020.10.20追記ここまで

2020.10.21追記
AWS RDSに対して、実際に動作確認してみました。
結果、RDS/MySQL 5.6、5.7、8.0いずれも、

util.dumpSchemas(["スキーマ名"],"出力先",{consistent:true})

によってエラー停止することなくダンプ出力されました。

ただし、RDS/MySQL 8.0の場合は、「BACKUP_ADMIN」権限が付与されていないとエラーになります。
インスタンス作成時に作るマスターユーザーには付与されていませんでしたので、別途GRANTしてください。

「BACKUP_ADMIN」権限自体が存在しないMySQL5.6、5.7については、このような制約はありません。


2020.10.21追記ここまで

2020.11.19追記
追記が遅くなってしまいましたが、少なくともMySQL Shell 8.0.22では、ダンプの一貫性が崩れるケースがあるとのことで、三谷さんがまとめてくださっています。
必読です!

mita2db.hateblo.jp
2020.11.19追記ここまで

まとめ(2020.10.21修正)

  • MySQL Shell 8.0.21から追加されたdumpInstance()/dumpSchemas()は爆速。
  • MySQL Shell 8.0.21は、AWS RDSとAuroraに対しては、dumpInstance()/dumpSchemas()はデフォルトでは使用できない。global read lockが取れないため。
    • MySQL Shell 8.0.22からは、「FLUSH TABLES WITH READ LOCK」を実行できなくても、一貫性のあるバックアップが取れるようになった。
    • MySQL8.0に対して、FLUSH TABLES WITH READ LOCKなしで一貫性のある並列バックアップを実行するためには、「BACKUP_ADMIN」権限が必要。(MySQL5.6、5.7は特に不要)
  • 「FLUSH TABLES WITH READ LOCKなし」「BACKUP_ADMIN権限なし」でも、「consistent:false」オプションをつけることで動くようにはなるが、バックアップの一貫性は保証されない。


さらなる情報が入手できたら追記/訂正します。


MySQL8,0およびMySQL Shellを勉強するなら、この2冊でしょ!

*1:スキーマ指定オプションの有無以外は基本的に一緒なので、このエントリは特に区別なく書いてます。もしそれ以外に大きな違いが見つかったら後で補足入れます。