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

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

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

本期公布试题71~80

目前已快更新过半，想更快学完所有题库，可以关注这个公众号

试题71:

Choose two.You must export data from a set of tables in the world_x database. Examine this set of 
tables:Tables (country, countryinfo, location)Which two options will export data into one or more 
files? 
C)shell> mysqlexport world_x country countryinfo location > mydump.sql [错误] 
A)shell> mysqldump world_x country countryinfo location > mydump.sql [正确] 
E)shell> mysql --batch world_x.country world_x.countryinfo world_x.1ocation > mydump.sql [错误] 
D)mysql> CLONE LOCAL DATA DIRECTORY = '/var/lib/mysql/world_x/country' ; mysql> CLONE 
LOCAL DATA DIRECTORY = '/var/lib/mysql/world_x/countryinfo' ; mysql> CLONE LOCAL DATA 
DIRECTORY = '/var/lib/mysql/world_x/location' ; [错误] 
B)mysql> SELECT * INTO OUTFILE '/output/country. txt' FROM world_x.country; mysql> SELECT * 
INTO OUTFILE '/output/countryinfo. txt' FROM world_x.countryinfo; mysql> SELECT * INTO OUTFILE 
'/output/location. txt' FROM world_x.location; [正确] 

解析

选择两个。您必须从 world_x 数据库中的一组表中导出数据。检查这组表：表 （country， countryinfo， location）哪两个选项会将数据导出到一个或多个文件中？

C) mysqlexport [错误]
    无效命令：MySQL 不存在 mysqlexport 工具，正确工具是 mysqldump。

E) mysql --batch world_x.country world_x.countryinfo world_x.location > mydump.sql [错误]
    语法错误：--batch 模式仅用于非交互式查询，不能直接导出表数据到文件。

D) CLONE LOCAL DATA DIRECTORY [错误]
    用途不符：CLONE 命令用于克隆远程/本地数据库实例，而非导出特定表数据。

A) shell> mysqldump world_x country countryinfo location > mydump.sql [正确]

B) mysql> SELECT * INTO OUTFILE '/output/country.txt' FROM world_x.country;（及类似语句） [正确]

试题72:

Choose three.Which are three benefits of using mysqlbackup instead of mysqldump? 
B)mysqlbackup allows logical backups with concurrency resulting in faster backups and restores. [错
误] 
A)mysqlbackup can perform partial backup of stored programs. [错误] 
F)mysqlbackup restores data from physical backups, which are faster than logical backups. [正确] 
C)mysqlbackup integrates tape backup and has the virtual tape option. [正确] 
E)mysqlbackup does not back up MySQL system tables, which shortens backup time. [错误] 
D)mysqlbackup can back up tables with the InnoDB engine without blocking reducing wait times 
due to contention. [正确]

解析

以下是关于 MySQL Enterprise Backup (mysqlbackup) 相比 mysqldump 的三个核心优势及解析：

F) mysqlbackup restores data from physical backups, which are faster than logical backups. [正确]
    物理备份恢复更快：
        mysqlbackup 直接备份 InnoDB 数据文件（如 .ibd 文件），恢复时只需复制文件，速度远高于 mysqldump 的逻辑备份（需执行 SQL 重建数据）。

C) mysqlbackup integrates tape backup and has the virtual tape option. [正确]
    支持磁带备份与虚拟磁带：
        企业级功能，可直接备份到磁带设备或虚拟磁带库（VTL），适合大规模数据归档需求。

D) mysqlbackup can back up tables with the InnoDB engine without blocking, reducing wait times due to contention. [正确]
    非阻塞备份 InnoDB 表：
        通过 Hot Backup 技术备份 InnoDB 表时不阻塞读写操作，而 mysqldump 默认会加锁（需配合 --single-transaction 避免锁表）。

B) mysqlbackup allows logical backups with concurrency... [错误]
    mysqlbackup 是物理备份工具，不支持逻辑备份。逻辑备份并发是 mysqldump 的特性（如 --parallel-schemas）。

A) mysqlbackup can perform partial backup of stored programs. [错误]
    不支持部分存储程序备份：mysqlbackup 按表空间或数据库备份，无法单独备份存储过程/函数（mysqldump 可通过 --routines 实现）。

