hiranoです。

あるサービスで使用しているMySQLが突然起動しなくなった時の対処方法を記す。

背景

何もしてないのに突然MySQLが起動しなくなった！

調査

MySQLのログを見ると以下のように表示されている。

170412 10:52:01 mysqld_safe Starting mysqld daemon with databases from /path/to/mysql
2017-04-12 10:52:01 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2017-04-12 10:52:01 0 [Note] /usr/sbin/mysqld (mysqld 5.6.27) starting as process 2716 ...

中略

2017-04-12 10:52:01 2716 [Note] InnoDB: Log scan progressed past the checkpoint lsn 86908734689
2017-04-12 10:52:01 2716 [Note] InnoDB: Database was not shutdown normally!
2017-04-12 10:52:01 2716 [Note] InnoDB: Starting crash recovery.
2017-04-12 10:52:01 2716 [Note] InnoDB: Reading tablespace information from the .ibd files...
2017-04-12 10:52:01 2716 [ERROR] InnoDB: space header page consists of zero bytes in tablespace ./dbname/table_name.ibd (table dbname/table_name)
2017-04-12 10:52:01 2716 [Note] InnoDB: Page size:1024 Pages to analyze:64
2017-04-12 10:52:01 2716 [Note] InnoDB: Page size: 1024, Possible space_id count:0
2017-04-12 10:52:01 2716 [Note] InnoDB: Page size:2048 Pages to analyze:32
2017-04-12 10:52:01 2716 [Note] InnoDB: Page size: 2048, Possible space_id count:0
2017-04-12 10:52:01 2716 [Note] InnoDB: Page size:4096 Pages to analyze:16
2017-04-12 10:52:01 2716 [Note] InnoDB: Page size: 4096, Possible space_id count:0
2017-04-12 10:52:01 2716 [Note] InnoDB: Page size:8192 Pages to analyze:8
2017-04-12 10:52:01 2716 [Note] InnoDB: Page size: 8192, Possible space_id count:0
2017-04-12 10:52:01 2716 [Note] InnoDB: Page size:16384 Pages to analyze:4
2017-04-12 10:52:01 2716 [Note] InnoDB: Page size: 16384, Possible space_id count:0
2017-04-12 10:52:01 7fb422a60720  InnoDB: Operating system error number 2 in a file operation.
InnoDB: The error means the system cannot find the path specified.
InnoDB: If you are installing InnoDB, remember that you must create
InnoDB: directories yourself, InnoDB does not create them.
InnoDB: Error: could not open single-table tablespace file ./dbname/table_name.ibd
InnoDB: We do not continue the crash recovery, because the table may become
InnoDB: corrupt if we cannot apply the log records in the InnoDB log to it.
InnoDB: To fix the problem and start mysqld:
InnoDB: 1) If there is a permission problem in the file and mysqld cannot
InnoDB: open the file, you should modify the permissions.
InnoDB: 2) If the table is not needed, or you can restore it from a backup,
InnoDB: then you can remove the .ibd file, and InnoDB will do a normal
InnoDB: crash recovery and ignore that table.
InnoDB: 3) If the file system or the disk is broken, and you cannot remove
InnoDB: the .ibd file, you can set innodb_force_recovery > 0 in my.cnf
InnoDB: and force InnoDB to continue crash recovery here.

対処

どうやらファイルが壊れているようだ（詳細不明）

ということでログに記載されている手順に従って対処する。

# ls -lh tablename.*
-rw-rw---- 1 mysql mysql 8.5K Nov 13  2015 tablename.frm
-rw-rw---- 1 mysql mysql  64K Mar  4 07:58 tablename.ibd

パーミッションは問題ない。データは本来は消えてしまうと困るが、バックアップがあるので tablename.ibd を削除してMySQLを起動してみる。

