MySQL 常用管理指令速查手冊

MySQL 常用管理指令速查手冊

涵蓋遠程連線授權、主從同步配置、資料導入導出、編碼設定、表維護、效能診斷等高頻運維場景，適合 DBA 與開發人員隨時查閱。

一、遠程連線授權

-- 建立可遠程連線的 root（生產環境建議用專用帳號）
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'strong_password' WITH GRANT OPTION;
FLUSH PRIVILEGES;

-- 僅允許特定 IP
GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.1.%' IDENTIFIED BY 'strong_password' WITH GRANT OPTION;
FLUSH PRIVILEGES;

二、解決遠程連線緩慢

編輯 my.cnf [mysqld] 區塊：

skip-name-resolve      # 禁止 DNS 反解，大幅降低連線延遲
skip-grant-tables      # 僅緊急救援用，正常環境禁用！

修改後重啟 MySQL。啟用 skip-name-resolve 後，授權表 Host 必須用 IP，不能用主機名。

三、修改密碼

# 命令列（已知舊密碼）
mysqladmin -u root -p'old_pass' password 'new_pass'

# SQL（已登入）
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('new_pass');
-- 或
UPDATE mysql.user SET authentication_string=PASSWORD('new_pass') WHERE User='root';
FLUSH PRIVILEGES;

四、主從同步故障處理

方案 I：重新指定 Binlog 位點

-- 從庫
STOP SLAVE;
-- 主庫
SHOW MASTER STATUS;
-- 記錄 File、Position
-- 從庫
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000020', MASTER_LOG_POS=1052;
START SLAVE;

方案 II：跳過錯誤事務

STOP SLAVE;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
START SLAVE;

謹慎使用：跳過可能導致主從資料不一致。優先排查錯誤原因。

五、主從配置範本

主庫

[mysqld]
log-bin=mysql-bin
server-id=1
binlog-do-db=test
binlog-ignore-db=mysql

從庫

[mysqld]
log-bin=mysql-bin
server-id=2
master-host=192.168.1.5
master-port=3306
master-user=repl_user
master-password=repl_pass
master-connect-retry=60
replicate-do-db=test
replicate-ignore-db=mysql

MySQL 5.6+ 建議用 CHANGE MASTER TO 動態配置，不寫入 my.cnf。

六、快速清空大表

TRUNCATE TABLE table_name;  -- 秒級，不記錄 Binlog（或記錄 DDL），無法回滾，不觸發 Trigger
-- 對比
DELETE FROM table_name;     -- 逐行刪除，記錄 Binlog，可回滾，觸發 Trigger，極慢

七、資料導入導出

# 導出（邏輯備份）
mysqldump -u user -p db_name > backup.sql
mysqldump -u user -p db_name table1 table2 > partial_backup.sql

# 導入
mysql -u user -p db_name < backup.sql
source /path/to/backup.sql  -- 在 mysql client 內執行

# 導出壓縮
mysqldump -u user -p db_name | gzip > backup.sql.gz
gunzip < backup.sql.gz | mysql -u user -p db_name

八、建立 UTF-8 資料庫

-- utf8mb4（完整 Unicode，含 Emoji，推薦）
CREATE DATABASE `db_name` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

-- utf8（BMP 字符，舊版相容）
CREATE DATABASE `db_name` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

九、效能診斷常用指令

-- 當前執行緒
SHOW PROCESSLIST;
SHOW FULL PROCESSLIST;

-- 慢查詢日誌開啟
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;
SET GLOBAL log_queries_not_using_indexes = ON;

-- 表狀態
SHOW TABLE STATUS LIKE 'table_name';

-- 索引資訊
SHOW INDEX FROM table_name;

-- 表大小統計
SELECT table_name, 
       ROUND(data_length/1024/1024,2) AS data_mb,
       ROUND(index_length/1024/1024,2) AS index_mb,
       ROUND((data_length+index_length)/1024/1024,2) AS total_mb,
       table_rows
FROM information_schema.tables
WHERE table_schema = 'db_name'
ORDER BY total_mb DESC;

-- InnoDB 狀態
SHOW ENGINE INNODB STATUS;

-- 全域狀態
SHOW GLOBAL STATUS LIKE 'Innodb%';
SHOW GLOBAL STATUS LIKE 'Qcache%';
SHOW GLOBAL STATUS LIKE 'Threads%';
SHOW GLOBAL STATUS LIKE 'Handler%';

-- 全域變數
SHOW GLOBAL VARIABLES LIKE 'innodb%';
SHOW GLOBAL VARIABLES LIKE 'query_cache%';

十、表維護指令

-- 分析表統計（更新索引基數）
ANALYZE TABLE table_name;

-- 優化表（整理碎片、重建索引、更新統計）
OPTIMIZE TABLE table_name;

-- 檢查表
CHECK TABLE table_name;

-- 修復表
REPAIR TABLE table_name;