MySQL 日常運維高頻命令速查,涵蓋 mysqldump、mysql 客戶端、DDL/DML、權限管理、編碼處理、重複資料清理。
# 完整備份 (含結構+資料)
mysqldump -h host -u user -p --opt dbname > backup.sql
# 僅結構 (不含資料)
mysqldump -h host -u user -p -d dbname > structure.sql
# 單表備份
mysqldump -h host -u user -p --opt --add-drop-table dbname tblname > table.sql
# 指定密碼 (避免互動輸入,注意安全性)
mysqldump -h host -u user --password=pwd --opt dbname > backup.sql
# 查詢結果導出檔案
SELECT title FROM book INTO OUTFILE '/tmp/outfile.txt';
# 查找重複記錄 (單欄位 peopleId)
SELECT * FROM people
WHERE peopleId IN (SELECT peopleId FROM people GROUP BY peopleId HAVING COUNT(*) > 1);
# 查詢不重複記錄 (去重)
SELECT * FROM phome_ecms_wma
WHERE title IN (SELECT DISTINCT title FROM phome_ecms_wma);
# 刪除重複 (保留首筆,利用匯出/匯入)
SELECT *, COUNT(DISTINCT title) INTO OUTFILE '/tmp/table.bak'
FROM phome_ecms_wma GROUP BY title;
DELETE FROM phome_ecms_wma;
LOAD DATA INFILE '/tmp/table.bak' REPLACE INTO TABLE phome_ecms_wma CHARACTER SET utf8;
# 查看當前編碼設定
SHOW VARIABLES LIKE 'character_set%';
# 修改表欄位類型
ALTER TABLE tbl CHANGE last_action last_action DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00';
# 新增欄位
ALTER TABLE host ADD ks_mac VARCHAR(100);
# 刪除欄位
ALTER TABLE tbl DROP COLUMN field_name;
# 重命名表
ALTER TABLE t1 RENAME t2;
# 一般索引
ALTER TABLE tbl ADD INDEX idx_name (col1, col2);
# 主鍵
ALTER TABLE tbl ADD PRIMARY KEY (id);
# 唯一索引
ALTER TABLE tbl ADD UNIQUE uk_card (cardnumber);
# 刪除索引
ALTER TABLE tbl DROP INDEX idx_name;
GRANT ALL PRIVILEGES ON dbname.* TO 'root'@'192.168.1.9' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;
--opt 等同 --add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset,備份預設最佳--single-transaction (InnoDB) 避免鎖表LOAD DATA INFILE 比 INSERT 快 20 倍以上CONVERT TO CHARACTER SET utf8mb4 重寫