MySQL 8.0 OCP 英文题库解析（七）

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

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

本期公布试题51~60

试题51:

Choose two.An existing asynchronous replication setup is running MySQL 8. Which two steps are a 
part of implementing GTID replication? 
F)On the slave, alter the MySQL master connection setting with:CHANGE MASTER TO 
MASTER_AUTO_POSITION = 1; [正确] 
D)Execute this on the slave to enable GTID:START SLAVE IO_THREAD WITH GTID; [错误] 
C)Execute this on the slave to enable GTID:RESET SLAVE; START SLAVE GTID_NEXT=AUTOMATIC; 
[错误] 
A)Enable GTID by executing this on the master and the slave:SET GLOBAL GTID_ENABLED=on; [错误] 
E)Restart MySQL (master and slave) with these options enabled:--gtid_mode=ON --log-bin --log
slave-updates --enforce-gtid-consistency [正确] 
B)On the slave, alter the MySQL master connection setting with:ALTER channel CHANGE MASTER 
TO MASTER_AUTO_POSITION = 1; [错误]

解析

现有的异步复制设置正在运行 MySQL 8。实施 GTID 复制包括哪两个步骤？

F)On the slave, alter the MySQL master connection setting with:CHANGE MASTER TO MASTER_AUTO_POSITION = 1; [正确] D)Execute this on the slave to enable GTID:START SLAVE IO_THREAD WITH GTID; [错误] 
语法错误，START SLAVE 不支持 WITH GTID 子句。C)Execute this on the slave to enable GTID:RESET SLAVE; START SLAVE GTID_NEXT=AUTOMATIC; [错误] 
GTID_NEXT 是客户端会话变量，不能通过 START SLAVE 设置。A)Enable GTID by executing this on the master and the slave:SET GLOBAL GTID_ENABLED=on; [错误] 
MySQL 8 中无此参数，需通过 gtid_mode=ON 配置。E)Restart MySQL (master and slave) with these options enabled:--gtid_mode=ON --log-bin --logslave-updates --enforce-gtid-consistency [正确] B)On the slave, alter the MySQL master connection setting with:ALTER channel CHANGE MASTER TO MASTER_AUTO_POSITION = 1; [错误]
语法错误，CHANGE MASTER 无需 ALTER channel 前缀。

试题52:

Choose two.You plan to install MySQL Server by using the RPM download. Which two statements 
are true? 
D)MySQL uses the RPM relocatable installation target feature. [错误] 
A)You must manually initialize the data directory. [错误] 
E)You can find the root password in the error log after the first start. [正确] 
C)The MySQL RPM package installation supports deploying multiple MySQL versions on the same 
host. [错误] 
F)The functionality is split among several RPM package files. [正确] 
B)You can provide the root password interactively. [错误]

解析

通过 RPM 包安装 MySQL Server 的两个正确陈述

E) 首次启动后，root 密码可在错误日志中找到原因：从 MySQL 5.7 开始，RPM 安装后首次启动时，系统会生成一个 临时 root 密码 并写入错误日志文件（默认路径：/var/log/mysqld.log）。F) 功能被拆分到多个 RPM 包中原因：MySQL 的 RPM 安装包通常分为多个文件，例如：mysql-community-server（核心服务端）mysql-community-client（客户端工具）mysql-community-common（共享库）等。安装示例：
sudo rpm -ivh mysql-community-{server,client,common}-*.rpm选项	                错误原因
D (RPM 可重定位安装)	MySQL 的 RPM 包 不支持 自定义安装路径（如 /opt），默认安装到 /usr/bin 和 /var/lib/mysql。
A (需手动初始化数据目录)	RPM 安装后首次启动服务时，mysqld 会自动初始化数据目录（无需手动执行 mysqld --initialize）。
C (支持多版本共存)	同一主机上 无法 通过 RPM 同时安装多个主要版本（如 8.0 和 5.7），会因文件冲突失败。
B (交互式设置 root 密码)	RPM 安装过程 不会 提示输入 root 密码，密码通过临时密码或后续 ALTER USER 设置。

试题53:

Choose four.Which four connection methods can MySQL clients specify with the --protocol option 
when connecting to a MySQL server? 
A)IPv4 [错误] 
E)IPv6 [错误] 
F)FILE [错误] 
B)SOCKET [正确] 
H)DIRECT [错误] 
G)TCP [正确] 
D)PIPE [正确] 
C)MEMORY [正确]

解析