E) mysqlbackup does not back up MySQL system tables... [错误]
    会备份系统表：mysqlbackup 默认备份包括 mysql 系统库（如用户权限表），但可通过 --exclude-databases 排除。

试题73:

Choose two.All MySQL Server instances belonging to InnoDB Cluster have SSL configured and 
enabled. You must configure InnoDB Cluster to use SSL for group communication. .Which two 
statements are true? 
F)Configuring SSL group communication also configures SSL distributed recovery. [错误] 
D)SSL group communication can be enabled for an existing cluster, one instance at time, by setting 
group_replication_ssl_mode. [错误] 
E)SSL group communication requires the use of an additional set of parameters 
group_replication_recovery_*. [正确] 
B)If only some InnoDB Cluster members are enabled for SSL group communication, and --ssl
mode=PREFERRED, communication will fall back to unencrypted connection. [错误] 
A)An existing InnoDB Cluster must be dissolved and created from scratch to enable SSL for group 
communication. [错误] 
C)SSL group communication must be enabled at cluster creation time by specifying createCluster 
(memberSslMode:'REQUIRED'). [正确]

解析

C) SSL group communication must be enabled at cluster creation time by specifying createCluster(memberSslMode:'REQUIRED'). [正确]
集群创建时启用 SSL：
    在 初始化集群 时，必须通过 memberSslMode:'REQUIRED' 参数强制要求 SSL 加密组通信（Group Replication）。

E) SSL group communication requires the use of an additional set of parameters group_replication_recovery_*. [正确]
需配置恢复通道的 SSL 参数

F) Configuring SSL group communication also configures SSL distributed recovery. [错误]
    组通信和恢复通道独立配置：
        组通信（group_replication_ssl_mode）和恢复通道（group_replication_recovery_*）的 SSL 需分别设置。

D) SSL group communication can be enabled for an existing cluster, one instance at a time, by setting group_replication_ssl_mode. [错误]
    现有集群无法动态启用 SSL 组通信：
        必须 解散集群并重新创建（或通过滚动重启+参数调整，但官方推荐重建）。

B) If only some InnoDB Cluster members are enabled for SSL group communication, and --ssl-mode=PREFERRED, communication will fall back to unencrypted connection. [错误]
    REQUIRED 模式下不允许降级：
        若集群配置为 memberSslMode:'REQUIRED'，任何未启用 SSL 的节点将 无法加入集群（不会回退到未加密）。

A) An existing InnoDB Cluster must be dissolved and created from scratch to enable SSL for group communication. [错误]
    不完全准确：
        虽然这是最安全的方法，但理论上可通过滚动重启+参数调整实现，但官方文档建议重建集群以确保一致性。

强制 SSL 需在集群创建时指定（C 正确），且需单独配置恢复通道的 SSL 参数（E 正确）。
常见误区：
组通信和恢复通道的 SSL 配置是独立的（F 错误）。
现有集群无法直接启用 SSL 组通信（D 错误），必须重建或复杂调整。

试题74:

After installing MySQL 8.0 on Oracle Linux 7, you initialize the data directory with the mysqld -
initialize command.Which two will assist in locating the root password? 
C)the root password inserted in the error log set by the --log-error=file_name variable  [正确] 
A)the root_pw variable stored in the mysql.install table [错误] 
B)the root password displayed on the screen via a Warning message  [正确] 
D)the root password written to the /root/.my.cnf file [错误] 
E)as root, executing the SHOW PASSWORD command by using the SHA-256 password encryption 
plugin [错误] 

解析

C) The root password inserted in the error log set by the --log-error=file_name variable. [正确]
错误日志中记录密码

B) The root password displayed on the screen via a Warning message. [正确]
    控制台警告信息显示密码：
        如果直接运行 mysqld --initialize（非后台模式），临时密码会以 Warning 消息 的形式输出到终端（但容易被忽略）。

试题75:

Choose two.Identify two ways to significantly improve data security. 
B)Use a private network behind a firewall. [正确] 
D)Configure MySQL to have only one administrative account. [错误] 
E)Configure mysqld to use only local disks or attached disks and to have its own account in the host 
system. [正确] 
A)Configure mysqld to run as the system admin account, such as root. [错误] 
C)Configure mysqld to use only networked disks. [错误]

