Log_bin_trust_function_creators error resolution when Amazon RDS MySQL database is restored

With Amazon AWS EC2 free cloud space, your DB instance uses Amazon RDS. There is a MySQL database with a large amount of data on Windows Server. Now you need to restore this MySQL database on Amazon RDS, tick off the views (because it will require super permissions, and the master user does not have this permission, after all, is managed RDS), leaving only tables, such as (using Navicat):

Figure 1: Tick off views because masteruser on RDS does not have super privilege to restore views object

However, when you restore, the following error message still appears:

You don't have the SUPER privilege and binary logging are enabled (you *might* want to use the less safe log_bin_trust_ Function_creators variable)

After querying, the log_bin_trust_function_creators value is off because there is trigger in table, and if you do not create trigger, this error message does not occur, but trigger must be created in a network query solution, Is the following approach:

Use the Settings command:

However, it cannot be completed without super privilege.

The permanent solution is as follows:

Linux system, in the/etc/my.cnf file, the [mysqld] section adds:

Windows system, in the My.ini file, the [mysqld] section adds:

Log_bin_trust_function_creators=1

You can restore the trigger in the Backup database table.

The reality is that for Amazon RDS, it is a dummy configuration that does not allow the user to configure MY.CNF (and cannot find the file at all), so the configuration method is to configure the parameter to operate on the MySQL instance. In the parameter group, add one (note: The default parameter group cannot be modified), modify it after naming, and set the log_bin_trust_function_creators value to 1;

Amazon RDS has three methods for configuring parameters, which can be referenced in http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_WorkingWithParamGroups.html:

Console Mode (AWS Management console);

Command line mode (CLI);

API mode;

Here's how to fix it using the console:

1, in the RDS console, the right navigation bar has "Parameter Groups" link, click "Create DB Parameters Group", create "Parameters group"

2. Select version, name and description

3, after the creation, you can edit the parameters inside (emphasis: mysql default parameter group is unable to edit parameters), here is the Log_bin_trust_function_creators value from default to 1, save exit;

4. Create a new RDS instance, in "Configure Advanced Settings", in the drop-down list of Parameter group options, select the group you just created and modified, such as:

Figure 2: In the new RDS instance, do not use the default parameter group, but use the group that you created and edited

4-1. Or, in the RDS actually created, select an instance, and in the "Instance Actions" drop-down menu at the top of the interface, click "Modify" to make changes to parameter group, such as:

Figure 3: Modifications to the existing parameter group (pre-modified status in the image, MySQL 5.6 default parameter group)

5. Wait for the RDS instance to be created (or modified to complete).

Restore the database and the table containing the triggers can be restored successfully.

Description: For views, there is no automatic restore on Amazon RDS because no super privileges are available. After tables restore, you can manually create a view and copy the view definition from the source db. If a user has a better way to do Amazon RDS, please reply to the instructions. Thank you.