一、連線 MySQL
# 本機
mysql -u root -p
# 遠程
mysql -h 192.168.1.10 -u root -p'password'
# 退出
exit 或 quit
二、修改密碼
# 首次設定（無舊密碼）
mysqladmin -u root password 'newpass'
# 變更
mysqladmin -u root -p'oldpass' password 'newpass'
# MySQL 5.7+ 內部 SQL
ALTER USER 'root'@'localhost' IDENTIFIED BY 'newpass';
三、帳號管理（最小權限原則）
# 建立唯讀帳號
CREATE USER 'readonly'@'%' IDENTIFIED BY 'pass';
GRANT SELECT ON mydb.* TO 'readonly'@'%';
# 建立讀寫帳號（限定 IP）
CREATE USER 'app'@'10.0.%' IDENTIFIED BY 'strong_pass';
GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO 'app'@'10.0.%';
# 管理員帳號（可授權）
CREATE USER 'admin'@'localhost' IDENTIFIED BY 'pass';
GRANT ALL ON mydb.* TO 'admin'@'localhost' WITH GRANT OPTION;
# 刪除帳號
DROP USER 'old'@'%';
# 刷新權限
FLUSH PRIVILEGES;
四、庫與表操作
| 操作 | 指令 |
|---|
| 顯示所有庫 | SHOW DATABASES; |
| 建立庫 | CREATE DATABASE db_name CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci; |
| 切換庫 | USE db_name; |
| 顯示表 | SHOW TABLES; |
| 檢視表結構 | DESC table_name; 或 SHOW COLUMNS FROM table_name; |
| 檢視建表 SQL | SHOW CREATE TABLE table_name; |
| 建表範例 | CREATE TABLE t (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50) NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP); |
| 刪表 | DROP TABLE table_name; |
| 清空表 | TRUNCATE TABLE table_name; |
| 修改表結構 | ALTER TABLE t ADD COLUMN email VARCHAR(100); |
五、資料增刪改查 (CRUD)
-- 插入
INSERT INTO t (name) VALUES ('Alice'), ('Bob');
-- 查詢
SELECT * FROM t WHERE name LIKE '%Ali%';
-- 更新
UPDATE t SET name='Alice Updated' WHERE id=1;
-- 刪除
DELETE FROM t WHERE id=2;
六、文字檔匯入匯出
# 匯出（Tab 分隔）
SELECT * FROM t INTO OUTFILE '/tmp/t.txt' FIELDS TERMINATED BY '\t';
# 匯入（需 FILE 權限、secure_file_priv 允許目錄）
LOAD DATA INFILE '/tmp/t.txt' INTO TABLE t FIELDS TERMINATED BY '\t';
# 本地檔案匯入（客戶端讀檔）
LOAD DATA LOCAL INFILE 'C:/data/t.txt' INTO TABLE t FIELDS TERMINATED BY '\t';
七、備份與還原
# 完整備份（含例程、觸發器、事件）
mysqldump --single-transaction --master-data=2 --routines --triggers --events -u root -p db > db_full.sql
# 壓縮備份
mysqldump ... | gzip > db_$(date +%F).sql.gz
# 還原
mysql -u root -p db < db_full.sql
# 或進入 mysql 後
SOURCE /path/db_full.sql;
八、腳本化執行
- 互動模式：
mysql> SOURCE script.sql;
- 非互動：
mysql -u user -p db < script.sql
- 單條指令：
mysql -u user -p -e "SELECT * FROM t;"
九、效能診斷常用指令
| 指令 | 用途 |
|---|
SHOW PROCESSLIST; | 當前連線/查詢，找長時間運行 |
SHOW ENGINE INNODB STATUS; | InnoDB 內部狀態、鎖、死鎖 |
SHOW GLOBAL STATUS LIKE 'Threads%'; | 連線統計 |
SHOW VARIABLES LIKE 'max_connections'; | 參數檢視 |
EXPLAIN SELECT ...; | 執行計劃分析 |
SHOW INDEX FROM t; | 索引資訊 |
ANALYZE TABLE t; | 更新統計資訊 |
OPTIMIZE TABLE t; | 整理碎片、回收空間 |
十、實用技巧彙總
- 分頁優化：游標分頁
WHERE id > last_id ORDER BY id LIMIT 20 取代 LIMIT offset, limit。
- 防注入：一律使用預處理/參數綁定，拼接 SQL 視為缺陷。
- 字符集：全鏈路
utf8mb4（資料庫/表/欄位/連線/程式）。
- 時區：伺服器、連線、應用統一 UTC，存
TIMESTAMP 或 DATETIME。
- 備份驗證：定期在測試環境完整還原，記錄 RTO/RPO。
- 監控告警：連線數、慢查詢、磁碟、複寫延遲、備份失敗。