连接到 MySQL 服务器时，MySQL 客户端可以使用 --protocol 选项指定哪四种连接方法？
–protocol=TCP|SOCKET|PIPE|MEMORY

试题54:

Choose four.You have a MySQL client installed on your Linux workstation with a default installation. 
You have your admin login credentials to connect to a MySQL server running Microsoft Windows 
on remote host 192.0.2.1:3306 to connect to the world database.Which four options need to be 
specified to complete this task with a single command?  I)--database=world [错误] 
C) --host=192.0.2.1 [正确] 
D)--protocol=UDP [错误] 
G)--socket=/tmp/mysql.sock [错误] 
E)--user=admin [正确] 
B) --protocol=pipe [错误] 
A) --port=3306 [正确] 
F)--password [正确] 
H)--shared-memory-base-name=world [错误]

解析

远程连接到192.0.2.1:3306 的world的库
需要哪些

1. --host=192.0.2.1 (C)作用：指定远程服务器的 IP 地址。必须性：连接远程服务器时必须明确主机地址。2. --user=admin (E)作用：指定管理员用户名（题目中已提供 admin 凭证）。必须性：身份验证必需项。3. --password (F)作用：提示输入密码（或直接使用 --password=your_password，但不安全）。必须性：除非配置了无密码登录，否则必须提供。4. --port=3306 (A)作用：指定 MySQL 服务端口（默认 3306，但需显式声明以确保连接正确）。必须性：若端口非默认值，则必须指定。I) --database=world	
非必需。连接后可手动 USE world;，题目未要求直接操作该库。

试题55:

Choose three.Which three are characteristics of a newly created role? 
D) It can be granted to user accounts. [正确] 
A) It is stored in the mysql.role table. [错误] 
F) It is created as a locked account. [正确] 
C) It can be protected with a password. [错误] 
B) It can be dropped using the DROP ROLE statement. [正确] 
E) It can be renamed using the RENAME ROLE statement. [错误]

解析

新创建角色的核心特性包括：
✅ 可授予用户 (D)
✅ 默认锁定 (F)
✅ 可删除 (B)
其他选项（如密码保护、重命名）是用户账户的特性，不适用于角色。

试题56:

Table t is an InnoDB table.Examine these statements and output:
SELECT COUNT(1) FROM t;
+----------+
| COUNT(1) |
+----------+
|       72 |
+----------+
SHOW INDEXES FROM t\G
*************************** 1. row ***************************Table: tNon_unique: 0Key_name: PRIMARYSeq_in_index: 1Column_name: aCollation: ACardinality: 72Sub_part: NULLPacked: NULLNull:Index_type: BTREEComment:
Index_comment:Visible: YESExpression: NULL
*************************** 2. row ***************************Table: tNon_unique: 1Key_name: b_idxSeq_in_index: 1Column_name: bCollation: ACardinality: 1Sub_part: NULLPacked: NULLNull: YESIndex_type: BTREEComment:
Index_comment:Visible: NOExpression: NULL
2 rows in set (0.00 sec)Which two are true? 
B)Table t has two viable indexes to be used for queries. [错误] 
A)ANALYZE TABLE t would update index statistics uniquely for the PRIMARY index. [错误] 
D)Index b_idx has a low number of unique values.  [正确] 
C)SELECT b from t would perform a table scan.  [正确] 
E)SELECT a FROM t would perform a table scan.  [错误] 

解析

C) SELECT b FROM t 会执行全表扫描 [正确]原因：索引 b_idx 的 Visible 属性为 NO，表示该索引不可见（MySQL 8.0+ 特性）。优化器会忽略不可见索引，因此查询 b 列时无法使用 b_idx，只能全表扫描。D) 索引 b_idx 的唯一值数量很少 [正确]原因：Cardinality（基数）表示索引中唯一值的估计数量，b_idx 的基数为 1，说明 b 列的值几乎全部重复（如全是 NULL 或相同值）。低基数的索引对查询优化帮助有限。B)只有主键索引（PRIMARY）是可见的，b_idx 不可见，因此仅 PRIMARY 可用。A)ANALYZE TABLE 会更新所有索引的统计信息，包括 b_idx（即使不可见。E）列 a 是主键，查询一定会走主键索引，不会全表扫描。

试题57:

Choose two.Which two statements are true about the mysqld-auto.cnf file? 
B)This file is for logging purposes only and is never processed. [错误] 
A)It is always updated with changes to system variables. [错误] 
E)It is read and processed at the beginning of startup configuration. [错误] 
D)This file is for storing MySQL Server configuration options in JSON format. [正确] 
F)This file is for storing MySQL server_uuid values only. [错误] 
C)It is read and processed at the end of startup configuration. [正确] 

