MySQL Shell で使える高度なデータダンプ機能

ここ最近、私は MySQL Shell の魅力にすっかりとりつかれています。MySQL Shell をあまりご存じない方のために説明すると、これは比較的新しく開発された、MySQL インスタンスの接続・管理を行うためのコマンドラインインターフェース (CLI) です。Podcastの「Inside MySQL: Sakila Speaks (英語)」で昨年 (2024) 年、我々は MySQL Shell の役立つ (そしてあまり知られていない) 機能についてとりあげました。そうした「隠れた宝石」の機能を MySQL ブログでも、この記事を含めたいくつかの記事で紹介いたします。

問題点

と節を名付けつつ、今回の記事では特に「問題点」はありませんが、他の MySQL Shell の Hidden Gems シリーズ (英語版) でもこの順序で述べたので、形式を合わせてみました。このシリーズでは、過去に何度か MySQL Shell のダンプやロードについてとりあげたことがありますが、本記事では特に、ダンプ時に利用できるいくつかのオプションについて掘り下げてみましょう。

解決策

これも実際には「解決策」ではありませんが、過去記事とフォーマットを合わせています。

まず、データダンプの際に使えるオプションを紹介します。これらのオプションは、使用する MySQL Shell 関数 (util.dumpInstance(), util.dumpSchemas() あるいは util.dumptables()) に関わらず指定できます。各関数の最後の引数 (任意) は、JSON 形式で指定する設定オプションになります。例えば、オプション付きでインスタンス全体をダンプする場合、コマンドは以下のようになります。

util.dumpInstance('/path/to/dump/folder/', {option1: 'オプション 1 の値', option2: 'オプション 2 の値'})

以降では、私が特に便利で興味深いと思うオプションを取り上げます。

ドライラン (Dry Run)

データ量が多いデータベースの場合、問題なくダンプできるかを確認するために、事前にドライランを行えます。このオプションを使うには dryRun: true を指定します。たとえば、

util.dumpInstance('~/dumps/example1', {dryRun:true})

ローカルの MySQL インスタンスでこのコマンドを実行すると、以下のような結果になります。

dryRun enabled, no locks will be acquired and no files will be created.
Acquiring global read lock
Global read lock acquired
Initializing - done
15 out of 19 schemas will be dumped and within them 132 tables, 17 views, 15 routines, 6 triggers.
3 out of 6 users will be dumped.
Gathering information - done
All transactions have been started
Locking instance for backup
Global read lock has been released
Writing global DDL files
Writing users DDL
Writing DDL - done        
Starting data dump
0% (0 rows / ~735.43K rows), 0.00 rows/s, 0.00 B/s uncompressed, 0.00 B/s compressed

まず、「ファイルが作成されない」というメッセージが出ていることに注目してください – ドライランの時はファイルを作成したくないので、これはよいことです。

次に、ダンプされるスキーマの数を示す行を見ると、全てのスキーマがダンプされていないことに気が付くと思います。これは、util.dumpInstance() がシステムテーブルをダンプしないためです。

残りの出力は、ダンプ中に実行されているプロセスの詳細を示します。データに問題があれば、この出力で確認できるでしょう。

スレッド処理

MySQL Shell が紹介されるたびに何度か述べられてきていますが、何度でも強調する価値がある情報として、MySQL Shell はダンプをマルチスレッドで実行することがあります。このオプションで適切にスレッド数を設定することで、データのダンプ処理が高速化されます。デフォルトでは MySQL Shell は 4 つのスレッドを使用します。指定する構文は次のとおりです。

util.dumpInstance('~/dumps/example2', {threads: 8})

このコマンドの出力は以下のようになります。

指定したスレッド数が、データをダンプする際に使われるスレッド数と、必ずしも一致するとは限らないことに注意してください。データ量が少ない場合やテーブル数が少ない場合など、意図的により少ないスレッドが使用される状況もあり得ます。また、スレッド数が多いからといって常にパフォーマンスが向上するとは限らない点にも注意してください。

ダンプデータのフィルタリング

ダンプするデータを絞りたい場合は、where オプションを用いてダンプするデータをフィルタリングできます。古いデータをダンプから除外したい場合や、データのサブセットだけが必要な場合などにおいては、このオプションはとても便利です。

データベース内のテーブルダンプの例を見てみます。

util.dumpTables('mysql_shorts', ['games'], '~/dumps/example3')

