OracleOCP认证考试练习真题题库五

试卷科目：OracleOCP认证考试练习OracleOCP认证考试练习真题题库五PAGE"pagenumber"pagenumber/SECTIONPAGES"numberofpages"numberofpagesOracleOCP认证考试练习真题题库五第1部分：单项选择题，共97题，每题只有一个正确答案,多选或少选均不得分。[单选题]1.Question:19.WhichstatementbestdescribesthepurposeoftheInnoDBbufferpool?A)Itisamountofbuffersavailableduringatransaction.B)ItcachesonlytheindexesforInnoDBtables.C)ItcachesdataandindexesforInnoDBtables.D)Itholdschangesmadeduringatransactionbeforetheyarewrittentothelog.E)ItisapoolofmemoryforSQLquerysortoperationsfromwithintheInnoDBengine.答案:C解析:[单选题]2.Question:56.Youarecontactedbyauserwhodoesnothavepermissiontoaccessadatabasetable.YouDetermineafterinvestigationthatthisusershouldbepermittedtohaveaccessandsoyouExecuteaGRANTstatementtoenabletheusertoaccessthetable.Whichstatementdescribestheactivationofthataccessfortheuser?A)Theaccessdoesnottakeeffectuntiltheuserlogsoutandbackin.B)Theaccessdoesnottakeeffectuntilthenexttimetheserverisstarted.C)Theaccessisavailableimmediately.D)TheaccessdoesnottakeeffectuntilyouissuetheFLUSHPRIVILEGESstatement.答案:C解析:[单选题]3.Question:112.YouhaveinstalledMySQLonOracleLinux7usingtheofficial...Examinethisextractoftheffile:[mysql]Port=3301[mysqld]Port=3302[mysqld@replica]Port=3303[replica]Port=3304YouissuethiscommandtostartMySQLServer:Systemctlstartmysqld@replicaOnwhichportistheserverlistening?A)3302B)3303C)3304D)3301答案:B解析:[单选题]4.Question:142.MySQLServerhasbeenintallandthe/etc/ffilehasbeenedited.Theserverdoesnotstartwhenyouattempttousethestartscriptoftheos,butstartsonlywhenyouprovidealltheoptionsonthecommandline.Youcheckthe/etc/my.cnffilepermissions:Shellsls-l/etc/my.cnf-rw-w-rw-.1rootroot345Nov182016/etc/fWhatisthereason?A)MySQLisstartingviaanon-rootuserwhenusingtheinitscript.B)MySQLwasinstalledinanon-defaultlocation,anddoesnotlookin/etc/fC)MySQLignoresworld-readableconfiurationfilesasasecuritymeasure.D)MySQLmustbestartedasrootwiththissetup.答案:C解析:[单选题]5.Question:162.Assumingthatauserhascorrectprivileges,whichLinuxconsolecommandwillfailtoshutdownaMySQLserver?A)mysql-e'shutdown'B)servicemysqldstopC)mysqld-eshutdownD)mysqladminshutdown答案:C解析:[单选题]6.Question:36.MySQLisinstalledonaLinuxserverandhasthisconfiguration:[mysqld]User=mysqlDatadir=/data/mysql/Asthe?root?user,youchangethedatadirlocationbyexecuting:Shell>cp-R/var/lib/mysql/data/mysql/Shell>chown-Rmysql/data/mysqlWhatisthepurposeofchangingownershipofdatadirtothe?mysql?user?A)MySQLneedstoberunastherootuser,butfilescannotbeownedbyit.B)Themysqldprocessrequiresallpermissionswithindatadirtobethesame.C)MySQLcannotberunastherootuser.D)MySQLrequirescorrectfileownershipwhileremainingsecure.答案:D解析:[单选题]7.Question:16.Thefollowinggrantswereexecuted:GRANTCREATEROUTINGONsales.*TO?webadmin?@?%?;GRANTALTERONPROCEDUREsales.myprocTO?webadmin?@?%?;AusersuccessfullyconnectstothedatabaseaswebadminandcreatedastoredprocedureNamedget_reports.Thenextday,theuserlogsinagainaswebadminandwantstodeletethestoredprocedurenamedget_reports,andtherefore,issuesthefollowingstatement:USEsales;DROPPROCEDUREIFEXISTSget_reports;Whatistheresultofexecutingthestatement?A)TheuserwillgetanerrorbecauseheorshedidnotusetheALTERstatementtodroptheStoredprocedure.B)Theuserwillgetanerrorbecauseheorshedidnotputthedatabasenameinfrontofthestoredprocedurename.C)Thestoredprocedurenamedget_reportswillbedropped.D)TheuserwillgetanerrorbecauseheorshedoesnothavethepermissiontodropstoredProcedures.答案:C解析:1、如果用户有createroutine权限那么他就可以创建procedure|function。2、如果用户创建了procedure|function那么mysql会自动赋予它对procedure|function的alterroutine和execute权限。3、必须具有alterroutine的权限，才可以使用{alter|drop}{procedure|function}[单选题]8.Question:96.WhyshouldyoubeselectivewhengrantingthePROCESSprivilegetoanaccount?A)Itallowsaclienttocontrolrunningprocessesonaserver.B)Itallowsaclienttoprocessscripts.C)Itallowsaclienttoseeanotheruser?squerieswiththeSHOWPROCESSLISTcommandD)Itallowstheuseofstoredroutines.答案:C解析:[单选题]9.Question:88.Giventheresultofthisquery:Mysql>selecthost,userfrommysql.user;HostuserEkateLocalhostmike%mikeDnullAclientconnectionisestablishedwiththeusernamemikefromthehost.However,whentryingtoexecutequeries,itisfoundthattheprivilegesareinsufficient.Youinvestigatefutherbyexecutingthecommand:Mysql>selectcurrent_user();Whatisthenamereturnedfromtheexecutedcommand?A)?mike?@?localhost?;B)??@??C)?mike?@??D)?mike?@?%?答案:D解析:user()是用来显示当前登陆的用户名与它对应的hostcurrrent_user()是用来显示当前登陆用户对应在user表中的哪一个[单选题]10.Question:52.AMySQLServerhasbeenrunninganexistingapplicationsuccessfullyforsixmonths.Thefisadjusted调整tocontainthisadditionalconfiguration:TheMySQLServerisrestartedwithouterror.Whateffectwillthenewconfigurationhaveonexistingaccount?[mysqld]Default-authentication-plgin=sha256_passwordA)Theyarenotaffectedbythisconfigurationchange.B)Theyallconnectviathesecuresha256_passwordalgorithm算法withoutanyconfigurationchange.C)Theywillhavetheirpasswordsupdatedonstart-uptosha256_passwordformat.D)Theywillhavetochangetheirpasswordthenexttimetheylogintotheserver.答案:A解析:[单选题]11.Question:76.YouattempttoconnecttoaMySQLServerbyusingthemysqlclientprogram.However,youReceivethisnotice:Whatwouldyouruntofixtheissue?A)themysql_upgradescriptB)themysqlclientwiththe--ignore-password-hashingoptionC)themysql_secure_installationscripttoupdateserversecuritysettingsD)themysqlclientwiththe--enable-cleartext-pluginoptionE)theinstallplugincommandforthemysql_cleartext_passwordplugin答案:D解析:mysql_clear_password：该插件是内嵌到MySQL客户端里的，允许客户端以明文的形式发送密码到服务器，可以与任何需要明文密码的服务器端插件一起使用。1通过设置环境变量LIBMYSQL_ENABLE_CLEARTEXT_PLUGIN值为1,Y或y，为所有客户端连接启用插件2在每次调用客户端程序时，使用--enable_cleartext_plugin选项来启用插件[单选题]12.Question:119.Youhaveforgottentherootuseraccountpassword.YoudecidetoresetthepasswordandExecute:Shell>/etc/init.d/mysqlstopShell>/etc/init.d/mysqlstart-skip-grant-tablesWhichadditionalargumentmakesthisoperationsafer?A)-read-only,tosetalldatatoread-onlyexceptforsuperusers.B)-skip-networking,toprohibitaccessfromremotelocations.C)-old-passwords,tostartMysqltousetooldpasswordformatwhilerunningwithoutthegranttablesD)-reset-grant-tables,tostarttheserverwithonlythemysqldatabaseaccessible答案:B解析:Skip-networking关闭MySQL的TCP/IP连接方式，开启后不能远程访问MySQL若希望指定的IP访问MySQL，可以在配置文件中增加bind-address=IP，前提是关闭skip-networking[单选题]13.Question:125.？Youhaveintalledthevalidatopasswordpluginandsetthevalidato_passwordpolicyvariable.Whichvalidation验证isaffectedbythevalidatepasswordpolicysetting?A)whetheranewpasswordisrejectedifitcontainsthecurrentuser?susernameB)whetheranewpasswordisrejectedifitcontainsawordfoundinadictionaryfileC)thelengthoftimebeforeanewlycreatedpasswordexpiresD)theamountofdelayafteranicorrectpasswordisentered答案:B解析:[单选题]14.Question:38.Youhavecreatedanewuserwiththisstatement:CREATEUSER?erika?@?localhost?IDENTIFIEDBY?first#1Pass?PASSWORDEXPIRE;Whatistheoutcome?A)When?erika?@?localhost?triestologinwiththeMySQLcommand-lineclient,theuserwillhavetochangethepasswordbeforeseeingthemysql>prompt.提示符B)When?erika?@?localhost?triestologinwiththeMySQLcommand-lineclient,theuserwillnotbepermittedtologinbecausethepasswordisexpired.C)When?erika?@?localhost?triestologinwiththeMySQLcommand-lineclient,theuserwillbepermittedtologinbutwillnotbeabletoissueantstatementsuntiltheuserchangesthepassword.D)Youreceiveasyntaxerrorthatindicates表示thatyoucannotsetapasswordandexpireitatthesametime.答案:C解析:密码过期PASSWORDEXPIREmysql.user表（默认关闭），开启前可登录但不能进行任何查询语句，可设置密码过期时间default_password_lifetime（全局，默认不过期），修改指定用户密码过期如下：1、过期天数：ALTERUSER?testuser?@?localhost'PASSWORDEXPIREINTERVAL30DAY;2、永不过期：ALTERUSER'testuser'@'localhost'PASSWORDEXPIRENEVER;[单选题]15.Question:11.Hostslave1hasipaddress0.Hostslave2hasipaddress0Examinethesecommands:Whydidthiserroroccur?A)Thehostonthecommandlineisnotdefinedintheloginpath.B)Themysqldinstancehasnotbeenrestartedaftercreatingtheloginpath.C)Thereisnopassworddefinedintheloginpath.D)TheDNSisnotconfiguredcorrectlyforslave1host.E)Theffileisnotreadable.答案:C解析:[单选题]16.Question:183.Whichcommand-lineutilitycreatesencryptedfilesthatcanbeusedbyclientprogramsandcontainobfuscatedusernamesandPasswords?A)mysqlpasswdB)mysqladminC)mysqld_safeD)mysql_config_editor答案:D解析:[单选题]17.Question:60.Oneofyourcolleagues同事istryingtomakeachangeusingthemysqlcommand-lineclientForhisorherapplicationsession.Thecolleagueinstantmessagesyouthiscommand:Mysql>SETSESSIONmax_connections=200;Whydoesthecommandfail?A)max_connectionsrequirestheGLOBALscope.B)ItscurrentuserdoesnothavetheSUPERprivilege.C)max_connectionsisnotadynamic动态variable.Youneedtochangetheconfigfileandrestartthedatabase.D)Userscancontrolonlythemax_user_connectionsvariable.答案:A解析:max_connections：最大连接数，默认为151，可动态修改。全局变量，对所有用户生效，实际上允许max_connections+1个客户端连接，额外一个连接供具有SUPER特权的用户使用Max_user_connections：对于单个数据库用户允许的最大同时连接数。默认为0，即表示无限制，可动态修改。[单选题]18.Question:46.Consider:Whichstatementbestdescribesthemeaningofthevalueforthekey_lencolumn?A)Itshowshowmanybyteswillbeusedfromeachindexrow.B)Itshowsthenumberofcharactersindexedinthekey.C)Itshowsthetotalsizeoftheindexrow.D)Itshowshowmanycolumnsintheindexareexamined.答案:A解析:[单选题]19.Question:133.AtwhichstageduringqueryprocessingdoesMySQLcreateaquery?sexecutionplan?A)AuthorizingoB)ExecutingC)ParsingD)Optimizing答案:D解析:Sql语句》查询缓存》器》优化器（执行计划）》执行器（权限认证）Https:///s?id=1711883948686737820&wfr=spider&for=pcHttps:///s?id=1730699144288222793&wfr=spider&for=pc[单选题]20.Question:93.Consider:Mysql>explainselectnamefromcountrywherepopulationbetween1and10000\G1row:Id:1Select_type:simpleType:rangePossible_keys:i_popKey:i_popKey_len:4Ref:nullRows:10Extra:usingwhereWhatdoestherangevalueinthetypecolumnmean?A)youcanuseanindexandreturnrowsthatfallwitharangeofvaluesB)thereisarangeofindexesthatcanbeused.C)ThistypeofindexusestherangehashD)Thetablewillbescannedoveracertainrangeofvalues.答案:A解析:A)youcanuseanindexandreturnrowsthatfallwitharangeofvalues#您可以使用索引并返回包含一定范围值的行B)thereisarangeofindexsthatcanbeused#有一系列的索引可以使用C)thistypeofindexusestherangehash#这种类型的索引使用hash范围D)thetablewillbescannedoveracertainrangeofvalues#表将在一定的值范围内被扫描[单选题]21.Question:137.Table:CityItype:ALLPossible_keys:NULLKey:NULLKey_len:NULLRef:NULLIOMS:4079Extra:Usingtemporary;UsingfilesortId:1Select_type:SIMPLETable:CountryType:eq_refPossible_keys:PRIMARYKey:PRIMARYKey_len:3Ref:world.City.Countrycoderows:1Extra:Usingwhere;DistinctWhichstatementbestdescribesthemeaningofthevaluesintherefcolumns?A)world.City.CountryCodeisusedastheprimarykeyfortheCountrytable.B)NoindexedcolumnsareusedtoselectrowsfromtheCountrytable.Theworld.City.CountryCodecolumnisusedtoselectrowsinthecitytable.C)NoindexedcolumnsareusedtoselectrowsfromtheCitytable.Theworld.City.CountryCodocolumnisusedtoselectrowsintheCountrytable.D)world.city.CountryCodeisusedtosorttherowsinthecitytable.答案:C解析:[单选题]22.Question:39.WhatistheorderoftablesshowninanEXPLAINoutput?A)Itliststablesfromthesmallesttothelargest.B)Itliststablesintheorderinwhichtheirdatawillberead.C)Itliststablesfromthemostoptimizedtotheleastoptimized.D)Itliststablesintheorderinwhichtheyarespecifiedinthestatementthatisbeingexplained.答案:B解析:[单选题]23.Question:33.Whatdoesthepossible_keyscolumninthisoutputdenoteA)ifitispossibleforyoutoincludeanyindexesinyourqueryB)whetherthereareanyindexesonthetablesthatyouarequeryingC)ifthereareanyindexesthatmaybeusedtosolvethisqueryD)whetherthereareanyindexesinyourquery答案:C解析:A或C,倾向于C[单选题]24.Question:113.Giventhesetabledefinitionsandtheexplainplan:CREATETABLE`Country`(`Code`char(3)NOTNULLDEFAULT'',`Name`char(52)NOTNULLDEFAULT'',PRIMARYKEY(`Code`));CREATETABLE`CountryLanguage`(`CountryLanguage_id`int(10)unsignedNOTNULLAUTO_INCREMENT,`CountryCode`varchar(5)NOTNULLDEFAULT'',`Language`char(30)NOTNULLDEFAULT'',`IsOfficial`enum('T','F')NOTNULLDEFAULT'F',PRIMARYKEY(`CountryLanguage_id`));mysql>EXPLAINSELECTc.NameFROMCountrycINNERJOINCountryLanguagec1onc.Code=c1.CountryCodeWHEREc1.Language='ENGLISH'ORDERBYIsOfficial;WhichALTERTABLEstatementwillimprovetheperformanceofthequery?A)ALTERTABLE`CountryLanguage`ADDINDEX`idx_OffLang`(`IsOfficial`,`Language`);B)ALTERTABLE`CountryLanguage`ADDINDEX`idx_Lang`(`Language`);C)ALTERTABLE`Country`ADDINDEX`idx_Code`(`Code`);D)ALTERTABLE`Country`ADDINDEX`idx_NameCont`(`Name`,`CountryCode`);答案:B解析:[单选题]25.Question:134.InwhichorderdoesMySQLprocessanincomingINSERTstatement?MySQL按什么顺序处理传入的INSERT语句？A)Itwritesthequerytothebinarylog,optimizesit,andthencheckswhethertheuserisAuthorizedtoperformthequery.B)Itcheckswhethertheuserisauthorizedtoperformthequery,writestotheblnarylog,Andthenoptimizesit.C)Itoptmizesthequery,checkswhethertheuserisauhorzedtoperformit,andthenwritesTothebinarylog.D)itcheckswhethertheuserisauhorzedtoperfomthequery,optimzesit,andthenwritesTothebinarylog.答案:D解析:[单选题]26.Question:45.Old_alter_tableisdisabledasshown.Mysql>SELECT@@old_alter_table;ConsiderthisstatementonaRANGE-partitionedtable:Mysql>ALTERTABLEordersDROPPARTITIONp1,p3;Whatistheoutcomeofexecutingthisstatement?A)Alldatainp1andp3partitionsisremovedandthetabledefinitionischanged.B)Alldatainp1andp3partitionsisremoved,butthetabledefinitionremainsunchanged.C)Onlythefirstpartition(p1)willbedroppedbecauseonlyonepartitioncanbedroppedatanytime.D)Itresultsinasyntaxerrorbecauseyoucannotspecifymorethanonepartitioninthesamestatement.答案:A解析:[单选题]27.Question:90.Youwanttodumponlydatafromtheuserdatatable.Whichmysqldumpcommandargumentisrequiredtoaccomplishthis?A)--no-create-infotoskipwritingCREATETABLEstatementsB)-data-onlyasthisspecifiesthatonlydataistobedumpedC)-sigle-transactionasthisobtainsaconsistentviewofdataonlyD)-table=userdataisordertodumponlythedatafromtheuserdatatable答案:A解析:[单选题]28.Question:104.TheMySQLuser?adam?currentlyhasUSAGEpermissions权限tothedatabase.Thefootballdatabaseistransactionalandhasnon-stopupdatesfromapplicationusers.The?adam?userneedstobetakeconsistentbackupofthefootballdatabasebyusingthe--single-transactionoption.WhichextraGRANTpermissionsarerequiredforadamtotakemysqldumpbackups?A)the?adam?usermustalsohaveSELECTonthefootballdatabaseforbackupstoworkB)the?adam?usermusthavetheSUPEprivilegesinordertotakedatabackups.C)the?adam?userneedsthePROCESSprivilegetobeabletotakeaconsistentbackupwhileotherusersareconnected.D)the?adam?usermustalsohaveSINGLETRANSACTIONglobalgranttotakeaconsistentbackup.答案:A解析:mysqldumprequiresatleasttheSELECTprivilegefordumpedtablesmysqldump至少需要SELECT为备表的特权[单选题]29.Question:105.Youneedtodumpthedatafromthemasterserverandimportitintoanewslaveserver.WhichmysqldumpoptioncanbeusedwhendumpingdatafromthemasterserverinordertoIncludetheinformation?A)include-log-fileB)master-dataC)include-master-infoD)master-binlog答案:B解析:master-data用于将主库的bin-log信息写入到dump文件中，即当前文件名(filename)和位置（position），用于主从复制的搭建相当于执行了一次showmaster;--master-data=1会将changemaster语句写入dump文件中,在从库导入后，配置主从无需再指定文件名和位置--master-data=2会将changemaster语句写入dump文件中，只不过会被注释掉[单选题]30.Question:43.Youbackupbyusingmysqldump.WhichconfigurationisrequiredontheMySQLServertoallowpoint-in-timerecovery?基于时间点的恢复A)binlog_format=STATEMENTB)log-binC)apply-logD)bonlog_format=ROWE)gtid_enable答案:B解析:[单选题]31.Question:79Youhaveaserverthathasverylimitedmemorybuthasaverylargetable.Youwillusemysqldumptobackupthistable.WhichoptionwillensuremysqldumpwillprocessarowatatimeinsteadofbufferingasetofRows?A)--tabB)--single-transactionC)--quickD)--skip-buffer答案:C解析:--quick：强制mysqldump从服务器一次查询一行数据而不是整张表，mysqldump每次查询都需要先将数据保存在本地缓冲区中，启用该选项后一次查询一行数据就可以尽量不使用缓冲区，但是如果内存够大的话，可以关闭该选项以加快备份速度。--single-transaction：将事务隔离模式设置为REPEATABLEREAD，并在备份数据前向服务器发送SQL语句STARTTRANSACTION显示开启一个事务快照，在事务快照内进行备份，使得备份的数据与获取事务快照时的数据一致，且不会阻塞任何应用访问服务器，在事务备份期间，不要有alter,create,drop等ddl操作，因为会破坏一致性状态，导致备份失败。[单选题]32.Question:10.Youhavejustexecutedamanualbackupbyusingthiscommand:Mysqlbackup-uroot-p--socket=/tmp/my.sock--backup-dir=/my/backup/backupTheoperationcompletedwithouterror.Whatisthestateofthisbackupandoperationrequiredbeforeitisreadytoberestored?A)BackupState=CompressedBackup;Operation=copy-backB)BackupState=RawBackup;Operation=apply-logC)BackupState=PreparedBackup;Operation=validateD)BackupState=PreparedBackup;Operation=apply-logE)BackupState=RawBackup;Operation=backup-dir-to-image答案:B解析:默认compress是关闭的,所以backupstate不是compressed的对于初始的backup产生的记录应当是初始记录的,需要使用apply-log来使得备份同步备份其实主要的内容也就是backup、restore和校验。每个阶段使用的主要参数如下：?Backupoperations:backup,backup-and-apply-log,backup-to-image?Updateoperations:apply-log,apply-incremental-backup?Restoreoperations:copy-back,copy-back-and-apply-log?Validationoperation:validateSingle-filebackup?operations:image-to-backup-dir,backup-dir-to-image,list-image,extract[单选题]33.Question:15.TherearemultipleinstancesofMySQLServerrunningonasingleOSthatisbackedupusingthemysqlbackupcommand.The/etc/my/cnfcontainsdefaultvalues,forexample,datadir=/var/lib/mysql/,withextrainstanceshavingtheirownseparateffile(forexample/etc/mysql/instanceN.cnf)overridingthedefaults.Arestoreofthesecondinstanceisattemptedfromthemysqlbackuparchiveusingthiscommand:Mysqlbackup-backup-dir=/opt/backup/mysql/instance2copybackuponstartingthesecondMySQLinstance,younoticethatthedatadoesnotmatchtheexpectedbackup.Whichcommand-lineoptionisrequiredtosuccessfullyupdatethesecondinstance?A)-restore=2B)--copy-back-from-logC)--backup-instance=/var-ib/mysql/instance2D)--instance=/var/lib/my-l/instance2E)--defaults-file=/etc/m-ql/f答案:E解析:mysql的启动参数默认从/etc/my/cnf取得,如果要使用特定的参数文件来启动,需要使用--defaults-file[单选题]34.Question:23.WhichMySQLutility程序programshouldyouusetoprocessandsorttheSlowQueryLogbasedonquerytimeoraveragequerytime?A)mysqldumpslowB)mysqldumpC)mysqlaccessD)mysqlshowE)mysqlslow答案:A解析:[单选题]35.Question:171.YouareusingthemysqldumpslowutilitytoviewthecontentsoftheslowquerylogYounoticetheletterNandthecharacterstring?s?inanumberoflocationsintheoutput.WhatdoestheNindicate?A)anabbreviation缩写fornullinastatementB)thenameoftheuserissuingthestatementC)anabstracte抽象substitution替换fornumbersindudedinwhereclausesD)thenumberoftimesthestatementwasexecuted.答案:C解析:1/path/mysqldumpslow-sc-t10/database/mysql/slow-log这会输出记录次数最多的10条SQL语句。其中：1-s,是sort的意思，表示按照何种方式排序，c(次数)、t(时间)、l(查询时间)、r(返回的记录数)，ac、at、al、ar，表示相应的倒序；2-t,是topn的意思，即为返回前面多少条的数据；3-g,是grep的意思，后边可以写一个正则匹配模式，大小写不敏感的出现次数(Count),执行最长时间(Time),累计总耗费时间(Time),等待锁的时间(Lock),发送给客户端的行总数(Rows),扫描的行总数(Rows),用户以及sql语句本身(抽象了一下格式,比如limit1,20用limitN,N表示).[单选题]36.Question:165。Yourmysqldatabaseisrunningonlinux,andyouhavethefollowingvariableinyou'reffile.MysqldLog-error=/var/log/host_name.errYourmysqlerrorlogisverylarge,andyouwanttosaveitandcreateanewlog.Whichsetofcommandswouldaccomplishthistask?A)Fromalinuxcommandline;#mv/var/log/host_name.err/var/loghostname.err-old;#mysqladminflush-logsB)FROMamysqlcommandlineMysql>SETSESSIONlog_syslog=?on?Mysql>setsessionlog_syslog)fatility=nonate;Mysql>Flus/varloghost_name.err;Mysql>setsessionlog_syslog=?off?C)Fromalinuxcommandline#mysqladmin-rorate-log=varloghost_name2.errD)Fromamysqlcommandline;Mysql>ROTATELOSSWITHTI…..MYSQL>FLUSRLOGS;E)FromamysqlcommandlineMysql>setgloralex..logs_days=0lMysql>setold_log_file=varlog/host_name.err-old?;Mysql>FlusholderrorlogsMYSQL>reseterrorlogs;答案:A解析:Shell>cdmysql-data-directoryShell>mvmysql.logmysql.oldShell>mvmysql-slow.logmysql-slow.oldShell>mysqladminflush-logsFLUSHLOGS;操作会生成一个新的binlog文件。如果在从库执行flushlogs不仅会生成一个新的binlog文件，而且会生成一个新的relaylog文件。Flushlogs还影响slowlog和generallog,当删除slowlog或者generallog,然后执行flushlogs，此时会再重新生成一个新的slowlog或者generallog[单选题]37.Question:58.YouenablebinaryloggingonMySQLServerwiththeconfiguration:Binlog-format=STATEMENTLog-binWhichdatabaseupdatesareloggedonthemasterservertothebinarylogbydefault?A)allupdatesexcepttotheTEMPDBdatabaseB)allupdatesexcepttothePERFORMANCE_SCHEMAdatabaseC)allupdatesnotinvolvingtemporarytablesD)allupdatestothedefaultdatabase,excepttemporarytablesE)allupdatestoalldatabases答案:B解析:？选B[单选题]38.Question:72.The/myfolder/ffilehasoptionset:[mysqld]Skip-log-bin/myfolder2/fhasthisoptionset:[mysqld]Log-bin=/valid/path/to/mysqlbinlogAllmentioned提及pathsareaccessibletotheaccountthatyouarecurrentlyusing.Assumethatanyotheroptionsmentionedineitherfilearevalidandlegaloptiondefinitions.Youstartaninstancebyusingthiscommandline:Mysqld--defaults-file=/myfolder/f--defaults-extra-file=/myfolder2/fWhatistheoutcome?A)MySQLstartsandBinaryLoggingisenabled.B)MySQLfailstostartduetotheconflictingoptionsintheconfigurationfiles.C)MySQLfailstostartduetoconflictingoptionsonthecommandline.D)MySQLstartsbutBinaryLoggingisdisabled.答案:D解析:使用--defaults-file=FILE，mysqlserver仅从这个配置文件读取参数。（READonlythegivenoptionfile.）--defaults-extra-file=FILE在全局配置文件之后，用户配置文件之前。即不指定--default-file时，从以下文件顺序（第5个）读取配置文件。[单选题]39.Question:169.WHRERDOESMysqllinuxRPMinstallthemysqldbinary?A)/USR/BIN/B)/USR/LIBEXEC/C)/OPT/MYSQL/SERVER/BIN/D)/USR/SBIN/E)/USR/LOCAL/MYSQL/BIN/答案:D解析:[单选题]40.Question:5.HowdoestheInnoDBstorageenginehandledeadlockswhentheyaredetected?A)Boththeaffectedtransactionswillberolledback.B)Theaffectedtransactionswaitforinnodb_lock_wait_timeoutseconds,andthenrollback.C)Oneoftheaffectedtransactionswillberolledback,theotherisallowedtoproceed.D)Thetransactionisolationleveldetermineswhichtransactionisrolledback.E)Theinnodb_locks_unsafe_for_binlogsettingdetermineswhichtransactionisrolledback.答案:C解析:MySQL有两种死锁处理方式：等待，直到超时（innodb_lock_wait_timeout=50s设置锁等待的时间，前提已经检测到锁的产生）。发起死锁检测，主动回滚一条事务，让其他事务继续执行，回滚代价最小的那一个事务（innodb_deadlock_detect=on）。由于性能原因，一般都是使用死锁检测来进行处理死锁。死锁检测（默认开启）死锁检测的原理是构建一个以事务为顶点、锁为边的有向图，判断有向图是否存在环，存在即有死锁。回滚检测到死锁之后，选择插入更新或者删除的行数最少的事务回滚，基于INFORMATION_SCHEMA.INNODB_TRX表中的trx_weight字段来判断。收集死锁信息：利用命令SHOWENGINEINNODBSTATUS查看死锁原因。调试阶段开启innodb_print_all_deadlocks，收集所有死锁日志。SELECT*FROMinformation_schema.INNODB_TRX;trx_rows_locked:事务锁定行数trx_rows_modified:事务修改行数#首先查询是否锁表SHOWOPENTABLESWHEREIn_use>0;#查询进程,保证拥有超级管理员权限SHOWPROCESSLIST;#杀死进程KILL4503;#查看正在锁的事务SELECT*FROMINFORMATION_SCHEMA.INNODB_LOCKS;#查看等待锁的事务SELECT*FROMINFORMATION_SCHEMA.INNODB_LOCK_WAITS;[单选题]41.Question:29.ExaminethemydatatableandSELECTstatements:Youissue:Mysql>begin;Mysql>updatemydataseta=0whereb=3;HowmanyrowsarenowprotectedbylockswiththedefaultInnoDBconfiguration?A)oneB)onerowandanext-keylockforsupremumC)onerowandagap-lockD)five答案:D解析:[单选题]42.Question:35.Isittruethatbinarybackupsalwaystakelessspacethantextbackups?A)Yes,becausebinarybackupsonlycontaindata,andnotstatementsrequiredtoinsertdataintothetables.B)No,becausetextbackupscanhaveoptimizations,whichmakethemsmaller,suchasupdatingmanyrowsatonce.C)No,becauseifInnoDBtablescontainmanyemptypages,theycouldtakemorespacethantheINSERTstatements.D)Yes,becauseevenifInnoDBtablescontainmanyemptypages,textbackupshaveemptyINSERTstatementsforthem.答案:C解析:[单选题]43.Question:158.Whichstatementbestdescribesa?warm?backup?A)Itonlybacksup?warm?data,thatis,datathathasbeenrecentlymodified.B)Itissimilartoa?hot?backup,butdiffersinthatitdoesnotpermitwriteoperations.C)Itissimilartoa?cold?backup,butdiffersinthatitperitswriteoperations.D)ITbacksupthebinarylog,whichcortainsthemostrecent?warm?changes.答案:B解析:温备针对myisam的备份(myisam不支持热备),备份时候实例只读不可写，数据库锁定表格（不可写入但可读）的状态下进行的[单选题]44.Question:151.YouareinstallingMySQLServer5.7fromatarballpackage.YouhavealreadysetupthecorrectPATHenvironmentvariableandconfiguredyour/etc/ffile.Whichcommandwillcorrectlyprepareadatadirectory?A)mysqld_safe--user-mysqlB)mysqld--initialize...C)servicemysqldprepare-initD)mysql-uroot-p<support-files/mysqldb.sqlE)mysqld_safe--initialize...答案:B解析:[单选题]45.Question:12.YouhaveinstalledMySQLServerforthefirsttimeonyoursystem.However,thedataDirectoryalongwiththetablesinthemysqlsystemdatabasearemissing.Whichstepdoyouperformtocreatethecontentsofthedatadirectory?#您首次在系统上安装了MySQL服务器。然而，数据mysql系统数据库中缺少目录和表。您执行哪一步来创建数据目录的内容？A)Runthecreate_system_tables.sqlfileB)Runthemysql_unpack.sqlfileC)Invokemysqldwiththe--initializeoption.D)Invokemysqlwiththe--initializeoption.答案:C解析:[单选题]46.Question:139.Whichstatementistrueabouttablespaces?A)Alltablesmustbeineitherthesystemtablespaceorageneraltablespace.B)Alltablespacefilesmustbeinthedirectoryspecifiedbythe--datadiroption.C)Thesystemtablespacecanbeconfiguredtospanmultiplefiles.D)Generaltablespacescanbeconfiguredtospanmultiplefiles.答案:D解析:关于表空间，哪个语句是正确的？A）所有表必须在系统表空间或常规表空间中。B）所有表空间文件都必须在--datadir选项指定的目录中。C）系统表空间可以配置为跨越多个文件。D）可以将常规表空间配置为跨越多个文件。[单选题]47.Question:135TheMySQLinstanceisadefaultRPMinstallationonaLinuxserver.#MySQL实例是Linux服务器上的默认RPM安装。