MYSQL5.6 OCP 1Z0-883 5.6題庫及解析二

11.You are using replication and the binary logfiles on your master server consume a lot of disk space.

Which two steps should you perform to safely remove some of the older binarylog files?

A.Ensure that none of the attached slaves are using any of the binary logs youwant to delete.

B.Use the command PURGE BINARY LOGS and specify a binary log file name or adate and time to remove unused files.

C.Execute the PURGE BINARY LOGE NOT USED command.

D.Remove all of the binary log files that have a modification date earlier thantoday.

E.Edit the .index file to remove the files you want to delete.

---------------------------------------------------------------------------

答案：AB

分析：

是必須要保證的，你的刪除的肯定不能正被slave使用啦苍糠。

PURGE LOGS

語法PURGE { BINARY | MASTER } LOGS { TO

'log_name' | BEFORE datetime_expr }左刽，所做操作會對.index文件進行自動更新。因此B正確刃滓，E錯仁烹。

錯，無此語法咧虎。

錯卓缰，具體清理到什么位置需要按照SHOW SLAVE STATUS來查看，而不是武斷地確定刪除早于今天的binary log文件砰诵。

12.Which two statements are true about InnoDBauto-increment locking?

A.The auto-increment lock can be a table-level lock.

B.InnoDB never uses table-level locks.

C.Some settings for innodb_autoinc_lock_mode can help reduce locking.

D.InnoDB always protects auto-increment updates with a table-level lock.

E.InnoDB does not use locks to enforce auto-increment uniqueness.

---------------------------------------------------------------------------

答案：A, C

分析：

auto-increment的AUTO-INC鎖是一個表級鎖征唬，因此A正確，B和E錯誤茁彭，总寒。

正確，根據(jù)數(shù)據(jù)庫參數(shù)innodb_autoinc_lock_mode的設(shè)置理肺，插入操作會根據(jù)模式和所用語句的不同選用相應(yīng)的鎖摄闸。innodb_autoinc_lock_mode = 2的時候善镰，將不使用表級鎖和輕量mutex鎖，不過在基于語句復(fù)制(SBR: Statement Based Replication)時年枕，會有交錯序列風險媳禁。參考：http://dev.mysql.com/doc/refman/ ... ement-handling.html http://dev.mysql.com/doc/refman/ ... t-configurable.html

13.Consider the Mysql Enterprise Audit plugin.

A CSV file called data.csv has 100 rows of data.

The stored procedure prepare_db() has 10 auditable statements.

You run the following statements in the mydb database:

Mysql> CALL prepare_db();

Mysql> LOAD DATA INFILE '/tmp/data.cav' INTO TABLE mytable;

Mysql> SHOW TABLES;

How many events are added to the audit log as a result of the precedingstatements?

A.102; top-level statements are logged, but LOAD DATA INFILE is logged as aseparate event.

B.3; only the top-level statements are logged.

C.111; top-level statements and all lower-level statements are logged.

D.12; only top-level statements and stored procedure events are logged.

----------------------------------------

答案：B

分析：

audit.log

文件中每個<AUDIT_RECORD>元素代表了一個事件，如客戶連接或關(guān)閉連接事件画切，執(zhí)行SQL語句等竣稽。僅頂層語句會被記錄下來，存儲程序(如觸發(fā)器或存儲過程)中的語句不會被記錄霍弹。命令如LOAD DATA INFILE在進行操作時毫别，其對文件內(nèi)容進行進行操作的具體細節(jié)不會被記錄。因此典格，A, C, D錯岛宦，B正確。參考：http://dev.mysql.com/doc/refman/ ... ogging-control.html

14.You execute the following statement in aMicrosoft Windows environment. There are no conflicts in the path namedefinitions.

C: \> mysqld

–-install Mysql56 –-defaults–file=C:\my–opts.cnf

What is the expected outcome?

A.Mysqld acts as an MSI installer and installs the Mysql 5.6 version, with thec:\my-opts.cnf?

configuration file.

B.Mysql is installed as the Windows service name Mysql56, and usesc:\my-opts.cnf as the

configuration file

C.An error message is issued because

–-install is not a validoption for mysqld.

D.A running Mysql 5.6 installation has its runtime configuration updated withthe server variables?

set in c:\my-opts.cnf.

------------------------------------------

答案：B

