今更感満載だけどMySQLのdumpリストアを検証してみる

概要

他にも書いている人たくさんいるけど、別名でリストアとかってあんまり書かれていないので、テストして残しておこうと思います。

テストケース１：mysqldumpで特定のデータベースをバックアップして、別名でリストアしてみる。

まず、testdb01というデータベースを作る。
そして、t_nameというテーブルにid4までのデータをインサートする。
その後、下記コマンドでtestdb01だけdumpを取得します。

# mysqldump -u root -p testdb01 > testdb01.sql

つぎに、testdb02という新しい空のデータベースを作成し、testdb01を更新してみる。
その後、

# mysql -u root -p testdb02 < testdb01.sql

その結果が以下の通りです。

mysql> use testdb01;
mysql> select * from t_name;

+------+-------+
| id | name |
+------+-------+
| 1 | hoge1 |
| 2 | hoge1 |
| 3 | hoge1 |
| 4 | hoge1 |
| 5 | hoge2 |
+------+-------+
5 rows in set (0.01 sec)

mysql> use testdb02;

mysql> select * from t_name;
+------+-------+
| id | name |
+------+-------+
| 1 | hoge1 |
| 2 | hoge1 |
| 3 | hoge1 |
| 4 | hoge1 |
+------+-------+
4 rows in set (0.00 sec)

ちゃんとtestdb01は更新されずに、testdb02にdumpで取得した時の状態になっています。

テストケース２：mysqldumpで全てのデータベースを取得して、特定のデータベースへリストアしてみる。

テストケース１で作ったtestdb01が存在している状態とします。
この状態で、下記コマンドで全データベースのdumpを取得します。

# mysqldump -u root -p --events -x --all-database > alldump.sql

つぎにtestdb01のデータベースを下記SQLで更新しちゃいます。

mysql> insert into t_name(id,name) values(6,'hoge01'),(7,'hoge01'),(8,'hoge01');

いまの時点でdumpデータと既存データベースに差異が発生します。
つぎにtestdb02を作成します。

mysql> create database testdb02;

この状態で先ほどのalldump.sqlをテストケースと同じようにtestdb02に流してみます。

mysql -u root -p testdb02 < alldump.sql

結果は以下のとおりです。

mysql> use testdb02;
Database changed
mysql> show tables;
Empty set (0.00 sec)

mysql> use testdb01;
Database changed
mysql> select * from t_name;
+------+-------+
| id | name |
+------+-------+
| 1 | hoge1 |
| 2 | hoge1 |
| 3 | hoge1 |
| 4 | hoge1 |
| 5 | hoge2 |
+------+-------+
5 rows in set (0.00 sec)

つまり、mysqldumpのallで取得したdumpファイルそのままでデータベース名を指定してリストアすると、dumpで取得した全てのデータが、dumpのデータまでロールバックしてしまうのです。
実行時に新しいデータベース名にしたとしても、エラーが出ません。
テストケース２の方法でリストアすると、既にデータベースがある場合はCREATE DATABASEは発行せず、テーブルのDROP,CREATEを実行するようにdumpファイルに記載があります。
これを知らないと結構面倒くさいことになる場合があります。

テストケース３：テストケース２の失敗を鑑みて、dumpファイルを修正して別名のデータベースへリストアしてみる。

テストケース２で作成したデータベースを使用します。
いまはこんな状態です。

mysql> use testdb01;
mysql> select * from t_name;
+------+-------+
| id | name |
+------+-------+
| 1 | hoge1 |
| 2 | hoge1 |
| 3 | hoge1 |
| 4 | hoge1 |
| 5 | hoge2 |
+------+-------+
5 rows in set (0.00 sec)

mysql> use testdb02;
Database changed
mysql> select * from t_name;
ERROR 1146 (42S02): Table 'testdb02.t_name' doesn't exist

テストケース２と同じように全体のdumpデータを取得します。

# mysqldump -u root -p --events -x --all-databases > alldump2.sql

