DevOpsツールとベストプラクティスの習得 - DevOpsナレッジハブ DevOpsツールとベストプラクティスの習得 - DevOpsナレッジハブ

破損したMySQLテーブルの復旧：実践的アプローチ

MySQLテーブルの破損を診断し、既存データを保護し、InnoDBおよびMyISAM向けの安全な復旧パスを選択する方法。

破損したMySQLテーブルの復旧：実践的アプローチ

テーブルの破損は、迅速かつ慎重な対応が両方とも求められる問題の一つです。不適切な修復コマンドは、回復可能なインシデントを永久的なデータ損失に変えてしまう可能性があります。最初の目標はテーブルを「修復」することではありません。最初の目標は、まだ保持しているすべてのバイトを保存し、損傷の拡大を防ぎ、その後に最もリスクの低い復旧パスを選択することです。

正確な手順はストレージエンジンに大きく依存します。InnoDBの復旧は通常、サーバーを起動してクリーンなデータをダンプするか、バックアップから復元するために十分な時間を確保することに重点を置きます。MyISAMの復旧は、多くの場合テーブル修復ツールを使用します。これらは異なるプレイブックとして扱い、互換性のないコマンドとして扱わないでください。

MySQLテーブル破損の理解

復旧に取り組む前に、テーブル破損が何を意味し、なぜ発生するのかを理解することが重要です。破損は、テーブルのファイル内の内部構造やデータが一貫性を失ったり、MySQLサーバーから読み取れなくなったりした場合に発生します。

破損の一般的な原因

MySQLテーブルの破損にはいくつかの要因が寄与します：

  • ハードウェア障害：故障したハードドライブ、不良RAM（特にECCメモリがない場合）、または信頼性の低い電源（UPSなし）は、書き込み中にデータが誤って書き込まれたり失われたりする原因となります。
  • オペレーティングシステムの問題：OSのバグ、ファイルシステムエラー、またはカーネルパニックは、MySQLがデータファイルを一貫して読み書きする能力を妨げる可能性があります。
  • 不適切なシャットダウン：グレースフルシャットダウンプロセスなしでのMySQLサーバーの突然の終了（例：停電、kill -9、システムクラッシュ）は、データファイルを不整合な状態のままにする可能性があります。
  • MySQLのバグ：安定版リリースでは稀ですが、MySQLサーバー自体の特定のバグが特定の状況下で破損を引き起こす可能性があります。
  • ディスク容量の問題：書き込み操作中にディスク容量が不足すると、不完全なデータファイルが発生する可能性があります。
  • マルウェア/ウイルス：データベースサーバーではあまり一般的ではありませんが、悪意のあるソフトウェアがファイルを破損することがあります。

破損の症状

破損の兆候を早期に認識することは、復旧に大きく役立ちます。一般的な症状は次のとおりです：

  • エラーメッセージ：MySQLサーバーログまたはクライアントアプリケーションに、「テーブルはクラッシュしたとマークされており、修復が必要です」、「ファイル '<テーブル>.frm' を開けません」、「ストレージエンジンからエラーNを受け取りました」、または「テーブル '<テーブル>' のインデックスが破損しています」などのエラーが表示されます。
  • 予期しないクエリ結果：データが含まれているはずのテーブルに対して、クエリが誤ったデータ、不完全な結果、または結果をまったく返さない。
  • サーバークラッシュ/再起動：特定のテーブルにアクセスしようとすると、MySQLサーバーが予期せずクラッシュする。
  • 高いCPU/I/O使用率：明確な理由もなくサーバーが異常に高いリソース消費を示す。これは多くの場合、破損したデータの読み取り試行の繰り返し失敗によるものです。
  • テーブルにアクセスできない：テーブルのクエリ、更新、または削除ができない。

破損したテーブルの検出

迅速な検出は、データ損失とダウンタイムを最小限に抑えるための鍵です。MySQLは、破損したテーブルを識別するためのいくつかのツールと方法を提供しています。

1. MySQLエラーログ

error.logファイル（場所はOSによって異なり、Linuxでは/var/log/mysql/error.logなど）は、最初の防御線です。MySQLは、サーバーの起動、シャットダウン、およびテーブル破損に関連するものを含む重大なエラーに関する詳細情報を記録します。これらのログを定期的に確認してください。

2. CHECK TABLEステートメント

CHECK TABLE SQLステートメントは、1つ以上のテーブルのエラーをチェックする最も簡単な方法です。各テーブルのステータスを返し、OKかCorruptedかを示します。

-- 単一のテーブルをチェック
CHECK TABLE your_database.your_table;

-- 複数のテーブルをチェック
CHECK TABLE tbl_name1, tbl_name2, tbl_name3;

