MySQL 運維實戰：死鎖、連接數、密碼、登錄、DATETIME .0、表空間 6 大高頻問題解決方案

目錄

1. 表鎖定排查與解鎖
2. 連接數耗盡
3. Root 密碼修改
4. 密碼正確卻無法登錄
5. DATETIME 顯示 .0 毫秒問題
6. 查看表空間佔用

1. 表鎖定排查與解鎖

DML 操作卡住通常因鎖衝突。排查步驟：

-- 1. 查看進程
SHOW PROCESSLIST;

-- 2. 定位持鎖進程 ID，殺掉
KILL ;

若 PROCESSLIST 難以判斷，查詢 InnoDB 內部鎖視圖：

SELECT * FROM information_schema.INNODB_TRX;
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
-- 關注 trx_mysql_thread_id，即為持鎖線程 ID

經驗提示：開發環境慎用 GUI 直接修改表結構（易鎖表），生產環境務必使用 DDL 語句：

ALTER TABLE tf_b_depart ADD (PARENT_MAJOR VARCHAR(6));
ALTER TABLE tf_f_task_target MODIFY VALUE DECIMAL(16,2);
CREATE TABLE td_s_salary_index (index_id VARCHAR(8));

2. 連接數耗盡

報錯：Too many connections。默認 max_connections=100。

臨時恢復：重啟 MySQL 服務。
永久調整：修改 my.cnf：

[mysqld01]
max_connections=1500
join_buffer_size = 128M
sort_buffer_size = 10M
read_rnd_buffer_size = 2M

重啟生效。同步調大 open_files_limit、ulimit -n 避免文件描述符不足。

3. Root 密碼修改

SELECT * FROM mysql.user WHERE USER='root';
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('root123');
SET PASSWORD FOR 'root'@'paas03' = PASSWORD('root123');
SET PASSWORD FOR 'root'@'%' = PASSWORD('root123');
FLUSH PRIVILEGES;

✅ 親測有效。MySQL 8.0 可用 ALTER USER 'root'@'%' IDENTIFIED BY 'newpwd';。

4. 密碼正確卻無法登錄

現象：遠程可登，本機報 Access denied for user 'zplat_cen1'@'aifs1'，去掉 -p 卻能免密進入但僅見 test 庫。

根因：MySQL 用戶按 用戶名@主機 區分，DBA 未創建 'zplat_cen1'@'aifs1'（本機主機名）。

解決：

CREATE USER 'zplat_cen1'@'aifs1' IDENTIFIED BY 'XXX';
GRANT ALL PRIVILEGES ON *.* TO 'zplat_cen1'@'aifs1' WITH GRANT OPTION;
FLUSH PRIVILEGES;

同一用戶需在 localhost、主機名、% 三處各創建一次。

5. DATETIME 顯示 .0 毫秒問題

JDBC 獲取 TIMESTAMP 類型字段時，rs.getTimestamp() 返回 2015-07-21 16:37:47.0，前端顯示多餘 .0。

方案一：SQL 層格式化（侵入性大）

SELECT DATE_FORMAT(RECEIVE_TIME, '%Y-%m-%d %H:%i:%s') FROM ...

方案二：框架層統一處理（推薦）

// Java 示例：ResultSetHandler 中統一轉換
if (type == Types.TIMESTAMP) {
    Timestamp t = rs.getTimestamp(name);
    if (t == null) return null;
    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
    return sdf.format(t);
}

// DECIMAL 類型同理去除尾部 .00
if (type == Types.DECIMAL) {
    String decimal = rs.getString(name);
    if (decimal == null) return null;
    if (decimal.indexOf(".") > 0) {
        decimal = decimal.replaceAll("0+?$", "").replaceAll("[.]$", "");
    }
    return decimal;
}

💡 正則表達式是處理此類格式化的利器。

6. 查看表空間佔用

SELECT table_name, data_length/1024/1024 AS MB
FROM information_schema.tables
WHERE table_schema = 'zplatdb'
ORDER BY data_length DESC;

快速定位大表，制定歸檔/分區策略。