![Mysql 崩溃,在恢复模式下转储了数据库。现在该怎么办?](https://linux22.com/image/647408/Mysql%20%E5%B4%A9%E6%BA%83%EF%BC%8C%E5%9C%A8%E6%81%A2%E5%A4%8D%E6%A8%A1%E5%BC%8F%E4%B8%8B%E8%BD%AC%E5%82%A8%E4%BA%86%E6%95%B0%E6%8D%AE%E5%BA%93%E3%80%82%E7%8E%B0%E5%9C%A8%E8%AF%A5%E6%80%8E%E4%B9%88%E5%8A%9E%EF%BC%9F.png)
我遇到了一个 InnoDB 问题,在我尝试修复它时,它似乎只会变得更糟。从 开始InnoDB: Unable to lock ./ibdata1, error: 11
,我移动它,然后将其复制回来。我很快又遇到了一个 ib_logfile 问题,解决失败了,此时我意识到确实有一个我错过的 mysql 进程正在运行,所以我用 终止了它killall -9 mysqld
。到目前为止,以前的解决尝试现在让我陷入了 的境地Assertion failure
。此时,我只是试着不要绊倒自己。
我已在恢复模式(级别“3”)下启动了数据库并转储了我的数据库。总共约 7gb。当不处于恢复模式时,错误如下:
141206 16:02:32 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql/
141206 16:02:32 InnoDB: Initializing buffer pool, size = 6.0G
141206 16:02:33 InnoDB: Completed initialization of buffer pool
InnoDB: Log scan progressed past the checkpoint lsn 546 4190820000
141206 16:02:33 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...
InnoDB: Doing recovery: scanned up to log sequence number 546 4190831828
InnoDB: Transaction 0 360762750 was in the XA prepared state.
InnoDB: 1 transaction(s) which must be rolled back or cleaned up
InnoDB: in total 0 row operations to undo
InnoDB: Trx id counter is 0 360763136
141206 16:02:33 InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percents: 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
InnoDB: Apply batch completed
InnoDB: Last MySQL binlog file position 0 6595, file name /var/lib/mysql/mysql-bin.000817
InnoDB: Starting in background the rollback of uncommitted transactions
141206 16:02:34 InnoDB: Rollback of non-prepared transactions completed
141206 16:02:34 InnoDB: Started; log sequence number 546 4190831828
/usr/libexec/mysqld: File '/var/log/mysql-slow.log' not found (Errcode: 13)
141206 16:02:34 [ERROR] Could not use /var/log/mysql-slow.log for logging (error 13). Turning logging off for the whole duration of the MySQL server process. To turn it on again: fix the cause, shutdown the MySQL server and restart it.
141206 16:02:34 InnoDB: Starting recovery for XA transactions...
141206 16:02:34 InnoDB: Transaction 0 360762750 in prepared state after recovery
141206 16:02:34 InnoDB: Transaction contains changes to 1 rows
141206 16:02:34 InnoDB: 1 transactions in prepared state after recovery
141206 16:02:34 [Note] Found 1 prepared transaction(s) in InnoDB
InnoDB: Error: trying to access page number 997017014 in space 0,
InnoDB: space name ./ibdata1,
InnoDB: which is outside the tablespace bounds.
InnoDB: Byte offset 0, len 16384, i/o type 10.
InnoDB: If you get this error at mysqld startup, please check that
InnoDB: your my.cnf matches the ibdata files that you have in the
InnoDB: MySQL server.
141206 16:02:34 InnoDB: Assertion failure in thread 140371479300064 in file fil/fil0fil.c line 4135
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.1/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
16:02:34 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=33554432
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 = 1125975 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
/usr/libexec/mysqld(my_print_stacktrace+0x29) [0x850989]
/usr/libexec/mysqld(handle_fatal_signal+0x483) [0x6a3ef3]
/lib64/libpthread.so.0(+0xf710) [0x7faac7e76710]
/lib64/libc.so.6(gsignal+0x35) [0x7faac64ac635]
/lib64/libc.so.6(abort+0x175) [0x7faac64ade15]
/usr/libexec/mysqld(fil_io+0x36e) [0x767e2e]
/usr/libexec/mysqld() [0x74fa03]
/usr/libexec/mysqld(buf_read_page+0x225) [0x750445]
/usr/libexec/mysqld(buf_page_get_gen+0x393) [0x749923]
/usr/libexec/mysqld(flst_insert_before+0x256) [0x772056]
/usr/libexec/mysqld(trx_purge_add_update_undo_to_history+0xf7) [0x7ccbe7]
/usr/libexec/mysqld(trx_undo_update_cleanup+0x29) [0x7dc789]
/usr/libexec/mysqld(trx_commit_off_kernel+0x1d9) [0x7db179]
/usr/libexec/mysqld(trx_finish_rollback_off_kernel+0x48) [0x7d3b58]
/usr/libexec/mysqld() [0x7a1f7a]
/usr/libexec/mysqld(que_run_threads+0x1f4) [0x7a21d4]
/usr/libexec/mysqld(trx_general_rollback_for_mysql+0xc0) [0x7d5390]
/usr/libexec/mysqld(trx_rollback_for_mysql+0x95) [0x7d5795]
/usr/libexec/mysqld() [0x732840]
/usr/libexec/mysqld() [0x73288b]
/usr/libexec/mysqld() [0x69976e]
/usr/libexec/mysqld(plugin_foreach_with_mask(THD*, char (*)(THD*, st_plugin_int*, void*), int, unsigned int, void*)+0x140) [0x7161e0]
/usr/libexec/mysqld(ha_recover(st_hash*)+0x9f) [0x6977ef]
/usr/libexec/mysqld() [0x5bf409]
/usr/libexec/mysqld(main+0x1b3) [0x5c1af3]
/lib64/libc.so.6(__libc_start_main+0xfd) [0x7faac6498d5d]
/usr/libexec/mysqld() [0x5098f9]
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.
141206 16:02:34 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
此时,下一步是否会删除并恢复数据库,或者是否应该采取一些更具侵入性或更具侵入性的方法?
答案1
一般情况下,innodb_force_recovery
模式应该让用户启动 InnoDB 并转储有价值的数据。
人们普遍认为,在启用 后,InnoDB 会修复表空间innodb_force_recovery
。不,它不会。(在某些情况下,您可以通过删除特定表来修复表空间,但那是另一回事)。
如果你足够幸运并且 MySQL 启动了,下一步就是从数据库中转储
mysqldump --skip-lock-tables -A > my_database.sql
然后你需要清除所有 InnoDB 文件,并从转储中恢复数据库
mysql < my_database.sql