1 / 79

第 11 章 伺服器與資料庫管理

第 11 章 伺服器與資料庫管理. 本章宗旨. 介紹 MySQL 之權限功能、工作原理與權限類型 介紹權限之授權與撤除指令及應用方式 簡介伺服器工作環境參數與管理 簡介日誌檔產生方式與用途 簡介伺服器管理指令 簡介災難預防與復原之意義與處理指令 Administrator 伺服器管理應用軟體使用說明. 大 綱. 11-1 帳號與存取權限管理 11-2 伺服器系統環境管理 11-3 日誌檔管理 11-4 mysqladmin －伺服器管理命令 11-5 災難預防與復原 11-6 設定維護排程計劃

demetria-richmond
Download Presentation

第 11 章 伺服器與資料庫管理

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. 第11章 伺服器與資料庫管理 主從式資料庫系統 - CH11

  2. 本章宗旨 • 介紹MySQL之權限功能、工作原理與權限類型 • 介紹權限之授權與撤除指令及應用方式 • 簡介伺服器工作環境參數與管理 • 簡介日誌檔產生方式與用途 • 簡介伺服器管理指令 • 簡介災難預防與復原之意義與處理指令 • Administrator伺服器管理應用軟體使用說明 主從式資料庫系統 - CH11

  3. 大 綱 11-1 帳號與存取權限管理 11-2 伺服器系統環境管理 11-3 日誌檔管理 11-4 mysqladmin－伺服器管理命令 11-5 災難預防與復原 11-6 設定維護排程計劃 11-7 Administrator 應用簡介 主從式資料庫系統 - CH11

  4. 11-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 權限驗證 主從式資料庫系統 - CH11

  5. 11-1-1 權限的功能 • 權限的主要功能 • 認證使用者是否有權登入伺服器 • 執行諸如Select、Insert、Update、Delete等資料處理作業時，認證是否有權開啟資料表和處理資料 • 進階的權限管理功能，在於驗證相關人員是否可以將權限授與他人使用 主從式資料庫系統 - CH11

  6. 11-1-2權限工作原理 • MySQL將存取權限控制分為兩個階段 • 階段一：伺服器先查核使用者是否被允許連線。 • 階段二：假如有連線權利，而且也成功的連線，使用者每發出每一道SQL指令時，MySQL將檢查是否有足夠權限去處理該項作業 主從式資料庫系統 - CH11

  7. 11-1-2權限工作原理(續) • 連線後假如權限有被變更過（不管是自己變更，或是他人變更），變更的權限並不會立即發生作用 • 除非已執行【Flush Privileges;】指令，MySQL才會重新讀取權限資料並置於記憶體 主從式資料庫系統 - CH11

  8. 11-1-2權限工作原理(續) • MySQL將所有授權資訊儲存在 ”mysql” 資料庫內的資料表 • 其中tables_priv和columns_priv兩個資料表用來存放資料處理權限資料，這些資料表用於階段二之查核 • User, Db及Host三個資料表用來存放連線權限資料，這些資料表用於階段一和二之查核 主從式資料庫系統 - CH11

  9. 11-1-2權限工作原理(續) • 權限資料表用法 • 登錄在”user”資料表之使用者，在指定的”host”登入時，這個使用者將可以處理位於伺服器上的資料庫（若所有權限都設為’Y’），此權限形同 ”Supervisor” 權限 • 登錄在 ”Db” 資料表之使用者，在指定的”host”登入時，這個使用者將可以處理指定的資料庫，包含此資料庫內的全部資料表 主從式資料庫系統 - CH11

  10. 11-1-2權限工作原理(續) • 若需要從不同的主機連線來處理指定的資料庫時，可以使用”host”資料表設定主機與資料庫。假如以這個方式工作，請將”Db”資料表之”host”欄位保留空白 • “tables_priv”與”columns_pri”權限設定意義與”Db”類似，不過它們可以指定更詳細的權限。假如使用者有權處理特定資料表，此意味他也有權處理該資料表之全部欄位 主從式資料庫系統 - CH11

  11. 11-1-3 權限類型 • 權限使用時機 • Select, Insert, Update 和Delete可用於處理指定資料庫內現存資料表的紀錄操作 • Create使用於建立新的資料庫或資料表， Drop則用於刪除現存的資料庫或資料表 • Index使用於建立和刪除索引表，擁有Create資料表權限的人，同時擁有Index權限 主從式資料庫系統 - CH11

  12. 11-1-3 權限類型(續) • 權限使用時機 • Alter 使用於改變資料表結構或重新命名 • Grant可用於將自己擁有的權限授與他人 • File用於以”Load Data Infile”和”Select”指令在伺服器上讀寫檔案 主從式資料庫系統 - CH11

  13. 11-1-3 權限類型(續) • Reload權限又分成：flush-hosts, flush-logs, flush-privileges, flush-status, flush-tables, flush-threads, refresh, reload • reload 用於通知伺服器重新載入權限資料表到記憶體 • flush-privileges與reload類似 • refresh 用來關閉和重開日誌檔，以及沖回所有資料表 • Fulsh-XXX功能與refresh類似，可以附帶案例 主從式資料庫系統 - CH11

  14. 11-1-3 權限類型(續) • shutdown用於關閉伺服器，無對應的SQL指令可用 • processlist用於顯示目前正在伺服器上工作的執行緒，使用者隨時都可以顯示自己啟動的執行緒並刪除它，但是只有擁有Process權限的人才能查看由他人啟動的執行緒，而擁有Super權限的人才能使用Kill來刪除由他人啟動的執行緒 • Create Temporary Table權限使用於以關鍵字”TEMPORARY”來建立暫存表 主從式資料庫系統 - CH11

  15. 11-1-3 權限類型(續) • Lock Tables用來鎖住資料表，擁有Select權限的人，才能執行Locak Tables。資料表被鎖定後，其他人無法讀取資料表 • Replication Client用於執行”Show Master Status”和”Show Slave Status”指令 • Replication Slave用於授權給備用伺服器之使用者，而這些人必須已連線至主伺服器，若未經授權，備用伺服器將無法要求更新主伺服器之資料 主從式資料庫系統 - CH11

  16. 11-1-3 權限類型(續) • Show Databases用於檢視指名的資料庫之全部資訊 • 若無此權限，執行此指令只能看到那些有被授權之資料表相關資訊 • 假如啟動伺服器時，若有搭配 ”--skip-show-database” 參數，縱使有此授權，也無法檢視資料庫相關資訊 主從式資料庫系統 - CH11

  17. 1. 完整授權 1.1 授權給使用者”monty”，以驗證密碼”Happy”從local host登入，並享有Grant權限（形同monty具有supervisor之權限） • 指令：GRANT ALL PRIVILEGES ON *.* TO 'monty'@'localhost'IDENTIFIED BY ' Happy ' WITH GRANT OPTION; 1.2 避免匿名者權限會蓋掉monty在Local host之權限，應增加一組權限，如下： • 指令：GRANT ALL PRIVILEGES ON *.* TO 'monty'@'%'IDENTIFIED BY ' Happy ' WITH GRANT OPTION; 主從式資料庫系統 - CH11

  18. 1. 完整授權(續) 1.3 授權給使用者：admin， 無驗證密碼，可從local host 登入，享有Reload和Process管理者之權限，可使用mysqladmin主控端指令來執行reload, refresh, 和flush-XXX工作，但是他無權處理任何資料 指令： GRANT RELOAD,PROCESS ON *.* TO 'admin'@'localhost'; 主從式資料庫系統 - CH11

  19. 1. 完整授權(續) 1.4 替使用者 ”dummy” 預設一個從Local host 登入之帳戶，但不給他任何權限（使用Usage參數之效果），權限則可留待以後再以部份授權方式加入 指令： GRANT USAGE ON *.* TO 'dummy'@'localhost'; 主從式資料庫系統 - CH11

  20. 2. 部份授權 2.1 設立帳戶並授與部分權限範例（例如只允許處理bankaccount資料庫） 指令：GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP ON bankaccount.* TO 'custom'@'localhost' IDENTIFIED BY 'obscure'; 2.2 使用Insert設立帳戶並授與部分權限範例，反之可使用Delete撤除授權 範例一：INSERT INTO user (Host,User,Password)VALUES('localhost','custom',PASSWORD('obscure')); 主從式資料庫系統 - CH11

  21. 2. 部份授權(續) 範例二：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'); 主從式資料庫系統 - CH11

  22. 2. 部份授權(續) 範例四：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; 主從式資料庫系統 - CH11

  23. 3. 撤除權限 3.1 撤除帳號 指令一：Drop User ‘帳號1’, ‘帳號2’ …; 指令二：REVOKE priv_type [(column_list)] [, priv_type [(column_list)]] ... ON {tbl_name | * | *.* | db_name.*} FROM帳號1 [,帳號2] ... 指令三：REVOKE ALL PRIVILEGES, GRANT OPTION FROM user [, user] ... 指令四：DELETE FROM mysql.userWHERE User=’'帳號’ and Host='host_name'; 主從式資料庫系統 - CH11

  24. 11-1-5 密碼設定與變更 1. 事前設立暗碼型密碼 • 將Grant….IDENTIFIED BY '’密碼’; 改為下列型式即可 • Grant….IDENTIFIED BY Password('’密碼’); • 使用Insert指令設立帳戶時，仍須使用 Password('’密碼’) 指令型式，將密碼轉成暗碼 主從式資料庫系統 - CH11

  25. 11-1-5 密碼設定與變更(續) 2. 事後設立暗碼型密碼 • Set Password For ‘Jeffrey@%’ = Password(‘密碼’); 說明：替使用者”Jeffrey”設立或變更密碼（只限root使用者） • Set Password = Password(‘密碼’); 說明：替使用者”Jeffrey”設立或變更密碼（只限Jeffrey本人） 主從式資料庫系統 - CH11

  26. 11-1-5 密碼設定與變更(續) 3. 變更密碼 指令： • UPDATE user SET Password = PASSWORD('密碼')WHERE Host = '%' AND User = 'Jeffrey'; 主從式資料庫系統 - CH11

  27. 11-1-6 啟用權限 • MySQL啟用全部或部份權限方式 • 起動mysqld時，所有權限資料表將被載入記憶體，所有權限將自動生效 • 當伺服器重新載入權限資料表時，下列連線中主控端之權限將受到影響： • 主控端在下一次執行資料表或欄位資料時，資料表或欄位權限才生效 • 主控端在下一次執行Use Database時，資料庫權限才生效 • 主控端在下一次連線時，完整權限和新的密碼才生效 主從式資料庫系統 - CH11

  28. 11-1-6 啟用權限(續) • 執行Grant, Revoke, 或Set Password指令後，伺服器將立即重新載入相關的權限資料表 • 執行Insert, Update, 或Delete指令，必須搭配”Flush Privileges;” 指令，通知伺服器重新載入相關的權限資料表，新的權限才生效 主從式資料庫系統 - CH11

  29. 11-1-7 伺服器連線 • 主控端與伺服端之連線指令 • mysql –h Host – u User –pPassword • 其中 ”-h” 代表指定連線主機參數 • Host代表主機名稱，可用數字碼，或是網域名稱 • ”-u” 代表使用者帳號參數 • User為使用者帳號 • ”-p”表示密碼參數 • Password為使用者自己設定之密碼，”-p”和密碼間不能有空格 • 若主控端與伺服端位於同一台電腦，則 –h Host部分可以省略 主從式資料庫系統 - CH11

  30. 11-1-8 主機及其他設定值 • user資料表上之host欄位，其輸入資料可使用下列型式 • 使用主機名稱、IP代碼，或是localhost • 使用主機名稱型式時，可搭配”%”萬用字元，例如單一字元”%”表示所有主機，”%.mysql.com” 表示符合”mysql.com”網域的所有主機 • 使用IP型式時，也可以搭配”%”萬用字元，例如”144.155.166.%” 主從式資料庫系統 - CH11

  31. 11-1-8 主機及其他設定值(續) • 使用IP型式時，可搭配遮罩格式 • 例如 ”192.58.197.0 /255.255.255.0”，此意味從主機”192.58.197.0”到”192.58.197.255”，都被視為合法主機 • db資料表之host欄位若為空白時，將以host資料表中對應之host與db紀錄作為權限依據。若db欄位保留空白，將被視為”%”型式 主從式資料庫系統 - CH11

  32. 11-1-8 主機及其他設定值(續) • 其他權限資料表之host欄若保留空白，將被視為”%”型式 • user權限資料表之user欄若保留空白，將被視為匿名帳號 • tables_priv和columns_priv 之db, table_name, column_name三個欄位不得使用”%”字元，也不能保留空白 主從式資料庫系統 - CH11

  33. 11-1-9 權限驗證 • 階段一之權限驗證過程如下 • 讀取user資料表紀錄 • 依據host及user將紀錄排序 • 比對主機，若有相符之主機資料，取出帳號及密碼與登入資料比對，資料皆符合時，即通過驗證 主從式資料庫系統 - CH11

  34. 11-2 伺服器系統環境管理 11-2-1 系統環境參數設定 • 系統參數值設定方式分成下列三種型式，型式一用於命令列，型式二用於參數檔，型式三用於應用程式。 型式一： --參數名稱 = 參數值，或 --參數名稱 型式二： 參數名稱 = 參數值，或 參數名稱 型式三：SET GLOBAL參數名稱 = 參數值； 主從式資料庫系統 - CH11

  35. 11-2-1 系統環境參數設定 • 下列參數格式具有相同效果 • 關閉效果 • --disable-column-names • --skip-column-names • --column-names=0 • 開啟效果 • --column-names • --enable-column-names • --column-names=1 主從式資料庫系統 - CH11

  36. 11-2-2 系統環境參數型式 • 各種常用系統環境參數請參見書本說明 主從式資料庫系統 - CH11

  37. 11-3 日誌檔管理 11-3-1 產生日誌檔 11-3-2 日誌檔內容 主從式資料庫系統 - CH11

  38. 11-3-1 產生日誌檔 • 啟動mysqld伺服器時，可搭配系統環境參數將執行過程之信息記錄在日誌檔 • 日誌檔可用下列指令強制關閉及重新開起新檔 • SQL指令：Flush Logs; • 命令列指令：mysqladmin flush-logs, 或mysqladmin refresh 主從式資料庫系統 - CH11

  39. 日誌檔類型 主從式資料庫系統 - CH11

  40. 11-3-2 日誌檔內容 1. 錯誤日誌檔 • 錯誤日誌檔內容包含伺服器啟動與停止時間，以及運作期間發生的錯誤資料 2. 查詢日誌檔 • 查詢日誌檔用於記錄主控端連線及執行SQL之相關信息，可用於追蹤問題發生原因 3. 異動資料日誌檔 • 異動資料日誌檔記錄所有會改變資料之SQL指令，同時記錄更新資料耗費的時間 4. 慢速查詢日誌檔 • 所有查詢指令處理時間超過狀態變數 ”long_query_time” 設定值者，將被記錄在慢速查詢日誌檔 主從式資料庫系統 - CH11

  41. 異動資料日誌檔 • 異動資料日誌檔之主要用途，在於資料更新後若需復原資料，可直接從日誌檔取得復原資料之相關訊息，這些信息也可用於備用伺服器之資料更新 • 毎次啟動伺服器或執行Flush Logs時，系統即自動產生一個新的日誌檔，檔名均相同，但是延伸名會以流水號遞增 • 假如日誌檔太大時（大於預設值max_binlog_size），系統即自動產生新的日誌檔 主從式資料庫系統 - CH11

  42. 異動資料日誌檔(續) • 執行大型交易型資料的話，日誌資料則不會被分割在兩個日誌檔 • 異動資料日誌檔可搭配”--log-bin-index”參數，系統將自動產生一個索引檔，用來記錄相關日誌檔之關係。當伺服器在運作時，不要去編輯索引檔，以免伺服器造成混淆 主從式資料庫系統 - CH11

  43. 異動資料日誌檔(續) • 可使用【Reset Master】指令來刪除所有異動資料日誌檔，或使用【PURGE MASTER LOGS】來刪除部份異動資料日誌檔 • 指令： PURGE MASTER LOGS TO 'mysql-bin.010'; PURGE MASTER LOGS BEFORE '2003-04-02 22:46:26'; 主從式資料庫系統 - CH11

  44. 異動資料日誌檔(續) • 刪除部份異動資料日誌檔程序 • SHOW SLAVE STATUS指令查看那個日誌檔正被讀取中 • SHOW MASTER LOGS指令查看主伺服器有那些日誌檔 • 選出最近的日誌檔，該檔將是系統還要使用的目標檔 • 備份將要刪除的日誌檔 • 執行刪除日誌檔 主從式資料庫系統 - CH11

  45. 異動資料日誌檔(續) • 刪除日誌檔時，請先確認無備份伺服器還要使用這些日誌檔 • 理想的刪除規則為，每天執行一次【mysqladmin flush-logs】，然後將三天以上的日誌檔以【Purge Master Logs】指令刪除 • Purge指令可同時整理索引檔。 主從式資料庫系統 - CH11

  46. 異動資料日誌檔(續) • 記錄資料更新指令時，可針對特定資料庫做選別，選別參數有兩種型式 --binlog-do-db = DB_Name：若更新指令之處理對象資料庫非現用資料庫時，該指令不被記錄 --binlog-ignore-db = DB_Name：若更新指令之處理對象資料庫非現用資料庫時，該指令將被記錄 主從式資料庫系統 - CH11

  47. 異動資料日誌檔(續) • 使用下述指令檢視日誌檔內容：。 • 近端伺服器：mysqlbinlog 日誌檔名 • 遠端伺服器：mysqlbinlog 日誌檔名 –R-h伺服器所在主機名稱 • 或使用下述指令更新特定的伺服器： mysqlbinlog日誌檔名| mysql -h 伺服器名 主從式資料庫系統 - CH11

  48. 11-4 mysqladmin伺服器管理命令 11-4-1用途與語法 • mysqladmin 是一個主控端應用程式，用來執行伺服器之管理作業，諸如伺服器之環境設定和提示現狀信息，增刪資料庫，以及其他作業 主從式資料庫系統 - CH11

  49. 11-4-1用途與語法 • mysqladmin 語法： c:\mysql\bin> mysqladmin [options] command [command-option] command ... 其中 • [options]為選項參數 • command為操作資料庫指令 • [command-option] 為指令選項參數 指令型式及參數選項請參看書本說明 主從式資料庫系統 - CH11

  50. 11-5 災難預防與復原 • 備份資料庫可預防因突發事故造成資料無法復原之損失 • 適時的維護資料表，則有助於提升系統效率 主從式資料庫系統 - CH11

More Related