Oracle 为庆祝 MySQL 30 周年，截止到 2025.07.31 之前。所有人均可以免费考取原价245美元的MySQL OCP 认证。

从今天开始，将英文题库免费公布出来，并进行解析，帮助大家在一个月之内轻松通过OCP认证。

本期公布试题161~170

试题161:

The languages table uses MyISAM and the countries table uses the InnoDB storage engine.Both 
tables are empty.Examine these statements:BEGIN;INSERT INTO languages(lang) VALUES 
(“Italian”);INSERT INTO countries(country) VALUES(“Italy”);ROLLBACK;What is the content of 
both tables after executing these statements? 
A)countries has one row, languages has none. [错误] 
B)Both tables have one row. [错误] 
D)languages has one row, coutries has none.  [正确] 
C)Both tables are empty. [错误]

解析

languages 表使用 MyISAM 存储引擎，countries 表使用 InnoDB 存储引擎。两表均为空。
检查以下 SQL 语句：
sqlBEGIN;
INSERT INTO languages(lang) VALUES ("Italian");
INSERT INTO countries(country) VALUES ("Italy");
ROLLBACK;执行这些语句后，两表的内容是什么？正确答案：D原因：MyISAM（languages 表）：不支持事务，INSERT 语句会立即提交，ROLLBACK 无效。结果：表中保留 Italian。InnoDB（countries 表）：支持事务，ROLLBACK 会撤销未提交的 INSERT。结果：表中无 Italy。

试题162:

What does the slave I/0 thread do? 
B)monitors and schedules I/O calls to the subsystem for the relay logs [错误] 
D)acquires a lock on the binary log for reading each event to be sent to the slave [错误] 
A)connects to the master and requests it to send updates recorded in its binary logs [正确] 
C)reads the relay log and executes the events contained in them [错误]

解析

题目： slave I/O线程的作用是什么？A) 连接到主服务器并请求发送其二进制日志中记录的更新 [正确]
B) 监控并调度对中继日志子系统的I/O调用 [错误]
C) 读取中继日志并执行其中包含的事件 [错误]
D) 获取二进制日志上的锁以读取要发送给从服务器的每个事件 [错误]

试题163:

Choose the best answer. Examine these commands and results:
SHOW GRANTS FOR jane; 
GRANT USAGE ON *.* TO ‘’（见下图）
Jane must create a temporary table named TOTALSALES in the 
SALES database.Which statement will provide Jane with the required privileges based on the principle of least privilege? D)GRANT ALL ON sales.* TO jane; [错误] 
A)GRANT CREATE TEMPORARY TABLES, INSERT, UPDATE, DELETE, SELECT ON sales.totalsales TO 
jane; [错误] 
B)GRANT CREATE TEMPORARY TABLES ON sales.* TO jane; [正确] 
C)GRANT CREATE TEMPORARY TABLES ON sales.totalsales TO jane; [错误] 

解析

临时表建表权限，选B

试题164:

Choose the best answer.Where is the default data directory located after installing MySQL using RPM on Oracle Linux 7? E)/usr/bin [错误] 
B)/usr/mysql [错误] 
D)/var/1ib/mysql [正确] 
C)/etc/my.cnf [错误] 
A)/usr [错误]

解析

D) /var/lib/mysql 是正确的默认数据目录位置。

试题165:

Binary log events for the 'mydb1' schema must be copied to a different schema name 
'mydb2' .Which command will do this? 
A)mysqlbinlog -- rewrite-db=' mydb1- >mydb2' I mysql [正确] 
C)mysqlbinlog -- rewrite-db=' mydb1' -- rewrite-db=' mydb2' I mysql [错误] 
D)mysqlbinlog --read- from= remote-server --rawl sed's/mydb1 /mydb2/g' I mysql [错误] 
B)mysqlbinlog --datebase=mydb1 --database=mydb2 lmysql [错误] 

解析

