0
0

MySQL在生产环境出现无法启动的问题解决

MySQL在生产环境出现无法启动的问题解决

1、事由

今天现服务器重启了，然后服务器启动完成之后我发现为啥后端程序没有启动，排查之后发现是MySQL没有启动连接不上数据库，错误信息如下：

2024-04-16T02:34:01.507696Z 0 [Warning] [MY-000081] [Server] option ＇max_allowed_packet＇: unsigned value 107374182400 adjusted to 1073741824.
2024-04-16T02:34:01.507749Z 0 [Warning] [MY-011070] [Server] ＇binlog_format＇ is deprecated and will be removed in a future release.
2024-04-16T02:34:01.507821Z 0 [Warning] [MY-010915] [Server] ＇NO_ZERO_DATE＇, ＇NO_ZERO_IN_DATE＇ and ＇ERROR_FOR_DIVISION_BY_ZERO＇ sql modes should be used with strict mode. They will be merged with strict mode in a future release.
2024-04-16T02:34:01.507923Z 0 [Warning] [MY-010918] [Server] ＇default_authentication_plugin＇ is deprecated and will be removed in a future release. Please use authentication_policy instead.
2024-04-16T02:34:01.507955Z 0 [System] [MY-010116] [Server] /www/server/mysql/bin/mysqld (mysqld 8.0.36) starting as process 7075
2024-04-16T02:34:01.524054Z 0 [Warning] [MY-013907] [InnoDB] Deprecated configuration parameters innodb_log_file_size and/or innodb_log_files_in_group have been used to compute innodb_redo_log_capacity=1073741824. Please use innodb_redo_log_capacity instead.
2024-04-16T02:34:01.526764Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2024-04-16T02:34:02.677451Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2024-04-16T02:34:02.975637Z 0 [ERROR] [MY-013183] [InnoDB] Assertion failure: trx0types.h:541:m_rsegs_n ＜ 2 thread 47034609473280
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.
2024-04-16T02:34:02Z UTC - mysqld got signal 6 ;
Most likely, you have hit a bug, but this error can also be caused by malfunctioning hardware.
BuildID[sha1]=911887188a59108d0b2707ced3fa0b5872644b4f
Thread pointer: 0x2ac79c0008c0
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 = 2ac7193089e8 thread_stack 0x100000
/www/server/mysql/bin/mysqld(my_print_stacktrace(unsigned char const*, unsigned long)+0x2e) [0x1fe320e]
/www/server/mysql/bin/mysqld(print_fatal_signal(int)+0x3a3) [0x107e883]
/www/server/mysql/bin/mysqld(my_server_abort()+0x5e) [0x107e98e]
/www/server/mysql/bin/mysqld(my_abort()+0xa) [0x1fdd96a]
/www/server/mysql/bin/mysqld(ut_dbg_assertion_failed(char const*, char const*, unsigned long)+0x30c) [0x22259ec]
/www/server/mysql/bin/mysqld(TrxUndoRsegsIterator::set_next()+0x5f1) [0x21e6dd1]
/www/server/mysql/bin/mysqld() [0x21e6e5f]
/www/server/mysql/bin/mysqld() [0x21e7dc8]
/www/server/mysql/bin/mysqld(trx_purge(unsigned long, unsigned long, bool)+0x13d) [0x21eabdd]
/www/server/mysql/bin/mysqld(srv_purge_coordinator_thread()+0xb72) [0x21c3882]
/www/server/mysql/bin/mysqld(std::thread::_State_impl＜std::thread::_Invoker＜std::tuple＜Detached_thread, void (*)()＞ ＞ ＞::_M_run()+0xb4) [0x20e3374]
/www/server/mysql/bin/mysqld() [0x280f0cf]
/lib64/libpthread.so.0(+0x7ea5) [0x2ac6e6256ea5]
/lib64/libc.so.6(clone+0x6d) [0x2ac6e7d0eb0d]

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (0): is an invalid pointer
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.

百度搜了一下之后说可能是执行的SQL有问题，然后我就想看下MySQL的BinLog日志，但是在服务器执行的时候发现：
在这里插入图片描述

但是我明明是有mysql的，mysql和mysqlbinlog正常来说都是在一起的，一番查找之后原因是：我用了宝塔，宝塔的mysqlbinlog日志地址是

