MySQL 無法啟動,錯誤日志顯示:
InnoDB: Initializing buffer pool, size = 128.0M
InnoDB: mmap(137363456 bytes) failed; errno 12
InnoDB: Fatal error: cannot allocate memory for the buffer pool
InnoDB: Assertion failure in thread ... in file ut0mem.c line 103
InnoDB: Failing assertion: ret || !assert_on_error
核心錯誤:errno 12 = ENOMEM(內存不足),InnoDB 無法分配 Buffer Pool 內存觸發斷言失敗。
| 可能原因 | 排查命令 | 解決方向 |
|---|---|---|
| 系統物理內存耗盡 | free -h、top | 釋放內存/增加內存/調小 buffer_pool |
| Swap 耗盡/未配置 | swapon -s、free -h | 擴充 Swap/優化內存使用 |
| 內核參數限制 | sysctl -a | grep -E 'vm.overcommit|vm.max_map_count' | 調整 overcommit_memory |
| CGroup 內存限制 (容器環境) | cat /sys/fs/cgroup/memory/memory.limit_in_bytes | 調整容器內存限制 |
| 大頁內存配置失敗 | cat /proc/meminfo | grep Huge | 禁用大頁或正確配置 |
# 查看內存佔用大戶
ps aux --sort=-%mem | head -20
# 停止非核心服務
systemctl stop nginx redis php-fpm # 視業務情況
# 清理緩存(臨時緩解)
sync && echo 3 > /proc/sys/vm/drop_caches
# 重啟 MySQL 服務
systemctl start mysqld
# 編輯配置文件
vim /etc/my.cnf
[mysqld]
# 原配置可能過大
# innodb_buffer_pool_size = 2G
# 根據實際可用內存調整(建議佔物理內存 60-70%)
innodb_buffer_pool_size = 1G
innodb_buffer_pool_chunk_size = 128M
innodb_buffer_pool_instances = 8
# 檢查當前 swap
swapon -s
free -h
# 臨時創建 4GB swap 文件
dd if=/dev/zero of=/swapfile bs=1M count=4096
chmod 600 /swapfile
mkswap /swapfile
swapon /swapfile
# 永久生效
echo '/swapfile none swap sw 0 0' >> /etc/fstab
# 調整 swappiness(數據庫建議 1-10)
echo 'vm.swappiness = 1' >> /etc/sysctl.conf
sysctl -p
# /etc/sysctl.conf
# 允許內存過度提交(防止 malloc 失敗)
vm.overcommit_memory = 1
vm.overcommit_ratio = 50
# 增加內存映射區域限制
vm.max_map_count = 262144
# 禁用透明大頁(數據庫建議)
echo never > /sys/kernel/mm/transparent_hugepage/enabled
echo 'echo never > /sys/kernel/mm/transparent_hugepage/enabled' >> /etc/rc.local
sysctl -p
若上述方法無效,數據文件可能已損壞,嘗試 innodb_force_recovery:
[mysqld]
innodb_force_recovery = 1 # 從 1 開始嘗試,最大 6
| 級別 | 名稱 | 風險 | 操作限制 |
|---|---|---|---|
| 1 | SRV_FORCE_IGNORE_CORRUPT | 低 | 忽略損壞頁,可 SELECT |
| 2 | SRV_FORCE_NO_BACKGROUND | 低 | 停止後台線程,可 SELECT |
| 3 | SRV_FORCE_NO_TRX_UNDO | 中 | 不執行回滾,唯讀 |
| 4 | SRV_FORCE_NO_IBUF_MERGE | 中 | 不合併插入緩衝,唯讀 |
| 5 | SRV_FORCE_NO_UNDO_LOG_SCAN | 高 | 不掃描 undo log,唯讀 |
| 6 | SRV_FORCE_NO_LOG_REDO | 極高 | 不執行 redo log,唯讀,可能丟數據 |
# 1. 添加 force_recovery=3 並重啟
systemctl restart mysqld
# 2. 導出所有數據
mysqldump -u root -p --all-databases --single-transaction --routines --triggers --events > /backup/recovery_$(date +%F).sql
# 3. 移除 force_recovery,重新初始化數據目錄
systemctl stop mysqld
rm -rf /data/mysql/3306/data/*
# 修改 my.cnf 移除 force_recovery
systemctl start mysqld
/usr/local/mysql/bin/mysqld --initialize-insecure ...
# 4. 導入數據
mysql -u root -p < /backup/recovery_$(date +%F).sql
# Prometheus + node_exporter 監控指標
# 設置告警規則:
- alert: MySQLMemoryPressure
expr: (node_memory_MemTotal_bytes - node_memory_MemAvailable_bytes) / node_memory_MemTotal_bytes > 0.85
for: 5m
labels:
severity: warning
annotations:
summary: "MySQL 服務器內存使用率超過 85%"
- alert: MySQLBufferPoolConfigExceedsMemory
expr: mysql_global_variables_innodb_buffer_pool_size_bytes > node_memory_MemTotal_bytes * 0.8
labels:
severity: critical