第11章伺服器与资料库管理

主從式資料庫系統-CH111第11章

伺服器與資料庫管理主從式資料庫系統-CH112本章宗旨介紹MySQL之權限功能、工作原理與權限類型介紹權限之授權與撤除指令及應用方式簡介伺服器工作環境參數與管理簡介日誌檔產生方式與用途簡介伺服器管理指令簡介災難預防與復原之意義與處理指令Administrator伺服器管理應用軟體使用說明主從式資料庫系統-CH113大綱11-1帳號與存取權限管理11-2伺服器系統環境管理11-3日誌檔管理11-4mysqladmin－伺服器管理命令11-5災難預防與復原11-6設定維護排程計劃11-7Administrator應用簡介主從式資料庫系統-CH11411-1帳號與存取權限管理11-1-1權限的功能11-1-2權限工作原理11-1-3權限類型11-1-4使用者授權與權限撤除11-1-5密碼設定與變更11-1-6啟用權限11-1-7伺服器連線11-1-8主機及其他設定值11-1-9權限驗證主從式資料庫系統-CH11511-1-1權限的功能權限的主要功能認證使用者是否有權登入伺服器執行諸如Select、Insert、Update、Delete等資料處理作業時，認證是否有權開啟資料表和處理資料進階的權限管理功能，在於驗證相關人員是否可以將權限授與他人使用主從式資料庫系統-CH11611-1-2權限工作原理MySQL將存取權限控制分為兩個階段階段一：伺服器先查核使用者是否被允許連線。階段二：假如有連線權利，而且也成功的連線，使用者每發出每一道SQL指令時，MySQL將檢查是否有足夠權限去處理該項作業主從式資料庫系統-CH11711-1-2權限工作原理(續)連線後假如權限有被變更過（不管是自己變更，或是他人變更），變更的權限並不會立即發生作用除非已執行【FlushPrivileges;】指令，MySQL才會重新讀取權限資料並置於記憶體主從式資料庫系統-CH11811-1-2權限工作原理(續)MySQL將所有授權資訊儲存在”mysql”資料庫內的資料表其中tables_priv和columns_priv兩個資料表用來存放資料處理權限資料，這些資料表用於階段二之查核User,Db及Host三個資料表用來存放連線權限資料，這些資料表用於階段一和二之查核主從式資料庫系統-CH11911-1-2權限工作原理(續)權限資料表用法登錄在”user”資料表之使用者，在指定的”host”登入時，這個使用者將可以處理位於伺服器上的資料庫（若所有權限都設為’Y’），此權限形同”Supervisor”權限登錄在”Db”資料表之使用者，在指定的”host”登入時，這個使用者將可以處理指定的資料庫，包含此資料庫內的全部資料表主從式資料庫系統-CH111011-1-2權限工作原理(續)若需要從不同的主機連線來處理指定的資料庫時，可以使用”host”資料表設定主機與資料庫。假如以這個方式工作，請將”Db”資料表之”host”欄位保留空白“tables_priv”與”columns_pri”權限設定意義與”Db”類似，不過它們可以指定更詳細的權限。假如使用者有權處理特定資料表，此意味他也有權處理該資料表之全部欄位主從式資料庫系統-CH111111-1-3權限類型權限使用時機Select,Insert,Update和Delete可用於處理指定資料庫內現存資料表的紀錄操作Create使用於建立新的資料庫或資料表，Drop則用於刪除現存的資料庫或資料表Index使用於建立和刪除索引表，擁有Create資料表權限的人，同時擁有Index權限主從式資料庫系統-CH111211-1-3權限類型(續)權限使用時機Alter使用於改變資料表結構或重新命名Grant可用於將自己擁有的權限授與他人File用於以”LoadDataInfile”和”Select”指令在伺服器上讀寫檔案主從式資料庫系統-CH111311-1-3權限類型(續)主從式資料庫系統-CH111411-1-3權限類型(續)shutdown用於關閉伺服器，無對應的SQL指令可用processlist用於顯示目前正在伺服器上工作的執行緒，使用者隨時都可以顯示自己啟動的執行緒並刪除它，但是只有擁有Process權限的人才能查看由他人啟動的執行緒，而擁有Super權限的人才能使用Kill來刪除由他人啟動的執行緒CreateTemporaryTable權限使用於以關鍵字”TEMPORARY”來建立暫存表主從式資料庫系統-CH111511-1-3權限類型(續)LockTables用來鎖住資料表，擁有Select權限的人，才能執行LocakTables。資料表被鎖定後，其他人無法讀取資料表ReplicationClient用於執行”ShowMasterStatus”和”ShowSlaveStatus”指令ReplicationSlave用於授權給備用伺服器之使用者，而這些人必須已連線至主伺服器，若未經授權，備用伺服器將無法要求更新主伺服器之資料主從式資料庫系統-CH111611-1-3權限類型(續)ShowDatabases用於檢視指名的資料庫之全部資訊若無此權限，執行此指令只能看到那些有被授權之資料表相關資訊假如啟動伺服器時，若有搭配”--skip-show-database”參數，縱使有此授權，也無法檢視資料庫相關資訊主從式資料庫系統-CH11171.完整授權1.1授權給使用者”monty”，以驗證密碼”Happy”從localhost登入，並享有Grant權限（形同monty具有supervisor之權限）指令：GRANTALLPRIVILEGESON*.*TO'monty'@'localhost'IDENTIFIEDBY'Happy'WITHGRANTOPTION;1.2避免匿名者權限會蓋掉monty在Localhost之權限，應增加一組權限，如下：指令：GRANTALLPRIVILEGESON*.*TO'monty'@'%'IDENTIFIEDBY'Happy'WITHGRANTOPTION;主從式資料庫系統-CH11181.完整授權(續)主從式資料庫系統-CH11191.完整授權(續)1.4替使用者”dummy”預設一個從Localhost登入之帳戶，但不給他任何權限（使用Usage參數之效果），權限則可留待以後再以部份授權方式加入指令：

