MySQL生产故障记录
MySQL生产故障记录
一个环境的MySQL发生故障，故障现象就一直无法启动，日志也没明显错误
Thread pointer: 0x7f3994000b60
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 = 7f3999ffadd8 thread_stack 0x40000
2022-04-30T09:59:53.384369Z 0 [Note] InnoDB: 5.7.32 started; log sequence number 257062865
2022-04-30T09:59:53.384736Z 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
mysqld(my_print_stacktrace+0x2c)[0x562aca0ade7c]
2022-04-30T09:59:53.385838Z 0 [Note] Plugin 'FEDERATED' is disabled.
mysqld(handle_fatal_signal+0x501)[0x562ac99c4f01]
/lib/x86_64-linux-gnu/libpthread.so.0(+0x12730)[0x7f39bf208730]
/lib/x86_64-linux-gnu/libc.so.6(gsignal+0x10b)[0x7f39bece37bb]
/lib/x86_64-linux-gnu/libc.so.6(abort+0x121)[0x7f39becce535]
mysqld(+0x6c1055)[0x562ac998c055]
mysqld(+0x6bdf8d)[0x562ac9988f8d]
mysqld(+0x10cca28)[0x562aca397a28]
mysqld(_Z9trx_purgemmb+0x563)[0x562aca39b763]
mysqld(srv_purge_coordinator_thread+0xab5)[0x562aca3738e5]
/lib/x86_64-linux-gnu/libpthread.so.0(+0x7fa3)[0x7f39bf1fdfa3]
/lib/x86_64-linux-gnu/libc.so.6(clone+0x3f)[0x7f39beda54cf]
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (0): Connection ID (thread ID): 0
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.
2022-04-30T10:00:18.202192Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2022-04-30T10:00:18.203796Z 0 [Note] mysqld (mysqld 5.7.32-log) starting as process 1 ...
2022-04-30T10:00:18.206442Z 0 [Note] InnoDB: PUNCH HOLE support available
2022-04-30T10:00:18.206455Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2022-04-30T10:00:18.206458Z 0 [Note] InnoDB: Uses event mutexes
2022-04-30T10:00:18.206461Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2022-04-30T10:00:18.206463Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2022-04-30T10:00:18.206465Z 0 [Note] InnoDB: Using Linux native AIO
2022-04-30T10:00:18.206671Z 0 [Note] InnoDB: Number of pools: 1
2022-04-30T10:00:18.206763Z 0 [Note] InnoDB: Using CPU crc32 instructions
2022-04-30T10:00:18.208355Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2022-04-30T10:00:18.215505Z 0 [Note] InnoDB: Completed initialization of buffer pool
2022-04-30T10:00:18.218112Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2022-04-30T10:00:18.231935Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2022-04-30T10:00:18.235226Z 0 [Note] InnoDB: Log scan progressed past the checkpoint lsn 257062799
2022-04-30T10:00:18.235251Z 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 257062865
2022-04-30T10:00:18.235276Z 0 [Note] InnoDB: Database was not shutdown normally!
2022-04-30T10:00:18.235285Z 0 [Note] InnoDB: Starting crash recovery.
2022-04-30T10:00:18.359405Z 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"
2022-04-30T10:00:18.359430Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2022-04-30T10:00:18.359487Z 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2022-04-30T10:00:18.394844Z 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2022-04-30T10:00:18.396692Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
2022-04-30T10:00:18.396719Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
2022-04-30 18:00:18 0x7f153dffb700 InnoDB: Assertion failure in thread 139729211209472 in file fut0lst.ic line 93
InnoDB: Failing assertion: addr.page == FIL_NULL || addr.boffset >= FIL_PAGE_DATA
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.7/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
10:00:18 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.
Attempting to collect some information that could help diagnose the problem.
As this is a crash and something is definitely wrong, the information
collection process might fail.
后面找文档发现可以修复，在配置文件中增加 innodb_force_recovery参数
参数含义：
参数innodb_force_recovery影响了整个Innodb存储引擎的恢复状况。该值默认为0，表示当需要恢复时执行所有的恢复操作。当不能进行有效恢复时，如数据页发生了corruption，Mysql数据库可能会宕机，并把错误写入错误日志中。
但在某些情况下，可能不需要执行完整的恢复操作。例如在进行alter table操作时，这时发生意外，数据库重启时会对Innodb表执行回滚操作。对于一个大表，这需要很长时间，甚至可能是几个小时。这时可以自行恢复，例如将表删除，从备份中重新将数据导入表中，这些操作可能要快于回滚操作。
Innodb_force_recovery可以设置6个非零值：
1(SRV_FORCE_IGNORE_CORRUPT):忽略检查到的corrupt页。
2(SRV_FORCE_NO_BACKGROUND):阻止主线程的运行，如主线程需要执行full purge操作，会导致crash。
3(SRV_FORCE_NO_TRX_UNDO):不执行事务回滚操作。
4(SRV_FORCE_NO_IBUF_MERGE):不执行插入缓冲的合并操作。
5(SRV_FORCE_NO_UNDO_LOG_SCAN):不查看重做日志，InnoDB存储引擎会将未提交的事务视为已提交。
6(SRV_FORCE_NO_LOG_REDO):不执行前滚的操作。
备注：当设置innodb_force_recovery大于0后，可以对标进行select、create、drop操作，但insert、update或者delete这类操作是不允许的。
修复：
1、在MySQL配置文件中加上 innodb_force_recovery=2，然后重启MySQL可以启动（1不行就2一直试到6）
2、全量备份MySQL数据
3、重新初始化MySQL
4、导入备份的MySQL数据后成功修复数据库
