我要评论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就可以正常启动了。
以上就是mysql在生产环境出现无法启动的问题解决的详细内容，更多关于mysql生产环境无法启动的资料请关注代码网其它相关文章！
发表评论