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但不強制，失敗則回退到非加密連接風險：可能降級到不安全連接