クラッシュしたmysql5.1のinnodbデータを復旧するまで

ひさびさにおもしろかった備忘録

#### ★状況

原因としてRaid1のディスクの片方が逝ってた影響を受けたデータ欠損と思われる（オンプレ）
mysqldが起動しなくなった
バックアップはdumpの途中でアーカイブしてしまうというダメな仕様だったため使用することが不可能だった

・my.cnfの設定情報（抜粋）

```
## for InnoDB param.
innodb_buffer_pool_size = 2900M
innodb_additional_mem_pool_size=16M
innodb_file_per_table
innodb_log_buffer_size = 32M
innodb_log_file_size = 128M
innodb_lock_wait_timeout=50
innodb_open_files = 512
innodb_flush_method = O_DIRECT
#innodb_flush_log_at_trx_commit=2
```

※innodb_data_file_pathの指定はないのでデフォルト値と思われる

・バージョン
5.1.53-log
テーブルはすべてInnoDB

・エラーログ（Error文字列抜粋、/var/libのしたのやつ）

```
161201 14:41:03  InnoDB: Error: Write to file ./ib_logfile1 failed at offset 0 38049792.
InnoDB: Error: tried to read 65536 bytes at offset 0 38038528.
InnoDB: Fatal error: cannot read from file. OS error number 17.
161201 14:41:27 - 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.
```

エラーメッセージからたどったリンク
http://dev.mysql.com/doc/refman/5.6/ja/innodb-error-handling.html
http://dev.mysql.com/doc/refman/5.6/ja/crashing.html
http://dev.mysql.com/doc/refman/5.6/ja/forcing-innodb-recovery.html

ググってでてきたリンク
エラーメッセージで検索した結果
http://dba.stackexchange.com/questions/68319/mysql-index-creation-failing-on-table-is-full

innodb_data_file_pathを変える場合
http://nippondanji.blogspot.jp/2009/01/innodb_16.html
https://open-groove.net/mysql/change-innodb-datafile-size/

ykstの共有データが肥大かする問題の解決方法（dumpして戻すしかないとか）
https://yakst.com/ja/posts/68

もし代替え機を用意することができないかつデータのアーカイブが終わってるのであれば以下の方法でディレクトリをまっさらにして起動、
そのあとにロールバックしてよいならばdumpデータを入れるのもよさそうであると思いました。
http://dev.mysql.com/doc/refman/5.6/ja/innodb-data-log-reconfiguration.html

#### ★./ib_logfile*を別ディレクトリに退避→起動しない

`./ib_logfile*`を別ディレクトリに退避、を実施、
ディスクの保守交換を実施、していただいたあとにmysqldを起動してみるも起動せず。

#### ★innodb_force_recoveryを調整して起動

`innodb_force_recovery = 3`にしたら起動した
しかしいくつかselectできないテーブルがあるのでrecoveryレベルを上げてみる

参考：
http://blog.memolib.com/memo/118/

#### ★innodb_force_recoveryを4にしてselectしてみる

4つselectできなかったのでrecoveryレベルを上げてみる



#### ★innodb_force_recoveryを5にしてバックアップを取る

1つをのぞいて見られるように。
`INTO OUTFILE`でcsvにとっとく。3つ成功、1つはテーブルが存在しないエラーが出た

```
mysql> SELECT * from hoge.items INTO OUTFILE "/tmp/hoge.items.csv" FIELDS TERMINATED BY ',';
Query OK, 22773 rows affected (0.27 sec)

mysql> SELECT * from hoge.ids INTO OUTFILE "/tmp/hoge.ids.csv" FIELDS TERMINATED BY ',';
Query OK, 42 rows affected (0.02 sec)

mysql> SELECT * from hoge.hosts INTO OUTFILE "/tmp/hoge.hosts.csv" FIELDS TERMINATED BY ',';
Query OK, 555 rows affected (0.02 sec)

mysql> SELECT * from hoge.acknowledges INTO OUTFILE "/tmp/hoge.acknowledges.csv" FIELDS TERMINATED BY ',';
ERROR 1146 (42S02): Table 'hoge.acknowledges' doesn't exist
```

データがselectできないので`innodb_force_recovery`を5まで上げたら一つのテーブルを除きdumpすることができた

ユーザ情報をとっとく

```
mysql> show grants for root@localhost;
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                                                              |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*****' WITH GRANT OPTION |
+----------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.04 sec)

mysql> show grants for hoge@'aa-hoge03';
+---------------------------------------------------------------------------------------------------------------------+
| Grants for hoge@aa-hoge03                                                                                   |
+---------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'hoge'@'aa-hoge03' IDENTIFIED BY PASSWORD '*****' |
| GRANT ALL PRIVILEGES ON `hoge`.* TO 'hoge'@'aa-hoge03'                                                    |
+---------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> show grants for hoge@localhost;
+---------------------------------------------------------------------------------------------------------------+
| Grants for hoge@localhost                                                                                   |
+---------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'hoge'@'localhost' IDENTIFIED BY PASSWORD '*****' |
| GRANT ALL PRIVILEGES ON `hoge`.* TO 'hoge'@'localhost'                                                    |
+---------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

～略～
```


