How can I resolve an error that I received when using mysqldump on Amazon RDS for MySQL or MariaDB?

Short description

You can receive the following errors when using mysqldump:

• Couldn't execute FLUSH TABLES WITH READ LOCK errors
• Max_allowed_packet errors
• SUPER privilege(s) and DEFINER errors
• Lost or aborted connection errors

Resolution

Couldn't execute FLUSH TABLES WITH READ LOCK error

When using the --master-data option with mysqldump to export data, you might receive an error similar to the following:

"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 master user doesn't have, and Amazon RDS doesn't support GLOBAL READ LOCK. When MySQL runs a CHANGE MASTER TO statement to get log information, the binary log file name and position (coordinates) is recorded in the mysqldump file. For more information, see the MySQL Documentation for ER_ACCESS_DENIED_ERROR.

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 by executing SHOW SLAVE STATUS because stopping the replica confirms that the binlog positions do not change. Follow these steps to create a mysqldump from an Amazon RDS MySQL read replica of this RDS DB instance.

1.    Set a value for the binary log retention.

2.    Stop the replication by running 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.    Run SHOW SLAVE STATUS on the replica and capture the Master_Log_File and Exec_Master_Log_Pos.

5.    If you use the replica for your application, start replication again by using the following stored procedure:

CALL mysql.rds_start_replication;

If you don't use the replica for your application, you can delete it.

Max_allowed_packet errors

When using mysqldump to export data, you might receive an error similar to the following:

"Error 2020: Got packet bigger than 'max_allowed_packet' bytes when dumping table tb\_name at row: XX"

This error occurs when the mysqldump command requests a packet that is larger than the value of the max_allowed_packet parameter that is set for your RDS DB instance. For more information, see the MySQL Documentation for Packet too large.

To resolve max_allowed_packet errors, 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 privilege(s) and DEFINER errors

When using mysqldump to import data into an RDS DB instance that is running MySQL or MariaDB, you might receive an error similar to the following:

"ERROR 1227 (42000) at line XX: Access denied; you need (at least one of) the SUPER privilege(s) for this operation"

This error indicates one or more of the following issues:

• Your target RDS DB instance has the binary log enabled (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 ERROR 1227 when enabling replication or automated backups on an Amazon RDS MySQL instance?
• 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. For more information, see How can I resolve definer errors when importing data to my Amazon RDS for MySQL instance using mysqldump?
• The command for the line referenced in the error message requires SUPER privilege(s) that aren't provided in RDS DB instances.

Lost or aborted connection errors

When using mysqldump to import data, you might receive an error similar to the following:

"mysqldump: error 2013: lost connection to mysql server during query when dumping table"

--or--

"mysqldump: Aborted connection XXXXXX to db: 'db_name' user: 'master_user' host: 'XXXXXXX' (Got timeout writing communication packets)"

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

MySQL Documentation for mysqldump

How do I enable functions, procedures, and triggers for my Amazon RDS MySQL DB instance?