生产环境的服务器被客户强制重启之后导致数据库连不上了，在此记录下处理的办法：
系统版本：Windows10
安装方式：Docker
MySQL版本：8.0.23
- MySQL启动日志如下：
2022-09-23 02:50:19+00:00 [Note] [Entrypoint]: Switching to dedicated user 'mysql'
2022-09-23 02:50:19+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 8.0.23-1debian10 started.
2022-09-23T02:50:19.972063Z 0 [Warning] [MY-011070] [Server] 'Disabling symbolic links using --skip-symbolic-links (or equivalent) is the default. Consider not using this option as it' is deprecated and will be removed in a future release.
2022-09-23T02:50:19.972466Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.23) starting as process 1
2022-09-23T02:50:19.977859Z 0 [Warning] [MY-010159] [Server] Setting lower_case_table_names=2 because file system for /var/lib/mysql/ is case insensitive
2022-09-23T02:50:19.994120Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2022-09-23T02:50:20.954063Z 0 [ERROR] [MY-013183] [InnoDB] Assertion failure: trx0undo.cc:519:free + TRX_UNDO_LOG_GTID_XA_HDR_SIZE < UNIV_PAGE_SIZE - 100 thread 140337720850176
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.
02:50:20 UTC - mysqld got signal 6 ;
Most likely, you have hit a bug, but this error can also be caused by malfunctioning hardware.
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 0x46000
/usr/sbin/mysqld(my_print_stacktrace(unsigned char const*, unsigned long)+0x2e) [0x563f44bbb39e]
/usr/sbin/mysqld(handle_fatal_signal+0x31b) [0x563f43f7522b]
/lib/x86_64-linux-gnu/libpthread.so.0(+0x12730) [0x7fa2ff9d7730]
/lib/x86_64-linux-gnu/libc.so.6(gsignal+0x10b) [0x7fa2ff0b47bb]
/lib/x86_64-linux-gnu/libc.so.6(abort+0x121) [0x7fa2ff09f535]
/usr/sbin/mysqld(+0x35624d3) [0x563f450794d3]
/usr/sbin/mysqld(+0x355b6f0) [0x563f450726f0]
/usr/sbin/mysqld(trx_undo_parse_page_header(mlog_id_t, unsigned char const*, unsigned char const*, unsigned char*, mtr_t*)+0x5e) [0x563f4507390e]
/usr/sbin/mysqld(+0x3402bdd) [0x563f44f19bdd]
/usr/sbin/mysqld(recv_recover_page_func(bool, buf_block_t*)+0x700) [0x563f44f1c020]
/usr/sbin/mysqld(buf_page_io_complete(buf_page_t*, bool)+0x277) [0x563f44d21ff7]
/usr/sbin/mysqld(fil_aio_wait(unsigned long)+0x119) [0x563f44d367c9]
/usr/sbin/mysqld(+0x34feff0) [0x563f45015ff0]
/usr/sbin/mysqld(std::thread::_State_impl<std::thread::_Invoker<std::tuple<Runnable, void (*)(unsigned long), unsigned long> > >::_M_run()+0xa9) [0x563f44d991c9]
/usr/lib/x86_64-linux-gnu/libstdc++.so.6(+0xbbb2f) [0x7fa2ff498b2f]
/lib/x86_64-linux-gnu/libpthread.so.0(+0x7fa3) [0x7fa2ff9ccfa3]
/lib/x86_64-linux-gnu/libc.so.6(clone+0x3f) [0x7fa2ff1764cf]
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文件损坏了，需要备份文件之后重新建一个MySQL容器
- 修改MySQL配置文件：my.cnf，在[mysqld]后加入如下配置
innodb_force_recovery = 1
innodb_force_recovery 参数
0：默认为0（没有强制恢复的正常启动）
1：(SRV_FORCE_IGNORE_CORRUPT): 忽略检查到的 corrupt 页。尽管检测到了损坏的 page 仍强制服务运行。一般设置为该值即可，然后 dump 出库表进行重建。
2：(SRV_FORCE_NO_BACKGROUND): 阻止主线程的运行，如主线程需要执行 full purge 操作，会导致 crash。 阻止 master thread 和任何 purge thread 运行。若 crash 发生在 purge 环节则使用该值。
3：(SRV_FORCE_NO_TRX_UNDO): 不执行事务回滚操作。如果你能以innodb_force_recovery为3或更低值转储你的表，那么你是比较安全的，只有在损坏的个人页的一些数据会丢失。
4：(SRV_FORCE_NO_IBUF_MERGE): 不执行插入缓冲的合并操作。如果可能导致崩溃则不要做这些操作。不要进行统计操作。该值可能永久损坏数据文件。若使用了该值，则将来要删除和重建辅助索引。4或更大的值被认为是危险的，因为数据文件可以被永久地损坏。
5：(SRV_FORCE_NO_UNDO_LOG_SCAN): 不查看重做日志，InnoDB 存储引擎会将未提交的事务视为已提交。此时 InnoDB 甚至把未完成的事务按照提交处理。该值可能永久性的损坏数据文件。
6：(SRV_FORCE_NO_LOG_REDO): 不执行前滚的操作。恢复时不做 redo log roll-forward。值6被认为是严重的，数据库页被留在一个陈旧的状态，这反过来又可能带给B-trees和其它数据库结构更多的损坏。
InnoDB 在innodb_force_recovery大于0时阻止INSERT，UPDATE或DELETE操作。对于MySQL 5.6.15，将innodb_force_recovery设为4或更高会让InnoDB处于只读模式。
innodb_force_recovery的数值从1开始往后试，最大值是6，我是试了1-4都无法启动数据库，后来用6成功启动起来了。
- 启动成功之后，将需要的数据库备份出来。
- 创建新的容器，将数据库恢复。
网友评论