-- 拡張チェックを実行（より徹底的だが低速）
CHECK TABLE your_database.your_table EXTENDED;

3. mysqlcheckユーティリティ

mysqlcheckは、テーブルのチェック、修復、最適化、分析を行うコマンドラインクライアントです。これは基本的にCHECK TABLE、REPAIR TABLE、ANALYZE TABLE、OPTIMIZE TABLEステートメントのラッパーであり、バッチ操作に便利です。

# 特定のデータベース内のすべてのテーブルをチェック
mysqlcheck -u root -p --databases your_database --check

# すべてのデータベース内のすべてのテーブルをチェック
mysqlcheck -u root -p --all-databases --check

# すべてのデータベースのチェックと修復を組み合わせる（自動修復）
mysqlcheck -u root -p --all-databases --check --auto-repair

開始前の重要な準備

復旧を試みる前に、さらなるデータ損失を防ぐために以下の重要な手順に従ってください。

1. 即時バックアップ！（論理バックアップおよび/または物理バックアップ）

これは最も重要なステップです。破損を疑っている場合でも、修復を試みる前にバックアップを作成し、フォールバックを確保してください。サーバーがまだ実行中で影響を受けたデータを読み取れる場合は、mysqldumpを使用した論理バックアップを優先してください。サーバーがダウンしているか不安定な場合は、MySQLを停止した状態でデータディレクトリまたは影響を受けたデータベースディレクトリの物理コピーを取得してください。環境がスナップショットを使用している場合は、設定を変更する前にスナップショットを取得してください。

# 例：データベースの論理バックアップを作成
mysqldump -u root -p your_database > /path/to/your_database_backup_pre_corruption.sql

2. 影響を受けたテーブル/データベースへの書き込みを停止

修復プロセス中のさらなる破損を防ぎ、データの一貫性を確保するために、影響を受けたテーブルまたはデータベース全体へのすべての書き込み操作を停止してください。これは以下の方法で実現できます：

  • データベースとやり取りするアプリケーションサーバーを停止する。
  • データベースを読み取り専用モードにする（可能な場合）。
  • FLUSH TABLES WITH READ LOCK;を使用する（スーパー権限が必要、UNLOCK TABLES;が発行されるまですべての書き込みをブロック）。
  • 破損が深刻な場合は、MySQLサーバーを完全に停止する。

3. ストレージエンジンを特定

MySQLは主にInnoDBとMyISAMのさまざまなストレージエンジンをサポートしています。復旧手順はこれらの間で大きく異なります。破損したテーブルのストレージエンジンを特定してください：

SHOW CREATE TABLE your_database.your_table;

出力のENGINE=句を探してください。ENGINE=InnoDBはInnoDBテーブルを示し、ENGINE=MyISAMはMyISAMテーブルを示します。InnoDBがデフォルトで一般的に堅牢であり、MyISAMは古く、フォールトトレランスが低いです。

テーブルにアクセスできずSHOW CREATE TABLEが失敗する場合は、バックアップ、デプロイメント移行ファイル、または同じスキーマを持つ別の環境からメタデータを確認してください。推測は危険です。MyISAM用のコマンドがInnoDBに対して無用または危険である可能性があるためです。

実践的なトリアージチェックリスト

何かを修復する前に、わかっていることを書き留めてください：

  1. 影響を受けているテーブルまたはデータベースはどれですか？
  2. MySQLは実行中ですか、クラッシュループしていますか、それとも起動を拒否していますか？
  3. 影響を受けているテーブルはInnoDBですか、それともMyISAMですか？
  4. 最後に正常なバックアップが取られたのはいつですか？
  5. レプリカは正常ですか、同じ破損を示していますか？
  6. アプリケーションを読み取り専用モードにできますか？

このチェックリストが重要なのは、最善の答えが「正常なレプリカを昇格させる」または「昨夜のバックアップを復元する」であり、「本番環境で修復コマンドを実行する」ではない可能性があるからです。レプリケーションがある場合は、すべてを再起動する前にレプリカを確認してください。遅延レプリカは、古いバックアップを復元する手間を省くことがありますが、損傷イベントを再生する前に停止した場合に限ります。

破損したテーブルの復旧：ステップバイステップのアプローチ

InnoDBテーブルの場合

InnoDBテーブルはトランザクションセーフで、クラッシュセーフになるように設計されています。ほとんどの場合、MySQLの組み込みクラッシュリカバリメカニズムは、再起動時に自動的に不整合を処理します。ただし、深刻な破損の場合は手動介入が必要になることがあります。

1. InnoDBの自動クラッシュリカバリ

サーバーがクラッシュした場合、MySQLを再起動するだけで問題が解決することがよくあります。InnoDBは自動的に不完全なトランザクションをロールバックし、データファイルを一貫した状態に戻そうとします。

