您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 資料庫相關知識

MySQL 管理實務：備份策略、權限控制與資料修復完整指南

編輯：MySQL 資料庫相關知識

MySQL 是內容驅動型網站的核心基礎設施。本文系統說明生產級 MySQL 管理關鍵技術：為何標準檔案備份不足、mysqldump 與增量日誌備份實務、GRANT/REVOKE 精細權限模型、遺忘 root 密碼重置、資料檔案檢查修復（myisamchk），助您建構可靠、安全的資料庫服務。

一、為什麼標準檔案備份失效？

MySQL 使用快取/緩衝區，磁碟檔案非即時一致；直接複製檔案無法保證時間點一致性。
標準備份僅提供「瞬間快照」，備份後新增資料若遺失對電商等業務不可接受。
需實時、線上、不影響服務的備份方案。

二、mysqldump 完整備份與恢復

備份指令

mysqldump -h myhost -u root -pmypass dbname > dbname_backup.sql

恢復步驟

mysqladmin -h myhost -u root -pmypass create dbname
mysql -h myhost -u root -pmypass dbname < dbname_backup.sql

優勢： 透過 SQL 邏輯備份，確保邏輯一致性，優於檔案級複製。

三、啟用更新日誌實現增量備份

配置方式

啟動參數：safe-mysqld --log-update=/usr/backups/mysql/update
或配置檔 my.cnf：
[mysqld] log-update=/usr/backups/mysql/update

產生 update.001, update.002... 記錄所有 INSERT/UPDATE/CREATE 等變更語句（不含 SELECT）。

日誌維護腳本（每週執行）

#!/bin/sh
find /usr/backups/mysql/ -name "update.[0-9]*" -type f -mtime +6 | xargs rm -f
/usr/local/mysql/bin/mysqladmin -u root -ppassword flush-logs

flush-logs 切換新日誌檔，確保舊檔可安全刪除。

增量恢復流程

# 1. 還原完整備份
mysqladmin create dbname
mysql dbname < dbname_backup.sql

# 2. 依序套用增量日誌
mysql --one-database dbname < update.100
mysql --one-database dbname < update.101
...

亦可編輯日誌移除誤操作（如 DROP TABLE）後再恢復，實現時間點恢復 (PITR)。

四、MySQL 存取控制：GRANT/REVOKE 精細權限

權限分類

類別	權限	說明
資料庫/資料表/欄位	SELECT	查詢資料
	INSERT	新增資料
	UPDATE	修改資料
	DELETE	刪除資料
	CREATE	建立資料庫/資料表
	DROP	刪除資料庫/資料表
	INDEX	建立/刪除索引
	ALTER	修改表結構
全域管理	FILE	伺服器檔案讀寫（高風險）
	PROCESS	檢視/終止其他執行緒
	RELOAD	重載授權表、刷新日誌
	SHUTDOWN	關閉 MySQL 服務
特殊	ALL	所有權限（等同 root）
特殊	USAGE	僅允許登入，無操作權限

GRANT 語法

GRANT  ON  TO  [IDENTIFIED BY "password"] [WITH GRANT OPTION];

：*.*（全域）、db.*（庫級）、db.tbl（表級）、db.tbl(col1,col2)（欄位級）
：username@host，支援 % 萬用字元（如 user@%.domain.com）
WITH GRANT OPTION：允許受授者轉授權限（謹慎使用）

實用範例

-- 僅讀用戶
GRANT SELECT ON mydb.* TO readonly@'%' IDENTIFIED BY 'pass';

-- 特定欄位更新權限
GRANT UPDATE (email, phone) ON mydb.users TO editor@localhost IDENTIFIED BY 'pass';

-- 管理特定資料庫
GRANT ALL ON mydb.* TO dbadmin@localhost IDENTIFIED BY 'pass' WITH GRANT OPTION;

REVOKE 語法

REVOKE  [(columns)] ON  FROM ;

完全移除用戶需直接刪除 mysql.user 表記錄：

DELETE FROM mysql.user WHERE User='baduser' AND Host='%';
FLUSH PRIVILEGES;

五、權限陷阱：匿名用戶與主機名解析

新安裝預設存在匿名用戶 ''@'localhost'、''@'host'，優先級高於萬用字元帳號，導致合法用戶連線被誤判為匿名並拒絕。
解法： DELETE FROM mysql.user WHERE User=''; FLUSH PRIVILEGES;
本機連線需同時建立 user@localhost 與 user@real_hostname 兩組帳號，避免解析不匹配。

六、遺忘 Root 密碼重置流程

停止服務：kill `cat /var/run/mysqld/mysqld.pid`（勿用 kill -9）
跳過授權啟動：safe-mysqld --skip-grant-tables &
免密登入修改：
mysql -u root mysql UPDATE user SET Password=PASSWORD('newpass') WHERE User='root'; FLUSH PRIVILEGES;
正常重啟服務。

七、資料檔案檢查與修復

檔案結構

.frm：表定義（欄位、型態）
.MYD：資料檔
.MYI：索引檔

myisamchk 操作

# 檢查單表
myisamchk /path/to/db/tbl_name
myisamchk /path/to/db/tbl_name.MYI

# 檢查全庫/全部
myisamchk /path/to/db/*.MYI
myisamchk /path/to/var/*/*.MYI

# 深度檢查
myisamchk --extend-check /path/to/tbl_name

三級修復策略（由快到慢）

快速修復： myisamchk -r -q（修復常見問題）
標準修復： myisamchk -r（重建索引）
擴展修復： myisamchk --safe-recover（處理嚴重損壞）

前置條件： 必須關閉 MySQL 服務或鎖定表，避免並發修改導致檢查誤判。

極端損壞救援

索引檔遺失/損壞： 備份 .MYD、.frm → DELETE FROM tbl 產生新 .MYI → 覆回 .MYD → 標準修復重建索引。
定義檔遺失： 備份 .MYD、.MYI → 刪除表檔 → 用原 CREATE TABLE 重建 .frm → 標準修復。