Mysql數(shù)據(jù)庫技術(shù)Mysql數(shù)據(jù)庫技術(shù)應(yīng)用（微課版）課件 項目7 視圖、索引和事務(wù)

項目6數(shù)據(jù)庫維護(hù)教材編寫組任務(wù)1用戶管理一、MySQL用戶1、超級管理員用戶（root）在Mysql安裝時自動創(chuàng)建，擁有全部權(quán)限，包括：創(chuàng)建用戶、刪除用戶和修改普通用戶密碼等。2、普通用戶由root創(chuàng)建，只擁有root分配的指定權(quán)限，包括：管理用戶的賬戶、權(quán)限等。3、示例例1：USEmysql；SELECTuser，hostFROMuser；任務(wù)1用戶管理二、MySQL用戶管理

MySQL的訪問控制分為兩個階段：第一階段進(jìn)行身份認(rèn)證，第二階段進(jìn)行權(quán)限認(rèn)證。1、創(chuàng)建用戶語法格式：CREATEUSER用戶名[@主機名][INDENTIFIEDBY‘密碼’]

任務(wù)1用戶管理說明：可以一次創(chuàng)建多個用戶，多個用戶之間使用逗號分隔。賬戶名稱由“用戶名稱@主機地址”組成，主機地址使用“l(fā)ocalhost”表示本地主機，如果省略主機名表示允許遠(yuǎn)程登錄(默認(rèn)主機名為%)。不選擇IDENTIFIEDBY選項時，表示該用戶無密碼。使用該命令的用戶必須擁有MySQL系統(tǒng)數(shù)據(jù)庫的全局CREATEUSER權(quán)限或INSERT權(quán)限，如root用戶。如果創(chuàng)建的賬戶已經(jīng)存在，則會出現(xiàn)錯誤。新建用戶權(quán)限有限，可以使用SHOW命令査看所有存儲引擎和字符集的列表，但無法訪問任何用戶數(shù)據(jù)庫，更不能訪問數(shù)據(jù)庫中的表?？梢允褂眯掠脩裘蛯?yīng)的密碼在MySQL命令行客戶端和MySQLWorkbench客戶端進(jìn)行登錄測試。示例例2：CREATEUSERt1@localhostIDENTIFIEDBY'123’;SELECTuser,hostFROMuser;任務(wù)1用戶管理示例例3：CREATEUSERt2IDENTIFEDBY'123456’;SELECTuser,hostFROMuser；任務(wù)1用戶管理示例例4：CREATEUSERt1;SELECTuser,hostFROMuser;任務(wù)1用戶管理任務(wù)1用戶管理2、修改用戶名語法格式：RENAMEUSER舊用戶名TO新用戶名[,…]說明：舊用戶名必須存在，可以一次給多個用戶重命名。示例：

例5：RENAMEUSER

t1@localhostTOteach1@localhost,

t2TOteach2;SELECTuser,hostFROMuser；任務(wù)1用戶管理3、修改密碼語法格式：SETPASSWORDFOR用戶名=’新密碼’或ALTERUSER用戶名[@主機名]IDENTIFIEDBY'新密碼'示例：

例6：SETPASSWORDFORteach2='abc123';任務(wù)1用戶管理4、刪除用戶語法格式：DROPUSER用戶1,用戶2.…

示例：

