Oracle 為慶祝 MySQL 30 周年，截止到 2025.07.31 之前。所有人均可以免費考取原價245美元的MySQL OCP 認證。

從今天開始，將英文題庫免費公布出來，并進行解析，幫助大家在一個月之內輕松通過OCP認證。

本期公布試題16~25

試題16:

choose two.Examine the modified output:mysql> 
SHOW SLAVE STATUS\G
******************1. row******************** 
Slave_IO_Running:Yes
Slave_SQL_Running:Yes 
Seconds_Behind_Master:1612 
Seconds_Behind_Master value is steadily growing. What are two possible causes? B)This value shows only I/O latency and is not indicative of the size of the transaction queue. [錯誤] A)The master is producing a large volume of events in parallel but the slave is processing them serially. [正確] C)One or more large tables do not have primary keys. [錯誤] E)The parallel slave threads are experiencing lock contention. [錯誤] D)The master is most probably too busy to transmit data and the slave needs to wait for more data. [正確]

解析

本題考查主從問題，從題目中可以看到IO線程和SQL線程均正常，延遲有1612秒，題目問造成主從延遲持續穩定增長的兩種可能的原因是什么？

B)This value shows only I/O latency and is not indicative of the size of the transaction queue.  [錯誤] 
該值僅顯示 I/O 延遲，并不表示事務隊列的大小。
該指標反映的是SQL線程與I/O線程的整體延遲，不僅是I/O延遲A)The master is producing a large volume of events in parallel but the slave is processing them serially. [正確] 
主服務器正在并行生成大量事件，但從服務器正在串行處理它們。這是造成延遲的一個原因之一，正確。C)One or more large tables do not have primary keys. [錯誤]
一個或多個大型表沒有主鍵。無主鍵表會導致復制效率降低，但不會直接表現為延遲持續增長E)The parallel slave threads are experiencing lock contention. [錯誤] 
并行從屬線程遇到鎖爭用。并行復制線程鎖競爭會導致延遲波動，而非穩定增長D)The master is most probably too busy to transmit data and the slave needs to wait for more data. [正確]
主服務器很可能太忙而無法傳輸數據，而從服務器需要等待更多數據。選項正確原因：主庫網絡帶寬不足或負載過高時，Binlog傳輸速度會變慢從庫I/O線程無法及時獲取新事件，導致SQL線程空閑等待表現為Slave_IO_Running: Yes但延遲持續增加對于選項A的情況，可考慮啟用從庫的并行復制功能（slave_parallel_workers）
對于選項D的情況，需檢查主庫網絡狀況和binlog_dump線程狀態

試題17:

Choose two.Which two are true about binary logs used in asynchronous replication? 
A)The master connects to the slave and initiates log transfer. [錯誤] 
B)They contain events that describe all queries run on the master. [錯誤] 
D)They are pulled from the master to the slave. [正確] 
C)They contain events that describe database changes on the master. [正確] 
E)They contain events that describe only administrative commands run on the master. [錯誤] 

解析

關于異步復制中使用的二進制日志，哪兩個是正確的？

A)The master connects to the slave and initiates log transfer. [錯誤] 
主服務器連接到從服務器并啟動日志傳輸。
錯誤 - 主庫不會主動連接從庫，復制方向完全由從庫發起B)They contain events that describe all queries run on the master. [錯誤] 
它們包含描述在 master 上運行的所有查詢的事件。
錯誤 - 二進制日志不記錄所有查詢（如SELECT/show等只讀操作不會被記錄）D)They are pulled from the master to the slave. [正確] 
它們從 master 拉到 slave。C)They contain events that describe database changes on the master. [正確] 
它們包含描述主服務器上的數據庫更改的事件。
二進制日志記錄的是"數據變更事件"（如DML語句、表結構變更等），而非原始SQL語句
以"事件"形式記錄能保證跨版本兼容性和確定性執行
注意：binlog_format=ROW時記錄行變更，STATEMENT時記錄原始SQL（但仍是事件形式封裝）E)They contain events that describe only administrative commands run on the master. [錯誤] 它們包含僅描述在主服務器上運行的管理命令的事件
不僅記錄管理命令，所有數據變更（INSERT/UPDATE等）都會記錄

