數(shù)據(jù)庫(kù)運(yùn)維形考實(shí)驗(yàn)1

1、實(shí)驗(yàn)1MySQL數(shù)據(jù)庫(kù)服務(wù)器配置實(shí)驗(yàn)?zāi)康恼莆誐ySQL的安裝方法，練習(xí)MySQL數(shù)據(jù)庫(kù)服務(wù)器的使用，理解MySQL服務(wù)器的組成，掌握MySQL服務(wù)器的配置方法。實(shí)驗(yàn)內(nèi)容【實(shí)驗(yàn)1-1】下載當(dāng)前最新版本的MySQL，或者之前某個(gè)版本的MySQL，并在Windows上完成安裝?！緦?shí)驗(yàn)1-2】安裝完畢后，找到MySQL的配置文件；并查看初始化配置文件的內(nèi)容?！緦?shí)驗(yàn)1-3】使用start和stop命令啟動(dòng)和關(guān)閉mysql數(shù)據(jù)庫(kù)?！緦?shí)驗(yàn)1-4】連接到MySQL服務(wù)器，連接成功后是一個(gè)mysql的提示。【實(shí)驗(yàn)1-5】在MySQL數(shù)據(jù)庫(kù)服務(wù)器端找到錯(cuò)誤日志文件，并查看錯(cuò)誤日志的內(nèi)容?！緦?shí)驗(yàn)1-6】通過(guò)初始化配

2、置文件，啟用二進(jìn)制日志、慢查詢?nèi)罩竞屯ㄓ貌樵內(nèi)罩?。【?shí)驗(yàn)1-7】查看二進(jìn)制日志、慢查詢?nèi)罩竞屯ㄓ貌樵內(nèi)罩镜膬?nèi)容?！緦?shí)驗(yàn)1-8】關(guān)閉二進(jìn)制日志、慢查詢?nèi)罩竞屯ㄓ貌樵內(nèi)罩?。【?shí)驗(yàn)1-9】用CREATEDATABASE語(yǔ)句創(chuàng)建數(shù)據(jù)庫(kù)，使用SHOWDATABASES命令查看顯示所有數(shù)據(jù)庫(kù)，可以看到新建的數(shù)據(jù)庫(kù)名稱。【實(shí)驗(yàn)1-10】使用Workbench圖形化管理工具創(chuàng)建數(shù)據(jù)庫(kù)?！緦?shí)驗(yàn)1-11】使用SHOWSTATUS命令查看系統(tǒng)狀態(tài)參數(shù)；使用SHOWVARIABLES命令查看服務(wù)器變量設(shè)置?！緦?shí)驗(yàn)1-12】使用命令“selectbasedir”和“selectdatadir”尋找到MySQL的安裝目錄

3、和數(shù)據(jù)存放目錄。實(shí)驗(yàn)1-13】使用SHOWENGINES查看所有引擎，在配置文件中更改默認(rèn)存儲(chǔ)引擎default-storage-engine變量，改為MylSAM。重啟MySQL,查看默認(rèn)存儲(chǔ)引擎是否改變。【實(shí)驗(yàn)1-14】創(chuàng)建一個(gè)數(shù)據(jù)表t_myisam(idintauto_increment,namevarchar(30)，primarykey(id)，創(chuàng)建時(shí)顯式指定存儲(chǔ)引擎為MylSAM。使用“ALTERTABLETABLE_NAMEENGlNE=ENGlNE_NAME”語(yǔ)句，更改表t_myisam的存儲(chǔ)引擎為lnnoDB?！緦?shí)驗(yàn)1-15】使用SHOWTABLESTATUSFROMDB_N

4、AMEWHERENAME=TABLE_NAME和“SHOWCREATETABLETABLE_NAME，命令查看數(shù)據(jù)庫(kù)DB_NAME下表TABLE_NAME的存儲(chǔ)引擎?！緦?shí)驗(yàn)1-16】lnnoDB的l/O相關(guān)配置，根據(jù)公式：事務(wù)日志的總大小=lnnodb_log_files_size*lnnodb_log_files_in_group來(lái)設(shè)置64M大小的事務(wù)日志緩沖區(qū)并設(shè)置在每次事務(wù)提交執(zhí)行l(wèi)og寫(xiě)入cache，并flush到磁盤(pán)。實(shí)驗(yàn)結(jié)果截圖eTXG0AMtpiy/wwvrmyuqlxcxn/downlond%/US侖llOtjaX館砒：政亦仏吃Xm耐tHWgXmi5?1加3金XMySQL.MS

5、QLXHiebimpqpotarcensourcedatobMtQcontactMySQL|login|RenterMYSQL.COMDOWNLOADSDOCUMENTATIONDEVELOPERZONEMySQLDatabaseServiceMySQLDatabaseServiceisafullymanageddatabaseservicetodeploycloudnativeapplicationsusingtheworldsmostpopularopensourcedatabase.FullyManagedDatabaseServiceinstantProylsJonlngAutam/i

