MySQL8崩溃恢复

Question

我的dev MySQL-8服务器昨晚崩溃了，无法重新启动。错误是：

[ERROR] [MY-012646] [InnoDB] File ./ibdata1: 'open' returned OS error 71. Cannot continue operation

我尝试了经典的“从innodb_force_recovery=1开始”，但这有0的效果(也删除了that =2，但没有成功。我还尝试删除ibdata和ib日志文件:它也不会启动。

我真的很惊讶这些“标准”方法并不适用于仅仅是崩溃的东西.但正如我们所知，邪恶经常存在于细节中。

因此，我尝试的最后一个选项是重新创建数据库中的表，然后替换由崩溃服务器自动生成的.idb文件。

积极的是MySQL数据库开始..。但是，当我想看到表的内容时，我会收到一条错误消息，上面写着：

Error Code: 1812. Tablespace is missing for table <table>.

我觉得我快到了..。欢迎任何帮助或建议。

非常感谢-克里斯

Answer 1

一些更新:当我用innodb_force_recovery=2启动它时，我可以启动db并转储我的数据，这是向前迈进了一步。

但是当我设置为innodb_force_recovery=0时，下面的消息失败了。当你从恢复力开始的时候，InnoDB不是自愈吗？

谢谢-C

InnoDB: End of page dump
InnoDB: Page may be an update undo log page
2021-03-24T10:38:09.631071Z 1 [ERROR] [MY-011899] [InnoDB] [FATAL] Unable to read page [page id: space=4294967279, page number=176] into the buffer pool after 100 attempts. The most probable cause of this error may be that the table has been corrupted. Or, the table was compressed with with an algorithm that is not supported by this instance. If it is not a decompress failure, you can try to fix this problem by using innodb_force_recovery. Please see http://dev.mysql.com/doc/refman/8.0/en/ for more details. Aborting...
2021-03-24T10:38:09.631894Z 1 [ERROR] [MY-013183] [InnoDB] Assertion failure: ut0ut.cc:532 thread 139943235643136
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/8.0/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
10:38:09 UTC - mysqld got signal 6 ;
Most likely, you have hit a bug, but this error can also be caused by malfunctioning hardware.
Thread pointer: 0x5e68130
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 = 7f4712d95bc0 thread_stack 0x46000
/usr/sbin/mysqld(my_print_stacktrace(unsigned char const*, unsigned long)+0x3d) [0x1f70f0d]
/usr/sbin/mysqld(handle_fatal_signal+0x333) [0xfc3603]
/lib64/libpthread.so.0(+0xf5f0) [0x7f472b48d5f0]
/lib64/libc.so.6(gsignal+0x37) [0x7f4729544337]
/lib64/libc.so.6(abort+0x148) [0x7f4729545a28]
/usr/sbin/mysqld() [0xd2e0aa]
/usr/sbin/mysqld(ib::fatal::~fatal()+0xa4) [0x221d614]
/usr/sbin/mysqld(Buf_fetch<Buf_fetch_normal>::read_page()+0x192) [0x227c182]
/usr/sbin/mysqld(Buf_fetch_normal::get(buf_block_t*&)+0x18) [0x227c1d8]
/usr/sbin/mysqld(Buf_fetch<Buf_fetch_normal>::single_page()+0x42) [0x2281882]
/usr/sbin/mysqld(buf_page_get_gen(page_id_t const&, page_size_t const&, unsigned long, buf_block_t*, Page_fetch, char const*, unsigned long, mtr_t*, bool)+0x1cd) [0x228244d]
/usr/sbin/mysqld(trx_undo_lists_init(trx_rseg_t*)+0x262) [0x2217c22]
/usr/sbin/mysqld(trx_rseg_mem_create(unsigned long, unsigned int, unsigned int, page_size_t const&, unsigned long, std::priority_queue<TrxUndoRsegs, std::vector<TrxUndoRsegs, ut_allocator<TrxUndoRsegs> >, TrxUndoRsegs>*, mtr_t*)+0x3b3) [0x21ff0d3]
/usr/sbin/mysqld(trx_rsegs_init(std::priority_queue<TrxUndoRsegs, std::vector<TrxUndoRsegs, ut_allocator<TrxUndoRsegs> >, TrxUndoRsegs>*)+0x7cd) [0x2200b2d]
/usr/sbin/mysqld(trx_sys_init_at_db_start()+0x134) [0x2205b74]
/usr/sbin/mysqld(srv_start(bool, std::string const&)+0x2055) [0x21cc055]
/usr/sbin/mysqld() [0x204860e]
/usr/sbin/mysqld(dd::bootstrap::DDSE_dict_init(THD*, dict_init_mode_t, unsigned int)+0x90) [0x1ca5960]
/usr/sbin/mysqld(dd::upgrade_57::do_pre_checks_and_initialize_dd(THD*)+0x19a) [0x1f3b1ca]
/usr/sbin/mysqld() [0x107fe88]
/usr/sbin/mysqld() [0x246d2bc]
/lib64/libpthread.so.0(+0x7e65) [0x7f472b485e65]
/lib64/libc.so.6(clone+0x6d) [0x7f472960c88d]

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (0): Connection ID (thread ID): 1
Status: NOT_KILLED

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.