MySQLデータを復活させる方法 #MySQL

こんにちは。 CYBIRD Advent Calendar 2022 16日目担当の @utamakura です。
サイバードに入社して6年目になる、サーバ寄りのエンジニアです。
普段はモバイルゲーム・Webサービス開発をメインで行っていて、兼務で社内運用ツール開発、DBA業務も行っています。
15日目は @kanacha さんによる『unity1week「そろえる」の参加記録』でした。
プカプカ浮かぶアヒルがかわいいです。実際にゲームをプレイできますので、そちらも是非ご覧ください！

はじめに

モバイルゲームやWebサービスでは、データベースのユーザデータ、ログデータの保持は最重要課題です。
ユーザデータ、ログデータが万一にも消去されない為にも、セキュリティ対策を徹底したり、チーム運用ルールを制定したり、データベースストレージのRAID構成を選択したり、定期的に外部領域にバックアップを保存する等してデータの二重化、三重化を行ったりして、予めリスクヘッジを図ります。
しかし、それでも何らかの想定外の障害によって壊れてしまったり、あるいは悪意ある第三者によって破壊されてしまったりする可能性はゼロではありません。

もしあなたのサービスのMySQLデータベースが、バックアップも含めて破壊されてしまったら…考えただけで恐ろしいですよね
そんな時に役立ててほしいのが、MySQLのInnoDBはibdファイルさえ復元できればデータベースの復元が可能 という点です。
今回は、そのibdファイルからの復元手順をご紹介します。

ibdファイルって？

MySQLで保存されているテーブル定義およびテーブルデータは、デフォルトでは ibdata1ファイル としてデータ保存されています。
file-per-table モード（テーブル毎にファイルを分ける設定）を有効にしている場合は、ibdata1ファイルではなく、DB配下ディレクトリにテーブル単位で frmファイル と ibdファイル として分割して保存されます。

DB情報を管理するファイルなので間違えやすいのですが、idbではなく ibd ファイルです。

ibdファイル

file-per-table モードを使用して作成されると、テーブル単位で作成されたibdファイルに書き込まれます。
このファイルには、テーブルデータとテーブルのインデックスが含まれます。

frmファイル

MySQL テーブルのメタデータ (テーブル定義など) を含むファイルです。
バックアップの場合、バックアップ後に変更または削除されたテーブルをリストアできるように、バックアップデータとともにfrmファイルの完全セットを常に保持する必要があります。

実際に復元する際にはibdファイルに加えてfrmファイルのセットも必要ですが、こちらはメタデータですので、データベースのテーブル定義さえ分かっていればfrmファイルを新しく生成できます。
テーブル定義はマイグレーションファイルがGit管理されていれば消失していないと思いますので、その前提で話を進めます。

ibdファイルから復元する場合の注意点

ibdファイルで復元するメリット (MySQL公式ドキュメントより引用)

サーバーへの負荷が少ない。
新しい環境に、全く同じデータのバックアップをリストアできる。
mysqldump でファイル出力＆インポートするよりも速い。データの再挿入とインデックスの再構築を行うよりも、短時間で行える。

ibdファイルで復元するデメリット

復元に失敗すると、対象スキーマが利用できない状態になる可能性があり、作業リスクが高い。
- 実際にこの手順を試した際に、何度か事象が発生しました。クエリは一切通らず、対象スキーマが復旧不可の状態になります…

ibdファイルでのデータ移行は、MySQL公式ドキュメントに記載されている方法ではあるものの、復元に失敗した時のリスクを考えると、平常通りDBからダンプが取れる状況であれば素直に mysqldump 等を使った移行が望ましいです。

dumpファイルからの復元方法

今回の検証とは直接関係はありませんが、DBのリストア方法として、お馴染みの mysqldump 以外にも下記の方法があります。
いずれもMySQL 5.7以降であれば利用可能です。
この中でも、MySQL Shellは出力・リストアともにパラレル処理が可能ですので処理速度に優れており、導入の手間があるもののデータ量が多いDBを扱うほどが有効となります。

