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 questionsThe --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.
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.
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.
Keep practicing
Take the free 1Z0-908 practice test
Ten exam-style questions with answers and explanations, plus the exam facts and study guides.
More questions
Other 1Z0-908 practice questions
- Question 2You must run multiple instances of MySQL Server on a single host. Which three methods are…
- Question 3Which two tools are available to monitor the global status of InnoDB locking? (Choose two…
- Question 4Which two MySQL Server accounts are locked by default? (Choose two.)
- Question 5All MySQL Server instances belonging to InnoDB Cluster have SSL configured and enabled. Y…
- Question 6t is a non-empty InnoDB table. Examine these statements, which are executed in one sessio…
- Question 8Examine this command, which executes successfully: $ mysqlrouter --bootstrap user@hostnam…
