服务器异常断电后Docker中Mysql无法启动问题解决,直接安装的Mysql无法启动解决思路一致。
环境
- Mysql8.0
- CentOS7
- Docker20.10.17
问题排查
查看容器日志
docker logs 容器ID
错误信息
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.
2023-02-20 06:54:21+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 8.0.23-1debian10 started.
2023-02-20 06:54:21+00:00 [Note] [Entrypoint]: Switching to dedicated user 'mysql'
2023-02-20 06:54:21+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 8.0.23-1debian10 started.
mysqld: [ERROR] Found option without preceding group in config file /etc/mysql/conf.d/my.cnf at line 1.
mysqld: [ERROR] Found option without preceding group in config file /etc/mysql/conf.d/mysql.cnf at line 1.
2023-02-20T06:54:21.664201Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.23) starting as process 1
2023-02-20T06:54:21.671960Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2023-02-20T06:54:22.466842Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2023-02-20T06:54:22.888621Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /var/run/mysqld/mysqlx.sock
2023-02-20T06:54:22.919170Z 0 [ERROR] [MY-013183] [InnoDB] Assertion failure: fut0lst.ic:86:addr.page == FIL_NULL || addr.boffset >= FIL_PAGE_DATA thread 140322109630208
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.
06:54:22 UTC - mysqld got signal 6 ;
Most likely, you have hit a bug, but this error can also be caused by malfunctioning hardware.
Thread pointer: 0x7f9f28000b20
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 = 7f9f497f9bb8 thread_stack 0x46000
/usr/sbin/mysqld(my_print_stacktrace(unsigned char const*, unsigned long)+0x2e) [0x5619f19d339e]
/usr/sbin/mysqld(handle_fatal_signal+0x31b) [0x5619f0d8d22b]
/lib/x86_64-linux-gnu/libpthread.so.0(+0x12730) [0x7f9f9b277730]
/lib/x86_64-linux-gnu/libc.so.6(gsignal+0x10b) [0x7f9f9a9547bb]
/lib/x86_64-linux-gnu/libc.so.6(abort+0x121) [0x7f9f9a93f535]
/usr/sbin/mysqld(+0x35624d3) [0x5619f1e914d3]
/usr/sbin/mysqld(+0x352e68b) [0x5619f1e5d68b]
/usr/sbin/mysqld(+0x352f1fc) [0x5619f1e5e1fc]
/usr/sbin/mysqld(trx_purge(unsigned long, unsigned long, bool)+0xbb) [0x5619f1e5e74b]
/usr/sbin/mysqld(srv_purge_coordinator_thread()+0x57a) [0x5619f1e2c7ba]
/usr/sbin/mysqld(std::thread::_State_impl<std::thread::_Invoker<std::tuple<Runnable, void (*)()> > >::_M_run()+0xa5) [0x5619f1bb1705]
/usr/lib/x86_64-linux-gnu/libstdc++.so.6(+0xbbb2f) [0x7f9f9ad38b2f]
/lib/x86_64-linux-gnu/libpthread.so.0(+0x7fa3) [0x7f9f9b26cfa3]
/lib/x86_64-linux-gnu/libc.so.6(clone+0x3f) [0x7f9f9aa164cf]
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
错误原因分析
数据库断电的时候,有个事务没做完就断电了,正常情况下再次启动数据库的时候,会进行事务恢复,但是有个表的 page 坏了,重做事务的时候做不了。
官方的意思:大概是没救了!只能导出数据重新安装,问题是已经无法正常启动了,MySQLDump无法备份;
解决方法
编辑MySQL的配置文件并在配置文件中的[mysqld]中添加:innodb_force_recovery 参数以只读方式启动,调整整个InnoDB存储引擎的恢复状况,innodb_force_recovery参数的值从 1-6 依次尝试,恢复等级越来越强。
innodb_force_recovery参数说明
innodb_force_recovery影响整个InnoDB存储引擎的恢复状况,默认值为0,表示当需要恢复时执行所有的恢复操作!!
当不能进行有效的恢复操作时,Mysql有可能无法启动,并记录下错误日志。
innodb_force_recovery可以设置为1-6,大的数字包含前面所有数字的影响。
当该参数的数值设置大于0后,可以对表进行select,create,drop操作,但insert,update或者delete这类操作是不允许的。
innodb_force_recovery=0 表示当需要恢复时执行所有的恢复操作;
innodb_force_recovery=1 表示忽略检查到的corrupt页;
innodb_force_recovery=2 表示阻止主线程的运行,如主线程需要执行full purge操作,会导致crash;
innodb_force_recovery=3 表示不执行事务回滚操作;
innodb_force_recovery=4 表示不执行插入缓冲的合并操作;
innodb_force_recovery=5 表示不查看重做日志,InnoDB存储引擎会将未提交的事务视为已提交;
innodb_force_recovery=6 表示不执行前滚的操作,强制重启!
在主从库出现这种情况时,如果配置文件里之前就有这个参数,则尝试将该参数值修改为0或6,依次尝试重启。
问题解决操作步骤
编辑MySQL的配置文件添加innodb_force_recovery参数
直接安装的Mysql添加参数
找到Mysql的配置文件,一般都在/etc/mysql/my.cnf
vi /etc/mysql/my.cnf
编辑MySQL的配置文件并在配置文件中的[mysqld]中添加:innodb_force_recovery = 6,添加好后保存启动数据库即可。
Docker安装的Mysql添加参数
Docker安装的Mysql如果启动失败是无法直接进入容器内部修改的,如果在之前已经将Mysql配置文件挂载到宿主机直接修改即可,如果没有挂载需要先把容器内配置文件拷贝到宿主机修改好在拷贝到容器内。
PS: 容器在重启状态可以对容器内部文件做操作
1、从容器复制到主机
docker cp mysql:/etc/mysql/my.cnf /home/my.cnf
2、修改配置文件
编辑MySQL的配置文件并在配置文件中的[mysqld]中添加:innodb_force_recovery = 6
vi /home/my.cnf
3、从主机复制到容器
docker cp /home/my.cnf mysql:/etc/mysql/my.cnf
4、重启容器
docker restart mysql
导出需要的数据库
# 备份数据库为sql文件到指定文件夹下
mysqldump -h127.0.0.1 -uroot -p密码 test > /home/test.sql;
重新安装数据库
如果要重装到同一台服务器,切记一定要先验证备份的数据库是否可用!!!
导入备份的数据库
导入前需要先创建数据库
mysql -h127.0.0.1 -uroot -p密码 test < test.sql