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

Resolution

Choose one of the following methods to resolve your error:

Resolve the "FLUSH TABLES WITH READ LOCK" error

If you use mysqldump to export data with the --master-data option, then you receive the following error message:

"mysqldump: Couldn't execute 'FLUSH TABLES WITH READ LOCK': Access denied for user 'user'@'%' (using password: YES) (1045)"

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 mysqldump file records the binary log file name and position. 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, the mysqldump doesn't include an exact log position. To work around this issue, take the mysqldump after you stop your application, 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. When you stop the replica, the binlog positions don't change.

To create a mysqldump from a read replica, complete the following steps:

1. Set a value for the binary log retention.

2. To stop the replication, run the following command on the read replica:

   CALL mysql.rds_stop_replication;

3. Take the mysqldump without --master-data=2 from the read replica.

4. To get the binary log position, run the following command on the replica:

   SHOW SLAVE STATUS;

5. Copy the values of Master_Log_File and Exec_Master_Log_Pos from the output.
   Note: For more information about how to use these values, see Configuring binary log file position replication with an external source instance.

6. If you use the replica for your application, then run the following command to start the replication:

   CALL mysql.rds_start_replication;

   Note: If you don't use the replica for your application, then you can delete the replica.

Resolve max_allowed_packet errors

If the mysqldump command requests a packet larger than the max_allowed_packet parameter value on your RDS DB instance, then you receive the following error message:

"mysqldump: Error 2020: Got packet bigger than 'max_allowed_packet' bytes when dumping table XXXX at row: XXXX"

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. For example, run the following command:

mysqldump --max-allowed-packet=128M -h hostname -u username -p database_name > dump.sql

Note: Replace hostname with your RDS DB instance endpoint, username with your database user, and database_name with the name of your database.

Resolve SUPER privileges and DEFINER errors

If the mysqldump file references DEFINER users or SUPER privileges that aren't available on your RDS DB instance, then you receive the following error message:

"Error: 1227 SQLSTATE: 42000 (ER_SPECIFIC_ACCESS_DENIED_ERROR) Access denied; you need (at least one of) the %s privilege(s) for this operation"

To resolve this error, take the following actions:

• Check whether your target RDS DB instance has the binary log turned on with the backup retention period greater than 0. If the mysqldump file contains an object such as a trigger, view, function, or event, then this error occurs. For more information, see How do I resolve 1227 and definer errors when I use mysqldump to import data to my Amazon RDS for MySQL DB instance?
• Verify that the mysqldump file doesn't create an object with a DEFINER attribute user that doesn't exist in your RDS DB instance. Also, verify that the attribute user has the required SUPER user privileges.
• Confirm that the command for the line referenced in the error message doesn't require SUPER privileges that your RDS DB instance doesn't provide.

Resolve connection errors

For more information about the cause and resolution of this error, see How do I resolve the error "MySQL server has gone away" when connecting to my Amazon RDS MySQL DB instance?

Related information

mysqldump on the MySQL website

Importing data from an external MySQL database to an Amazon RDS for MySQL DB instance