/www/server/mysql/bin

这里面的

2、分析binlog日志

./mysqlbinlog --start-datetime=“2024-04-16 02:00:00” /home/mysql/mysql-bin.000007

大概看了binlog日志，发现最后几条的sql并没有问题，然后我就觉得大概率是因为服务器是突然断点重启的，但是此时正在执行sql，造成innodb数据库文件出现错误的原因

（已脱敏数据）

#240416  2:00:56 server id 1  end_log_pos 651706988 CRC32 0xecb191f3     Xid = 25647715
COMMIT/*!*/;
# at 651706988
#240416  2:00:56 server id 1  end_log_pos 651707067 CRC32 0x8d36daa2     Anonymous_GTID    last_committed=634356    sequence_number=634357    rbr_only=no    original_committed_timestamp=1713204056015478    immediate_commit_timestamp=1713204056015478    transaction_length=934
# original_commit_timestamp=1713204056015478 (2024-04-16 02:00:56.015478 CST)
# immediate_commit_timestamp=1713204056015478 (2024-04-16 02:00:56.015478 CST)
/*!80001 SET @@session.original_commit_timestamp=1713204056015478*//*!*/;
/*!80014 SET @@session.original_server_version=80036*//*!*/;
/*!80014 SET @@session.immediate_server_version=80036*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 651707067
#240416  2:00:56 server id 1  end_log_pos 651707162 CRC32 0x156f48b9     Query    thread_id=2911    exec_time=0    error_code=0
SET TIMESTAMP=1713204056/*!*/;
BEGIN
/*!*/;
# at 651707162
#240416  2:00:56 server id 1  end_log_pos 651707435 CRC32 0x9dd48b58     Query    thread_id=2911    exec_time=0    error_code=0
SET TIMESTAMP=1713204056/*!*/;
UPDATE QRTZ_TRIGGERS SET TRIGGER_STATE = 'ACQUIRED' WHERE SCHED_NAME = 'schedulerName' AND TRIGGER_NAME AND TRIGGTE = 'WAITING'
/*!*/;
# at 651707435
#240416  2:00:56 server id 1  end_log_pos 651707891 CRC32 0x35e6afbf     Query    thread_id=2911    exec_time=0    error_code=0
SET TIMESTAMP=1713204056/*!*/;
INSERT INTO QRTZ_FIRED_TRIGGERS (SCHED_NAME, ENTRY_ID, TRIGGER_NAME, TRIGGER_GROUP, INSTANCE_NAME, FIRED_TIME 5)
/*!*/;
# at 651707891
#240416  2:00:56 server id 1  end_log_pos 651707922 CRC32 0x451c13c4     Xid = 25647718
COMMIT/*!*/;
# at 651707922
#240416  2:00:56 server id 1  end_log_pos 651708001 CRC32 0xae4efe8c     Anonymous_GTID    last_committed=634357    sequence_number=634358    rbr_only=no    original_committed_timestamp=1713204056017004    immediate_commit_timestamp=1713204056017004    transaction_length=548
# original_commit_timestamp=1713204056017004 (2024-04-16 02:00:56.017004 CST)
# immediate_commit_timestamp=1713204056017004 (2024-04-16 02:00:56.017004 CST)
/*!80001 SET @@session.original_commit_timestamp=1713204056017004*//*!*/;
/*!80014 SET @@session.original_server_version=80036*//*!*/;
/*!80014 SET @@session.immediate_server_version=80036*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 651708001
#240416  2:00:56 server id 1  end_log_pos 651708118 CRC32 0x1f8b6580     Query    thread_id=2882    exec_time=0    error_code=0
SET TIMESTAMP=1713204056/*!*/;
BEGIN
/*!*/;
# at 651708118
#240416  2:00:56 server id 1  end_log_pos 651708439 CRC32 0xb4b71846     Query    thread_id=2882    exec_time=0    error_code=0
SET TIMESTAMP=1713204056/*!*/;
UPDATE infra_job_log  SET end_time='2024-04-16 02:00:56.015004', duration=5, status=1, result='执行支付通知 0 个',  update_time='2024-04-16 02:00:56.015661',  updater=null  WHERE id=383950 AND deleted=0
/*!*/;
# at 651708439
#240416  2:00:56 server id 1  end_log_pos 651708470 CRC32 0x93c3d766     Xid = 25647730
COMMIT/*!*/;
# at 651708470
#240416  2:00:56 server id 1  end_log_pos 651708549 CRC32 0x75b1af91     Anonymous_GTID    last_committed=634358    sequence_number=634359    rbr_only=no    original_committed_timestamp=1713204056020270    immediate_commit_timestamp=1713204056020270    transaction_length=938
# original_commit_timestamp=1713204056020270 (2024-04-16 02:00:56.020270 CST)
# immediate_commit_timestamp=1713204056020270 (2024-04-16 02:00:56.020270 CST)
/*!80001 SET @@session.original_commit_timestamp=1713204056020270*//*!*/;
/*!80014 SET @@session.original_server_version=80036*//*!*/;
/*!80014 SET @@session.immediate_server_version=80036*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 651708549
#240416  2:00:56 server id 1  end_log_pos 651708644 CRC32 0x82977794     Query    thread_id=2915    exec_time=0    error_code=0
SET TIMESTAMP=1713204056/*!*/;
BEGIN
/*!*/;
# at 651708644
#240416  2:00:56 server id 1  end_log_pos 651708905 CRC32 0xa05db17c     Query    thread_id=2915    exec_time=0    error_code=0
SET TIMESTAMP=1713204056/*!*/;
UPDATE QRTZ_TRIGGERS SET TRIG'BLOCKED'
/*!*/;
# at 651708905
#240416  2:00:56 server id 1  end_log_pos 651709172 CRC32 0xf8de225b     Query    thread_id=2915    exec_time=0    error_code=0
SET TIMESTAMP=1713204056/*!*/;
UPDATE QRTZ_TRIGGERSE = 'PAUSED_BLOCKED'
/*!*/;
# at 651709172
#240416  2:00:56 server id 1  end_log_pos 651709377 CRC32 0xb4ef1017     Query    thread_id=2915    exec_time=0    error_code=0
SET TIMESTAMP=1713204056/*!*/;
DELETE FROM QRTZ_FIRED_TRIGGERS WHERE SCHED_NAME = 'schedulerName' AND 
/*!*/;
# at 651709377
#240416  2:00:56 server id 1  end_log_pos 651709408 CRC32 0xfc3cd382     Xid = 25647729
COMMIT/*!*/;
# at 651709408
#240416  2:00:56 server id 1  end_log_pos 651709487 CRC32 0x90969279     Anonymous_GTID    last_committed=634359    sequence_number=634360    rbr_only=no    original_committed_timestamp=1713204056026583    immediate_commit_timestamp=1713204056026583    transaction_length=955
# original_commit_timestamp=1713204056026583 (2024-04-16 02:00:56.026583 CST)
# immediate_commit_timestamp=1713204056026583 (2024-04-16 02:00:56.026583 CST)
/*!80001 SET @@session.original_commit_timestamp=1713204056026583*//*!*/;
/*!80014 SET @@session.original_server_version=80036*//*!*/;
/*!80014 SET @@session.immediate_server_version=80036*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 651709487
#240416  2:00:56 server id 1  end_log_pos 651709582 CRC32 0x954d1385     Query    thread_id=2882    exec_time=0    error_code=0
SET TIMESTAMP=1713204056/*!*/;
BEGIN
/*!*/;
# at 651709582
#240416  2:00:56 server id 1  end_log_pos 651709855 CRC32 0x4443046e     Query    thread_id=2882    exec_time=0    error_code=0
SET TIMESTAMP=1713204056/*!*/;
UPDATE QRTZ_TRIGGERS SET TRIGGER_STATE = 'WAITISTATE = 'ACQUIRED'
/*!*/;
# at 651709855
#240416  2:00:56 server id 1  end_log_pos 651710127 CRC32 0x2a9b0ef0     Query    thread_id=2882    exec_time=0    error_code=0
SET TIMESTAMP=1713204056/*!*/;
UPDATE QRTZ_TRIGGERS 
/*!*/;
# at 651710127
#240416  2:00:56 server id 1  end_log_pos 651710332 CRC32 0xd8fea345     Query    thread_id=2882    exec_time=0    error_code=0
SET TIMESTAMP=1713204056/*!*/;
DELETE FROM QRTZ_FIRED_TR
/*!*/;
# at 651710332
#240416  2:00:56 server id 1  end_log_pos 651710363 CRC32 0xc4b82dea     Xid = 25647738
COMMIT/*!*/;
# at 651710363
#240416  2:00:56 server id 1  end_log_pos 651710442 CRC32 0x1990f6bf     Anonymous_GTID    last_committed=634360    sequence_number=634361    rbr_only=no    original_committed_timestamp=1713204056032340    immediate_commit_timestamp=1713204056032340    transaction_length=928
# original_commit_timestamp=1713204056032340 (2024-04-16 02:00:56.032340 CST)
# immediate_commit_timestamp=1713204056032340 (2024-04-16 02:00:56.032340 CST)
/*!80001 SET @@session.original_commit_timestamp=1713204056032340*//*!*/;
/*!80014 SET @@session.original_server_version=80036*//*!*/;
/*!80014 SET @@session.immediate_server_version=80036*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 651710442
#240416  2:00:56 server id 1  end_log_pos 651710537 CRC32 0x15fddec2     Query    thread_id=2882    exec_time=0    error_code=0
SET TIMESTAMP=1713204056/*!*/;
BEGIN
/*!*/;
# at 651710537
#240416  2:00:56 server id 1  end_log_pos 651710807 CRC32 0x87d15abb     Query    thread_id=2882    exec_time=0    error_code=0
SET TIMESTAMP=1713204056/*!*/;
UPDATE QRTZ_TRIGGERS SET TRSTATE = 'WAITING'
/*!*/;
# at 651710807
#240416  2:00:56 server id 1  end_log_pos 651711260 CRC32 0xb988e5b0     Query    thread_id=2882    exec_time=0    error_code=0
SET TIMESTAMP=1713204056/*!*/;
INSERT INTO
/*!*/;
# at 651711260
#240416  2:00:56 server id 1  end_log_pos 651711291 CRC32 0x889caa0e     Xid = 25647746
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

