mysql InnoDB crash and recover

[摘要] 因为机器突然断电,mysql innodb crash 了。数据文件损坏导致mysql进程起不来,几经折腾,终于把服务起来了,但是解决过程并不完美,下面还是把处理经过记录一下。

操作系统:CentOS release 6.8
先贴出来mysql.log 日志的报错情况:

information that should help you find out what is causing the crash.
2018-01-05 08:19:44 13811 [Note] Plugin 'FEDERATED' is disabled.
2018-01-05 08:19:44 13811 [Note] InnoDB: Using atomics to ref count buffer pool pages
2018-01-05 08:19:44 13811 [Note] InnoDB: The InnoDB memory heap is disabled
2018-01-05 08:19:44 13811 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2018-01-05 08:19:44 13811 [Note] InnoDB: Memory barrier is not used
2018-01-05 08:19:44 13811 [Note] InnoDB: Compressed tables use zlib 1.2.3
2018-01-05 08:19:44 13811 [Note] InnoDB: Using Linux native AIO
2018-01-05 08:19:44 13811 [Note] InnoDB: Using CPU crc32 instructions
2018-01-05 08:19:44 13811 [Note] InnoDB: Initializing buffer pool, size = 8.0G
2018-01-05 08:19:44 13811 [Note] InnoDB: Completed initialization of buffer pool
2018-01-05 08:19:44 13811 [Note] InnoDB: Highest supported file format is Barracuda.
2018-01-05 08:19:44 13811 [Note] InnoDB: Log scan progressed past the checkpoint lsn 229983134766
2018-01-05 08:19:44 13811 [Note] InnoDB: Database was not shutdown normally!
2018-01-05 08:19:44 13811 [Note] InnoDB: Starting crash recovery.
2018-01-05 08:19:44 13811 [Note] InnoDB: Reading tablespace information from the .ibd files...
2018-01-05 08:19:44 13811 [Note] InnoDB: Restoring possible half-written data pages
2018-01-05 08:19:44 13811 [Note] InnoDB: from the doublewrite buffer...
InnoDB: Doing recovery: scanned up to log sequence number 229983251745
InnoDB: Database page corruption on disk or a failed
InnoDB: file read of page 691.
InnoDB: You may have to recover from a backup.
2018-01-05 08:19:45 7f3dd3ed1720 InnoDB: Page dump in ascii and hex (16384 bytes):

InnoDB: End of page dump
2018-01-05 08:19:45 7f3dd3ed1720 InnoDB: uncompressed page, 
stored checksum in field1 1575536212, calculated checksums for field1: crc32 1100691290, 
innodb 1575536212, none 3735928559, stored checksum in field2 2238042318, calculated checksums for field2: 
crc32 1100691290, innodb 3402961953, none 3735928559, page LSN 53 2349971314, low 4 bytes of LSN at page end 2349884494, 
page number (if stored to page already) 691, space id (if created with >= MySQL-4.1.1 and stored already) 0
InnoDB: Page may be an update undo log page
InnoDB: Database page corruption on disk or a failed
InnoDB: file read of page 691.
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.
InnoDB: If the corrupt page is an index page
InnoDB: you can also try to fix the corruption
InnoDB: by dumping, dropping, and reimporting
InnoDB: the corrupt table. You can use CHECK
InnoDB: TABLE to scan your table for corruption.
InnoDB: See also
InnoDB: about forcing recovery.
InnoDB: Ending processing because of a corrupt database page.
2018-01-05 08:19:45 7f3dd3ed1720  
InnoDB: Assertion failure in thread 139903525263136 in file line 4295
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: about forcing recovery.
00:19:45 UTC - 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.
We will try our best to scrape up some info that will hopefully help
diagnose the problem, but since we have already crashed,
something is definitely wrong and this may fail.
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 285949 K  bytes of memory
Hope that's ok; if not, decrease some variables in the equation.
8388608 + 131072 * 700
8388608 + 91750400 = 97792

Thread pointer: 0x0
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 0 thread_stack 0x40000
The manual page at contains
information that should help you find out what is causing the crash.

innodb_force_recovery = 4


As a safety measure, InnoDB prevents INSERT, UPDATE, or DELETE operations when innodb_force_recovery is greater than 0.


Lets the server run even if it detects a corrupt page. Tries to make SELECT * FROM tbl_name jump over corrupt index records and pages, which helps in dumping tables.


Prevents the master thread and any purge threads from running. If a crash would occur during the purge operation, this recovery value prevents it.


Does not run transaction rollbacks after crash recovery.


Prevents insert buffer merge operations. If they would cause a crash, does not do them. 
Does not calculate table statistics. This value can permanently corrupt data files. After using this value, be prepared to drop and recreate all secondary indexes.


Does not look at undo logs when starting the database: InnoDB treats even incomplete transactions as committed. This value can permanently corrupt data files.


Does not do the redo log roll-forward in connection with recovery. This value can permanently corrupt data files. Leaves database pages in an obsolete state, 
which in turn may introduce more corruption into B-trees and other database structures.

在my.cnf中添加完 innodb_force_recovery 配置后,mysql可以启动了,然后尝试修复所有的数据库:

mysqlcheck -u root -p --auto-repair --check --all-databases
common_schema.metadata                             OK
common_schema.numbers                              OK
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


xtrabackup: Error: failed to read page after 10 retries. File ./ibdata1 seems to be corrupted.

于是尝试在 innodb_force_recovery = 4 的情况下,使用xtrabackup备份一下数据库,然后重建mysql。
这个过程中 innobackupex 备份是成功的,重建mysql也是成功的,重新apply-log和copy-back也是成功,但是恢复之后的数据库仍然无法启动,跟之前的情况类似,已经很无语了。

但是问题是最近的备份距现在已经过去了20个小时,innodb crash 这段时间的更新会丢失,由于在innodb_force_recovery = 4的状态下,mysql是可以启动的,所以在原来的数据基础上启动了新的端口:3307,然后手动比较3307和3306两套数据的变化!