How do I resolve an error in mysqldump on Amazon RDS MySQL or MariaDB?

3 minute read

I want to troubleshoot an error when I use mysqldump on Amazon Relational Database Service (Amazon RDS) to import or export data.

Resolution

Use the following resolution steps, to resolve your error.

Couldn't start FLUSH TABLES WITH READ LOCK error

When you use mysqpdump to export data with the —master-data option, the —master-data option acquires a FLUSH TABLES WITH READ LOCK. This requires SUPER privileges that the Amazon RDS primary user doesn't have. Amazon RDS doesn't support GLOBAL READ LOCK. When MySQL runs a CHANGE MASTER TO command, the binary log file name and position is recorded in the mysqldump file. For more information, see ER_ACCESS_DENIED_ERROR on the MySQL website.

To resolve this error, remove the —master-data option. When you remove this option, you aren't given an exact log position in the mysqldump. To work around this issue, either take the mysqldump when your application is stopped, or take the mysqldump from an Amazon RDS read replica. This allows you to get the exact log position when you run the SHOW SLAVE STATUS command. Because when you stop the replica, this action confirms that the binlog positions don't change. To create a mysqldump from an Amazon RDS MySQL read replica of this RDS DB instance, complete the following steps:

Set a value for the binary log retention.

Run the following command on the read replica to stop the replication:

CALL mysql.rds_stop_replication;

Take the mysqldump without —master-data=2 from the read replica.
Run the SHOW SLAVE STATUS command on the replica and capture the Master_Log_File and Exec_Master_Log_Pos.
If you use the replica for your application, run the following command to start the replication again:
```
CALL mysql.rds_start_replication;
```
If you don't use the replica for your application, then you can delete the replica.

Max_allowed_packet errors

The max_allowed_packet error occurs when the mysqldump command requests a packet that's larger than the value of the set max_allowed_packet parameter on your RDS DB instance. For more information, see Packet too large on the MySQL website.

To resolve this error, increase the global value for max_allowed_packet. Or configure the max_allowed_packet in the mysqldump for that session (rather than globally for the whole database). For example, you can modify the command similar to the following:

$ mysqldump --max\_allowed\_packet=1G ......

SUPER privileges and DEFINER errors

This error shows one or more of the following issues:

Your target RDS DB instance has the binary log turned on with the backup retention period > 0. And the mysqldump file contains an object, such as a trigger, view, function, or event. For more information, see How can I resolve 1227 and definer errors when importing data to my Amazon RDS for MySQL DB instance using mysqldump?
The mysqldump file that you imported tried to create an object with a DEFINER attribute user that doesn't exist in your RDS DB instance. Or you tried to create an attribute user that doesn't have the required SUPER user privileges.
The command for the line referenced in the error message requires SUPER privilege(s) that aren't provided in your RDS DB instances.