MySQL 備份恢復與用戶授權速查：mysqldump、CREATE USER、GRANT/REVOKE、存儲引擎影響約束

MySQL 備份恢復與用戶授權速查：mysqldump、CREATE USER、GRANT/REVOKE、存儲引擎影響約束

最精簡的備份/還原指令、用戶建立授權模板、以及外鍵約束對存儲引擎的依賴關係，適合貼在工位牆上隨時查閱。

1. 備份與還原

# 邏輯備份 (mysqldump)
mysqldump -u root -p dbname > backup.sql          # 整庫含資料
mysqldump -u root -p -d dbname > struct.sql       # 僅結構 (-d / --no-data)
mysqldump -u root -p dbname tblname > tbl.sql     # 單表

# 還原
mysql -u root -p dbname < backup.sql
# 或在 mysql shell
mysql> SOURCE /path/backup.sql;

2. 用戶建立與授權

# 建立用戶
CREATE USER 'username'@'host' IDENTIFIED BY 'password';

# 授權 (注意 DBNAME.* 後的 * 號不可少)
GRANT ALL PRIVILEGES ON DBNAME.* TO 'username'@'host';

# 回收權限
REVOKE ALL PRIVILEGES ON DBNAME.* FROM 'username'@'host';

# 刷新生效 (直接操作權限表後必須)
FLUSH PRIVILEGES;

3. 存儲引擎決定約束生效與否

• 外鍵約束 (FOREIGN KEY)：僅 InnoDB 支援，MyISAM / MEMORY / CSV 等引擎定義後會被忽略 (不報錯、不生效)。
• 其他約束 (PK, UK, NOT NULL, DEFAULT) 在所有引擎均生效。
• 建表時若需外鍵，務必指定 ENGINE=InnoDB。

4. 快速驗證清單

1. 備份檔大小 > 0 且含 CREATE TABLE / INSERT 關鍵字。
2. 還原後 CHECKSUM TABLE 或 SELECT COUNT(*) 與源庫一致。
3. SHOW GRANTS FOR 'user'@'host'; 確認權限生效。
4. SHOW CREATE TABLE tbl; 確認 ENGINE=InnoDB 且外鍵定義存在。

牢記「mysqldump 導出、mysql 導入、CREATE USER 建帳、GRANT 授權、InnoDB 做外鍵」，即可應對 90% 日常備份與權限操作場景。