《數(shù)據(jù)庫應用技術項目化教程》第九章數(shù)據(jù)庫高級管理

數(shù)據(jù)備份恢復用戶管理權限管理第九章數(shù)據(jù)庫高級管理MySQL提供了一個mysqldump命令，它可以實現(xiàn)數(shù)據(jù)的備份。mysqldump命令可以備份單個數(shù)據(jù)庫、多個數(shù)據(jù)庫、所有數(shù)據(jù)庫或指定數(shù)據(jù)庫中的指定1個（或多個）數(shù)據(jù)表。

1）備份單個數(shù)據(jù)庫mysqldump命令備份數(shù)據(jù)庫的語法格式如下：mysqldump–uusername–ppassword數(shù)據(jù)庫名稱[數(shù)據(jù)表1名稱[數(shù)據(jù)表2名稱…]]>filename.sql9.1數(shù)據(jù)庫的備份與恢復9.1.1數(shù)據(jù)的備份備份數(shù)據(jù)庫

jwxt,備份文件名c:\jwxtbak.sql，SQL語句如下：mysqldump–uroot–p123456

jwxt

>

c:\jwxtbak.sql其中：登錄用戶名root，登錄密碼123456，需要備份的數(shù)據(jù)庫名稱jwxt需要注意的是，在使用mysqldump命令備份數(shù)據(jù)庫時，直接在DOS命令行窗口執(zhí)行該命令即可，不需要登錄到MySQL數(shù)據(jù)庫。9.1數(shù)據(jù)庫的備份與恢復9.1.1數(shù)據(jù)的備份實例9-1備份結(jié)束后，打開c:\jwxtbak.sql文件（可用記事本等程序打開），內(nèi)容如下：9.1數(shù)據(jù)庫的備份與恢復9.1.1數(shù)據(jù)的備份備份數(shù)據(jù)庫jwxt中的數(shù)據(jù)表

tb_bjdmb和tb_xsxxb,備份文件名c:\jwxt_bjdmb_bak.sql，SQL語句如下：mysqldump–uroot–p123456

jwxttb_bjdmbtb_xsxxb

>

c:\jwxt_bjdmb_bak.sql其中：登錄用戶名root，登錄密碼123456，需要備份的數(shù)據(jù)庫名稱jwxt，需要備份的數(shù)據(jù)表名稱tb_bjdmb、tb_xsxxb。9.1數(shù)據(jù)庫的備份與恢復9.1.1數(shù)據(jù)的備份實例9-23）備份所有數(shù)據(jù)庫使用mysqldump命令備份所有數(shù)據(jù)庫時，只需在該命令后使用“--all-databases”參數(shù)即可，其語法格式如下：mysqldump–uusername–ppassword--all-databases

