Problem description
When I run the mysqldump command on my ApsaraDB RDS for MySQL instance, a primary/secondary switchover is triggered.
Causes
If you use the
--master-data parameter but you do not use the --single-transaction parameter when you run the mysqldump command on a table in your RDS instance, the FLUSH TABLES WITH READ LOCK statement is executed after you run the command. As a result, DML operations cannot be performed on the table when you run the mysqldump command. If DML operations cannot be performed for a long period of time, the failure detection mechanism of the high availability (HA) system fails. In this case, a switchover between the primary RDS instance and the secondary RDS instance is performed. Note If the HA component periodically executes DML statements on the primary RDS instance and the statements fail to be executed for a long period time, the failure detection mechanism of the HA system fails.
Solutions
If you use the --master-data parameter when you run the mysqldump command, you must also use the --single-transaction parameter. This way, a consistent view is provided to ensure data consistency. Then, you can execute the UNLOCK TABLES statement to unlock the table. DML operations can be performed during data export, and no primary/secondary switchover is triggered.
Sample command:
mysqldump -h127.0.0.1 -usystem -p123456 --master-data=2 --single-transaction --set-gtid-purged=OFF --all-databases --triggers --events --routines> /tmp/all_master.sql