第153回　mysqlpumpを使ってバックアップを取ってみる

皆さんはMySQLからデータを論理バックアップする際にどんなコマンドを使っているでしょうか？

5.7より前のバージョンを利用していた場合は、第15回　mysqldumpを使ってバックアップするや第62回　MySQLのクライアントプログラムいろいろ［その2］で紹介したmysqldumpを使用していることが多いのではないかなとは思います。

今回は、MySQL 5.7.8から導入されたmysqlpump（誤字じゃないです）について紹介してきます。

検証環境

今回は、第125回　phpMyAdminでDockerで建てたMySQLにアクセスするで記載したdocker-composeを利用して作成します。手元で簡単に試せるように、GitHubのわたしのレポジトリにサンプルコードとして置いてあるので、気軽に試したい方はgit cloneして試してみてください。試すにはdockerとdocker-composeが必要です。

テスト用のデータとして、以下のtestデータベースにテストテーブルを追加します。

mysql> create database test;
mysql> create table test.user(name text, index name_index(name));
mysql> INSERT INTO test.user values ('kimura'),('kk2170');
mysql> SELECT * FROM test.user;
+--------+
| name   |
+--------+
| kimura |
| kk2170 |
+--------+
2 rows in set (0.00 sec)

mysqlpumpをおすすめしたい理由

個人的ににmysqlpumpを使ってて、嬉しく思う機能を幾つか紹介したいと思います。

mysqldumpの設定と互換性がある（8.0.20以降では非推奨）
リストア後にindexの構築してくれるSQLが出力される
バックアップを並列で行える

それぞれについて解説をしていきます。

mysqldumpのmy.cnfの設定と互換性がある（8.0.20以降では非推奨）

いきなり非推奨となってしまった機能の紹介で申し訳ないのですが、こちら地味ですがmysqldumpから移行を考える上では便利な機能です。mysqlpumpはmysqldumpの後継だけあって、my.cnfに書かれた設定をそのまま利用できました。

試してみる際には、MySQLのバージョンに注意して、8.0.20以降を使用している場合には、8.0.20以前のクライアントを使用して試してみてください。8.0.20以前もしくは5.7系を使用している場合には、mysqlpumpを試してみたいと思った時に、コマンドのdをpに変えるだけで、同じ設定で試してみることができます。

リストア後にindexの構築してくれるSQLが出力される

こちらも地味ですが、かなりありがたい機能です。mysqlpumpでは、リストア時にテーブル構造の復旧とデータのリストアが終わってから、indexの構築をするSQLを出力してくれます。

indexの構築が後回しになると何が嬉しいかと言うと、リストアが高速化できるようになるのです。理屈としては、テーブル構造の復旧時にindexを貼ってしまうとリストア中に都度都度indexの構築が発生してしまい、遅くなってしまいます。そのためテーブル構造を作って、データを入れて、最後にindexを作成するという順番で行う方が速くなります。

この機能はmysqlpumpのデフォルトでONになっているので、mysqlpumpを利用してバックアップをしただけでリストアの速度が高速になる、ということもあります。

mysqlpumpで実際どうなっているか確認してみましょう。ちょっと結果が長いので、見てほしい部分だけ抜きだしてみました。

$ mysqlpump -uroot test > backup.sql
$ cat backup.sql

  〈中略〉

CREATE TABLE `test`.`user` (
`name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
;
INSERT INTO `test`.`user` VALUES ("kimura"),("kk2170");
USE `test`;
ALTER TABLE `test`.`user` ADD KEY `name_index` (`name`);

  〈省略〉

INSERT INTOが終わった後で、ALTER TABLEでINDEXの追加を行っていることがわかります。こちらmysqldumpにも--disable-keysというオプションがあって同様の事ができるのですが、ストレージエンジンがMyISAMでユニークでないものと限られていたので、非常に便利な機能となります。

バックアップが並列で行える

mysqlpumpでは、バックアップを並列に行うことができます。並列性の設定としては、キューとスレッドで指定ができます。デフォルトではキューは1つで、キューごとのスレッドは2つになります。つまり、mysqldumpからmysqlpumpに変えるだけで、2並列でバックアップがされることになります。

さらにCPUやストレージに余裕がある場合に、もっと並列数を増やしたいときには、--default-parallelismオプションを使用することで、スレッド数を変更することができます。デフォルトでは--default-parallelism=2になってます。

逆にシングルスレッドで動いてほしい場合は、--default-parallelism=0を指定して、後述する--parallel-schemasオプションを指定しない場合、シングルスレッドで動いてキューも作成されません。

キューをデータベースごとに分けたい場合には、--parallel-schemasオプションを使用します。--parallel-schemas=[スレッド数:]データベース名,データベース名…とすることで、指定したデータベースを取得するためのキューが作成され、複数のデータベースを一度に取得することができます。ここで指定したスレッド数がキューで使用されます。指定を行わなかった場合は、--default-parallelismオプションの値が設定されます。

複数のキューを使用したい場合には、--parallel-schemasオプションを必要なだけ指定をしましょう。

使う上での注意

5.7.11より前のバージョンのmysqlpumpには、並列に実行した際に--single-transactionを使用しても、一貫性が保証されないという仕様がありました。5.7.11からは修正されているので、それ以降を使用している場合は問題ないのですが、5.7.10以前のmysqlpumpをご利用の際には注意が必要です。その場合には、--default-parallelism=0を指定して、--parallel-schemasを使わないようにしましょう。

まとめ

さて、こんなに便利になったmysqlpumpですが、MySQL 5.7.8から同梱されるようになったことから薄々と察してもらえると思いますが、MySQL 5.6以前に関しては使用することが保証されていません。バージョン間で違うコマンドを使うのは嫌だなと思った方もおられるかもしれませんが、MySQL 5.6は2020年2月1日にEOLとなっています。つまり、EOLで無いMySQLをご利用の方なら利用できることになります。まだ、古いバージョンのMySQLをご利用の方は頑張ってアップグレードを行いましょう。

MySQLで論理バックアップしたくなったら、ぜひmysqlpumpを思い出してみてください。