MySQL 8.0 OCP 英文题库解析（十五）

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

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

微信图片_20250507171214.png

本期公布试题131~140

试题131:

Examine the full path name of the backup image from MySQL Enterprise Backup with the -
compress option:/backup/full/mybackup/myimage.imgmysqlbackup.cnf contains this 
data:mysqlbackupbackup-dir=/backup/full/myrestorebackup
image=/backup/full/mybackup/myimage.img uncompressYou must perform a database restore to 
a new machine.which command can provision the new database in datadir as /data/MEB? 
B)mysqlbackup --defaults-file=mysqlbackup.cnf --datadir=/data/MEB image-to-dir-and-apply-log 
[错误] 
C)mysqlbackup --defaults-file=mysqlbackup.cnf --datadir=/data/MEB apply-log-and-copy-back [错
误] 
D)mysqlbackup --defaults-file=mysqlbackup.cnf --datadir=/data/MEB copy-back-and-apply-log [正
确] 
A)mysqlbackup --defaults-file=mysqlbackup.cnf --datadir=/data/MEB restore-and-apply-log [错误] 
E)mysqlbackup --defaults-file=mysqlbackup.cnf --datadir=/data/MEB image-to-dir [错误]

解析

题目描述了一个使用MySQL Enterprise Backup (MEB)创建的压缩备份镜像，路径为/backup/full/mybackup/myimage.img。配置文件mysqlbackup.cnf包含以下内容：backup-dir=/backup/full/myrestorebackup
backup-image=/backup/full/mybackup/myimage.img
uncompress要求将数据库恢复到新机器的/data/MEB目录下。正确答案是 
D) mysqlbackup --defaults-file=mysqlbackup.cnf --datadir=/data/MEB copy-back-and-apply-log解析：copy-back-and-apply-log命令是MEB中用于从备份镜像恢复数据库的标准命令它会执行两个操作：首先应用日志(apply-log)准备备份数据然后将数据复制回(copy-back)指定的数据目录--datadir=/data/MEB指定了恢复的目标目录--defaults-file指定了配置文件路径A) restore-and-apply-log - MEB没有这个命令选项B) image-to-dir-and-apply-log - 这个命令会将镜像解压到中间目录，但不直接恢复到数据目录C) apply-log-and-copy-back - 命令顺序错误，应该是先copy-back再apply-logE) image-to-dir - 仅解压镜像，不执行恢复操作

试题132:

Choose the best answer.MySQL Enterprise Monitor Query Analyzer is configured to monitor an 
instance. Which statement is true? 
E)The slow query log must be enabled on the monitored server to collect information for the Query 
Analyzer. [错误] 
A)The Query Response Time index (QRTi) is fixed to 100ms and cannot be customized. [错误] 
C)An agent must be installed locally on the instance to use the Query Analyzer. [错误] 
B)Enabling the events_statements_history_long consumer allows tracking the longest running query. 
[错误] 
D)The Query Analyzer can monitor an unlimited number of normalized statements. [正确]

解析

MySQL Enterprise Monitor Query Analyzer 解析
正确答案是 D) "The Query Analyzer can monitor an unlimited number of normalized statements." (Query Analyzer可以监控无限数量的标准化语句) [正确]
解析：Query Analyzer 的工作原理：Query Analyzer 通过性能模式(Performance Schema)收集查询数据它分析"标准化"的SQL语句(去除具体参数值的通用查询模式)对每种查询模式进行聚合分析，而不是单独记录每个具体查询为什么D选项正确：Query Analyzer 的设计可以处理无限数量的标准化查询模式它不会因为监控的查询类型数量多而达到限制这是MySQL Enterprise Monitor的强大功能之一其他选项错误原因：A) "The Query Response Time index (QRTi) is fixed to 100ms and cannot be customized."错误：QRTi(查询响应时间指数)是可配置的，不是固定值B) "Enabling the events_statements_history_long consumer allows tracking the longest running query."错误：虽然这个消费者可以跟踪长时间运行的查询，但不是Query Analyzer的必要条件C) "An agent must be installed locally on the instance to use the Query Analyzer."错误：MySQL Enterprise Monitor使用远程代理，不需要在被监控实例上本地安装E) "The slow query log must be enabled on the monitored server to collect information for the Query Analyzer."错误：Query Analyzer使用Performance Schema，不需要启用慢查询日志

