Question bank
Oracle MySQL 8.0 Database Administrator
43 Oracle MySQL 8.0 Database Administrator questions with the correct answer and a written explanation, organised across 8 exam domains.
- 43 questions
- 8 domains
- Foundation level
certilumQuestion index
Jump straight to any question. Answers and explanations are inline.
Backup and Recovery
8 questions in this domain
- Question 1Single Choice
When restoring a very large MySQL dataset, which backup type is generally faster?
- ALogical backup generated by mysqldump
- BPhysical backup of raw database filesCorrect answer
- CCSV export generated by SELECT INTO OUTFILE
- DAll backup types restore equally quickly
Why this is the answer
Physical backups copy data files directly and avoid parsing and executing SQL statements, so they generally restore faster than logical backups on large datasets.
- Question 2Multiple Select
Which statements about mysqldump --single-transaction are true?
- AIt provides a consistent snapshot for InnoDB tables.Correct answer
- BIt prevents all writes for the entire backup.
- CIt holds a global read lock until the dump completes.
- DIt allows concurrent DML operations on InnoDB tables.Correct answer
Why this is the answer
The --single-transaction option uses a consistent InnoDB snapshot and does not act as a global lock. It allows DML to continue during the logical backup.
- Question 3Interactive
A retention variable shown by the server is set to 0. What is the result for binary logs?
- ABinary logs are not automatically removed by this setting and can be retained until manually purged.Correct answer
- BBinary logs expire immediately after the next backup.
- CBinary logs will be deleted after a fixed number of transactions.
- DBinary logging is disabled on the server.
Why this is the answer
A value of 0 disables automatic expiration based on time for this setting, so binary logs are not removed solely because of this expiration setting.
- Question 4Single Choice
What must exist in addition to a logical backup to restore a database to a specific moment in time?
- ABinary logs covering the period from the backup time to the target timeCorrect answer
- BRedo logs from before the backup was taken
- CA copy of the doublewrite buffer
- DOnly the original backup file
Why this is the answer
A backup is a point-in-time snapshot. Binary logs capture subsequent changes and are required to replay transactions for point-in-time recovery.
- Question 5Multiple Select
Which statements correctly show the difference between redo logs and binary logs?
- ARedo logs support crash recovery.Correct answer
- BBinary logs support replication and point-in-time recovery.Correct answer
- CRedo logs store SQL text for logical restore.
- DBinary logs are used by the InnoDB buffer pool for crash recovery.
Why this is the answer
Redo logs are physical and used for crash recovery. Binary logs are logical and used for replication and point-in-time recovery.
- Question 6Interactive
A DBA executes this command on a running MySQL server. Why is the result not a valid hot backup?
- AThe data files may change during the copy, leaving an inconsistent backup.Correct answer
- BThe cp command cannot preserve file permissions.
- CInnoDB stores all data in the buffer pool and does not use data files.
- DMySQL automatically encrypts files, making them unreadable at the destination.
Why this is the answer
Copying a live MySQL data directory with a file-system command is unsafe because data files can be modified while being copied, resulting in an inconsistent backup.
- Question 7Single Choice
Which log is primarily used by InnoDB to recover from an instance crash?
- AThe redo logCorrect answer
- BThe binary log
- CThe buffer pool
- DThe relay log
Why this is the answer
InnoDB uses redo logs during crash recovery to restore a consistent state. Binary logs are not used for InnoDB crash recovery.
- Question 8Multiple Select
Which statements about the InnoDB doublewrite buffer are true?
- AIt reduces the risk of torn pages after a partial write.Correct answer
- BIt stores a second copy of pages before they are written to their permanent locations.Correct answer
- CIt records all user transactions for logical recovery.
- DIt is another name for the binary log.
Why this is the answer
The doublewrite buffer protects against torn data pages by staging pages before they are written to their final data file locations.
Database Design and Implementation
4 questions in this domain
- Question 9Single Choice
Which statement is generally true when comparing logical and physical backups for large MySQL databases?
- APhysical backups are faster for large datasets.Correct answer
- BLogical backups are faster for large datasets.
- CLogical restores avoid SQL parsing overhead.
- DPhysical backups require stopping MySQL first.
Why this is the answer
Logical backups use SQL statements, so restore requires parsing and execution. Physical backups copy data files directly and are usually faster for large data sets.
- Question 10Multiple Select
Which two statements correctly compare the roles of redo logs and binary logs in MySQL 8.0?
- ARedo logs handle crash recovery.Correct answer
- BBinary logs support point-in-time recovery.Correct answer
- CRedo logs restore to specific timestamps.
- DBinary logs handle crash recovery.
Why this is the answer
Redo logs are used for crash recovery after instance failures. Binary logs are used to replicate data and to perform point-in-time recovery.
- Question 11Interactive
Review the MySQL server configuration shown in the code block. What effect does this setting have on binary log files?
- ABinary logs are purged after thirty days.Correct answer
- BBinary logs are deleted after each backup.
- CRedo logs are purged after thirty days.
- DBinary logs are kept until the buffer pool fills.
Why this is the answer
The setting defines automatic binary log expiration. After 2,592,000 seconds, or 30 days, old binary log files are purged.
- Question 12Single Choice
When using mysqldump, what does the --single-transaction option do for InnoDB tables?
- AStarts a consistent snapshot without a global lock.Correct answer
- BAcquires a global lock that blocks writes.
- CLocks every table during the dump.
- DDisables binary logging while running.
Why this is the answer
--single-transaction starts one consistent transaction for InnoDB, giving a snapshot without locking the whole server. It does not block writes or disable binary logging.
Installation and Configuration
4 questions in this domain
- Question 13Single Choice
In a MySQL instance failure, what is the primary purpose of InnoDB redo logs during startup recovery?
- ACrash recoveryCorrect answer
- BPoint-in-time recovery
- CReplication
- DLogical backup
Why this is the answer
Redo logs are used by InnoDB crash recovery to restore an instance after failure. Binary logs, not redo logs, handle point-in-time recovery and replication.
- Question 14Multiple Select
Which two statements correctly describe the behavior of MySQL's `--single-transaction` option when making an InnoDB backup?
- AProvides a consistent snapshotCorrect answer
- BStops all concurrent writes
- CEquivalent to a global lock
- DDoes not block all writesCorrect answer
Why this is the answer
`--single-transaction` provides a consistent snapshot for InnoDB backups but does not stop all writes. It is not equivalent to a global lock.
- Question 15Interactive
A DBA executes the SQL statement shown in the code block. What MySQL server setting is being configured?
- ABinary log expiration in secondsCorrect answer
- BRedo log file size
- CInnoDB buffer pool size
- DSlow query log threshold
Why this is the answer
The `binlog_expire_logs_seconds` variable controls binary log retention in seconds. Setting it to 604800 retains logs for seven days.
- Question 16Single Choice
Why are binary logs needed after a logical backup if a DBA must recover the database to a specific point in time?
- ATo enable point-in-time recoveryCorrect answer
- BTo accelerate physical backup
- CTo increase redo log capacity
- DTo shrink InnoDB tablespace
Why this is the answer
A logical backup is insufficient if binary logs from the backup time onward are missing. These logs allow changes to be replayed for point-in-time recovery.
MySQL Server Architecture
5 questions in this domain
- Question 17Single Choice
Which backup type is generally slower for large datasets because it must parse and apply SQL statements?
- APhysical backup
- BLogical backupCorrect answer
- CFile-system snapshot
- DBinary log backup
Why this is the answer
Logical backups recreate database objects and data by executing SQL statements, making them slower than physical backups for large datasets.
- Question 18Multiple Select
Which statements are true about mysqldump --single-transaction during a backup?
- AProvides a consistent InnoDB snapshot during the dumpCorrect answer
- BBlocks all writes from every session
- CDoes not prevent other writes from continuingCorrect answer
- DIs equivalent to a global read lock
Why this is the answer
--single-transaction gives a consistent InnoDB snapshot, but it does not take a global lock, so writes from other sessions can continue while the dump runs.
- Question 19Interactive
When the query in the code block is executed, which binary log maintenance setting is reported?
- ASeconds before binary logs expireCorrect answer
- BDays before binary logs expire
- CMaximum binary log file size
- DNumber of retained binary logs
Why this is the answer
The binlog_expire_logs_seconds variable controls how many seconds binary log files are retained before automatic expiration.
- Question 20Single Choice
What is the primary purpose of InnoDB redo logs after an instance failure?
- APhysical recovery after an instance failureCorrect answer
- BReplication to replica servers
- CCreating logical backups
- DPoint-in-time recovery with binary logs
Why this is the answer
Redo logs are used for physical crash recovery after an instance failure. Binary logs, not redo logs, support replication and point-in-time recovery.
- Question 21Multiple Select
Which statements are correct about recovery-related components in InnoDB?
- ARedo logs are used for physical crash recoveryCorrect answer
- BBinary logs support replication and point-in-time recoveryCorrect answer
- CThe doublewrite buffer helps handle partially written pagesCorrect answer
- DBinary logs, not redo logs, are used for instance crash recovery
Why this is the answer
Redo logs perform physical crash recovery. Binary logs support replication and point-in-time recovery, and the doublewrite buffer helps correct partially written pages during crash recovery.
Performance Optimization
8 questions in this domain
- Question 22Single Choice
For very large MySQL datasets, which backup method is generally slower to create or restore?
- ALogical backupCorrect answer
- BPhysical backup
- CTransaction log backup
- DDisk snapshot backup
Why this is the answer
Logical backups export and reload SQL row by row, making them slower than physical file backups for large volumes.
- Question 23Multiple Select
Which statements correctly describe the behavior of mysqldump --single-transaction?
- AProvides a consistent InnoDB snapshotCorrect answer
- BAllows other writes during the backupCorrect answer
- CAcquires a global read lock
- DPrevents all table modifications
Why this is the answer
It starts a transaction with a consistent snapshot, but it does not lock the database or stop other writes.
- Question 24Interactive
Read the backup command displayed in the code block, then classify the backup type.
- ALogical backupCorrect answer
- BPhysical backup
- CBinary log snapshot
- DTablespace copy
Why this is the answer
mysqldump generates SQL statements, so this is a logical backup, not a physical file backup.
- Question 25Single Choice
To restore a logical MySQL backup to a specific point in time, which files are also required?
- ABinary log filesCorrect answer
- BRedo log files
- CDoublewrite buffer files
- DBuffer pool contents
Why this is the answer
Binary logs record changes that happen after the backup, so they are needed to roll a restored database forward to a specific time.
- Question 26Multiple Select
Which statements about using cp or tar on a running MySQL data directory are correct?
- AIt may produce inconsistent filesCorrect answer
- BIt is consistent after a clean shutdownCorrect answer
- CIt never requires flushing logs
- DIt is the recommended way to back up a live database
Why this is the answer
A running data directory can have mismatched files because buffers and logs are not flushed consistently, so a clean shutdown is safer.
- Question 27Interactive
The code block shows a mysqldump backup command with an additional option. What does this option enable after restoring the backup?
- AIt makes point-in-time recovery possibleCorrect answer
- BIt forces a physical backup
- CIt disables the binary log
- DIt shrinks the backup file
Why this is the answer
Recording the binary log position shows where to begin replaying binary logs after restoring the logical backup.
- Question 28Single Choice
What is the primary purpose of the InnoDB redo log?
- ACrash recoveryCorrect answer
- BPoint-in-time recovery
- CReplication only
- DLogical backups
Why this is the answer
Redo logs record physical changes and are used to replay committed transactions after a crash or power failure.
- Question 29Multiple Select
Which statements correctly describe the MySQL buffer pool and crash recovery?
- AIt resides in volatile memoryCorrect answer
- BContents are lost on crashCorrect answer
- CIt remains intact after restart
- DIt is part of the redo log
Why this is the answer
The buffer pool is stored in RAM, so recovery must rely on redo logs and disk files instead of memory contents.
Replication and High Availability
4 questions in this domain
- Question 30Single Choice
For large MySQL datasets, which statement correctly compares logical and physical backups?
- ALogical backups are faster
- BPhysical backups are generally fasterCorrect answer
- CBoth have equal performance
- DPhysical backups are always slower
Why this is the answer
Logical backups rebuild data with SQL operations and add overhead. Physical backups copy raw data files directly, so they are usually faster on large datasets.
- Question 31Multiple Select
Which statements accurately describe the purpose of MySQL binary logs?
- AStore changes for replicationCorrect answer
- BEnable point-in-time recoveryCorrect answer
- CHandle crash recovery
- DReplace full backups
Why this is the answer
Binary logs record transactional changes and are used for replication and point-in-time recovery. Crash recovery is performed by InnoDB redo logs, and binary logs cannot replace a full backup.
- Question 32Interactive
The code block shows a mysqldump command with --single-transaction. What does this option do?
- AIt applies a global lock and stops writes
- BIt gives a consistent snapshot without a global lockCorrect answer
- CIt disables binary logging during the dump
- DIt locks all tables for the whole dump
Why this is the answer
--single-transaction starts a consistent InnoDB transaction or snapshot. It does not stop writes with a global lock, but it still provides a consistent view for InnoDB tables.
- Question 33Single Choice
What is the main problem with using cp or tar to copy a running MySQL data directory as a backup?
- ACopied data files are internally inconsistentCorrect answer
- BThe server shuts down during file copy
- CBackups cannot be encrypted later
- DCopying requires binary logging enabled
Why this is the answer
A running MySQL server can be writing to data files at any time. Copying those files with cp or tar can produce an inconsistent backup that is not valid for recovery.
Security and User Administration
5 questions in this domain
- Question 34Single Choice
According to MySQL recovery mechanisms, what is the role of InnoDB redo logs during crash recovery?
- ATo apply committed but unflushed changesCorrect answer
- BTo replicate user changes to replica servers
- CTo restore the volatile contents of the buffer pool
- DTo copy new data into the system tablespace
Why this is the answer
Redo logs record committed changes and are replayed at crash recovery, while binary logs and doublewrite buffers serve different roles.
- Question 35Multiple Select
Which statements correctly describe how binary logs relate to MySQL backup and recovery?
- ABinary logs allow recovery of transactions that occurred after the backup.Correct answer
- BBinary logs are only needed when backing up MyISAM tables.
- CA backup alone cannot support point-in-time recovery without the needed binary logs.Correct answer
- DRedo logs and binary logs are interchangeable for point-in-time recovery.
Why this is the answer
The binary log contains transaction history; combined with a backup it enables point-in-time recovery. The redo log is for crash recovery, not PITR.
- Question 36Interactive
Why is the file-system copy shown in the code block not considered a valid backup while MySQL is running?
- AFiles may mix written and unwritten pages, creating an inconsistent data directory.Correct answer
- BThe command stops all user connections and prevents further writes.
- CThe redo log is erased when file-system copying starts.
- DThe command excludes the transaction log from the copy.
Why this is the answer
Copying files from a live data directory can mix states of pages and logs. Use MySQL backup tools or a consistent snapshot method instead.
- Question 37Single Choice
In MySQL database administration, which statement best describes the main characteristic of a logical backup?
- AIt creates a set of SQL statements that can reload the data.Correct answer
- BIt copies physical InnoDB tablespace files.
- CIt is always faster than a physical backup for large databases.
- DIt can be taken only by shutting down MySQL.
Why this is the answer
A logical backup stores data as SQL statements. It is normally slower than physical backup for large datasets and requires binary logs for full PITR.
- Question 38Multiple Select
Which statements are correct about mysqldump --single-transaction when backing up InnoDB tables?
- AIt starts a consistent read and creates a snapshot for the dump.Correct answer
- BIt behaves like a global read lock that stops all InnoDB writes.
- CIt does not prevent normal InnoDB DML from running during the backup.Correct answer
- DIt automatically rotates binary logs when the backup completes.
Why this is the answer
--single-transaction gives a consistent InnoDB snapshot without a global lock. Writes and DML are not blocked, and binary logs are not rotated automatically.
Storage Engines and Table Management
5 questions in this domain
- Question 39Single Choice
When you need to back up a very large database, which type of backup is generally faster?
- ALogical backups are faster.
- BPhysical backups are generally faster.Correct answer
- CBoth are always equally fast.
- DSpeed depends only on the storage engine.
Why this is the answer
Logical backups use SQL statements, which adds parsing and network overhead. Physical backups copy files directly, so they are generally faster for large datasets.
- Question 40Multiple Select
Which two statements are true when --single-transaction is used to create a consistent backup with mysqldump?
- AProvides a consistent snapshotCorrect answer
- BPrevents all write statements
- CCreates a global lock on all tables
- DDoes not stop all DML operationsCorrect answer
Why this is the answer
The option starts a consistent snapshot, but it is not a global lock. It does not block or prevent all write operations during the backup.
- Question 41Interactive
Review the mysqldump command in the code block. What does its transaction-related option accomplish?
- AProduces a consistent transactional snapshotCorrect answer
- BAcquires a global read lock for all tables
- CCopies only the database schema
- DSwitches MySQL to physical backup mode
Why this is the answer
The option shown is --single-transaction. It starts a consistent transaction snapshot and avoids requiring a long-running global lock on InnoDB tables.
- Question 42Single Choice
To restore a database to a specific point in time, which component must be used together with a logical backup?
- ABinary logs generated after the backupCorrect answer
- BRedo logs generated after the backup
- CBuffer Pool contents from the old instance
- DDoublewrite Buffer pages from the backup
Why this is the answer
The logical backup captures the database as of backup time. To reach a later millisecond, you must apply binary logs recorded after the backup.
- Question 43Multiple Select
Why is copying a running MySQL data directory with cp or tar not considered a valid hot backup? Choose two.
- AMySQL may write to files during the copy.Correct answer
- BThe copy can contain files from different points in time.Correct answer
- CMySQL shuts down automatically while files are copied.
- DThe binary log is never included in the copy.
Why this is the answer
cp or tar does not coordinate writes. The server can change data and log files during the copy, so the resulting backup can be inconsistent and invalid.
Frequently asked questions
Everything about the Oracle MySQL 8.0 Database Administrator question bank and how to use it.