智慧園區(qū)-園區(qū)數(shù)字化監(jiān)控系統(tǒng)監(jiān)控系統(tǒng)運行監(jiān)控園區(qū)數(shù)字化監(jiān)控系統(tǒng)數(shù)據(jù)庫日常運行監(jiān)控01、職業(yè)能力目標04、任務實施02、任務描述與要求05、任務小結(jié)03、知識儲備目錄職業(yè)能力目標Contents01成為最優(yōu)秀值得信賴的教育服務提供商職業(yè)能力目標能根據(jù)數(shù)據(jù)庫管理方法，完成數(shù)據(jù)庫用戶創(chuàng)建、查看、刪除及密碼設置能根據(jù)數(shù)據(jù)庫用戶實際情況，完成數(shù)據(jù)庫用戶權(quán)限的授予、查看及撤銷能根據(jù)數(shù)據(jù)庫運行監(jiān)控需求，完成數(shù)據(jù)庫的備份與還原任務描述與要求Contents02成為最優(yōu)秀值得信賴的教育服務提供商任務描述另外，為了防止意外事件導致數(shù)據(jù)丟失，數(shù)據(jù)庫管理員還需對數(shù)據(jù)庫進行日常的備份與還原。N園區(qū)的數(shù)據(jù)庫存儲著園區(qū)中多家公司的員工信息，N園區(qū)的數(shù)據(jù)庫管理員需為每家公司配備一個數(shù)據(jù)庫用戶，并對各用戶的權(quán)限進行用戶創(chuàng)建、用戶刪除、密碼設置、用戶信息查看、用戶權(quán)限的授予及撤銷等管理。任務要求完成數(shù)據(jù)庫用戶的創(chuàng)建、查看、刪除、密碼設置完成數(shù)據(jù)庫用戶權(quán)限的授予、查看、撤銷完成數(shù)據(jù)庫的備份與還原讓我們先學習以上任務的一下相關(guān)知識知識儲備Contents03成為最優(yōu)秀值得信賴的教育服務提供商數(shù)據(jù)庫賬號管理及權(quán)限1數(shù)據(jù)庫備份及還原2知識儲備MySQL的權(quán)限體系在MySQL中，用戶權(quán)限分為不同的級別，且不同級別的權(quán)限信息存儲在不同的系統(tǒng)表中。通常，將MySQL權(quán)限體系分為五個層級：全局層級：權(quán)限存儲于mysql.user表中，適用于服務器中所有數(shù)據(jù)庫。數(shù)據(jù)庫層級：權(quán)限存儲于mysql.db表和mysql.host表中，適用于指定數(shù)據(jù)庫中的所有目標。表層級：權(quán)限存儲于mysql.tables_priv表中，適用于指定表中的所有列。列層級：權(quán)限存儲于mysql.columns_priv表中，適用于指定表中的單一列。子程序?qū)蛹墸篶reateroutine、alterroutine、execute和grant等權(quán)限適用于已存儲的子程序，可被授為全局層級和數(shù)據(jù)庫層級。數(shù)據(jù)庫權(quán)限的分類在MySQL中，可授予的權(quán)限可分為數(shù)據(jù)類、結(jié)構(gòu)類、管理類三大類。數(shù)據(jù)類：包括select、insert、update、delete等結(jié)構(gòu)類：包括create、alter、index、drop、createview、showview、createroutine、alterroutine、execute、event、trigger、createtemporarytables等管理類：包括grantoption、super、process、file、reload、shutdown、showdatabases、locktables、references、replicationclient、replicationslave、createuser、createtablespace等數(shù)據(jù)庫權(quán)限表的存取在系統(tǒng)進行數(shù)據(jù)庫權(quán)限的存和取的過程中，涉及到“mysql”數(shù)據(jù)庫中最重要的三個權(quán)限表是三個表的重要性依次遞減。最重要的表是user表，而最不常使用的表是host表。user表host表db表安全列和資源控制列管理權(quán)限用戶列權(quán)限列普通權(quán)限在user表中，用戶列和權(quán)限列是使用最頻繁的列。數(shù)據(jù)庫的操作就用到了普通權(quán)限，如select_priv、super_priv等。數(shù)據(jù)庫權(quán)限表的存取判斷是否存在進行連接的IP地址、用戶名、密碼身份驗證通過身份驗證不通過，連接將被拒絕根據(jù)user表、db表、tables_priv表、columns_priv表的順序依次獲取數(shù)據(jù)庫權(quán)限。根據(jù)user表中的host、user、authentication_string字段在上述權(quán)限表中，權(quán)限范圍依次遞減，范圍最大的是user表，范圍最小的是columns_priv表，且全局權(quán)限覆蓋局部權(quán)限。存在否則權(quán)限表存取第一個過程數(shù)據(jù)庫權(quán)限表的存取權(quán)限表存取第二個過程檢查全局權(quán)限表user表權(quán)限值為“Y”無需檢查db表、tables_priv表、columns_priv表權(quán)限值為“N”檢查db表中該用戶對應的具體數(shù)據(jù)庫的權(quán)限此用戶取得該數(shù)據(jù)庫的該權(quán)限檢查tables_priv表中此數(shù)據(jù)庫對應的具體表的權(quán)限檢查columns_priv表中具體列的權(quán)限該用戶不具備該權(quán)限權(quán)限值為“N”權(quán)限值為“N”權(quán)限值為“N”權(quán)限值為“Y”此用戶取得該數(shù)據(jù)庫的該權(quán)限權(quán)限值為“Y”此用戶取得該數(shù)據(jù)庫的該權(quán)限權(quán)限值為“Y”數(shù)據(jù)庫權(quán)限表的存取通過使用以下select語句可查看到相應表中的各權(quán)限值，即可得到該用戶是否具有相應權(quán)限：select*from表名whereuser=‘用戶名’andhost=‘主機’;數(shù)據(jù)庫單用戶管理在MySQL數(shù)據(jù)庫中，管理員（用戶名為root）有著最高的權(quán)限，可以實現(xiàn)對數(shù)據(jù)庫的管理。root數(shù)據(jù)庫管理系統(tǒng)中存儲著多個數(shù)據(jù)庫、數(shù)據(jù)表和記錄。對這些信息的權(quán)限管理尤為重要，不同的用戶可應設置不同的訪問權(quán)限，包括對某臺數(shù)據(jù)庫服務器的訪問權(quán)限、某個數(shù)據(jù)庫的訪問權(quán)限、某個表的訪問權(quán)限等。管理員需要對不同用戶賦予不同的權(quán)限。數(shù)據(jù)庫單用戶管理（1）創(chuàng)建用戶和密碼createuser‘用戶名’@‘主機’identifiedby[password]‘密碼’;執(zhí)行上述語句可進行新用戶的創(chuàng)建，各字段說明如下：用戶名：創(chuàng)建的用戶名主機：指定該用戶能夠登錄的主機（IP地址、網(wǎng)段、主機名），localhost表示本地用戶，通配符%表示允許任意主機登陸密碼：可設置為三種類型，分別為無密碼、明文密碼和加密密碼。如果省略“identifiedbypassword‘密碼’”，表示不設置密碼；如果直接在“密碼”處輸入設置的密碼，表示使用自動加密的明文密碼。數(shù)據(jù)庫單用戶管理（2）查看用戶信息usemysql;selectuser,authentication_string,hostfromuserwhereuserlike‘用戶名’;執(zhí)行上述語句可查看保存在mysql數(shù)據(jù)庫中的user表中的用戶信息，各字段說明如下：user：用戶名authentication_string：用戶密碼hostfromuser：用戶主機，表示允許用戶登錄的主機數(shù)據(jù)庫單用戶管理（3）重命名用戶renameuser‘用戶名’@‘主機’to‘用戶名’@‘主機’;執(zhí)行上述語句可進行用戶的重命名，各字段說明如下：第一個用戶名和主機：原來的用戶名和允許用戶登錄的主機第二個用戶名和主機：重命名的用戶名和允許用戶登錄的主機（4）刪除用戶dropuser‘用戶名’@‘主機’;執(zhí)行上述語句可刪除用戶，各字段說明如下：用戶名：要刪除的用戶，可以刪除一個或多個用戶主機：語句中若未明確給出具體主機，則默認為通配符%數(shù)據(jù)庫單用戶管理（5）修改當前用戶密碼setpassword=password(‘新密碼’);執(zhí)行上述set語句可進行當前用戶密碼的修改。（6）修改其他用戶密碼alteruser‘用戶名’@‘主機’identifiedby‘新密碼’;執(zhí)行上述set語句進行其他用戶的密碼修改。數(shù)據(jù)庫單用戶權(quán)限變更對數(shù)據(jù)庫用戶的權(quán)限管理操作主要如下三種：（1）授予權(quán)限可通過執(zhí)行g(shù)rant語句實現(xiàn)。授予權(quán)限使用的grant語句如下：grant授權(quán)列表on數(shù)據(jù)庫名.表名to‘用戶’@‘主機’identifiedby‘密碼’;執(zhí)行使用上述grant語句可實現(xiàn)數(shù)據(jù)庫用戶權(quán)限授予，若指定的用戶名不存在，grant語句會創(chuàng)建新用戶；若指定的用戶名存在，grant語句可修改用戶信息。（2）查看權(quán)限showgrantsfor‘用戶’@‘主機’;執(zhí)行上述show語句可實現(xiàn)數(shù)據(jù)庫用戶權(quán)限的查看。（3）撤銷權(quán)限r(nóng)evoke權(quán)限列表on數(shù)據(jù)庫名.表名from‘用戶’@‘主機’;執(zhí)行上述revoke語句可實現(xiàn)用戶權(quán)限的撤銷。與授予權(quán)限相同，權(quán)限進行調(diào)整之后，需要執(zhí)行flushprivileges語句進行刷新。root用戶密碼重置（1）在命令提示符（CMD）窗口，使用如下命令停止MySQL：netstopmysql（2）在MySQL的安裝路徑下，以不檢查權(quán)限的方式啟動MySQL，使用的命令如下：mysqld--skip-grant-tables（3）在另一個命令提示符窗口，進入MySQL的安裝路徑，可使用如下命令進入數(shù)據(jù)庫：mysql-uroot-p（4）使用update語句可實現(xiàn)root用戶密碼的修改，使用的語句如下：updatemysql.usersetauthentication_string=password(‘新密碼’)whereuser=‘root’andhost=‘localhost’;如果管理員忘記了root用戶的密碼，可通過如下操作查看root密碼或?qū)oot密碼進行重置：數(shù)據(jù)庫賬號管理及權(quán)限1數(shù)據(jù)庫備份及還原2知識儲備數(shù)據(jù)庫備份在生產(chǎn)環(huán)境中，數(shù)據(jù)庫可能面臨著各種各樣的意外遭遇導致數(shù)據(jù)丟失，如硬件故障、軟件故障、自然災害、黑客攻擊、誤操作等。為了確保數(shù)據(jù)丟失后能夠得到恢復，數(shù)據(jù)庫需定期進行備份。備份數(shù)據(jù)類型數(shù)據(jù)二進制日志InnoDB事務日志代碼服務期配置文件存儲過程觸發(fā)器時間調(diào)度器存儲函數(shù)數(shù)據(jù)庫備份分類根據(jù)備份數(shù)據(jù)集、備份時數(shù)據(jù)庫運行狀態(tài)、數(shù)據(jù)庫中數(shù)據(jù)的備份方式等不同分類方法，數(shù)據(jù)備份可分成不同類型。分類依據(jù)分類特點根據(jù)備份數(shù)據(jù)集完全備份指備份整個數(shù)據(jù)集（即整個數(shù)據(jù)庫）。部分備份增量備份備份自上一次備份以來（增量或完全）變化的數(shù)據(jù)。差異備份備份自上一次完全備份以來變化的數(shù)據(jù)。根據(jù)備份時數(shù)據(jù)庫運行狀態(tài)熱備份進行備份的時候，數(shù)據(jù)庫的讀寫操作均不受影響。溫備份指進行備份的時候，數(shù)據(jù)庫的讀操作可執(zhí)行，而寫操作不可執(zhí)行。冷備份指進行備份的時候，數(shù)據(jù)庫的讀寫操作均不可執(zhí)行，數(shù)據(jù)庫需要下線。根據(jù)數(shù)據(jù)庫中數(shù)據(jù)的備份方式物理備份過tar、cp等命令直接打包復制數(shù)據(jù)庫的數(shù)據(jù)文件實現(xiàn)備份。邏輯備份通過特定工具從數(shù)據(jù)庫中導出數(shù)據(jù)并另存?zhèn)浞?。相比于物理備份，邏輯備份有可能會丟失數(shù)據(jù)精度。備份與還原策略備份和還原數(shù)據(jù)應根據(jù)不同的應用場景自定義，使用可用資源，需要設計完善的備份策略以進行可靠的數(shù)據(jù)備份和還原，從而實現(xiàn)最大的數(shù)據(jù)可用性和最小的數(shù)據(jù)丟失，并兼顧維護和存儲備份的成本。還原策略：定義了負責執(zhí)行還原的人員、如何執(zhí)行還原以滿足數(shù)據(jù)庫可用性和最大程度減少數(shù)據(jù)丟失的目標、如何測試還原。備份策略：定義了備份的類型和頻率、備份所需硬件的特性和速度、備份的測試方法、備份介質(zhì)的存儲位置和方法、安全注意事項等。根據(jù)還原策略，需對所有組合中的備份成功實現(xiàn)還原，并且測試還原的數(shù)據(jù)庫是否具有物理一致性，備份策略才可成功生成。備份與還原策略設計備份和還原策略需要考慮各種因素：數(shù)據(jù)庫方面對可用性和防止數(shù)據(jù)丟失或損壞的要求數(shù)據(jù)庫的特性包括大小、使用模式、內(nèi)容特性以及數(shù)據(jù)要求等對資源的約束如硬件、人員、備份介質(zhì)的存儲空間以及所存儲介質(zhì)的物理安全性等數(shù)據(jù)庫還原與數(shù)據(jù)庫備份相對應，若出現(xiàn)故障，數(shù)據(jù)庫管理員必須按照正確的邏輯順序?qū)?shù)據(jù)庫中的數(shù)據(jù)進行還原。數(shù)據(jù)庫的還原和恢復支持從整個數(shù)據(jù)庫、數(shù)據(jù)文件或數(shù)據(jù)頁的備份還原數(shù)據(jù)。對數(shù)據(jù)文件的還原，指的是還原和恢復一個或一組數(shù)據(jù)文件。在文件還原過程中，其所在文件組自動變?yōu)槊摍C狀態(tài)，若進行任何訪問脫機文件組的嘗試都將導致錯誤發(fā)生。文件還原對數(shù)據(jù)頁的還原，指的是在完整恢復模式或大容量日志恢復模式下，可以還原單個頁面。無論文件組數(shù)量的大小，任何數(shù)據(jù)庫執(zhí)行頁面的還原都可以實現(xiàn)。頁面還原對數(shù)據(jù)庫備份的還原，指的是還原和恢復整個數(shù)據(jù)庫。在還原和恢復操作期間，數(shù)據(jù)庫處于脫機狀態(tài)。數(shù)據(jù)庫完整還原可逆恢復與不可逆恢復通常，遇到誤刪庫、誤刪表、誤刪列、表空間損壞或出現(xiàn)壞塊等場景，需要從備份中還原數(shù)據(jù)。并不是所有場景下丟失的數(shù)據(jù)都能完整無缺地恢復回來。因此，數(shù)據(jù)還原可根據(jù)應用場景分為可逆恢復與不可逆恢復兩大類。不可逆恢復也就是通常的DDL（DataDefintionLanguage，數(shù)據(jù)庫模式定義語言），通常應用于誤刪庫、誤刪表、表空間損壞或出現(xiàn)壞塊等場景?？赡婊謴屯ǔ？衫胋inlog進行回滾，通常應用于誤刪數(shù)據(jù)文件的場景。數(shù)據(jù)還原原理1通過binlog實現(xiàn)增量數(shù)據(jù)的恢復。增倍恢復的操作過程如下：數(shù)據(jù)備份在遠程機器，需進行全備恢復。操作過程如下：將備份數(shù)據(jù)拷貝到目標機器上解壓文件，applyredolog更改文件權(quán)限實例啟動234確定需要恢復的起始點，即全備對應的binlog位點；解析主庫的binlog，確定誤刪數(shù)據(jù)的位點，作為恢復的終點；利用mysqlbinlog—start-position—stop-position+管道的方式，將binlog恢復到目標實例上。123數(shù)據(jù)庫還原步驟無論通過那種方式還原數(shù)據(jù)，在恢復數(shù)據(jù)庫前，數(shù)據(jù)庫引擎都將保證整個數(shù)據(jù)庫在邏輯上的一致性。例如，要還原一個文件，須使該文件前滾足夠長度，以便與數(shù)據(jù)庫保持一致，從而能夠?qū)崿F(xiàn)文件的恢復和聯(lián)機。如果執(zhí)行文件還原，數(shù)據(jù)庫引擎執(zhí)行如下兩個步驟：創(chuàng)建所有丟失的數(shù)據(jù)庫文件；將數(shù)據(jù)從備份設備復制到數(shù)據(jù)庫文件。如果執(zhí)行數(shù)據(jù)庫還原，數(shù)據(jù)庫引擎執(zhí)行如下三個步驟：創(chuàng)建數(shù)據(jù)庫和事務日志文件；從數(shù)據(jù)庫的備份介質(zhì)將所有數(shù)據(jù)、日志和索引頁復制到數(shù)據(jù)庫文件中；在恢復過程中應用事務日志。邏輯恢復與物理恢復邏輯恢復的方法mysqldump、mysqladmin、source、mysqlimport、loaddatainfile、altertable‘表名’importtablespace物理恢復的方法還原直接復制目錄的備份、ibbackup、Xtrabackup、MEB任務實施Contents04成為最優(yōu)秀值得信賴的教育服務提供商任務實施-1.數(shù)據(jù)庫賬號管理123用戶重命名用戶信息查看用戶創(chuàng)建4用戶密碼修改5用戶刪除6root用戶密碼重置7用戶權(quán)限授予8用戶權(quán)限查看9用戶權(quán)限撤銷任務實施-1.數(shù)據(jù)庫賬號管理任務前準備：新建數(shù)據(jù)庫：DMS（DigitalMonitoringSystem智慧園區(qū)數(shù)字化監(jiān)控系統(tǒng)數(shù)據(jù)庫）在DMS數(shù)據(jù)庫中新建數(shù)據(jù)表：employee_dms，在employee_dms數(shù)據(jù)表中存儲著兩個員工LEA與LEB。employee_dms數(shù)據(jù)表結(jié)構(gòu)示例如圖:LEA的mobile為,entry_date為“20210101”LEB的mobile為,entry_date為“20210102”任務實施-1.數(shù)據(jù)庫賬號管理createdatabaseDMS;usedms;createtableifnotexists`employee_dms`(`id`intunsignedauto_increment,`name`varchar(100)notnull,`mobile`varchar(40)notnull,`entry_date`date,primarykey(`id`))engine=InnoDBdefaultcharset=utf8;insertintoemployee_dms(id,name,mobile,entry_date)values(1,"LEA"20210101);insertintoemployee_dms(id,name,mobile,entry_date)values(2,"LEB"20210102);創(chuàng)建代碼如下：任務實施-1.1用戶創(chuàng)建使用create語句，創(chuàng)建一個本地用戶LE_1和一個主機為192.168.1.112的用戶LE_2。其中，用戶LE_1不設置密碼，用戶LE_2密碼設置為“123”。運行語句后，創(chuàng)建的用戶被保存在mysql數(shù)據(jù)庫的user表中?？梢允褂胹elect語句查看user表。任務實施-1.2用戶信息查看使用show語句查看當前用戶（root）權(quán)限范圍內(nèi)的數(shù)據(jù)庫：執(zhí)行后可看到當前root用戶權(quán)限范圍內(nèi)的數(shù)據(jù)庫包括了系統(tǒng)默認的數(shù)據(jù)庫，以及創(chuàng)建的dms數(shù)據(jù)庫。使用use和select命令查看user表中的數(shù)據(jù)（用戶名、加密后密碼、主機信息）。任務實施-1.3用戶重命名使用rename語句將用戶LE_1的用戶名改為“LE_Company”rename'LE_1'@'localhost'to'LE_Company'@'localhost';執(zhí)行成功后，使用select語句可看到用戶LE_1已重命名為“LE_Company”任務實施-1.4用戶密碼修改由于用戶LE_2的密碼過于簡單，根據(jù)園區(qū)數(shù)據(jù)庫管理要求，需將其密碼修改為包含中英文兩種類型字符的密碼，此處將LE_2密碼改為“admin123”任務實施-1.5用戶刪除假如LE_Company用戶不再使用，可使用如下drop語句進行刪除：Dropuser‘LE_Company’@‘localhost’;使用select語句查看user表中用戶LE_Company的記錄，已不存在任務實施-1.6root用戶密碼重置假如忘記root用戶的密碼，可通過如下方法進行密碼重置。首先，使用管理員的身份運行命令提示符（CMD），使用“netstopmysql”命令停止MySQL：切換到MySQL的安裝路徑在安裝路徑下，使用“mysqld--skip-grant-tables”命令跳過授權(quán)表：任務實施-1.6root用戶密碼重置保留上一操作命令提示符窗口，使用管理員身份重新運行一個命令提示符。切換到MySQL安裝路徑的bin目錄下，輸入“mysql-uroot-p;”命令進行數(shù)據(jù)庫登錄。執(zhí)行后出現(xiàn)“Enterpassword:”提示輸入密碼，可直接回車實現(xiàn)跳過授權(quán)表登錄。登錄MySQL后，使用如下語句即可重置root用戶的密碼為“123456”：updatemysql.usersetauthentication_string=password(‘123456’)whereuser=‘root’andhost=‘localhost’;任務實施-1.7用戶權(quán)限授予使用grant語句給用戶LE_2授予對employee_dms數(shù)據(jù)表進行select、insert、update、delete操作的權(quán)限，并進行權(quán)限的刷新：grantselect,insert,update,deleteondms.employee_dmsto'LE_2'@'192.168.1.112'identifiedby'admin123';flushprivileges;任務實施-1.8用戶權(quán)限查看使用show語句查看用戶LE_2的權(quán)限：showgrantsfor'LE_2'@'192.168.1.112';任務實施-1.8用戶權(quán)限撤銷現(xiàn)僅需保留用戶LE_2對表employee_dms的select，使用revoke語句權(quán)限撤銷其insert、update和delete權(quán)限：revokeinsert,update,deleteondms.employee_dmsfrom'LE_2'@'192.168.1.112';flushprivileges;任務實施-2.數(shù)據(jù)庫備份及還原12數(shù)據(jù)庫還原數(shù)據(jù)庫備份任務