このコマンドは、MySQL Shell に「mysql_shorts」スキーマの「games」テーブル全体をダンプするように指示しています。

その出力は以下のとおり。

Acquiring global read lock
Global read lock acquired
Initializing - done
1 tables and 0 views will be dumped.
Gathering information - done
All transactions have been started
Locking instance for backup
Global read lock has been released
Writing global DDL files
Running data dump using 4 threads.
NOTE: Progress information uses estimated values and may not be accurate.
Writing schema metadata - done      
Writing DDL - done
Writing table metadata - done
Starting data dump
100% (40 rows / ~40 rows), 0.00 rows/s, 0.00 B/s uncompressed, 0.00 B/s compressed
Dump duration: 00:00:00s
Total duration: 00:00:00s
Schemas dumped: 1
Tables dumped: 1
Uncompressed data size: 483 bytes
Compressed data size: 227 bytes
Compression ratio: 2.1
Rows written: 40
Bytes written: 227 bytes
Average uncompressed throughput: 483.00 B/s
Average compressed throughput: 227.00 B/s

このメッセージは、40行がダンプに出力されたことを示しています。

次に示すのは、スコアが 90 以上の games テーブルの行だけをダンプする例です。

util.dumpTables('mysql_shorts', ['games'], '~/dumps/example4', {where: {"mysql_shorts.games": "score >= 90"}})

where オプションは JSON オブジェクトで、オブジェクト内の各キーはカラム名、値はフィルターに使用する条件を示しています。このコマンドの場合、出力はこうなります。

Acquiring global read lock
Global read lock acquired
Initializing - done
1 tables and 0 views will be dumped.
Gathering information - done
All transactions have been started
Locking instance for backup
Global read lock has been released
Writing global DDL files
Running data dump using 4 threads.
NOTE: Progress information uses estimated values and may not be accurate.
Writing schema metadata - done      
Writing DDL - done      
Writing table metadata - done
Starting data dump
35% (14 rows / ~40 rows), 0.00 rows/s, 0.00 B/s uncompressed, 0.00 B/s compressed
Dump duration: 00:00:00s
Total duration: 00:00:00s
Schemas dumped: 1
Tables dumped: 1
Uncompressed data size: 175 bytes
Compressed data size: 115 bytes
Compression ratio: 1.5
Rows written: 14
Bytes written: 115 bytes
Average uncompressed throughput: 175.00 B/s
Average compressed throughput: 115.00 B/s

この出力は、ダンプ結果に 14 行だけが書き込まれたことを示しています。

Oracle Cloud へのダンプ

MySQL Shell のいろいろな機能の中でも、私のいちばんのお気に入りは、Oracle Cloud ストレージバケットに直接ダンプできることです。この機能をカスタマイズできる、いくつかのオプションが用意されています。

この例では、OCI CLIがインストールされていることが前提となっています。

util.dumpInstance("example5", {osBucketName:"database_dumps"})

これを実行しているシステム上の OCI CLI の設定/構成によっては、バケットの名前空間もオプション項目に含めなければならない場合があります。

このコマンドの出力は次の様になります。

Acquiring global read lock
Global read lock acquired
Initializing - done
15 out of 19 schemas will be dumped and within them 132 tables, 17 views, 15 routines, 6 triggers.
3 out of 6 users will be dumped.
Gathering information - done
All transactions have been started
Locking instance for backup
Global read lock has been released
Writing global DDL files
Writing users DDL
Running data dump using 4 threads.
NOTE: Progress information uses estimated values and may not be accurate.
Writing schema metadata - done        
Writing DDL - done          
Writing table metadata - done          
Starting data dump
101% (745.78K rows / ~735.44K rows), 162.92K rows/s, 31.23 MB/s uncompressed, 3.08 MB/s compressed                
Dump duration: 00:00:10s
Total duration: 00:00:10s
Schemas dumped: 15
Tables dumped: 132
Uncompressed data size: 144.61 MB
Compressed data size: 14.27 MB
Compression ratio: 10.1
Rows written: 745780
Bytes written: 14.27 MB
Average uncompressed throughput: 14.39 MB/s
Average compressed throughput: 1.42 MB/s

下の画像は直接ダンプの結果として、OCI アカウントの database_dumps バケットに作成されたファイルを示しています。

HeatWave との互換性の確保

