MySQLでテーブルが壊れた時に行った対処方法

色々多忙ということもあって久々の投稿です。

本サイトの各種の記事などコンテンツの管理は「Concrete5」というCMSで行っているのですが、この「Concrete5」で使用するMySQLが、1年ほど前から一部のテーブルにアクセスすると以下のようなエラーが出て、バックアップのためのテーブルアクセスもできない状況が長く続いていました。

mysqdump: Couldn't execute 'SELECT *(中略)':  Lost connection to MySQL server during auery (2013)

どうやらmysqldumpによるバックアップの途中で上記のSQL文に出てくるテーブルで「 Lost connection to MySQL server during auery (2013)」となっているようでした。

バックアップが取れないため、Concrete5のバージョンアップができずしばらく放置していましたが、やはり何とか治そうと最近再びmysqldumpでバックアップを取ろうとすると、今度は以下のエラーも出るようになってしまっていました。

mysqldump: Error: 'Access denied; you need (at least one of) the PROCESS privilege(s) for this operation' when trying to dump tablespaces

上記のエラーは権限関連のものらしく、調べてみると、どうやら日々のyum updateによってmysqlのバージョンアップされ、それに伴って「PROCESS」権限が必要になったようでした。

参考:MysqldumpでPROCESS権限(PROCESS Privilege)を要求される(https://isgs-lab.com/424/)

ということで、上記のサイトの情報を元に、以下のように権限の編集を行ったところ、このエラーはでなくなりました。
なお、下記のユーザー名やデータベース名等は読み替えてください。
また、注意事項としては、ユーザー名について、「'ユーザー名'@'localhost'」のように@前後それぞれを「"ユーザー名"@"localhost"」のようにダブルクォーテーションでくくらないとうまく行きませんでした。(これで少々ハマりました)

・現状の権限の参照

mysql> SHOW GRANTS FOR "ユーザー名"@"localhost";
+---------------------------------------------------------------------------------------------------------------------+
| Grants for ユーザー名@localhost                                                                                     |
+---------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'ユーザー名'@'localhost' IDENTIFIED BY PASSWORD '***************' |
| GRANT ALL PRIVILEGES ON `データベース名`.* TO 'ユーザー名'@'localhost'                                                 |
+---------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.02 sec)

「PROCESS」という権限は無いので、以下のように追加しました。

・権限の追加

mysql> GRANT PROCESS ON *.* TO "ユーザー名"@"localhost";

再び権限を参照してみると、

mysql> SHOW GRANTS FOR "ユーザー名"@"localhost";
+---------------------------------------------------------------------------------------------------------------------+
| Grants for ユーザー名@localhost                                                                                     |
+---------------------------------------------------------------------------------------------------------------------+
| GRANT PROCESS ON *.* TO 'ユーザー名'@'localhost' IDENTIFIED BY PASSWORD '***************' |
| GRANT ALL PRIVILEGES ON `データベース名`.* TO 'ユーザー名'@'localhost'                                                 |
+---------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.02 sec)

GRANT PROCESS ON *.* TO ...となり、この後のmysqldumpでは、権限のエラーはでなくなりました。

しかし、もう一つの「Lost connection to MySQL server during auery (2013)」がどうしてもわかりませんでした。

とにかく特定のテーブルにアクセスする、selectだろうがdeleteだろうがdropだろうが、何をやっても同じエラーになる状態だったので、「もしやテーブルの破損か？」と疑うことにしました。

過去に何度か停電で自宅サーバーが不意に停止したことがあったので、それがテーブルの破損の原因かと思われます。そんなわけで、これを機に無停電電源装置(UPS)も購入することにしました。

ということで、テーブルが破損して何もできないならできるようにすればよいことから、
「/etc/my.cnf」に以下の記述を行い、MySQLの再起動を行いました。

[mysqld]
・
・
・
innodb_force_recovery = 1

この「innodb_force_recovery」は0より大きい数値を指定すると、その数値の大きさに従って強制的にリカバリを行えるらしいです。1でだめなら2、2でダメなら3、という具合に徐々に数値を上げてリカバリしてみると良いらしいです。もちろん、リカバリに必要なバックアップを採取出来たら、再び元に戻してMySQLを再起動する必要があります。ただし、あまり数値を大きくすると、それはそれで問題があるようなので、1つずつ上げていくようにすべきだそうです。

筆者の場合、「innodb_force_recovery = 1」でエラーとなっていたmysqldumpが通り、また、この設定を戻してMySQLを再起動後、mysqldumpで採取したバックアップを戻すと、無事にバックアップの復元ができ、リカバリが完了しました。

この後、「Concrete5」のバージョンアップもうまく行くことも確認できました。

参考：[手順] MySQL データベースでの InnoDB 破損を修復するには

今後、MySQLでデータベースの一部が破損した場合は、この方法を再び試してみようと思います。