2. innodb_force_recoveryの使用（細心の注意を払って使用！）

自動リカバリが失敗し、サーバーが起動しないかテーブルにアクセスできないままの場合、innodb_force_recoveryを使用できます。このオプションは、破損を検出した場合でもInnoDBを強制的に起動し、データをダンプできるようにします。これは通常の操作には決して使用せず、データを抽出するための最後の手段としてのみ使用する必要があります。レベルが高いと通常のリカバリ作業をスキップし、不整合なデータを公開する可能性があります。

my.cnf（またはmy.ini）ファイルを編集し、[mysqld]セクションの下にinnodb_force_recovery設定を追加または変更します。レベル1から開始し、必要に応じて段階的に増やしてください。復旧の試行後はこの設定を削除することを忘れないでください。 レベルは（最も穏やかなものから最も積極的なものへ）：

  • 1 (SRV_FORCE_IGNORE_CORRUPT)：破損したページを無視します。テーブルからのSELECTを許可します。
  • 2 (SRV_FORCE_NO_BACKGROUND)：マスタースレッドの実行を防ぎ、バックグラウンド操作を停止します。
  • 3 (SRV_FORCE_NO_TRX_UNDO)：トランザクションのロールバックを実行しません。
  • 4 (SRV_FORCE_NO_IBUF_MERGE)：挿入バッファのマージを防ぎます。
  • 5 (SRV_FORCE_NO_UNDO_LOG_SCAN)：アンドゥログを参照しません。SELECTステートメントが失敗する可能性があります。
  • 6 (SRV_FORCE_NO_LOG_REDO)：リドゥログのロールフォワードを実行しません。データ損失のリスクが最も高い。

innodb_force_recoveryを使用した復旧プロセス：

  1. 再度バックアップ： 進む前に可能な限り最新のバックアップを確保してください。
  2. MySQLを停止： sudo systemctl stop mysql（または同等のコマンド）。
  3. my.cnfを編集： innodb_force_recovery = 1を追加。
  4. MySQLを起動： sudo systemctl start mysql。
  5. データのダンプを試行： サーバーが起動したら、すぐに影響を受けたデータベース/テーブルをmysqldumpします。1つのテーブルが失敗した場合は、正常なテーブルを個別にダンプして、1つの不良オブジェクトが全体の救出を妨げないようにします。
    mysqldump -u root -p your_database > /path/to/your_database_dump_forced.sql
  6. MySQLを停止： sudo systemctl stop mysql。
  7. my.cnfからinnodb_force_recoveryを削除： これは重要です。
  8. MySQLを起動： sudo systemctl start mysql。
  9. 破損したデータベース/テーブルを削除： ダンプが成功した場合、問題のあるデータベース/テーブルを削除します。
    DROP DATABASE your_database;
  10. 再作成してインポート： データベースを再作成し、ダンプファイルからデータをインポートします。
    mysql -u root -p -e "CREATE DATABASE your_database;"
mysql -u root -p your_database < /path/to/your_database_dump_forced.sql

3. バックアップからの復元

最近の正常なバックアップがある場合、これは深刻なInnoDB破損に対する最も迅速で信頼性の高い復旧方法であることがよくあります。破損したデータベース/テーブルを削除し、バックアップから復元してください。

可能な場合は、まず別のインスタンスに復元してください。これにより、バックアップが使用可能であることを確認し、アプリケーションのスモークテストを実行し、本番データを置き換える前に行数を比較できます。存在するが復元されたことのないバックアップは、依然として仮定に過ぎません。

MyISAMテーブルの場合

MyISAMテーブルはよりシンプルですがトランザクション対応ではないため、不適切なシャットダウンによる破損の影響を受けやすくなっています。復旧は通常、修復ユーティリティの使用を含みます。

1. REPAIR TABLEステートメント

REPAIR TABLEステートメントは、破損したMyISAMテーブルの修正を試みます。テーブルファイルをバックアップした後にのみ使用してください。修復は、損傷と修復モードに応じて、インデックスを再構築したり、損傷した行を破棄したりする場合があります。

-- 標準修復
REPAIR TABLE your_database.your_table;

-- クイック修復（より徹底的でない、高速）
REPAIR TABLE your_table QUICK;

-- 拡張修復（より徹底的、低速、インデックスを再構築する可能性あり）
REPAIR TABLE your_table EXTENDED;

2. mysqlcheckユーティリティ（修復オプション付き）

前述のように、mysqlcheckは修復も実行できます。これは複数のテーブルやデータベースをバッチ修復するのに便利です。

# 特定のデータベース内のすべてのテーブルを修復
mysqlcheck -u root -p --databases your_database --repair

# すべてのデータベース内のすべてのテーブルを修復
mysqlcheck -u root -p --all-databases --repair