GRANTUSAGEON*.*TO'dummy'@'localhost';主從式資料庫系統-CH11202.部份授權主從式資料庫系統-CH11212.部份授權(續)主從式資料庫系統-CH11222.部份授權(續)範例四：INSERTINTOdb(Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv)VALUES('','expenses','custom','Y','Y','Y','Y','Y','Y');啟用權限指令：FLUSHPRIVILEGES;主從式資料庫系統-CH11233.撤除權限

3.1撤除帳號

指令一：DropUser‘帳號1’,‘帳號2’…;指令二：REVOKEpriv_type[(column_list)][,priv_type[(column_list)]]...ON{tbl_name|*|*.*|db_name.*}FROM帳號1[,帳號2]...指令三：REVOKEALLPRIVILEGES,GRANTOPTIONFROMuser[,user]...指令四：DELETEFROMmysql.userWHEREUser=’'帳號’andHost='host_name';主從式資料庫系統-CH112411-1-5密碼設定與變更主從式資料庫系統-CH112511-1-5密碼設定與變更(續)主從式資料庫系統-CH112611-1-5密碼設定與變更(續)主從式資料庫系統-CH112711-1-6啟用權限MySQL啟用全部或部份權限方式起動mysqld時，所有權限資料表將被載入記憶體，所有權限將自動生效當伺服器重新載入權限資料表時，下列連線中主控端之權限將受到影響：主控端在下一次執行資料表或欄位資料時，資料表或欄位權限才生效主控端在下一次執行UseDatabase時，資料庫權限才生效主控端在下一次連線時，完整權限和新的密碼才生效主從式資料庫系統-CH112811-1-6啟用權限(續)主從式資料庫系統-CH112911-1-7伺服器連線主從式資料庫系統-CH113011-1-8主機及其他設定值user資料表上之host欄位，其輸入資料可使用下列型式使用主機名稱、IP代碼，或是localhost使用主機名稱型式時，可搭配”%”萬用字元，例如單一字元”%”表示所有主機，”%.”表示符合””網域的所有主機使用IP型式時，也可以搭配”%”萬用字元，例如”144.155.166.%”主從式資料庫系統-CH113111-1-8主機及其他設定值(續)使用IP型式時，可搭配遮罩格式例如”/”，此意味從主機””到”55”，都被視為合法主機db資料表之host欄位若為空白時，將以host資料表中對應之host與db紀錄作為權限依據。若db欄位保留空白，將被視為”%”型式主從式資料庫系統-CH113211-1-8主機及其他設定值(續)其他權限資料表之host欄若保留空白，將被視為”%”型式user權限資料表之user欄若保留空白，將被視為匿名帳號tables_priv和columns_priv之db,table_name,column_name三個欄位不得使用”%”字元，也不能保留空白主從式資料庫系統-CH113311-1-9權限驗證階段一之權限驗證過程如下讀取user資料表紀錄依據host及user將紀錄排序比對主機，若有相符之主機資料，取出帳號及密碼與登入資料比對，資料皆符合時，即通過驗證主從式資料庫系統-CH113411-2伺服器系統環境管理11-2-1系統環境參數設定系統參數值設定方式分成下列三種型式，型式一用於命令列，型式二用於參數檔，型式三用於應用程式。型式一：--參數名稱=參數值，或--參數名稱型式二：參數名稱=參數值，或參數名稱型式三：SETGLOBAL參數名稱=參數值；主從式資料庫系統-CH113511-2-1系統環境參數設定