>filename.sql需要注意的是，如果使用“--all-databases”參數(shù)備份了所有的數(shù)據(jù)庫，那么在恢復數(shù)據(jù)庫時，不需要創(chuàng)建數(shù)據(jù)庫并指定要操作的數(shù)據(jù)庫，因為，對應的備份文件中包含了CREATEDATABASE語句和USE語句。9.1數(shù)據(jù)庫的備份與恢復9.1.1數(shù)據(jù)的備份mysql–uusername–ppassword[dbname]<filename.sqlmysql命令恢復數(shù)據(jù)數(shù)據(jù)庫的語法格式如下：上述語法格式中，username表示登錄用戶名，password表示用戶密碼，dbname表示要恢復的數(shù)據(jù)庫名稱，如果使用mysqldump命令備份的filename.sql文件中包含創(chuàng)建數(shù)據(jù)庫的語句，則不需要指定數(shù)據(jù)庫；反之，則需要先創(chuàng)建需恢復的數(shù)據(jù)庫后，再進行恢復。9.1數(shù)據(jù)庫的備份與恢復9.1.2數(shù)據(jù)的恢復正常情況下，數(shù)據(jù)庫名稱是不能自動恢復的，因此在恢復數(shù)據(jù)之前必須先創(chuàng)建數(shù)據(jù)庫。接下來通過一個案例來學習數(shù)據(jù)的恢復，具體操作步驟如下：1）刪除數(shù)據(jù)庫jwxtDROPDATABASEjwxt;9.1數(shù)據(jù)庫的備份與恢復9.1.2數(shù)據(jù)的恢復實例9-3數(shù)據(jù)庫刪除完成，使用SHOWDATABASES語句查詢數(shù)據(jù)庫，查詢結(jié)果如下：9.1數(shù)據(jù)庫的備份與恢復9.1.2數(shù)據(jù)的恢復2）創(chuàng)建數(shù)據(jù)庫由于數(shù)據(jù)庫庫名稱是不能恢復的，因此先要創(chuàng)建一個數(shù)據(jù)庫jwxt，具體語句如下：CREATEDATABASEjwxt;上述語句執(zhí)行成功后，接下來就可以恢復數(shù)據(jù)庫中的數(shù)據(jù)。9.1數(shù)據(jù)庫的備份與恢復9.1.2數(shù)據(jù)的恢復3）恢復數(shù)據(jù)mysql-uroot-pjwxt<c:/jwxtbak.sqlEnterpassword:******使用mysql語句恢復c:\jwxtbak.sql文件，具體語句如下：上述語句執(zhí)行成功后，數(shù)據(jù)庫中的數(shù)據(jù)就會被恢復。此處沒有指定登錄用戶密碼，所以在登錄時需要輸入密碼（顯示的密碼以*代替）9.1數(shù)據(jù)庫的備份與恢復9.1.2數(shù)據(jù)的恢復

4）查看數(shù)據(jù)為了驗證數(shù)據(jù)已經(jīng)恢復成功，可以使用SELECT語句查詢數(shù)據(jù)庫jwxt中學生信息表tb_xsxxb的數(shù)據(jù)，查詢結(jié)果如下：9.1數(shù)據(jù)庫的備份與恢復9.1.2數(shù)據(jù)的恢復以上恢復方式只是其中的一種，我們還可以登錄到MySQL數(shù)據(jù)庫，使用source命令來恢復數(shù)據(jù)，source命令恢復數(shù)據(jù)的語法格式如下：source命令的語法格式比較簡單，只需要在導人文件的時候指定文件名字為全路徑名稱即可。按照該語句執(zhí)行，效果和在命令行執(zhí)行mysql命令是一樣的，這里不再詳述。9.1數(shù)據(jù)庫的備份與恢復use數(shù)據(jù)庫名稱;source[盤符:\]filename.sql注意：執(zhí)行此語句時，不要加結(jié)束符如;9.1.2數(shù)據(jù)的恢復在安裝MySQL時，會自動安裝一個名為mysql的數(shù)據(jù)庫，該數(shù)據(jù)庫中的表都是權限表，如user、db、host、tables_priv、column_priv和procs_priv，其中user表是最重要的一個權限表，它記錄了允許連接到服務器的賬號信息以及一些全局級的權限信息，通過操作該表就可以對這些信息進行修改。9.2用戶管理9.2.1user表使用如下SQL語句，可以查看已有用戶信息：usemysql;select

*fromuser;提示：user表的主要字段有：Host（主機）、User（用戶名）、Select_priv（查詢權限）、Insert_priv

（插入權限）、Update_priv

（更新權限）、Delete_priv

（刪除權限）、Create_priv（創(chuàng)建庫或表權限）、Drop_priv（刪除庫或表權限）、Create_view_priv（創(chuàng)建視圖權限）、Show_view_priv（查看視圖權限）、Trigger_priv（觸發(fā)器權限）、authentication_string（密碼）、password_expired（密碼是否過期）、account_locked（用戶是否鎖定）等9.2用戶管理9.2.1user表

1）使用CREATEUSER語句創(chuàng)建用戶