分析：首先mysqld是作為MySQL服務(wù)端主程序來運行的耍缴，它并不負責MSI安裝的過程砾肺，因此A錯。通過mysqld --install可以進行Windows服務(wù)注冊防嗡，同時--defaults-file用于設(shè)置啟動服務(wù)端時使用的配置文件变汪，B正確。請注意--install命令項僅存在于Windows版MySQL的mysqld命令中蚁趁，如果你是在Linux上安裝MySQL是無法找到mysqld對應(yīng)的--install命令項的裙盾。

錯誤，因為其對于Windows版的mysqld是有效項他嫡。

錯番官，因此這命令不是用于安裝時的配置。參考：

http://dev.mysql.com/doc/refman/5.7/en/windows-start-service.html

15钢属、Which two methods will clearor reset the collected events in the tables?

Consider the events_% tables in performance Schema.?

Which two methods will clear or reset the collected events in the tables?

A.Using DELETE statements, for example, DELETE FROMperformance_schema.events_watis_current;

B.Using the statement RESET PERFORMANCE CACHE;

C.Using the statement FLUSH PERFORMANCE CACHE;

D.Using TRUNCATE statements, for example, TRUNATE TABLEperformance_schema.events_waits_current;

E.Disabling and re-enabling all instruments

F.Restarting Mysql

答案：DE

16.?What are fourcapabilities of the mysql client program?

A.Creating and dropping databases

B.Creating, dropping, and modifying tables and indexes

C.Shutting down the server by using the SHUTDOWN command

D.Creating and administering users

E.Displaying replication status information

F.Initiating a binary backup of the database by using the START BACKUP command

--------------------------------------

答案：A,B,D,E

分析：首先我們需要分清楚MySQL和mysql這兩個詞的概念徘熔，MySQL是指MySQL整個數(shù)據(jù)庫和其軟件，而mysql則是其軟件中涵蓋的一個客戶端工具淆党。本題考的是對這些客戶端工具使用酷师。在使用mysql命令行工具登陸服務(wù)端后，可以執(zhí)行的命令也非常多宁否，比如建立和刪除數(shù)據(jù)庫窒升，表和索引的增刪改等。你也可以使用mysql客戶端工具來建立用戶慕匠，并進行對用戶的權(quán)限和訪問進行管理饱须。當然在Master-Slave Replication的主庫和從庫，你也可以使用show

master status及show slave status來查看復(fù)制的狀態(tài)情況台谊。因此, ABDE都是正確的蓉媳。至于關(guān)閉MySQL Server譬挚，這有多種方式，其中一種是使用mysqladmin客戶端工具shutdown命令來實現(xiàn)的,mysql客戶端工具不負責這事。而備份，邏輯備份可使用mysqldump 或mysqlpump(從MySQL 5.7.6開始)适袜。而binary backup則可以使用mysqlbackup（如果你的MySQL是企業(yè)版的話）或使用copy表文件的方式來進行備份摧莽，而不是在mysql命令行工具中鍵入START BACKUP命令顷锰。參考：http://dev.mysql.com/doc/refman/5.7/en/programs-client.html http://dev.mysql.com/doc/refman/5.7/en/show-slave-status.html http://dev.mysql.com/doc/refman/5.7/en/mysqladmin.html http://dev.mysql.com/doc/refman/5.7/en/backup-methods.html

17.Assume that you want to know which MysqlServer options were set to custom values.

Which two methods would you use to find out?

A.Check the configuration files in the order in which they are read by theMysql Server and compare them with default values.

B.Check the command-line options provided for the Mysql Server and compare themwith default values.

C.Check the output of SHOW GLOBAL VARIABLES and compare it with default values.

D.Query the INFORMATION_SCHEMA.GLOBAL_VARIABLES table and compare the resultwith default values.

答案：C, D (for MySQL 5.6) C (for MySQL 5.7)

分析；

MySQL Server