試題18:

You have appropriate privileges and are about to shut down a running MySQL server process on Oracle Linux 7.Which three are valid methods that will shut down the MySQL server? E)mysqld_safe --shutdown [錯誤] 
A)mysqld_safe -S /tmp/mysql.sock SHUTDOWN [錯誤] 
B)kill mysqld_safe [錯誤] 
F)systemctl stop mysqld  [正確] 
G)mysql> SHUTDOWN; [正確] 
D)mysql -S /tmp/mysql.sock --shutdown [錯誤] 
C)mysqladmin shutdown  [正確]

解析

您具有適當的權限，并且即將關閉 Oracle Linux 7 上正在運行的 MySQL 服務器進程。哪三種方法是將關閉 MySQL 服務器的有效方法？

E)mysqld_safe --shutdown [錯誤] 
mysqld_safe不支持--shutdown參數（這是早期版本的誤傳）A)mysqld_safe -S /tmp/mysql.sock SHUTDOWN [錯誤] 
mysqld_safe沒有-S參數，也不接受SHUTDOWN指令B)kill mysqld_safe [錯誤] 
直接kill mysqld_safe進程可能導致非正常關閉F)systemctl stop mysqld  [正確] G)mysql> SHUTDOWN; [正確] D)mysql -S /tmp/mysql.sock --shutdown [錯誤] 
mysql客戶端沒有--shutdown參數C)mysqladmin shutdown  [正確]
MySQL官方提供的管理工具

試題19:

Choose two.Examine this MySQL Shell command:dba.rebootClusterFromCompleteOutage () Which two statements are true? E)It reconfigures InnoDB Cluster if the cluster was stopped. [錯誤] 
D)It performs InnoDB Cluster instances rolling restart. [正確] 
A)It stops and restarts all InnoDB Cluster instances and initializes the metadata. [錯誤] 
F)It picks the minimum number of instances necessary to rebuild the quorum and reconfigures 
InnoDB Cluster. [錯誤] 
C)It is not mandatory that all instances are running and reachable before running the command. [正確] 
B)It only stops and restarts all InnoDB Cluster instances. [錯誤] 
G)It only starts all InnoDB Cluster instances. [錯誤] 

解析

本題考查mysql shell的使用，dba.rebootClusterFromCompleteOutage() 是 MySQL Shell 中用于 從完全宕機狀態恢復 InnoDB Cluster 的關鍵命令，主要解決集群因意外故障導致所有節點不可用后的恢復問題。

正確選項：
D) 執行InnoDB Cluster實例的滾動重啟
C) 運行該命令前不要求所有實例都必須在線且可訪問E)It reconfigures InnoDB Cluster if the cluster was stopped. [錯誤] 
如果集群已停止，它會重新配置 InnoDB Cluster
錯誤：描述不準確，該命令專為完全宕機場景設計D)It performs InnoDB Cluster instances rolling restart. [正確] 
它執行 InnoDB Cluster 實例滾動重啟A)It stops and restarts all InnoDB Cluster instances and initializes the metadata. [錯誤] 
它會停止并重新啟動所有 InnoDB Cluster 實例并初始化元數據。
錯誤，不會顯式停止實例，而是針對已停止的集群進行恢復F)It picks the minimum number of instances necessary to rebuild the quorum and reconfigures InnoDB Cluster. [錯誤] 
它選擇重建 quorum 所需的最小實例數并重新配置 InnoDB Cluster。
錯誤：不是選擇"最少實例"，而是基于現存實例重建仲裁C)It is not mandatory that all instances are running and reachable before running the command. [正確] 
在運行命令之前，并非所有實例都正在運行且可訪問。B)It only stops and restarts all InnoDB Cluster instances. [錯誤] 
它僅停止并重新啟動所有 InnoDB Cluster 實例。
錯誤：并非簡單重啟，重點是重建集群元數據和拓撲G)It only starts all InnoDB Cluster instances. [錯誤] 
它僅啟動所有 InnoDB Cluster 實例
錯誤：包含元數據重建等復雜操作，非單純啟動實例