・リストアが高速になるオプション

`--order-by-primary`をつかう。
主キーでソートするとインサートが早い、されてないと遅いらしいので。
http://d.hatena.ne.jp/sh2/20120122

```
# mysqldump -uroot -p --opt --flush-logs --single-transaction --database hoge --ignore-table=hoge.acknowledges --order-by-primary > /tmp/hoge-database.sql
```

#### ★途中で止まったのでtimeoutを伸ばした

```
mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table `history_uint` at row: 1867552
```
あとでよく見たら/var/spool/mail/rootに上記エラーがこの事件以前は出つづけていた。

http://banginu.blogspot.jp/2015/04/mysqldump-error-2013-lost-connection-to.html

```
mysql> show global variables like '%timeout%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| connect_timeout            | 10    |
| delayed_insert_timeout     | 300   |
| innodb_lock_wait_timeout   | 50    |
| innodb_rollback_on_timeout | OFF   |
| interactive_timeout        | 28800 |
| net_read_timeout           | 30    |
| net_write_timeout          | 60    |
| slave_net_timeout          | 3600  |
| table_lock_wait_timeout    | 50    |
| wait_timeout               | 28800 |
+----------------------------+-------+
10 rows in set (0.00 sec)

mysql> set global net_read_timeout=36000;
Query OK, 0 rows affected (0.00 sec)

mysql> set global net_write_timeout=36000;
Query OK, 0 rows affected (0.00 sec)

mysql> set global wait_timeout=36000;
Query OK, 0 rows affected (0.00 sec)

mysql> set global innodb_lock_wait_timeout=36000;
Query OK, 0 rows affected (0.00 sec)
```

ほかにmax_allowed_packetを16Mから32Mにした。たぶんtimeoutよりはこちらの方が効いてたと思われる。
ヒストリ系のテーブルサイズがだいぶあったので。
https://ottomatik.groovehq.com/knowledge_base/topics/solving-error-2013-lost-connection-to-mysql-server-during-query-when-dumping-table


#### ★データディレクトリをmvして起動スクリプトで初期化

起動スクリプトを実行すると、データディレクトリが空だったりしたら勝手に初期化処理が走ってくれる。（remiリポジトリのmysql）ただしディレクトリオーナーはmysqlユーザにしないといけない。

```
# mv /var/lib/mysql{,.20161202bk}

# ls -dl /var/lib/mysql*
drwxr-xr-x 2 root  root  4096 12月  2 12:01 /var/lib/mysql
drwxr-xr-x 5 mysql mysql 4096 12月  2 11:59 /var/lib/mysql.20161202bk
drwxr-xr-x 5 mysql mysql 4096 12月  2 09:38 /var/lib/mysql_crash20161202
drwxr-xr-x 5 mysql mysql 4096 12月  2 09:03 /var/lib/mysql_new
drwxr-xr-x 5 mysql mysql 4096 12月  2 03:44 /var/lib/mysql_zab02_20161202
# chown mysql. /var/lib/mysql
# ls -dl /var/lib/mysql*
drwxr-xr-x 2 mysql mysql 4096 12月  2 12:01 /var/lib/mysql
drwxr-xr-x 5 mysql mysql 4096 12月  2 11:59 /var/lib/mysql.20161202bk
drwxr-xr-x 5 mysql mysql 4096 12月  2 09:38 /var/lib/mysql_crash20161202
drwxr-xr-x 5 mysql mysql 4096 12月  2 09:03 /var/lib/mysql_new
drwxr-xr-x 5 mysql mysql 4096 12月  2 03:44 /var/lib/mysql_zab02_20161202

# service mysqld start
MySQL データベースを初期化中:  Installing MySQL system tables...
OK
Filling help tables...
OK

To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:

/usr/bin/mysqladmin -u root password 'new-password'
/usr/bin/mysqladmin -u root -h hoge-db01 password 'new-password'

Alternatively you can run:
/usr/bin/mysql_secure_installation

which will also give you the option of removing the test
databases and anonymous user created by default.  This is
strongly recommended for production servers.

See the manual for more instructions.

You can start the MySQL daemon with:
cd /usr ; /usr/bin/mysqld_safe &

You can test the MySQL daemon with mysql-test-run.pl
cd /usr/mysql-test ; perl mysql-test-run.pl

Please report any problems with the /usr/bin/mysqlbug script!

                                                           [  OK  ]
mysqld を起動中:                                           [  OK  ]

# mysql -u root -p
```

