MySQL 實用小技巧合集：密碼修改、遠程授權、DDL 變更、索引建立、資料匯入導出

前言：DBA 與開發者的隨身工具箱

整理 20+ 高頻 MySQL 操作片段，涵蓋帳號管理、DDL 變更、資料操作、索引優化、匯入導出，每條經過生產驗證，可直接複製執行。

一、帳號與權限管理

1. 修改 root 密碼

mysql -u root -p
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('newpass');
FLUSH PRIVILEGES;

2. 建立超級管理員 (允許遠程)

GRANT ALL PRIVILEGES ON *.* TO 'admin'@'%' IDENTIFIED BY 'strongpass' WITH GRANT OPTION;
FLUSH PRIVILEGES;

3. 建立特定資料庫唯讀帳號

GRANT SELECT ON mydb.* TO 'readonly'@'192.168.1.%' IDENTIFIED BY 'pass';
FLUSH PRIVILEGES;

4. 回收權限並刪除帳號

REVOKE ALL PRIVILEGES ON *.* FROM 'olduser'@'%';
DELETE FROM mysql.user WHERE User='olduser' AND Host='%';
FLUSH PRIVILEGES;

二、DDL 結構變更 (單表多操作合併執行，減少鎖時間)

ALTER TABLE t1
  RENAME TO t2,
  MODIFY a TINYINT NOT NULL,
  CHANGE b c CHAR(20),
  ADD d TIMESTAMP,
  ADD INDEX idx_d (d),
  ADD PRIMARY KEY (a),
  DROP COLUMN c,
  ADD c INT UNSIGNED NOT NULL AUTO_INCREMENT,
  ADD INDEX idx_c (c);
-- 注意：AUTO_INCREMENT 欄位必須建立索引且 NOT NULL

三、資料操作 (DML)

1. 條件刪除

DELETE FROM t1 WHERE c > 10;

2. 批次更新

UPDATE t1 SET user='mysqlcn', password='mysqlcn' WHERE id < 100;

3. 前綴索引 (節省空間、加速 LIKE 查詢)

CREATE INDEX idx_name_prefix ON customer (name(10));

四、資料匯入導出

1. mysqldump 邏輯備份

# 完整庫備份 (含結構+資料)
mysqldump -u root -p --single-transaction --routines --triggers --events dbname > backup.sql

# 僅結構
mysqldump -u root -p -d dbname > schema.sql

# 僅資料
mysqldump -u root -p -t dbname > data.sql

# 壓縮備份
mysqldump -u root -p dbname | gzip > backup.sql.gz

2. 還原備份

mysql -u root -p dbname < backup.sql
# 或
mysql -u root -p -e "SOURCE /path/backup.sql" dbname

3. CSV 匯出/匯入

# 匯出
SELECT * FROM t1 INTO OUTFILE '/tmp/t1.csv'
  FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';

# 匯入 (需 FILE 權限、secure_file_priv 目錄)
LOAD DATA INFILE '/tmp/t1.csv'
  INTO TABLE t1
  FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n'
  IGNORE 1 LINES;

五、資訊查詢

SHOW DATABASES;
USE mysql; SHOW TABLES;
DESC user;           -- 表結構
SHOW CREATE TABLE t1; -- 完整建表語句
SHOW INDEX FROM t1;   -- 索引詳情
SHOW PROCESSLIST;     -- 當前執行線程
SHOW ENGINE INNODB STATUS\G  -- InnoDB 內部狀態

六、效能診斷片段

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

-- 查看表大小
SELECT table_name, ROUND(data_length/1024/1024,2) AS data_mb,
       ROUND(index_length/1024/1024,2) AS idx_mb
FROM information_schema.tables
WHERE table_schema = 'dbname'
ORDER BY data_mb DESC;

-- 找出無索引表
SELECT table_name FROM information_schema.tables
WHERE table_schema = 'dbname' AND engine = 'InnoDB'
AND table_name NOT IN (SELECT DISTINCT table_name FROM information_schema.statistics WHERE table_schema = 'dbname');

七、事務控制

START TRANSACTION;
INSERT ...; UPDATE ...; DELETE ...;
COMMIT;  -- 或 ROLLBACK;

-- 自動提交關閉
SET autocommit = 0;
-- 批次操作後
COMMIT;
SET autocommit = 1;

結語

技巧無高低，適用即珍貴。建議將本文件納入團隊運維手冊，Code Review 時檢查「是否使用批次 DDL、是否加前綴索引、是否關閉自動提交做批次事務」。熟練這些片段，能讓日常操作從「查文檔、試錯誤」轉為「複製、微調、執行、驗證」的標準化流程。