例7：SELECTuser,hostFROMmysql.user;DROPUSERt1;SELECTuser,hostFROMmysql.user；一、基本概念1、用戶賬戶在Mysql中，每個用戶都通過一個唯一的用戶名和主機名組合來標(biāo)識。用戶名：區(qū)分不同的用戶；主機名：指定用戶可以從哪些主機連接到數(shù)據(jù)庫服務(wù)器。2、權(quán)限類型數(shù)據(jù)訪問權(quán)限：SELECT、INSERT、UPDATE、DELETE等；數(shù)據(jù)定義權(quán)限：CREATE、DROP、ALTER等；管理權(quán)限：GRANTOPTION、PROCESS等。任務(wù)2權(quán)限管理3、權(quán)限級別全局級別：適用于整個MySQL服務(wù)器；數(shù)據(jù)庫級別：適用于特定的數(shù)據(jù)庫；表級別：適用于特定的表；列級別：進(jìn)一步細(xì)化到表的特定列。用戶權(quán)限級別管理員用戶：能夠執(zhí)行所有的數(shù)據(jù)庫操作，包括：創(chuàng)建和刪除數(shù)據(jù)庫、表以及存儲過程等；開發(fā)人員用戶：能夠修改數(shù)據(jù)庫結(jié)構(gòu)和編寫存儲過程，但不需要對數(shù)據(jù)庫進(jìn)行全局管理；普通用戶：只對特定的表進(jìn)行數(shù)據(jù)的查詢和插入操作。任務(wù)2權(quán)限管理二、查看權(quán)限語法格式：SHOWGRANTSFOR用戶名@主機名說明:對新用戶查看其權(quán)限時，顯示結(jié)果中的USAGE代表無權(quán)限。例8：SELECTuser,hostFROMmysql.user,SHOWGRANTSFORteach1@localhost;任務(wù)2權(quán)限管理三、授予權(quán)限語法格式：GRANT權(quán)限[列名列表]ON[目標(biāo)]{表|*|*.*|庫名.*}任務(wù)2權(quán)限管理說明:權(quán)限：權(quán)限的名稱，可以有多個，如SELECT、DELETE等。可以給不同對象授予不同的權(quán)限。ON關(guān)鍵字：要授予權(quán)限的數(shù)據(jù)庫名或表名，目標(biāo)可以是表(TABLE)、函數(shù)(FUNCTION)、存儲過程(PROCEDURE)等?！け砻罕砑墮?quán)限，適用于指定數(shù)據(jù)庫中的所有表。*：當(dāng)前數(shù)據(jù)庫的數(shù)據(jù)庫級權(quán)限。*.*：全局權(quán)限，適用于所有數(shù)據(jù)庫和所有表。庫名.*：指定數(shù)據(jù)庫中的所有表。TO子句用來設(shè)定用戶和密碼。權(quán)限還可以是“CREATEUSER”和“SHOWDATABASES”。WITH權(quán)限限制：使用WITHGRANTOPTION子句,將自己的權(quán)限授予其他用戶,而不管該用戶有無此權(quán)限。任務(wù)2權(quán)限管理示例例9：GRANTCREATE,UPDATE,SELECTONdbschool.*TOteach1@localhost;SHOWGRANTSFORteach1@localhost;任務(wù)2權(quán)限管理示例例10：GRANTCREATEUSERON*.*TOteach1@localhost；SHOWGRANTSFORteach1@localhost;任務(wù)2權(quán)限管理例11：root用戶授權(quán)給teach1用戶。其操作命令如下：GRANTINSERTONdbschool.classTOteach1@localhostWITHGRANTOPTION;

teach1將INSERT權(quán)限和SELECT權(quán)限授予用戶teach2。其操作命令如下:GRANTINSERT,SELECTONdbschool.classTOteach2；任務(wù)2權(quán)限管理四、回收權(quán)限語法格式：(1)REVOKE權(quán)限[列名列表]ON{表|*|*.*|庫名.*}FROM用戶或(2)REVOKEALLPRIVILEGES,GRANTOPTIONFROM用戶說明:REVOKE可以回收用戶權(quán)限，但不刪除該用戶，用法與GRANT相似，但效果相反。格式(1)回收用戶的指定權(quán)限，格式(2)回收用戶的所有權(quán)限。任務(wù)2權(quán)限管理例12：SHOWGRANTSFORteach1@localhost;REVOKEUPDATEONdbschool.*FROMteach1@localhost;任務(wù)2權(quán)限管理一、MySQL數(shù)據(jù)庫的備份1、邏輯備份（mysqldump）概念：是指將數(shù)據(jù)描述從MySQL數(shù)據(jù)庫導(dǎo)出到SQL文件中。mysqldump是MySQL自帶的邏輯備份工具。注意：通常由管理員用戶完成數(shù)據(jù)備份；在執(zhí)行mysqldump命令時，如果數(shù)據(jù)庫不存在，需要先創(chuàng)建數(shù)據(jù)庫。（1）備份整個數(shù)據(jù)庫語法格式：mysqldump–u用戶名–p[密碼]數(shù)據(jù)庫名>備份文件名.sql任務(wù)3數(shù)據(jù)庫的備份與恢復(fù)示例例13：

備份有以下兩種方法：①在cmd命令窗口執(zhí)行以下命令備份。mysqldump-uroot-pdbschool>D:\dbschool.sql

②在MySQLWorkbench客戶端備份。任務(wù)3數(shù)據(jù)庫的備份與恢復(fù)（2）備份數(shù)據(jù)庫中的表語法格式：mysqldump–u用戶名–p[密碼]數(shù)據(jù)庫名