※show grantsしたユーザのGrantを発行しておく。


#### ★過去データから壊れたテーブル分のデータをとりだす

過去データは途中までだったものの不幸中の幸いで壊れたテーブルはdumpデータの最初の方の行に存在していた。
dumpデータをviで開き該当テーブルの行数確認してからheadとtailでファイルに吐く原始的な感じで取り出しました。

```
# head -56 hoge.sql| tail -30 > hoge.acknowledges.sql
# scp -P 1220 ./hoge.acknowledges.sql hoge-op@172.27.27.28:/tmp/
```

ほかの方法は以下のあたりに。

特定のDBスキーマを取り出す方法↓
http://nippondanji.blogspot.com/2009/12/mysqldumpawk.html

dumpデータからテーブルをcsplitで分ける方法↓
http://qiita.com/toshiro3/items/7844d45076b83103c095

#### ★バイナリログを無効化してデータ投入

ザックリいうとデータファイルに書くのとログファイルに書くのを片方やらずにディスクI/Oが減ったぶん早い、はず。（dumpデータは無圧縮状態で4.8GBくらいでデータ投入にかかったのは3時間ちょっとでした）

```
mysql> SET sql_log_bin=OFF;
mysql> CREATE DATABASE `hoge` /*!40100 DEFAULT CHARACTER SET utf8 */;
mysql> use hoge;
mysql> SOURCE /tmp/hoge-database.sql;
mysql> SOURCE /tmp/hoge.acknowledges.sql;
mysql> LOAD DATA LOCAL INFILE "/tmp/hoge.hosts.csv" INTO TABLE hoge.hosts FIELDS TERMINATED BY ',';
mysql> LOAD DATA LOCAL INFILE "/tmp/hoge.ids.csv" INTO TABLE hoge.ids FIELDS TERMINATED BY ',';
mysql> LOAD DATA LOCAL INFILE "/tmp/hoge.items.csv" INTO TABLE hoge.items FIELDS TERMINATED BY ',';
```

快適データローディング的な参考サイト：
http://nippondanji.blogspot.jp/2010/03/innodb.html
http://d.hatena.ne.jp/sh2/20120122

#### ★バックアップの仕組みを改修

以前は圧縮前にNFS上に直に書きに行ってI/Oバウンドすぎてたかつgzipが別でバックアップ完了する前に仕掛けられていたため修正。エラー通知も入れてみた。

```
# crontab -l
--------
# Database backup
0 12 * * * mysqldump -uroot -p`cat /path_to_file` --opt --flush-logs --single-transaction --master-data=2 --order-by-primary --all-databases |gzip > /tmp/hoge-database.sql.gz 2> /var/log/mysqldump.log && touch /tmp/dbbkcomp-$(/bin/date +\%Y\%m\%d).lock
# Copy backup to remote
0 23 * * * /usr/bin/test -f /tmp/dbbkcomp-$(/bin/date +\%Y\%m\%d).lock && mv -f /mnt/hoge-database.sql.gz{,.1} && /bin/cp -pf /tmp/hoge-database.sql.gz /mnt/hoge-database.sql.gz && /bin/rm -f /tmp/dbbkcomp-$(/bin/date +\%Y\%m\%d).lock
# mail alert if cannot take a database backup.
0 0 * * * /usr/bin/test -s /var/log/mysqldump.log && echo 'detected an alert of database backup.(/var/log/mysqldump.log)' | /bin/mailx -s 'backup failer' hoge-op@hoge.net && mv -f /var/log/mysqldump.log.1
```

#### ★個人的なバックアップ原則

・レプリケーションはバックアップではない（間違って全削除とかのオペミスも容赦なく同期される）
・レストア試験した上で復旧手順まで書いていないのはバックアップではない（誰も彼もが専門知識があるわけではないので緊急時に時間を食ったり見当違いなことして二次障害とか起きるとつらい）
・エラーハンドリングしてないバックアップスクリプトで取られたデータはつかえない（量が増えることを想定してなかった処理があるとまずい）
・ローカルバックアップはバックアップではない（ローカルが落ちて壊れてたらジ・エンド）
・冗長構成組んでおくと何か起きても営業日対応ですむ（自動フェイルオーバを仕込んであるかつキャパシティが足りてる場合のみ）

まあローカルはバックアップではないとはいえmysqldumpコマンドから直にNW越しにする場合はI/Oとtimeout的なパラメータ等手間なので処理を分けたり最低限圧縮必須なのかなと思いました。

データ消失系はインフラ的怖い話の中でも特に寒い感じで事業継続性にディープインパクトという印象なので戻せてよかったです。

ということで以上。
高速データローディング試せてまんぞくしました。