データを HeatWave MySQL インスタンスにロードする予定がある場合、データベースとテーブルの構造に互換性の問題がないことを確認しなければなりません。ocimds オプションは、これらの互換性チェックを有効にします。

util.dumpSchemas(['mysql_shorts'], '~/dumps/example6', {ocimds:true})

互換性の問題がなければダンプは通常通り進みますが、万一互換性の問題がある場合、出力は次のようになる可能性があります。

Acquiring global read lock
Global read lock acquired
Initializing - done
1 schemas will be dumped and within them 24 tables, 0 views, 2 routines.
Gathering information - done
All transactions have been started
Locking instance for backup
Global read lock has been released
NOTE: When migrating to MySQL HeatWave Service, please always use the latest available version of MySQL Shell.
Checking for compatibility with MySQL HeatWave Service 9.0.0
Checking for potential upgrade issues.
NOTE: The value of 'targetVersion' option (9.0.0) is not greater than current version of the server (9.0.0), skipping upgrade compatibility checks
NOTE: Database `mysql_shorts` had unsupported ENCRYPTION option commented out
WARNING: One or more DDL statements contain DEFINER clause but user information is not included in the dump. Loading will fail if accounts set as definers do not already exist in the target DB System instance.
WARNING: Function `mysql_shorts`.`multiply_ints` - definition uses DEFINER clause set to user `scott`@`localhost` which can only be executed by this user or a user with SET_ANY_DEFINER, SET_USER_ID or SUPER privileges
WARNING: Function `mysql_shorts`.`multiply_ints` - definition does not use SQL SECURITY INVOKER characteristic, which is mandatory when the DEFINER clause is omitted or removed
WARNING: Procedure `mysql_shorts`.`job_title_count` - definition uses DEFINER clause set to user `scott`@`localhost` which can only be executed by this user or a user with SET_ANY_DEFINER, SET_USER_ID or SUPER privileges
WARNING: Procedure `mysql_shorts`.`job_title_count` - definition does not use SQL SECURITY INVOKER characteristic, which is mandatory when the DEFINER clause is omitted or removed
ERROR: Table `mysql_shorts`.`user_2` does not have a Primary Key, which is required for High Availability in MySQL HeatWave Service
ERROR: Table `mysql_shorts`.`games` does not have a Primary Key, which is required for High Availability in MySQL HeatWave Service

NOTE: One or more objects with the DEFINER clause were found.

      The 'targetVersion' option was not set and compatibility was checked with the MySQL HeatWave Service 9.0.0.
      Loading the dump will fail if it is loaded into an DB System instance that does not support the SET_ANY_DEFINER privilege, which was introduced in 8.2.0.

ERROR: One or more tables without Primary Keys were found.

       MySQL HeatWave Service High Availability (MySQL HeatWave Service HA) requires Primary Keys to be present in all tables.
       To continue with the dump you must do one of the following:

       * Create PRIMARY keys (regular or invisible) in all tables before dumping them.
         MySQL 8.0.23 supports the creation of invisible columns to allow creating Primary Key columns with no impact to applications. For more details, see https://dev.mysql.com/doc/refman/en/invisible-columns.html.
         This is considered a best practice for both performance and usability and will work seamlessly with MySQL HeatWave Service.

       * Add the "create_invisible_pks" to the "compatibility" option.
         The dump will proceed and loader will automatically add Primary Keys to tables that don't have them when loading into MySQL HeatWave Service.
         This will make it possible to enable HA in MySQL HeatWave Service without application impact and without changes to the source database.
         Inbound Replication into a DB System HA instance will also be possible, as long as the instance has version 8.0.32 or newer. For more information, see https://docs.oracle.com/en-us/iaas/mysql-database/doc/creating-replication-channel.html.

       * Add the "ignore_missing_pks" to the "compatibility" option.
         This will disable this check and the dump will be produced normally, Primary Keys will not be added automatically.
         It will not be possible to load the dump in an HA enabled DB System instance.

Compatibility issues with MySQL HeatWave Service 9.0.0 were found. Please use the 'compatibility' option to apply compatibility adaptations to the dumped DDL.
Validating MySQL HeatWave Service compatibility - done       
Util.dumpSchemas: While 'Validating MySQL HeatWave Service compatibility': Compatibility issues were found (MYSQLSH 52004)