配置項由多處設(shè)置組成，由于Server的主程序為mysqld，因此其命令項設(shè)置也主要是看mysqld對應(yīng)的項的設(shè)置闷尿。在命令項設(shè)置后，啟動后相應(yīng)項反應(yīng)在數(shù)據(jù)庫上女坑，就是那些Global Variables全局變量了填具，當用戶會話訪問時，其Session Variables則會copy自全局變量值匆骗，之后用戶可以根據(jù)需要使用SET命令來修改會話變量劳景。當然如果有足夠的權(quán)限，用戶也可以修改全局變量碉就，不過這種修改僅應(yīng)用于正在運行MySQL Server盟广，且對之后新登陸會話有效，一旦Server重啟就打回原形了铝噩。此題中衡蚂，主要是希望查看啟動后，使用的全局變量和其默認值修改情況骏庸，一般在啟動初始化時，我們可以提前通過mysqld的命令行項上直接修改年叮，或使用配置文件來進行啟動時候的項的默認修改具被。但是A, B都是錯的，因為你無法確認MySQL Server的全局變量在啟動后是否有人為被再次修改過只损。

正確一姿，因為show global variables可以了解當前所有全局變量值，從而和默認值進行比較跃惫。請注意：show variables指的是查看當前會話變量叮叹，因此一定要加上global。

在MySQL 5.6版本中正確爆存，因為其global

variables值存放于此表中蛉顽。不過在5.7版本，GLOBAL_VARIABLES這張表被移至performance_schema下先较，原先INFORMATION_SCHEMA.GLOBAL_VARIABLES將成為空表并被棄用携冤。參考：http://dev.mysql.com/doc/refman/5.7/en/option-files.html http://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html

18.You install a copy of Mysql 5.6.13 on a

brand new Linux server by using RPM packages. The server starts successfully as

verified by the following commands:?

$ pidof mysqld

3132

$ tail

–n2 /var/lib.mysql/hostname.err

2013-08-18 08:18:38 3132 [Note] /usr/sbin/mysqld: ready for connections.

Version: '5.6.13-enterprise-commercial-advanced' socket: '/tmp/mysql.sock'port: 3306

Mysql Enterprise Server

–Advanced Edition (Commercial)

You attempt to log in as the root user with the following command:?

$ mysql

–u root

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password:NO)

Which statement is true about this scenario?

A.The RPM installation script sets a default password of password for newinstallations.

B.The local root user must log in with a blank password initially: mysql

–u root –p.

C.New security measures mean that the mysql_secure_installation script must berun first on all new installations.

D.The mysql_install_db post-installation script used

–-random-passwords.

--------------------------------------

答案：D

分析：

MySQL5.6 Linux RPM

包安裝中會調(diào)用帶有--random-passwords參數(shù)項的mysql_install_db腳本命令, 為root用戶生成一個隨機密碼悼粮，并保存在$HOME/.mysql_secret文件中。

錯曾棕，生成的是一個隨機密碼扣猫，而非默認密碼。B錯翘地，因為本地root用戶已經(jīng)有一個生成的密碼了申尤，因此空密碼是不能登陸成功的。

錯衙耕，mysql_secure_installation是一個非必要腳本昧穿，在MySQL安裝完成后，你可以運行此腳本來進一步增強其安全策略臭杰。參考：http://dev.mysql.com/doc/refman/

... stallation-rpm.htmlhttp://dev.mysql.com/doc/refman/

... e-installation.html

19.A Mysql Server has been running an existing

application successfully for six months.

The my.cnf is adjusted to contain the following

additional configuration:

[mysqld]

default-authentication-plugin=sha256_password

The Mysql Server is restarted without error.

What effect will the new configuration have in

existing accounts?

A.They will have their passwords updated on

start-up to sha256_password format

B.They will have to change their password the next

time they login to the server

C.They are not affected by this configuration

change

D.They all connect via the secure

sha256_password algorithm without any configuration change.

-----------------------------

答案：C分析：

default-authentication-plugin

的作用是為了在create user時默認其所使用的密碼加密格式粤咪。其和已經(jīng)建立的用戶所使用的密碼格式無關(guān)，因此A,B錯渴杆。在客戶端工具進行連接時寥枝，服務(wù)端會在mysql.user表中進行匹配，以了解對應(yīng)登陸用戶所使用的密碼格式磁奖，并以此授權(quán)方式進行登陸驗證囊拜，因此D錯。參考：http://dev.mysql.com/doc/refman/

... ication-plugin.html

20比搭、In a design situation, there

are multiple character sets that can properly encode your data.

Which three should influence your choice of

character set?

A.Syntax when writing queries involving JOINS

B.Syntax when writing queries involving JOINS

C.Memory usage when working with the data

D.Character set mapping index hash size

E.Disk usage when storing data

答案：CDE