數(shù)據(jù)庫應(yīng)用技術(shù)（MySQL）（第二版） 課件 模塊四 管理數(shù)據(jù)庫

模塊四

管理數(shù)據(jù)庫

數(shù)據(jù)庫中保存著非常重要的數(shù)據(jù)和資料，保證數(shù)據(jù)的安全性對于任何數(shù)據(jù)庫管理系統(tǒng)來說都是極為重要的。數(shù)據(jù)庫安全是指保護(hù)數(shù)據(jù)庫，防止因不合法的使用而造成數(shù)據(jù)泄露、更改或破壞。

為了保證數(shù)據(jù)庫的安全，MySQL提供了完善的安全管理機(jī)制，同時還提供了功能強(qiáng)大的數(shù)據(jù)庫備份和還原工具。定期對數(shù)據(jù)庫進(jìn)行備份，就可以從多種故障中恢復(fù)數(shù)據(jù)，將損失降到最低。本模塊主要包括以下2個學(xué)習(xí)任務(wù)：

任務(wù)1數(shù)據(jù)庫安全管理

任務(wù)2數(shù)據(jù)庫備份與還原數(shù)據(jù)庫存儲著大量的關(guān)鍵數(shù)據(jù)與敏感數(shù)據(jù)，這些數(shù)據(jù)一旦被泄露或竊取，會對個人、企業(yè)甚至國家造成無法估量的損失。數(shù)據(jù)安全已成為國家安全保障體系的重要組成內(nèi)容。為了保證數(shù)據(jù)庫的安全，用戶要訪問MySQL數(shù)據(jù)庫，必須擁有登錄MySQL服務(wù)器的用戶名和密碼。登錄服務(wù)器后，用戶只能在其權(quán)限范圍內(nèi)使用數(shù)據(jù)庫資源。數(shù)據(jù)庫安全管理的任務(wù)主要是對數(shù)據(jù)庫進(jìn)行安全性設(shè)置，給操作數(shù)據(jù)庫的人員分配用戶名、密碼以及可操作的權(quán)限范圍。任務(wù)描述任務(wù)1數(shù)據(jù)庫安全管理了解MySQL權(quán)限表12掌握創(chuàng)建和管理用戶賬戶的方法3掌握查看權(quán)限的方法掌握授予和收回權(quán)限的方法4任務(wù)目標(biāo)了解掌握掌握掌握用戶管理MySQL用戶主要分為root用戶和普通用戶，root用戶是超級管理員，擁有操作和管理MySQL的所有權(quán)限，普通用戶只擁有被賦予的權(quán)限。用戶賬號及相關(guān)信息存儲在系統(tǒng)數(shù)據(jù)庫mysql的user表中。MySQL提供了許多命令用于創(chuàng)建用戶、修改用戶、刪除用戶等。知識點(diǎn)1知識準(zhǔn)備1.創(chuàng)建用戶可以使用CREATEUSER語句創(chuàng)建用戶，其基本語法格式如下：用戶管理知識點(diǎn)1知識準(zhǔn)備CREATEUSER'username'@'hostname'[IDENTIFIEDBY'password'][,'username'@'hostname'[IDENTIFIEDBY'password']]…指定創(chuàng)建的用戶名。指定用戶連接MySQL服務(wù)器時所用主機(jī)的名字，可以是IP地址、域名以及“%”。其中，“%”表示任何主機(jī)名。指定用戶的密碼。提示：CREATEUSER語句可以同時創(chuàng)建多個用戶，各用戶之間用逗號分隔。創(chuàng)建的新用戶可以登錄MySQL服務(wù)器，不能執(zhí)行任何數(shù)據(jù)庫操作。是可選項，用于設(shè)置用戶的密碼。如果用戶無密碼，可以省略。2.修改用戶密碼用戶密碼至關(guān)重要，一旦丟失需要及時修改。root用戶既可以修改自己的密碼，也可以修改普通用戶的密碼，普通用戶需要擁有相應(yīng)權(quán)限才能修改自己的密碼。MySQL中可以使用ALTERUSER語句，也可以使用SETPASSWORD語句修改用戶密碼。（1）ALTERUSER語句修改用戶密碼的基本語法格式如下：（2）SETPASSWORD語句修改用戶密碼的基本語法格式如下：用戶管理知識點(diǎn)1知識準(zhǔn)備ALTERUSERuser[IDENTIFIEDBY'newpassword'][,user[IDENTIFIEDBY'newpassword']]…指定新密碼。指定要修改密碼的用戶賬號，由用戶名和主機(jī)名構(gòu)成，也可以使用USER()函數(shù)指定用戶，表示修改當(dāng)前用戶的密碼。提示：ALTERUSER語句是修改用戶密碼的首選SQL語句，推薦使用。SETPASSWORD[FORuser]='newpassword'指定新密碼。指定要修改密碼的用戶賬號，user由用戶名和主機(jī)名構(gòu)成。FORuser可以省略，表示修改當(dāng)前用戶的密碼。3.刪除用戶MySQL中可以使用DROPUSER語句刪除用戶，也可以使用DELETE語句直接將用戶的信息從mysql.user表中刪除。DROPUSER語句的基本語法格式如下：用戶管理知識點(diǎn)1知識準(zhǔn)備DROPUSER'username'@'hostname'[,'username'@'hostname']…說明：DROPUSER語句可以同時刪除多個用戶，并撤銷其權(quán)限。用戶的刪除不會影響到他們之前創(chuàng)建的表、索引等數(shù)據(jù)庫對象。權(quán)限管理在MySQL數(shù)據(jù)庫中，為了保證數(shù)據(jù)的安全性，數(shù)據(jù)庫管理員需要為每個用戶分配不同的權(quán)限，限制用戶只能在其權(quán)限范圍內(nèi)操作數(shù)據(jù)庫資源。管理員還可以根據(jù)不同的情況為用戶增加或收回對數(shù)據(jù)的操作權(quán)限，從而控制數(shù)據(jù)操作人員的權(quán)限。根據(jù)權(quán)限的作用范圍，權(quán)限信息分別存儲在系統(tǒng)數(shù)據(jù)庫mysql的不同數(shù)據(jù)表中。啟動MySQL服務(wù)時，會自動加載這些權(quán)限信息，并將這些權(quán)限信息讀取到內(nèi)存中。知識點(diǎn)2知識準(zhǔn)備數(shù)據(jù)表說明user保存用戶被授予的全局權(quán)限db保存用戶被授予的數(shù)據(jù)庫權(quán)限tables_priv保存用戶被授予的數(shù)據(jù)表權(quán)限columns_priv保存用戶被授予的列權(quán)限procs_priv保存用戶被授予的存儲過程和函數(shù)權(quán)限1.查看權(quán)限可以使用SHOWGRANTS語句查看用戶的權(quán)限信息，其基本語法格式如下：權(quán)限管理知識點(diǎn)2知識準(zhǔn)備SHOWGRANTS[FORuser]指定要查看權(quán)限的用戶賬號，user由用戶名和主機(jī)名構(gòu)成。FORuser可以省略，表示查看當(dāng)前用戶的權(quán)限。2.授予權(quán)限合理的授權(quán)可以保證數(shù)據(jù)庫的安全，MySQL中可以使用GRANT語句為用戶授權(quán)，其基本語法格式如下：權(quán)限管理知識點(diǎn)2知識準(zhǔn)備GRANTpriv_type[(column_list)][,priv_type[(column_list)]]…ONpriv_levelTOuser[,user]…[WITHGRANTOPTION]指定權(quán)限類型，如SELECT、UPDATE等。可選項，指定權(quán)限授予給表中哪些列。指定被授予權(quán)限的用戶，由用戶名和主機(jī)名構(gòu)成?？蛇x項，指定用戶可以將自己擁有的權(quán)限授予給其他用戶。指定權(quán)限所作用的范圍。有這樣幾類格式：“*”表示當(dāng)前數(shù)據(jù)庫中的所有表；“*.*”表示所有數(shù)據(jù)庫中的所有表；“db_name.*”表示指定數(shù)據(jù)庫db_name中的所有表；“db_name.tbl_name”表示指定數(shù)據(jù)庫db_name中的指定表或視圖tbl_name；“tbl_name”表示指定表或視圖tbl_name；“db_name.routine_name”表示指定數(shù)據(jù)庫db_name中的指定存儲過程或函數(shù)routine_name。3.收回權(quán)限在MySQL中，為了保證數(shù)據(jù)庫的安全性，需要將用戶非必要的權(quán)限收回，MySQL提供REVOKE語句收回用戶的權(quán)限。其基本語法格式如下：權(quán)限管理知識點(diǎn)2知識準(zhǔn)備REVOKEpriv_type[(column_list)][,priv_type[(column_list)]]…ONpriv_levelFROMuser[,user]…任務(wù)實施1使用圖形化工具管理用戶2使用SQL語句管理用戶3使用圖形化工具管理權(quán)限4使用SQL語句管理權(quán)限1.創(chuàng)建用戶【例1】創(chuàng)建用戶“judy”，主機(jī)名為“l(fā)ocalhost”，密碼為“judy666”。任務(wù)實施使用圖形化工具管理用戶12.修改用戶密碼【例2】修改用戶“judy”的密碼為“judy888”。任務(wù)實施使用圖形化工具管理用戶11.創(chuàng)建用戶【例3】使用CREATEUSER語句創(chuàng)建一個新用戶，用戶名為“xqy”，主機(jī)名為“l(fā)ocalhost”，密碼為“xqy666”。任務(wù)實施使用SQL語句管理用戶2mysql>CREATEUSER'xqy'@'localhost'IDENTIFIEDBY'xqy666';QueryOK,0rowsaffected(0.01sec)mysql>SELECThost,userFROMmysql.user;+-----------------+-------------------------+|host |user |+-----------------+-------------------------+|localhost |judy ||localhost |schema ||localhost |mysql.session ||localhost |mysql.sys ||localhost |root ||localhost |xqy |+-----------------+-------------------------+6rowsinset(0.03sec)CREATEUSER語句執(zhí)行成功后，可以使用SELECT語句查看用戶是否已經(jīng)被添加到user表中?！纠?】使用CREATEUSER語句創(chuàng)建兩個用戶，用戶名為“xx”，主機(jī)為任意主機(jī)，密碼為“xx666”；用戶名為“yy”，主機(jī)名為“l(fā)ocalhost”，密碼為“yy666”。任務(wù)實施使用SQL語句管理用戶2mysql>CREATEUSER'xx'@'%'IDENTIFIEDBY'xx666','yy'@'localhost'IDENTIFIEDBY'yy666';QueryOK,0rowsaffected(0.02sec)說明：主機(jī)名為“l(fā)ocalhost”表示該用戶只能從本地主機(jī)連接MySQL服務(wù)器。主機(jī)為任意主機(jī)，使用“%”表示該用戶可以在任何主機(jī)中連接MySQL服務(wù)器。2.修改用戶密碼【例5】使用ALTERUSER語句將用戶“xqy”的密碼改為“xqy888”。任務(wù)實施使用SQL語句管理用戶2mysql>ALTERUSER'xqy'@'localhost'IDENTIFIEDBY'xqy888';QueryOK,0rowsaffected(0.02sec)【例6】使用SETPASSWORD語句將用戶“xqy”的密碼改為“xqy999”。任務(wù)實施使用SQL語句管理用戶2mysql>SETPASSWORDFOR'xqy'@'localhost'='xqy999';QueryOK,0rowsaffected(0.02sec)3.刪除用戶【例4-1-7】使用DROPUSER語句刪除用戶“xx”。任務(wù)實施使用SQL語句管理用戶2mysql>DROPUSER'xx'@'%';QueryOK,0rowsaffected(0.02sec)【例8】使用DELETE語句刪除用戶“yy”。任務(wù)實施使用SQL語句管理用戶2mysql>DELETEFROMmysql.userWHEREhost='localhost'ANDuser='yy';QueryOK,1rowaffected(0.01sec)執(zhí)行成功后，可以通過SELECT語句查看user表，以確定用戶是否已經(jīng)被成功刪除。由于DELETE語句是直接對user表進(jìn)行操作，執(zhí)行完DELETE語句后需要使用“FLUSHPRIVILEGES;”語句重新加載權(quán)限。多學(xué)一招：MySQL還提供了RENAMEUSER語句用于對用戶進(jìn)行重命名，其基本語法格式如下：任務(wù)實施使用SQL語句管理用戶2RENAMEUSERold_userTOnew_user[,old_userTOnew_user]…【例9】將用戶名“xqy”修改為“xjy”。mysql>RENAMEUSER'xqy'@'localhost'TO'xjy'@'localhost';QueryOK,0rowsaffected(0.01sec)指定已經(jīng)存在的用戶賬號。指定新的用戶賬號。創(chuàng)建的新用戶可以登錄MySQL服務(wù)器，但是沒有任何操作權(quán)限，可以根據(jù)業(yè)務(wù)需求給用戶分配適當(dāng)?shù)臋?quán)限?！纠?0】授予用戶“judy@localhost”在整個服務(wù)器上擁有“Select”、“Update”、“Insert”和“Delete”權(quán)限。任務(wù)實施使用圖形化工具管理權(quán)限3【例11】授予用戶“judy@localhost”在數(shù)據(jù)庫beems上擁有“Create”、“Drop”和“Alter”權(quán)限。任務(wù)實施使用圖形化工具管理權(quán)限31.查看權(quán)限【例12】使用SHOWGRANTS語句查看root用戶的權(quán)限信息。任務(wù)實施使用SQL語句管理權(quán)限4root用戶擁有所有權(quán)限，并且可以為其他用戶授予權(quán)限?！纠?3】使用SHOWGRANTS語句查看用戶“xqy”的權(quán)限信息。任務(wù)實施使用SQL語句管理權(quán)限4mysql>SHOWGRANTSFOR'xqy'@'localhost';+---------------------------------------------------------+|Grantsforxqy@localhost |+---------------------------------------------------------+|GRANTUSAGEON*.*TO`xqy`@`localhost`|+---------------------------------------------------------+1rowinset(0.05sec)USAGE權(quán)限只能用于登錄MySQL數(shù)據(jù)庫，不能執(zhí)行任何操作，且USAGE權(quán)限不能被收回。2.授予權(quán)限【例14】使用GRANT語句設(shè)置用戶“xqy”對所有數(shù)據(jù)庫有“insert”、“select”權(quán)限。任務(wù)實施使用SQL語句管理權(quán)限4mysql>GRANTINSERT,SELECTON*.*TO'xqy'@'localhost'WITHGRANTOPTION;QueryOK,0rowsaffected(0.01sec)mysql>SHOWGRANTSFOR'xqy'@'localhost';+--------------------------------------------------------------------------------------------------------------+|Grantsforxqy@localhost |+--------------------------------------------------------------------------------------------------------------+|GRANTSELECT,INSERTON*.*TO`xqy`@`localhost`WITHGRANTOPTION|+--------------------------------------------------------------------------------------------------------------+1rowinset(0.04sec)再次查看用戶“xqy”的權(quán)限信息。3.收回權(quán)限【例14】使用REVOKE語句收回用戶“xqy”的INSERT權(quán)限。任務(wù)實施使用SQL語句管理權(quán)限4mysql>REVOKEINSERTON*.*FROM'xqy'@'localhost';QueryOK,0rowsaffected(0.01sec)mysql>SHOWGRANTSFOR'xqy'@'localhost';+--------------------------------------------------------------------------------------------------+|Grantsforxqy@localhost |+--------------------------------------------------------------------------------------------------+|GRANTSELECTON*.*TO`xqy`@`localhost`WITHGRANTOPTION |+--------------------------------------------------------------------------------------------------+1rowinset(0.03sec)用戶“xqy”只有SELECT權(quán)限了，INSERT權(quán)限被成功收回了。再次查看用戶“xqy”的權(quán)限信息。-經(jīng)驗分享為了保證數(shù)據(jù)的安全，數(shù)據(jù)庫管理員要對權(quán)限進(jìn)行管理，合理的權(quán)限管理能夠保證數(shù)據(jù)庫系統(tǒng)的安全，不合理的權(quán)限設(shè)置可能會給數(shù)據(jù)庫帶來意想不到的危害。因此，管理數(shù)據(jù)庫時，應(yīng)注意只授予用戶能滿足需要的最小權(quán)限，創(chuàng)建用戶時限制用戶的登錄主機(jī)，定期清理不需要的用戶或者收回非必要的權(quán)限。模塊四