下列參數格式具有相同效果關閉效果--disable-column-names--skip-column-names--column-names=0開啟效果--column-names--enable-column-names--column-names=1主從式資料庫系統-CH113611-2-2系統環境參數型式各種常用系統環境參數請參見書本說明主從式資料庫系統-CH113711-3日誌檔管理11-3-1產生日誌檔11-3-2日誌檔內容主從式資料庫系統-CH113811-3-1產生日誌檔啟動mysqld伺服器時，可搭配系統環境參數將執行過程之信息記錄在日誌檔日誌檔可用下列指令強制關閉及重新開起新檔SQL指令：FlushLogs;命令列指令：mysqladminflush-logs,或mysqladminrefresh主從式資料庫系統-CH1139日誌檔類型檔案類型預設檔名儲存內容錯誤日誌主機名.err記錄伺服器啟動、運作及停止時遭遇的問題isam日誌自定檔名用於ISAM和MyISAM資料表除錯階段之所有改變資料查詢日誌主機名.log記錄主控端連線及執行SQL之相關信息，可用於追蹤問題發生原因異動資料主機名-bin.log記錄所有會改變資料之SQL指令，這些信息也可用於備用伺服器之資料更新慢速查詢主機名-slow.log記錄所有查詢耗費時間大於lon_query_time之指令，或是未使用索引表之指令主從式資料庫系統-CH114011-3-2日誌檔內容1.錯誤日誌檔錯誤日誌檔內容包含伺服器啟動與停止時間，以及運作期間發生的錯誤資料2.查詢日誌檔查詢日誌檔用於記錄主控端連線及執行SQL之相關信息，可用於追蹤問題發生原因3.異動資料日誌檔異動資料日誌檔記錄所有會改變資料之SQL指令，同時記錄更新資料耗費的時間4.慢速查詢日誌檔所有查詢指令處理時間超過狀態變數”long_query_time”設定值者，將被記錄在慢速查詢日誌檔主從式資料庫系統-CH1141異動資料日誌檔異動資料日誌檔之主要用途，在於資料更新後若需復原資料，可直接從日誌檔取得復原資料之相關訊息，這些信息也可用於備用伺服器之資料更新毎次啟動伺服器或執行FlushLogs時，系統即自動產生一個新的日誌檔，檔名均相同，但是延伸名會以流水號遞增假如日誌檔太大時（大於預設值max_binlog_size），系統即自動產生新的日誌檔主從式資料庫系統-CH1142異動資料日誌檔(續)執行大型交易型資料的話，日誌資料則不會被分割在兩個日誌檔異動資料日誌檔可搭配”--log-bin-index”參數，系統將自動產生一個索引檔，用來記錄相關日誌檔之關係。當伺服器在運作時，不要去編輯索引檔，以免伺服器造成混淆主從式資料庫系統-CH1143異動資料日誌檔(續)可使用【ResetMaster】指令來刪除所有異動資料日誌檔，或使用【PURGEMASTERLOGS】來刪除部份異動資料日誌檔指令：PURGEMASTERLOGSTO'mysql-bin.010';PURGEMASTERLOGSBEFORE'2003-04-0222:46:26';主從式資料庫系統-CH1144異動資料日誌檔(續)刪除部份異動資料日誌檔程序SHOWSLAVESTATUS指令查看那個日誌檔正被讀取中SHOWMASTERLOGS指令查看主伺服器有那些日誌檔選出最近的日誌檔，該檔將是系統還要使用的目標檔備份將要刪除的日誌檔執行刪除日誌檔主從式資料庫系統-CH1145異動資料日誌檔(續)刪除日誌檔時，請先確認無備份伺服器還要使用這些日誌檔理想的刪除規則為，每天執行一次【mysqladminflush-logs】，然後將三天以上的日誌檔以【PurgeMasterLogs】指令刪除Purge指令可同時整理索引檔。主從式資料庫系統-CH1146異動資料日誌檔(續)記錄資料更新指令時，可針對特定資料庫做選別，選別參數有兩種型式--binlog-do-db=DB_Name：若更新指令之處理對象資料庫非現用資料庫時，該指令不被記錄--binlog-ignore-db=DB_Name：若更新指令之處理對象資料庫非現用資料庫時，該指令將被記錄主從式資料庫系統-CH1147異動資料日誌檔(續)使用下述指令檢視日誌檔內容：。近端伺服器：mysqlbinlog日誌檔名遠端伺服器：mysqlbinlog日誌檔名–R

