MySQL崩溃- InnoDB

Question

我正在使用mysql 5.5

我所有的桌子都在innoDB里。

今天，我的应用程序崩溃了，mysql无法启动。

这是我的发现。

The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
########information that should help you find out what is causing the crash.
170806 10:50:09 [Note] Plugin 'FEDERATED' is disabled.
170806 10:50:09 InnoDB: The InnoDB memory heap is disabled
170806 10:50:09 InnoDB: Mutexes and rw_locks use GCC atomic builtins
170806 10:50:09 InnoDB: Compressed tables use zlib 1.2.3
170806 10:50:09 InnoDB: Using Linux native AIO
170806 10:50:09 InnoDB: Initializing buffer pool, size = 4.0G
170806 10:50:09 InnoDB: Completed initialization of buffer pool
170806 10:50:09 InnoDB: highest supported file format is Barracuda.
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
170806 10:50:10  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
170806 10:50:17  InnoDB: Error: page 7 log sequence number 302558359800
InnoDB: is in the future! Current system log sequence number 294192622092.
InnoDB: Your database may be corrupt or you may have copied the InnoDB
InnoDB: tablespace but not the InnoDB log files. See
InnoDB: http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html
InnoDB: for more information.
InnoDB: http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html
InnoDB: for more information.
170806 10:50:17  InnoDB: Error: page 1 log sequence number 302441785451
InnoDB: is in the future! Current system log sequence number 294192622092.
InnoDB: Your database may be corrupt or you may have copied the InnoDB
InnoDB: tablespace but not the InnoDB log files. See
InnoDB: http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html
InnoDB: for more information.
170806 10:50:17  InnoDB: Error: page 2 log sequence number 303029483310
InnoDB: is in the future! Current system log sequence number 294192622092.
InnoDB: Your database may be corrupt or you may have copied the InnoDB
InnoDB: tablespace but not the InnoDB log files. See
InnoDB: http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html
InnoDB: for more information.
170806 10:50:17  InnoDB: Error: page 4 log sequence number 302739570567
InnoDB: is in the future! Current system log sequence number 294192622092.
InnoDB: Your database may be corrupt or you may have copied the InnoDB
InnoDB: tablespace but not the InnoDB log files. See
InnoDB: http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html
InnoDB: for more information.
170806 10:50:17  InnoDB: Error: page 11 log sequence number 301115446423
InnoDB: is in the future! Current system log sequence number 294192622092.
InnoDB: Your database may be corrupt or you may have copied the InnoDB
InnoDB: tablespace but not the InnoDB log files. See
InnoDB: http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html
InnoDB: for more information.
170806 10:50:17  InnoDB: Error: page 5 log sequence number 303030219699
InnoDB: is in the future! Current system log sequence number 294192622092.
InnoDB: Your database may be corrupt or you may have copied the InnoDB
InnoDB: tablespace but not the InnoDB log files. See
InnoDB: http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html
InnoDB: for more information.
InnoDB: Cleaning up trx with id 15D24474E
InnoDB: Cleaning up trx with id 15D236307
InnoDB: Cleaning up trx with id 15D2360AA
InnoDB: Cleaning up trx with id 15D2359A6
170806 10:50:19 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
170806 10:50:19 [Note]   - '0.0.0.0' resolves to '0.0.0.0';
170806 10:50:19  InnoDB: Assertion failure in thread 140545344018176 in file trx0purge.c line 840
InnoDB: Failing assertion: purge_sys->purge_trx_no <= purge_sys->rseg->last_trx_no
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
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: http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
05:20:19 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.

key_buffer_size=7385088
read_buffer_size=131072
max_used_connections=0
read_buffer_size=131072
max_used_connections=0
max_threads=500
thread_count=0
connection_count=0
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 1101118 K  bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

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
170806 10:50:19 [Note] Server socket created on IP: '0.0.0.0'.
/usr/sbin/mysqld(my_print_stacktrace+0x35)[0x7add75]
/usr/sbin/mysqld(handle_fatal_signal+0x4a4)[0x688cd4]
/lib64/libpthread.so.0(+0xf7e0)[0x7fd45a6667e0]
/lib64/libc.so.6(gsignal+0x35)[0x7fd45982d495]
/lib64/libc.so.6(abort+0x175)[0x7fd45982ec75]
/usr/sbin/mysqld[0x85c462]
/usr/sbin/mysqld[0x85c7e1]
/usr/sbin/mysqld[0x929f32]
/usr/sbin/mysqld[0x91e802]
/usr/sbin/mysqld[0x85b15a]
/usr/sbin/mysqld[0x8507f2]
/lib64/libpthread.so.0(+0x7aa1)[0x7fd45a65eaa1]
/lib64/libc.so.6(clone+0x6d)[0x7fd4598e3bcd]
The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.

然后我添加了innodb_force_recovery=6，所以现在它已经启动了，但是无法登录。

InnoDB: space id 22147 did not exist in memory. Retrying an open.
170806 18:19:31  InnoDB: error: space object of table 'db/tbl',
InnoDB: space id 56405 did not exist in memory. Retrying an open.    
InnoDB: A new raw disk partition was initialized or
    InnoDB: innodb_force_recovery is on: we do not allow
    InnoDB: database modifications by the user. Shut down
    InnoDB: mysqld and edit my.cnf so that newraw is replaced
    InnoDB: with raw, and innodb_force_... is removed.
    InnoDB: A new raw disk partition was initialized or
    InnoDB: innodb_force_recovery is on: we do not allow
    InnoDB: database modifications by the user. Shut down
    InnoDB: mysqld and edit my.cnf so that newraw is replaced
    InnoDB: with raw, and innodb_force_... is removed.
    InnoDB: A new raw disk partition was initialized or
    InnoDB: innodb_force_recovery is on: we do not allow
    InnoDB: database modifications by the user. Shut down
    InnoDB: mysqld and edit my.cnf so that newraw is replaced
    InnoDB: with raw, and innodb_force_... is removed.

我如何才能确定是什么导致了这个问题，以及解决方法是什么？

Answer 1

根据您的日志，它显示了you have corruption in the InnoDB tablespace。

InnoDB: InnoDB表空间中的损坏。请参阅InnoDB：http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html InnoDB:关于强制恢复。

根据MySQL文档这里 for Forcing InnoDB Recovery

在重新启动服务器之前，可以将以下行添加到选项文件的米舍尔德部分：

[mysqld]
innodb_force_recovery = 1

警告

仅在紧急情况下将innodb_force_recovery设置为大于0的值，以便启动InnoDB并转储表。在这样做之前，请确保您有数据库的备份副本，以防需要重新创建数据库。值4或更大可永久损坏数据文件。在成功测试数据库的单独物理副本后，只在生产服务器实例上使用4或更高的innodb_force_recovery设置。当强制InnoDB恢复时，您应该始终从innodb_force_recovery=1开始，并且只在必要时增量地增加值。