MySQL 8.0 OCP 英文題庫解析（十八）

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

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

微信圖片_20250507171214.png

本期公布試題161~170

試題161:

The languages table uses MyISAM and the countries table uses the InnoDB storage engine.Both 
tables are empty.Examine these statements:BEGIN;INSERT INTO languages(lang) VALUES 
(“Italian”);INSERT INTO countries(country) VALUES(“Italy”);ROLLBACK;What is the content of 
both tables after executing these statements? 
A)countries has one row, languages has none. [錯誤] 
B)Both tables have one row. [錯誤] 
D)languages has one row, coutries has none.  [正確] 
C)Both tables are empty. [錯誤]

解析

languages 表使用 MyISAM 存儲引擎，countries 表使用 InnoDB 存儲引擎。兩表均為空。
檢查以下 SQL 語句：
sqlBEGIN;
INSERT INTO languages(lang) VALUES ("Italian");
INSERT INTO countries(country) VALUES ("Italy");
ROLLBACK;執行這些語句后，兩表的內容是什么？正確答案：D原因：MyISAM（languages 表）：不支持事務，INSERT 語句會立即提交，ROLLBACK 無效。結果：表中保留 Italian。InnoDB（countries 表）：支持事務，ROLLBACK 會撤銷未提交的 INSERT。結果：表中無 Italy。

試題162:

What does the slave I/0 thread do? 
B)monitors and schedules I/O calls to the subsystem for the relay logs [錯誤] 
D)acquires a lock on the binary log for reading each event to be sent to the slave [錯誤] 
A)connects to the master and requests it to send updates recorded in its binary logs [正確] 
C)reads the relay log and executes the events contained in them [錯誤]

解析


題目： slave I/O線程的作用是什么？A) 連接到主服務器并請求發送其二進制日志中記錄的更新 [正確]
B) 監控并調度對中繼日志子系統的I/O調用 [錯誤]
C) 讀取中繼日志并執行其中包含的事件 [錯誤]
D) 獲取二進制日志上的鎖以讀取要發送給從服務器的每個事件 [錯誤]

試題163:

Choose the best answer. Examine these commands and results:
SHOW GRANTS FOR jane; 
GRANT USAGE ON *.* TO ‘’（見下圖）
Jane must create a temporary table named TOTALSALES in the 
SALES database.Which statement will provide Jane with the required privileges based on the principle of least privilege? D)GRANT ALL ON sales.* TO jane; [錯誤] 
A)GRANT CREATE TEMPORARY TABLES, INSERT, UPDATE, DELETE, SELECT ON sales.totalsales TO 
jane; [錯誤] 
B)GRANT CREATE TEMPORARY TABLES ON sales.* TO jane; [正確] 
C)GRANT CREATE TEMPORARY TABLES ON sales.totalsales TO jane; [錯誤]

圖片.png

解析

臨時表建表權限，選B

試題164:

Choose the best answer.Where is the default data directory located after installing MySQL using RPM on Oracle Linux 7? E)/usr/bin [錯誤] 
B)/usr/mysql [錯誤] 
D)/var/1ib/mysql [正確] 
C)/etc/my.cnf [錯誤] 
A)/usr [錯誤]

解析

D) /var/lib/mysql 是正確的默認數據目錄位置。

試題165:

Binary log events for the 'mydb1' schema must be copied to a different schema name 
'mydb2' .Which command will do this? 
A)mysqlbinlog -- rewrite-db=' mydb1- >mydb2' I mysql [正確] 
C)mysqlbinlog -- rewrite-db=' mydb1' -- rewrite-db=' mydb2' I mysql [錯誤] 
D)mysqlbinlog --read- from= remote-server --rawl sed's/mydb1 /mydb2/g' I mysql [錯誤] 
B)mysqlbinlog --datebase=mydb1 --database=mydb2 lmysql [錯誤]

解析

正確選項A解析：
--rewrite-db='oldname->newname' 是mysqlbinlog的正確參數格式，用于將二進制日志中的數據庫名從mydb1重寫為mydb2，然后通過管道(|)將結果傳遞給mysql命令執行。