6、ticBackup%UHdMySQLFeaturesundSecurityRxeDataProtectionRegulatoryCompliance100HDevelopedandManagedbytheMySQLTeam100HSupportedbytheMySQLTeam100UCompatibility皿thOn-PremisesMySQL100MBuiltonMySQLFnlt?rprtseFditionIntegrationwithOracleTechnologicBuiltonOracleGn2OoudInfrastructureLeamMoreContactSalesMySQLE

7、nterpriseEditionIICA-Qf.QTME左與AWH.*ql.coni逬齊RS恵手MySQLEnterpriseEditionincludesthemostcomprehcnsrvcsetofadvancedfeaturesQWdQIO：MOriLfelOriLfelMySQLCommunityDownloadsg-rationInsolationCompleteJCtStatusProgressMySQLNot.fier1.1.8CompleteMySQLForExcel13.8Installing25%Connector/J8.0.20ReadytoInstallConnec

8、tcr/NET&0.20ReadytoInstallMySQLDocumentotion5.730RedytoInstcllSamplesandExamples5.7.30ReadytoInstallprocNotShowDetailsC：Misersnct3tartysql4242502ei6z97z*22142816Z87/22丄42816ZB7/2214201683/3110ZW16ZH7/2Z18vijratfO中QW(l圧的etEBUC.y)o；B心a4Itffr3C2:BKlm.nfDFdq*尙-XiOllierur()nuvaluer94MyQT.HeTverFnrnnc*：Co

9、nfIqirationle15a0GeneratedtycneservermstanzeccnrLcuraTionwizarln/AA4InululiwvlunInylruGi.un丁2)61onUnaxyoucancc.曲thisnierc/rc/ircntro3tglobaloptionsr11dnycqldita-dir/ny.unEtoi&tccxvx-:if.coptions124(IccaLsratodicJ*forthisin:alliuicjcrto13、/mycaljeluoul-p)shouldtwer匕filmrit;bftInsrlIt.iandlftet/jr-y16