试题133:

Choose the best answerUsers report errors when trying to connect from 192.0.2.5 and is connecting using the mysql_native password authentication plugin.Examine these commands and output:（见下图）Which statement identifies the cause of the errors? E)thread_cache is too small. [错误] 
F)skip_name_resolve is enabled. [错误] 
C)Connections are attempted without a valid user account or password. [错误] 
B)Network connectivity issues occurring between client and the MySQL instance. [正确] 
D)User accounts are defined using the mysql_native_pasword plugin for password authentication. 
[错误] 
A)max_connections is too small. [错误]

图片.png

解析

选择最佳答案用户在尝试从 192.0.2.5 进行连接时报告错误，并且正在连接。哪个选项显示错位原因。
performance_schema.host_cache 不会直接统计底层网络问题（如 "Connection refused"），而是记录更高层的连接错误（如认证失败、主机名解析失败等）。真正的网络连通性问题需要通过系统日志、防火墙规则或网络工具排查。COUNT_AUTH_PLUGIN_ERRORS:367 身份验证插件报告的错误数 正确答案是 B) "Network connectivity issues occurring between client and the MySQL instance." (客户端和MySQL实例之间存在网络连接问题) [正确]

试题134:

You want to check the values of the sort_buffer_size session variables of all existing connections. 
Which performance_schema table can you query? 
C)variables_by_thread [正确] 
B)session_variables [错误] 
A)global_variables [错误] 
D)user_variables_by_thread [错误]

解析

题目要求查询所有现有连接的 sort_buffer_size 会话变量值，正确的查询表是 performance_schema.variables_by_thread。
各选项分析C) variables_by_thread [正确]这是 performance_schema 中专门存储每个线程(连接)的会话变量的表包含三列：THREAD_ID、VARIABLE_NAME 和 VARIABLE_VALUE可以查询特定会话变量的值，如：SELECT * FROM performance_schema.variables_by_thread 
WHERE VARIABLE_NAME = 'sort_buffer_size';

试题135:

Choose the best answer.Which feature is provided by multi-source replication? A)providing a common source for the same data to be replicated to other servers [错误] 
B)allowing multiple servers to back up to one server [正确] 
C)managing conflicts between two sets of the same data [错误] 
D)providing multi-source replication where all servers act as the master [错误]

解析


正确答案：
B) allowing multiple servers to back up to one server (允许多个服务器备份到一个服务器) [正确]多源复制(Multi-Source Replication)的核心功能多源复制是MySQL 5.7引入的重要功能，它允许一个从服务器(Slave)同时从多个主服务器(Master)接收复制数据。主要用途：将多个独立数据库服务器的数据集中到一个服务器实现数据聚合(OLAP场景、数据仓库建设)简化备份管理(多个源服务器可以备份到单个目标服务器)典型应用场景：分支机构数据集中到总部不同业务系统的数据合并分析跨数据中心的灾备方案其他选项错误原因A) "providing a common source for the same data to be replicated to other servers"错误：描述的是普通主从复制，不是多源复制的特点C) "managing conflicts between two sets of the same data"错误：MySQL不自动处理多源复制中的数据冲突，需要应用层解决D) "providing multi-source replication where all servers act as the master"错误：多源复制中只有从服务器接收多个源，不是所有服务器都充当主服务器

试题136:

Choose the best answer.t is a non-empty InnoDB table.Examine these statements, which are 
executed in one session:BEGIN SELECT * FROM t FOR UPDATE;Which is true? 
D)If OPTIMIZE TABLE; is invoked, it will create a table lock on t and force a transaction rollback. [正确] 
C)If OPTIMIZE LOCAL TABLE t; is invoked from another session, it executes normally and returns the 
status. [错误] 
B)If ANALYZE TABLE; is invoked from the same session, it hangs until the transaction is committed 
or rolled back. [错误] 
A)mysqlcheck --analyze --all-databases will execute normally on all tables and return a report. [错误]

解析


