force_innodb_recovery 用法和错误日志

分类:笔记 293浏览

此文档用于mysql innodb异常时强行拯救数据库用,发生死机、异常关机、异常重启、或进程崩溃后可能会导致innodb异常,在启动mysql会出现Carsh错误或是已经启动但无法通过3306链接,netstat -an 内也看不到端口监听。

相关错误日志:

2024-04-06T17:56:31.710033Z 0 [System] [MY-015015] [Server] MySQL Server - start.
2024-04-06T17:56:31.977805Z 0 [System] [MY-010116] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.2.0) starting as process 99589
2024-04-06T17:56:31.996558Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2024-04-06T17:56:32.719205Z 0 [ERROR] [MY-013183] [InnoDB] Assertion failure: log0recv.cc:1931:!page || page_is_comp(page) == dict_table_is_comp(index->table) thread 139989020174080
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.2/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
2024-04-06T17:56:32Z UTC - mysqld got signal 6 ;
Most likely, you have hit a bug, but this error can also be caused by malfunctioning hardware.
BuildID[sha1]=0dce9d32385a353b68cc2c62004d21f7dac74fac
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...
2024-04-06T17:56:32.719211Z 0 [ERROR] [MY-013183] [InnoDB] Assertion failure: log0recv.cc:1931:!page || page_is_comp(page) == dict_table_is_comp(index->table) thread 139989011781376
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.2/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
stack_bottom = 0 thread_stack 0x100000
/usr/local/mysql/bin/mysqld(my_print_stacktrace(unsigned char const*, unsigned long)+0x2e) [0x20c6d6e]
/usr/local/mysql/bin/mysqld(print_fatal_signal(int)+0x35f) [0xfdfacf]
/usr/local/mysql/bin/mysqld(my_server_abort()+0x6e) [0xfdfc0e]
/usr/local/mysql/bin/mysqld(my_abort()+0xa) [0x20c177a]
/usr/local/mysql/bin/mysqld(ut_dbg_assertion_failed(char const*, char const*, unsigned long)+0x312) [0x22d7cd2]
/usr/local/mysql/bin/mysqld() [0x21d2721]
/usr/local/mysql/bin/mysqld(recv_recover_page_func(bool, buf_block_t*)+0x62b) [0x21d539b]
/usr/local/mysql/bin/mysqld(buf_page_io_complete(buf_page_t*, bool, IORequest*, fil_node_t*)+0x4c0) [0x2336f60]
/usr/local/mysql/bin/mysqld(fil_aio_wait(unsigned long)+0x162) [0x2428bf2]
/usr/local/mysql/bin/mysqld() [0x21f22f0]
/usr/local/mysql/bin/mysqld(std::thread::_State_impl<std::thread::_Invoker<std::tuple<Detached_thread, void (*)(unsigned long), unsigned long> > >::_M_run()+0xb6) [0x21e93b6]
/usr/local/mysql/bin/mysqld() [0x2a6f8b4]
/lib64/libpthread.so.0(+0x81ca) [0x7f51e36b81ca]
/lib64/libc.so.6(clone+0x43) [0x7f51e1902e73]
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.
2024-04-06T17:56:32Z UTC - mysqld got signal 6 ;
Most likely, you have hit a bug, but this error can also be caused by malfunctioning hardware.
BuildID[sha1]=0dce9d32385a353b68cc2c62004d21f7dac74fac
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 0x100000
/usr/local/mysql/bin/mysqld(my_print_stacktrace(unsigned char const*, unsigned long)+0x2e) [0x20c6d6e]
/usr/local/mysql/bin/mysqld(print_fatal_signal(int)+0x35f) [0xfdfacf]
/usr/local/mysql/bin/mysqld(my_server_abort()+0x6e) [0xfdfc0e]
/usr/local/mysql/bin/mysqld(my_abort()+0xa) [0x20c177a]
/usr/local/mysql/bin/mysqld(ut_dbg_assertion_failed(char const*, char const*, unsigned long)+0x312) [0x22d7cd2]
/usr/local/mysql/bin/mysqld() [0x21d2721]
/usr/local/mysql/bin/mysqld(recv_recover_page_func(bool, buf_block_t*)+0x62b) [0x21d539b]
/usr/local/mysql/bin/mysqld(buf_page_io_complete(buf_page_t*, bool, IORequest*, fil_node_t*)+0x4c0) [0x2336f60]
/usr/local/mysql/bin/mysqld(fil_aio_wait(unsigned long)+0x162) [0x2428bf2]
/usr/local/mysql/bin/mysqld() [0x21f22f0]
/usr/local/mysql/bin/mysqld(std::thread::_State_impl<std::thread::_Invoker<std::tuple<Detached_thread, void (*)(unsigned long), unsigned long> > >::_M_run()+0xb6) [0x21e93b6]
/usr/local/mysql/bin/mysqld() [0x2a6f8b4]
/lib64/libpthread.so.0(+0x81ca) [0x7f51e36b81ca]
/lib64/libc.so.6(clone+0x43) [0x7f51e1902e73]
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个参数,以下为详细介绍:

