Linux 系統下 MySQL 重要目錄與密碼管理實戰指南

一、Linux MySQL 關鍵目錄速查表

二、MySQL 密碼管理全生命周期

2.1 初始化後設置 root 密碼 (MySQL 5.7+)

# 1. 啟動服務
systemctl start mysqld

# 2. 獲取臨時密碼 (5.7+ 默認生成隨機密碼寫入錯誤日志)
grep 'temporary password' /var/log/mysqld.log
# 輸出示例: 2023-01-01T00:00:00.000000Z 0 [Note] A temporary password is generated for root@localhost: xYz123!@#

# 3. 首次登錄修改密碼 (必須滿足密碼策略: 長度、大小寫、數字、特殊字符)
mysql -uroot -p'xYz123!@#' --connect-expired-password
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewStrongPass123!';
mysql> FLUSH PRIVILEGES;

2.2 常規修改密碼方式

2.3 忘記 root 密碼緊急重置 (通用方案)

1. 停止服務：systemctl stop mysqld
2. 創建臨時初始化文件 /tmp/init.sql：

   ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewTempPass123!';
   FLUSH PRIVILEGES;

3. 以跳過權限模式啟動並加載初始化文件：

   mysqld --user=mysql --init-file=/tmp/init.file --daemonize
   # 或 systemd 臨時覆蓋
   systemctl set-environment MYSQLD_OPTS="--init-file=/tmp/init.sql"
   systemctl start mysqld

4. 新密碼登錄驗證：mysql -uroot -p'NewTempPass123!'
5. 移除臨時配置，正常重啟：systemctl unset-environment MYSQLD_OPTS; systemctl restart mysqld

2.4 密碼策略與安全加固 (validate_password 組件)

-- 安裝組件 (8.0+)
INSTALL COMPONENT 'file://component_validate_password';

-- 設置策略等級: LOW/MEDIUM/STRONG
SET GLOBAL validate_password.policy = MEDIUM;
SET GLOBAL validate_password.length = 12;
SET GLOBAL validate_password.mixed_case_count = 1;
SET GLOBAL validate_password.number_count = 1;
SET GLOBAL validate_password.special_char_count = 1;

-- 禁止密碼重用
SET GLOBAL validate_password.password_history = 5;

-- 密碼過期策略
ALTER USER 'app_user'@'%' PASSWORD EXPIRE INTERVAL 90 DAY;

2.5 多用戶權限最小化授權模板

-- 唯讀賬號 (報表、備份)
CREATE USER 'readonly'@'%' IDENTIFIED BY 'StrongPass123!';
GRANT SELECT ON `mydb`.* TO 'readonly'@'%';
GRANT SELECT ON `mysql`.`help_topic` TO 'readonly'@'%';  -- help 系統表
FLUSH PRIVILEGES;

-- 應用讀寫賬號 (僅業務庫)
CREATE USER 'app_rw'@'10.0.1.%' IDENTIFIED BY 'StrongPass123!';
GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE ON `mydb`.* TO 'app_rw'@'10.0.1.%';
FLUSH PRIVILEGES;

-- 運維管理賬號 (受限管理權限)
CREATE USER 'dba_admin'@'10.0.0.10' IDENTIFIED BY 'StrongPass123!';
GRANT PROCESS, REPLICATION CLIENT, RELOAD, SHOW DATABASES, SUPER ON *.* TO 'dba_admin'@'10.0.0.10';
GRANT SELECT, INSERT, UPDATE, DELETE ON `mydb`.* TO 'dba_admin'@'10.0.0.10';
FLUSH PRIVILEGES;

三、啟動、停止、重啟、狀態檢查標準化操作

四、常見啟動失敗排查清單

1. 權限問題：chown -R mysql:mysql /var/lib/mysql /var/run/mysqld /var/log/mysqld.log
2. 端口衝突：netstat -ntlp | grep 3306，修改 port 或殺佔用進程
3. 配置語法錯誤：mysqld --validate-config 或 mysqld --help --verbose 2>&1 | head -20
4. SELinux/AppArmor：setenforce 0 臨時關閉驗證，或配置策略 semanage fcontext -a -t mysqld_db_t "/data/mysql(/.*)?"
5. 磁盤空間不足：df -h /var/lib/mysql，清理舊日志、臨時文件、擴容
6. 內存不足 OOM Killer：dmesg | grep -i kill，調整 innodb_buffer_pool_size、增加 Swap
7. PID/Socket 目錄缺失：mkdir -p /var/run/mysqld; chown mysql:mysql /var/run/mysqld

五、自動化運維腳本片段

#!/bin/bash
# mysql_health_check.sh - 定時任務每分鐘執行
MYSQL_USER="monitor"
MYSQL_PASS="MonitorPass123!"
MYSQL_HOST="127.0.0.1"
MYSQL_PORT=3306

# 1. 連通性
if ! mysql -u$MYSQL_USER -p$MYSQL_PASS -h$MYSQL_HOST -P$MYSQL_PORT -e "SELECT 1;" &>/dev/null; then
    echo "$(date) MySQL connection FAILED" | tee -a /var/log/mysql_health.log
    # 告警: 發送郵件/鉚釘/企業微信
    exit 1
fi

# 2. 主從延遲 (如果是從庫)
LAG=$(mysql -u$MYSQL_USER -p$MYSQL_PASS -h$MYSQL_HOST -P$MYSQL_PORT -sN -e "SHOW SLAVE STATUS\G" | grep "Seconds_Behind_Master" | awk '{print $2}')
if [[ "$LAG" != "NULL" && $LAG -gt 30 ]]; then
    echo "$(date) Replication lag: $LAG seconds" | tee -a /var/log/mysql_health.log
fi

# 3. 連接數使用率
USED=$(mysql -u$MYSQL_USER -p$MYSQL_PASS -h$MYSQL_HOST -P$MYSQL_PORT -sN -e "SHOW STATUS LIKE 'Threads_connected';" | awk '{print $2}')
MAX=$(mysql -u$MYSQL_USER -p$MYSQL_PASS -h$MYSQL_HOST -P$MYSQL_PORT -sN -e "SHOW VARIABLES LIKE 'max_connections';" | awk '{print $2}')
PCT=$((USED * 100 / MAX))
if [[ $PCT -gt 80 ]]; then
    echo "$(date) Connection usage: $PCT% ($USED/$MAX)" | tee -a /var/log/mysql_health.log
fi

# 4. 磁盤空間
DISK_PCT=$(df -h /var/lib/mysql | awk 'NR==2 {print $5}' | sed 's/%//')
if [[ $DISK_PCT -gt 85 ]]; then
    echo "$(date) Disk usage: $DISK_PCT%" | tee -a /var/log/mysql_health.log
fi

總結

掌握 Linux 下 MySQL 的目錄佈局、密碼管理全流程、服務控制標準化、故障排查清單，是 DBA 穩定交付的基本功。建議將上述操作封裝為 Ansible Playbook、Shell 腳本庫或 Python 模塊，實現「基礎設施即代碼」，消除手工操作差異，提升運維效率與可靠性。