MySQL道普請便り

第229回MySQL Shellを使ってリストアしてみる

今回は、第226回 MySQL Shellを使ってdumpしてみるで紹介したMySQL Shellを使って、dumpしたデータをリストアする方法を紹介します。

検証環境

今回はDockerで建てたMySQLを使用します。以下のコマンドでDockerを建てて、ローカルからアクセスします。

% docker run --platform linux/x86_64 -p 127.0.0.1:3307:3306 -e MYSQL_ROOT_PASSWORD=my-secret-pw -e MYSQL_USER=kk2170 -e MYSQL_PASSWORD=my-secret-pw -d mysql:8.4.2 --secure-file-priv='/tmp'

今回はMySQL Shellをインストールして実行していきますが、いったん従来のmysqlクライアントでアクセスが可能であることを確認しましょう。方法は以下の通りです。

% mysql -uroot -pmy-secret-pw -h127.0.0.1 -P3307

執筆時点では、以下の通りMySQL 8.4.2を使用しています。

mysql>  select version();
+-----------+
| version() |
+-----------+
| 8.4.2     |
+-----------+
1 row in set (0.01 sec)

今回は、第226回のテストデータとして第2回 MySQLにはじめてのデータを入れてみるで紹介されている「サンプルデータその3、KEN_ALL.CSV」のデータを利用して行うため、--secure-file-priv='/tmp'を指定しています。

コンテナ内にunzipが入っていないため、unzipからiconvまでのコマンドをローカルで行い、docker cpでファイルを事前に送り、その後LOAD DATA INFILEを実行しています。

ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statementというエラーが表示された場合は、KEN_ALL_CSV_UTF8.csvを置いたファイルが--secure-file-privで指定を行っている'/tmp'配下にあるか確認してみてください。

第226回 MySQL Shellを使ってdumpしてみるの通りにJSモードに切り替えて、dumpTablesを使用してテーブルのdumpを取得しておきましょう。

 MySQL  127.0.0.1:3307 ssl  zipcode  SQL > \js
Switching to JavaScript mode...
 MySQL  127.0.0.1:3307 ssl  zipcode  JS > util.dumpTables('zipcode', ['zipcode'], '/tmp/zipcode')
〈省略〉

下記のように作成されていれば問題ありません。

% ls /tmp/zipcode
@.done.json     zipcode.json            zipcode@zipcode@0.tsv.zst
@.json          zipcode.sql             zipcode@zipcode@0.tsv.zst.idx
@.post.sql      zipcode@zipcode.json    zipcode@zipcode@@1.tsv.zst
@.sql           zipcode@zipcode.sql     zipcode@zipcode@@1.tsv.zst.idx

ローカルマシンからmysqlshellを実行した場合はローカルマシンの/tmp配下に作成されるため、docker cpを使って/tmpに取得したdumpファイルを移動しておきましょう。docker psでコンテナidを調べましょう。

% docker ps
CONTAINER ID   IMAGE         COMMAND                   CREATED          STATUS          PORTS                                 NAMES
6a4220322603   mysql:8.4.2   "docker-entrypoint.s…"   31 minutes ago   Up 31 minutes   33060/tcp, 127.0.0.1:3307->3306/tcp   admiring_heisenberg

調べたコンテナidを使用して送信します。

% docker cp /tmp/zipcode 6a4220322603:
/tmp/zipcode
Successfully copied 2.2MB to 6a4220322603:/tmp/zipcode

リストアしてみる

それでは早速リストアを試してみましょう。リストア先としてzipcodeデータベースをdropしておきましょう。ただ、その前に検証前と後でデータの数などが変わっていないか軽く調べるため、COUNTでテーブル全体の件数を記憶してから実施しましょう。

 MySQL  127.0.0.1:3307 ssl  zipcode  SQL > select COUNT(*) from zipcode;
+----------+
| COUNT(*) |
+----------+
|   124703 |
+----------+
1 row in set (0.0260 sec)

 MySQL  127.0.0.1:3307 ssl  SQL > DROP DATABASE zipcode2;
Query OK, 1 row affected (0.0111 sec)

zipcodeを削除できたのでリストアを実施してみましょう。リストアにはutil.loadDump()関数を使用します。第一引数にリストアしたい対象があるディレクトリを指定できます。第二引数には連想配列の形でオプションを指定できます。今回はdryRunというオプションを有効にしています。このオプションを使用すると、どのような実行がされるのか確認することができます。まずはSQLモードになっているので、一旦JSモードに切り替えてから実施してみましょう。

 MySQL  127.0.0.1:3307 ssl  zipcode  SQL > \js
Switching to JavaScript mode...
 MySQL  127.0.0.1:3307 ssl  zipcode  JS > util.loadDump("/tmp/zipcode",{dryRun: true});
ERROR: The 'local_infile' global system variable must be set to ON in the target server, after the server is verified to be trusted.
Util.loadDump: local_infile disabled in server (MYSQLSH 53025)

