OCP MySQL 8.0 Database Administrator Free Sample Questions

A financial services company is deploying a new MySQL 8.0 instance that will store sensitive client data. A security audit mandates that all data, including temporary data created by complex queries and data being replicated, must be encrypted at rest. Which configuration settings are required to meet this strict mandate?

A database administrator is tasked with setting up a new three-node InnoDB Cluster in single-primary mode. The goal is to ensure that if the primary node fails, one of the secondary nodes is automatically promoted to primary. Which component is responsible for managing this automatic failover process?

During a performance audit, you discover that a critical reporting query is performing poorly. You run EXPLAIN and notice that the optimizer is choosing a suboptimal index. You have determined that forcing the use of a specific index, idx_report_date, will significantly improve performance. What is the most effective way to instruct the optimizer to use this specific index for the query without making permanent schema changes?

A junior DBA is attempting to restore a large database from a logical backup created with mysqldump. The restore process is taking an exceptionally long time. The backup file contains both schema definitions and data, and the target tables use the InnoDB storage engine. Which of the following is the MOST likely cause of the slow restore speed?

You are managing a MySQL 8.0 server where multiple development teams share the same instance. To simplify permissions management, you have created roles such as dev_read, dev_write, and dev_dba. A developer, 'sara'@'localhost', has been granted the dev_write role. After connecting, Sara reports that she is unable to modify data. You have verified her grant with SHOW GRANTS FOR 'sara'@'localhost'; which shows GRANT 'dev_write' TO 'sara'@'localhost'. What is the most likely reason for this issue?

A database administrator is investigating high I/O wait times on a production MySQL 8.0 server. The investigation reveals that the server is performing a large number of writes to the doublewrite buffer. What is the primary purpose of the doublewrite buffer in InnoDB?

You are trying to install a fresh MySQL 8.0 server on a new Linux machine, but the server fails to start. Upon examining the error log, you see the message: [ERROR] [MY-010457] [Server] --initialize specified but the data directory has files in it. Aborting. What is the correct action to resolve this issue and complete the installation?

True or False: In MySQL 8.0, using SET PERSIST innodb_buffer_pool_size = 16G; will immediately resize the buffer pool to 16GB and ensure the setting is retained after a server restart.

A database is experiencing severe replication lag. SHOW REPLICA STATUS indicates that the I/O thread is running far ahead of the SQL thread. The source server has a high-concurrency workload with many small transactions. The replica server has sufficient CPU and I/O capacity. Which configuration change on the replica is most likely to reduce the SQL thread lag?

You are analyzing the slow query log and find numerous queries that are not using indexes. The log entry for one such query is shown below. What does the value Query_time: 2.153608 represent?

# Time: 2023-10-27T10:30:05.123456Z
# User@Host: webapp[webapp] @ localhost []
# Thread_id: 42 Schema: sales QC_Hit: No
# Query_time: 2.153608 Lock_time: 0.000120 Rows_sent: 500 Rows_examined: 8504321
SET timestamp=1698399005;
SELECT * FROM transactions WHERE status='pending';