ここ最近、私は MySQL Shell の魅力にすっかりとりつかれています。MySQL Shell をあまりご存じない方のために説明すると、これは比較的新しく開発された、MySQL インスタンスの接続・管理を行うためのコマンドラインインターフェース (CLI) です。Podcastの「Inside MySQL: Sakila Speaks (英語)」で昨年 (2024) 年、我々は MySQL Shell の役立つ (そしてあまり知られていない) 機能についてとりあげました。そうした「隠れた宝石」の機能を MySQL ブログでも、この記事を含めたいくつかの記事で紹介いたします。
問題点
本記事では特定の「問題点」はありませんが、これまでの MySQL Shell の Hidden Gems シリーズ (英語版) の形式を維持したいと思います。1つ前の記事では MySQL Shell を使ったデータダンプのオプションについて述べましたが、今回は MySQL Shell を使ったデータロードでのオプションについて詳しく説明します。
解決策
これも実際のところ「解決策」ではありませんが、関連記事のフォーマットに揃えましょう。
まず、データのロード時に使えるオプションをいくつか見てみましょう。これらのオプションは util.loadDump() 関数の呼び出しの一部として使えます。この関数の任意に設定できる最後の引数は、JSON 形式のオプション設定です。例えば、ローカルシステム上のダンプからデータをロードしたい場合、次のようなコマンドを使います。
util.loadDump('/path/to/dump/folder/', {option1: 'オプション 1 の値', option2: 'オプション 2 の値'})興味深く役立ちそうなオプションに絞って、ここでは紹介します。
local_infile の有効化
データロードコマンドの実行に先立って、local_infile というグローバルシステム変数がONに設定されていることを確認する必要があります。この変数が正しい値を持っているかを確認するには、次のSQLコマンドを実行します。
show global variables like 'local_infile';このコマンドの出力は以下のようになります。
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| local_infile | OFF |
+---------------+-------+結果が「OFF」であった場合は、変更する必要があります。この変数の値を変更するには、次の SQL を実行します。
SET GLOBAL local_infile = 'ON';変数の設定後、次のコマンドで値を再確認します。
show global variables like 'local_infile';値は以下のように、ON と表示されるはずです。
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| local_infile | ON |
+---------------+-------+ドライラン (Dry Run)
非常に大きなデータベースダンプを持っている場合や、データを HeatWave MySQL インスタンスに移動するような場合、実際のロードを行う前にうまくいくかどうか確認したいことがあるでしょう。dryRun:true を用いてコマンドを実行すると、実際の動作を行わずにテストすることができます。例えば、
util.loadDump('~/dumps/example1', {dryRun:true})このコマンドをローカルの MySQL インスタンスに対して実行すると、以下のような結果が表示されます。
Loading DDL and Data from '~/dumps/example1' using 4 threads.
Opening dump...
dryRun enabled, no changes will be made.
Target is MySQL 9.0.0. Dump was produced from MySQL 9.0.0
Scanning metadata - done
Checking for pre-existing objects...
Executing common preamble SQL
Executing DDL - done
Executing view DDL - done
Starting data load
Executing common postamble SQL
0% (0 bytes / 144.61 MB), 0.00 B/s (0.00 rows/s), 132 / 132 tables done
Recreating indexes - done
No data loaded.
181 DDL files were executed in 0 sec.
Total duration: 0 sec
0 warnings were reported during the load.ご覧の通り、このダンプをロードするにあって警告事項はなく、接続している MySQL インスタンスで問題なく実行されるはずです。
スレッド処理
前の記事でも述べましたが何度でも強調します、MySQL Shell はマルチスレッドでダンプやロードを実行できます。このオプションはデータのロード処理を高速化します。デフォルトでは MySQL Shell は 4 つのスレッドを使用します。指定する構文は次のとおりです。
util.loadDump('~/dumps/example2', {threads: 8})このコマンドの出力は以下のようになります。
Loading DDL and Data from '~/dumps/example2' using 8 threads.
Opening dump...
Target is MySQL 9.0.0. Dump was produced from MySQL 9.0.0
Scanning metadata - done
Checking for pre-existing objects...
Executing common preamble SQL
Executing DDL - done
Executing view DDL - done
Starting data load
2 thds loading | 100% (144.61 MB / 144.61 MB), 57.09 MB/s (255.48K rows/s), 130 / 132 tables done
Recreating indexes - done
Executing common postamble SQL
139 chunks (745.78K rows, 144.61 MB) for 132 tables in 15 schemas were loaded in 2 sec (avg throughput 57.09 MB/s, 294.41K rows/s)
181 DDL files were executed in 0 sec.
Data load duration: 2 sec
2 indexes were recreated in 0 sec.
Total duration: 3 sec
0 warnings were reported during the load.データロードの進行状況を見ていると、使用されるスレッド数が指定した数よりも少ないこともあります。MySQL Shell は指定したスレッド数を使用しようとしますが、場合によってはより少ないスレッドが使用されることもあります。条件によっては、スレッド数が多いからといってパフォーマンスが向上するとは限りません。
ロード進行状況ファイル
progressFile オプションを使うと、ロードの進行状況を追跡するためのローカル進行状況ファイルの場所を指定できます。ダンプファイルがデータをロードしている MySQL インスタンスからローカルに見える場合、このファイルはダンプのソースディレクトリに自動的に作成されます。ファイル名は次の形式になります: load-progress-<server-uuid>.json。
ファイルの出力は以下のようになります。
{"op":"SERVER-UUID","done":true,"timestamp":1720014625787,"uuid":"207fe466-3940-11ef-8bc3-da561d907cbe"}
{"op":"SCHEMA-DDL","done":false,"timestamp":1720014625804,"schema":"json_demo"}
{"op":"SCHEMA-DDL","done":false,"timestamp":1720014625804,"schema":"test_schema"}
{"op":"SCHEMA-DDL","done":false,"timestamp":1720014625804,"schema":"knex_demo"}
{"op":"SCHEMA-DDL","done":false,"timestamp":1720014625804,"schema":"mysql_innodb_cluster_metadata"}
{"op":"SCHEMA-DDL","done":false,"timestamp":1720014625804,"schema":"doc-store-intro"}
{"op":"SCHEMA-DDL","done":false,"timestamp":1720014625804,"schema":"doc_store_demo"}
{"op":"SCHEMA-DDL","done":false,"timestamp":1720014625804,"schema":"node_demo"}
{"op":"SCHEMA-DDL","done":false,"timestamp":1720014625804,"schema":"mysql_shorts"}
{"op":"SCHEMA-DDL","done":false,"timestamp":1720014625809,"schema":"my_golf_league"}
{"op":"SCHEMA-DDL","done":false,"timestamp":1720014625809,"schema":"golf_league_manager"}
{"op":"SCHEMA-DDL","done":false,"timestamp":1720014625810,"schema":"testting_demo"}
{"op":"SCHEMA-DDL","done":false,"timestamp":1720014625811,"schema":"nosql_demo"}
{"op":"SCHEMA-DDL","done":false,"timestamp":1720014625812,"schema":"view-demo"}
{"op":"SCHEMA-DDL","done":false,"timestamp":1720014625813,"schema":"sql_template_tag_demo"}
{"op":"SCHEMA-DDL","done":false,"timestamp":1720014625814,"schema":"window-function-demo"}
{"op":"TABLE-DDL","done":false,"timestamp":1720014625815,"schema":"knex_demo","table":"knex_migrations_lock","_worker":5,"_srvthreads":1}
{"op":"TABLE-DDL","done":false,"timestamp":1720014625817,"schema":"json_demo","table":"season","_worker":2,"_srvthreads":1}...バイナリログのスキップ
「skipBinLog」オプションを使うと、大規模なデータセットをロードする際にパフォーマンスを向上させられます。このオプションを true に設定すると、MySQL Shell にはデータをロードする前に SET sql_bin_log=0 ステートメントを発行するような指示が伝わります。これはオンプレミスの MySQL インスタンスでのみ機能し、HeatWave MySQL インスタンスでのロード時にこのオプションを true に設定すると、エラーが発生します。
以下が使用例です。
util.loadDump("~/dumps/example2", {skipBinLog:true})このコマンドでの出力は、上記のスレッド処理の例と同様になります。
進行状況のリセット
util.loadDump("~/dumps/example2", {skipBinLog: true}) コマンドを再度実行すると、次のようなメッセージが表示されます。
Loading DDL and Data from '~/dumps/example2' using 4 threads.
Opening dump...
Target is MySQL 9.0.0. Dump was produced from MySQL 9.0.0
NOTE: Load progress file detected. Load will be resumed from where it was left, assuming no external updates were made.
You may enable the 'resetProgress' option to discard progress for this MySQL instance and force it to be completely reloaded.
Scanning metadata - done
Executing common preamble SQL
Executing DDL - done
Executing view DDL - done
Starting data load
100% (144.61 MB / 144.61 MB), 0.00 B/s (0.00 rows/s), 0 / 132 tables done
Executing common postamble SQL
Recreating indexes - done
There was no remaining data left to be loaded.
132 DDL files were executed in 0 sec.
Total duration: 0 sec
0 warnings were reported during the load.このメッセージの意味は、ロード進行状況ファイルが検出され、中断したところから再開されるという意味です。最初にデータをロードしようとしたときにエラーやその他の中断が発生した場合、そのポイントからロードが続行されます。最初のダンプが成功した場合は、何も実行されません。
resetProgess オプションを用いると、このロード進行状況ファイルを無視してデータロードを最初から開始するように MySQL Shell に指示できます。このオプションを使用するには、前回のロード試行で作成されたすべてのデータベースオブジェクトを先に削除する必要があります。このオブジェクトには、スキーマ、テーブル、ユーザー、ビュー、トリガー、ルーチン、イベント、全てが含まれます。削除しなければエラーが発生します（ただし、ignoreExistingObjects オプションを使った場合は別になりますが、これについては後ほど説明します）。
実際の使用例を以下に示します。
util.loadDump("~/dumps/example2", {resetProgress:true})以前に作成されたオブジェクトを削除しなければ、出力は次のようになります。
Loading DDL and Data from '~/dumps/example2' using 4 threads.
Opening dump...
Target is MySQL 9.0.0. Dump was produced from MySQL 9.0.0
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...
ERROR: Schema `test_schema` already contains a table named `test_collection`
ERROR: Schema `json_demo` already contains a table named `season`
ERROR: Schema `knex_demo` already contains a table named `knex_migrations`
ERROR: Schema `knex_demo` already contains a table named `knex_migrations_lock`
ERROR: Schema `knex_demo` already contains a table named `user`
ERROR: Schema `knex_demo` already contains a table named `user_type`
ERROR: Schema `doc-store-intro` already contains a table named `restaurant`
ERROR: Schema `doc_store_demo` already contains a table named `scores`
ERROR: Schema `node_demo` already contains a table named `scores`
ERROR: Schema `mysql_shorts` already contains a table named `collection_demo`
ERROR: Schema `mysql_shorts` already contains a table named `course`
ERROR: Schema `mysql_shorts` already contains a table named `csv_import`
ERROR: Schema `mysql_shorts` already contains a table named `games`
ERROR: Schema `mysql_shorts` already contains a table named `golfer`
ERROR: Schema `mysql_shorts` already contains a table named `golfer_score`
ERROR: Schema `mysql_shorts` already contains a table named `hole`
ERROR: Schema `mysql_shorts` already contains a table named `hole_group`
ERROR: Schema `mysql_shorts` already contains a table named `hole_score`
ERROR: Schema `mysql_shorts` already contains a table named `ipsum`
ERROR: Schema `mysql_shorts` already contains a table named `json_constraint_demo`
ERROR: Schema `mysql_shorts` already contains a table named `pets`
ERROR: Schema `mysql_shorts` already contains a table named `product`
ERROR: Schema `mysql_shorts` already contains a table named `product_color`
ERROR: Schema `mysql_shorts` already contains a table named `replica_demo`
ERROR: Schema `mysql_shorts` already contains a table named `restaurant`
ERROR: Schema `mysql_shorts` already contains a table named `sales`
ERROR: Schema `mysql_shorts` already contains a table named `sports_team_organization`
ERROR: Schema `mysql_shorts` already contains a table named `sports_team_organization_fan`
ERROR: Schema `mysql_shorts` already contains a table named `student`
ERROR: Schema `mysql_shorts` already contains a table named `user`
ERROR: Schema `mysql_shorts` already contains a table named `user_2`
ERROR: Schema `mysql_shorts` already contains a table named `user_3`
ERROR: Schema `mysql_shorts` already contains a table named `user_type`
ERROR: Schema `mysql_shorts` already contains a function named `multiply_ints`
ERROR: Schema `mysql_shorts` already contains a procedure named `job_title_count`
ERROR: Schema `sql_template_tag_demo` already contains a table named `user`
ERROR: Schema `window-function-demo` already contains a table named `player`
ERROR: One or more objects in the dump already exist in the destination database. You must either DROP these objects or exclude them from the load.
Util.loadDump: While 'Scanning metadata': Duplicate objects found in destination database (MYSQLSH 53021)出力には、作成済みアイテムのリストが表示されます。
既存オブジェクトの無視
上述したように、ignoreExistingObjects オプションを使うと、データロードの再実行中に既存のオブジェクトに遭遇しても、エラーが発生しないようにすることができます。以下は、resetProgress と ignoreExistingObjects オプションを使用するコマンドの例です（前のコマンドでロードしたいくつかのスキーマを削除しています）。
util.loadDump("~/dumps/example2", {resetProgress:true, ignoreExistingObjects:true})このコマンドの出力は次のようになります。
Loading DDL and Data from '~/dumps/example2' using 4 threads.
Opening dump...
Target is MySQL 9.0.0. Dump was produced from MySQL 9.0.0
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...
NOTE: Schema `test_schema` already contains a table named `test_collection`
NOTE: Schema `json_demo` already contains a table named `season`
NOTE: Schema `knex_demo` already contains a table named `knex_migrations`
NOTE: Schema `knex_demo` already contains a table named `knex_migrations_lock`
NOTE: Schema `knex_demo` already contains a table named `user`
NOTE: Schema `knex_demo` already contains a table named `user_type`
NOTE: Schema `doc-store-intro` already contains a table named `restaurant`
NOTE: Schema `doc_store_demo` already contains a table named `scores`
NOTE: Schema `node_demo` already contains a table named `scores`
NOTE: Schema `node_demo` already contains a function named `JSON_ARRAY_AVG`
NOTE: Schema `mysql_innodb_cluster_metadata` already contains a table named `async_cluster_members`
NOTE: Schema `mysql_innodb_cluster_metadata` already contains a table named `async_cluster_views`
NOTE: Schema `mysql_innodb_cluster_metadata` already contains a table named `clusters`
NOTE: Schema `mysql_innodb_cluster_metadata` already contains a table named `clusterset_members`
NOTE: Schema `mysql_innodb_cluster_metadata` already contains a table named `clusterset_views`
NOTE: Schema `mysql_innodb_cluster_metadata` already contains a table named `clustersets`
NOTE: Schema `mysql_innodb_cluster_metadata` already contains a table named `instances`
NOTE: Schema `mysql_innodb_cluster_metadata` already contains a table named `router_rest_accounts`
NOTE: Schema `mysql_innodb_cluster_metadata` already contains a table named `routers`
NOTE: Schema `mysql_innodb_cluster_metadata` already contains a view named `v2_instances`
NOTE: Schema `mysql_innodb_cluster_metadata` already contains a view named `v2_ar_members`
NOTE: Schema `mysql_innodb_cluster_metadata` already contains a view named `v2_router_rest_accounts`
NOTE: Schema `mysql_innodb_cluster_metadata` already contains a view named `v2_cs_members`
NOTE: Schema `mysql_innodb_cluster_metadata` already contains a view named `v2_gr_clusters`
NOTE: Schema `mysql_innodb_cluster_metadata` already contains a view named `v2_routers`
NOTE: Schema `mysql_innodb_cluster_metadata` already contains a view named `v2_this_instance`
NOTE: Schema `mysql_innodb_cluster_metadata` already contains a view named `v2_cs_clustersets`
NOTE: Schema `mysql_innodb_cluster_metadata` already contains a view named `v2_clusters`
NOTE: Schema `mysql_innodb_cluster_metadata` already contains a view named `v2_ar_clusters`
NOTE: Schema `mysql_innodb_cluster_metadata` already contains a view named `v2_cs_router_options`
NOTE: Schema `mysql_innodb_cluster_metadata` already contains a view named `schema_version`
NOTE: Schema `mysql_innodb_cluster_metadata` already contains a procedure named `v2_cs_add_invalidated_member`
NOTE: Schema `mysql_innodb_cluster_metadata` already contains a procedure named `v2_cs_created`
NOTE: Schema `mysql_innodb_cluster_metadata` already contains a procedure named `v2_cs_member_added`
NOTE: Schema `mysql_innodb_cluster_metadata` already contains a procedure named `v2_cs_member_rejoined`
NOTE: Schema `mysql_innodb_cluster_metadata` already contains a procedure named `v2_cs_member_removed`
NOTE: Schema `mysql_innodb_cluster_metadata` already contains a procedure named `v2_cs_primary_changed`
NOTE: Schema `mysql_innodb_cluster_metadata` already contains a procedure named `v2_cs_primary_force_changed`
NOTE: Schema `mysql_innodb_cluster_metadata` already contains a procedure named `v2_set_global_router_option`
NOTE: Schema `mysql_innodb_cluster_metadata` already contains a procedure named `v2_set_routing_option`
NOTE: Schema `mysql_innodb_cluster_metadata` already contains a procedure named `_v2_begin_cs_change`
NOTE: Schema `testting_demo` already contains a table named `user`
NOTE: Schema `my_golf_league` already contains a table named `adonis_schema`
NOTE: Schema `my_golf_league` already contains a table named `adonis_schema_versions`
NOTE: Schema `my_golf_league` already contains a table named `league`
NOTE: Schema `my_golf_league` already contains a table named `pkmap`
NOTE: Schema `my_golf_league` already contains a table named `season`
NOTE: Schema `nosql_demo` already contains a table named `restaurant`
NOTE: Schema `sql_template_tag_demo` already contains a table named `user`
NOTE: Schema `window-function-demo` already contains a table named `player`
NOTE: One or more objects in the dump already exist in the destination database but will be ignored because the 'ignoreExistingObjects' option was enabled.
Executing common preamble SQL
Executing DDL - done
Executing view DDL - done
Starting data load
2 thds indexing / 100% (144.61 MB / 144.61 MB), 47.52 MB/s (236.10K rows/s), 132 / 132 tables done
Executing common postamble SQL
Recreating indexes - done
139 chunks (745.78K rows, 144.61 MB) for 132 tables in 15 schemas were loaded in 3 sec (avg throughput 47.52 MB/s, 245.06K rows/s)
157 DDL files were executed in 0 sec.
Data load duration: 3 sec
2 indexes were recreated in 0 sec.
Total duration: 3 sec
35 rows were replaced
0 warnings were reported during the load.この出力には、すでに存在するためにスキップされたオブジェクトが表示されます。
Oracle Cloud からのロード
私のお気に入りの MySQL Shell の機能の 1 つは、Oracle Cloud ストレージバケットからデータをロードできることです。この機能を活用するための、いくつかのオプションが用意されています。
この例では、OCI CLI がインストールされていることを前提としています。
util.loadDump("example5", {osBucketName:"database_dumps"})システム上の OCI CLI の構成によっては、バケットの名前空間も含める必要がある場合もあります。
このコマンドの出力は次のとおりです。
Loading DDL and Data from OCI ObjectStorage bucket=database_dumps, prefix='example5' using 4 threads.
Opening dump...
Target is MySQL 9.0.0. Dump was produced from MySQL 9.0.0
Fetching dump data from remote location...
Listing files - done
Scanning metadata - done
Checking for pre-existing objects...
Executing common preamble SQL
Executing DDL - done
Executing view DDL - done
Starting data load
2 thds loading - 100% (144.61 MB / 144.61 MB), 85.27 KB/s (1.20K rows/s), 122 / 132 tables done
Recreating indexes - done
Executing common postamble SQL
139 chunks (745.78K rows, 144.61 MB) for 132 tables in 15 schemas were loaded in 13 sec (avg throughput 10.37 MB/s, 53.47K rows/s)
181 DDL files were executed in 14 sec.
Data load duration: 13 sec
2 indexes were recreated in 0 sec.
Total duration: 33 sec
0 warnings were reported during the load.下の画像は、ダンプをロードしたときに作成された進行状況ファイルを示しています。これは「example5」という名前のフォルダ内のOCIバケットに追加されました。
インビジブル・プライマリキーの作成
createInvisiblePKs オプションは、プライマリキーが定義されていないダンプ内のすべてのテーブルに対して、不可視のプライマリキー（インビジブル・プライマリキー）を追加します。ダンプが createInvisiblePKs オプションを使用して作成された場合、ロードの createInvisiblePKs オプションは自動的に true に設定されます。
util.loadDump("~/dumps/example2", {createInvisblePKs: true})まとめ
MySQL データベースからデータをロードするために使える MySQL Shell のユーティリティは、パワフルであるにもかかわらず使いやすい、優れたツールです。オプションを使用して、ロードが期待通りに実行されることを事前確認するドライランの実行、中断されたロードの進行状況のリセット、ロード対象の MySQL インスタンスでの既存のオブジェクトの無視、OCI ストレージバケットからの保存されたダンプデータのロードなどを行えます。MySQL Shell で利用可能な様々なロードオプションについて詳しく知るには、MySQL Shell ロードドキュメントをご覧ください。
（訳者註: 本記事の翻訳前の元記事は、2024 年 7 月 4 日に公開されました）