この出力では、DEFINER 句の問題、プライマリキーの不足、ストアドプログラムの定義時の SQL SECURITY INVOKER の不足など、いくつかの異なる問題が示されています参。幸いこれらの互換性問題について、出力は互換性を改善し確保するための提案を提供しています。提案には、DDL の変更または互換性オプションの追加などが含まれています。

これらの提案を使って、互換性オプションを用いコマンドを書き直すことができます。

util.dumpSchemas(['mysql_shorts'], '~/dumps/example6', {ocimds:true, compatibility:['create_invisible_pks', 'strip_definers']})

このコマンドの出力は次のようになります。

NOTE: The 'targetVersion' option is set to 9.0.0. This version supports the SET_ANY_DEFINER privilege, using the 'strip_definers' compatibility option is unnecessary.
Acquiring global read lock
Global read lock acquired
Initializing - done
1 schemas will be dumped and within them 24 tables, 0 views, 2 routines.
Gathering information - done
All transactions have been started
Locking instance for backup
Global read lock has been released
NOTE: When migrating to MySQL HeatWave Service, please always use the latest available version of MySQL Shell.
Checking for compatibility with MySQL HeatWave Service 9.0.0
Checking for potential upgrade issues.
NOTE: The value of 'targetVersion' option (9.0.0) is not greater than current version of the server (9.0.0), skipping upgrade compatibility checks
NOTE: Database `mysql_shorts` had unsupported ENCRYPTION option commented out
NOTE: Function `mysql_shorts`.`multiply_ints` had definer clause removed
NOTE: Function `mysql_shorts`.`multiply_ints` had SQL SECURITY characteristic set to INVOKER
NOTE: Procedure `mysql_shorts`.`job_title_count` had definer clause removed
NOTE: Procedure `mysql_shorts`.`job_title_count` had SQL SECURITY characteristic set to INVOKER
NOTE: Table `mysql_shorts`.`user_2` does not have a Primary Key, this will be fixed when the dump is loaded
NOTE: Table `mysql_shorts`.`games` does not have a Primary Key, this will be fixed when the dump is loaded

NOTE: One or more tables without Primary Keys were found.

      Missing Primary Keys will be created automatically when this dump is loaded.
      This will make it possible to enable High Availability in MySQL HeatWave Service DB System instance without application impact and without changes to the source database.
      Inbound Replication into a DB System HA instance will also be possible, as long as the instance has version 8.0.32 or newer. For more information, see https://docs.oracle.com/en-us/iaas/mysql-database/doc/creating-replication-channel.html.

Compatibility issues with MySQL HeatWave Service 9.0.0 were found and repaired. Please review the changes made before loading them.
Validating MySQL HeatWave Service compatibility - done       
Writing global DDL files
Running data dump using 4 threads.
NOTE: Progress information uses estimated values and may not be accurate.
Writing schema metadata - done      
Writing DDL - done
Writing table metadata - done
Starting data dump
100% (212.40K rows / ~210.74K rows), 0.00 rows/s, 0.00 B/s uncompressed, 0.00 B/s compressed
Dump duration: 00:00:00s
Total duration: 00:00:00s
Schemas dumped: 1
Tables dumped: 24
Uncompressed data size: 25.69 MB
Compressed data size: 3.12 MB
Compression ratio: 8.2
Rows written: 212395
Bytes written: 3.12 MB
Average uncompressed throughput: 25.69 MB/s
Average compressed throughput: 3.12 MB/s

これらの出力は、ダンプ作成時にスキーマに加えられる変更を示しています。その中には、いくつかのテーブルにインビジブル・プライマリキーを追加すること、一部のオブジェクトから definer を削除すること、他のオブジェクトの SQl SECURITY を INVOKER に設定することなどが含まれています。

これらの設定オプションを設定することで、このデータを HeatWave MySQL インスタンスにロードする際に互換性が維持されます。

まとめ

MySQL データベースからデータをダンプするために使える MySQL Shell のユーティリティは、パワフルであるにもかかわらず使いやすい、優れたツールです。オプションを活用して、ダンプしたいデータを制限したり、データをダンプする先の OCU バケットを指定したり、HeatWave 互換性を確保したり、ダンププロセスのドライランを実行したりできます。その他のダンプコマンドや、MySQL Shell で使える他のオプションについて詳しく知るには、MySQL Shell ダンプドキュメントをご確認ください。

（訳者註: 本記事の翻訳前の元記事は、2024 年 7 月 3 日に公開されました）