使用CREATEUSER語句創(chuàng)建新用戶時，服務器會自動修改相應的授權表，但需要注意的是，該語句創(chuàng)建的新用戶是沒有任何權限的。CREATEUSER語句創(chuàng)建用戶的語法格式如下：CREATEUSER'username'@'hostname'[IDENTIFIEDBY[password]'password'][,'username'@'hostname'[IDENTIFIEDBY[password]'password']]…創(chuàng)建新用戶有三種方式：9.2用戶管理9.2.2創(chuàng)建新用戶使用語句CREATEUSER創(chuàng)建一個數(shù)據(jù)庫jwxt的新用戶，用戶名為user1，密碼為abc1,首先使用USE語句，選擇數(shù)據(jù)庫jwxt后，再執(zhí)行語句如下:CREATEUSER'user1'@'localhost'IDENTIFIEDBY'abc1';語句執(zhí)行成功后，使用USE

mysql切換到系統(tǒng)數(shù)據(jù)庫mysql，執(zhí)行如下語句查看用戶是否創(chuàng)建成功（authentication_string字段為用戶密碼加密后的結(jié)果）：9.2用戶管理實例9-4

2）使用GRANT語句創(chuàng)建用戶GRANT語句不僅可以創(chuàng)建新用戶，還可以對用戶進行授權（將在后面講解），該語句會自動加載權限表，不需要手動刷新，而且安全、準確、錯誤少，因此，使用GRANT語句是創(chuàng)建用戶最常用的方法。GRANT語句創(chuàng)建用戶的語法格式如下：9.2用戶管理9.2.2創(chuàng)建新用戶GRANTpriv_typeONdatabase.tableTOuser[IDENTIFIEDBY[PASSWORD]'password']使用GRANT語句創(chuàng)建一個新用戶，用戶名為user2，密碼為abc2，并授予該用戶對學生信息表tb_xsxxb表有查詢權限（SELECT權限），GRANT語句如下：GRANTSELECTONjwxt.tb_xsxxbTO'user2'@'localhost'IDENTIFIEDBY'abc2';語句執(zhí)行成功后，可以查看用戶是否創(chuàng)建成功：9.2用戶管理實例9-5

3）使用INSERT語句創(chuàng)建用戶

不管是CREATEUSER語句還是GRANT語句，在創(chuàng)建用戶時，實際上都是在user表中添加一條新的記錄，因此，也可以使用INSERT語句直接在該表中添加一個用戶。INSERT語句創(chuàng)建用戶的語法格式如下：9.2用戶管理9.2.2創(chuàng)建新用戶INSERTINTOmysql.user(Host,User,authentication_string,ssl_cipher,x509_issuer,x509_subject)VALUES(hostname,username,PASSWORD(password),'','','');使用INSERT語句在mysql.user表中創(chuàng)建一個新用戶，用戶名為user3，密碼為abc3,INSERT語句如下:INSERTINTOmysql.user(Host,User,authentication_string,ssl_cipher,x509_issuer,x509_subject)VALUES('localhost','user3',PASSWORD('abc3'),'','','');語句執(zhí)行成功后，可以查看用戶是否創(chuàng)建成功：9.2用戶管理實例9-6由于INSERT語句沒有刷新權限表的功能，因此，mythird用戶暫時是不能使用的，為了讓當前用戶生效，還需要手動刷新當前的權限表或重新啟動MySQL服務，刷新權限表的語句如下：注意:FLUSHPRIVILEGES;執(zhí)行成功后，就可以使用user3用戶登錄MySQL數(shù)據(jù)庫。9.2用戶管理

1）使用DROPUSER語句刪除用戶

如果要刪除某個用戶，只需在DROPUSER后面指定要刪除的用戶信息即可。DROPUSER語句刪除用戶的語法格式如下：刪除用戶有兩種方式，接下來將針對這兩種方式進行詳細地講解。上述語法格式中，username標示要刪除的用戶hostname表示主機名，DROPUSER語句可以同時刪除一個或多個用戶，多個用戶之間用逗號隔開。值得注意的是，使用DROPUSER語句來刪除用戶時，必須擁有DROPUSER的權限。9.2用戶管理9.2.3刪除普通用戶

DROPUSER'username'@'hostname'[,'username'@'hostname'];DROPUSER'user3'@'localhost';root用戶登陸數(shù)據(jù)庫服務器，使用DROPUSER語句刪除用戶user3，SQL語句如下:

可以通過SELECT語句驗證用戶是否刪除成功：9.2用戶管理實例9-7