3、数据恢复

既然原因大概就是这样，我们现在得解决数据恢复的问题，既然是innodb文件的问题，那这个库我的想法就是别用了，导出原有数据库的文件，然后新建一个库，毕竟Innodb的文件我也实在不知道怎么修复，但是现在由于mysql启动不了无法导出数据，按照错误信息的提示，我们在mysql的配置文件中加入以下内容：

# 强制启用恢复模式
innodb_force_recovery = 1

其中参数的含义是：

  • 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 设置为只读。

建议从1开始逐步往上尝试启动，我是尝试到4之后才启动成功的。

启动成功之后我们此时不要就这样用了！因为我们使用的是恢复模式运行的，顾名思义就是用来恢复数据的！

启动成功后我们在使用dump导出一下数据库的结构和数据(我用的是宝塔就直接点击备份数据了)

在这里插入图片描述

备份成功之后我们就可以删除原来的数据库了，因为原来数据库的innodb文件已经损坏了，使用宝塔也删不掉这个数据库，我就想直接删除MySQL的数据文件

4、删除原来数据库

由于使用的是恢复模式，而且innodb的文件已经损坏了，所以我们可以找到mysql配置文件，找到对应的数据文件，然后使用rm -rf 文件删除掉原有的数据库文件。

数据无价！！！

确保已备份全部数据！！！！！！！！

# 删除宝塔的MySQL数据rm-rf /www/server/mysql

删除完之后，我们重新安装数据库，接着把直接备份的数据库重新导入，MySQL就可以正常启动了。

版权所有：XuanRan

未经书面授权，禁止转载！

标签： mysql 数据库
本文转载自: https://blog.csdn.net/qq_37945670/article/details/137822464
版权归原作者 XuanRanDev 所有， 如有侵权，请联系我们删除。
登录后发布评论

“MySQL在生产环境出现无法启动的问题解决”的评论:

还没有评论
关于作者
...
overfit同步小助手
文章同步
相关阅读

linux的软链接详解

如何在Windows系统上，下载并配置macOS 10.14虚拟机

一款完全开源并免费的监测与分析系统，支持监测，预警，分析，报告，支持本地化部署（附源码）

Git学习-1

毕设开源 python大数据旅游数据分析可视化系统(源码分享)

Kafka 快速入门（一）

基于大数据的电信诈骗行为可视化系统含预测研究【lightGBM，XGBoost，随机森林】

文章导航