749
技術社區[雲棲]
innodb 引擎數據恢複
今天遇到一個問題 mysql-5.5
數據庫保障
部分錯誤日誌如下
InnoDB: stored checksum 808812544, prior-to-4.0.14-form stored checksum 959328563 InnoDB: Page lsn 791621944 858666297, low 4 bytes of lsn at page end 841888053 InnoDB: Page number (if stored to page already) 307162691, InnoDB: space id (if created with >= MySQL-4.1.1 and stored already) 841958736 InnoDB: Database page corruption on disk or a failed InnoDB: file read of page 44864. InnoDB: You may have to recover from a backup. InnoDB: It is also possible that your operating InnoDB: system has corrupted its own file cache InnoDB: and rebooting your computer removes the InnoDB: error. 130826 9:59:12 [ERROR] Invalid (old?) table or database name 'zs20121215-21'
檢測數據庫時候發生下麵錯誤
mysql> check table activity.enterGiveApp; ERROR 2013 (HY000): Lost connection to MySQL server during query
表結構存在
mysql> desc activity.goldidea; +-------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+--------------+------+-----+---------+----------------+ | id | int(10) | NO | PRI | NULL | auto_increment | | userId | varchar(50) | YES | | NULL | | | localIp | varchar(100) | YES | | NULL | | | create_time | datetime | YES | | NULL | | +-------------+--------------+------+-----+---------+----------------+ 4 rows in set (0.06 sec)
查詢數據發生下麵錯誤
mysql> select ID from activity.goldidea where ID < 50; ERROR 2013 (HY000): Lost connection to MySQL server during query
非 MyISAM 表，不支持 repair 修複
mysql> repair table activity.goldidea; +-----------------------+--------+----------+---------------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +-----------------------+--------+----------+---------------------------------------------------------+ | activity.funitv_click | repair | note | The storage engine for the table doesn't support repair | +-----------------------+--------+----------+---------------------------------------------------------+ 1 row in set (0.01 sec)
查詢存儲
mysql> select engine from information_schema.tables where table_schema='activity' and table_name=goldidea'; +--------+ | engine | +--------+ | InnoDB | +--------+ 1 row in set (0.00 sec)
嚐試修複
my.cnf 啟動參數添加
innodb_force_recovery = 6
重啟 mysql
檢測表
mysql> check table activity.goldidea; ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql> desc activity.goldidea; +-------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+--------------+------+-----+---------+----------------+ | id | int(10) | NO | PRI | NULL | auto_increment | | userId | varchar(50) | YES | | NULL | | | localIp | varchar(100) | YES | | NULL | | | create_time | datetime | YES | | NULL | | +-------------+--------------+------+-----+---------+----------------+ 4 rows in set (0.06 sec)
mysql> select count(*) from activity.goldidea; +----------+ | count(*) | +----------+ | 60827 | +----------+ 1 row in set (0.13 sec) mysql> check table activity.goldidea; +-------------------+-------+----------+----------+ | Table | Op | Msg_type | Msg_text | +-------------------+-------+----------+----------+ | activity.goldidea | check | status | OK | +-------------------+-------+----------+----------+ 1 row in set (0.22 sec)
走運地修複好表及數據了
最後更新：2017-04-03 16:49:00