MySql Server 无法在 innodb_force_recovery 设置为值 1-6 的情况下启动

MySql Server 无法在 innodb_force_recovery 设置为值 1-6 的情况下启动

我有一个 ubuntu 14.04 KVM,它作为服务器 LAMP 运行,直到今天我安装了 Wordpress 插件并且 MySql Server 5.5 停止工作。

我曾多次尝试启动服务器,并将 innodb_force_recovery 设置为 1 到 6,以便恢复数据和信息。我不在乎丢失一些数据,但我不能承受丢失全部数据。(我应该更频繁地备份,这是我的错)。

我的mysqld的配置是:

[MYSQLD]
innodb_force_recovery=6
innodb_fast_shutdown=0
innodb_purge_threads=0
#table_open_cache=4450
#table_definition_cache=1450
#innodb_buffer_pool_size=4G

错误日志给了我以下信息:

000000000000000000000000000000000000000000000000000000000000000000(the 0 are many more but I did not paste it); asc                                                                                                                                                                                                                                   ## here I have many many empty lines                                                                                                                                                       ;
InnoDB: End of page dump
160428 18:34:39  InnoDB: Page checksum 1575996416, prior-to-4.0.14-form checksum 1371122432
InnoDB: stored checksum 0, prior-to-4.0.14-form stored checksum 0
InnoDB: Page lsn 0 0, low 4 bytes of lsn at page end 0
InnoDB: Page number (if stored to page already) 0,
InnoDB: space id (if created with >= MySQL-4.1.1 and stored already) 0
InnoDB: Page may be a freshly allocated page
160428 18:34:39  InnoDB: Assertion failure in thread 140591351621376 in file rem0rec.c line 569
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.
17:34:39 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=16777216
read_buffer_size=131072
max_used_connections=0
max_threads=151
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 = 346701 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 0x30000
/usr/sbin/mysqld(my_print_stacktrace+0x20)[0x5613594c3390]
/usr/sbin/mysqld(handle_fatal_signal+0x3d5)[0x5613593ac785]
/lib/x86_64-linux-gnu/libpthread.so.0(+0x10340)[0x7fde0f230340]
/lib/x86_64-linux-gnu/libc.so.6(gsignal+0x39)[0x7fde0e887cc9]
/lib/x86_64-linux-gnu/libc.so.6(abort+0x148)[0x7fde0e88b0d8]
/usr/sbin/mysqld(+0x6534f0)[0x5613596234f0]
/usr/sbin/mysqld(+0x640f5d)[0x561359610f5d]
/usr/sbin/mysqld(+0x5bcd8e)[0x56135958cd8e]
/usr/sbin/mysqld(+0x57f25d)[0x56135954f25d]
/usr/sbin/mysqld(+0x65bb03)[0x56135962bb03]
/usr/sbin/mysqld(+0x660361)[0x561359630361]
/usr/sbin/mysqld(+0x65c128)[0x56135962c128]
/usr/sbin/mysqld(+0x651ac4)[0x561359621ac4]
/usr/sbin/mysqld(+0x5a1b93)[0x561359571b93]
/usr/sbin/mysqld(+0x5a210e)[0x56135957210e]
/lib/x86_64-linux-gnu/libpthread.so.0(+0x8182)[0x7fde0f228182]
/lib/x86_64-linux-gnu/libc.so.6(clone+0x6d)[0x7fde0e94b47d]
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: End of page dump
160428 18:34:39  InnoDB: Page checksum 1575996416, prior-to-4.0.14-form checksum 1371122432
InnoDB: stored checksum 0, prior-to-4.0.14-form stored checksum 0
InnoDB: Page lsn 0 0, low 4 bytes of lsn at page end 0
InnoDB: Page number (if stored to page already) 0,
InnoDB: space id (if created with >= MySQL-4.1.1 and stored already) 0
InnoDB: Page may be a freshly allocated page
160428 18:34:39  InnoDB: Assertion failure in thread 140591351621376 in file rem0rec.c line 569
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.
17:34:39 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=16777216
read_buffer_size=131072
max_used_connections=0
max_threads=151
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 = 346701 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 0x30000
/usr/sbin/mysqld(my_print_stacktrace+0x20)[0x5613594c3390]
/usr/sbin/mysqld(handle_fatal_signal+0x3d5)[0x5613593ac785]
/lib/x86_64-linux-gnu/libpthread.so.0(+0x10340)[0x7fde0f230340]
/lib/x86_64-linux-gnu/libc.so.6(gsignal+0x39)[0x7fde0e887cc9]
/lib/x86_64-linux-gnu/libc.so.6(abort+0x148)[0x7fde0e88b0d8]
/usr/sbin/mysqld(+0x6534f0)[0x5613596234f0]
/usr/sbin/mysqld(+0x640f5d)[0x561359610f5d]
/usr/sbin/mysqld(+0x5bcd8e)[0x56135958cd8e]
/usr/sbin/mysqld(+0x57f25d)[0x56135954f25d]
/usr/sbin/mysqld(+0x65bb03)[0x56135962bb03]
/usr/sbin/mysqld(+0x660361)[0x561359630361]
/usr/sbin/mysqld(+0x65c128)[0x56135962c128]
/usr/sbin/mysqld(+0x651ac4)[0x561359621ac4]
/usr/sbin/mysqld(+0x5a1b93)[0x561359571b93]
/usr/sbin/mysqld(+0x5a210e)[0x56135957210e]
/lib/x86_64-linux-gnu/libpthread.so.0(+0x8182)[0x7fde0f228182]
/lib/x86_64-linux-gnu/libc.so.6(clone+0x6d)[0x7fde0e94b47d]
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.

我的主要目标是恢复数据库,但我无法启动服务器。有人能帮我找到解决方案吗?我已经尝试了几个小时,但我没有其他选择。

谢谢你!

答案1

我设法使用 #mysqld_safe --innodb_force_recovery 4 启动服务器。我尝试了数百次重新启动 mysql 服务器,但结果就是这样。

在我恢复了我能够进行的备份之后,一切似乎都正常运行。

答案2

对于我来说,无论innodb_force_recovery设置到什么级别,恢复都不是一个选择。

基于这个答案,而且由于 InnoDB 是问题的一部分,所以我完全禁用了 InnoDB。

您可以编辑/etc/my.cnf以添加我添加的内容或声明innodb_force_recovery=1,但它必须位于部分中[mysqld]grep搜索发现该部分已经在中/etc/my.cnf.d/server.cnf。因此,我添加了[mysqld]

[mysqld]
innodb=OFF
default_storage_engine=MyISAM

然后,一切启动正常,我可以进入并开始使用 MySQL。

相关内容