實(shí)訓(xùn)案例名稱(chēng)：用戶管理一、任務(wù)介紹MySQL數(shù)據(jù)庫(kù)的用戶管理包括創(chuàng)建用戶、修改用戶、刪除用戶等操作。下面創(chuàng)建一個(gè)用戶名為xiaogang的用戶，密碼設(shè)置為123456，然后查詢數(shù)據(jù)庫(kù)里有哪些用戶。二、實(shí)現(xiàn)步驟1.創(chuàng)建用戶#使用用戶名root和相應(yīng)密碼，連接本地MySQLC:\Users\Administrator>mysql-uroot-p123456mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.#創(chuàng)建用戶xiaogang，任何IP地址的客戶端都可以訪問(wèn)mysql>CREATEuser'xiaogang'@'%'identifiedby'123456';QueryOK,0rowsaffected(0.22sec)#創(chuàng)建用戶xiaoying，只有本地的客戶端才可以訪問(wèn)mysql>CREATEuser'xiaoying'@'localhost'identifiedby'123456';QueryOK,0rowsaffected(0.00sec)#創(chuàng)建用戶xiaoming，只有指定IP地址0的客戶端才可以訪問(wèn)mysql>CREATEuser'xiaoming'@'0'identifiedby'123456';QueryOK,0rowsaffected(0.00sec)#查詢數(shù)據(jù)庫(kù)有哪些用戶mysql>SELECTuser,hostFROMmysql.user;+------------------+--------------+|user

|host

|+------------------+--------------+|shopdb

|%

||xiaogang

|%

||xiaoming

|0

||schema|localhost

||mysql.session

|localhost

||mysql.sys

|localhost

||root

|localhost

||xiaoying

|localhost

|+------------------+--------------+8rowsinset(0.00sec)使用新創(chuàng)建的用戶xiaogang連接數(shù)據(jù)庫(kù)，語(yǔ)句所示。#使用用戶xiaogang連接數(shù)據(jù)庫(kù)C:\Users\Administrator>mysql-uxiaogang-p123456mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.mysql>2.修改用戶下面將用戶為xiaogang的密碼設(shè)置為123456789，并將xiaoying的客戶端ip限制改為00，如下所示。#使用用戶名root和相應(yīng)密碼，連接本地MySQLC:\Users\Administrator>mysql-uroot-p123456mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.#修改用戶xiaogang的密碼為123456789mysql>ALTERUSER'xiaogang'@'%'IDENTIFIEDBY'123456789';QueryOK,1rowaffected,1warning(0.00sec)Rowsmatched:1Changed:1Warnings:1#退出連接mysql>exitBye#使用用戶xiaogang和修改后的密碼進(jìn)行連接，連接失敗C:\Users\Administrator>mysql-uxiaogang-p123456789mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.ERROR1045(28000):Accessdeniedforuser'xiaogang'@'localhost'(usingpassword:YES)#使用root用戶連接C:\Users\Administrator>mysql-uroot-p123456mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.#已經(jīng)擁有reload權(quán)限，如果沒(méi)有，則需要授權(quán)mysql>flushprivileges;QueryOK,0rowsaffected(0.00sec)#退出連接mysql>exitBye#使用用戶xiaogang和修改后的密碼進(jìn)行連接，連接成功C:\Users\Administrator>mysql-uxiaogang-p123456789mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.#退出連接mysql>exitBye#使用root用戶連接C:\Users\Administrator>mysql-uroot-p123456mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.#修改用戶xiaoying可以訪問(wèn)數(shù)據(jù)庫(kù)的客戶端IP地址mysql>updatemysql.usersethost='00'whereuser='xiaoying';QueryOK,1rowaffected(0.00sec)Rowsmatched:1Changed:1Warnings:0#查看用戶mysql>SELECTuser,hostFROMmysql.user;+-------------------------------+-----------------------------+|user

|host

|+-------------------------------+-----------------------------+|shopdb

|%