mysqlpump
- ダンプ出力がパラレル処理。（リストアはシリアル処理なのでmysqldumpと変わらない）
- テーブル定義のインデックス・外部キー制約等をデータインポート後に追加するようダンプ出力してくれるので、mysqldumpに比べて少し高速にリストアできる。
- mysqlpumpが使えるMySQLバージョンであれば、基本的に最初からインストールされている。
MySQL Shell
- ダンプ出力およびリストアがパラレル処理。 並列実行数は設定により調整可能。(mysqldump、mysqlpumpに比べてリストアが非常に高速)
- オプション機能が豊富。
- 別途インストール必要。デフォルトの実行方法が対話形式なので慣れが必要。

ibdファイルは解析できない？

ibdファイルはバイナリファイルなので、基本的には中身を確認しようとしても文字化けしてしまいますが、innodb_ruby というツールを使うことで分析が可能です。
テーブルデータの特定までは難しいのですが、テーブルスペースの内部でどのように保存されているか覗くことができるので有用なツールだと思います。

ex) テーブルスペースの中身を可視化 (space-extents-illustrate)

検証環境の用意

まずは検証の為の仮想環境を用意します。
今回はibd復元がメインテーマなので、環境構築の詳細は省きますが、下記バージョンで構築しました。

CentOS：v6.10
MySQL：v5.7.37
Ruby：v2.6.9
Vagrantfile：generic/centos6 をベースに作成。

復元の流れ

0. ibdファイルをレスキューする
 1. テーブル定義のみのDBを用意する
 2. DISCARD TABLESPACE
3. ibdファイルコピー
 4. IMPORT TABLESPACE

0. ibdファイルをレスキューする

とにもかくにも、ibdファイルを復旧させないことには始まりません。
復旧ツール、復旧業者、知り合いのスーパーエンジニア等、頼れるものは何でも頼りましょう。

今回はあくまで検証ですので、適当なibdファイルを用意しておきます。

1. テーブル定義のみのDBを用意する

作成した仮想環境にSSH接続し、まずは復元するDBを作成します。

SQL

mysql> CREATE DATABASE VERIFYDB DEFAULT CHARACTER SET utf8;

テーブル定義のみのDDLを反映します。

ターミナル

mysql -u root -p VERIFYDB < テーブル定義.sql

実行後、下記のようにDB配下のディレクトリにibdファイルとfrmファイルが生成されていることが確認できます。

DB配下のディレクトリ

/var/lib/mysql/VERIFYDB
├─ db.opt
├─ user_voice.frm
├─ user_voice#P#p0.ibd
├─ user_voice#P#p1.ibd
├─ user_voice#P#p2.ibd
├─ user_voice#P#p3.ibd
├─ user_voice#P#p4.ibd
├─ user_voice#P#p5.ibd
├─ user_voice#P#p6.ibd
├─ user_voice#P#p7.ibd
├─ user_voice#P#p8.ibd
├─ user_voice#P#p9.ibd
├─ user_voice#P#p10.ibd
├─ user_voice#P#p11.ibd
├─ user_voice#P#p12.ibd
├─ user_voice#P#p13.ibd
├─ user_voice#P#p14.ibd
├─ user_voice#P#p15.ibd
├─ user_voice#P#p16.ibd
├─ user_voice#P#p17.ibd
├─ user_voice#P#p18.ibd
├─ user_voice#P#p19.ibd
├─ voice_mst.frm
└─ voice_mst.ibd

MEMORYストレージエンジンのテーブルに関しては、保存データはメモリに格納されますのでibdファイルは作成されません。

テーブルレコードが0件でも、ibdファイルは生成されます。

パーティショニングされているテーブルは テーブル名#P#p0.ibd の命名規則でパーティション毎にファイルが作成されます。

2. DISCARD TABLESPACE

復元するためには、まず置き換える前のibdファイルを破棄する必要があります。
下記クエリを実行します。

SQL

-- 外部キー制約を一時的に無効にする
mysql> SET FOREIGN_KEY_CHECKS=0;

mysql> ALTER TABLE `user_voice` DISCARD TABLESPACE;
mysql> ALTER TABLE `voice_mst` DISCARD TABLESPACE;

mysql> SET FOREIGN_KEY_CHECKS=1;

ibdファイルが削除されたことが確認できます。

