mysqldump -h DB_IP -u User -p --opt DBName > backup_$(date +%F).sql
# 輸入密碼或使用 --password=Pwd (不安全,僅腳本內部使用)
mysqldump -h DB_IP -u User -p -d DBName > structure.sql
mysqldump -h DB_IP -u User -p --opt DBName TableName > table_dump.sql
mysqldump -h DB_IP -u User -p DBName TableName --where="status=1 AND created_at>'2023-01-01'" > filtered.sql
mysqldump -h DB_IP -u User -p -t DBName TableName > data_only.sql
| 參數 | 短參 | 說明 |
|---|---|---|
| --opt | - | 啟用批次優化組合 (--add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset),**預設開啟** |
| --single-transaction | - | InnoDB 熱備份關鍵,一致性快照不鎖表 (需 REPEATABLE READ 隔離級) |
| --master-data=1/2 | - | 輸出 CHANGE MASTER TO 陳述式,用於建立 Slave (1=註解, 2=可執行) |
| --hex-blob | - | 二進位欄位 (BLOB/BINARY) 以十六進位輸出,避免編碼問題 |
| --routines / --triggers | -R / --triggers | 包含預存程序/函數/觸發器 (預設包含觸發器,程序需 -R) |
| --default-character-set | - | 指定連線編碼 (如 utf8mb4),防止匯出亂碼 |
| --compress | -C | 壓縮傳輸協定 (慢速網路有效) |
mysql> SELECT * FROM book INTO OUTFILE '/tmp/books.csv'
FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';
注意:需 `FILE` 權限,檔案寫在伺服器端,`secure_file_priv` 限制目錄。
-- 查重 (依 peopleId)
SELECT * FROM people WHERE peopleId IN (
SELECT peopleId FROM people GROUP BY peopleId HAVING COUNT(*) > 1
);
-- 刪重保留最小 ID
DELETE p1 FROM people p1
JOIN people p2 ON p1.peopleId = p2.peopleId AND p1.id > p2.id;
-- 查看目前編碼設定
SHOW VARIABLES LIKE 'character_set%';
SHOW VARIABLES LIKE 'collation%';
-- 修改資料庫預設編碼
ALTER DATABASE DBName CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- 修改資料表編碼 (含欄位轉換)
ALTER TABLE TableName CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- 修改單一欄位
ALTER TABLE TableName CHANGE ColName ColName VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- 新增欄位
ALTER TABLE host ADD ks_mac VARCHAR(100);
-- 刪除欄位
ALTER TABLE table_name DROP COLUMN field_name;
-- 重命名資料表
RENAME TABLE t1 TO t2; -- 或 ALTER TABLE t1 RENAME t2;
-- 索引操作
ALTER TABLE t ADD INDEX idx_name (col1, col2);
ALTER TABLE t ADD PRIMARY KEY (id);
ALTER TABLE t ADD UNIQUE uk_card (card_number);
ALTER TABLE t DROP INDEX idx_name;
-- 建立遠程使用者
CREATE USER 'app_user'@'192.168.1.%' IDENTIFIED BY 'StrongPass#2024';
-- 或舊版語法
GRANT ALL PRIVILEGES ON app_db.* TO 'app_user'@'192.168.1.%' IDENTIFIED BY 'StrongPass#2024';
FLUSH PRIVILEGES;
-- 查看執行計畫
EXPLAIN SELECT * FROM t WHERE col = 'val';
-- 查看表狀態 (資料大小、索引大小、行數、碎片)
SHOW TABLE STATUS LIKE 't';
-- 分析表 (更新統計資訊)
ANALYZE TABLE t;
-- 優化表 (整理碎片、重建索引 - MyISAM/InnoDB)
OPTIMIZE TABLE t;