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错误，。

正确，根据数据库参数innodb_autoinc_lock_mode的设置，插入操作会根据模式和所用语句的不同选用相应的锁。innodb_autoinc_lock_mode = 2的时候，将不使用表级锁和轻量mutex锁，不过在基于语句复制(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>元素代表了一个事件，如客户连接或关闭连接事件，执行SQL语句等。仅顶层语句会被记录下来，存储程序(如触发器或存储过程)中的语句不会被记录。命令如LOAD DATA INFILE在进行操作时，其对文件内容进行进行操作的具体细节不会被记录。因此，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服务端主程序来运行的，它并不负责MSI安装的过程，因此A错。通过mysqld --install可以进行Windows服务注册，同时--defaults-file用于设置启动服务端时使用的配置文件，B正确。请注意--install命令项仅存在于Windows版MySQL的mysqld命令中，如果你是在Linux上安装MySQL是无法找到mysqld对应的--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整个数据库和其软件，而mysql则是其软件中涵盖的一个客户端工具。本题考的是对这些客户端工具使用。在使用mysql命令行工具登陆服务端后，可以执行的命令也非常多，比如建立和删除数据库，表和索引的增删改等。你也可以使用mysql客户端工具来建立用户，并进行对用户的权限和访问进行管理。当然在Master-Slave Replication的主库和从库，你也可以使用show

master status及show slave status来查看复制的状态情况。因此, ABDE都是正确的。至于关闭MySQL Server，这有多种方式，其中一种是使用mysqladmin客户端工具shutdown命令来实现的,mysql客户端工具不负责这事。而备份，逻辑备份可使用mysqldump 或mysqlpump(从MySQL 5.7.6开始)。而binary backup则可以使用mysqlbackup（如果你的MySQL是企业版的话）或使用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

配置项由多处设置组成，由于Server的主程序为mysqld，因此其命令项设置也主要是看mysqld对应的项的设置。在命令项设置后，启动后相应项反应在数据库上，就是那些Global Variables全局变量了，当用户会话访问时，其Session Variables则会copy自全局变量值，之后用户可以根据需要使用SET命令来修改会话变量。当然如果有足够的权限，用户也可以修改全局变量，不过这种修改仅应用于正在运行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

包安装中会调用带有--random-passwords参数项的mysql_install_db脚本命令, 为root用户生成一个随机密码，并保存在$HOME/.mysql_secret文件中。

错，生成的是一个随机密码，而非默认密码。B错，因为本地root用户已经有一个生成的密码了，因此空密码是不能登陆成功的。

错，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时默认其所使用的密码加密格式。其和已经建立的用户所使用的密码格式无关，因此A,B错。在客户端工具进行连接时，服务端会在mysql.user表中进行匹配，以了解对应登陆用户所使用的密码格式，并以此授权方式进行登陆验证，因此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