-h

伺服器所在主機名稱或使用下述指令更新特定的伺服器：mysqlbinlog日誌檔名|mysql-h伺服器名主從式資料庫系統-CH114811-4mysqladmin

伺服器管理命令11-4-1用途與語法mysqladmin是一個主控端應用程式，用來執行伺服器之管理作業，諸如伺服器之環境設定和提示現狀信息，增刪資料庫，以及其他作業主從式資料庫系統-CH114911-4-1用途與語法mysqladmin語法：c:\mysql\bin>mysqladmin[options]command[command-option]command...其中[options]為選項參數command為操作資料庫指令[command-option]為指令選項參數指令型式及參數選項請參看書本說明主從式資料庫系統-CH115011-5災難預防與復原

備份資料庫可預防因突發事故造成資料無法復原之損失適時的維護資料表，則有助於提升系統效率主從式資料庫系統-CH115111-5災難預防與復原(續)11-5-1資料庫備份備份資料庫之方式，可分成完整、漸增或局部資料表三種情形完整備份時，必須確認資料庫已無任何執行緒使用它資料表備份時，須確認所有異動資料含索引已被完整寫回磁碟主從式資料庫系統-CH11521.前置處理為了使備份資料能維持一致性，備份前應先執行下列指令，將相關資料表之索引資料寫回磁碟LockTables資料表1Read,資料表2Read…;FlushTablesWithReadLock;UnlockTables;主從式資料庫系統-CH11531.前置處理(續)下列指令可用來檢視MyISAM資料表之狀態信息myisamchk–d資料表名：提示資料表一般資訊myisamchk–d-v資料表名：提示資料表一般資訊及檔案配置資訊myisamchk–eis資料表名：提示資料表重要資訊，須檢視整個資料表，所以較費時。myisamchk–eiv資料表名：與-eis類似，但索引結構資訊更詳盡主從式資料庫系統-CH11542.完整備份執行完整備份時，請先停止伺服器，然後重新啟動並搭配”log-bin”參數，重新設定備份日誌起始點完整備份指令以主控端應用程式mysqldump（在命令列下執行）執行完整備份工作備份結果為一專案檔型式主從式資料庫系統-CH11553.完整備份範例範例1：c:\mysql\bin>mysqldump--optdb_name-rbackup-file.sql說明：將資料庫db_name以mysqldump(搭配opt參數)完整下載，並儲存在專案檔backup-file.sql。此專案檔可用下列指令，將將資料庫db_name以mysql命令列指令，建立在其他主機上建檔指令：c:\mysql\bin>mysqldb_name<backup-file.sql主從式資料庫系統-CH11563.完整備份範例(續)範例2：直接從特定伺服器資料下載至另一伺服器上指令：c:\mysql\bin>mysqldump--optdb_name|mysql--host=遠端主機-Cdb_name範例3：一次將數個資料庫下載至專案檔指令：c:\mysql\bin>mysqldump--databases資料庫1[資料庫2...]-rmy_databases.sql主從式資料庫系統-CH11573.完整備份範例(續)範例4：一次將所有資料庫下載至專案檔。指令：c:\mysql\bin>mysqldump--all-databases-rall_databases.sql範例5：若儲存引擎為InnoDB時，改用下列指令：指令：c:\mysql\bin>mysqldump--all-databases--single-transaction-rall_databases.sql主從式資料庫系統-CH11584.漸增式備份採用漸增式備份時，伺服器須在—log-bin模式下執行，以便取得相關的備份線索資訊（前次完整備份或漸增備份之完成位置）執行備份工作前，先執行FlushLogs指令，轉回適當之日誌檔拷貝所須的日誌檔前次為完整備份時，拷貝全部若為漸增式，可能只需一個最近的日誌檔主從式資料庫系統-CH11595.局部備份局部備份乃針對個別資料表加以備份使用下列指令來局部備份指令1：BACKUPTABLEtbl_name[,tbl_name]...TO‘/path/to/backup/directory’;說明：此指令可同時拷貝數個資料表至指定目錄，指名資料表所屬之`.frm‘及`.MYD’檔案將被複製，`.MYI‘可利用前面兩個檔案來重新索引後取得指令2：SELECT*INTOOUTFILE'file_name'FROMtbl_name;主從式資料庫系統-CH116011-5-2復原資料1.重建資料庫指令：c:\mysql\bin>mysqldb_name<backup-file.sql說明：backup-file.sql是以mysqldump命令產生之專案檔主從式資料庫系統-CH116111-5-2復原資料(續)2.復原資料表指令1：RESTORETABLEtbl_name[,tbl_name]...FROM‘/path/to/backup/directory’;說明：將以Backup指令備份的資料表復原，只能用於資料表不存在的情況，若資料表已存在時，將產生錯誤指令2：LOADDATAINFILEFILE_nameREPLACE…;說明：將以SELECTINTO指令備份的資料，重新載入對應的資料表。若資料表有Primary或Unique型索引表，舊鍵值紀錄將被新紀錄取代主從式資料庫系統-CH116211-5-2復原資料(續)復原資料時，請先以RepairTable資料表或mysqlaheck–r資料表指令檢查資料表，99.9﹪的MyISAM資料表問題都可以克服無法解決問題時，請改用下列方式處理：重新載入前次以mysqldump下載之備份資料。執行下列指令來更新資料c:\mysql\bin>mysqlbinlog對應日誌檔名|mysql主從式資料庫系統-CH116311-5-3資料表維護與損壞復原1.分析資料表指令(ANALYZETABLE)語法：ANALYZE[LOCAL|NO_WRITE_TO_BINLOG]TABLEtbl_name[,tbl_name]...說明：可用於分析資料表及重建索引表，事前須先以讀取鎖定方式鎖表。適用於所有型態資料表主從式資料庫系統-CH11642.檢查資料表指令(CHECKTABLE)語法：CHECKTABLEtbl_name[,tbl_name]...[option]...說明：可用於檢查資料表及或提示錯誤資訊，適用於所有MyISAM和InnoDB型態資料表其中option

有下列選項

{QUICK|FAST|MEDIUM|EXTENDED|CHANGED}主從式資料庫系統-CH1165檢查資料表選項參數

型式代表意義QUICK不做資料列掃描以檢查不正確之連結FAST只檢查未正常關閉的資料表CHANGED只檢查資料有變更或未正常關閉的資料表MEDIUM資料列掃描以維持正確之連結，同時檢查紀錄與索引之checksumEXTENDED完整檢查，需耗費很長的時間主從式資料庫系統-CH11663.整理資料表指令(OPTIMIZETABLE)語法：OPTIMIZE[LOCAL|NO_WRITE_TO_BINLOG]TABLEtbl_name[,tbl_name]...說明：紀錄為不定長度的資料表，有大量資料被刪除時，可用此道指令將紀錄重新連結（刪除空洞）。前述資料表若經常要做更新處理時，可於每週或每月處理一次OPTIMIZE主從式資料庫系統-CH11673.整理資料表(續)OPTIMIZETABLE將依下述過成程來處理資料表檢查是否有刪除位置或紀錄被分段儲存，有則修復它檢查索引是否未排序，若未排序則重新排序檢查統計資料是否正確，不正確則更正之主從式資料庫系統-CH11684.修復資料表指令(REPAIRTABLE)語法：REPAIR[LOCAL|NO_WRITE_TO_BINLOG]TABLEtbl_name[,tbl_name]...[QUICK][EXTENDED][USE_FRM]說明：此道指令只適用於MyISAM資料表，可用於修復損壞的資料表。其中各選項參數意義簡述如下QUICK：只修復索引樹EXTENDED：以逐列方式重建索引USE_FRM：依據`.frm'檔案重建索引表主從式資料庫系統-CH11695.mysqlcheck前述1~4指令也可改用mysqlchk主控端命令來處理，此命令只用來處理MyISAM資料表，執行命令時，伺服器不需要停止指令型式c:\mysql\bin>mysqlcheck[options]db_na