試題20:

Choose two.Examine this command and output:（見下圖）Which two options will improve the security of the MySQL instance?

D)Change the parent directory owner and group to mysql. [錯誤] 
A)Remove the world read/execute privilege from the accounting directory. [正確] 
F)Remove group read/write privileges from the private_key.pem file. [正確] 
E)Remove world read privileges from the server-cert.pem certificate file. [錯誤] 
B)Remove world read privileges from the public_key.pem file. [錯誤] 
C)Change the group ownership of the mysql directory to the mysql user group. [錯誤] 

解析

哪兩個選項將提高 MySQL 實例的安全性？

A) 移除accounting目錄的全局讀/執行權限
F) 移除private_key.pem文件的組讀寫權限D)Change the parent directory owner and group to mysql. [錯誤] 
修改父目錄屬主為mysql
父目錄已屬于mysql:mysql，無需修改（且可能影響其他服務）。A)Remove the world read/execute privilege from the accounting directory. [正確] 
目錄權限為 drwxrwxr-x 表示其他用戶（world）有讀和執行權限，可能導致未授權用戶遍歷目錄內容。F)Remove group read/write privileges from the private_key.pem file. [正確] 
private_key.pem權限為 rw-rw---- 表示同組用戶有讀寫權限，私鑰文件應嚴格限制訪問。E)Remove world read privileges from the server-cert.pem certificate file. [錯誤] 
移除server-cert.pem的全局讀權限
證書文件（權限rw-r--r--）需被客戶端讀取，限制后會導致連接失敗。B)Remove world read privileges from the public_key.pem file. [錯誤] 
移除public_key.pem的全局讀權限
公鑰本身設計為可公開分發，無需限制讀取（權限rw-r--r--已合理）。C)Change the group ownership of the mysql directory to the mysql user group. [錯誤] 
修改 mysql 目錄的組所有權為 mysql 用戶組
無需更改。

試題21:

Choose two.Which two statements are true about general tablespaces? 
A)General tablespaces support temporary tables. [錯誤] 
B)Dropping a table from a general tablespace releases the space back to the operating system. [錯誤] 
C)An existing table can be moved into a general tablespace. [正確] 
E)A new table can be created explicitly in a general tablespace. [正確] 
D)A general tablespace can have multiple data files. [錯誤] 

解析

這道題和試題13一模一樣,只是選項換了換。再來看下general 表空間的使用

A)General tablespaces support temporary tables. [錯誤] 
B)Dropping a table from a general tablespace releases the space back to the operating system. [錯誤] 
C)An existing table can be moved into a general tablespace. [正確] 
E)A new table can be created explicitly in a general tablespace. [正確] 
D)A general tablespace can have multiple data files. [錯誤] 本題考查general表空間的使用
B)Dropping a table from a general tablespace releases the space back to the operating system. [錯誤]
從通用表空間刪除表會釋放空間回操作系統
錯誤：通用表空間的空間不會自動釋放回操作系統
需要手動執行ALTER TABLESPACE … DROP DATAFILE來釋放空間D) 一個通用表空間可以有多個數據文件
錯誤：每個通用表空間只能有一個數據文件(.ibd文件)
但可以動態擴展這個數據文件的大小A) 通用表空間支持臨時表
錯誤：通用表空間不支持臨時表
臨時表只能存儲在臨時表空間或獨立表空間中E) 可以顯式地在通用表空間中創建新表
使用CREATE TABLE … TABLESPACE tablespace_name語法
例如：CREATE TABLE t1 (id INT) TABLESPACE ts1;C) 可以將現有表移動到通用表空間中
使用ALTER TABLE … TABLESPACE語法
例如：ALTER TABLE t1 TABLESPACE ts1;

試題22:

Choose three.Examine this command, which executes successfully:cluster.addInstance( ' 
<user>@<host>:<port>' , recoveryMethod:' clone ') 
Which three statements are true? D)The account used to perform this recovery needs the BACKUP_ADMIN privilege. [正確] 
E)A new instance is installed, initialized, and provisioned with data from an instance already in the 
cluster and joined to the cluster. [錯誤] 
B)InnoDB tablespaces outside the datadir are able to be cloned. [正確] 
C)A target instance must exist, then it will be provisioned with data from an instance already in the 
cluster and joined to the cluster. [正確] 
A)It is always slower than recoveryMethod:' incremental ' . [錯誤] 
F)InnoDB redo logs must not rotate for the duration of the execution; otherwise, the recovery will 
fail. [錯誤] 

解析

下是關于 cluster.addInstance() 使用 recoveryMethod: ‘clone’ 方法的三個正確選項及其解析：

D)The account used to perform this recovery needs the BACKUP_ADMIN privilege. [正確] 
執行此恢復操作的賬戶需要 BACKUP_ADMIN 權限E)A new instance is installed, initialized, and provisioned with data from an instance already in the 
cluster and joined to the cluster. [錯誤] 
新實例會被安裝、初始化并直接從集群中的實例克隆數據后加入集群
錯誤原因：clone 恢復方法 不會自動安裝 MySQL 實例，僅適用于已存在但無數據的實例。B)InnoDB tablespaces outside the datadir are able to be cloned. [正確] 
可以克隆位于 datadir 之外的 InnoDB 表空間
克隆操作會復制所有 InnoDB 表空間，包括使用 CREATE TABLESPACE 創建的通用表空間（即使不在默認數據目錄內）。
例外：臨時表空間（temporary tablespaces）不會被克隆。C)A target instance must exist, then it will be provisioned with data from an instance already in the 
cluster and joined to the cluster. [正確] 
目標實例必須已存在，隨后會從集群中的現有實例克隆數據并加入集群A)It is always slower than recoveryMethod:' incremental ' . [錯誤] 
它總是比 recoveryMethod: 'incremental' 慢
錯誤原因：克隆速度取決于數據量，增量恢復（incremental）可能更慢（需應用大量 binlog）。F)InnoDB redo logs must not rotate for the duration of the execution; otherwise, the recovery will fail. [錯誤] 
執行期間 InnoDB 重做日志不能輪轉，否則恢復會失敗
錯誤原因：克隆過程不依賴重做日志（redo log），而是直接復制數據文件，日志輪轉不影響操作。

試題23:

Choose three.Which three sets of item information are visible in the mysql system database? 
G)information about table structures [錯誤] 
F)rollback segments [錯誤] 
E)performance monitoring information [錯誤] 
C)plugins [正確] 
D)audit log events [錯誤] 
B)help topics [正確] 
A)time zone information and definitions [正確]

解析

mysql 系統數據庫中可以看到哪三組監控項信息？

G)information about table structures [錯誤] 
表結構信息
實際存儲位置：表結構（元數據）存儲在 information_schema 或 performance_schema 數據庫，而非 mysql 庫。F)rollback segments [錯誤]
回滾段信息
實際存儲位置：InnoDB 回滾段信息可通過 information_schema.innodb_trx 或 performance_schema 查看，與 mysql 庫無關。E)performance monitoring information [錯誤] 
性能監控信息
實際存儲位置：性能數據主要在 performance_schema 或 sys 數據庫，mysql 庫不存儲監控數據。C)plugins [正確] 
mysql.plugin 表存儲已安裝的插件信息（如認證插件、存儲引擎插件等）。D)audit log events [錯誤] 
審計日志事件
實際存儲位置：若啟用審計日志，通常存儲于專用文件或 audit_log 插件管理的表中，默認不在 mysql 庫。B)help topics [正確] 
mysql.help_topic 表包含 MySQL 內置的幫助文檔內容（如 SQL 語法說明）。A)time zone information and definitions [正確]
時區信息與定義 (time zone information)
mysql.time_zone* 系列表存儲時區數據（需手動加載）
關鍵表：mysql.time_zone：時區名稱mysql.time_zone_leap_second：閏秒信息mysql.time_zone_transition：時區轉換規則