testdb02にリストアしたいのですが、testdb01まで更新されてしまわないかの確認のため、testdb01のデータを更新かけます。

mysql> insert into t_name(id,name) values(6,'hoge3'),(7,'hoge3'),(8,'hoge3');
Query OK, 3 rows affected (0.02 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from t_name;
+------+-------+
| id | name |
+------+-------+
| 1 | hoge1 |
| 2 | hoge1 |
| 3 | hoge1 |
| 4 | hoge1 |
| 5 | hoge2 |
| 6 | hoge3 |
| 7 | hoge3 |
| 8 | hoge3 |
+------+-------+
8 rows in set (0.00 sec)

この状態でquitで抜けて、dumpを編集します。

# cp -p alldump2.sql alldump2.sql.org
# vi alldump2.sql
--
-- Current Database: `<strong>testdb01</strong>`
--

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `<span style="color: #ff0000;"><strong>testdb01</strong></span>` /*!40100 DEFAULT CHARACTER SET latin1 */;

USE `<span style="color: #ff0000;"><strong>testdb01</strong></span>`;

--
-- Table structure for table `t_name`
--

DROP TABLE IF EXISTS `t_name`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `t_name` (
`id` int(11) DEFAULT NULL,
`name` text
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `t_name`
--

LOCK TABLES `t_name` WRITE;
/*!40000 ALTER TABLE `t_name` DISABLE KEYS */;
INSERT INTO `t_name` VALUES (1,'hoge1'),(2,'hoge1'),(3,'hoge1'),(4,'hoge1'),(5,'hoge2');
/*!40000 ALTER TABLE `t_name` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Dumping events for database '<strong>testdb01</strong>'
--

dumpファイルにtestdb01と記載されている所があります。
ここをtestdb02に書き換えてしまいます。
というかCREATE文は「–」でコメントアウトしちゃいます。
なので、

USE `<span style="color: #ff0000;"><strong>testdb01</strong></span>`;

を

USE `<span style="color: #ff0000;"><strong>testdb02</strong></span>`;

でもいいですし、testdb02ではなくて、testdb03にしたい場合は、

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `<span style="color: #ff0000;"><strong>testdb01</strong></span>` /*!40100 DEFAULT CHARACTER SET latin1 */;

USE `<span style="color: #ff0000;"><strong>testdb01</strong></span>`;

を

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `<span style="color: #ff0000;"><strong>testdb02</strong></span>` /*!40100 DEFAULT CHARACTER SET latin1 */;

USE `<span style="color: #ff0000;"><strong>testdb02</strong></span>`;

にしてみます。

今回は、testdb02にリストアしてみることにします。
リストアするときはオプションの指定が必要なので注意します。
また、存在しないデータベースだとエラーになるので、別名のデータベース名が存在しない場合は先にcreate databaseで作っておく必要があります。
今回は既に空のtestdb02があるので問題ありません。

# mysql -u root -p testdb02 < alldump2.sql

mysql> use testdb01;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from t_name;
+------+-------+
| id | name |
+------+-------+
| 1 | hoge1 |
| 2 | hoge1 |
| 3 | hoge1 |
| 4 | hoge1 |
| 5 | hoge2 |
| 6 | hoge3 |
| 7 | hoge3 |
| 8 | hoge3 |
+------+-------+
8 rows in set (0.00 sec)

testdb01のデータは上書きされておりません。

mysql> use testdb02;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from t_name;
+------+-------+
| id | name |
+------+-------+
| 1 | hoge1 |
| 2 | hoge1 |
| 3 | hoge1 |
| 4 | hoge1 |
| 5 | hoge2 |
+------+-------+
5 rows in set (0.00 sec)

testdb02にdumpで取得したtestdb01のデータがインサートされております。
このようにすることで、MySQLのdumpをリストアが行えるようになります。

以上です。
とても基本的なところではありますが、実は結構はまりやすい個所なので、しっかりとdumpのオプションを理解した上でリストアするようにした方が良いですね。