2017-04-12 11:59:18 5774 [Note] InnoDB: Starting crash recovery.
2017-04-12 11:59:18 5774 [Note] InnoDB: Reading tablespace information from the .ibd files...
2017-04-12 11:59:18 5774 [Note] InnoDB: Restoring possible half-written data pages
2017-04-12 11:59:18 5774 [Note] InnoDB: from the doublewrite buffer...
InnoDB: Doing recovery: scanned up to log sequence number 86908734741
InnoDB: 1 transaction(s) which must be rolled back or cleaned up
InnoDB: in total 0 row operations to undo
InnoDB: Trx id counter is 484100864
2017-04-12 11:59:19 7fea1dc27720  InnoDB: Rolling back trx with id 484100424, 0 rows to undo
2017-04-12 11:59:19 5774 [Note] InnoDB: Rollback of trx with id 484100424 completed
2017-04-12 11:59:19 5774 [ERROR] InnoDB: Table dbname/tablename in the InnoDB data dictionary has tablespace id 180974, but tablespace with that id or name does not exist. Have you deleted or moved .ibd files? This may also be a table created with CREATE TEMPORARY TABLE whose .ibd and .frm files MySQL automatically removed, but the table still exists in the InnoDB internal data dictionary.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html
InnoDB: for how to resolve the issue.
2017-04-12 11:59:19 5774 [Note] InnoDB: 128 rollback segment(s) are active.
2017-04-12 11:59:19 5774 [Note] InnoDB: Waiting for purge to start
2017-04-12 11:59:19 5774 [ERROR] InnoDB: Failed to find tablespace for table '"dbname"."tablename"' in the cache. Attempting to load the tablespace with space id 180974.
2017-04-12 11:59:19 7fe9f1bb7700  InnoDB: Operating system error number 2 in a file operation.
InnoDB: The error means the system cannot find the path specified.
2017-04-12 11:59:19 5774 [ERROR] InnoDB: Could not find a valid tablespace file for 'dbname/tablename'. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html for how to resolve the issue.
2017-04-12 11:59:19 5774 [Note] InnoDB: 5.6.27 started; log sequence number 86908734741
2017-04-12 11:59:19 5774 [Note] Server hostname (bind-address): '*'; port: 3306
2017-04-12 11:59:19 5774 [Note] IPv6 is available.
2017-04-12 11:59:19 5774 [Note]   - '::' resolves to '::';
2017-04-12 11:59:19 5774 [Note] Server socket created on IP: '::'.
2017-04-12 11:59:19 5774 [Note] Event Scheduler: Loaded 0 events
2017-04-12 11:59:19 5774 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.6.27'  socket: '/path/to/mysql/mysql.sock'  port: 3306  MySQL Community Server (GPL)
2017-04-12 11:59:53 7fe9f4a26700 InnoDB: cannot calculate statistics for table "dbname"."tablename" because the .ibd file is missing. For help, please refer to http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html
2017-04-12 12:00:05 7fe9f49e5700 InnoDB: cannot calculate statistics for table "dbname"."tablename" because the .ibd file is missing. For help, please refer to http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html
2017-04-12 12:00:06 7fe9f4a26700 InnoDB: cannot calculate statistics for table "dbname"."tablename" because the .ibd file is missing. For help, please refer to http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html

ところどころエラーが出ているが、起動自体は成功した。

MySQLに接続して状況を確認する。

show tables like 'tablename';
+------------------------------+
| Tables_in_dbname (tablename) |
+------------------------------+
| tablename                    |
+------------------------------+
1 row in set (0.00 sec)

存在している様子なので、中身をチェック

mysql> select count(*) from tablename ;
ERROR 1146 (42S02): Table 'dbname.tablename' doesn't exist

show tables だと存在するのに存在しないだと！そんなバカな…。

と、心が折れそうになったが色々と試したところ、以下の手順でOKだった。

1. テーブル削除

mysql> drop table tablename;
Query OK, 0 rows affected (0.19 sec)

2. テーブル作成

これは普通に CREATE TABLE するだけ。試してないが、DROPした後ならダンプファイルからインポートすることも可能と思われる。

3. データを復元

バックアップしておいたデータからデータを復元。

これで完了。

余談

心が折れそうになってからなんとかなるまでの間で tablename.ibd だけでなく tablename.frm も消したところ、以下のようになった。

mysql> CREATE TABLE IF NOT EXISTS `dbname`.`tablename` (
省略
    -> ENGINE = InnoDB;
ERROR 1813 (HY000): Tablespace for table '`dbname`.`tablename`' exists. Please DISCARD the tablespace before IMPORT.
mysql> drop table tablename;
ERROR 1051 (42S02): Unknown table 'dbname.tablename'

作ろうとすると既に存在すると言われ、削除しようとすると存在しない（正確には不明）と言われる。正直詰んだと思ったが、以下のサイトにたどり着き何となく frm は消さなくても良いかもと思って色々試したら解決できた。

accountingse.net

＃まとめ

起動しなくなったらログをよく読め
公式手順がやはり王道
バックアップはこまめに（今回もバックアップなかったらデータ死亡だった）

interprism's blog

インタープリズム株式会社の開発者ブログです。

MySQLで一部のテーブルが破損して起動しなくなった時の対処

背景

調査

対処

1. テーブル削除

2. テーブル作成

3. データを復元

余談