實驗訓(xùn)練4：數(shù)據(jù)庫系統(tǒng)維護

實驗訓(xùn)練4：數(shù)據(jù)庫系統(tǒng)維護實驗?zāi)康模夯趯嶒?創(chuàng)建的汽車用品網(wǎng)上商城，練習(xí)創(chuàng)建用戶、權(quán)限管理，數(shù)據(jù)庫備份與恢復(fù)方法，數(shù)據(jù)導(dǎo)出導(dǎo)入的方法，體會數(shù)據(jù)庫系統(tǒng)維護的主要工作。實驗內(nèi)容：1．?dāng)?shù)據(jù)庫安全性【實驗6-1】建立賬戶：創(chuàng)建一個用戶名為‘Teacher’密碼為‘T99999’的用戶；創(chuàng)建一個用戶名為‘Student’密碼為‘S11111’的用戶。>grantselect,insert,update,deleteon*.*toTeacher"Identifiedby"T99999";>

flushprivileges;>

flushprivileges;>GRANTSElECTON*.*TO'reader'@'%'

IDENTIFIEDBY"passwd";>

flushprivileges;>GRANTALLPRIVILEGESON*.*TO'cacti'@'%'IDENTIFIEDBY'cacti'WITHGRANTOPTION;>FLUSHPRIVILEGES;>REVOKEALLPRIVILEGESON*.*FROMcacti;>REVOKEALLPRIVILEGESONcacti.*FROMcacti;>REVOKEGRANTOPTIONON*.*FROMcacti;>FLUSHPRIVILEGES;>

grantallondb.*to'user'@'%'identifiedby"passwd";>

grantallondb.*to'user'@'localhost'identifiedby"passwd";>

flushprivileges;【實驗6-2】用戶授權(quán)：將Shopping數(shù)據(jù)庫上SELECT、INSERT、DELETE、UPDATE的權(quán)限授予‘Teacher’用戶；將Shopping數(shù)據(jù)庫上SELECT的權(quán)限授予‘Student’用戶。grantselect,insert,update,deleteonredmine1.*to

jira@"%"identifiedby"jira";GRANTALLPRIVILEGESON*.*TO'myuser'@''IDENTIFIEDBY

'mypassword'WITHGRANTOPTION;

FLUSH

PRIVILEGES;

【實驗6-3】以‘Teacher’用戶身份連接Shopping數(shù)據(jù)庫，分別執(zhí)行SELECT、INSERT、DELETE、UPDATE、CREATE操作，查看執(zhí)行結(jié)果；以‘Student’用戶身份連接Shopping數(shù)據(jù)庫，執(zhí)行SELECT、INSERT、DELETE、UPDATE操作，查看執(zhí)行結(jié)果。2．?dāng)?shù)據(jù)庫備份與恢復(fù)【實驗6-4】使用mysqldump工具對Shopping數(shù)據(jù)庫進行備份，查看備份文件。[mysql@redhat6MysqlDb_Backup]$mysqldump-uroot-p--single-transaction--master-data--flush-logs--all-databases>FullBackup_2018-04-09.sqlEnterpassword:--Warning:Skippingthedataoftablemysql.event.Specifythe--eventsoptionexplicitly.[mysql@redhat6MysqlDb_Backup]$ls-ltrtotal2123616-rw-rw-r--1mysqlmysql2137635937Apr809:45Full-Backup_On_2018-04-08.sql-rw-rw-r--1mysqlmysql11542439Apr911:34Backup_2018-04-09.sql-rw-rw-r--1mysqlmysql5943Apr915:00Backup_store_basic_info.sql-rw-rw-r--1mysqlmysql25389763Apr915:04FullBackup_2018-04-09.sql--MySQLdump10.13Distrib5.5.57,forlinux-glibc2.12(x86_64)----Host:localhostDatabase:----------------------------------------------------------Serverversion5.5.57-log【實驗6-5】對Shopping數(shù)據(jù)庫啟用二進制日志，并且查看日志。【實驗6-6】使用mysqldump工具對Shopping數(shù)據(jù)庫進行恢復(fù)，查看恢復(fù)前后Shopping數(shù)據(jù)庫的數(shù)據(jù)狀態(tài)。在/etc/f文件中【mysqld】下加上：

server-id=1（在整個Mysql集群中保證唯一）

log-bin=binlog

log-bin-index=binlog.index重啟Mysql：

MYSQL啟用和查看二進制日志查看是否啟用了日志mysql>showvariableslike'log_bin';查看當(dāng)前的日志mysql>showmasterstatus;看二進制日志文件用mysqlbinlogshell>mysqlbinlogmail-bin.000001

查看表結(jié)構(gòu)showcolumnsfromtables；3．?dāng)?shù)據(jù)導(dǎo)入導(dǎo)出【實驗6-7】分別使用SELECT…INTO、MySQL命令、MySQLWorkbench完成Shopping數(shù)據(jù)庫中會員表和汽車配件表的導(dǎo)出，查看導(dǎo)出結(jié)果?！緦嶒?-8】分別使用LOADDATA、MySQLIMPORT、MySQLWorkbench完成Shopping數(shù)據(jù)庫中會員表和汽車配件表的導(dǎo)入，查看導(dǎo)入結(jié)果。Subrandnum()Dimj,pAsInteger,tem,x(16)AsStringx(1)="0"x(2)="1"x(3)="2"x(4)="3"x(5)="4"x(6)="5"x(7)="6"x(8)="7"x(9)="8"x(10)="9"x(11)="A"x(12)="B"x(13)="C"x(14)="D"x(15)="E"x(16)="F"Forj=1To32Randomizep=Int(16*Rn