Oracle Certification
60 practice questions with correct answers and detailed explanations. Use this guide to review concepts before taking the practice exam.
The Oracle MySQL Database Admin (1Z0-931) certification validates professional expertise in Oracle technologies. This study guide covers all 60 practice questions from our 1Z0-931 practice test, complete with correct answers and explanations to help you understand each concept thoroughly.
Review each question and explanation below, then test yourself with the full interactive practice exam to measure your readiness.
Which MySQL storage engine is optimized for read-heavy workloads and provides in-memory performance with persistence?
InnoDB is the default MySQL storage engine that offers ACID compliance, row-level locking, and excellent performance for both read and write operations while maintaining data persistence.
What is the primary advantage of using MySQL replication for a production database environment?
MySQL replication creates copies of data on replica servers, enabling load distribution for reads and providing failover capabilities for high availability scenarios.
When configuring a MySQL master-slave replication topology, which parameter must be set identically on both the master and slave servers?
The binary log format (STATEMENT, ROW, or MIXED) must be compatible between master and slave to ensure proper replication of changes. While server-id must be unique, log format should align.
Which command is used to promote a MySQL slave to become the new master in a replication topology?
To promote a slave, you must first stop replication with STOP SLAVE, then execute RESET SLAVE ALL to clear replication metadata, allowing it to function as an independent master.
What is the purpose of the mysql_upgrade utility in MySQL administration?
mysql_upgrade is a post-upgrade tool that checks system table compatibility, updates privilege tables, and ensures the new MySQL version can properly read existing data structures.
In a MySQL backup scenario, what is the key limitation of using mysqldump for large databases?
mysqldump can lock tables during backup unless the --single-transaction option is used for InnoDB, which may impact availability. This is a key limitation for large, active databases.
Which backup method provides a physical, file-level copy of the entire MySQL data directory while maintaining consistency?
Physical backups like MySQL Enterprise Backup copy actual data files while maintaining consistency, offering faster restoration and incremental backup capabilities compared to logical methods.
How does the innodb_buffer_pool_size parameter affect MySQL performance?
innodb_buffer_pool_size is the most critical InnoDB performance parameter, caching both data pages and index pages to minimize expensive disk reads. It should typically be 50-75% of available RAM.
What is the relationship between the max_connections parameter and the number of open_files_limit in MySQL?
MySQL connections require multiple file descriptors for tables, temporary files, and connections. open_files_limit should be significantly higher than max_connections to accommodate additional overhead.
Which of the following is the correct approach to safely modify the innodb_buffer_pool_size parameter on a running MySQL production server?
MySQL 5.7 and later support dynamic innodb_buffer_pool_size resizing, allowing adjustments without restart. For earlier versions, a restart is required to apply changes.
What does the sync_binlog parameter control in MySQL?
sync_binlog=1 ensures that every transaction is synced to disk before being acknowledged, providing crash safety. Setting it to 0 improves performance but risks data loss.
In MySQL, what is the purpose of the relay log in a replication slave?
The relay log on a slave stores events retrieved from the master's binary log via the IO thread, allowing the SQL thread to apply changes asynchronously and independently.
Which monitoring metric is most critical for identifying InnoDB table lock contention issues?
Innodb_row_lock_waits counts lock waits due to row conflicts. Increasing values indicate lock contention. SHOW ENGINE INNODB STATUS provides detailed lock wait information for analysis.
How should you handle a scenario where a MySQL replication slave has fallen significantly behind the master due to slow query execution?
Replication lag should be addressed by identifying bottlenecks (slow queries, inadequate resources) and optimizing the slave. MySQL 5.7+ supports parallel replication with slave_parallel_workers.
Which statement correctly describes the impact of using READ COMMITTED isolation level in InnoDB?
READ COMMITTED isolation level prevents dirty reads but allows unrepeatable and phantom reads. It uses less locking than higher isolation levels, improving concurrency at the cost of less consistency.
What is the significance of the auto_increment_increment and auto_increment_offset parameters in a replication environment?
In multi-master replication, these parameters ensure unique auto_increment values across masters by distributing ID ranges. For example, master1 generates 1,3,5... and master2 generates 2,4,6...
Which privilege is required for a user to execute SHOW ENGINE INNODB STATUS command?
The PROCESS privilege allows users to view all threads and execute SHOW ENGINE INNODB STATUS. This privilege is essential for monitoring and troubleshooting database performance.
In MySQL, what does the innodb_autoinc_lock_mode parameter control?
innodb_autoinc_lock_mode (0=traditional, 1=consecutive, 2=interleaved) determines lock behavior during INSERT operations. Mode 2 provides best performance but may cause gaps in IDs.
Which of the following best describes the purpose of the slow query log in MySQL?
The slow query log records queries exceeding long_query_time, helping DBAs identify resource-intensive queries for optimization. Queries without index usage can be logged with log_queries_not_using_indexes.
What is the recommended approach for performing a point-in-time recovery (PITR) in MySQL?
PITR requires restoring a full backup (from before the failure), then using binary logs with mysqlbinlog to replay events up to the desired timestamp, achieving precise recovery.
How does the binlog_format=ROW setting differ from binlog_format=STATEMENT in terms of replication implications?
ROW format logs actual row changes, ensuring consistency but using more space. STATEMENT logs SQL statements, risking issues with NOW(), RAND(), etc. Mixed format combines both approaches.
Which statement accurately describes the relationship between the general query log and performance impact?
The general query log logs all queries, causing substantial performance overhead. It should only be enabled temporarily for specific troubleshooting, not in normal production use.
In the context of MySQL user management, what is the primary security difference between host wildcards (%) and specific IP addresses?
Using specific IP addresses instead of % (wildcard) follows the principle of least privilege, restricting database access only to necessary hosts and reducing unauthorized access risk.
What is the impact of setting innodb_flush_log_at_trx_commit = 2 on data safety and performance?
Mode 2 flushes logs to OS cache at commit, providing good performance. However, if the OS crashes before flushing cache to disk, committed transactions may be lost. Mode 1 is safest.
Which command combination is necessary to safely backup a running MySQL database while ensuring consistent snapshots for InnoDB tables?
mysqldump with --single-transaction creates a consistent snapshot using REPEATABLE READ isolation without locking tables. The --master-data option records binary log position for PITR.
What is the purpose of the CHANGE MASTER TO command in MySQL replication setup?
CHANGE MASTER TO on a slave configures the master's hostname, port, credentials, binary log file, and position, establishing the replication connection parameters without immediately starting replication.
Which MySQL storage engine is the default and provides ACID compliance?
InnoDB has been the default storage engine in MySQL since version 5.5 and provides full ACID (Atomicity, Consistency, Isolation, Durability) transaction support.
What is the maximum size of a single table in MySQL when using InnoDB storage engine with default settings?
InnoDB tables can grow up to the limits imposed by the underlying file system, as each table is stored in separate files when using file-per-table settings.
Which command is used to display the current user and host connection information in MySQL?
Both SELECT USER() and SELECT CURRENT_USER() work, but USER() is the most commonly used function to display the current connected user and host.
What is the primary purpose of the mysql_secure_installation script?
The mysql_secure_installation script helps secure a MySQL installation by removing anonymous users, disabling remote root login, removing test databases, and setting a root password.
In MySQL, which of the following best describes the purpose of the innodb_buffer_pool_size parameter?
innodb_buffer_pool_size is one of the most important InnoDB configuration parameters; it determines how much memory InnoDB uses to cache frequently accessed data and indexes.
Which privilege level in MySQL allows a user to perform operations on all databases?
Global privileges are the highest privilege level and grant permissions on all databases and tables in the MySQL server when assigned using the . syntax.
What does the FLUSH PRIVILEGES command do in MySQL?
FLUSH PRIVILEGES reloads the privilege tables from disk, which is essential after directly modifying user permissions in the mysql database without using GRANT/REVOKE commands.
Which MySQL replication topology involves one master server replicating to multiple slave servers?
One-to-many (or master-to-slave) replication is the most common topology where a single master server replicates data to one or more slave servers.
What is the primary purpose of the MySQL binary log (binlog)?
The binary log records all modifications to database data (INSERT, UPDATE, DELETE) and is essential for both replication and point-in-time recovery operations.
When performing a full backup using mysqldump with the --all-databases option, which of the following is NOT typically backed up?
mysqldump backs up database objects but not server configuration files (my.cnf); configuration parameters must be backed up separately by copying the configuration files.
Which of the following describes the primary difference between a logical backup and a physical backup in MySQL?
Logical backups (like mysqldump output) contain SQL statements that recreate data, while physical backups copy the actual database files directly from disk.
What is the significance of the Master_Log_File and Read_Master_Log_Pos values in SHOW SLAVE STATUS output?
Master_Log_File and Read_Master_Log_Pos indicate the binary log file and position that the slave I/O thread has read from the master, not necessarily what has been applied yet.
In MySQL, which system variable controls the maximum allowed packet size for communication between client and server?
The max_allowed_packet variable sets the maximum packet size for both client-to-server and server-to-client communication, and must match between server and clients.
What is the primary advantage of using partitioning on a large MySQL table?
Table partitioning distributes data across multiple physical files based on a partition key and enables partition pruning, where the optimizer eliminates unnecessary partitions from query execution.
Which of the following is NOT a valid partitioning type in MySQL?
MySQL supports RANGE, HASH, KEY, and LIST partitioning types; SEQUENTIAL partitioning does not exist in MySQL partitioning mechanisms.
What does the innodb_flush_log_at_trx_commit parameter control?
innodb_flush_log_at_trx_commit determines when the InnoDB redo log is flushed to disk: value 0 flushes every second, 1 flushes at each commit, and 2 flushes every second but not on commit.
Which MySQL user account is created by default and typically has unrestricted privileges on the server?
The root user is the default administrative account created during MySQL installation with full privileges on all databases; this account should be secured with a strong password.
What is the purpose of the slow query log in MySQL?
The slow query log records queries that exceed the time specified in long_query_time, helping administrators identify and optimize inefficient queries.
In MySQL replication, what does the Relay_Log_Pos value in SHOW SLAVE STATUS represent?
Relay_Log_Pos shows how far the SQL thread has progressed in executing the relay log, indicating which events have been applied to the slave database.
Which command is used to create a new user account in MySQL with specific database privileges?
While CREATE USER followed by GRANT is the modern approach, GRANT can also implicitly create a user in some MySQL configurations, making both methods valid.
What is the primary function of the innodb_autoinc_lock_mode parameter?
innodb_autoinc_lock_mode controls how InnoDB locks and generates AUTO_INCREMENT values: 0 (traditional), 1 (consecutive), or 2 (interleaved) modes.
Which of the following best describes the purpose of the MySQL GTID (Global Transaction ID)?
GTID provides a unique identifier for each transaction across the entire replication topology, simplifying replication management and enabling safe failover scenarios.
In MySQL, which of the following statements about the information_schema database is correct?
information_schema is a virtual database that provides real-time metadata about database objects; it doesn't store persistent data and is generated dynamically.
What is the main difference between using WHERE clause filtering versus partition pruning for query optimization?
Partition pruning is a query optimization technique where the optimizer determines which partitions contain relevant data and eliminates others before execution, while WHERE filtering processes rows after retrieval.
You are tasked with improving query performance on a large table with millions of rows. After analyzing slow query logs, you notice that a frequently executed query is performing a full table scan. What is the MOST appropriate first step to address this issue?
Creating appropriate indexes on columns used in WHERE clauses and JOINs is the fundamental technique to avoid full table scans. While buffer pool size and query caching can help, they do not address the root cause of inefficient query execution plans.
A MySQL instance is running with the default storage engine. You need to ensure ACID compliance and support for foreign keys. Which storage engine should you verify is being used for your tables?
InnoDB is the only standard MySQL storage engine that provides ACID compliance and enforces foreign key constraints. MyISAM does not support transactions or foreign keys, while Memory and CSV have other significant limitations.
You are implementing a backup strategy for a business-critical MySQL database. The requirements state that recovery point objective (RPO) must be less than 5 minutes. Which backup approach would BEST meet this requirement while maintaining reasonable backup storage costs?
Combining daily full backups with binary log archival allows recovery to any point within the last 5 minutes (or longer depending on log retention), meeting the RPO requirement cost-effectively. Weekly backups cannot meet a 5-minute RPO, hourly full backups are expensive, and disabling binary logs eliminates point-in-time recovery.
During a security audit, you discover that user accounts are created with overly permissive global privileges. You need to restrict a user named 'webapp' to only SELECT and INSERT operations on the 'sales' database. What is the correct approach?
The correct approach is to first REVOKE all global privileges, then GRANT only the specific privileges needed on the specific database. Option B reverses the order, which is less effective, while options C and D are either unnecessary or incomplete.
A production database experiences unpredictable latency spikes every few hours. Monitoring shows that InnoDB buffer pool usage remains high and stable. Which diagnostic step would MOST likely identify the root cause?
The slow query log directly captures queries executing slower than the configured threshold, making it ideal for correlating performance spikes with specific queries. While PROCESSLIST and buffer pool analysis can be useful, the slow query log provides the most direct evidence of problematic query patterns during spikes.
You are implementing replication for a large MySQL database with multiple terabytes of data. The source server experiences significant load during binary log creation. Which replication feature could reduce the I/O impact on the source?
Setting binlog_row_image=MINIMAL reduces the amount of data written to binary logs by only recording columns that changed, decreasing I/O on the source. Parallel replication affects replica apply speed, not source load; read_only is a safety measure; and binlog_cache_size buffers in memory but doesn't reduce actual disk writes.
A DBA needs to maintain the data dictionary while performing maintenance on a production MySQL 8.0 instance. Which statement accurately describes the characteristics of the MySQL data dictionary?
In MySQL 8.0+, the data dictionary is stored in the InnoDB system tablespace and is automatically maintained as changes occur. It is not in MyISAM, does not require manual rebuild commands, and is definitely modifiable when schema changes occur.
You are analyzing a database with mixed transaction sizes and notice that small, frequent transactions are performing poorly due to lock contention. Which MySQL feature is specifically designed to reduce lock contention for such workloads?
InnoDB intrinsic locks are optimized for scenarios with high lock contention by using efficient internal locking mechanisms. SERIALIZABLE increases contention, changing timeout settings doesn't reduce contention, and READ UNCOMMITTED creates consistency problems rather than solving performance issues.
During a capacity planning meeting, the team discusses implementing automated backups. One proposal suggests using mysqldump with the --single-transaction option. What is the primary advantage of this option for InnoDB tables?
The --single-transaction option leverages InnoDB's Multi-Version Concurrency Control (MVCC) to create a consistent backup without acquiring locks, allowing normal operations to continue. It does not compress data, does not enable parallel backup natively, and specifically avoids blocking writes.
A MySQL database administrator suspects that connections are not being properly cleaned up, leading to resource exhaustion. The administrator needs to identify idle connections consuming resources. Which command or approach would be MOST appropriate for this diagnostic task?
SHOW PROCESSLIST displays all connections with their status and idle time; connections showing 'Sleep' command are idle. Option B would kill active long-running queries; option C is inefficient; option D doesn't automatically terminate idle connections without additional configuration like the interactive_timeout variable.
You've reviewed all 60 questions. Take the interactive practice exam to simulate the real test environment.
▶ Start Practice Exam — Free