10、Sof屮)：5rtvrt(r.j.C:P:efl*13-*v-KT.*/171naacarotKa2-azvorraidzt:hoccnfi-grfilojagthoctirtupoptiong-defaults-fLie-3*2.TcxkicaIbuixjzi/cxIcvciIliecstrKHxiliav/?xculcUhii:iuZl;inF(2ditt;Jie1.1,匕q2/:nyqli-i.t-ffl1t.t11r*C:Poq.iniu,MymQ：My273lutrX.Ymy.：i*門(mén)621$:cinstallzneserverasaAi.nd:ecuzetnism=234occun

11、cu:d2i：jrhcll/ug*一-.S3S9-C:Windowssystcm32cmd.exeDefaultPublicCsMJseronetstopRyoql黑肚廳醮龜11S3e5：C:Windowssystem32cmd.exemysql-uroot-h!27.O.O.l-pC：llsersnetstantmysql豔魯斃矗豔功。C：Usersmysql-uroot-hl27.B.0.1-pEntoipassword：*VeleonetotheHySQLnonitor.Connandsendwith:orgVouvMySQLconnectionidis1Serverversion：5.

12、5.50n;SQLCommunityServerGPL)CopyrightarkofOracleCorpoi*ationand/oritsaffiliates.Othernanc3maybetradenarksoftheirrespectiveounet*sType1help：1of9hfforhelp.Type9tocleat*thecurrentinputstatement.nysqlMyQLWorkbenchFileSditarLocalj沖aceHySQLxViewQueryDitaieServerToolsS*“t:ncKel?actn&rfvSCHEMASNvtcQmcctcdNa

13、vigatorAdHruuit；ConpiedFo:Gxifu*a9(ngRnrgSrKrPerformanceSchana:TWejdFod:MIBLQQ莒逞員:GWindov/s5ystem32crrxl.exe朗PWiM殊曰刪M墩和MI：ST亠加IQyR*4P)HIf日旨eNBI世能亠cl金軸龍-I*wa95滬MR9勺100：0110220310410566207iOS209110311112：1倉(cāng)M215116.17|T1HFPathzothedaTaba3excotdatadir-C;/lrogronUata/HyEQL/NylQLWurvuu57/DataTIk.dclttulLu

14、hax.ucLcrsiihucwill3a；aw】ua:anc*yunuuictabiclb5Xirobrnclidefin冬fchayacrr*匚server-Vh*default3rorajemneinnthatwillbeuwwdwhncreatpnevtablesvnenledulL-s：Qxage-engiae=Myli；AMSeiLticSQLdutlixcLcqlmodcTRTCTTrWTf!TMUJ：力WOM7TOCKA7F.(TBFENCF.NGTtrHTfTT-TTfTTOX*lieneralandulowlogglrglcg-cutpat=h丄J-yavxul-luqne

15、tr/sIIoqnItt-lAPTOF-OHPDVA1(;slovqueryloj=uslov_qucry_lo3-filo=,LAraOP-OHF2DVAA-ulow.lcg丄nmysq1*uroot-p:nterpassword:*elconetotheMySQLoonitorCoamandsendvith:org.ourMySQLconnecticnidis5erverversion：5.7.27-logMySQLComnunityServer(CPUCopyright(c2000.2019.Oracleandcritsaffiliates.11rightsreserved)”cleis

16、argistoredtrademarkofOracleCornoraxionand/oritsiffiliotos.Othrnomeamaybetrndenarksoftheirrespectiveypehelp:1or*h*forhelp.:ypec*toclearthecurrentinputstatenent.lyaqlgho*binlogQvntsinLP70P-OHP2DVAA-bin.000001;BjAdminijtramr:令勺rryfurcotpC:Viado*357Sten32ays4ilurcot*pEnterpassword:WaiconotothaH/SIXaon-t

17、or.Cgumkordvith:org.YcurtfySQLconnocticx:dta5Serverversion：3727-iogifvSQLConnunityServer(CTLCopyrightc)2000.20】9Oracleendoritsaffiliates.AllrixhtsresexvePrevicus_xtids11S4roviinoi(000AdmhiWjtor:的承特-匚V：adoT3xsy3tea22)ietstopwygleM?SQLserviceisstopping.弋JItSQLservicek；9ppwlauccessfully.L:V:ndoT2systQa

18、32ntsrarrmyslTh。MySQLzrvsgisttrTlng.IhMtSQLseniceasstartedsuccessfully.:ndor3x3T3tennysql-urcctSiAdmiriisirfltar:血9癢承軒-urootp1rxinsex,1waxning(0.COaec)mvsqlshowvariableslike*general_log:Variable_naxeValuegeneral.logOS1rowinset.1irarninc(0.COsecmy2qlsetglobalgeneral_lcg=，off*;QueryOK.015saffected(0.0

19、2secmysQlsetiloballeneral.looff:BlAdniintrioc令m/qlurnol-pooroooooo105Ssl.server.aot.beforeSl_sssion_ccha_hitsS31_se33ion_8chBni33SslsessioncacheEodeSsLsessioncache.overflowsSsl_session_cache_sizeg_“dimgtsS$l_sessions_reus-=|Ssl_usedjession_cacheentrie3Ssl.verifydepthSsl_verify_DodeSslversionTabllock

20、s.ixnediateTable_lock3_waitecTable_open_cache_hitsTableopencacteEissesTable_op9E_cache_ovrfloTsTcIogmaxpagesusedTclcgpagesi2eTe_iog_page_wait3Thread$_cacKedIhreadsconnectedThreadscreatedThreads_mnningVptiooUptine_since_flush.tfttus561otsinset0.COsec)St*dfViinkTrator:金色撈力斜-myql-uccotjversioncannentMy

21、SQLCoauiunityServek(GPL：ver3ijeempi1aexS664Dilosffin64Tgittimecut2SW0Tarningccunt513rowsinsot.1warning(0.00see)QVSQ1rarnin_count13rorsinset1varnitig(0.01sec.MbasedirC:ProiiaaFiles:MrSQCMySflLServer5.7rovin5Pt?oloetC&AMdYr：JCCdatadirC:Pg?raaDataM比QlAMvSQLServer5.7Jata1IAdEwiistrator:mysqlurootpJXrovi

22、usrtWC0secrovsinsot0.00socavsqlquitBnC:Tintlossysleii32rv?lstopBysqlTheMySQLservice13stoppinf.IhoMySQLsaxvicvasstoppedsuceossfully.C:Tindow33ysten32nezstar:mvscilTlxMySQLserviceisstcating.ClheMrSOLservicemsStartedsuccessfully.C:Tindovssyton32M,sql-uroot-p；：En：orpasssord:*elcoaetotheM7SQLaxcitorCoama

23、ndsendwith;orYourMvSQLconnect;onidis2Serverversion：$.7.27-logMySQLCamunitrServer,GPUCopyright(c)2000.2019Omclaixl/orilsafflllaies.Allrightsreserved.(JraclaisaYQgmoedTradonarKorOraeloCorporationand/ornsdffiliatosOthorqcixmsoaybotradcaorksoftlwirrspctivoTCrsType*help;*crbfoxhe：pType*c*tpclearthecuxTen

24、tinputstatciKnt，、余G今兄用-v“創(chuàng)-paBlAdrr.irii5.tr.itof；介令15力mysqlurootpInnoDBYESsupper:2Trancac:ionc.rMRGJCYISAXYESColleciioEofidentical1MEMORYYESHashbasedsteradinmetBLACKHOLEYES/dwv/nullstorageengineMVISAMDEFAILTMylS?MstorageenginecsvYESCSVstorageengineARCHIVEYESArchivestorageenginePERFORMAWF.SCHEVAYESP

25、erformanceFEDERATEDNOFedera:edMySQL3Torage9rowsinsotcreatetableiayiszuiCidintauto.increnent*-nampvarcharmiginefylSAM;QveryOK.0i*o*saffected；0.03sec)fldAdmiei5t心or:朮令CE了的mysidintavto_incrraient-canGvarchar(30hprimarykev(id)-Gngine=MyISAMQuer7OK,0rowsaffected(0.0?aec)mysqlshowcreatetabletoiTisaa：TabloCroatoTablet_ny