在使用innodb_force_recovery参数强制恢复mysql库以后,错误日志里出现大量错误信息:
2015-01-01 17:58:28 26714 [ERROR] InnoDB: Failed to find tablespace for table '"live_koo"."live_consumer_koo"' in the cache. Attempting to load the tablespace with space id 6.
2015-01-01 17:58:28 26714 [ERROR] InnoDB: Failed to find tablespace for table '"live_koo"."live_provider_soooner"' in the cache. Attempting to load the tablespace with space id 7.
2015-01-01 17:58:28 26714 [ERROR] InnoDB: Failed to find tablespace for table '"live_koo"."live_room"' in the cache. Attempting to load the tablespace with space id 8.
2015-01-01 17:58:28 26714 [ERROR] InnoDB: Failed to find tablespace for table '"live_koo"."live_room_failing"' in the cache. Attempting to load the tablespace with space id 9.
2015-01-01 17:58:28 26714 [ERROR] InnoDB: Failed to find tablespace for table '"live_koo"."live_stats_detail"' in the cache. Attempting to load the tablespace with space id 10.
2015-01-01 17:58:28 26714 [ERROR] InnoDB: Failed to find tablespace for table '"live_koo"."live_stats_num"' in the cache. Attempting to load the tablespace with space id 11.
2015-01-01 17:58:28 26714 [ERROR] InnoDB: Failed to find tablespace for table '"live_koo"."live_users"' in the cache. Attempting to load the tablespace with space id 12.
2015-01-01 17:59:32 26714 [ERROR] InnoDB: Failed to find tablespace for table '"mysql"."innodb_index_stats"' in the cache. Attempting to load the tablespace with space id 2.
2015-01-01 17:59:32 26714 [ERROR] InnoDB: Failed to find tablespace for table '"mysql"."innodb_table_stats"' in the cache. Attempting to load the tablespace with space id 1.
使用mysqlcheck修复失败:
[root@test11g scripts]# mysqlcheck -u root -proot --repair -A
live_koo.live_consumer_koo
note : The storage engine for the table doesn't support repair
live_koo.live_provider_soooner
note : The storage engine for the table doesn't support repair
live_koo.live_room
note : The storage engine for the table doesn't support repair
live_koo.live_room_failing
note : The storage engine for the table doesn't support repair
live_koo.live_stats_detail
note : The storage engine for the table doesn't support repair
live_koo.live_stats_num
note : The storage engine for the table doesn't support repair
live_koo.live_users
note : The storage engine for the table doesn't support repair
mysql.columns_priv OK
mysql.db OK
mysql.event OK
mysql.func OK
mysql.general_log OK
mysql.help_category OK
mysql.help_keyword OK
mysql.help_relation OK
mysql.help_topic OK
mysql.innodb_index_stats
note : The storage engine for the table doesn't support repair
mysql.innodb_table_stats
note : The storage engine for the table doesn't support repair
mysql.ndb_binlog_index OK
mysql.plugin OK
mysql.proc OK
mysql.procs_priv OK
mysql.proxies_priv OK
mysql.servers OK
mysql.slave_master_info
note : The storage engine for the table doesn't support repair
mysql.slave_relay_log_info
note : The storage engine for the table doesn't support repair
mysql.slave_worker_info
note : The storage engine for the table doesn't support repair
mysql.slow_log OK
mysql.tables_priv OK
mysql.time_zone OK
mysql.time_zone_leap_second OK
mysql.time_zone_name OK
mysql.time_zone_transition OK
mysql.time_zone_transition_type OK
mysql.user OK
mysqlcheck: Got error: 1142: SELECT, INSERT command denied to user 'root'@'localhost' for table 'accounts' when executing 'REPAIR TABLE ... '
突然想到是否还可以通过percona recover工具的innochecksum来恢复,尝试了一下:
[root@test11g percona-data-recovery-tool]# ./innochecksum -f /tol/mysql/data/ibdata1 page 8 invalid (fails old style checksum)
page 8: old style: calculated = 0xB90131D5; recorded = 0xB0FDF629
fixing old checksum of page 8
page 8 invalid (fails new style checksum)
page 8: new style: calculated = 0x2638C5C8; recorded = 0x266CEF14
fixing new checksum of page 8
page 11 invalid (fails old style checksum)
page 11: old style: calculated = 0x515DE6E; recorded = 0xFA888BCE
fixing old checksum of page 11
page 11 invalid (fails new style checksum)
page 11: new style: calculated = 0x19468103; recorded = 0xF8159BA8
fixing new checksum of page 11
[root@test11g percona-data-recovery-tool]# ./innochecksum -f /tol/mysql/data/ibdata1
[root@test11g percona-data-recovery-tool]#
通过innochecksum 修复后,可以不使用 innodb_force_recovery参数,也能正常启动mysql服务了
[root@test11g scripts]# /etc/init.d/mysqld start
Starting MySQL.. [ OK ]
错误日志也没再出现上面的错误信息。
2015-01-01 17:58:28 26714 [ERROR] InnoDB: Failed to find tablespace for table '"live_koo"."live_consumer_koo"' in the cache. Attempting to load the tablespace with space id 6.
2015-01-01 17:58:28 26714 [ERROR] InnoDB: Failed to find tablespace for table '"live_koo"."live_provider_soooner"' in the cache. Attempting to load the tablespace with space id 7.
2015-01-01 17:58:28 26714 [ERROR] InnoDB: Failed to find tablespace for table '"live_koo"."live_room"' in the cache. Attempting to load the tablespace with space id 8.
2015-01-01 17:58:28 26714 [ERROR] InnoDB: Failed to find tablespace for table '"live_koo"."live_room_failing"' in the cache. Attempting to load the tablespace with space id 9.
2015-01-01 17:58:28 26714 [ERROR] InnoDB: Failed to find tablespace for table '"live_koo"."live_stats_detail"' in the cache. Attempting to load the tablespace with space id 10.
2015-01-01 17:58:28 26714 [ERROR] InnoDB: Failed to find tablespace for table '"live_koo"."live_stats_num"' in the cache. Attempting to load the tablespace with space id 11.
2015-01-01 17:58:28 26714 [ERROR] InnoDB: Failed to find tablespace for table '"live_koo"."live_users"' in the cache. Attempting to load the tablespace with space id 12.
2015-01-01 17:59:32 26714 [ERROR] InnoDB: Failed to find tablespace for table '"mysql"."innodb_index_stats"' in the cache. Attempting to load the tablespace with space id 2.
2015-01-01 17:59:32 26714 [ERROR] InnoDB: Failed to find tablespace for table '"mysql"."innodb_table_stats"' in the cache. Attempting to load the tablespace with space id 1.
使用mysqlcheck修复失败:
[root@test11g scripts]# mysqlcheck -u root -proot --repair -A
live_koo.live_consumer_koo
note : The storage engine for the table doesn't support repair
live_koo.live_provider_soooner
note : The storage engine for the table doesn't support repair
live_koo.live_room
note : The storage engine for the table doesn't support repair
live_koo.live_room_failing
note : The storage engine for the table doesn't support repair
live_koo.live_stats_detail
note : The storage engine for the table doesn't support repair
live_koo.live_stats_num
note : The storage engine for the table doesn't support repair
live_koo.live_users
note : The storage engine for the table doesn't support repair
mysql.columns_priv OK
mysql.db OK
mysql.event OK
mysql.func OK
mysql.general_log OK
mysql.help_category OK
mysql.help_keyword OK
mysql.help_relation OK
mysql.help_topic OK
mysql.innodb_index_stats
note : The storage engine for the table doesn't support repair
mysql.innodb_table_stats
note : The storage engine for the table doesn't support repair
mysql.ndb_binlog_index OK
mysql.plugin OK
mysql.proc OK
mysql.procs_priv OK
mysql.proxies_priv OK
mysql.servers OK
mysql.slave_master_info
note : The storage engine for the table doesn't support repair
mysql.slave_relay_log_info
note : The storage engine for the table doesn't support repair
mysql.slave_worker_info
note : The storage engine for the table doesn't support repair
mysql.slow_log OK
mysql.tables_priv OK
mysql.time_zone OK
mysql.time_zone_leap_second OK
mysql.time_zone_name OK
mysql.time_zone_transition OK
mysql.time_zone_transition_type OK
mysql.user OK
mysqlcheck: Got error: 1142: SELECT, INSERT command denied to user 'root'@'localhost' for table 'accounts' when executing 'REPAIR TABLE ... '
[root@test11g percona-data-recovery-tool]# ./innochecksum -f /tol/mysql/data/ibdata1 page 8 invalid (fails old style checksum)
page 8: old style: calculated = 0xB90131D5; recorded = 0xB0FDF629
fixing old checksum of page 8
page 8 invalid (fails new style checksum)
page 8: new style: calculated = 0x2638C5C8; recorded = 0x266CEF14
fixing new checksum of page 8
page 11 invalid (fails old style checksum)
page 11: old style: calculated = 0x515DE6E; recorded = 0xFA888BCE
fixing old checksum of page 11
page 11 invalid (fails new style checksum)
page 11: new style: calculated = 0x19468103; recorded = 0xF8159BA8
fixing new checksum of page 11
[root@test11g percona-data-recovery-tool]# ./innochecksum -f /tol/mysql/data/ibdata1
[root@test11g percona-data-recovery-tool]#
通过innochecksum 修复后,可以不使用 innodb_force_recovery参数,也能正常启动mysql服务了
[root@test11g scripts]# /etc/init.d/mysqld start
Starting MySQL.. [ OK ]
错误日志也没再出现上面的错误信息。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/23249684/viewspace-1387010/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/23249684/viewspace-1387010/