解析

显著提升 MySQL 数据安全性的两种有效方法 
B) Use a private network behind a firewall. [正确]
    私有网络 + 防火墙隔离

E) Configure mysqld to use only local磁盘或专用外接磁盘，并为其分配独立系统账户。 [正确]

D) Configure MySQL to have only one administrative account. [错误]
    单管理员账户不现实

A) Configure mysqld to run as the system admin account, such as root. [错误]
    绝对禁止以 root 运行 MySQL：
        若 MySQL 进程被入侵，攻击者将直接获得主机 root 权限。

C) Configure mysqld to use only networked disks. [错误]
    网络存储风险高

试题76:

Which two are valid uses for binary logs on a MySQL instance? 
E)recording the order in which queries are issued [错误] 
D)point-in-time recovery [正确] 
A)logging the duration and locks for all queries [错误] 
B)replication [正确] 
C)audit of all queries [错误] 

解析

关于 MySQL 二进制日志（Binary Log） 的两个核心用途

D) Point-in-time recovery. [正确]
    时间点恢复（PITR）

B) Replication. [正确]
    主从复制

E) Recording the order in which queries are issued. [错误]
    不记录查询顺序：二进制日志记录数据变更事件，而非原始查询（如 SELECT 或无实际变更的 DDL）。

A) Logging the duration and locks for all queries. [错误]
    不记录执行时间和锁信息：此类信息由 performance_schema 或慢查询日志（slow_query_log）记录。

C) Audit of all queries. [错误]
    非审计工具：二进制日志仅记录修改数据的操作，完整审计需启用 audit_log 插件。

试题77:

Choose two.Which two are features of MySQL Enterprise Firewall? 
B) modifying SQL statement dynamically with substitutions [错误] 
A) blocking of potential threats by configuring pre- approved whitelists [正确] 
C) recording incoming SQL statement to facilitate the creation of a whitelist of permitted 
commands [正确] 
D) automatic locking of user accounts who break your firewall [错误] 
E) provides stateless firewall access to TCP/3306 [错误] 

解析

关于 MySQL Enterprise Firewall 的两个核心功能

A) Blocking of potential threats by configuring pre-approved whitelists. [正确]
基于白名单的威胁拦截

C) Recording incoming SQL statements to facilitate the creation of a whitelist of permitted commands. [正确]
    SQL语句记录与白名单生成：
        在"学习模式"（DETECTING）下，Firewall 会记录所有执行的SQL语句，帮助管理员分析并生成白名单，而无需手动编写规则。

B) Modifying SQL statements dynamically with substitutions. [错误]
    不支持SQL重写：Firewall 仅拦截或放行查询，不会修改SQL内容。

D) Automatic locking of user accounts who break your firewall. [错误]
    不锁定账户：Firewall 仅阻断违规查询，账户管理需配合 CREATE USER ... FAILED_LOGIN_ATTEMPTS 等功能实现。

E) Provides stateless firewall access to TCP/3306. [错误]
    非网络层防火墙：Enterprise Firewall 工作在 SQL协议层，而非网络端口（如TCP/3306）的访问控制。

试题78:

Choose three.Which three methods display the complete table definition of an InnoDB table?
E)SELECT * FROM table 1\G [错误]
F)SHOW CREATE TABLE [正确]
C)mysqldump --no-data schema table [正确]
A)hexdump -v -C table.frm [错误]
B)REPAIR TABLE table USE_FRM [错误]
D)Query the Information Schema. [正确]

解析

查看 InnoDB 表完整定义 的三种正确方法

F) SHOW CREATE TABLE [正确]

C) mysqldump --no-data schema table [正确]

D) Query the Information Schema. [正确]
    从元数据表中查询：
        通过 information_schema.tables 和 information_schema.columns 等表获取表的详细定义。


E) SELECT * FROM table \G [错误]
    仅显示数据，不显示表结构：
        该命令查询表中的数据（行记录），而非表定义。

