记一次MySQL生产环境无法启动的问题

今早发现个人站点MySQL无法启动，导致无法登录，查看日志发现以下报错：

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...
2019-11-15 08:47:24 7518 [Note] Plugin 'FEDERATED' is disabled.
2019-11-15 08:47:24 7518 [Note] InnoDB: Using atomics to ref count buffer pool pages
2019-11-15 08:47:24 7518 [Note] InnoDB: The InnoDB memory heap is disabled
2019-11-15 08:47:24 7518 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2019-11-15 08:47:24 7518 [Note] InnoDB: Memory barrier is not used
2019-11-15 08:47:24 7518 [Note] InnoDB: Compressed tables use zlib 1.2.11
2019-11-15 08:47:24 7518 [Note] InnoDB: Using Linux native AIO
2019-11-15 08:47:24 7518 [Note] InnoDB: Using CPU crc32 instructions
2019-11-15 08:47:24 7518 [Note] InnoDB: Initializing buffer pool, size = 256.0M
2019-11-15 08:47:24 7518 [Note] InnoDB: Completed initialization of buffer pool
2019-11-15 08:47:24 7518 [Note] InnoDB: Highest supported file format is Barracuda.
2019-11-15 08:47:24 7518 [Note] InnoDB: Log scan progressed past the checkpoint lsn 8081314994
2019-11-15 08:47:24 7518 [Note] InnoDB: Database was not shutdown normally!
2019-11-15 08:47:24 7518 [Note] InnoDB: Starting crash recovery.
2019-11-15 08:47:24 7518 [Note] InnoDB: Reading tablespace information from the .ibd files...
2019-11-15 08:47:24 7518 [Note] InnoDB: Restoring possible half-written data pages
2019-11-15 08:47:24 7518 [Note] InnoDB: from the doublewrite buffer...
InnoDB: Doing recovery: scanned up to log sequence number 8081327808
2019-11-15 08:47:24 7518 [Note] InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percent: 64 65 66 67 68 2019-11-15 08:47:24 2b3267ecb700 InnoDB: Error: page 78 log sequence number 8081835177
InnoDB: is in the future! Current system log sequence number 8081327808.
InnoDB: Your database may be corrupt or you may have copied the InnoDB
InnoDB: tablespace but not the InnoDB log files. See
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html
InnoDB: for more information.
69 70 71 2019-11-15 08:47:24 2b3267cca70000:47:24 UTC - mysqld got signal 11 ;
InnoDB: Error: page 3 log sequence number 8081837465
InnoDB: is in the future! Current system log sequence number 8081327808.
InnoDB: Your database may be corrupt or you may have copied the InnoDB
InnoDB: tablespace but not the InnoDB log files. See
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html
InnoDB: for more information.
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=67108864
read_buffer_size=1048576
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 = 1095926 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...
72 73 stack_bottom = 0 thread_stack 0x40000
74 75 76 77 /www/server/mysql/bin/mysqld(my_print_stacktrace+0x2c)[0x903fac]
78 79 /www/server/mysql/bin/mysqld(handle_fatal_signal+0x357)[0x6747d7]
/lib64/libpthread.so.0(+0xf5f0)[0x2b323e5ec5f0]
80 81 /www/server/mysql/bin/mysqld[0x98c904]
2019-11-15 08:47:24 2b3267cca700 InnoDB: Error: page 18 log sequence number 8081565946
InnoDB: is in the future! Current system log sequence number 8081327808.
InnoDB: Your database may be corrupt or you may have copied the InnoDB
InnoDB: tablespace but not the InnoDB log files. See
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html
InnoDB: for more information.
82 /www/server/mysql/bin/mysqld[0x98e58c]
83 84 /www/server/mysql/bin/mysqld[0x97666c]
85 86 87 /www/server/mysql/bin/mysqld[0x978107]
88 2019-11-15 08:47:24 2b3267cca700 InnoDB: Error: page 10 log sequence number 8081837122
InnoDB: is in the future! Current system log sequence number 8081327808.
InnoDB: Your database may be corrupt or you may have copied the InnoDB
InnoDB: tablespace but not the InnoDB log files. See
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html
InnoDB: for more information.
/www/server/mysql/bin/mysqld[0xa4c068]
89 90 91 /www/server/mysql/bin/mysqld[0xa90a76]
92 93 /www/server/mysql/bin/mysqld[0x9eeb78]
/lib64/libpthread.so.0(+0x7e65)[0x2b323e5e4e65]
/lib64/libc.so.6(clone+0x6d)[0x2b323f75388d]
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引擎出了问题。日志里提示到 http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html查看强制恢复的方法。

进入innodb恢复模式：

如果是宝塔面板，在软件商店-MySQL，打开配置修改，在第六行mysqld下面加入：

innodb_force_recovery = 6

其他自行编译的MySQL的话，请在my.conf里面的mysqld下面加入：

innodb_force_recovery = 6

如果innodb_force_recovery = 6不生效，则可尝试2——6几个数字。

扫盲：
innodb_force_recovery影响整个InnoDB存储引擎的恢复状况。默认为0，表示当需要恢复时执行所有的恢复操作（即校验数据页/purge undo/insert buffer merge/rolling back&forward），当不能进行有效的恢复操作时，mysql有可能无法启动，并记录错误日志；
innodb_force_recovery可以设置为1-6,大的数字包含前面所有数字的影响。当设置参数值大于0后，可以对表进行select,create,drop操作,但insert,update或者delete这类操作是不允许的。

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):不执行前滚的操作。

然后重启MySQL服务：

systemctl restart mysqld.service

正常启动MySQL后，把数据库进行备份，可以参考此文章：Linux下使用mysqldump进行数据库备份恢复

备份后，我们删除data目录下的这三个文件：

rm -f ib_logfile0 ib_logfile1 ibdata1

注：宝塔的data路径为：/www/server/data

退出innodb恢复模式：

删除文件后，我们回到第一步。

如果是宝塔面板，在软件商店-MySQL，打开配置修改，注释下面这行：

innodb_force_recovery = 6

或者将其改为：

innodb_force_recovery = 0

其他版本MySQL操作一致，修改my.conf进行注释即可。

操作完成后，我们进行数据库导入，可以参考此文章：Linux下使用mysqldump进行数据库备份恢复

导入过程中会遇到如下报错：

Tablespace for table `tablexx` exists. Please DISCARD the tablespace before IMPORT.

这是因为我们之前的数据库中存在了文件，导致无法导入。

解决办法：

1.删除之前数据库，然后再添加。

2.把数据库之前的目录清空，再进行导入

注：宝塔面板数据库目录为：/www/server/data 将其目录下的数据库文件夹清空即可。

至此，数据库恢复完成。

后记：

通过搜索发现，出现此问题的用户并不少，但是大多解释都为MySQL版本BUG，通过升级版本即可修复，但是由于一部分生产环境的MySQL开发版本就是5.x，无法进行升级，只能进行修复，希望不会再出现此问题，泪奔~