MySQL

MySQLのibdataから個別のテーブルデータをリストアする

バックアップは取っていてもリストアできないと宝の持ち腐れですね。

ibdataのコールドバックアップは取っていて、サクッと一部のテーブルのデータのみリストアする方法です。 稼働中のMySQLを止める必要がないので、一部のテーブルだけ復旧したい場合や、とりあえず昔のテーブルの状況を見たい場合などに利用可能です。 データベース全体のリストアではないので、リストアの時間を短縮したいときに使えるかと思います。

やり方としては公式のドキュメントに書いてある通りなのですが、もうちょっと細かくやり方を見ていきます。 innodb_file_per_tableがONになっていて、テーブル毎にibdataが作成されていることが前提になります。

大まかな手順は下記のようになります。

  1. 復旧したいデータベース・テーブルがない場合はあらかじめ作成しておく
  2. 該当テーブルへの変更をLOCKする
  3. テーブルスペースを削除(ibdファイルを削除)
  4. 復旧したいibdファイルをコピーする
  5. テーブルスペースをインポート
  6. Lockを解除

下記のバージョンで検証しました。

  • MySQL: 5.6.42
  • CentOS: 7.5

準備

テストのためのデータベースとテーブル・テストデータを作成します。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
mysql> CREATE DATABASE `test`  CHARACTER SET utf8mb4;
mysql> use test;
mysql> CREATE TABLE `test_table` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255),
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

mysql> insert into test_table (name , updated_at, created_at) value ('hoge', now(), now());
mysql> insert into test_table (name , updated_at, created_at) value ('fuga', now(), now());

mysql> select * from test_table;
+----+------+---------------------+---------------------+
| id | name | updated_at          | created_at          |
+----+------+---------------------+---------------------+
|  1 | hoge | 2018-12-01 11:11:37 | 2018-12-01 11:11:37 |
|  2 | fuga | 2018-12-01 11:11:57 | 2018-12-01 11:11:57 |
+----+------+---------------------+---------------------+

MySQLを止めて、/var/lib/mysql/test/test_table.ibd をバックアップします。

1
2

$ sudo systemctl stop mysqld.service

リストア

1. リストア先のデータベース・テーブルの準備

復旧したいデータベース・テーブルがない場合はあらかじめ作成します。

1
2
3
4
5
6
7
8
9
mysql> CREATE DATABASE `test`  CHARACTER SET utf8mb4;
mysql> use test;
mysql> CREATE TABLE `test_table` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255),
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

もちろんこの段階ではデータは存在しません。

1
2
mysql> select * from test_table;
Empty set (0.00 sec)

2. 該当テーブルへの変更をLOCK

該当テーブルへの変更をLOCKします。

1
2
mysql> LOCK TABLES test_table WRITE;
Query OK, 0 rows affected (0.00 sec)

3. テーブルスペースの削除

テーブルスペースを削除(ibdファイルを削除)します。

1
2
mysql> ALTER TABLE test_table DISCARD TABLESPACE;
Query OK, 0 rows affected (0.00 sec)

4. 復旧するファイルの移動

復旧したいibdファイルをコピーします。

権限も正しくmysqlユーザーで読み書きできるように設定しましょう。

1
2
3
4
5
6
$ sudo ls -la /var/lib/mysql/test/
drwx------. 2 mysql mysql  4096 Dec  1 11:44 .
drwxr-xr-x. 6 mysql mysql  4096 Dec  1 11:42 ..
-rw-rw----. 1 mysql mysql    67 Dec  1 11:42 db.opt
-rw-rw----. 1 mysql mysql  8670 Dec  1 11:42 test_table.frm
-rw-r-----. 1 mysql mysql 98304 Dec  1 11:45 test_table.ibd

5. テーブルスペースをインポート

テーブルスペースをインポートします。

1
2
mysql> ALTER TABLE test_table IMPORT TABLESPACE;
Query OK, 0 rows affected, 1 warning (0.02 sec)

6. Lockを解除

ロックを解除します。 また、データが存在することが確認できます。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test_table;
+----+------+---------------------+---------------------+
| id | name | updated_at          | created_at          |
+----+------+---------------------+---------------------+
|  1 | hoge | 2018-12-01 11:11:37 | 2018-12-01 11:11:37 |
|  2 | fuga | 2018-12-01 11:11:57 | 2018-12-01 11:11:57 |
+----+------+---------------------+---------------------+

まとめ

AWSのRDSなど、マネージドのデータベース環境を利用することが最近は多くなりました。 RDSならリストアもマネージコンソールでぽちぽちやれば簡単に切り戻せて便利ですね。

とはいえ、まだMySQLを様々な理由で自分達で運用している環境もあるかと思います。 コールドバックアップから手軽にテーブル単位でリストアできるのは便利ですね。