1Z0-908 Sample Questions & Answers
Pulls together user accounts, roles and authentication plug-ins, the single biggest weight, plus log monitoring, query optimization, backup strategies, replication for high availability, client and server installation steps, and InnoDB's own storage architecture.
Launch the full 1Z0-908 simulator →Showing 10 of 20 free samples.
- Question 1Intermediate
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?
Show answer & 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.
- Question 2Beginner
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?Show answer & explanation
Correct answer: B
The
--initializeoperation 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. - Question 3Intermediate
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.Show answer & explanation
Correct answer: B
The statement is false.
innodb_buffer_pool_sizeis a static variable, meaning it can only be set at server startup. WhileSET PERSISTwill correctly write the setting to themysqld-auto.cnffile 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. - Question 4Intermediate
High Availability Techniques · Explain the role of replication threads
A database is experiencing severe replication lag.
SHOW REPLICA STATUSindicates 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?Show answer & explanation
Correct answer: B
The problem describes a bottleneck at the SQL thread, which by default is single-threaded. Setting
replica_parallel_workers(orslave_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. - Question 5Beginner
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.153608represent?# 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: 8504321SET timestamp=1698399005;SELECT * FROM transactions WHERE status='pending';Show answer & explanation
Correct answer: B
Query_timerepresents 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. - Question 6Intermediate
Backups and Recovery · Implement a backup strategy
A new DBA is creating their first backup strategy. The requirements are a Recovery Point Objective (RPO) of 15 minutes and a Recovery Time Objective (RTO) of 1 hour. The database is 500GB and moderately busy. Which combination of backup methods best satisfies these requirements?
Show answer & explanation
Correct answer: C
This is a robust strategy. A full physical backup (e.g., with MEB) provides a fast baseline for recovery (helping meet the RTO). Backing up binary logs frequently (e.g., every 15 minutes) allows for point-in-time recovery, meeting the 15-minute RPO. The combination is effective for both RPO and RTO.
- Question 7Advanced
Architecture · Understand and use the Data Dictionary
You are migrating a legacy application from MySQL 5.7 to MySQL 8.0. The application code frequently queries the
information_schema.tablesview to get row counts for MyISAM tables. After the migration, the developers report that these queries are now significantly slower. What is the reason for this performance degradation?Show answer & explanation
Correct answer: A
In MySQL 8.0, the
information_schemawas redesigned from temporary tables to views on top of the new transactional data dictionary tables. While this provides consistency, some queries, especially those that require table scans on the underlying dictionary tables (like getting row counts), can be slower than the previous file-based method. - Question 8Intermediate
Query Optimization · Create indexes to improve server performance
A DBA needs to test the impact of removing an index (
idx_old_filter) from a large production table without causing downtime or permanently dropping it. The goal is to make the index unusable by the query optimizer for all sessions to evaluate the performance impact on the application's workload. Which command should be used?Show answer & explanation
Correct answer: C
Setting an index to
INVISIBLEmakes it unavailable to the query optimizer, effectively hiding it from all queries. However, the index is still maintained in the background. This allows for safe performance testing, and the index can be madeVISIBLEagain instantly with anotherALTER TABLEcommand. - Question 9Intermediate
Monitoring and Maintenance · Configure MySQL Enterprise Audit
A government agency is setting up a MySQL 8.0 server for archival purposes. A strict security policy requires that all administrative actions, connections, and data modifications on a specific sensitive database (
archive_db) be logged for auditing. However, to save space and reduce noise, read-only queries (SELECT) should not be logged. Which MySQL Enterprise feature is best suited for this requirement?Show answer & explanation
Correct answer: C
MySQL Enterprise Audit is designed for this exact purpose. It allows for the creation of flexible audit rules and filters. You can create a rule to log connections, DDL, and DML on
archive_dbwhile specifically excluding theSELECTquery class, thereby meeting the security requirement precisely. - Question 10Beginner
Architecture · Configure buffers and caches
A new MySQL instance is being configured on a host with 128GB of RAM, dedicated solely to the database. The primary workload consists of complex analytical queries on a 100GB InnoDB dataset. Following best practices, what would be an appropriate initial setting for
innodb_buffer_pool_size?Show answer & explanation
Correct answer: C
A common and effective starting point for a dedicated server is to size the buffer pool slightly larger than the total dataset size, if memory allows. Setting it to 100GB ensures the entire dataset can be cached in memory, which is ideal for analytical queries. This leaves 28GB for the OS and other MySQL processes, which is a safe margin.
Ready for the real thing?
The full 1Z0-908 simulator has every exam-style question, timed mode, and instant scoring.