表名>備份文件名.sql示例：例14：mysqldump-uroot-pdbschoolclass>D:\dbschool_class.sql（3）備份多個數(shù)據(jù)庫語法格式：mysqldump–u用戶名–p[密碼]--databases數(shù)據(jù)庫1數(shù)據(jù)庫2>備份文件名.sql示例：例15：mysqldump-uroot-p--databasesdbschoolmysql>D:\mysql_dbschool.sql任務(wù)3數(shù)據(jù)庫的備份與恢復(fù)（4）備份所有數(shù)據(jù)庫語法格式：mysqldump–u用戶名–p[密碼]--all-database>備份文件名.sql示例：例16：mysqldump-uroot-p--all-database>D:\dbschool_class.sql任務(wù)3數(shù)據(jù)庫的備份與恢復(fù)2、物理備份（xtrabackup）概念：是Percona提供的一個開源的免費數(shù)據(jù)庫熱備份軟件，是一個對InnoDB做數(shù)據(jù)備份的工具。物理備份直接復(fù)制數(shù)據(jù)庫的文件，因此備份和恢復(fù)通常比邏輯備份更快。語法格式：xtrabackup--backup--target-dir=/path/to/backupdir--datadir=/path/to/datadir說明：--target-dir是備份存放的目錄，--datadir是mysql的數(shù)據(jù)目錄。任務(wù)3數(shù)據(jù)庫的備份與恢復(fù)二、MySQL數(shù)據(jù)庫的恢復(fù)1、邏輯備份的恢復(fù)概念：是指數(shù)據(jù)庫發(fā)生故障或數(shù)據(jù)丟失的情況下，通過一系列操作和技術(shù)手段將數(shù)據(jù)庫恢復(fù)到正常運行狀態(tài)的過程。（1）邏輯備份的恢復(fù)在MySQL命令行使用SOURCE命令導(dǎo)入數(shù)據(jù)語法格式:SOURCE備份文件說明:如果服務(wù)器中沒有要恢復(fù)的數(shù)據(jù)庫，則要先創(chuàng)建，且用USE命令打開。任務(wù)3數(shù)據(jù)庫的備份與恢復(fù)示例例17：DROPDATABASEIFEXISTSdbschool;CREATEDATABASEdbschool;USEdbschool;SOURCED:\dbschool.sql任務(wù)3數(shù)據(jù)庫的備份與恢復(fù)root用戶在cmd窗口使用MySQL命令進(jìn)行恢復(fù)。語法格式:mysql–uroot–p數(shù)據(jù)庫名<備份文件說明:

恢復(fù)前確認(rèn)服務(wù)器中存在要恢復(fù)的數(shù)據(jù)庫。示例：例18：mysql–uroot–p

dbschool<d:\dbschool.sql在MySQLWorkbench客戶端進(jìn)行恢復(fù)。示例：例19：先刪除dbschool數(shù)據(jù)庫;再執(zhí)行Server菜單中的DataImport

命令;單擊窗口中間的New按鈕進(jìn)行創(chuàng)建，并將其設(shè)置為當(dāng)前數(shù)據(jù)庫;最后單擊StartImport

按鈕即可。任務(wù)3數(shù)據(jù)庫的備份與恢復(fù)2、物理備份的恢復(fù)（1）準(zhǔn)備備份文件以供恢復(fù)；語法格式：xtrabackup--prepare--target-dir=/path/to/backupdir（2）停止MySQL服務(wù)，并移動或替換當(dāng)前的數(shù)據(jù)目錄為備份目錄；語法格式：systemctlstopmysqlmv/path/to/current/datadir/path/to/current/datadir_oldmv/path/to/backupdir/path/to/current/datadir（3）啟動MySQL服務(wù)。語法格式：systemctlstartmysql注意：物理恢復(fù)可能需要額外的步驟來確保文件的權(quán)限和所有權(quán)正確，以及修復(fù)任何可能存在的表損壞問題。任務(wù)3數(shù)據(jù)庫的備份與恢復(fù)三、備份策略1、定期備份2、多種備份方式相結(jié)合3、保留多個備份版本4、驗證備份5、加密備份6、備份存儲7、備份恢復(fù)測試任務(wù)3數(shù)據(jù)庫的備份與恢復(fù)一、硬件和基礎(chǔ)設(shè)施優(yōu)化1、存儲優(yōu)化選擇高性能的存儲設(shè)備，加快數(shù)據(jù)讀寫速度；采用RAID技術(shù)，提高數(shù)據(jù)的可靠性和讀寫性能；合理配置磁盤分區(qū)和文件系統(tǒng)，避免IO瓶頸。2、內(nèi)存優(yōu)化增加物理內(nèi)存，確保數(shù)據(jù)庫有足夠的內(nèi)存來緩存數(shù)據(jù)和索引；調(diào)整MySQL的內(nèi)存相關(guān)配置參數(shù)，如innodb_buff