OCP MySQL 8.0 Database Administrator Free Sample Questions

Description

Covers MySQL server architecture and InnoDB storage, installation and client configuration, user accounts and authentication plug-ins, log monitoring, query optimization, backups, and replication.

20 free sample questions206 in the full practice test Other version: 1z0-888(152)

Try simulator

1Z0-908 Sample Questions

  1. Question 1

    AdvancedMultiple answers

    Security · Encrypt MySQL data

    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?

    Answer and explanation

    Correct answers: A, B, C

    This setting ensures that any newly created schemas and tables within them inherit the encryption attribute, enforcing the policy for new objects.

    A keyring plugin is a prerequisite for MySQL data-at-rest encryption. It manages the master encryption key, without which encryption cannot be enabled.

    The mandate requires all data at rest to be encrypted. This includes binary logs (replication data) and redo logs (transactional recovery data), which are encrypted by these specific settings.

  2. Question 2

    Intermediate

    High Availability Techniques · Describe MySQL InnoDB cluster and Group Replication

    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?

    Answer and explanation

    Correct answer: C

    The Group Replication plugin, which underpins InnoDB Cluster, is responsible for managing group membership, detecting failures, and running the election process to automatically promote a new primary node when the current one fails. This is a core feature of its distributed consensus mechanism.

  3. Question 3

    Intermediate

    Query Optimization · Examine how MySQL optimizes queries

    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?

    Answer and explanation

    Correct answer: A

    The FORCE INDEX hint is the most direct way to instruct the MySQL optimizer to use a specific index if it is at all possible. It is stronger than USE INDEX, as it tells the optimizer to consider a full table scan as very expensive, thus heavily favoring the specified index.

  4. Question 4

    Intermediate

    Backups and Recovery · Use mysqldump and mysqlpump to perform logical backups

    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?

    Answer and explanation

    Correct answer: B

    When autocommit is enabled (the default), each INSERT statement in the dump file is treated as a separate transaction, causing a log flush to disk for every single row. This creates massive I/O overhead. Disabling autocommit and wrapping the data load in a single transaction dramatically improves performance.

  5. Question 5

    Intermediate

    Security · Create user accounts and roles

    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?

    Answer and explanation

    Correct answer: B

    In MySQL 8.0, granted roles are not automatically activated upon login unless they are set as a default role for that user (SET DEFAULT ROLE ...). If it's not a default role, the user must manually activate it in their session using SET ROLE 'dev_write'; to gain its privileges.

  6. Question 6

    Intermediate

    Architecture · Understand how InnoDB stores data and logs

    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?

    Answer and explanation

    Correct answer: C

    The doublewrite buffer's purpose is for crash safety. InnoDB first writes pages to the doublewrite buffer and then to their final location in the data files. If the server crashes during the second write (a torn page scenario), InnoDB can recover the correct page from the doublewrite buffer during recovery.

  7. Question 7

    Beginner

    Server Installation and Configuration · Install and use the MySQL server and client programs

    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?

    Answer and explanation

    Correct answer: B

    The --initialize operation is designed to create the system tables and initialize a new MySQL instance. It requires a completely empty data directory to prevent overwriting an existing installation. The correct action is to remove all files and subdirectories from the data directory before trying again.

  8. Question 8

    Intermediate

    Server Installation and Configuration · Configure MySQL variables

    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.

    Answer and explanation

    Correct answer: B

    The statement is false. innodb_buffer_pool_size is a static variable, meaning it can only be set at server startup. While SET PERSIST will correctly write the setting to the mysqld-auto.cnf file to be used on the next restart, it cannot dynamically change the size of the running buffer pool. A restart is required for the change to take effect.

  9. Question 9

    Intermediate

    High Availability Techniques · Explain the role of replication threads

    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?

    Answer and explanation

    Correct answer: B

    The problem describes a bottleneck at the SQL thread, which by default is single-threaded. Setting replica_parallel_workers (or slave_parallel_workers) to a value greater than 1 enables multi-threaded replication, allowing the replica to apply transactions in parallel, which is ideal for a high-concurrency source workload.

  10. Question 10

    Beginner

    Monitoring and Maintenance · Configure and view MySQL log files

    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';

    Answer and explanation

    Correct answer: B

    Query_time represents the total wall-clock time the query took to execute, measured in seconds. This includes all phases of query processing, from parsing to sending the final result set to the client.

Register free for 10 more questions

Or unlock all 206 1Z0-908 questions with explanations, timed mode and flashcards.