Zenn
🐳

MySQLが起動しない場合にデータを移行する方法

2024/12/31に公開
MySQL
tech

経緯

バックアップから復元してください。えっ？バックアップ取ってない？って時に利用する方法です。

環境

dockerを利用して検証します。

version: "3"

services:
  db:
    image: mysql:5.7
    ports:
      - 3306:3306 
    environment:
      MYSQL_ROOT_PASSWORD: root
      MYSQL_DATABASE: sample
    volumes:
      - ./data:/var/lib/mysql

はじめに

起動直後、volumesで指定したディレクトリは以下のようになります。

└── data
    ├── mysql
    ├── performance_schema
    ├── sample
    └── sys

これらのディレクトリはdatabaseのファイルが格納されています。試しにshow databases;で確認すると、以下の結果が得られます。

Database
---
information_schema
mysql
performance_schema
sample
sys

今回対象としているsampleデータベースはまだテーブルが作成されていない状態です。ディレクトリにはdb.optファイルのみ存在しています。

./data/sample
└── db.opt

試しに以下のSQLでテーブルを作成してみます。

create table IF not exists `sample_table`
(
    `id`    INT(20) AUTO_INCREMENT,
    `name`  VARCHAR(20) NOT NULL,
    PRIMARY KEY (`id`)
)

次のように.frmと.ibdのファイルが作成されました。

./data/sample
├── db.opt
├── sample_table.frm
└── sample_table.ibd

.frmファイルはMySQLのテーブル定義を含むファイルです。.ibdファイルはテーブルスペースファイルと呼ばれており、InnoDBのテーブルは.ibdファイルとして出力されます。ただし、innodb_file_per_tableを有効にしている時にテーブル毎に出力されます。MySQLの5.6.7以降にデフォルトで有効です。

そもそも起動ができない場合はどんな時？

テーブル定義の情報は.frmだけでなく、InnoDBデータディクショナリにも格納されます。何らかの原因で不整合が発生した場合、起動ができなくなることがあるようです。例えば、CREATE TABLEやALTER TABLEの最中にサーバーが落ちたりすることが原因になり得ます。

他にも、サーバーが落ちることによりInnoDBが破損する場合もあるようです。急なシャットダウンやメモリ不足によりサーバーが落ちることにより、ページが不完全な形で保存されてしまい、再起動時にエラーとなるようです。ページはデータファイルとメモリ間で一度に転送するデータ量を表す単位であり、1行のデータサイズに依存し、複数行の可能性もあります。

自分はDockerをローカルで動かしている際に、いつの間にか起動がしなくなったことがあります。きちんとコンテナを止めなかったり、強制シャットダウンとしてしまった時に同様のことが起きた可能性があります。実際、工場のマシンでは停電によるシャットダウンやハードウェアの故障が原因となることが多いのではないかと思いました。

InnoDBの強制的なリカバリーから復元

innodb_force_recoveryの設定値を徐々に調整し、mysqlを起動させデータダンプを取得する方法です。起動さえできてしまえば、通常のバックアップの取得と同じ方法でデータダンプをとることが可能です。ただし、設定値によりデータファイルが永続的に破損する場合があるので注意が必要です。

各設定値は公式ドキュメントに以下のように記載されています。設定値は1から1つずつ上げて起動を確かめます。永続的な破損の可能性が出てくるのは4以上となります。

1 (SRV_FORCE_IGNORE_CORRUPT)
破損したページを検出した場合でも、サーバーが動作できるようにします。SELECT * FROM tbl_name での破損したインデックスレコードおよびページの飛び越しを試行します。これが、テーブルのダンプに役立ちます。

2 (SRV_FORCE_NO_BACKGROUND)
マスタースレッドや、すべてのパージスレッドが実行されないようにします。パージ操作中にクラッシュが発生しそうになった場合は、このリカバリの値によって回避されます。

3 (SRV_FORCE_NO_TRX_UNDO)
クラッシュリカバリのあとにトランザクションロールバックを実行しません。

4 (SRV_FORCE_NO_IBUF_MERGE)
挿入バッファーのマージ操作を回避します。その操作によってクラッシュが発生しそうになった場合は、それが回避されます。テーブル統計を計算しません。この値を指定すると、データファイルが永続的に破損する場合があります。この値を使用したあと、すべてのセカンダリインデックスを削除して再作成するように準備してください。MySQL 5.6.15 の時点では、InnoDB を読み取り専用に設定します。

5 (SRV_FORCE_NO_UNDO_LOG_SCAN)
データベースを起動するときに、Undo ログを参照しません。InnoDB は、未完了のトランザクションでさえコミット済みとして処理します。この値を指定すると、データファイルが永続的に破損する場合があります。MySQL 5.6.15 の時点では、InnoDB を読み取り専用に設定します。

6 (SRV_FORCE_NO_LOG_REDO)
リカバリに関連した Redo ログのロールフォワードを実行しません。この値を指定すると、データファイルが永続的に破損する場合があります。データベースページを廃止された状態のままにし、それによって B ツリーやその他のデータベース構造にさらに多くの破損が発生する可能性があります。MySQL 5.6.15 の時点では、InnoDB を読み取り専用に設定します。

ibdファイルからの復元

Transportable Tablespace機能を利用した復元方法です。公式ドキュメントによると各テーブルを.ibdファイルとして出力するメリットは以下の理由があるようです。

  • 本番サーバーに余計な負荷を掛けずにレポートを実行するため。
  • 新しいスレーブサーバーに、あるテーブルとまったく同じデータをセットアップするため。
  • 問題や誤りが発生したあとに、テーブルのバックアップ版をリストアするため。
  • mysqldump コマンドの結果をインポートするよりも、データを移動させる方が速いため。データの再挿入とインデックスの再構築を行うよりも、データがすぐに使用できるためです。
  • システム要件により適したストレージ媒体を持つサーバーに file-per-table テーブルスペースを移動するため。たとえば、アクセス頻度の高いテーブルを SSD デバイスに置いたり、大規模なテーブルを大容量の HDD デバイスに置いたりする場合です。

元々の想定でもリストアの用途として用意されたもののようです。後はパフォーマンスの改善でしょうか。

手順

  1. 移行先のデータベースを用意
  2. 移行先のデータベースに移行元と同じテーブルを作成
  3. 移行先の対象テーブルに対してTABLESPACEの削除を実行
  4. 移行元の対象テーブルの.ibdファイルを移行先にコピー
  5. 移行先の対象テーブルに対してTABLESPACEのインポートを実行

1と2については説明を省略します。元々のDDLから復元を行ってください。

TABLESPACEを削除するためには以下のSQLを実行します。

ALTER TABLE sample_table DISCARD TABLESPACE;

実行すると.ibdファイルが削除されます

./data/sample
├── db.opt
└── sample_table.frm

0 directories, 2 files

移行元の.ibdファイルをコピーして、以下のコマンドを実行します。

ALTER TABLE sample_table IMPORT TABLESPACE;

後は全テーブルについても同様の方法で復元が可能です。

まとめ

正直なところ、なんでバックアップ取ってないの？と不満でしたが、ibdファイルからの復元方法があることがわかりよかったです。 :blush:

参考

Discussion