3. myisamchkユーティリティ（コマンドライン）

myisamchkは、MyISAMテーブルを直接チェックおよび修復するための低レベルのコマンドラインユーティリティです。物理的な.MYI（インデックス）および.MYD（データ）ファイルに対して動作します。重要：myisamchkを使用する際は、さらなる破損やファイルの競合を防ぐためにMySQLサーバーを停止する必要があります。

myisamchkを使用した復旧プロセス：

  1. バックアップ！ your_table.frm、your_table.MYI、your_table.MYDファイルを安全な場所にコピーします。
  2. MySQLを停止： sudo systemctl stop mysql（またはsudo service mysql stop）。
  3. データディレクトリに移動： データベースファイルが保存されているディレクトリに移動します（例：/var/lib/mysql/your_database_name）。
    cd /var/lib/mysql/your_database_name
  4. テーブルをチェック：
    myisamchk your_table.MYI
    これにより、テーブルの健全性に関する情報が出力されます。
  5. テーブルを修復：
    • 安全な修復： myisamchk -r your_table.MYI（破損した行をロールバック、より安全）
    • 積極的な修復： myisamchk -o your_table.MYIまたはmyisamchk -f your_table.MYI（インデックスの再構築を試みる、データが失われる可能性あり；-rが失敗した場合に使用）
    • 非常に積極的な修復： myisamchk -r -f your_table.MYI（再構築と強制を組み合わせる）
  6. MySQLを再起動： sudo systemctl start mysql（またはsudo service mysql start）。

MyISAM修復後は、アプリケーションレベルのチェックを実行してください。テーブルは構造的に修復されていても、ビジネスにとって重要な行が欠落している可能性があります。たとえば、注文テーブルはCHECK TABLEに合格しても、支払い記録、ログ、またはバックアップとの照合が必要なギャップが存在する場合があります。

将来の破損を防ぐ

復旧方法を知ることは不可欠ですが、そもそも破損を防ぐことが常に最善の戦略です。以下のベストプラクティスを実装してください：

  • 定期的で検証済みのバックアップ：堅牢なバックアップ戦略（論理バックアップと物理バックアップの両方）を実装し、バックアップが復元可能であることを定期的にテストします。
  • グレースフルシャットダウン：systemctl stop mysql、mysqladmin shutdown、またはサービス管理を使用して、常にMySQLをグレースフルにシャットダウンします。kill -9は避けてください。
  • 堅牢なハードウェア：ECC RAM（エラー訂正コードメモリ）やディスク冗長性のためのRAID構成など、信頼性の高いハードウェアに投資します。停電から保護するためにUPS（無停電電源装置）を使用します。
  • システムリソースの監視：ディスク容量、I/Oパフォーマンス、CPU使用率、メモリに注意を払います。リソースの枯渇は予期しない問題を引き起こす可能性があります。
  • InnoDBの使用（デフォルトで推奨）：InnoDBはトランザクションセーフであり、MyISAMと比較して優れたクラッシュリカバリ機能を提供します。新しいテーブルにはデフォルトでInnoDBを選択する必要があります。
  • MySQLを最新に保つ：MySQLのバージョンを最新の状態に保ち、セキュリティパッチとバグ修正を迅速に適用します。新しいバージョンには、安定性とデータ整合性の改善が含まれていることがよくあります。
  • エラーログを定期的に確認：MySQLエラーログを確認する習慣をつけて、本格的な破損に発展する前に警告サインをキャッチします。
  • ファイルシステムとOSのベストプラクティス：堅牢なファイルシステム（例：ext4、XFS）を使用し、オペレーティングシステムが適切にメンテナンスされていることを確認します。

「復旧」が意味すべきこと

「サーバーが起動した」で止まらないでください。復旧されたデータベースは、いくつかの実用的なチェックを通過する必要があります：

  • CHECK TABLEまたはエンジンに適した検証でクリーンな結果が返される。
  • アプリケーションの読み取りおよび書き込みスモークテストが合格する。
  • 重要なテーブルの行数が期待値または既知のバックアップ数と一致する。
  • エラーログにストレージエンジンのエラーが繰り返し表示されなくなる。
  • バックアップが再開され、少なくとも1回の新しい復元テストが成功している。

MySQLテーブルの破損は深刻ですが、証拠を保存し、書き込みを停止し、エンジンを特定し、フォールバックが確保されるまで積極的な修復コマンドを避けることで、復旧パスは管理可能です。多くのインシデントでは、最も安全な修正は検証済みの復元です。innodb_force_recovery、REPAIR TABLE、myisamchkなどの救出ツールが必要な場合は、抽出と制御された修復のために使用し、日常的なメンテナンスとして使用しないでください。