クエリ実行後

/var/lib/mysql/VERIFYDB
├─ db.opt
├─ user_voice.frm
└─ voice_mst.frm

3. ibdファイルコピー

次に、DB配下のディレクトリに、復元したいibdファイルをコピーします。
パーティショニングされているテーブルは テーブル名#P#p0.ibd の命名規則でファイルが作成されていますので全て移動させます。

コピー後

/var/lib/mysql/VERIFYDB
├─ db.opt
├─ user_voice.frm
├─ user_voice#P#p0.ibd
├─ user_voice#P#p1.ibd
├─ user_voice#P#p2.ibd
├─ user_voice#P#p3.ibd
├─ user_voice#P#p4.ibd
├─ user_voice#P#p5.ibd
├─ user_voice#P#p6.ibd
├─ user_voice#P#p7.ibd
├─ user_voice#P#p8.ibd
├─ user_voice#P#p9.ibd
├─ user_voice#P#p10.ibd
├─ user_voice#P#p11.ibd
├─ user_voice#P#p12.ibd
├─ user_voice#P#p13.ibd
├─ user_voice#P#p14.ibd
├─ user_voice#P#p15.ibd
├─ user_voice#P#p16.ibd
├─ user_voice#P#p17.ibd
├─ user_voice#P#p18.ibd
├─ user_voice#P#p19.ibd
├─ voice_mst.frm
└─ voice_mst.ibd

4. IMPORT TABLESPACE

ibdファイルをコピーしただけでは認識されません。
ibdファイルを元に復元するため、下記クエリを実行します。

SQL

-- 外部キー制約を一時的に無効にする
mysql> SET FOREIGN_KEY_CHECKS=0;

mysql> ALTER TABLE `user_voice` IMPORT TABLESPACE;
mysql> ALTER TABLE `voice_mst` IMPORT TABLESPACE;

mysql> SET FOREIGN_KEY_CHECKS=1;

以上で復元完了です。
あとは、復元したDBからdumpファイルを取得して、必要に応じてレプリカにも反映すればMySQL復活です

もし復元失敗した場合

IMPORT TABLESPACE 実行時、ibdファイルが破損していたり、MySQLバージョン問題が原因で復元失敗することがあります。

SQL

mysql> ALTER TABLE xxxxxxxx IMPORT TABLESPACE;
No connection. Trying to reconnect...
Connection id:    2
Current database: VERIFYDB

ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (111)
ERROR:
Can't connect to the server

/var/log/mysqld.log

2022-04-15T05:25:32.155917Z 2 [Warning] InnoDB: Tablespace for table `VERIFYDB`.`xxxxxxxx` is set as discarded.
2022-04-15T05:25:32.156129Z 2 [Warning] InnoDB: Cannot calculate statistics for table `VERIFYDB`.`xxxxxxxx` because the .ibd file is missing. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue.
2022-04-15 05:25:32 0x7f9ba413e700  InnoDB: Assertion failure in thread 140306449426176 in file ha_innodb.cc line 21051
InnoDB: Failing assertion: format != 0
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
05:25:32 UTC - mysqld got signal 6 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
Attempting to collect some information that could help diagnose the problem.
As this is a crash and something is definitely wrong, the information
collection process might fail.

...

このような状態になったら、復元失敗となりクエリが一切通らない状態となります。
下記手順で、一度スキーマを削除してからibd復元を最初からやり直す必要があります。

MySQLプロセスを再起動
該当のスキーマをDROP（エラーが返ってくるが削除できている）
コピーしたibdファイルがディスクに残るので手動削除

あとがき

今回、MySQLをibdファイルから復元する手順を紹介させていただきました。
データ障害は起きないに越したことはないですし、そのような事態にならないよう事前に対策を取ることが最善ではありますが、このような復旧方法もあるということを頭の片隅に留めておくだけでもいざという時の救いになるかもしれません。
参考になれば幸いです。

CYBIRD Advent Calendar 2022 17日目は @cy-tatsuya-sakai さんの「Position Based Dynamics : XPBD、SubstepXPBDをUnityで実装してみた」です。
お楽しみに！

MySQLデータを復活させる方法