試題24:

Which two situations will cause the binary log to rotate? 
A)FLUSH HOSTS executed [錯誤] 
D)SET sql_ log bin-1 executed [錯誤] 
C)max_ binloq cache size exceeded [錯誤] 
B)max binlog_size exceeded [正確] 
F)FLUSH LOGS executed [正確] 
E)SET syne binlog-l executed1 [錯誤]

解析

哪兩種情況會導致 binlog 切換？

A)FLUSH HOSTS executed [錯誤] 
執行 FLUSH HOSTS
清空主機緩存（如連接錯誤記錄），與二進制日志無關。D)SET sql_log bin-1 executed [錯誤] 
執行 SET sql_log_bin=0
臨時禁用當前會話的二進制日志記錄，不會觸發輪換。C)max_binlog cache size exceeded [錯誤] 
max_binlog_cache_size 超出限制
控制單個事務允許的最大緩存大小，超限會報錯（Multi-statement transaction required more than 'max_binlog_cache_size'），但不會輪換日志文件。B)max binlog_size exceeded [正確] 
當當前二進制日志文件大小達到 max_binlog_size 參數（默認 1GB）時，MySQL 會自動創建一個新的二進制日志文件。
注意：實際文件可能略微超過設定值，因為輪換僅在事務完成后觸發。F)FLUSH LOGS executed [正確] 
手動執行 FLUSH LOGS 會強制關閉當前二進制日志文件并立即創建一個新文件。E)SET sync_binlog l executed1 [錯誤]
控制制二進制日志同步到磁盤的頻率,不觸發輪換

試題25:

Choose three.Which three statements are true about MySQL replication? 
D) Any instance can have multiple slaves, but it can have only one master. [錯誤] 
G) Binary logging must be enabled on the master in order to replicate to other instances. [正確] 
E) Binary logs contain only transactions originating from a single MySQL instance. [錯誤] 
F) Replication can use only TCP/IP connections. [正確] 
C) Each instance in a replication topology must have a unique server ID. [正確] 
A) Each slave must have its own MySQL user for replication. [錯誤] 
B) A replication user must have the SELECT privilege for all tables that need to be replicated. [錯誤]

解析

關于 MySQL 復制，哪三個陳述是正確的？

D) Any instance can have multiple slaves, but it can have only one master. [錯誤]
任何實例可以有多個從庫，但只能有一個主庫
錯誤原因：MySQL 支持多主復制（如環形復制、組復制），并非只能單主。 G) Binary logging must be enabled on the master in order to replicate to other instances. [正確] 
主庫必須啟用二進制日志（Binary Log）才能復制到其他實例E) Binary logs contain only transactions originating from a single MySQL instance. [錯誤] 
二進制日志僅包含來自單個 MySQL 實例的事務
錯誤原因：二進制日志記錄所有數據變更，包括來自其他實例的中繼事件（在級聯復制中）。F) Replication can use only TCP/IP connections. [正確] 
復制僅能使用 TCP/IP 連接，MySQL 復制默認通過 TCP/IP 協議通信，不支持 Unix Socket 或其他協議。C) Each instance in a replication topology must have a unique server ID. [正確] 
復制拓撲中的每個實例必須具有唯一的 server ID
server_id 用于標識復制拓撲中的每個節點，必須全局唯一（否則導致數據混亂）。A) Each slave must have its own MySQL user for replication. [錯誤] 
每個從庫必須有自己的 MySQL 復制用戶
錯誤原因：所有從庫可共享同一個主庫的復制賬號（只需主庫授權一次）。B) A replication user must have the SELECT privilege for all tables that need to be replicated. [錯誤]
復制用戶需要對所有復制的表具有 SELECT 權限
錯誤原因：復制用戶僅需 REPLICATION SLAVE 權限，無需數據讀取權限

未完，待續。后續題庫會陸續發出，請關注。