管理數(shù)據(jù)庫計算機(jī)系統(tǒng)的軟硬件故障、人為破壞和用戶誤操作等都有可能造成數(shù)據(jù)丟失或被破壞。數(shù)據(jù)庫備份就是為數(shù)據(jù)庫創(chuàng)建一個副本，以便在數(shù)據(jù)庫出現(xiàn)故障或者遭到破壞時恢復(fù)數(shù)據(jù)，從而將損失降到最低。本任務(wù)主要講解如何備份和還原數(shù)據(jù)庫。任務(wù)描述任務(wù)2數(shù)據(jù)庫備份與還原了解數(shù)據(jù)庫備份類型12理解數(shù)據(jù)庫備份與還原的作用3了解數(shù)據(jù)庫備份與還原的常用方法學(xué)會用圖形化工具和SQL語句備份和還原數(shù)據(jù)庫4任務(wù)目標(biāo)了解理解學(xué)會了解數(shù)據(jù)庫備份知識點(diǎn)1知識準(zhǔn)備數(shù)據(jù)庫備份是指導(dǎo)出數(shù)據(jù)或者以復(fù)制表文件的方式來制作數(shù)據(jù)庫的副本。1.備份類型（1）根據(jù)備份范圍，可分為完全備份、增量備份、差異備份。完全備份是指備份整個數(shù)據(jù)庫。增量備份是指備份自上一次完全備份或增量備份以來變化了的數(shù)據(jù)。差異備份是指備份自上一次完全備份以來變化的數(shù)據(jù)。（2）根據(jù)數(shù)據(jù)庫狀態(tài)，可分為熱備份、溫備份、冷備份。熱備份是指備份數(shù)據(jù)庫時，數(shù)據(jù)庫的讀寫操作不受影響。溫備份是指備份數(shù)據(jù)庫時，數(shù)據(jù)庫能進(jìn)行讀操作，但是不能進(jìn)行寫操作。冷備份是指備份數(shù)據(jù)庫時，需要關(guān)閉MySQL服務(wù)，不能進(jìn)行讀寫操作。數(shù)據(jù)庫備份知識點(diǎn)1知識準(zhǔn)備（3）根據(jù)備份方式，可分為物理備份、邏輯備份。物理備份是直接復(fù)制數(shù)據(jù)庫文件。邏輯備份是將數(shù)據(jù)庫中的數(shù)據(jù)備份為一個文本文件。數(shù)據(jù)庫備份知識點(diǎn)1知識準(zhǔn)備2.mysqldump命令在MySQL的bin目錄中，提供了mysqldump命令，用于將數(shù)據(jù)庫備份成一個文本文件，數(shù)據(jù)表的結(jié)構(gòu)和數(shù)據(jù)等信息都存儲在生成的文本文件中。mysqldump命令可以備份單個或多個數(shù)據(jù)庫。（1）備份單個數(shù)據(jù)庫基本語法格式如下：mysqldump-uusername-pdbname[tbname1[tbname2…]]>filename.sql指定用戶名。指定需要備份的數(shù)據(jù)庫名稱。指定數(shù)據(jù)庫中的表名，多個表名之間用空格隔開。如果不指定表名則表示備份整個數(shù)據(jù)庫。指定備份文件的名稱，文件名前可以加上路徑。通常備份文件的后綴名為“.sql”，也可以備份成其他格式。數(shù)據(jù)庫備份知識點(diǎn)1知識準(zhǔn)備（2）備份多個數(shù)據(jù)庫基本語法格式如下：（3）備份所有數(shù)據(jù)庫基本語法格式如下：mysqldump-uusername-p--databasesdbname1[dbname2…]>filename.sql后面至少應(yīng)指定一個數(shù)據(jù)庫，多個數(shù)據(jù)庫名之間用空格隔開。mysqldump-uusername-p--all-databases>filename.sql表示備份所有的數(shù)據(jù)庫。重點(diǎn)提示：mysqldump命令是在命令提示符窗口中執(zhí)行。備份單個數(shù)據(jù)庫時，備份文件中不包含創(chuàng)建數(shù)據(jù)庫和選擇數(shù)據(jù)庫的語句。而備份多個數(shù)據(jù)庫時，備份文件中包含創(chuàng)建和選擇數(shù)據(jù)庫的語句。數(shù)據(jù)庫還原知識點(diǎn)2知識準(zhǔn)備數(shù)據(jù)庫還原也稱為數(shù)據(jù)庫恢復(fù)，是指當(dāng)數(shù)據(jù)庫出現(xiàn)故障或遭到破壞時，將備份的數(shù)據(jù)庫加載到系統(tǒng)，從而使數(shù)據(jù)庫從錯誤狀態(tài)恢復(fù)到備份時的正確狀態(tài)。數(shù)據(jù)庫還原是以備份為基礎(chǔ)的，還原數(shù)據(jù)庫的方法應(yīng)與備份數(shù)據(jù)庫的方法相對應(yīng)。1.mysql命令使用mysql命令還原數(shù)據(jù)的基本語法格式如下：mysql-uusername–p[dbname]<filename.sql指定要還原的數(shù)據(jù)庫名稱。如果備份文件filename.sql中包含創(chuàng)建數(shù)據(jù)庫的語句，可以不指定數(shù)據(jù)庫名稱。提示：mysql命令也是在命令提示符窗口中執(zhí)行的。數(shù)據(jù)庫還原知識點(diǎn)2知識準(zhǔn)備2.source命令source是MySQL客戶端提供的命令，其基本語法格式如下：sourcefilename.sql說明：source命令的語法格式比較簡單，只需要指定備份文件的名稱及路徑就可以還原數(shù)據(jù)。任務(wù)實施1使用圖形化工具備份數(shù)據(jù)庫2使用SQL語句備份數(shù)據(jù)庫3使用圖形化工具還原數(shù)據(jù)4使用SQL語句還原數(shù)據(jù)【例1】備份數(shù)據(jù)庫beems。任務(wù)實施使用圖形化工具備份數(shù)據(jù)庫1【例2】使用mysqldump命令備份數(shù)據(jù)庫beems到F盤，備份文件名為beems.sql。步驟1：以管理員身份打開“命令提示符”窗口。步驟2：在命令提示符下輸入：cdC:\ProgramFiles\MySQL\MySQLServer8.0\bin將當(dāng)前目錄切換到MySQL安裝目錄下的bin目錄。步驟3：在命令提示符下輸入：mysqldump–uroot–pbeems>F:\beems.sql按回車鍵，再輸入密碼，備份數(shù)據(jù)庫。任務(wù)實施使用SQL語句備份數(shù)據(jù)庫2步驟4：備份完成后，在F盤中可以查看備份文件beems.sql。任務(wù)實施使用SQL語句備份數(shù)據(jù)庫2備份文件開頭包含mysqldump命令的版本號、備份數(shù)據(jù)的主機(jī)名、數(shù)據(jù)庫名和MySQL服務(wù)器的版本號。以“--”開頭的是SQL語句的注釋。以“/*!”開頭、以“*/”結(jié)尾的語句是可執(zhí)行的MySQL注釋，這些語句可以被特定版本的MySQL執(zhí)行，但在其他數(shù)據(jù)庫管理系統(tǒng)中作為注釋被忽略，這可以提高數(shù)據(jù)庫的可移植性。為了演示數(shù)據(jù)的還原，先