MySQL在生產(chǎn)環(huán)境出現(xiàn)無法啟動的問題解決
更新時間：2024年10月28日 11:41:41 作者：XuanRanDev
在當(dāng)今的數(shù)據(jù)驅(qū)動世界中,MySQL作為廣泛應(yīng)用的關(guān)系型數(shù)據(jù)庫管理系統(tǒng),在眾多生產(chǎn)環(huán)境中承擔(dān)著至關(guān)重要的角色,然而,面對復(fù)雜多變的業(yè)務(wù)場景,MySQL可能會遭遇各類故障和性能瓶頸,本文將深入探討MySQL在生產(chǎn)環(huán)境出現(xiàn)無法啟動的問題解決,需要的朋友可以參考下
MySQL在生產(chǎn)環(huán)境出現(xiàn)無法啟動的問題解決
1、事由
今天現(xiàn)服務(wù)器重啟了，然后服務(wù)器啟動完成之后我發(fā)現(xiàn)為啥后端程序沒有啟動，排查之后發(fā)現(xiàn)是MySQL沒有啟動連接不上數(shù)據(jù)庫，錯誤信息如下：
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.
百度搜了一下之后說可能是執(zhí)行的SQL有問題，然后我就想看下MySQL的BinLog日志，但是在服務(wù)器執(zhí)行的時候發(fā)現(xiàn)：
但是我明明是有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日志，發(fā)現(xiàn)最后幾條的sql并沒有問題，然后我就覺得大概率是因?yàn)榉?wù)器是突然斷點(diǎn)重啟的，但是此時正在執(zhí)行sql，造成innodb數(shù)據(jù)庫文件出現(xiàn)錯誤的原因
（已脫敏數(shù)據(jù)）
#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='執(zhí)行支付通知 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、數(shù)據(jù)恢復(fù)
既然原因大概就是這樣，我們現(xiàn)在得解決數(shù)據(jù)恢復(fù)的問題，既然是innodb文件的問題，那這個庫我的想法就是別用了，導(dǎo)出原有數(shù)據(jù)庫的文件，然后新建一個庫，畢竟Innodb的文件我也實(shí)在不知道怎么修復(fù)，但是現(xiàn)在由于mysql啟動不了無法導(dǎo)出數(shù)據(jù)，按照錯誤信息的提示，我們在mysql的配置文件中加入以下內(nèi)容：
# 強(qiáng)制啟用恢復(fù)模式 innodb_force_recovery = 1
其中參數(shù)的含義是：
1 （SRV_FORCE_IGNORE_CORRUPT)
即使檢測到損壞的頁面，也允許服務(wù)器運(yùn)行。嘗試使 SELECT * FROM *tbl_name*跳過損壞的索引記錄和頁面，這有助于轉(zhuǎn)儲表。
2 （SRV_FORCE_NO_BACKGROUND)
阻止主線程和任何清除線程運(yùn)行。如果在清除操作期間發(fā)生意外退出，則此恢復(fù)值會阻止它。
3 （SRV_FORCE_NO_TRX_UNDO)
崩潰恢復(fù)后不運(yùn)行事務(wù)回滾.
4 （SRV_FORCE_NO_IBUF_MERGE)
阻止插入緩沖區(qū)合并操作。如果它們會導(dǎo)致崩潰，請不要這樣做。不計(jì)算表統(tǒng)計(jì)信息。此值可能會永久損壞數(shù)據(jù)文件。使用此值后，請準(zhǔn)備好刪除并重新創(chuàng)建所有二級索引。將 InnoDB 設(shè)置為只讀。
5 （SRV_FORCE_NO_UNDO_LOG_SCAN)
啟動數(shù)據(jù)庫時不查看撤消日志：InnoDB 甚至將未完成的事務(wù)視為已提交。此值可能會永久損壞數(shù)據(jù)文件。將 InnoDB 設(shè)置為只讀。
6（SRV_FORCE_NO_LOG_REDO)
不執(zhí)行與恢復(fù)相關(guān)的重做日志前滾。此值可能會永久損壞數(shù)據(jù)文件。使數(shù)據(jù)庫頁處于過時狀態(tài)，這反過來又可能會給 B 樹和其他數(shù)據(jù)庫結(jié)構(gòu)帶來更多損壞。將 InnoDB 設(shè)置為只讀。
建議從1開始逐步往上嘗試啟動，我是嘗試到4之后才啟動成功的。
啟動成功之后我們此時不要就這樣用了！因?yàn)槲覀兪褂玫氖腔謴?fù)模式運(yùn)行的，顧名思義就是用來恢復(fù)數(shù)據(jù)的！
啟動成功后我們在使用dump導(dǎo)出一下數(shù)據(jù)庫的結(jié)構(gòu)和數(shù)據(jù)(我用的是寶塔就直接點(diǎn)擊備份數(shù)據(jù)了)
備份成功之后我們就可以刪除原來的數(shù)據(jù)庫了，因?yàn)樵瓉頂?shù)據(jù)庫的innodb文件已經(jīng)損壞了，使用寶塔也刪不掉這個數(shù)據(jù)庫，我就想直接刪除MySQL的數(shù)據(jù)文件
4、刪除原來數(shù)據(jù)庫
由于使用的是恢復(fù)模式，而且innodb的文件已經(jīng)損壞了，所以我們可以找到mysql配置文件，找到對應(yīng)的數(shù)據(jù)文件，然后使用rm -rf 文件刪除掉原有的數(shù)據(jù)庫文件。
數(shù)據(jù)無價?。?！
確保已備份全部數(shù)據(jù)?。。。。。。?！
# 刪除寶塔的MySQL數(shù)據(jù) rm -rf /www/server/mysql
刪除完之后，我們重新安裝數(shù)據(jù)庫，接著把直接備份的數(shù)據(jù)庫重新導(dǎo)入，MySQL就可以正常啟動了。
以上就是MySQL在生產(chǎn)環(huán)境出現(xiàn)無法啟動的問題解決的詳細(xì)內(nèi)容，更多關(guān)于MySQL生產(chǎn)環(huán)境無法啟動的資料請關(guān)注腳本之家其它相關(guān)文章！
相關(guān)文章
MySQL學(xué)習(xí)必備條件查詢數(shù)據(jù)
這篇文章主要介紹了MySQL學(xué)習(xí)必備條件查詢數(shù)據(jù)，首先通過利用where語句可以對數(shù)據(jù)進(jìn)行篩選展開主題相關(guān)內(nèi)容，具有一定的參考價值，需要的小伙伴可以參考一下，希望對你有所幫助2022-03-03
這篇文章主要介紹了MySQL 函數(shù)索引及優(yōu)化方案的相關(guān)資料，幫助大家更好的理解和學(xué)習(xí)MySQL，感興趣的朋友可以了解下2020-09-09
這篇文章主要介紹了MySQL 大表添加一列的實(shí)現(xiàn)，文中通過示例代碼介紹的非常詳細(xì)，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2021-02-02
今天在寫sql語句的時候，想確認(rèn)下mysql的不等于運(yùn)算符是用什么符號表示的2013-08-08
這篇文章主要給大家介紹了關(guān)于MySQL如何優(yōu)雅的刪除大表的相關(guān)資料，文中通過示例代碼介紹的非常詳細(xì)，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2020-12-12
MySQL的point函數(shù)是一個用于處理空間坐標(biāo)系的函數(shù)，它可以將兩個數(shù)值作為參數(shù)，返回一個Point對象，這篇文章主要介紹了mysql中point的使用,需要的朋友可以參考下2023-07-07
本文介紹了在Hive中進(jìn)行行轉(zhuǎn)列的幾種方法,包括使用CASE?WHEN/IF、Get_Json_Object、Str_To_Map以及UNION?ALL和EXPLODE函數(shù),每種方法都有其適用場景,感興趣的可以了解一下2024-12-12
這篇文章主要介紹了MySQL分頁技術(shù)、6種分頁方法總結(jié),本文總結(jié)了6種分頁的方法并分別一一講解它們的特點(diǎn),需要的朋友可以參考下2015-07-07
這篇文章主要介紹了MySql insert插入操作的3個小技巧分享,本文講解了插入的數(shù)據(jù)來源自其他表、插入時排除（忽略）重復(fù)記錄、插入時遇到重復(fù)記錄做更新操作三個小技巧,需要的朋友可以參考下2015-03-03
MySQL判斷查詢條件是否包含某字符串的7種方式總結(jié)
SQLServer數(shù)據(jù)庫死鎖是指在多個事務(wù)同時訪問數(shù)據(jù)庫資源時,發(fā)生了互相等待對方所持有資源的情況,導(dǎo)致所有事務(wù)無法繼續(xù)執(zhí)行的現(xiàn)象,這篇文章主要給大家介紹了關(guān)于MySQL判斷查詢條件是否包含某字符串的7種方式,需要的朋友可以參考下2024-07-07