萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL 常用管理命令大全:備份還原、結構操作、權限與編碼

一、mysqldump 備份與匯出

1. 完整備份 (含結構+資料)

mysqldump -h DB_IP -u User -p --opt DBName > backup_$(date +%F).sql
# 輸入密碼或使用 --password=Pwd (不安全,僅腳本內部使用)

2. 僅匯出結構 (無資料)

mysqldump -h DB_IP -u User -p -d DBName > structure.sql

3. 匯出指定資料表

mysqldump -h DB_IP -u User -p --opt DBName TableName > table_dump.sql

4. 按條件匯出資料 (WHERE 子句)

mysqldump -h DB_IP -u User -p DBName TableName --where="status=1 AND created_at>'2023-01-01'" > filtered.sql

5. 只匯出資料 (不含 CREATE TABLE) - 適合異構遷移

mysqldump -h DB_IP -u User -p -t DBName TableName > data_only.sql

6. 重要參數速查

參數短參說明
--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 客戶端常用操作

1. 查詢結果導出檔案

mysql> SELECT * FROM book INTO OUTFILE '/tmp/books.csv' 
    FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';

注意:需 `FILE` 權限,檔案寫在伺服器端,`secure_file_priv` 限制目錄。

2. 查找/刪除重複資料

-- 查重 (依 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;

3. 編碼檢查與修改

-- 查看目前編碼設定
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;

4. DDL 結構變更

-- 新增欄位
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;

5. 遠程存取授權

-- 建立遠程使用者
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;
copyright © 萬盛學電腦網 all rights reserved