解析

mysqld-auto.cnf 是 MySQL 8.0 引入的 动态配置持久化文件，专门用于保存通过 SET PERSIST 或 SET PERSIST_ONLY 命令修改的全局系统变量，采用 JSON 格式存储。
D)This file is for storing MySQL Server configuration options in JSON format. [正确] C)It is read and processed at the end of startup configuration. [正确] 
该文件在启动配置的最后阶段被读取 (C) [正确]加载顺序MySQL 启动时配置加载优先级：命令行参数 > 2. 传统配置文件（my.cnf）> 3. mysqld-auto.cnf最后加载确保动态持久化的配置覆盖其他设置。A) 所有系统变量变更都会更新此文件	
仅通过 SET PERSIST 系列命令修改的变量会写入	B) 仅用于日志记录	
该文件是 功能性配置，直接影响服务器行为	与日志文件（如 error log）无关E) 在启动开始时读取	
实际在 最后阶段 加载以实现覆盖	F) 仅存储 server_uuid	
server_uuid 存储在 auto.cnf 中	两者文件名和用途均不同

试题58:

Choose two.Examine this command and output:（见下图）Which two statements are true?

F) The lock is at the table object level. [错误] 
A) The lock is an exclusive lock. [正确] 
B) The lock is a shared lock. [错误] 
D) The lock is an intentional lock. [错误] 
C) The lock is a row-level lock. [正确] 
E) The lock is at the metadata object level. [错误]

解析

分析这个锁类型
从lock type可以看到是record 行锁
lock mode可以看到是X 排他锁
所以A C正确，其他错误

试题59:

Choose two.Your MySQL installation is running low on space due to binary logs. You need to reduce 
your log space usage urgently.Which two sets of actions when completed will accomplish this? 
E)Use SET GLOBAL binlog_expire_logs_seconds=<value> and run the FLUSH BINARY LOGS command. [正确] 
B)Use SET GLOBAL binlog_expire_logs_seconds=<value> and restart the server. [错误] 
F)Set binlog_expire_logs_seconds = 0 in my.cnf and restart the server. [错误] 
D)Set binlog_expire_logs_seconds in my.cnf. [错误] 
A)Use SET PERSIST binlog_expire_logs_seconds=<value>. [错误] 
C)Use PURGE BINARY LOGS to <binlog_name>. [正确]

解析

选择两个。由于二进制日志，您的 MySQL 安装空间不足。您需要紧急减少日志空间使用量。哪两组作完成后将完成此作？

使用 PURGE BINARY LOGS 手动删除日志 (C) [正确]
SET GLOBAL binlog_expire_logs_seconds = 86400;设为 0 会禁用自动清理。A正确。B) 设置后重启服务器	
无需重启，SET GLOBAL 已实时生效，重启是冗余操作F) 设为 0 并重启	binlog_expire_logs_seconds=0 会 禁用自动清理，加剧空间问题	与目标矛盾D) 仅修改 my.cnf	配置文件需重启生效，无法紧急释放空间	
适合长期配置但非紧急处理A) 使用 SET PERSIST	
虽能持久化配置，但不会立即清理现有日志	

试题60:

Choose three.You must run multiple instances of MySQL Server on a single host. Which three 
methods are supported? 
C)Run mysqld with --datadir defined for each instance. [错误] 
E)Start mysqld or mysqld_safe using different option files for each instance. [正确] 
A)Use system tools to lock each instance to its own CPU. [错误] 
F)Use systemd with different settings for each instance. [正确] 
D)Run MySQL Server docker containers. [正确] 
B)Use resource groups to lock different instances on separate CPUs. [错误] 

解析

在单个主机上运行 MySQL Server 的多个实例。

1. 使用 Docker 容器运行多个实例 (D) [正确]2. 为每个实例使用不同的选项文件 (E) [正确]通过独立的配置文件（如 `my.cnf`）为每个实例指定不同的参数（如端口、数据目录、socket 文件等）3. 使用 systemd 管理多个实例 (F)** [正确] 通过 systemd 单元文件为每个实例定义独立服务，指定不同的启动参数和环境。A) 使用系统工具锁定 CPU
多实例运行无关 B) 使用资源组锁定 CPU 
MySQL 资源组用于查询级 CPU 控制，不适用于实例隔离，混淆概念 C) 仅定义 datadir ，必须同时指定端口、socket 等参数，否则冲突