正确选项A解析：
--rewrite-db='oldname->newname' 是mysqlbinlog的正确参数格式，用于将二进制日志中的数据库名从mydb1重写为mydb2，然后通过管道(|)将结果传递给mysql命令执行。

试题166:

Examine this snippet from the binary log file named binlog.000036:# at 5000324#191120 14155116 
server id 1 end_log_pos 500453 crc32 0x98159515 Query thread_id=9 exec_time=2 
error_code=0xid=1106SET TIMESTAMP=1574222116/*!*/;DROP TABLE ‘rental’/* generated by 
server*//*!*/;The rental table was accidentally dropped, and you must recover the table.You have 
restored the last backup, which corresponds to the start of the binlog.000036 binary log. Which 
command will complete the recovery? 
D)mysqlbinlog --stop-position=500453 binlog.000036 mysql [错误] 
B)mysqlbinlog --stop-datetime='2019-11-20 14:55:16' binlog.000036 mysql [错误] 
C)mysqlbinlog --stop-datetime='2019-11-20 14:55:18' binlog.000036 mysql [错误] 
A)mysqlbinlog --stop-position=500324 binlog.000036 mysql [正确]

解析

rental表被意外删除，你必须恢复该表。你已经恢复了最后一个备份，该备份对应于binlog.000036二进制日志的开头。哪个命令能完成恢复？A) mysqlbinlog --stop-position=500324 binlog.000036 | mysql 是正确的命令。
解析这道题考察的是如何使用mysqlbinlog进行时间点恢复(PITR)。关键点分析：日志显示DROP TABLE事件开始于position 5000324，结束于500453要恢复rental表，需要应用binlog中在DROP TABLE之前的操作应该停止在DROP TABLE开始的位置(5000324)，而不是结束位置(500453)

试题167:

Choose the best answer.You recently upgraded your MySQL installation to MySQL 8.0. Examine this 
client error:ERROR 2059 (HY000):Authentication plugin 'caching_sha2_password' cannot be 
loaded:/usr/local/mysql/lib/plugin/caching_sha2_password.so:cannot open shared object file:No 
such file or directoryWhich option will allow this client to connect to MySQL Server? 
B)mysqld default_authentication_plugin=sha256_password [错误] 
A)ALTER USER user IDENTIFIED WITH caching_sha2_password BY 'password'; [错误] 
D)ALTER USER user IDENTIFIED WITH mysql_native_password BY 'password' ; [正确] 
F)mysqld default_authentication_plugin=mysql_native_password [错误] 
C)mysqld default_authentication_plugin=caching_sha2_password [错误] 
E)ALTER USER user IDENTIFIED WITH sha256_password BY 'password' ; [错误] 

解析

MySQL 8.0默认使用caching_sha2_password认证插件，但某些客户端可能不支持或缺少相关库文件，导致连接失败。正确选项D解析：
将用户认证方式改为旧的mysql_native_password插件可以解决此问题，因为：mysql_native_password是MySQL 5.7及之前版本的默认插件所有MySQL客户端都支持这个插件不需要额外的共享库文件

试题168:

Choose the best answer.Examine this SQL statement:UPDATE world.city SET Population = Population 
* 1.1 WHERE CountryCode IN (SELECT Code FROM world.country WHERE Continent = ' Asia ' )Which 
set of privileges will allow Tom to execute this SQL statement? 
C) GRANT UPDATE ON `world`.`city` TO `tom`@`%`； GRANT SELECT ON `world`.`country` TO 
`tom`@`%` [正确] 
A) GRANT UPDATE ON `world`.* TO `tom`@`%`; GRANT ALL PRIVILEGES ON `world`.`country` TO 
`tom`@`%`; [错误] 
B) GRANT UPDATE ON `world`.`city` TO `tom`@`%`； GRANT SELECT ON `world`.* TO `tom`@`%` [错
误] 
D) GRANT ALL PRIVILEGES ON `world`.`city` TO `tom`@`%`； GRANT SELECT (`code`) ON 
`world`.`country` TO `tom`@`%` [错误]