1 (SRV_FORCE_IGNORE_CORRUPT)
#使服务器即使检测到损坏的页面也可以运行 。尝试 跳过损坏的索引记录和页,这有助于转储表。SELECT * FROM tbl_name
2 (SRV_FORCE_NO_BACKGROUND)
#防止主线程和任何清除线程运行。如果在清除操作期间发生意外退出,则此恢复值将阻止它。
3 (SRV_FORCE_NO_TRX_UNDO)
#崩溃恢复后 不运行事务 回滚。
4 (SRV_FORCE_NO_IBUF_MERGE)
#防止插入缓冲区合并操作。如果它们会导致崩溃,请不要这样做。不计算表统计信息。此值可能会永久损坏数据文件。使用此值后,
#准备删除并重新创建所有二级索引。设置 InnoDB为只读。
5 (SRV_FORCE_NO_UNDO_LOG_SCAN)
#启动数据库时 不查看撤消日志:InnoDB甚至将未完成的事务也视为已提交。此值可能会永久损坏数据文件。设置InnoDB为只读。
6 (SRV_FORCE_NO_LOG_REDO)
#不进行与恢复有关的重做日志前滚。此值可能会永久损坏数据文件。使数据库页面处于过时状态,这反过来可能会使B树和其他数据库结构遭受更多破坏#设置 InnoDB为只读。

使用方法和启动方案:

编辑数据库文件:


vi /etc/my.cnf

填入并保存:

innodb_force_recovery = 1
skip-grant-tables

尝试启动Mysql:

/usr/local/mysql/bin/mysqld_safe --skip-grant-tables &

直到可以链接Mysql后再进行下一步如mysqldump操作。

如果无法启动或长时间未响应,执行:

ps -aux | grep mysqld
kill pid

杀死线程后重新执行

使用策略和方案:

1、如果确保绝对的安全,保存对应数据库的idb、ibdata1等相关文件,用于操作回档。

2、如果是虚拟机下运行建议先创建快照已备快速回档。

3、innodb_force_recovery启动mysql时,应当从1-3逐个开始,有时候执行到1成功启动后,innodb会自动修复相关问题,则完全结束。

4、如果1无法启动,依次选用2-3,前3项基本不会导致数据丢失或损坏。

5、如果前3项无法启动,在进行第4-6项时建议先备份后操作,4-6项意味着会损坏现在innodb的数据,请做好数据丢失、最大限度挽回的准备。

6、在我个人的实际使用下,唯有第6项才能正常启动,无论如何尽快mysqldump出还能救的数据,尽可能发现错误则跳过放弃此表(异常表)数据,将其它有用的mysqldump回本地。

相关文献参考:

http://dev.mysql.com/doc/refman/8.2/en/forcing-innodb-recovery.html

http://dev.mysql.com/doc/mysql/en/crashing.html