正确答案：
D) "If OPTIMIZE TABLE; is invoked, it will create a table lock on t and force a transaction rollback." (如果调用OPTIMIZE TABLE，它将在表t上创建表锁并强制事务回滚) [正确]BEGIN;SELECT * FROM t FOR UPDATE;  -- 获取了表t的行级排他锁(X锁)关键点：FOR UPDATE 获取的是行级排他锁，不是表锁但某些DDL操作需要获取表级锁为什么选项D正确OPTIMIZE TABLE 的行为：实际执行的是 ALTER TABLE 操作需要获取表级排他锁(X锁)会强制终止当前持有锁的事务(导致回滚)这是InnoDB的机制，防止长时间锁等待错误日志表现：会记录类似 "Lock wait timeout exceeded; try restarting transaction" 的错误然后自动回滚被阻塞的事务其他选项错误原因A) "mysqlcheck --analyze --all-databases will execute normally on all tables and return a report."错误：ANALYZE TABLE 也需要获取锁，会被阻塞或导致事务回滚B) "If ANALYZE TABLE; is invoked from the same session, it hangs until the transaction is committed or rolled back."错误：同一会话中执行会直接死锁，MySQL会检测到并终止一个操作C) "If OPTIMIZE LOCAL TABLE t; is invoked from another session, it executes normally and returns the status."错误：LOCAL 修饰符不影响锁行为，仍然需要获取表锁

试题137:

Choose the best answe You have upgraded the MySQL binaries from 5.7.28 to 8.0.18 by using an in
place upgrade. Examine the message sequence generated during the first start of MySQL 8.0.18:（见
下图）Which step or set of steps will resolve the errors?

图片.png

A)Start mysqld again using the --upgrade=FORCE option. [错误] 
C)Execute:mysqlcheck --repair mysql columns_priv event proc proxies_priv tables_priv. [正确] 
B)Go to the <datadir>/mysql directory and execute:myisamchk --update-state columns_priv event proc proxies_priv tables_priv. [错误] 
E)Execute:mysqlcheck --check-upgrade mysql columns_priv event proc proxies_priv tables_priv. [错误] 
D)Remove the redo logs. Replace the MySQL binaries with the 5.7.28 binaries. Prepare the tables for upgrade. Upgrade to 8.0.18 again. [错误]

解析

错误日志分析从错误日志可以看出：从MySQL 5.7.28升级到8.0.18时出现问题多个系统表需要升级：columns_priv, event, proc, proxies_priv, tables_priv这些表无法正常打开，导致DD表填充失败，最终服务中止为什么选项C正确mysqlcheck --repair 是解决此问题的正确方法：专门用于修复表结构问题可以处理升级过程中的表格式不兼容问题针对mysql系统数据库中的特定表进行修复是MySQL官方推荐的升级问题解决方法其他选项错误原因A) "--upgrade=FORCE" 选项：错误：这个选项不会修复表结构问题，只是强制升级过程继续B) "myisamchk --update-state"：错误：这些是InnoDB表，不应使用MyISAM工具修复D) 回退并重新升级：错误：过于复杂，不是首选解决方案E) "mysqlcheck --check-upgrade"：错误：只检查不修复，不能解决问题

试题138:

Choose the best answer.Which statement is true about MySQL Enterprise Transparent Data 
Encryption (TDE)? 
B)TDE can encrypt InnoDB and MyISAM tables only when the tables are stored in the SYSTEM 
tablespace. [错误] 
C)Lost tablespace encryption keys can be regenerated only if the master database key is known or 
present in the Key Vault specification. [错误] 
D)Both MyISAM and InnoDB tables can be encrypted by setting the keyring_engine = All variable in 
the MySQL configuration file. [错误] 
A)MySQL TDE uses an appropriate keyring plugin to store the keys in a centralized location. [正确]

解析