2）使用DELETE語句刪除用戶DELETE語句不僅可以刪除普通表中的數(shù)據(jù)，還可以刪除user表中的數(shù)據(jù)，使用該語句刪除user表中的數(shù)據(jù)時，只需指定表名為mysql.user，以及要刪除的用戶信息即可。同樣的，在使用DELETE語句時必須擁有對mysql.user表的DELETE權限。DELETE語句的語法格式如下：上述語法格式中，mysql.user參數(shù)指定要操作的表，WHERE指定條件語句，Host和User都是mysql.user表的字段，這兩個字段可以確定唯一的一條記錄。9.2用戶管理DELETEFROMmysql.userWHEREHOST='hostname'ANDUSER='username';DELETEFROMmysql.userWHEREHost='localhost'ANDUser='user2';root用戶登陸數(shù)據(jù)庫服務器，使用DELETE語句刪除用戶user2，SQL語句如下：可以通過SELECT語句驗證用戶是否刪除成功：9.2用戶管理實例9-81）修改用戶名

修改已有用戶user1的用戶名稱為user1_1，SQL語句如下：RENAMEUSER'user1'@'localhost'TO'user1_1'@'localhost';9.2用戶管理9.2.4修改用戶信息實例9-9mysqladmin–u用戶名–p原密碼password新密碼;2）使用mysqladmin命令修改用戶密碼在命令行窗口，使用mysqladmin命令，將root用戶的密碼修改為ABC，SQL語句如下:該語句執(zhí)行完畢，root用戶的密碼被修改，下次登錄時使用新的密碼。注意：MySQL密碼區(qū)別英文大小寫。9.2用戶管理9.2.4修改用戶信息

3）使用UPDATE語句修改用戶密碼由于所有的用戶信息都存放在mysql.user表中，因此，只要root用戶登錄到MySQL服務器，使用UPDATE語句就可以直接用戶密碼。UPDATE語句修改密碼的語法格式如下：UPDATEmysql.usersetPassword=PASSWORD('new_password')WHEREUser='username'andHost='hostname';9.2用戶管理9.2.4修改用戶信息

4）使用SET語句修改用戶的密碼

用戶登陸到MySQL服務器后，還可以通過SET語句來修改自己的密碼。SET語句修改密碼的語法格式如下：SETPASSWORD=PASSWORD('new_password');使用user1用戶登錄到MySQL服務器，使用SET語句將user1用戶的密碼修改為123456，SET語句如下:9.2用戶管理9.2.4修改用戶信息

實例9-10

MySqL數(shù)據(jù)庫中有多種類型的權限，這些權限信息被存儲在MySQL數(shù)據(jù)庫的user、db、host、tables_priv、column_priv和procs_priv表中。在MySQL啟動時，服務器將這些數(shù)據(jù)庫中的權限信息讀取到內(nèi)存中。9.3權限管理9.3.1MySQL的權限類型SHOWGRANTSFOR'username'@'hostname';

從上述語法格式可以看出，SHOWGRANTS語法格式比較簡單，只需要指定查詢的用戶名和主機名即可。為了方便查詢用戶的權限信息，MySQL還提供了一個SHOWGRANTS語句。SHOWGRANTS的語法格式如下：9.3權限管理9.3.2權限查詢使用SHOWGRANTS語句查看用戶root的權限信息，語句執(zhí)行結(jié)果如下：root用戶擁有所有權限，并且可以為其他用戶賦予權限。9.3權限管理實例9-11

數(shù)據(jù)庫中的用戶擁有不同的權限，合理的授權可以保證數(shù)據(jù)庫的安全。在MySQL中提供了一個GRANT語句，該語句可以為用戶授權。上述語法格式中，privileges表示權限類型，columns參數(shù)表示權限作用于某一列，該參數(shù)可以省略不寫，此時權限作用于整個表，username表示用戶名，hostname表示主機名，IDENTIFIEDBY參數(shù)為用戶設置密碼，PASSWORD參數(shù)為關鍵字，password為用戶的新密碼。WITH關鍵字后面可以帶有多個參數(shù)with_option。9.3權限管理9.3.3權限授予使用GRANT語句賦予用戶user