||xiaogang

|%

||xiaoying

|00

||xiaoming

|0

||mysql.session

|localhost

||mysql.sys

|localhost

||root

|localhost

|+-------------------------------+-----------------------------+7rowsinset(0.00sec)mysql>3.刪除用戶下面將xiaoying用戶刪除，執(zhí)行刪除命令后，需要執(zhí)行flushprivileges;命令或者重啟一下MySQL服務(wù)，才能生效。#使用用戶名root和相應(yīng)密碼，連接本地MySQLC:\Users\Administrator>mysql-uroot-p123456mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.#查看所有用戶mysql>SELECTuser,hostFROMmysql.user;+-------------------------------+-----------------------------+|user

|host |+-------------------------------+-----------------------------+|shopdb

|% ||xiaogang

|% ||xiaoying

|00 ||xiaoming

|0 ||mysql.session

|localhost ||mysql.sys

|localhost ||root

|localhost |+-------------------------------+-----------------------------+7rowsinset(0.00sec)#刪除用戶xiaoying，host為localhostmysql>DELETEFROMmysql.userWHEREuser='xiaoying'andhost='localhost';QueryOK,0rowsaffected(0.00sec)#查看用戶，用戶xiaoying對(duì)應(yīng)的host是00，并沒(méi)有刪除成功mysql>SELECTuser,hostFROMmysql.user;+-------------------------------+-----------------------------+|user

|host |+-------------------------------+-----------------------------+|shopdb

|% ||xiaogang

|% ||xiaoying

|00 ||xiaoming

|0 ||mysql.session

|localhost ||mysql.sys

|localhost ||root

|localhost |+-------------------------------+-----------------------------+7rowsinset(0.00sec)#刪除用戶xiaoying，host為“00”，有一行數(shù)據(jù)受到影響，說(shuō)明刪除有效mysql>DELETEFROMmysql.userWHEREuser='xiaoying'andhost='00';QueryOK,1rowaffected(0.00sec)#查看所有用戶，發(fā)現(xiàn)用戶xiaoying依然存在mysql>SELECTuser,hostFROMmysql.user;+-------------------------------+-----------------------------+|user

|host |+-------------------------------+-----------------------------+|shopdb

|% ||xiaogang

|% ||xiaoying

|00 ||xiaoming

|0 ||mysql.session

|localhost ||mysql.sys

|localhost ||root

|localhost |+-------------------------------+-----------------------------+7rowsinset(0.00sec)#執(zhí)行刷新操作mysql>flushprivileges;QueryOK,0rowsaffected(0.00sec)#查看所有用戶，用戶xiaoying刪除成功mysql>SELECTuser,hostFROMmysql.user;+-------------------------------+-----------------------------+|user

|host |+-------------------------------+-----------------------------+|shopdb

|% ||xiaogang

|% ||xiaoming

|0 ||mysql.session

|localhost ||mysql.sys

|localhost ||root