MySQL Enterprise 透明数据加密(TDE)解析
题目原文：Which statement is true about MySQL Enterprise Transparent Data Encryption (TDE)?翻译：关于MySQL企业版透明数据加密(TDE)，哪个陈述是正确的？正确答案：
A) "MySQL TDE uses an appropriate keyring plugin to store the keys in a centralized location." 
(MySQL TDE使用适当的keyring插件将密钥存储在集中位置) [正确]B）TDE can encrypt InnoDB and MyISAM tables only when the tables are stored in the SYSTEM tablespace.
TDE只能在表存储在SYSTEM表空间时加密InnoDB和MyISAM表。
错误原因：TDE可以加密所有InnoDB表空间(包括独立表空间)MyISAM表不支持TDE加密不限于SYSTEM表空间C）Lost tablespace encryption keys can be regenerated only if the master database key is known or present in the Key Vault specification.
丢失的表空间加密密钥只有在主数据库密钥已知或存在于Key Vault规范中时才能重新生成。错误原因：如果加密密钥丢失且没有备份，数据将永久无法访问主密钥不能"重新生成"，只能使用备份恢复这是TDE的重要安全特性，不是缺陷D）Both MyISAM and InnoDB tables can be encrypted by setting the keyring_engine = All variable in the MySQL configuration file.
通过在MySQL配置文件中设置keyring_engine = All，可以加密MyISAM和InnoDB表。错误原因：不存在keyring_engine = All这样的参数MyISAM表不支持TDE加密InnoDB加密是通过表空间加密实现，不是通过这个参数

试题139:

You are using the InnoDB engine and the innodb_file_per_table option is set. You delete a significant number of rows of a large table named FACTORY.INVENTORY.Which command will reorganize the physical storage of table data and associated index data for the INVENTORY table, in order to reduce storage space and improve I/O efficiency? 
B)ANALYZE TABLE FACTORY.INVENTORY  [错误] 
C)OPTIMIZE TABLE FACTORY.INVENTORY [正确] 
E)mysqldump -u root -p FACTORY INVENTORY [错误] 
A)CHECK TABLE FACTORY.INVENTORY [错误] 
D)mysqlcheck -u root -p FACTORY.INVENTORY  [错误]

解析

您正在使用InnoDB引擎且设置了innodb_file_per_table选项。您删除了名为FACTORY.INVENTORY的大表中的大量行。哪个命令可以重组INVENTORY表的物理存储及其关联的索引数据，以减少存储空间并提高I/O效率？optimize table、analyze table 和 alter table 这三种方式重建表的区别。
从 MySQL 5.6 版本开始，alter table t engine = InnoDB（也就是 recreate）（有短暂MDL写锁，后转换为MDL读锁）
analyze table t 其实不是重建表，只是对表的索引信息做重新统计，没有修改数据，这个过程中加了 MDL 读锁；
optimize table t 等于 recreate+analyze。C) OPTIMIZE TABLE FACTORY.INVENTORY [正确]B) ANALYZE TABLE FACTORY.INVENTORY [错误]仅更新优化器使用的索引统计信息A) CHECK TABLE FACTORY.INVENTORY [错误]仅检查表错误D) mysqlcheck -u root -p FACTORY.INVENTORY [错误]命令行表维护工具（默认执行CHECK操作）
局限：需要明确指定--optimize参数才等效OPTIMIZEE) mysqldump -u root -p FACTORY INVENTORY [错误]创建逻辑备份
局限：需配合DROP/CREATE TABLE才能重组存储

试题140:

Choose the best answer.You must configure the MySQL command-line client to provide the highest 
level of trust and security when connecting to a remote MySQL Server.Which value of --ssl-mode 
will do this? 
D)REQUIRED [错误] 
A)VERIFY_CA [错误] 
B)PREFERRED [错误] 
C)VERIFY_IDENTITY [正确]

解析

题目要求配置MySQL命令行客户端在连接远程服务器时提供最高级别的信任和安全性，这需要通过--ssl-mode参数设置。
各SSL模式安全级别对比C) VERIFY_IDENTITY [正确]安全级别：最高功能：强制SSL加密连接验证CA证书有效性额外验证服务器主机名是否匹配证书中的身份信息防止的攻击：中间人攻击(MITM)、证书伪造典型应用：生产环境关键系统连接A) VERIFY_CA [错误]安全级别：高局限：只验证CA证书，不验证主机名匹配风险：可能接受正确CA签发但错误主机的证书D) REQUIRED [错误]安全级别：中局限：强制SSL但不验证证书风险：可能接受自签名或无效证书B) PREFERRED [错误]安全级别：低行为：尝试SSL但不强制，失败则回退到非加密连接风险：可能降级到不安全连接