A) hexdump -v -C table.frm [错误]
    InnoDB 不依赖 .frm 文件：
        MySQL 8.0+ 已移除 .frm 文件，表定义存储在数据字典中（mysql.ibd）。
        即使旧版本，直接解析二进制文件也无法直观获取定义。

B) REPAIR TABLE table USE_FRM [错误]
    用于修复表，不显示定义：
        此命令尝试通过 .frm 文件修复损坏的表，不会输出表结构。

试题79:

Which two statements are true about the mysql_config_editor program?  
F)It manages the configuration of the MySQL Firewall feature. [错误] 
C)It can move datadir to a new location. [错误] 
B)It manages the configuration of client programs. (only work for mysql client) [正确] 
G)It can be used to create and edit SSL certificates and log locations. [错误] 
E)It will use client options by default unless you provide –login-path. (mysql = mysql –login
path=client) [正确] 
D)It manages the configuration of user privileges for accessing the server. [错误] 
A)It provides an interface to change my.cnf files. [错误]

解析

关于 mysql_config_editor 程序 的两个正确说法

F) It manages the configuration of the MySQL Firewall feature. [错误]
    与防火墙无关：MySQL Enterprise Firewall 的配置需通过 SQL 命令或插件实现。

C) It can move datadir to a new location. [错误]
    不管理数据目录：datadir 的修改需手动编辑 my.cnf 并迁移数据文件。

G) It can be used to create and edit SSL certificates and log locations. [错误]
    不处理 SSL 证书或日志：SSL 证书需通过 openssl 或 MySQL 的 ALTER INSTANCE 配置。

D) It manages the configuration of user privileges for accessing the server. [错误]
    不管理用户权限：权限控制需通过 GRANT/REVOKE 语句。

A) It provides an interface to change my.cnf files. [错误]
    不修改 my.cnf：mysql_config_editor 仅维护 ~/.mylogin.cnf 文件，不影响服务端配置。

B) It manages the configuration of client programs (only work for mysql client). [正确]
    管理客户端程序的登录配置：
        mysql_config_editor 用于安全存储客户端（如 mysql、mysqldump）的连接凭据（用户名、密码、主机等），避免在命令行或脚本中明文暴露密码。

E) It will use client options by default unless you provide --login-path (mysql = mysql --login-path=client). [正确]
    默认使用 [client] 登录路径
若不指定 --login-path，客户端工具（如 mysql）会自动尝试读取 [client] 段的配置。

试题80:

Choose three.A MySQL server is monitored using MySQL Enterprise Monitor 's agentless 
installation.Which three features are available with this installation method? 
C) CPU utilization [错误] 
B) security-related advisor warnings [正确] 
E) MySQL Query Analysis data [正确] 
D) disk usage and disk characteristics including disk advisors warnings [错误] 
A) MySQL Replication monitoring [正确] 
G) network-related information and network characteristics [错误] 
F) operating system memory utilization [错误]

解析

使用 MySQL Enterprise Monitor 的无代理安装来监控 MySQL 服务器。
此安装方法提供哪三个功能？

B) Security-related advisor warnings. [正确]
    安全顾问告警：
        无代理模式下，MEM 仍可通过 SQL 接口检测安全风险（如弱密码、未加密连接、权限配置问题）并生成告警。

E) MySQL Query Analysis data. [正确]
    查询性能分析：
        通过 performance_schema 和慢查询日志收集 SQL 执行统计信息（如慢查询、高频查询），提供优化建议。

A) MySQL Replication monitoring. [正确]
    复制监控：
        监控主从复制的状态（SHOW REPLICA STATUS）、延迟（Seconds_Behind_Master）及错误，无需代理。

C) CPU utilization. [错误]
    无代理模式无法直接获取主机 CPU 使用率：需代理（Agent）访问操作系统指标。

D) Disk usage and disk characteristics including disk advisors warnings. [错误]
    磁盘监控需代理：文件系统空间、IOPS 等数据需代理读取 /proc 或 sysstat。

G) Network-related information and network characteristics. [错误]
    网络指标需代理：带宽、丢包率等需代理抓取系统网络接口数据。

F) Operating system memory utilization. [错误]
    内存使用率需代理：MEM 无代理模式无法直接读取 /proc/meminfo。