PCDE Migrate data solutions Practice Question
An engineer is manually migrating a MySQL database to Cloud SQL using mysqldump and import. They need to capture the binary log position to enable CDC with Database Migration Service later. Which TWO mysqldump flags should they include to ensure a consistent snapshot and capture the log position? (Choose 2 correct answers.)
Answer choices
Why each option matters
Answer the question above first, then reveal the full breakdown to understand why each option is right or wrong.
Correct answer & explanation
✓
--single-transaction
--single-transaction ensures a consistent snapshot by starting a transaction and avoiding table locks (for InnoDB). --master-data=2 writes the binary log position as a comment in the dump file (value 2). --skip-lock-tables is not needed because --single-transaction handles consistency. --triggers exports triggers but not binary log position. --no-data excludes data.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
--no-data
Why it's wrong here
--no-data omits data; the engineer needs to migrate data.
- ✓
--single-transaction
Why this is correct
This flag ensures a consistent snapshot without locking tables.
- ✓
--master-data=2
Why this is correct
This flag includes the binary log filename and position as a comment in the dump.
- ✗
--skip-lock-tables
Why it's wrong here
This flag is not needed; --single-transaction already avoids locks.
- ✗
--triggers
Why it's wrong here
--triggers exports triggers but does not capture binary log position.
Go deeper
Related to this question
About these practice questions
Courseiva writes every PCDE question from scratch — 1,446 in total, each with an explanation and a wrong-answer breakdown. None are copied from real exams or dumps. Learn why practice questions differ from exam dumps →
Same concept, more angles
7 more ways this is tested on PCDE
These questions test the same concept from different angles. Work through them to make sure you can recognise it however the exam phrases it.
Variation 1. An engineer is migrating a MySQL database to Cloud SQL using mysqldump. The source database uses InnoDB tables. Which TWO mysqldump options should the engineer use to perform a consistent online backup without locking tables? (Choose 2 correct answers.)
easy- ✓ A.--quick
- ✓ B.--single-transaction
- C.--routines
- D.--no-data
- E.--skip-lock-tables
Why A: --single-transaction starts a transaction to get a consistent snapshot without locking. --quick prevents memory buffering. --skip-lock-tables avoids table locks, but --single-transaction already does that. --no-data exports only schema. --routines exports stored procedures but not consistency.
Variation 2. A team is planning a one-time migration of a 500 GB MySQL database to Cloud SQL using Database Migration Service. They want to minimize the impact on the source database. Which mysqldump flags should they use when taking the initial snapshot?
easy- A.--master-data=2 and --single-transaction
- B.--lock-all-tables and --flush-logs
- ✓ C.--single-transaction and --skip-lock-tables
- D.--all-databases and --routines
Why C: For a consistent snapshot without locking InnoDB tables, use --single-transaction. --skip-lock-tables avoids table locks. --master-data is not needed for DMS one-time migration.
Variation 3. An engineer is manually migrating a MySQL database to Cloud SQL using mysqldump and mysql import. They need to ensure the dump captures a consistent snapshot without locking InnoDB tables. Which mysqldump flags should they use?
medium- A.--single-transaction --lock-tables
- B.--lock-tables --skip-lock-tables
- ✓ C.--single-transaction --skip-lock-tables
- D.--all-databases --single-transaction
Why C: --single-transaction uses a transaction to get a consistent snapshot for InnoDB without locking. --skip-lock-tables prevents table locks. Together they achieve consistent backup without disrupting writes.
Variation 4. During a MySQL to Cloud SQL migration using Database Migration Service, the full dump phase is taking much longer than expected. The source MySQL database is 500 GB and the Cloud SQL instance is of sufficient size. What is the most likely cause of the slow dump?
hard- A.The source database is using a public IP address.
- ✓ B.The source database has many MyISAM tables.
- C.Binary logging is not enabled on the source.
- D.The Cloud SQL instance is using a shared-core machine type.
Why B: Without --single-transaction, mysqldump locks tables, causing contention and slow performance. DMS uses mysqldump internally and requires InnoDB tables with --single-transaction for consistent non-blocking dumps.
Variation 5. During a one-time (non-continuous) migration from MySQL to Cloud SQL using mysqldump, the engineer wants to ensure the dump does not lock InnoDB tables. Which flag should be included in the mysqldump command?
medium- A.--skip-lock-tables
- B.--flush-logs
- ✓ C.--single-transaction
- D.--lock-all-tables
Why C: --single-transaction starts a transaction at the time of dump, ensuring a consistent snapshot without locking tables (for InnoDB).
Variation 6. A team is migrating a MySQL database to Cloud SQL using mysqldump for the initial snapshot. The source database uses InnoDB tables. Which mysqldump flags should be used to ensure a consistent snapshot without locking tables?
easy- ✓ A.--single-transaction --skip-lock-tables
- B.--all-databases --routines
- C.--lock-tables --single-transaction
- D.--flush-logs --master-data=2
Why A: --single-transaction uses a read transaction to get a consistent snapshot without table locks for InnoDB.
Variation 7. A company is using mysqldump to migrate a MySQL database to Cloud SQL. The source database uses InnoDB tables and is running a production workload. They want to ensure a consistent snapshot without locking tables. Which mysqldump flags should they use?
medium- A.--single-transaction --master-data
- B.--lock-all-tables --flush-logs
- ✓ C.--single-transaction --skip-lock-tables
- D.--skip-lock-tables --all-databases
Why C: --single-transaction uses a transaction to get a consistent snapshot without locking tables (works with InnoDB). --skip-lock-tables prevents explicit table locks. --lock-all-tables would lock tables. --master-data is for binary log coordinates but not required for consistency.
JA
Written by Johnson Ajibi, MSc IT Security
Senior Network & Security Engineer · founder of Courseiva
This PCDE practice question is part of Courseiva's free Google Cloud certification practice question bank. Courseiva provides original exam-style practice questions with explanations, topic-based practice, mock exams, readiness tracking, and study analytics to help learners prepare for the PCDE exam.