第226回　MySQL Shellを使ってdumpしてみる

今回は、第214回　MySQL ShellでMySQLに接続してみるで紹介させていただいたMySQL Shellを使って、MySQL Shellでダンプを取得する方法を紹介したいと思います。

検証環境

今回は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)

今回は、テストデータとして第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'配下にあるか確認してみてください。

MySQL Shellに接続してdumpしてみる

今回は、第214回と同様にDockerのイメージにあるMySQL Shellを使用して接続をしていきます。まずは、対話モードでも実行を行いたいので対話モードでJavaScriptを実行します。

SQLがデフォルトのモードになっているので\jsでJavaScriptに変更します。

 MySQL  localhost  SQL > \js
Switching to JavaScript mode...
 MySQL  localhost  JS >

とりあえず、util.dumpTablesを使ってテーブル単位のダンプを取得してみましょう。

あまりないとは思いますが、インターネットに接続できない状況など、そもそもブラウザを使わずにどんなオプションがあるか確認したい場合もあると思います。その場合は\?と入力した後に使いたい機能を入れると、必要な引数や使用できるオプションを確認できます。

これが動かない場合は、上記のJSモードへの切り替えがうまくいっているか確認をしてみてください。

 MySQL  localhost  JS > \? util.dumpTables
NAME
      dumpTables - Dumps the specified tables or views from the given schema to
                   the files in the target directory.

SYNTAX
      util.dumpTables(schema, tables, outputUrl[, options])

WHERE
      schema: Name of the schema that contains tables/views to be dumped.
      tables: List of tables/views to be dumped.
      outputUrl: Target directory to store the dump files.
      options: Dictionary with the dump options.
  〈省略〉

この説明を見ると、第1引数にデータベース名を、第2引数に配列で取得したいテーブル名を入れて、第3引数に出力したいディレクトリを指定すれば動く事がわかります。

では試してみましょう。zipcodeデータベースのzipcodeテーブルのダンプを/tmp/zipcodeディレクトリに作成してみます。

 MySQL  localhost  JS > util.dumpTables('zipcode', ['zipcode'], '/tmp/zipcode')
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
97% (124.69K rows / ~128.08K 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: 15.43 MB
Compressed data size: 2.19 MB
Compression ratio: 7.0
Rows written: 124695
Bytes written: 2.19 MB
Average uncompressed throughput: 15.43 MB/s
Average compressed throughput: 2.19 MB/s

確認してみましょう。/tmp/zipcodeディレクトリに移動して結果を見てみます。

bash-5.1# pwd
/tmp/zipcode
bash-5.1# ls
@.done.json  @.post.sql  zipcode.json  [email protected]  zipcode@[email protected]      zipcode@zipcode@@1.tsv.zst
@.json       @.sql       zipcode.sql   [email protected]   zipcode@[email protected]  zipcode@zipcode@@1.tsv.zst.idx
bash-5.1#

このように、ダンプが取得されていることがわかります。mysqldumpや、第153回で紹介させていただいたmysqlpumpで取得した時とは形式が異なることが、パッと見てわかると思います。

MySQL Shellで使える3種のdump方法

MySQL Shellではダンプを取る方法として以下の3つがあります。

util.dumpInstance
util.dumpSchemas
util.dumpTables

それぞれ上から順に軽く説明をしていくと、util.dumpInstanceはMySQLサーバにある全部のデータベースのbackupを取得できます。全データベースを出力するので、引数にはファイルの出力先だけ記載すれば済みます。

 MySQL  localhost  JS > util.dumpInstance('/tmp/dump_instance')
   〈省略〉
 bash-5.1# pwd
/tmp/dump_instance
bash-5.1# ls
@.done.json  @.post.sql  @.users.sql   zipcode.sql           [email protected]        zipcode@[email protected]  zipcode@zipcode@@1.tsv.zst.idx
@.json       @.sql       zipcode.json  [email protected]  zipcode@[email protected]  zipcode@zipcode@@1.tsv.zst

@.users.sqlが追加で出力されたのがわかります。中を覗いてみると、Dockerに設定したアカウントなどを見て確認できます。MySQL全体の論理バックアップを取りたい場合、これで簡単に取得できます。

util.dumpSchemasは指定したデータベースに関して取得できる機能になります。Schema「s」となっていることからわかる通り、複数のデータベースを指定してバックアップを取得できます。

 MySQL  localhost  JS > util.dumpSchemas(['zipcode'], '/tmp/dumpschemas')
   〈省略〉
bash-5.1# pwd
/tmp/dumpschemas
bash-5.1# ls
@.done.json  @.post.sql  zipcode.json  [email protected]  zipcode@[email protected]      zipcode@zipcode@@1.tsv.zst
@.json       @.sql       zipcode.sql   [email protected]   zipcode@[email protected]  zipcode@zipcode@@1.tsv.zst.idx

このように簡単に取得することができました。

まとめ

今回は、MySQL Shellでダンプを取る方法を紹介しました。デフォルトで圧縮が有効になっていたり並列するなど、かなり便利な機能となります。MySQL公式の機能として活用できる点もふまえると、InnoDB以外を使用している場合や論理バックアップで満たせない性能要件などが無い場合は、第一候補になるのではないかと思います。

ただ、mysqldumpやmysqlpumpとは違う形式で出力されるので、出力されたSQLに改編を加えていた場合は注意をしましょう。特にmysqlpumpは8.4以降では削除されてしまったため、今後のバックアップにMySQL Shellを活用してみてはいかがでしょうか。

今後は、リストア方法や細かい使い方について解説をしていく予定です。