平成30/ 2018-12-19 21:49
MYSQL/DB壊れた

Mysqlコマンドログイン後ならば

check table xxx;
で、エラー内容を確認し、

repair table xxx;
で、修復をする。

しかし、僕の場合は、check table で、以下のエラーでて、うまくいかなかった。詳細分からないが、多分、DB重いのでタイムアウトかな。
ERROR 2013 (HY000): Lost connection to MySQL server during query

なので、repair table もできない。なので、
myisamchk コマンドを使う。

事前に、
create table xxx;
で、DBテーブルの定義が、
ENGINE=MyISAM
になってること確認しとく。

エラーチェックする場合で、簡易的でいい場合は、
myisamchk /var/lib/mysql/yyy/xxx.MYI

もう少し詳しく、、の場合は、
myisamchk -e /var/lib/mysql/yyy/xxx.MYI

で、修復する。
軽く修正する場合は、
myisamchk -r -q /var/lib/mysql/yyy/xxx.MYI

それでダメなら、
myisamchk -r /var/lib/mysql/yyy/xxx.MYI

それでダメなら
myisamchk -o /var/lib/mysql/yyy/xxx.MYI

オプションの説明。
-r, --recover       Can fix almost anything except unique keys that aren't
                    unique.
-o, --safe-recover  Uses old recovery method; Slower than '-r' but can
                    handle a couple of cases where '-r' reports that it
                    can't fix the data file.
-q, --quick         Faster repair by not modifying the data file.
                    One can give a second '-q' to force myisamchk to
                    modify the original datafile in case of duplicate keys.
                    NOTE: Tables where the data file is currupted can't be
                    fixed with this option.

実際のログだが、意味不明。しらべないと、。
( バックアップとっておこう )
cp -a /var/lib/mysql/yyy/xxx.MYI ./

( 簡易エラーチェック )
# myisamchk /var/lib/mysql/yyy/xxx.MYI
Checking MyISAM file: /var/lib/mysql/yyy/xxx.MYI
Data records:  634354   Deleted blocks:     971
myisamchk: warning: Table is marked as crashed
myisamchk: warning: 62 clients are using or haven't closed the table properly
- check file-size
- check record delete-chain
myisamchk: error: Record at pos: 336550372 is not remove-marked
myisamchk: error: record delete-link-chain corrupted
- check key delete-chain
- check index reference
- check data record references index: 1
- check data record references index: 2
- check data record references index: 3
- check data record references index: 4
- check data record references index: 5
- check record links
myisamchk: error: Found too long record (15) at 334907656
MyISAM-table '/var/lib/mysql/yyy/xxx.MYI' is corrupted
Fix it using switch "-r" or "-o"
#

( も少し詳しく )
# myisamchk -e /var/lib/mysql/yyy/xxx.MYI
Checking MyISAM file: /var/lib/mysql/yyy/xxx.MYI
Data records:  634354   Deleted blocks:     971
myisamchk: warning: Table is marked as crashed
myisamchk: warning: 62 clients are using or haven't closed the table properly
- check file-size
- check record delete-chain
myisamchk: error: Record at pos: 336550372 is not remove-marked
myisamchk: error: record delete-link-chain corrupted
- check key delete-chain
- check index reference
- check data record references index: 1
- check data record references index: 2
- check data record references index: 3
- check data record references index: 4
- check data record references index: 5
- check records and index references
myisamchk: error: Found too long record (15) at 334907656
MyISAM-table '/var/lib/mysql/yyy/xxx.MYI' is corrupted
Fix it using switch "-r" or "-o"
#

( 簡易的に修復 )
# myisamchk -r -q  /var/lib/mysql/yyy/xxx.MYI
- check record delete-chain
myisamchk: error: Record at pos: 336550372 is not remove-marked
myisamchk: error: record delete-link-chain corrupted
myisamchk: error: Quick-recover aborted; Run recovery without switch 'q'
Updating MyISAM file: /var/lib/mysql/yyy/xxx.MYI
MyISAM-table '/var/lib/mysql/yyy/xxx.MYI' is not fixed because of errors
Try fixing it by using the --safe-recover (-o), the --force (-f) option or by not using the --quick (-q) flag
#
修復できなかった。。

