はじめに
2023-01-17にリリースされたMySQL Shell8.0.32でexportTable(), dumpInstance(), dumpSchemas(), dumpTables() ユーティリティでwhereによるフィルタリング機能が追加されました。
今回はこちらの機能にいて紹介していきます。
使用するバージョンはMySQL8.0.32及び、MySQL Shell8.0.32です。
使い方
各Utilityの実行例
exportTable(), dumpInstance(), dumpSchemas(), dumpTables()でそれぞれwhereによるフィルタリングを行う場合の記述例となります。
|
1 2 3 4 |
shell-js> util.dumpInstance( "/mysqlshell/dumpInstance", { "where" : {"sakila.actor": "actor_id < 150"}} ) |
|
1 2 3 4 |
shell-js> util.dumpSchemas( ["sakila"], "/mysqlshell/dumpSchemas", { "where" : {"sakila.actor": "actor_id < 150"}} ) |
|
1 2 3 4 |
shell-js> util.dumpTables( "sakila", ["actor"], "/mysqlshell/dumpTables", { "where" : {"sakila.actor": "actor_id < 150"}} ) |
|
1 2 3 |
shell-js> util.exportTable( "sakila.actor", "actor.csv",{ "where" : "actor_id < 150"} ) |
次に実際にフィルタリングがされていることを確認してみます。
compression: "none"で圧縮を行わないようにして、ダンプされたファイルの中身を確認します。
|
1 2 3 4 |
shell-js> util.dumpTables( "sakila", ["actor"], "/mysqlshell/test1", {compression: "none", "where" : {"sakila.actor": "actor_id < 10"}} ) |
actor_idが10未満の行のみダンプされています。
|
1 2 3 4 5 6 7 8 9 10 |
# cat /mysqlshell/test1/sakila@actor@@0.tsv 1 PENELOPE GUINESS 2006-02-14 19:34:33 2 NICK WAHLBERG 2006-02-14 19:34:33 3 ED CHASE 2006-02-14 19:34:33 4 JENNIFER DAVIS 2006-02-14 19:34:33 5 JOHNNY LOLLOBRIGIDA 2006-02-14 19:34:33 6 BETTE NICHOLSON 2006-02-14 19:34:33 7 GRACE MOSTEL 2006-02-14 19:34:33 8 MATTHEW JOHANSSON 2006-02-14 19:34:33 9 JOE SWANK 2006-02-14 19:34:33 |
条件の記述例
記述例をいくつか紹介します。
文字列を条件にする場合、ダブルクォーテーションをエスケープするか、シングルクォーテーションを使用します。
|
1 2 3 4 5 6 7 8 9 |
shell-js> util.dumpTables( "sakila", ["actor"], "/mysqlshell/test2", {compression: "none", "where" : {"sakila.actor": "last_name = \"KILMER\""}} ) または shell-js> util.dumpTables( "sakila", ["actor"], "/mysqlshell/test3", {compression: "none", "where" : {"sakila.actor": 'last_name = "KILMER"'}} ) |
条件を組み合わせたいときはandやorを使用します。
|
1 2 3 4 |
shell-js> util.dumpTables( "sakila", ["actor"], "/mysqlshell/test4", {compression: "none", "where" : {"sakila.actor": "actor_id < 150 or actor_id > 170"}} ) |
()(括弧)を使用して複雑な条件を設定することも可能です。
|
1 2 3 4 |
shell-js> util.dumpTables( "sakila", ["actor"], "/mysqlshell/test5", {compression: "none", "where" : {"sakila.actor": "(actor_id < 50 or actor_id > 160 ) and last_name = \"KILMER\""}} ) |
この場合、actor_idが50未満または160より大きい行であること、そしてlast_nameがKILMERである行がダンプされます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
# cat /mysqlshell/test5/sakila@actor@@0.tsv 23 SANDRA KILMER 2006-02-14 19:34:33 45 REESE KILMER 2006-02-14 19:34:33 162 OPRAH KILMER 2006-02-14 19:34:33 // last_nameがKILMERである行は以下の通り mysql> select * from sakila.actor where last_name="KILMER"; +----------+------------+-----------+---------------------+ | actor_id | first_name | last_name | last_update | +----------+------------+-----------+---------------------+ | 23 | SANDRA | KILMER | 2006-02-15 04:34:33 | | 45 | REESE | KILMER | 2006-02-15 04:34:33 | | 55 | FAY | KILMER | 2006-02-15 04:34:33 | | 153 | MINNIE | KILMER | 2006-02-15 04:34:33 | | 162 | OPRAH | KILMER | 2006-02-15 04:34:33 | +----------+------------+-----------+---------------------+ 5 rows in set (0.00 sec) |
inやlikeも使用できます。
|
1 2 3 4 5 6 7 8 9 |
shell-js> util.dumpTables( "sakila", ["actor"], "/mysqlshell/test6", {compression: "none", "where" : {"sakila.actor": "actor_id in (150,151)"}} ) shell-js> util.dumpTables( "sakila", ["actor"], "/mysqlshell/test7", {compression: "none", "where" : {"sakila.actor": 'last_name like "K%"'}} ) |
条件に関数を使用することもできます。
|
1 2 3 4 |
shell-js> util.dumpTables( "sakila", ["actor"], "/mysqlshell/test8", {compression: "none", "where" : {"sakila.actor": "last_update < now()"}} ) |
複数のテーブルに対して、フィルタリングを行いたい場合は、以下のように,(カンマ)で区切ります。
|
1 2 3 4 |
shell-js> util.dumpTables( "sakila", ["actor","rental"], "/mysqlshell/test9", { "where" : {"sakila.actor": "actor_id > 150", "sakila.rental": "rental_id < 30"}} ) |
なお、1つのテーブルに対する条件をカンマで区切って記述した場合、後勝ちで後ろの条件だけが適用されるようなので、分けて記載しないように注意してください。
|
1 2 3 4 |
shell-js> util.dumpTables( "sakila", ["actor"], "/mysqlshell/test10", {compression: "none", "where" : {"sakila.actor": 'last_name = "KILMER"', "sakila.actor": "actor_id > 190"}} ) |
last_name = "KILMER"となる行は5行ありますが、actor_id > 190となる行のみダンプされています。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
mysql> select * from sakila.actor where last_name = "KILMER"; +----------+------------+-----------+---------------------+ | actor_id | first_name | last_name | last_update | +----------+------------+-----------+---------------------+ | 23 | SANDRA | KILMER | 2006-02-15 04:34:33 | | 45 | REESE | KILMER | 2006-02-15 04:34:33 | | 55 | FAY | KILMER | 2006-02-15 04:34:33 | | 153 | MINNIE | KILMER | 2006-02-15 04:34:33 | | 162 | OPRAH | KILMER | 2006-02-15 04:34:33 | +----------+------------+-----------+---------------------+ 5 rows in set (0.00 sec) # cat /mysqlshell/test10/sakila@actor@@0.tsv 191 GREGORY GOODING 2006-02-14 19:34:33 192 JOHN SUVARI 2006-02-14 19:34:33 193 BURT TEMPLE 2006-02-14 19:34:33 194 MERYL ALLEN 2006-02-14 19:34:33 195 JAYNE SILVERSTONE 2006-02-14 19:34:33 196 BELA WALKEN 2006-02-14 19:34:33 197 REESE WEST 2006-02-14 19:34:33 198 MARY KEITEL 2006-02-14 19:34:33 199 JULIA FAWCETT 2006-02-14 19:34:33 200 THORA TEMPLE 2006-02-14 19:34:33 |
フィルタリングが誤っている場合
ドキュメントには、SQLの条件は実行時にのみ検証されると書いてあります。
Note
The SQL is validated only when it is executed. If you are exporting many tables, any SQL-syntax-related issues will only be seen late in the process. As such, it is recommended you test your SQL condition before using it in a long-running export process.
検証はテーブル定義などがダンプされた後に行われます。
以下のように存在しないカラムidを条件にして実行してみます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
shell-js> util.dumpTables( "sakila", ["actor"], "/mysqlshell/test11", {"where" : {"sakila.actor": "id < 150"}} ) 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. ERROR: [Worker003]: Error while chunking `sakila`.`actor`: MySQL Error 1054 (42S22): Unknown column 'id' in 'where clause' Util.dumpTables: While 'Writing schema metadata': Fatal error during dump (MYSQLSH 52006) |
ダンプは途中で失敗します。
また、条件の記法が誤っている場合もプロセスの途中で失敗します。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
shell-js> util.dumpSchemas( ["sakila"], "/mysqlshell/test12", {"where" : {"sakila.actor": "actor_id 10"}} ) Acquiring global read lock Global read lock acquired Initializing - done 1 schemas will be dumped and within them 16 tables, 7 views, 6 routines, 6 triggers. 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. ERROR: [Worker000]: Error while chunking `sakila`.`actor`: MySQL Error 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '10) ORDER BY `actor_id` LIMIT 1' at line 1 Util.dumpSchemas: While 'Writing schema metadata': Fatal error during dump (MYSQLSH 52006) |
デフォルトでテーブルのダンプは以下の順で行われます。
- 各テーブル定義のダンプ
- 各テーブルのデータを複数のファイルに分割するための計算
- 各テーブルデータのダンプ
2のファイル分割のための計算で、WHERE条件を含んだSQLが実行されます。そのため条件に誤りがあった場合、テーブルデータのダンプが始まる前の段階で、エラーによってダンプは終了します。(threadsオプションで処理を並行して実施した場合は、2と3のフェーズが並行して行われる可能性はあります。）
ただし、chunking: falseで実行すると、データのファイル分割は行われないため、2のフェーズはスキップされます。
よってSQLは、各テーブルのデータダンプ時に初めて実行されます。そのため、一部のテーブルのダンプが完了した後で、ダンプが失敗する可能性があります。その場合、長時間実行されるダンプの最後の方で、プロセスが中断されるといったことが起こる可能性もあります。
いずれにしても、ドキュメントにある通り事前にテストを行ったほうがいいでしょう。
残念ながら、dryrunではwhere条件の検証は行われないらしく、誤った記載でもdryrunは成功します。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
shell-js> util.dumpTables( "sakila", ["actor"], "/mysqlshell/test13", {dryRun: true, "where" : {"sakila.actor": "id < 150"}} ) dryRun enabled, no locks will be acquired and no files will be created. 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 Writing DDL - done Starting data dump 0% (0 rows / ~200 rows), 0.00 rows/s, 0.00 B/s uncompressed, 0.00 B/s compressed |
また、テーブルの方が存在しない場合は特にエラーは起きずに無視されるようです。試しにsakila.actorsという存在しないテーブルを条件にすると、ダンプは成功します。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
shell-js> util.dumpTables( "sakila", ["actor"], "/mysqlshell/test14", {compression: "none","where" : {"sakila.actors": "actor_id < 10"}} ) 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% (200 rows / ~200 rows), 0.00 rows/s, 0.00 B/s Dump duration: 00:00:00s Total duration: 00:00:00s Schemas dumped: 1 Tables dumped: 1 Data size: 7.40 KB Rows written: 200 Bytes written: 7.40 KB Average throughput: 7.40 KB/s |
当然ながら、sakila.actorは全ての行がダンプされます。
|
1 2 3 4 5 6 7 8 9 10 |
# cat /mysqlshell/test14/sakila@actor@@0.tsv 1 PENELOPE GUINESS 2006-02-14 19:34:33 2 NICK WAHLBERG 2006-02-14 19:34:33 3 ED CHASE 2006-02-14 19:34:33 4 JENNIFER DAVIS 2006-02-14 19:34:33 ...(略) 197 REESE WEST 2006-02-14 19:34:33 198 MARY KEITEL 2006-02-14 19:34:33 199 JULIA FAWCETT 2006-02-14 19:34:33 200 THORA TEMPLE 2006-02-14 19:34:33 |
テーブル名を含め、条件に誤りがないかは十分なチェックが必要です。
具体的なチェック方法ですが、whereの部分をコピーしてSELECT文に書き換えて実行すると、実際にダンプされるデータの確認もできるので良いのではないかと思います。
|
1 2 3 |
"where" : {"sakila.actor": "actor_id < 10"} ↓ SELECT * FROM sakila.actor where actor_id < 10; |
まとめ
MySQL Shellのwhereによるフィルタリング機能について紹介しました。
今回は紹介できませんでしたが、同じくMySQL Shell8.0.32から特定のパーティションのみをダンプするpartitionsオプションも追加されています。これらを組み合わせることで、より柔軟に任意のデータのダンプが行えるようになりました。
一方で、記法の誤りは、コマンドを実行して即座に検証されるわけではないため、その点は注意が必要です。