試題166:

Examine this snippet from the binary log file named binlog.000036:# at 5000324#191120 14155116 
server id 1 end_log_pos 500453 crc32 0x98159515 Query thread_id=9 exec_time=2 
error_code=0xid=1106SET TIMESTAMP=1574222116/*!*/;DROP TABLE ‘rental’/* generated by 
server*//*!*/;The rental table was accidentally dropped, and you must recover the table.You have 
restored the last backup, which corresponds to the start of the binlog.000036 binary log. Which 
command will complete the recovery? 
D)mysqlbinlog --stop-position=500453 binlog.000036 mysql [錯誤] 
B)mysqlbinlog --stop-datetime='2019-11-20 14:55:16' binlog.000036 mysql [錯誤] 
C)mysqlbinlog --stop-datetime='2019-11-20 14:55:18' binlog.000036 mysql [錯誤] 
A)mysqlbinlog --stop-position=500324 binlog.000036 mysql [正確]

解析

rental表被意外刪除，你必須恢復該表。你已經恢復了最后一個備份，該備份對應于binlog.000036二進制日志的開頭。哪個命令能完成恢復？A) mysqlbinlog --stop-position=500324 binlog.000036 | mysql 是正確的命令。
解析這道題考察的是如何使用mysqlbinlog進行時間點恢復(PITR)。關鍵點分析：日志顯示DROP TABLE事件開始于position 5000324，結束于500453要恢復rental表，需要應用binlog中在DROP TABLE之前的操作應該停止在DROP TABLE開始的位置(5000324)，而不是結束位置(500453)

試題167:

Choose the best answer.You recently upgraded your MySQL installation to MySQL 8.0. Examine this 
client error:ERROR 2059 (HY000):Authentication plugin 'caching_sha2_password' cannot be 
loaded:/usr/local/mysql/lib/plugin/caching_sha2_password.so:cannot open shared object file:No 
such file or directoryWhich option will allow this client to connect to MySQL Server? 
B)mysqld default_authentication_plugin=sha256_password [錯誤] 
A)ALTER USER user IDENTIFIED WITH caching_sha2_password BY 'password'; [錯誤] 
D)ALTER USER user IDENTIFIED WITH mysql_native_password BY 'password' ; [正確] 
F)mysqld default_authentication_plugin=mysql_native_password [錯誤] 
C)mysqld default_authentication_plugin=caching_sha2_password [錯誤] 
E)ALTER USER user IDENTIFIED WITH sha256_password BY 'password' ; [錯誤]

解析

MySQL 8.0默認使用caching_sha2_password認證插件，但某些客戶端可能不支持或缺少相關庫文件，導致連接失敗。正確選項D解析：
將用戶認證方式改為舊的mysql_native_password插件可以解決此問題，因為：mysql_native_password是MySQL 5.7及之前版本的默認插件所有MySQL客戶端都支持這個插件不需要額外的共享庫文件

試題168:


Choose the best answer.Examine this SQL statement:UPDATE world.city SET Population = Population 
* 1.1 WHERE CountryCode IN (SELECT Code FROM world.country WHERE Continent = ' Asia ' )Which 
set of privileges will allow Tom to execute this SQL statement? 
C) GRANT UPDATE ON `world`.`city` TO `tom`@`%`； GRANT SELECT ON `world`.`country` TO 
`tom`@`%` [正確] 
A) GRANT UPDATE ON `world`.* TO `tom`@`%`; GRANT ALL PRIVILEGES ON `world`.`country` TO 
`tom`@`%`; [錯誤] 
B) GRANT UPDATE ON `world`.`city` TO `tom`@`%`； GRANT SELECT ON `world`.* TO `tom`@`%` [錯
誤] 
D) GRANT ALL PRIVILEGES ON `world`.`city` TO `tom`@`%`； GRANT SELECT (`code`) ON 
`world`.`country` TO `tom`@`%` [錯誤]

