1Z0-908 practice question 9 of 25

Examine this command, which executes successfully: shell> mysqldump --master-data=2 --single-transaction --result-file=dump.sql mydb Which two statements are true? (Choose two.)

Full set: 140 questions
Answer options
A: It executes flush tables with read lock. Correct answer
B: It enforces consistent backups for all storage engines.
C: The backup created is a consistent data dump. Correct answer
D: This option uses the READ COMMITTED transaction isolation mode.
E: It is a cold backup.
Correct Answer:
A. It executes flush tables with read lock.; C. The backup created is a consistent data dump.
Explanation

The --single-transaction option creates a consistent backup by starting a transaction with a REPEATABLE READ isolation level. This provides a point-in-time snapshot of all data within transactional storage engines like InnoDB, making the backup a consistent data dump (C). When --master-data is used with --single-transaction, mysqldump must also record the precise binary log coordinates corresponding to that snapshot. To prevent any other operations from altering tables or the log position between starting the transaction and reading the coordinates, mysqldump briefly acquires a global read lock by executing FLUSH TABLES WITH READ LOCK (A). The lock is released immediately after the log position is recorded.

Why Incorrect

B. The --single-transaction option only guarantees consistency for transactional storage engines (e.g., InnoDB). Data in non-transactional tables (e.g., MyISAM) can still change during the dump.
D. The --single-transaction option sets the session's transaction isolation level to REPEATABLE READ to create the consistent snapshot, not READ COMMITTED.
E. This is a hot backup because the database remains online and available for read/write operations (on InnoDB tables) while the backup is in progress. A cold backup requires the database to be offline.

References

1. MySQL 8.0 Reference Manual

Section 7.4.4

"mysqldump — A Database Backup Program":

For Answer C & D: Under the description for the --single-transaction option: "This option sends a START TRANSACTION SQL statement to the server before dumping any data. It is useful only with transactional tables such as InnoDB

because then it dumps the consistent state of the database at the time when START TRANSACTION was issued... This statement automatically sets the current session's transaction isolation level to REPEATABLE READ." This confirms the backup is consistent (C) and the isolation level is REPEATABLE READ

making (D) incorrect.

For Answer A: Under the description for the --master-data option: "When used in combination with --single-transaction

a global read lock is acquired only for a short time at the beginning of the dump. The lock is released after the binary log coordinates are read." This confirms that a lock is acquired.

For Answer B: Under the description for --single-transaction: "When using this option

you should keep in mind that only InnoDB tables are dumped in a consistent state." This explicitly states it does not apply to all storage engines.

2. MySQL 8.0 Reference Manual

Section 15.7.2.1

"Transaction Isolation Levels":

This section details the behavior of REPEATABLE READ

which is the isolation level used by mysqldump --single-transaction. It explains how consistent read snapshots are created

which is the mechanism that makes the dump consistent.

Scroll to Top