|localhost |+-------------------------------+-----------------------------+6rowsinset(0.00sec)實(shí)訓(xùn)案例名稱(chēng)：權(quán)限管理一、任務(wù)介紹下面將創(chuàng)建數(shù)據(jù)庫(kù)、表、索引的權(quán)限賦予xiaogang這個(gè)用戶，然后再撤回這個(gè)權(quán)限，接著授予所有權(quán)限。二、實(shí)現(xiàn)步驟#使用用戶名root和相應(yīng)密碼，連接本地MySQLC:\Users\Administrator>mysql-uroot-p123456mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.#顯示用戶xiaogang的權(quán)限，USAGE表示沒(méi)有任何權(quán)限mysql>SHOWGRANTSFORxiaogang;+---------------------------------------------------------+|Grantsforxiaogang@%|+---------------------------------------------------------+|GRANTUSAGEON*.*TO'xiaogang'@'%'WITHGRANTOPTION|+---------------------------------------------------------+1rowinset(0.00sec)#將創(chuàng)建數(shù)據(jù)庫(kù)、表、索引的權(quán)限授予用戶xiaogangmysql>GRANTCREATEON*.*TO'xiaogang'@'%'WITHGRANTOPTION;QueryOK,0rowsaffected,1warning(0.00sec)#顯示用戶xiaogang的權(quán)限，該用戶擁有創(chuàng)建數(shù)據(jù)庫(kù)、表、索引的權(quán)限mysql>SHOWGRANTSFORxiaogang;+---------------------------------------------------------+|Grantsforxiaogang@%|+---------------------------------------------------------+|GRANTCREATEON*.*TO'xiaogang'@'%'WITHGRANTOPTION|+---------------------------------------------------------+1rowinset(0.00sec)#撤銷(xiāo)用戶xiaogang創(chuàng)建數(shù)據(jù)庫(kù)、表、索引的權(quán)限mysql>REVOKECREATEON*.*FROM'xiaogang'@'%';QueryOK,0rowsaffected(0.00sec)#權(quán)限撤銷(xiāo)后，用戶xiaogang無(wú)任何權(quán)限mysql>SHOWGRANTSFORxiaogang;+---------------------------------------------------------+|Grantsforxiaogang@%|+---------------------------------------------------------+|GRANTUSAGEON*.*TO'xiaogang'@'%'WITHGRANTOPTION|+---------------------------------------------------------+1rowinset(0.00sec)#授予用戶xiaogang所有權(quán)限mysql>GRANTALLPRIVILEGESON*.*TO'xiaogang'@'%'WITHGRANTOPTION;QueryOK,0rowsaffected,1warning(0.00sec)#顯示用戶xiaogang的權(quán)限，該用戶擁有所有權(quán)限mysql>SHOWGRANTSFORxiaogang;+-----------------------------------------------------------------+|Grantsforxiaogang@%|+-----------------------------------------------------------------+|GRANTALLPRIVILEGESON*.*TO'xiaogang'@'%'WITHGRANTOPTION|+-----------------------------------------------------------------+1rowinset(0.00sec)#查詢用戶xiaogang擁有的具體權(quán)限mysql>SELECT*FROMmysql.userWHEREuser='xiaogang'\G***************************1.row***************************Host:%User:xiaogangSelect_priv:YInsert_priv:YUpdate_priv:YDelete_priv:YCreate_priv:YDrop_priv:YReload_priv:YShutdown_priv:YProcess_priv:YFile_priv:YGrant_priv:YReferences_priv:YIndex_priv:YAlter_priv:YShow_db_priv:YSuper_priv:YCreate_tmp_table_priv:YLock_tables_priv:YExecute_priv:YRepl_slave_priv:YRepl_client_priv:YCreate_view_priv:YShow_view_priv:YCreate_routine_priv:YAlter_routine_priv:YCreate_user_priv:YEvent_priv:YTrigger_priv:YCreate_tablespace_priv:Yssl_type:ssl_cipher:0xx509_issuer:0xx509_subject:0xmax_questions:0max_updates:0max_connections:0max_user_connections:0plugin:caching_sha2_passwordauthentication_string:$A$005$7password_expired:Npassword_last_changed:2023-06-1500:04:12password_lifetime:NULLaccount_locked:NCreate_role_priv:YDrop_role_priv:YPassword_reuse_history:NULLPassword_reuse_time:NULLPassword_require_current:NULLUser_attributes:NULL1rowinset(0.00sec)實(shí)訓(xùn)案例名稱(chēng)：備份和還原一、任務(wù)介紹下面使用MySQL數(shù)據(jù)庫(kù)自帶的備份工具mysqldump進(jìn)行備份，將員工管理系統(tǒng)數(shù)據(jù)庫(kù)staff備份出來(lái)。二、實(shí)現(xiàn)步驟1.備份#備份圖書(shū)管理系統(tǒng)數(shù)據(jù)庫(kù)booksC:\Users\Administrator>mysqldump-uroot--password=123456books>C:\MySQL_bak\