解析

正確答案C) 
GRANT UPDATE ON world.city TO tom@%; 
GRANT SELECT ON world.country TO tom@%; 
是正確的權限組合。這道題考察的是MySQL權限管理，特別是執行UPDATE語句涉及子查詢時所需的權限。SQL語句分析：主語句：UPDATE world.city - 需要city表的UPDATE權限子查詢：SELECT Code FROM world.country - 需要country表的SELECT權限正確選項C解析：授予city表的UPDATE權限允許修改數據授予country表的SELECT權限允許執行子查詢這是最小權限原則的完美體現錯誤選項分析：A) 授予world.*的UPDATE權限范圍過大，且country表的ALL PRIVILEGES權限過大B) 授予world.*的SELECT權限范圍過大D) 列級權限(SELECT(code))不適用于此場景，且ALL PRIVILEGES權限過大

試題169:

Choose the best answer.Which command enables rule-based MySQL Auditing capabilities? 
D)shell> mysql < audit_log_filter_linux_install.sql. [正確] 
B)mysql> INSTALL COMPONENT audit_log; [錯誤] 
A)shell> mysqld --initialize --log-raw=audit.log [錯誤] 
C)mysql> INSTALL PLUGIN audit_log; [錯誤]

解析

題目： 哪個命令可以啟用基于規則的MySQL審計功能？
選項翻譯A) shell> mysqld --initialize --log-raw=audit.log [錯誤]
B) mysql> INSTALL COMPONENT audit_log; [錯誤]
C) mysql> INSTALL PLUGIN audit_log; [錯誤]
D) shell> mysql < audit_log_filter_linux_install.sql [正確]正確答案
D) shell> mysql < audit_log_filter_linux_install.sql 是正確的命令。
解析這道題考察的是MySQL企業版審計功能的安裝和啟用方式。正確選項D解析：
在MySQL企業版中，基于規則的審計功能需要通過執行特定的SQL腳本來安裝：audit_log_filter_linux_install.sql 腳本包含安裝審計功能所需的所有SQL語句通過命令行執行該腳本可以正確安裝和配置審計功能這是MySQL企業版審計功能的官方安裝方法

試題170:

Choose the best answer.You are having performance issues with MySQL instances. Those servers 
are monitored with MySQL Enterprise Monitor.Using Query Analyzer, where do you begin to look 
for problem queries? 
A)Sort the \Exec\ column and check for SQL queries with low Query Response Time index (QRTi) 
values. [正確] 
C)Sort the \Exec\ column and check for SQL queries with high Query Response Time index (QRTi) 
values. [錯誤] 
B)Look for queries with low total latency times in the Latency section in the times series graph. [錯誤] 
D)Look for queries with big prolonged spikes in row activity/access graph in the times series graph. 
[錯誤]

解析

題目： 你的MySQL實例遇到性能問題。這些服務器使用MySQL企業監控器進行監控。使用查詢分析器(Query Analyzer)時，你應該從哪里開始查找問題查詢？
選項翻譯A) 按"Exec"列排序，檢查查詢響應時間指數(QRTi)值低的SQL查詢 [正確]
B) 在時間序列圖的延遲部分查找總延遲時間低的查詢 [錯誤]
C) 按"Exec"列排序，檢查查詢響應時間指數(QRTi)值高的SQL查詢 [錯誤]
D) 在時間序列圖中查找行活動/訪問圖中出現長時間大幅波動的查詢 [錯誤]
正確答案A) 按"Exec"列排序，檢查查詢響應時間指數(QRTi)值低的SQL查詢 是正確的做法。
解析這道題考察的是使用MySQL Enterprise Monitor的Query Analyzer進行性能問題診斷的方法。正確選項A解析：QRTi(Query Response Time index)是衡量查詢性能的關鍵指標QRTi值低表示查詢性能差(與基準相比)按執行次數(Exec)排序可以快速定位高頻低效查詢這是性能調優的標準做法：先解決高頻低效查詢