実行してみると、上記のようにエラーが発生しました。ローカルにあるファイルを読み取る権限がないため、loadに失敗したことがわかります。なので、local_infileができるようにlocal_infileを変更しましょう。

 MySQL  127.0.0.1:3307 ssl  zipcode  JS > \sql
Switching to SQL mode... Commands end with ;
 MySQL  127.0.0.1:3307 ssl  zipcode  SQL > SET GLOBAL local_infile=on;
Query OK, 0 rows affected (0.0071 sec)

変更できたので実施していきましょう。

 MySQL  127.0.0.1:3307 ssl  JS > util.loadDump("/tmp/zipcode",{dryRun: true});
Loading DDL and Data from '/tmp/zipcode' using 4 threads.
Opening dump...
dryRun enabled, no changes will be made.
Target is MySQL 8.4.2. Dump was produced from MySQL 8.4.2
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 / 15.43 MB), 0.00 B/s (0.00 rows/s), 1 / 1 tables done
Recreating indexes - done
No data loaded.
2 DDL files were executed in 0 sec.
Total duration: 0 sec
0 warnings were reported during the load.

気になるところとしては、4スレッドで実施されていることがわかります。CPUのスレッド数に余裕がある場合は、スレッド数を変更しても良いかもしれません。

またdryRun enabled, no changes will be made.からdryRunモードで実施できていることやTarget is MySQL 8.4.2. Dump was produced from MySQL 8.4.2というメッセージから、dump先のバージョンとdumpが作成されたバージョンが確認できます。

続けて、dryRunを外して実際にリストアしてみましょう。

 MySQL  127.0.0.1:3307 ssl  JS > util.loadDump("/tmp/zipcode");
Loading DDL and Data from '/tmp/zipcode' using 4 threads.
Opening dump...
Target is MySQL 8.4.2. Dump was produced from MySQL 8.4.2
Scanning metadata - done
Checking for pre-existing objects...
Executing common preamble SQL
Executing DDL - done
Executing view DDL - done
Starting data load
1 thds loading \ 100% (15.43 MB / 15.43 MB), 0.00 B/s (0.00 rows/s), 1 / 1 tables done
Executing common postamble SQL
Recreating indexes - done
2 chunks (124.70K rows, 15.43 MB) for 1 tables in 1 schemas were loaded in 0 sec (avg throughput 15.43 MB/s, 124.70K rows/s)
2 DDL files were executed in 0 sec.
Data load duration: 0 sec
Total duration: 1 sec
0 warnings were reported during the load.

違う点としては、dryRun関連のメッセージが消えたのと、0%だった進捗が100%になっていることがわかります。データの確認をしてみましょう。

 MySQL  127.0.0.1:3307 ssl  JS > \sql
Switching to SQL mode... Commands end with ;
 MySQL  127.0.0.1:3307 ssl  zipcode  SQL > select COUNT(*) from zipcode;
+----------+
| COUNT(*) |
+----------+
|   124703 |
+----------+
1 row in set (0.0260 sec)

もともと取得していたSELECT文での総数と比較しても同数になっているので、リストアができていることがわかりました。

今回は同名のデータベースを作成しましたが、schemaオプションを使用すると別のデータベースにリストアすることも可能です。現在のデータベースの数を数えておきましょう。

 MySQL  127.0.0.1:3307 ssl  zipcode2  SQL > show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| zipcode            |
+--------------------+
5 rows in set (0.0046 sec)

5件であることがわかります。ではこれにschemaオプションを使用してリストアを実施してみましょう。

 MySQL  127.0.0.1:3307 ssl  zipcode  SQL > \js
Switching to JavaScript mode...
 MySQL  127.0.0.1:3307 ssl  zipcode  JS > util.loadDump("/tmp/zipcode",{schema: 'zipcode2'});
 〈 省略 〉
  MySQL  127.0.0.1:3307 ssl  zipcode  JS > \sql
Switching to SQL mode... Commands end with ;
 MySQL  127.0.0.1:3307 ssl  zipcode2  SQL > show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| zipcode            |
| zipcode2           |
+--------------------+
6 rows in set (0.0045 sec)

zipcode2というスキーマにリストアされたことがわかります。

その他の便利なオプションとしてはdeferTableIndexesがあります。このオプションはセカンダリインデックスの作成を遅延させるか否かを選択できるオプションで、⁠ off | fulltext | all ]の3種類が選択できます。offの場合はテーブルの作成中にインデックスが構築されます。fulltextを指定した場合は全文検索インデックだけ最後に構築します。allを指定した場合は、全部のセカンダリインデックスの作成が遅延されます。ただ、auto incrementでユニークなものなどがある場合は、8.0.21ではallを指定を避けたほうが良いそうです。

まとめ

今回と第226回 MySQL Shellを使ってdumpしてみるMySQL Shellでダンプとリストアする方法を紹介させていただきました。

MySQL Shellでのバックアップは高速に実施できて、デフォルトで圧縮されていて便利なため、使用することも増えると思います。是非活用していきましょう。

おすすめ記事

記事・ニュース一覧