第三章 伺服器管理
3.1. 存取權限管理 2
3.1.1 權限工作原理 2
3.1.2 權限類型 4
3.1.3 授權 6
3.1.4 撤除權限 7
3.1.5 密碼設定與變更 8
3.1.6 主機及其它設定值 8
3.1.7 啟用權限 9
3.1.8 權限驗證 9
3.2 伺服器系統環境管理 10
3.2.1 系統環境參數設定 10
3.2.2 系統環境參數形式 10
3.3 日誌檔管理 13
3.3.1 產生日誌檔 13
3.3.2 日誌檔內容 13
3.4 伺服器管理命令－mysqladmin 16
3.4.1 用途與語法 16
3.4.2 支援指令 16
3.5 災難預防與復原 18
3.5.1 資料庫備份 18
3.6 復原資料 21
3.6.1 重建資料庫 21
3.6.2 復原資料表 21
3.6.3 資料表維護與損壞復原 21
3.7 設定維護排程計劃 24
3.8 備用伺服器 24
3.8.1 基本需求 24
3.8.2 備份主伺服器資料庫 25
3.8.3 建立備用伺服器 25
回目錄
第三章 伺服器管理
本章介紹有關 MySQL 伺服器和資料庫之安全控管方式，以及資料庫 之備援處理程序(Back Up)。從安全性(Security)及可靠性(Reliability)角度出 發，協助讀者防止當機問題發生及當機後資料回復之處理方法。資料庫系 統可能因一時疏忽而造成資料流失，嚴重者還將造成不能復原。一個好的 應用系統，除了須考慮實用性和效率外，安全性和可靠性更是格外重要。
一個不安全的系統，很可能所有的資料在瞬間就被有心人竊用，甚至於造 成生命財產之安全損失。對於這些潛在的問題，在系統研發初期，系統發 展人員就須慎重考慮。此外，為了提昇系統可靠性，本章也將介紹備用伺 服器建立方法。
3.1. 存取權限管理
MySQL 採用一種先進的安全與權限控管系統，本節將介紹述 MySQL 之權限控管方式。權限的主要功能在於驗證使用者是否有權登入伺服器，
以及在執行諸如 Select、Insert、Update、Delete 等資料處理作業時，是否 有權開啟對應資料表來處理資料。此外，進階權限管理功能可用於驗證相 關人員是否可以將權限授與他人使用。
3.1.1 權限工作原理
MySQL 之權限管理乃針對使用者、主機和資料庫分別作驗證，當使用 者從不同的主機登入時，系統會驗證該使用者在該主機以及所要連線的資 料庫是否有獲得授權。雖然一個使用者只需要一個帳號，當他從不同的主 機連線時，他都必須經過個別主機授權，才能成功的登入伺服器。MySQL 將存取權限控制分為兩個階段：
階段一：伺服器先查核使用者是否有權連線。
階段二：假如有連線權且成功的連線時，使用者每發出每一道 SQL 指令，
MySQL 將檢查該使用者是否有足夠權限去處理該項作業。
連 線 後 假 如 權 限 有 被 變 更 過 （ 不 管 是 自 己 變 更 ， 或 是 管 理 者 變 更 ），
變更的權限並不會立即發生作用，執行【Flush Privileges】指令之後，MySQL 才會重新讀取權限資料表並置於記憶體。MySQL 將所有授權資訊儲存在名 稱為 mysql 資料庫內的相關資料表，其中 tables_priv 和 columns_priv 兩個 資料表用來存放資料處理權限資料，資料結構如表 3.1 所示，這些資料表 將用於階段二之查核；User, Db 及 Host 三個資料表則用來存放連線權限資 料，資料結構如表 3.2 所示，這些資料表使用於階段一和二之查核。
↑H
表 3.1 資料處理權限資料表 ↑H 資料表 tables priv columns priv
Host Host
Db Db
User User
Table name 控管項目
Table_name
Column name Table priv Column priv 授權項目
Column priv
表 3.2 連線權限資料表
資料表 User db Host
Host Host Host
User Db Db
控管項目
Password User
Select priv Select priv Select priv Insert priv Insert priv Insert priv Update priv Update priv Update priv Delete priv Delete priv Delete priv Index priv Index priv Index priv Alter priv Alter priv Alter priv Create priv Create priv Create priv Drop priv Drop priv Drop priv Grant priv Grant priv Grant priv Create view priv Create view priv Create view priv Show view priv Show view priv Show view priv References priv References priv References priv Reload priv
Shutdown priv Process priv File priv Show db priv Super priv
Create tmp table Create tmp table Create tmp table pri Lock tables priv Lock tables priv Lock tables priv Execute priv
Repl slave priv Repl client priv ssl type
ssl cipher x509 issuer 授權項
目
x509 subject
max questions max updates max connections
前述相關欄位之型態，其中 Host 不分大小寫，其餘欄位則區分大小 寫。列示在表 3.2 的授權項目欄位，其內容都是 ENUM(‘N’,’Y’)型，預設為 N，表示未授權。列示在表 3.1 的授權項目，其內容都是 SET 型，可能的集 合元素列示於表 3.3。
表 3.3 授權工作項目
資權限表 欄名 e 可能的工作項目
tables_priv Table_priv
'Select', 'Insert', 'Update', 'Delete', 'Create', 'Drop', 'Grant', 'References', 'Index', 'Alter'
tables_priv Column_priv 'Select', 'Insert', 'Update', 'References' columns_priv Column_priv 'Select', 'Insert',
'Update', 'References' 各權限資料表之用法簡述如下：
¾ 登錄在 User 資料表之使用者，在指定的 Host 登入時，該使用者將可以 處 理 位 於 伺 服 器 上 的 資 料 庫 （ 若 所 有 權 限 都 設 為 Y ）， 此 權 限 形 同 Supervisor 權限。
¾ 登錄在 Db 資料表之使用者，在指定的 Host 登入時，該使用者將可以 處理指定的資料庫，以及此資料庫內的全部資料表。
¾ 若需要從不同的主機連線來處理指定的資料庫時，可以使用 Host 資料 表設定主機與資料庫。假如以這個方式工作，請將 Db 資料表之 Host 欄位保留空白。
¾ tables_priv 與 columns_priv 權限設定意義與 Db 類似，不過它們可以指 定更詳細的權限。假如使用者有權處理特定資料表，此意謂他也有權處 理該資料表之全部欄位。
3.1.2 權限類型
MySQL 提供的權限類型以及各種權限之用途，摘述如表 3.4 說明。表 3.4 之各項權限使用時機詳述如下，其中非用於伺服器管理之權限，可搭 配 mysqladmin 主控端應用程式以及使用 SQL 指令來操作，而伺服器管理 權限只能搭配 mysqladmin 來操作。
↑H
表 3.4 權限類型
權限類型 設定欄位 用途（適用對像）
ALTER Alter_priv 資料表 DELETE Delete_priv 資料表 INDEX Index_priv 資料表 INSERT Insert_priv 資料表 SELECT Select_priv 資料表 UPDATE Update_priv 資料表
CREATE Create_priv 資料庫, 資料表, 或索 引表
DROP Drop_priv 資料庫或資料表 GRANT Grant_priv 資料庫或資料表 CREATE
TEMPORARY TABLES
Create_tmp_table_priv 伺服器管理
FILE File_priv 伺服器檔案匯入匯出 LOCK TABLES Lock_tables_priv 伺服器管理
PROCESS Process_priv 伺服器管理 RELOAD Reload_priv 伺服器管理 REPLICATION
CLIENT Repl_client_priv 伺服器管理 REPLICATION
SLAVE Repl_slave_priv 伺服器管理 SHOW
DATABASES Show_db_priv 伺服器管理 SHUTDOWN Shutdown_priv 伺服器管理 SUPER Super_priv 伺服器管理
¾ Select, Insert, Update 和 Delete 可用於處理指定資料庫內現存資料表的紀 錄操作。
¾ Create 使用於建立新的資料庫或資料表， Drop 則用於刪除現存的資料 庫或資料表。
¾ Index 使用於建立和刪除索引表，擁有 Create 資料表權限的人，同時擁有 Index 權限。
¾ Alter 使用於改變資料表結構或重新命名。
¾ Grant 可將自己擁有的權限授與他人。
¾ File 可以使用 Load Data Infile 和 Select 指令在伺服器上讀寫檔案。
¾ Reload 權 限 分 成 flush-Hosts, flush-logs, flush-privileges, flush-status, flush-tables, flush-threads, refresh, reload。其中，
9 reload 用於通知伺服器重新載入權限資料表到記憶體。
9 flush-privileges與reload類似。 ↑H
9 refresh 用來關閉和重開日誌檔，以及沖回所有資料表。
9 Fulsh-XXX 功能與 refresh 類似，但分的更細，而且也可以附帶案例。
¾ shutdown 用於關閉伺服器，無對應的 SQL 指令可用。
¾ processlist 用於顯示目前正在伺服器上工作的執行緒，使用者隨時都可以 顯示自己啟動的執行緒並刪除它，但是只有擁有 Process 權限的人才能查 看由他人啟動的執行緒，而擁有 Super 權限的人才能使用 Kill 來刪除由 他人啟動的執行緒。
¾ Create Temporary Table 可搭配關鍵字 TEMPORARY 來建立暫存表。
¾ Lock Tables 用來鎖住資料表，擁有 Select 權限的人，才能執行 Lock Tables。資料表被鎖定後，其他人無法寫入及/或讀取資料表。
¾ Replication Client 用於執行 Show Master Status 和 Show Slave Status 指令。
¾ Replication Slave 用於授權給備用伺服器之使用者，而這些人必須已連線 至主伺服器。若未經授權，備用伺服器將無法要求更新主伺服器之資料。
¾ Show Databases 用於檢視指名的資料庫之全部資訊，若無此權限，執行 此指令只能看到那些有被授權之資料表相關資訊。假如啟動伺服器時，
若有搭配 --skip-show-database 參數，縱使有此授權，也無法檢視資料庫 相關資訊。
3.1.3 授權
授權方式可分成完整授權（使用 Grant）和部份授權（使用 Insert）兩 種型式。假定有伺服器使用者 root 之權限，而且擁有 mysql 資料庫之 insert 和 reload 管理者權限，連線後可以將自幾擁有之權限給與其他使用者。
3.1.3.1 完整授權
範例 1：授權給使用者 monty，以密碼 Happy 從 LocalHost 登入，並享有 Grant 權限（形同 monty 具有 supervisor 之權限）。
指令：GRANT ALL PRIVILEGES ON *.* TO 'monty'@'LocalHost.' IDENTIFIED BY ' Happy ' WITH GRANT OPTION;
範例 2： 避免匿名者權限會蓋掉 monty 在 LocalHost 之權限，應增加一組權限，
指令如下：
指令：GRANT ALL PRIVILEGES ON *.* TO 'monty'@'%' IDENTIFIED BY ' Happy ' WITH GRANT OPTION;
範例 3： 授權給使用者：Admin， 無驗證密碼，可從 LocalHost 登入，享 有 Reload 和 Process 管理者權限，可使用 mysqladmin 主控端 應用程式來執行reload, refresh, 和flush-XXX工作，但是他無權處
理任何資料庫。 ↑H
指令：GRANT RELOAD, PROCESS ON *.* TO 'Admin'@'LocalHost';
範例 4：替使用者 dummy 預設一個從 LocalHost 登入之帳戶，但不給他任何權 限（使用 Usage 參數之效果），權限則可留待以後再以部份授權方式 加入。
指令：GRANT USAGE ON *.* TO 'dummy'@'LocalHost';
3.1.3.2 部份授權
¾ 設立帳戶並授與部分權限範例（例如只允許處理 bankaccount 資料庫）。
指令：GRANT SELECT, INSERT, UPDATE, DELETE, CREATE,DROP ON bankaccount.* TO 'custom'@'localHost' IDENTIFIED BY 'obscure';
¾ 使用 Insert 設立帳戶並授與部份權限範例，反之可使用 Delete 來撤除授 權。
範例一：INSERT INTO User (Host,User,Password) VALUES ('localHost', 'custom', PASSWORD('obscure'));
範例二：INSERT INTO User (Host,User,Password) VALUES ('whitehouse.gov' ,'custom', PASSWORD('obscure'));
範例三：INSERT INTO db (Host,Db,User,Select_priv,Insert_priv,
Update_priv,Delete_priv,Create_priv,Drop_priv) VALUES ('localHost', 'bankaccount', 'custom', 'Y', 'Y', 'Y', 'Y','Y', 'Y');
範例四：INSERT INTO db (Host,Db,User,Select_priv,Insert_priv, Update_priv,Delete_priv,Create_priv,Drop_priv) VALUES ('whitehouse.gov', 'expenses', 'custom', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y');
¾ 啟用權限指令：FLUSH PRIVILEGES;
3.1.4 撤除權限
下列指令可將指名帳號從 User 資料表刪除，資料刪除後，前述使用者即無 權限。
指令一：Drop User ‘帳號 1’, ‘帳號 2’ …;
也可以使用指令二或指令三先將使用者權限撤除，然後使用指令四將指名 帳號從 User 資料表刪除。
指令二：REVOKE priv_type [(column_list)] [, priv_type [(column_list)]] ... ON {tbl_name | * | *.* | db_name.*} FROM帳號 1 [,帳號 2] ... ↑H
指令三：REVOKE ALL PRIVILEGES, GRANT OPTION FROM User [, User] ...
指令四：DELETE FROM mysql.User WHERE User=’'帳號’ and Host='Host_name';
3.1.5 密碼設定與變更
可在設立帳戶時一併加入密碼或在事後補設，也可以將現用密碼加以 變更。為了安全起見，千萬不要以明碼方式儲存 User 資料表之 Password。
MySQL 提供一個 Password()函數，此函數可將設定密碼加密成 41 位元組 的暗碼。請牢記密碼，否則任何人都無法將暗碼轉成明碼。假如忘記密碼，
那只能請系統管理人員將舊帳號刪除，然後重建一組新的帳號和密碼。
3.1.5.1 事前設立密碼
將 Grant….IDENTIFIED BY '’密碼’; 改為下列形式即可：
Grant….IDENTIFIED BY Password('’密碼’);。
假如使用 Insert 指令設立帳戶時，也須使用 Password('’密碼’) 指令型式，
將密碼轉成暗碼。
3.1.5.2 事後補設密碼
下列指令可替使用者 Jeffrey 設立或變更密碼（只限 root 使用者）：
Set Password For ‘Jeffrey@%’ = Password(‘密碼’);
下列指令可替登入 MySQL 之用戶自行設立或變更密碼（只限本人）：
Set Password = Password(‘密碼’);
3.1.5.3 變更密碼
可使用下列指令變更密碼：
UPDATE User SET Password = PASSWORD('密碼') WHERE Host = '%' AND User = 'Jeffrey';
3.1.6 主機及其它設定值
在 User 資料表上之 Host 欄位，可使用下列型式輸入資料：
¾ 使用主機名稱、IP 代碼，或是 LocalHost。
¾ 使用主機名稱形式時，可搭配%萬用字元，例如單一字元%表示所有主 機，%.mysql.com 表示符合 mysql.com 網域的所有主機。
¾ 使用 IP 形式時，也可以搭配%萬用字元，例如 144.155.166.%。
¾ 使用IP形式時，可搭配遮罩格式，例如 192.58.197.0/255.255.255.0，此 意謂從主機 192.58.197.0 到 192.58.197.255，都被視為合法主機。 ↑H
¾ db 資料表之 Host 欄位若為空白時，將以 Host 資料表中對應之 Host 與 db 紀錄作為權限依據。若 db 欄位保留空白，將被視為%型式。
¾ 其它權限資料表之 Host 欄若保留空白，將被視為%型式。
¾ User 權限資料表之 User 欄若保留空白，將被視為匿名帳號。
¾ tables_priv 和 columns_priv 之 db, table_name, column_name 三個欄位不 得使用%字元，也不能保留空白。
3.1.7 啟用權限
在下列情況下，MySQL 將自動啟用全部或部份權限。
¾ 啟動 mysqld 時，所有權限資料表將被載入記憶體，所有權限將自動生效。
¾ 當伺服器重新載入權限資料表時，下列連線中主控端之權限將受到影響：
主控端在下一次執行資料表或欄位資料時，資料表或欄位權限才生 效。
主控端在下一次執行 Use Database 時，資料庫權限才生效。
主控端在下一次連線時，完整權限和新的密碼才生效。
¾ 執行 Grant, Revoke, 或 Set Password 指令後，伺服器將立即重新載入相 關的權限資料表。
¾ 執行 Insert, Update, 或 Delete 指令，必須搭配”Flush Privileges;” 指令，
新的權限才生效。
3.1.8 權限驗證
連線時，伺服器將進行階段一之權限驗證，驗證過程如下：
¾ 讀取 User 資料表紀錄。
¾ 依據 Host 及 User 將紀錄排序。
¾ 比對主機，若有相符之主機資料，取出帳號及密碼與登入資料比對，資 料皆符合時，即通過驗證。
在此特別說明，由於 MySQL 先檢查主機名稱，假如同一主機在 User 資料表登錄兩筆資料，一筆為匿名資料，另一筆為系統之某項工作之專責 人員，縱使你以專責人員之帳號和密碼登入，由於匿名資料排序在前，因 此你會被視為匿名使用者，因而你會失去處理專責工作之權限。
通過驗證後，假如你無法正常處理資料，很可能你被驗證為匿名使用 者。此時你可以使用指令【Select Current_User()】來查驗用戶資訊，提示 資訊格式為"帳號@主機"，請檢視你的帳號是否登錄在伺服器上。
假如你無法通過驗證，請系統管理人員依序檢查User, Db, Host三個權 限資料表之相關資料是否正確設定，或是檢查連線參數檔之帳號、主機，
或密碼是否正確設定。 ↑H
3.2 伺服器系統環境管理
3.2.1 系統環境參數設定
啟動 mysqld 伺服器時，可同時設定系統工作環境。可在命令列下輸入 系 統 環 境 變 數 及 設 定 參 數 值 ， 或 是 將 需 要 設 定 之 環 境 參 數 加 入 在 名 稱 叫 my.ini 之參數檔內。假如每次工作所需環境都不相同時，在命令列下設定 是較為可行的方式，否則應將環境參數直接加在參數檔內較為省事。參數 檔之用法與產生方式，請參考第一章 1.3.2 節之說明。當伺服器啟動後，
必要時也可以在應用程式內，重新設定系統環境參數。
系統參數值設定方式分成下列三種型式，形式一用於命令列，形式二 用於參數檔，形式三用於應用程式。
形式一： --參數名稱 = 參數值，或 --參數名稱 形式二： 參數名稱 = 參數值，或 參數名稱 形式三：SET GLOBAL 參數名稱 = 參數值；
設定參數時，下列參數格式具有相同效果。
¾ 關閉效果
--disable-column-names --skip-column-names --column-names=0
¾ 開啟效果
--column-names
--enable-column-names --column-names=1 3.2.2 系統環境參數形式
各種常用系統參數摘述如下：
--help（或 -?）：顯示簡短信息後，離開 mysqld。
--ansi：以標準 SQL 語法取代 MySQL 語法。
--basedir=PATH(或 –b PATH)：指出 MySQL 之安裝路徑在輸入之 PATH 路徑。
--character-set-server=CHARSET：使用輸入之 CHARSET 作為伺服器之預 設字元集。
--core-file：mysqld 當機後產生之信息檔案。
--datadir=PATH(或 -h PATH)：指出 MySQL之資料庫儲存在輸入之 PATH 路徑。 ↑H
--debug[= 選 項 值 ] ： 啟 動 追 蹤 功 能 ， MySQL 執 行 過 程 將 被 記 錄 在 mysqld.trace 檔案內。可搭配選項值，常用選項值為 d:t:o,file_name。
--default-storage-engine=TYPE：預設儲存引擎，與--default-table-type 同義。
--default-table-type=TYPE：預設資料表型態。
--delay-key-write[=OFF|ON|ALL]：設定 DELAYED KEYS 是否作用，OFF 用於 關閉，ON 用於啟動，ALL 用於所有 MyISAM 資料表之寫入索引均發生作用。
註：啟用 ALL 時，不能在其他應用程式使用相關資料表，否則可能造成索引 表錯誤。
--flush：當每道 SQL 指令完成後，立即將變更資料寫入磁碟。正常情況 下，MySQL 在每道 SQL 指令完成後，將執行一次寫入動作，而作業系 統也會同步處理磁碟寫入動作。
--init-file=FILE：啟動伺服器時，同時執行 FILE 內之 SQL 指令。
--innodb-safe-binlog：確保 InnoDB 資料表和 Binary Log 檔維持一致性。
--log[=FILE](或 –l [FILE])：將連線和查詢信息記錄在 FILE 檔案內，若 未指名檔案，MySQL 將以【主機名.log】當檔名。
--log-bin[=FILE]：將所有造成資料變更之查詢記錄在 FILE 檔案內，若未 指名檔案，MySQL 將以【主機名-bin】當檔名。檔名不要加上延伸名，
MySQL 會自動冠上流水號。此日誌檔主要用於備援用途，紀錄日誌檔 時，整體效率會降低 1％左右。
--log-bin-index[=FILE]：查詢結果日誌檔之索引檔，用於指名日誌檔之先 後順序，若未指明檔案，MySQL 將以【主機名-bin.index】當檔名。
--log-error[=FILE]：將錯誤及伺服器啟動信息記錄在 FILE 檔案內，若未 指名檔案，MySQL 將以【主機名.err】當檔名。
--log-isam[=FILE]：將所有 ISAM 和 MyISAM 資料表之變更信息記錄在 FILE 檔案內，僅用於除錯。
--log-long-format ： 啟 用 相 關 日 誌 檔 時 ， 預 設 以 此 格 式 紀 錄 資 料 ， 包 括 Username 和 timestamp。
--log-short-format： 啟 用 相 關 日 誌 檔 時 ， 改 用 短 格 式 紀 錄 資 料 ， 不 紀 錄 Username 和 timestamp。
--log-queries-not-using-indexes：設定’log-slow-queries’參數時，搭配此參 數可將無索引表可用之查詢指令記錄在 log-slow-queries 日誌檔。
--log-slow-queries[=FILE] ： 查 詢 時 間 超 過 預 設 值 ’long-query-time’ 之 指 令，將記錄在 FILE 日誌檔。
--log--warning(或 W)：將警告信息輸出在錯誤日誌檔。
--low-priority-updates：設定變更資料表紀錄之操作（諸如 Insert, Replace, Delete, Update）優先權低於 Select，此參數可以針對個別資料表操作指 令來設定。
--myisam-recover[=Option[,Option2…]]]：設定MyISAM資料表復 ↑H
原模式，可搭配 Default, Backup, Force, Quick 等參數，參數可複選，中 間以逗號間隔。MySQL 建議使用參數為：Backup,Force。各參數之意義 如下述：
Default：形同不加參數。
Backup：復原資料前，先將 TBL_Name.MYD 備份在 TBL_Name.BAK 檔。
Force：強制復原資料，縱使會流失部份紀錄。
Quick：假如無刪除記錄區塊，可不檢查紀錄。
--open-files-limit=COUNT： 設 定 可 開 啟 資 料 表 總 數 ， 假 如 未 給 COUNT 值或設為 0 時，mysqld 將使用 max-connection*5 或 max-connections + table-cache*2 之值來設定（取兩者較大值）。假如 mysqld 提示 ”Too many open files.”，你應該試著加大此參數值。
--safe-User-create：無 Insert 權限的人，不能使用 Grant 將權限授與其他 人。
--skip-bdb：BDB 儲存引擎失效。
--skip-concurrent-insert：MyISAM 資料表不能同時執行 Insert 和 Select。
--skip-delay-key-write：忽略 DELAY_KEY_WRITE 指令，可用 --delay-key-write = OFF 替代。
--skip-grant-tables： 伺 服 器 不 做 任 何 資 料 庫 驗 證 。 執 行 Flush Privileges 指令後，將恢復驗證工作。
--skip-Host-cache：不使用快取來解析 Name-to-IP，直接透過 DNS 伺服器 來查詢連線主機資料。
--skip-innodb：InnoDB 儲存引擎失效。
--skip-isam：ISAM 儲存引擎失效。
--skip-name-resolve：不以名稱解析主機資料，改以 IP 解析。
--skip-networking：不以 TCP/IP 協定連線。
--skip-show-database：無 Show Databases 權限的人，無法查閱資料庫相 關資訊。
--skip-stack-trace：不寫入追蹤信息，使用於除錯模式時。
--skip-thread-proiority：執行緒優先權失效。
--sql-mode=VALUE：設定 SQL 模式給 MySQL 用。
--tmpdir=PATH（或 –t PATH）：記憶體不足時，建立之暫存表檔案將指 向磁碟之 PATH 路徑內。
--User=User_Name：設定連線帳號。
--version（或 -V）：顯示版本信息後離開。
↑H
3.3 日誌檔管理
3.3.1 產生日誌檔
啟動 mysqld 伺服器時，可搭配系統環境參數將執行過程之信息記錄在 日誌檔，日誌檔類型摘述於表 3.5。所有日誌資料若未指定檔名或路徑，
將使用預設檔名存檔，檔案將存在參數檔 mysqd 區段之預設路徑。前述日 誌檔可用下列指令強制關閉及重新開啟新檔：
¾ SQL 指令：Flush Logs;
¾ 命令列指令：mysqladmin flush-logs, 或 mysqladmin refresh 表 3.5 日誌檔類型
檔案類型 預設檔名 儲存內容
錯誤日誌 主機名.err 記錄伺服器啟動、運作及停止時遭遇的問 題
isam 日誌 自定檔名 用於 ISAM 和 MyISAM 資料表除錯階段 之所有改變資料
查詢日誌 主機名.log 記錄主控端連線及執行 SQL 之相關信 息，可用於追蹤問題發生原因
異動資料 主機名-bin.log 記錄所有會改變資料之 SQL 指令，這些 信息也可用於備用伺服器之資料更新 慢速查詢 主機名
-slow.log
記錄所有查詢耗費時間大於
lon_query_time 之指令，或是未使用索引 表之指令
3.3.2 日誌檔內容 3.3.2.1 錯誤日誌檔
錯誤日誌檔內容包含伺服器啟動與停止時間，以及運作期間發生的錯 誤資料。假如 mysqld 在運作期間發生非預期的當機問題，而 mysqld_safe 需 要 重 新 開 啟 它 時 ， mysqld_safe 會 將 重 新 開 啟 資 料 記 錄 在 日 誌 檔 。 當 mysqld 通知某個資料表需要被自動檢查或修復時，也會將該類資料記錄下 來。在執行 Flush Logs 指令後，日誌檔將被改名為原檔名加字尾-old，後 續紀錄則記錄在原預設檔案內。
3.3.2.2 查詢日誌檔
查詢日誌檔用於記錄主控端連線及執行SQL之相關信息，可用於追蹤 問題發生原因。此日誌檔可用異動資料日誌檔取代（使用--log-bin）。記錄 資料時，將依伺服器收到SQL指令之先後順序，而非執行之先後順 ↑H
序。執行 Flush Logs 指令後，日誌檔名不會被改名。假如你需要保留舊紀 錄，請自行將檔案改名或備份在其它目錄下。注意，使用 Windows 作業系 統時，必須先停止伺服器，查詢日誌檔才能改名。
3.3.2.3 異動資料日誌檔
異動資料日誌檔用於記錄所有會改變資料之 SQL 指令，同時記錄更新 資料耗費的時間。若更新指令不改變資料時，這些指令不會被記錄下來，
假如需要完整紀錄的話，則須改用查詢日誌檔。記錄異動資料日誌檔時，
將使得系統效率將低約 1％，需要復原資料時，這個代價是值得的。
異動資料日誌檔之主要用途，在於資料更新後若需要復原資料，可直 接從日誌檔取得復原資料之相關訊息，這些信息也可用於備用伺服器之資 料更新。
毎次啟動伺服器或執行 Flush Logs 時，系統即自動產生一個新的日誌 檔，檔名均相同，但是延伸名會以流水號遞增。假如日誌檔太大時（大於 預設值 max_binlog_size），系統即自動產生新的日誌檔，若是執行大型交 易型資料的話，日誌資料不會分割在兩個日誌檔。
異動資料日誌檔可搭配--log-bin-index 參數，系統將自動產生一個索引 檔，用來記錄相關日誌檔之關係。當伺服器在運作時，不要去編輯索引檔，
以免伺服器造成混淆。
可 使 用 【 Reset Master 】 指 令 來 刪 除 所 有 異 動 資 料 日 誌 檔 ， 或 使 用
【PURGE MASTER LOGS】來刪除部份異動資料日誌檔，指令型式如下：
PURGE MASTER LOGS TO 'mysql-bin.010';
PURGE MASTER LOGS BEFORE '2003-04-02 22:46:26';
刪除部份異動資料日誌檔時，請遵照下列程序：
9 若有使用備用伺服器(slave server), 請用 SHOW SLAVE STATUS 指令查 看那個日誌檔正被讀取中。
9 請用 SHOW MASTER LOGS 指令查看主伺服器有那些日誌檔。
9 選出最近的日誌檔，該檔將是系統還要使用的目標檔。
9 備份將要刪除的日誌檔。
9 執行刪除日誌檔。
假如有啟動備份伺服器，刪除日誌檔時，請先確認並無備用伺服器還 要 使 用 這 些 日 誌 檔 。 理 想 的 刪 除 規 則 為 ， 每 天 執 行 一 次 【 mysqladmin flush-logs】，然後將三天以上的日誌檔以【Purge Master Logs】指令刪除，
使用Purge指令可同時整理索引檔。 ↑H
記錄資料更新指令時，可針對特定資料庫做選別，選別參數有兩種型 式：
9 --binlog-do-db = DB_Name：若更新指令之處理對象資料庫非現用資 料庫時，該指令不被記錄。
9 --binlog-ignore-db = DB_Name：若更新指令之處理對象資料庫非現 用資料庫時，該指令將被記錄。
上述參數可同時設定多組，若有多組參數時，系統將依下列規則來判 定是否紀錄更新指令。
規則 1：檢查是否有設定參數
無：立即記錄，並離開檢查規則。
有：檢查規則 2。
規則 2：檢查是否有使用 Use 指令重設現用資料庫 無：不做記錄，並離開檢查規則。
有：檢查規則 3。
規則 3：檢查是否有 binlog-do-db
有：現用資料庫是否符合 binlog-do-db 規則 有：立即記錄，並離開檢查規則。
無：不做記錄，並離開檢查規則。
無：檢查規則 4。
規則 4：有設 binlog-ignore-db，檢查現用資料庫是否符合 binlog-ignore-db 規則
有：不做記錄，並離開檢查規則。
無：立即記錄，並離開檢查規則。
擁有 Super 權限的人員，可在主控端執行【Set SQL_LOG_BIN=0】指 令，關閉其對應更新指令之記錄工作。
可使用下述指令檢視日誌檔內容：。
9 近端伺服器：mysqlbinlog 日誌檔名
9 遠端伺服器：mysqlbinlog 日誌檔名 –R -h 伺服器所在主機名稱 也可以使用下述指令更新特定的伺服器：
mysqlbinlog 日誌檔名| mysql -h 伺服器名
執行資料更新指令時，預設情況是以非同步方式寫入資料到日誌檔，
當系統發生問題時，可能會造成最後一道指令流失。為避免發生此問題，
可將狀態變數sync_binlog設為 1，使之同步，但會拖慢系統速度。 ↑H
3.3.2.4 慢速查詢日誌檔
所有查詢指令處理時間超過狀態變數 long_query_time 之設定值者，將 被記錄在慢速查詢日誌檔。當此類指令執行完畢，且相關的鎖定動作也被 釋 放 ， 此 類 指 令 才 會 被 記 錄 ， 紀 錄 順 序 未 必 會 與 執 行 順 序 相 同 。 搭 配 long-log-format 參數時，未使用索引表之指令也會被記錄下來。記錄在此 檔案內之指令，應該考慮予以最佳化處理。
3.4 伺服器管理命令－mysqladmin
3.4.1 用途與語法
mysqladmin 是一個主控端應用程式，用來執行伺服器之管理作業，諸如伺 服器之環境設定和提示現狀信息，增刪資料庫，以及其它作業。
執行 mysqladmin 之語法如下：
c:\mysql\bin> mysqladmin [options] command [command-option] command ...
其中[options]為選項參數，
command 為操作資料庫指令，
[command-option] 為指令選項參數。
3.4.2 支援指令 3.4.2.1 指令名稱
create db_name：建立指名的資料庫。
drop db_name：刪除指名的資料庫。
extended-status ：提示伺服器使用的環境變數與設定值。
flush-Hosts：沖回主機快取資訊。
flush-logs：沖回所有日誌檔。
flush-privileges：重新載入權限資料表。
flush-status：清除狀態變數。
flush-tables：沖回所有資料表。
flush-threads：沖回所有執行緒快取。
kill id,id,...：刪除伺服器指定執行緒。
old-password new-password：將密碼存成舊格式（16 位元組）。
password new-password：變更密碼。
ping：測試伺服器是否仍活著。 傳回 0 表示拒絕連線，1 表示仍活著。
processlist：提示連線中的執行緒清單。與 SQL 指令 SHOW PROCESSLIST 或 SHOW FULL PROCESSLIST 等效。
reload ：重新載入權限資料表。
refresh：沖回所有資料表，然後關閉及重開日誌檔。 ↑H
shutdown：停止伺服器。
start-slave：起動備用伺服器。
status：提示伺服器狀態信息。
stop-slave：停止備用伺服器。
variables：提示伺服器系統變數及設定值。
version：提示版次信息。
3.4.2.2 指令用法
可用前四碼之縮寫格式，例如：
c:\mysql\bin> mysqladmin proc stat 3.4.2.3 輸出信息之關鍵字意義
Uptime：MySQL 伺服器運作時間（以秒計）。
Threads：主控端連線執行緒總數。
Questions：主控端送出的查詢指令總數。
Slow queries：查詢時間過長之查詢指令總數。
Opens：開啟資料表總數。
Flush tables：執行 flush ..., refresh, 和 reload 指令總數。
Open tables：目前被開啟的資料表總數。
3.4.2.4 選項參數
--help(或 -? )：提示相關使用信息。
--compress(或 -C )：若主從端均支援壓縮協定，所有在主從端傳送的資料都加 以壓縮。
--count=#(或 -c # )：設定重複回合數，需搭配--sleep (-i)參數使用。
--debug[=debug_options] (或 -# [debug_options] )：寫入除錯日誌檔，
debug_options 參數可指定為 'd:t:o,file_name' ，預設為 'd:t:o,/tmp/mysqladmin.trace'。
--default-character-set=charset：設定預設字元即集。
--force(或 -f )：強制執行相關指令。
--Host=Host_name(或 -h Host_name )：設定連線主機。
--password[=password] (或 -p[password] )：連線登入密碼。
--port=port_num(或 -P port_num )：設定通訊埠。
--protocol={TCP | SOCKET | PIPE | MEMORY}：選用通訊協定。
--relative(或 -r )：提示前後次之設定差異值。
--silent(或 -s )：未能連線時，不提示任何信息即離開。
--sleep=delay(或 -i delay)：依序執行指令時，設定中間之暫停時間。
--User=User_name(或 -u User_name )：設定連線帳號。 ↑H
--verbose(或 -v )：輸出完整的執行過程之信息。
--version(或 -V )：提示版次信昔息。
--vertical(或 -E )：以垂直方式輸出信息。
--wait[=#](或 -w[#] )：未能成功連線時等候次數，預設為 1 次。
3.5 災難預防與復原
本節探討重點分成資料庫備份和資料表復原兩部份，備份資料庫可預 防因突發事故造成資料無法復原之損失，適時的維護資料表，則有助於提 升系統效率。
3.5.1 資料庫備份
備份資料庫之方式，可分成完整、漸增或局部資料表三種情形。完整 備份時，必須確認資料庫已無任何執行緒使用它，而資料表備份則須確認 所有異動資料含索引都已被完整寫回磁碟。由於 MySQL 資料表是以檔案 形式儲存，所以很容易進行局部備份。
3.5.1.1 前置處理
為了使備份資料能維持一致性，備份前應先執行下列指令，將相關資 料表之索引資料寫回磁碟。
Lock Tables 資料表 1 Read, 資料表 2 Read …;
Flush Tables With Read Lock;
Unlock Tables;
可使用下列指令來檢視 MyISAM 資料表之狀態信息：
myisamchk –d 資料表名：提示資料表一般資訊。
myisamchk –d -v 資料表名：提示資料表一般資訊及檔案配置資訊。
myisamchk –eis 資料表名：提示資料表重要資訊，須檢視整個資料表，所以較 費時。
myisamchk –eiv 資料表名：與-eis 類似，但索引結構資訊更詳盡。
3.5.1.2 完整備份
執行完整備份時，請先停止伺服器，然後重新啟動並搭配 ”log-bin” 參 數，重新設定備份日誌起始點，接者以主控端應用程式 mysqldump（在命令 列下執行），執行完整備份工作，備份結果為一專案檔形式。mysqldump 可 搭配下列參數來備份資料：
--help(或 -?)：顯示參數意義。
--add-drop-table：加入 DROP TABLE 指令在 CREATE TABLE 之前。
--add-locks：加入LOCK TABLES 和UNLOCK TABLES 指令在Insert Into指 令前後，將來復原資料時可加快處理速度。 ↑H
--all-databases(或 –A)：下載所有資料庫及所有資料表。
--comments[={0|1}]：預設值為 1，設為 0 時，與--skip-comments 等效(不加註 解) 。
--complete-insert(或 –c)：使用完整的 INSERT 指令，包含欄名。
--create-options：加入完整的 CREATE TABLE 指令。
--databases(或 –B)：資料庫變更時，加入 USE db_name 指令。
--default-character-set=charset：設定預設字元集。
--delayed：使用 INSERT DELAYED 型式之指令。
--disable-keys(或 –K)：在 INSERT 指令前後加上後序指令： /*!40000 ALTER TABLE tbl_name DISABLE KEYS */; 和 /*!40000 ALTER TABLE tbl_name ENABLE KEYS */;
--extended-insert(或 –e)：使用多值組(VALUES lists) 之 INSERT 指令。
--fields-terminated-by=.../ --fields-enclosed-by=... /
--fields-optionally-enclosed-by =... / --fields-escaped-by=... /
--lines-terminated-by =... ：左列參數需搭配 –T 參數，其用法與 LOAD DATA INFILE 指令之修飾子句類似。
--flush-logs(或 –F)：沖回伺服器日誌檔，此參數最好與--lock-all-tables 或 --master-data 一起使用。
--force(或 -f )：當 SQL 指令發生錯誤時，繼續資料表之下載。
--Host=Host_name(或 -h Host_name )：下載資料至指定主機，預設為 LocalHost。
--lock-all-tables(或 -x )：鎖定所有資料庫之所有資料表。
--lock-tables(或 –l)：資料表將以 READ LOCAL 型式上鎖。
--master-data[=value]：強制將日誌檔之位置和檔名加在輸出檔後，若 Value 設為 1，則資料將以 CHANGE MASTER 指令型式加入，假如你以 SQL 指令直接從主伺服器備份資料至備用伺服器時，備用伺服器將可以從正 確位置開始儲存資料。若 Value 設為 2（此為預設值），則 CHANGE MASTER 指令將被改成 SQL 型式之註解。
--no-create-db(或 -n )：剔除 CREATE DATABASE /*!32312 IF NOT EXISTS*/
db_name 指令（保留原資料庫）。
--no-create-info(或 -t )：剔除 CREATE TABLE 指令（保留原資料表）。
--no-data(或 -d )：不下載資料（只下載資料表結構）。
--opt：此指令代替 --add-drop-table, --add-locks, --create-options, --disable-keys , --extended-insert, --lock-tables, --quick 及 --set-charset 之組合，此指令為 MySQL 4.1 版之預設值。
--password[=password](或 -p[password] )：連線密碼。
--port=port_num(或 -P port_num)：通訊埠。
--protocol={TCP | SOCKET | PIPE | MEMORY}：通訊協定。 ↑H
--quick(或 -q )：下載大型資料表時，可加快速度。
--result-file=file(或 -r file)：file 為輸出檔名， Windows 作業系統須加上此參 數。
--skip-comments：下載資料不加註解。
--tab=path(或 -T path) ：替每一個資料表建立一個內容為 CREATE TABLE 指令之專案檔，檔名為`tbl_name.sql'，下載資料則存在`tbl_name.txt'之檔 案內。
--tables：覆蓋以 --databases 或 –B 所產生之前導參數，前導參數改為資料表 名。
--User=User_name(或 -u User_name)：帳號。
--xml(或 -X )：輸出格式改為 XML。
3.5.1.3 完整備份範例
範例 1：c:\mysql\bin> mysqldump --opt db_name -r backup-file.sql。
說明：將資料庫 db_name 以 mysqldump(搭配 opt 參數)完整下載，並儲存在專案 檔 backup-file.sql。此專案檔可用下列指令將將資料庫 db_name 以 mysql 命 令列指令建立在其它主機上。
建檔指令：c:\mysql\bin> mysql db_name < backup-file.sql 範例 2：直接從特定伺服器資料下載至另一伺服器上。
指令：c:\mysql\bin> mysqldump --opt db_name | mysql --Host=遠端主機 -C db_name
範例 3：一次將數個資料庫下載至專案檔。
指令：c:\mysql\bin> mysqldump --databases 資料庫 1 [資料庫 2 ...] -r my_databases.sql
範例 4：一次將所有資料庫下載至專案檔。
指令：c:\mysql\bin> mysqldump --all-databases -r all_databases.sql 範例 5：若儲存引擎為 InnoDB 時，改用下列指令：
指令：c:\mysql\bin> mysqldump --all-databases --single-transaction -r all_databases.sql
3.5.1.4 漸增式備份
採用漸增式備份時，伺服器須在--log-bin模式下執行，以便取得相關 的備份線索資訊（前次完整備份或漸增備份之完成位置）。 執 行 備 份 工 作 前，先執行Flush Logs指令以轉回適當之日誌檔，然後拷貝所須的日誌檔
（前次為完整備份時，拷貝全部；若為漸增式，可能只需拷貝一個最近的 日誌檔）。 ↑H
3.5.1.5 局部備份
所謂局部備份，乃針對個別資料表加以備份，可以使用下列指令來處 理此項工作：
指令 1：BACKUP TABLE tbl_name [, tbl_name] ... TO '/path/to/backup/directory';
說明：此指令可同時拷貝數個資料表至指定目錄，指名資料表所屬之.FRM 及.MYD 檔案將被複製，.MYI 可利用前面兩個檔案來重新索引後取得。
指令 2：SELECT * INTO OUTFILE 'file_name' FROM tbl_name;
3.6 復原資料
3.6.1 重建資料庫
指令：c:\mysql\bin> mysql db_name < backup-file.sql
說明：backup-file.sql 是以 mysqldump 命令產生之專案檔。
3.6.2 復原資料表
指令 1：RESTORE TABLE tbl_name [, tbl_name] ... FROM '/path/to/backup/directory';
說明：將以 Backup 指令備份的資料表復原，只能用於資料表不存在的情況，若 資料表已存在時，將產生錯誤。
指令 2：LOAD DATA INFILE FILE_name REPLACE…;
說明：將以 SELECT INTO 指令備份的資料，重新載入對應的資料表。若資料表 有 Primary 或 Unique 型索引表，舊鍵值紀錄將被新紀錄取代。
復原資料時，請先以 Repair Table 資料表或 mysqlaheck –r 資料表指令檢查 資料表，99.9﹪的 MyISAM 資料表問題都可以克服。若無法解決問題時，請改 用下列方式處理：
¾ 首先重新載入前次以 mysqldump 下載之備份資料。
¾ 執行下列指令來更新資料
c:\mysql\bin>mysqlbinlog 對應日誌檔名 | mysql 3.6.3 資料表維護與損壞復原
本節介紹資料表之定期維護觀念，常用維護指令之用法，以及損壞時 之復原方式。雖然 MySQL 提供的修復指令非常安全，但還是建議讀者養 成定期備份資料之習慣，以策安全。以下簡述常用的資料表檢修指令語法 及用途。
3.6.3.1 分析資料表指令(ANALYZE TABLE ) ↑H
語法：ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...
說明：可用於分析資料表及重建索引表，事前須先以讀取鎖定方式鎖表。適用於 所有型態資料表。
3.6.3.2 檢查資料表指令(CHECK TABLE)
語法：CHECK TABLE tbl_name [, tbl_name] ... [option] ...
說明：可用於檢查資料表及或提示錯誤資訊，適用於所有 MyISAM 和 InnoDB 型態資料表。其中 option 有下列選項，代表意義請見表 3.6，參數可複選。
{QUICK | FAST | MEDIUM | EXTENDED | CHANGED}
表 3.6 檢查資料表選項參數 型式 代表意義
QUICK 不做資料列掃描以檢查不正確之連結 FAST 只檢查未正常關閉的資料表
CHANGED 只檢查資料有變更或未正常關閉的資料表
MEDIUM 資料列掃描以維持正確之連結，同時檢查紀錄與索引之 checksum
EXTENDED 完整檢查，需耗費很長的時間
3.6.3.3 整理資料表指令(OPTIMIZE TABLE)
語法：OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...
說明：紀錄為不定長度的資料表，有大量資料被刪除時，可用此道指令將紀錄重 新連結（刪除空洞）。前述資料表若經常需要更新處理時，可於每週或每 月處理一次 OPTIMIZE。
OPTIMIZE TABLE 指令將依下述過程來處理資料表：
9 檢查是否有刪除位置或紀錄被分段儲存，有則修復它。
9 檢查索引是否未排序，若未排序則重新排序。
9 檢查統計資料是否正確，不正確則更正之。
3.6.3.4 修復資料表指令(REPAIR TABLE)
語法：REPAIR [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ... [QUICK] [EXTENDED] [USE_FRM]
說明：此道指令只適用於MyISAM資料表，可用於修復損壞的資料表。其中各選 項參數意義簡述如下： ↑H
QUICK：只修復索引樹。
EXTENDED：以逐列方式重建索引。
USE_FRM：依據`.FRM'檔案重建索引表。
3.6.3.5 mysqlcheck
前述指令也可改用 mysqlchk 主控端命令來處理，此命令只用來處理 MyISAM 資料表。執行命令時，伺服器不需要停止。可使用下列型式來執行命 令：
c:\mysql\bin> mysqlcheck [options] db_name [tables]
c:\mysql\bin> mysqlcheck [options] --databases DB1 [DB2 DB3...]
c:\mysql\bin> mysqlcheck [options] --all-databases
若不指名資料表，或是搭配 --databases 或 --all-databases 選項參數時，所 有的資料庫都會被檢查。假如希望 mysqlcheck 可專用於 Repair, Analyze, 或 Optimize，可以將 mysqlcheck 複製後，並分別改名為 mysqlrepair(可省略 –r 參數), mysqlanalyze(可省略 –a 參數), mysqloptimize(可省略 –o 參數)。
mysqlcheck 可搭配下列選項參數使用：
--help(或 -? )：提示使用資訊
--all-databases(或 -A)：檢查全部資料表。
--all-in-1(或 -1) ：檢查指名資料庫內的全部資料表。
--analyze(或 -a) ：分析資料表。
--auto-repair：資料表若有損壞則自動修復它，修復工作會在檢查全部資料表後 進行。
--check(或 -c)：檢查資料表。
--check-only-changed(或 -C)：只檢查最近有變更資料或未正常關閉的資料表。
--compress：若主從端均支援壓縮協定，所有在主從端傳送的資料都加以壓縮。
--databases(或 -B)：檢查指名資料庫內的所有資料表。
--debug[=debug_options] (或 -# [debug_options])：記錄除錯信息，debug_options 通常會加上 'd:t:o,file_name'。
--default-character-set=charset：預設字元集。
--extended(或 -e )：完整檢查，除了耗時，也會產生一堆垃圾信息，可檢查出 100% 的錯誤。
--fast(或-F)：只檢查未正常關閉的資料表。
--force(或 -f)：縱使 SQL 指令有誤，強制完成檢查工作。
--Host=Host_name(或-h Host_name )：指名連線主機。
--medium-check(或 -m)：速度會比 --extended 模式快，可檢查出 99.99% 的錯 誤。
--optimize(或 -o )：整理資料表。 ↑H
--password[=password] (或-p[password])：設定登入密碼。
--port=port_num(或 -P port_num )：設定通訊埠。
--protocol={TCP | SOCKET | PIPE | MEMORY}：指定通訊協定。
--quick(或 -q )：只檢查索引樹，為最快之檢查模式。
--repair(或 -r)：修復資料表，除了無法檢查唯一鍵，所有錯誤都可處理。
--silent(或 -s )：只輸出錯誤信息。
--tables：指名資料表。
--User=User_name(或-u User_name：連線登入帳號。
--verbose(或 -v)：輸出各階段之信息。
--version(或 -V) ：提示版次信息。
3.7 設定維護排程計劃
永遠記住「預防重於修理」的道理，不要在問題發生後，才匆匆忙忙 的去尋找問題發生的原因，然後以急就章的方式去解決問題，對於資料處 理人員而言，這絕對是個夢魘。建議讀者在系統上線前，擬妥一份維護排 程，然後定期檢查或分析相關的資料表，讓所有的資料表隨時都能維持在 正常情況，不但使得資料正確性更高、系統更安全，而且也更有效率。以 下提出一些維護排程的觀念和可行做法：
¾ 對於重要資料表，應定期以 Check, Repair, Optimize 指令檢查。
¾ 搭配 –myisam-recover 參數來起動伺服器，在資料處理人員正式處理資 料表前，即完成資料表檢查和修復工作。
¾ 定期執行維修計畫，例如每天一次（每天均需更新處理的資料表），或每 週一次（較不重要的資料表）。
¾ 經常需要增刪紀錄之資料表，每月處理一次 Optimize。
3.8 備用伺服器
為了提昇系統可靠度，MySQL 提供備用伺服器之機制。除了安裝常態 性的主伺服器外，用戶可依需求安裝一台以上之備用伺服器。備用伺服器 除了可增加資料庫之安全外（在線備份功能）， 當 主 伺 服 器 當 機 時 ， 可 立 即將主機切換至備用伺服器，因而可以大幅降低當機的損失。此節將說明 安裝備用伺服器的步驟及注意事項。
3.8.1 基本需求
安裝備用伺服器須滿足下列條件：
¾ 擁有兩台主機且已安裝 MySQL。
¾ 兩台主機之 MySQL 版本相容。
¾ 設 定 備 用 伺 服 器 的 人 員 用 有 "Super"權 限 ， 且 在 主 伺 服 器 已 設 定 連 線 帳 號，以及授與"Replicate Slave"權限。可使用下列範例指令來授權：↑H
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%.mydomain.com' IDENTIFIED BY 'slavepass';
若需要從主伺服器載入資料，則須另外授與 Select、Super 及 Reload 權限。
3.8.2 備份主伺服器資料庫
為了使備用伺服器之起始狀態能與主伺服同步且一致，須將主伺服器 之相關資料庫備份至備用伺服器，可使用你最熟悉的打包軟體進行備份工 作。假如需保留備份伺服器之 mysql 資料庫設定資料，則不需備份 mysql 資料庫。以下說明備份資料庫之步驟。
Step 1 強制將資料寫回伺服器並上鎖 可使用下列指令處理此項工作：
Flush Tables with Read Lock;
Step 2 備份相關資料庫
Step 3 檢視主伺服器之 binary log 可使用下列指令處理此項工作，
Show Master Status;
系統將提示如表 3.7 之記錄。請記下檔名(File 欄)及位置(Position 欄)，這 些 資 料 在 設 定 備 用 伺 服 器 時 需 要 用 到 。 假 如 主 伺 服 器 未 啟 動 log 處 理 機 制，則表 3.7 之各欄都是空白，在設定備用伺服器時，則須填入檔名為""
空白字串及位置為 4。
表 3.7 Binary Log 紀錄明細
File Position Binlog_Do_DB Binlog_Ignore_DB Mysql_bin.003 73 test Mysal,mysql
Step 4 將資料表解鎖
使用下述指令將資料表解鎖：
Unlock Tables;
3.8.3 建立備用伺服器 Step 5 重設 my.ini 定義
請確認 my.ini 檔之【mysqld】段是否有下述定義，若無則補上，然後 重新起動伺服器。
[mysqld]
log-bin=mysql-bin
server-id=1 ↑H
若有使用 InnoDB，則須在補上下述定義：
innodb_flush_log_at_trx_commit=1 sync_binlog=1
Step 6 重設備用伺服器 my.ini 定義
將作為備用伺服器主機暫停，在 my.ini 檔之【mysqld】段加入下述定 義：
[mysqld]
server-id=2
若 須 設 定 多 個 備 用 伺 服 器 ， 請 仿 照 此 步 驟 依 序 設 定 其 它 主 機 ， 但 server-id 須設為遞增值。
Step 7 拷貝備份資料庫至備用主機
若 以 備 份 檔 案 方 式 拷 貝 者 ， 請 直 接 將 檔 案 拷 入 備 用 主 機 ， 若 以 mysqldump 拷貝者，可在後續步驟載入備用伺服器。
Step 8 啟動備用伺服器
啟動備用伺服器。若該主機曾經設為備用伺服器，則啟動伺服器指令 可 加 上 -–skip-slave-start 選 項 。 若 須 截 取 更 多 訊 息 在 錯 誤 日 誌 檔 (error log)，可加上 -–log-warning 選項。
Step 9 重載備份資料庫至備用主機
若以 mysqldump 拷貝資料庫者，請以下述指令重載資料；否則請直接 登入備用伺服器。
C:\mysql>mysql –u root –p < 備份檔 Step 10 設定連接主伺服器相關屬性
請以下列指令設定連接屬性：
mysql>change master to
->Master_Host=主伺服器主機位址 ->Master_User=連線帳號
->Master_Password=連線密碼
->Master_Log_File=主伺服器日誌檔名（註：在 Step 3 取得的檔 案）
->Master_Log_Pos=主伺服器日誌檔位置;（在 Step 3 取得的檔 案）
Step 11 啟動備用伺服器
請以下列指令啟動備用伺服器：
Mysql> Start Slave; ↑H
一 旦 備 用 伺 服 器 正 常 工 作 ， 在 data 目 錄 內 將 產 生 Master.info 和 Relay-log.info 兩 個 檔 案 ， 備 用 伺 服 器 使 用 兩 個 檔 案 去 追 蹤 主 伺 服 器 之 binary log 之處理過程。請不要修改這兩個檔案之內容。
↑H
課後習題
1. 簡述 MySQL 權限工作原理建立資料庫之指令。
2. MySQL 資料庫使用那三個主要全限資料表？
3. MySQL 資料庫權限分成那些類型？
4. 系統管理人員如何授與用戶 MySQL 資料庫使用權？
5. 如何設定伺服器工作參數？
6. 簡述日誌檔之類型與用途。
7. 如何備份 MySQL 資料庫？
8. 如何復原 MySQL 資料庫？
↑H 回目錄