books_20250706.sql#備份IP地址為0的服務(wù)器中的圖書(shū)管理系統(tǒng)數(shù)據(jù)庫(kù)booksC:\Users\Administrator>mysqldump--opt-uroot--password=123456-h0books>C:\MySQL_bak\books_20250706.sql#備份圖書(shū)管理系統(tǒng)數(shù)據(jù)庫(kù)books中的圖書(shū)借閱表book_borrowC:\Users\Administrator>mysqldump-uroot--password=123456booksbook_borrow>

C:\MySQL_bak\books_20250706.sqlmysqldump通用的備份腳本如下。@echooffsetBAT_HOME=C:\MySQL_baksetDaysAgo=30forfiles/p%BAT_HOME%/s/m*.*/d-%DaysAgo%/c"cmd/cdel@path"setBKDIR=%Date:~0,4%%Date:~5,2%setBKFILE=%Date:~0,4%%Date:~5,2%%Date:~8,2%setPATH=C:\ProgramFiles\MySQL\MySQLServer8.0\binmkdir%BAT_HOME%\%BKDIR%mysqldump--opt-uroot--password=123456-h0books>

%BAT_HOME%\%BKDIR%\staff_%BKFILE%.sqlecho"數(shù)據(jù)庫(kù)備份完成！"2.還原mysql>USEbooksmysql>sourceC:\MySQL_bak\books_20250706.sql;實(shí)訓(xùn)案例名稱(chēng)：主從同步配置一、任務(wù)介紹數(shù)據(jù)庫(kù)在使用過(guò)程中，為了保證數(shù)據(jù)的安全性，往往會(huì)進(jìn)行備份操作，這時(shí)就可以使用主從數(shù)據(jù)庫(kù)同步配置，將主數(shù)據(jù)庫(kù)中的數(shù)據(jù)同步到從數(shù)據(jù)庫(kù)里，以達(dá)到備份的目的。二、實(shí)現(xiàn)步驟1.主數(shù)據(jù)庫(kù)配置主數(shù)據(jù)庫(kù)配置需要在my.ini配置文件中添加參數(shù)、分配用于同步的用戶、查找同步的二進(jìn)制日志文件和位置。（1）打開(kāi)MySQL數(shù)據(jù)庫(kù)的my.ini配置文件，添加數(shù)據(jù)庫(kù)唯一標(biāo)識(shí)server-id（主數(shù)據(jù)庫(kù)和從數(shù)據(jù)庫(kù)需要不一致），開(kāi)啟log-bin日志以及需要同步的數(shù)據(jù)庫(kù)binlog-do-db，binlog-ignore-db用來(lái)配置不需要同步的數(shù)據(jù)庫(kù)。server-id=1 #主數(shù)據(jù)庫(kù)和從數(shù)據(jù)庫(kù)需要不一致log-bin=mysql-bin #開(kāi)啟log-bin二進(jìn)制日志文件binlog-do-db=db_test #需要同步的數(shù)據(jù)庫(kù)binlog-ignore-db=staff #不需要同步的數(shù)據(jù)庫(kù)（2）查看log-bin日志文件是否開(kāi)啟成功，只有l(wèi)og-bin等于ON，文件開(kāi)啟成功，才可以進(jìn)行同步操作。mysql>showvariableslike'log_bin%';+----------------------------------+--------------------------------+|Variable_name|Value|+----------------------------------+--------------------------------+|log_bin|ON||log_bin_basename|…||log_bin_index|…||log_bin_trust_function_creators|OFF||log_bin_use_v1_row_events|OFF|+----------------------------------+--------------------------------+5rowsinset,1warning(0.00sec)（3）分配用于同步的賬號(hào)backup，密碼是123456。GRANTREPLICATIONSLAVEON*.*to'backup'@'%';（4）查看同步的二進(jìn)制日志文件名稱(chēng)mysql-bin.000002和位置437，用于在進(jìn)行從數(shù)據(jù)庫(kù)同步配置時(shí)使用。mysql>showmasterstatus;+---------------+--------+-------------+----------------+-----------------+|+---------------+--------+-------------+----------------+-----------------++---------------+--------+-------------+----------------+-----------------+2.從數(shù)據(jù)庫(kù)配置從數(shù)據(jù)庫(kù)配置需要在my.ini配置文件中添加參數(shù)、配置同步的主數(shù)據(jù)庫(kù)、開(kāi)啟同步的從數(shù)據(jù)庫(kù)。（1）打開(kāi)MySQL數(shù)據(jù)庫(kù)的my.ini配置文件，添加數(shù)據(jù)庫(kù)唯一標(biāo)識(shí)server-id（主數(shù)據(jù)庫(kù)和從數(shù)據(jù)庫(kù)需要不一致），開(kāi)啟log-bin日志以及需要同步的數(shù)據(jù)庫(kù)replicate-do-db，并把從數(shù)據(jù)庫(kù)設(shè)置為只讀數(shù)據(jù)庫(kù)。server-id=2 #主數(shù)據(jù)庫(kù)和從數(shù)據(jù)庫(kù)需要不一致log-bin=mysql-bin #開(kāi)啟log-bin二進(jìn)制日志文件replicate-do-db=db_test #需要同步的數(shù)據(jù)庫(kù)read_only #（2）配置同步的數(shù)據(jù)庫(kù)，MASTER_HOST是主數(shù)據(jù)ip，MASTER_PORT是主數(shù)據(jù)庫(kù)端口號(hào)，MASTER_USER是主數(shù)據(jù)庫(kù)分配的同步賬號(hào)，MASTER_LOG_FILE是主數(shù)據(jù)庫(kù)同步的二進(jìn)制日志文件，MASTER_LOG_POS是同步文件的位置。CHANAGEMASTERTOMASTER_HOST='0',MASTER_PORT=3306,MASTER_USER='backup',MASTER_PASSWORD='123456',MASTER_LOG_FILE='mysql-bin.000002',MASTER_LOG_POS=437;（3）開(kāi)啟從數(shù)據(jù)庫(kù)同步STARTSLAVE。mysql>STARTSLAVE;（4）查看從數(shù)據(jù)庫(kù)同步狀態(tài)SHOWSLAVESTATUS，查看同步的二進(jìn)制日志文件名稱(chēng)mysqlbin.000002和位置437，Slave_IO_Running等于Yes和Slave_SQL_Running等于Yes說(shuō)明同步配置成功，可以用于主從數(shù)據(jù)庫(kù)同步。mysql>SHOWSLAVESTATUS\G***************************1.row***************************Slave_IO_State:WaitingformastertosendeventMaster_Host:0 #主數(shù)據(jù)庫(kù)的IP地址Master_User:backup #主數(shù)據(jù)庫(kù)分配的同步賬號(hào)Master_Port:3306 #主數(shù)據(jù)庫(kù)的端口號(hào)Connect_Retry:60M #主數(shù)據(jù)庫(kù)同步的二進(jìn)制日志文件Read_Master_Log_Pos:437 #主數(shù)據(jù)庫(kù)同步的二進(jìn)制日志文件的位置Relay_Log_File:WINDOWS-MKCKMF8-relay-bin.000002Relay_Log_Pos:320Relay_Master_Log_File:mysql-bin.000002Slave_IO_Running:YesSlave_SQL_Running:YesReplicate_Do_DB:db_test#要同步的數(shù)據(jù)庫(kù)Replicate_Ignore_DB:Replicate_Do_Table:Replicate_Ignore_Table:Replicate_Wild_Do_Table:Replicate_Wild_Ignore_Table:Last_Errno:0Last_Error:ip_Counter:0Exec_Master_Log_Pos:643Relay_Log_Space:537Until_Condition:NoneUntil_Log_File:Until_Log_Pos:0Master_S