今回は、第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.
mysql> select version(); +-----------+ | version() | +-----------+ | 8.4.2 | +-----------+ 1 row in set (0.01 sec)
今回は、第226回のテストデータとして第2回 MySQLにはじめてのデータを入れてみるで紹介されている--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_--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.関数を使用します。第一引数にリストアしたい対象があるディレクトリを指定できます。第二引数には連想配列の形でオプションを指定できます。今回は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_local_を変更しましょう。
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.というメッセージから、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があります。このオプションはセカンダリインデックスの作成を遅延させるか否かを選択できるオプションで、
まとめ
今回と第226回 MySQL Shellを使ってdumpしてみるMySQL Shellでダンプとリストアする方法を紹介させていただきました。
MySQL Shellでのバックアップは高速に実施できて、デフォルトで圧縮されていて便利なため、使用することも増えると思います。是非活用していきましょう。