小亦平台會持續給大家科普一些運維過程中常見的問題解決案例，運維朋友們可以在往期文章中查看更多案例
問題概述
- 客户反饋數倉MySQL主節點在 8:12 分左右異常重啓。
- MySQL錯誤日誌中顯示以下關鍵警告：
2022-10-18T00:18:40.242196Z 0 [Warning] Changed limits: max_open_files: 5000 (requested 5106)
2022-10-18T00:18:40.242350Z 0 [Warning] Changed limits: table_open_cache: 1995 (requested 2048)- 錯誤日誌中還存在如下的報錯信息，既SYS_DATAFILES 表中存在多張表文件無法打開的報錯（示例）：
2022-10-18T00:23:11.068767Z 0 [ERROR] InnoDB: Cannot open datafile for read-only: './htds_tmp/ads_cdebt_user_call_action_ld.ibd' OS error: 71
2022-10-18T00:23:11.084399Z 0 [ERROR] InnoDB: Cannot open datafile for read-only: './htds_tmp/ads_fgs_group_avg_check_pay_ld.ibd' OS error: 71
2022-10-18T00:23:11.086658Z 0 [ERROR] InnoDB: Cannot open datafile for read-only: './htds_tmp/ads_fgs_month_payment_rate_ld.ibd' OS error: 71
2022-10-18T00:23:11.088429Z 0 [ERROR] InnoDB: Cannot open datafile for read-only: './htds_tmp/ads_fgs_user_month_payment_ld.ibd' OS error: 71
2022-10-18T00:23:11.089924Z 0 [ERROR] InnoDB: Cannot open datafile for read-only: './htds_tmp/ads_fgskb_avg_pay_ld.ibd' OS error: 71
2022-10-18T00:23:11.102771Z 0 [ERROR] InnoDB: Cannot open datafile for read-only: './htds_tmp/ads_user_app_d_ld.ibd' OS error: 71
2022-10-18T00:23:11.105748Z 0 [ERROR] InnoDB: Cannot open datafile for read-only: './htds_tmp/ads_user_message_d_ld.ibd' OS error: 71
2022-10-18T00:23:11.108828Z 0 [ERROR] InnoDB: Cannot open datafile for read-only: './htds_tmp/ads_user_sms_d_ld.ibd' OS error: 71
2022-10-18T00:23:11.120084Z 0 [ERROR] InnoDB: Cannot open datafile for read-only: './htds_tmp/dds_ucp_collector_info.ibd' OS error: 71
2022-10-18T00:23:11.127838Z 0 [ERROR] InnoDB: Cannot open datafile for read-only: './htds_tmp/dws_acct_capital_a_ld.ibd' OS error: 71
2022-10-18T00:23:11.130021Z 0 [ERROR] InnoDB: Cannot open datafile for read-only: './htds_tmp/dws_acct_month_a_ld.ibd' OS error: 71
2022-10-18T00:23:11.132180Z 0 [ERROR] InnoDB: Cannot open datafile for read-only: './htds_tmp/dws_acct_month_d_ld.ibd' OS error: 71
2022-10-18T00:23:11.134604Z 0 [ERROR] InnoDB: Cannot open datafile for read-only: './htds_tmp/dws_acct_settle_odue_d_ld.ibd' OS error: 71
2022-10-18T00:23:11.136651Z 0 [ERROR] InnoDB: Cannot open datafile for read-only: './htds_tmp/dws_cust_capital_a_ld.ibd' OS error: 71
2022-10-18T00:23:11.138114Z 0 [ERROR] InnoDB: Cannot open datafile for read-only: './htds_tmp/dws_cust_case_d_ld.ibd' OS error: 71
2022-10-18T00:23:11.140843Z 0 [ERROR] InnoDB: Cannot open datafile for read-only: './htds_tmp/dws_cust_channel_type_user_month_a_ld.ibd' OS error: 71
2022-10-18T00:23:11.142936Z 0 [ERROR] InnoDB: Cannot open datafile for read-only: './htds_tmp/dws_cust_month_a_ld.ibd' OS error: 71
2022-10-18T00:23:11.144918Z 0 [ERROR] InnoDB: Cannot open datafile for read-only: './htds_tmp/dws_cust_month_d_ld.ibd' OS error: 71
2022-10-18T00:23:11.146228Z 0 [ERROR] InnoDB: Cannot open datafile for read-only: './htds_tmp/dws_cust_product_team_task_flag_ld.ibd' OS error: 71問題分析
- MySQL崩潰直接原因
- 通過分析錯誤日誌，確認崩潰發生在執行 Change Buffer 合併操作時觸發了已知 Bug（https://bugs.mysql.com/bug.php?id=104850）。
- 該 Bug 在 MySQL 5.7.31 版本修復（https://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-31.html）。
- 資源限制問題
- 操作系統和 MySQL 的 max_open_files 設置過低（當前 5000，實際需 5106），導致文件句柄不足。
3. 表文件缺失問題
- 錯誤日誌顯示多個表（如 ads_cdebt_user_call_action_ld.ibd）在數據字典中存在，但物理文件無法訪問（OS error 71），路徑為 /data/mysql/htds_tmp。
解決方案
- 升級 MySQL 版本
- 升級到 5.7.31 或更高版本修復 Bug。
- 操作步驟：先在測試環境升級驗證，確認無問題後再升級生產環境。
- 調整系統與 MySQL 資源限制
- 修改操作系統的文件打開數限制，編輯 /etc/security/limits.conf，設置為 65535。
- 修改 MySQL 服務配置，在 /usr/lib/systemd/system/mysqld.service 中設置 LimitNOFILE=65535。
- 最後優化 MySQL 參數配置
- 在 my.cnf 中調整以下參數：
table_open_cache = 8192
table_definition_cache >= 當前數據庫中表的數量 # 必須滿足此條件
open_files_limit = 65535
binlog_format = row # 從 mixed 改為 row- 檢查缺失的表文件
驗證路徑 /data/mysql/htds_tmp 下報錯的表文件（如 ads_cdebt_user_call_action_ld.ibd）是否存在，並修復文件丟失問題。
立即查看更多mysql的相關內容：https://www.ces-xiaoyi.com.cn/#/welcome/knowledge/panel/detai...
運維工作中遇到難題？立即提交工單：https://www.ces-xiaoyi.com.cn/#/workOrder?marketing_code=arti... 小亦平台工程師火速響應，助您快速修復故障!
