When I try to import data to an Amazon Relational Database Service (Amazon RDS) for MySQL instance using mysqldump, I get an error similar to the following:

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

This error occurs when the destination database has the binary log enabled, and the mysqldump file contains an object (a trigger, view, function, or event).

If any of the create statements don't have the “NO SQL,” “READS SQL DATA,” or “DETERMINISTIC” keywords, then MySQL can't create those objects, and the import fails with error 1227.

To resolve error 1227, perform one of the following actions:

Change the parameter group value of log_bin_trust_function_creators to 1:

To relax this condition and allow the import of all the objects, set the global log_bin_trust_function_creators system variables to 1 through the RDS custom parameter group. For more information, see Modifying Parameters in a DB Parameter Group. Note: Because you can't change values in a default parameter group, you must create a DB parameter group and then modify the parameter.

  1. Open the Amazon RDS console, and then choose Parameter groups from the navigation pane.
  2. Select the log_bin_trust_function_creators parameter group, choose Parameter group actions, and then choose Edit.
  3. Change the Value to 1.
  4. Choose Save changes. Note: Be sure that the same parameter group is attached to the target instance that is experiencing the error.

Find the DEFINER in the mysqldump and remove users that do not exist in the target database

To resolve error 1227, find the DEFINER in the mysqldump, and then remove the user that does not exist from the RDS target database. This issue can occur when Amazon RDS for MySQL attempts to create an object under a database user in the target database, and that database user doesn't exist on the destination database. Some MySQL clients provide the option to ignore the definer when creating a logical backup, but this doesn't occur by default. To find and remove the DEFINER, see I received a definer error when importing data to my RDS MySQL instance using mysqldump.

For an example DEFINER in mysqldump, see the following:

/*!50003 CREATE*/ /*!50017 DEFINER=`rdsadmin`@`localhost`*/ /*!50003 TRIGGER customer_update AFTER UPDATE ON `customer` FOR EACH ROW

Remove the following section:

/*!50017 DEFINER=`rdsadmin`@`localhost`*/

The modified mysqldump should look like the following:

/*!50003 CREATE*/ /*!50017 DEFINER=`masteruser`@`%`*/ /*!50003 TRIGGER customer_update AFTER UPDATE ON `customer` FOR EACH ROW

Did this page help you? Yes | No

Back to the AWS Support Knowledge Center

Need help? Visit the AWS Support Center.

Published: 2016-06-27

Updated: 2018-09-18