( 更に修復 )
# myisamchk -r /var/lib/mysql/yyy/xxx.MYI
- recovering (with sort) MyISAM-table '/var/lib/mysql/yyy/xxx.MYI'
Data records: 634354
- Fixing index 1
Found block with too small length at 334907656; Skipped
Found block with too small length at 334907692; Skipped
Wrong block with wrong total length starting at 334910980
Wrong block with wrong total length starting at 334911108
Wrong block with wrong total length starting at 334913312
.... 省略

Found block with too small length at 336521884; Skipped
Found block with too small length at 336521976; Skipped
Wrong block with wrong total length starting at 336522392
Wrong block with wrong total length starting at 336522532
- Fixing index 2
- Fixing index 3
- Fixing index 4
- Fixing index 5
Data records: 634285
#
改善したが、時間立ったら、再び不安定になった、。
select * from .. を実行すると以下のエラーでるし、。
ERROR 1030 (HY000): Got error 134 from storage engine
mysql>

再びエラーチェックしたが、エラー無くならないし、。

( 最後の手段の修復 )
#  myisamchk -o /var/lib/mysql/yyy/xxx.MYI
- recovering (with keycache) MyISAM-table '/var/lib/mysql/yyy/xxx.MYI'
Data records: 635474
Found link that points at 331772368 (outside data file) at 330549604
Found block with too small length at 330549644; Skipped
Found link that points at 331771664 (outside data file) at 330549812
Found block with too small length at 330549852; Skipped
Found link that points at 331771852 (outside data file) at 330550032
Found block with too small length at 330550072; Skipped
Found link that points at 331771872 (outside data file) at 330550240
Found block with too small length at 330550280; Skipped
Found link that points at 331771976 (outside data file) at 330550448
Found block with too small length at 330550488; Skipped
Found link that points at 331771248 (outside data file) at 330550656
... 省略
Found link that points at 331766932 (outside data file) at 331287088
Found link that points at 331766952 (outside data file) at 331287196
Found link that points at 331766972 (outside data file) at 331287312
Data records: 635399
#
でも、改善ならないので、上記の修復を何度も実行したら、エラーチェックのログが変わった。でも意味不明。

#  myisamchk  /var/lib/mysql/yyy/xxx.MYI
Checking MyISAM file: /var/lib/mysql/yyy/xxx.MYI
Data records:  635394   Deleted blocks:       0
myisamchk: warning: Table is marked as crashed
myisamchk: warning: 4 clients are using or haven't closed the table properly
- check file-size
myisamchk: error: Size of indexfile is: 313947136        Should be: 313986048
myisamchk: error: Size of datafile is: 331787320         Should be: 331811576
- check record delete-chain
- check key delete-chain
- check index reference
- check data record references index: 1
myisamchk: error: Found 635322 keys of 635394
- check record links
myisamchk: error: Record-count is not ok; is 635322       Should be: 635394
myisamchk: warning: Found     635322 parts                Should be: 635782 parts
MyISAM-table '/var/lib/mysql/yyy/xxx.MYI' is corrupted
Fix it using switch "-r" or "-o"
#


結局、満足な修復はできず、以下を検討することにした。
1. いらないDBデータを削除する。
2. サーバのメモリを増やす。
3. Webサーバ、DBサーバを切り分ける。


( Thanks. )
テーブルの修復(myisamchk)
http://www.bnote.net/mysql/19_myisamchk.shtml

myisamchk で修復する
http://weble.org/2011/11/09/mysql-myisamchk

4.5.6.4. myisamchk の修復オプション
http://dev.mysql.com/doc/refman/4.1/ja/myisamchk-repair-options.html

MySQL 破損したテーブルを復旧させる
http://blog.cloudpack.jp/2011/07/server-news-mysql-restoration.html

[MySQL] MySQLのデータベースが壊れたみたいです
http://webcache.googleusercontent.com/search?q=cache:jjC92dvVhpkJ:blog.kaburk.com/os/linux/mysql-broken.html+&cd=2&hl=ja&ct=clnk&gl=jp

TOP>サーバ構築・運用> key_buffer_sizeとは
http://thinkit.co.jp/free/article/0707/2/5/
Table ‘./mysql/proc’ is marked as crashed and should be repaired の対処方法
http://webcache.googleusercontent.com/search?q=cache:7cGnmUrrBKcJ:www.s-quad.com/wordpress/%3Fp%3D946+&cd=3&hl=ja&ct=clnk&gl=jp


接続できない 復旧 修復 DBデータ壊れた