[MySQL] dumpとリストア入門

この記事では、MySQLのdumpとリストアの方法を初心者向けに解説します。
環境: MySQL:8.0.40

dumpを取る...DBのバックアップを取ること
リストアする...バックアップをDBに適用させて、データの復元を行うこと

dumpとリストアの実行方法

dumpの取り方

• mysqldumpコマンドを使います
• [option]には、--host、--port、--user、--passwordなど皆様の環境に合わせて設定してください
• dump.sqlにdump結果がSQLで保存されます

$ mysqldump [option] > dump.sql

リストアの方法

• dumpしたファイルを読み込ませてデータを復元します

$ mysql [option] < dump.sql

実際のDBを使ってやってみる

現在データベースmainの中にあるusersテーブルを元に解説します。

mysql> select database();
+------------+
| database() |
+------------+
| main       |
+------------+
1 row in set (0.02 sec)

mysql> select * from users;
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  1 | number_1 |   10 |
|  2 | number_2 |   20 |
|  3 | number_3 |   30 |
+----+----------+------+
3 rows in set (0.01 sec)

usersテーブルのデータをdumpしてみる

データベースmainにあるusersテーブルを、まるごと全てdumpする

$ mysqldump [option] main users > users_dump.sql

dump結果のusers_dump.sqlを見てみる(抜粋しています)

$ vi users_dump.sql
・・・
DROP TABLE IF EXISTS `users`;

CREATE TABLE `users` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `age` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

LOCK TABLES `users` WRITE;

INSERT INTO `users` VALUES (1,'number_1',10),(2,'number_2',20),(3,'number_3',30);

UNLOCK TABLES;
・・・

中身は、下記の処理がSQLで書かれています。

1. usersテーブルが存在していたら、DROPで削除
2. CREATE TABLEでusersテーブルを作り直し
3. usersテーブルに書き込みロック
4. 元々usersテーブルに入っていたデータをusersテーブルにINSERTする
5. ロックを解除

このSQLをDBに読み込ませてあげることで、データの復元をすることができます。

usersテーブルのデータをリストアしてみる

下記の手順でリストアしてみます。

1. usersテーブルの中身を削除
2. 先ほど取ったdumpファイルを使って復元

# データ削除
mysql> delete from users;
Query OK, 3 rows affected (0.07 sec)

# リストア
$ mysql [option] main < users_dump.sql

# usersテーブルのデータが復元されていることを確認
mysql> select * from users;
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  1 | number_1 |   10 |
|  2 | number_2 |   20 |
|  3 | number_3 |   30 |
+----+----------+------+
3 rows in set (0.03 sec)

dumpの取り方色々

オプションを設定すると、さまざまなdumpの取り方ができます。

• mainのusersのidが1のもののみdumpを取る

mysqldump [option] main users --where id=1 > users_dump.sql

• スキーマ情報を省略して、データだけをdump

mysqldump [option] --no-create-info main users > users_dump.sql

• リストア時に、データをreplace intoで入れられるようにする

mysqldump [option] --replace main users > users_dump.sql

• リストア時に、データをinsert ignore intoで入れられるようにする

mysqldump [option] --insert-ignore main users > users_dump.sql

参考