解析

正确答案C) 
GRANT UPDATE ON world.city TO tom@%; 
GRANT SELECT ON world.country TO tom@%; 
是正确的权限组合。这道题考察的是MySQL权限管理，特别是执行UPDATE语句涉及子查询时所需的权限。SQL语句分析：主语句：UPDATE world.city - 需要city表的UPDATE权限子查询：SELECT Code FROM world.country - 需要country表的SELECT权限正确选项C解析：授予city表的UPDATE权限允许修改数据授予country表的SELECT权限允许执行子查询这是最小权限原则的完美体现错误选项分析：A) 授予world.*的UPDATE权限范围过大，且country表的ALL PRIVILEGES权限过大B) 授予world.*的SELECT权限范围过大D) 列级权限(SELECT(code))不适用于此场景，且ALL PRIVILEGES权限过大

试题169:

Choose the best answer.Which command enables rule-based MySQL Auditing capabilities? 
D)shell> mysql < audit_log_filter_linux_install.sql. [正确] 
B)mysql> INSTALL COMPONENT audit_log; [错误] 
A)shell> mysqld --initialize --log-raw=audit.log [错误] 
C)mysql> INSTALL PLUGIN audit_log; [错误]

解析

题目： 哪个命令可以启用基于规则的MySQL审计功能？
选项翻译A) shell> mysqld --initialize --log-raw=audit.log [错误]
B) mysql> INSTALL COMPONENT audit_log; [错误]
C) mysql> INSTALL PLUGIN audit_log; [错误]
D) shell> mysql < audit_log_filter_linux_install.sql [正确]正确答案
D) shell> mysql < audit_log_filter_linux_install.sql 是正确的命令。
解析这道题考察的是MySQL企业版审计功能的安装和启用方式。正确选项D解析：
在MySQL企业版中，基于规则的审计功能需要通过执行特定的SQL脚本来安装：audit_log_filter_linux_install.sql 脚本包含安装审计功能所需的所有SQL语句通过命令行执行该脚本可以正确安装和配置审计功能这是MySQL企业版审计功能的官方安装方法

试题170:

Choose the best answer.You are having performance issues with MySQL instances. Those servers 
are monitored with MySQL Enterprise Monitor.Using Query Analyzer, where do you begin to look 
for problem queries? 
A)Sort the \Exec\ column and check for SQL queries with low Query Response Time index (QRTi) 
values. [正确] 
C)Sort the \Exec\ column and check for SQL queries with high Query Response Time index (QRTi) 
values. [错误] 
B)Look for queries with low total latency times in the Latency section in the times series graph. [错误] 
D)Look for queries with big prolonged spikes in row activity/access graph in the times series graph. 
[错误]

解析

题目： 你的MySQL实例遇到性能问题。这些服务器使用MySQL企业监控器进行监控。使用查询分析器(Query Analyzer)时，你应该从哪里开始查找问题查询？
选项翻译A) 按"Exec"列排序，检查查询响应时间指数(QRTi)值低的SQL查询 [正确]
B) 在时间序列图的延迟部分查找总延迟时间低的查询 [错误]
C) 按"Exec"列排序，检查查询响应时间指数(QRTi)值高的SQL查询 [错误]
D) 在时间序列图中查找行活动/访问图中出现长时间大幅波动的查询 [错误]
正确答案A) 按"Exec"列排序，检查查询响应时间指数(QRTi)值低的SQL查询 是正确的做法。
解析这道题考察的是使用MySQL Enterprise Monitor的Query Analyzer进行性能问题诊断的方法。正确选项A解析：QRTi(Query Response Time index)是衡量查询性能的关键指标QRTi值低表示查询性能差(与基准相比)按执行次数(Exec)排序可以快速定位高频低效查询这是性能调优的标准做法：先解决高频低效查询