MySQL/Amazon RDS 错误：“您没有超级权限...”

MySQL/Amazon RDS error: "you do not have SUPER priviledges..."

提问by tim peterson

I'm attempting to copy my mysql database from an Amazon EC2 to an RDS:

我正在尝试将我的 mysql 数据库从 Amazon EC2 复制到 RDS：

I successfuly did a mysqldumpof my database into my root folder using this:

我mysqldump使用这个成功地将我的数据库放入了我的根文件夹中：

root@ip-xx-xx-xx-xx:~# mysqldump my_database -u my_username -p > my_database.sql

Then I tried to transfer this .sql file to my new RDS database:

然后我尝试将此 .sql 文件传输到我的新 RDS 数据库：

root@ip-xx-xx-xx-xx:~# mysql my_database -u my_username -p -h  
my_new_database.xxxxxxxxx.us-east-1.rds.amazonaws.com < my_database.sql

Unfortunately, I get following error message:

不幸的是，我收到以下错误消息：

You do not have the SUPER privilege and binary logging is enabled 
(you *might* want to use  the less safe log_bin_trust_function_creators variable)

I tried to GRANT SUPER..in a variety of ways but I'm getting errors when I try to do that too. Typing mysql > FLUSH privileges;doesn't work either.

我尝试GRANT SUPER..了多种方式，但是当我尝试这样做时也会出错。打字mysql > FLUSH privileges;也不行。

I'm a mysql beginner so sorry for such an easy question. Thoughts?

我是 mysql 初学者，很抱歉问这么简单的问题。想法？

采纳答案by Ross Smith II

Per http://getasysadmin.com/2011/06/amazon-rds-super-privileges/, you need to set log_bin_trust_function_creatorsto 1 in AWS console, to load your dump file without errors.

根据http://getasysadmin.com/2011/06/amazon-rds-super-privileges/，您需要log_bin_trust_function_creators在AWS 控制台中设置为 1 ，以正确加载转储文件。

If you want to ignore these errors, and load the rest of the dump file, you can use the -foption:

如果要忽略这些错误并加载转储文件的其余部分，可以使用以下-f选项：

mysql -f my_database -u my_username -p -h  
my_new_database.xxxxxxxxx.us-east-1.rds.amazonaws.com < my_database.sql

The -fwill report errors, but will continue processing the remainder of the dump file.

该-f会报告错误，但会继续处理转储文件的剩余部分。

回答by arun-r

1. Open the RDS web console.
2. Open the “Parameter Groups” tab.
3. Create a new Parameter Group. On the dialog, select the MySQL family compatible to your MySQL database version, give it a name and confirm. Select the just created Parameter Group and issue “Edit Parameters”.
4. Look for the parameter ‘log_bin_trust_function_creators' and set its value to '1′.
5. Save the changes.
6. Open the “Instances” tab. Expand your MySQL instance and issue the “Instance Action” named “Modify”.
7. Select the just created Parameter Group and enable “Apply Immediately”.
8. Click on “Continue” and confirm the changes.
9. Wait for the "Modifying" operation to be completed.
10. Again, open the “Instances” tab. Expand your MySQL instance and expand “Instance Action” tab and select "Reboot".

1. 打开 RDS Web 控制台。
2. 打开“参数组”选项卡。
3. 创建一个新的参数组。在对话框中，选择与您的 MySQL 数据库版本兼容的 MySQL 系列，为其命名并确认。选择刚刚创建的参数组并发出“编辑参数”。
4. 查找参数“log_bin_trust_function_creators”并将其值设置为“1”。
5. 保存更改。
6. 打开“实例”选项卡。展开您的 MySQL 实例并发出名为“修改”的“实例操作”。
7. 选择刚刚创建的参数组并启用“立即应用”。
8. 单击“继续”并确认更改。
9. 等待“修改”操作完成。
10. 再次打开“实例”选项卡。展开您的 MySQL 实例并展开“Instance Action”选项卡并选择“Reboot”。

回答by anders

The problem with triggers and stored procedures in the dump file is that these definitions include the user who the stored procedure should be created by, the DEFINER. The user most likely doesn't exist in the RDS so a error is then raised. To be able to load the dump file you can remove the DEFINER using sed or Perl and create the stored procedure/trigger with the user who is performing the import.

转储文件中的触发器和存储过程的问题在于，这些定义包括应该由 DEFINER 创建存储过程的用户。该用户很可能不存在于 RDS 中，因此会引发错误。为了能够加载转储文件，您可以使用 sed 或 Perl 删除 DEFINER，并与执行导入的用户一起创建存储过程/触发器。

perl -pe 's/\sDEFINER=`[^`]+`@`[^`]+`//' < mysqldump.sql > mysqldump.fixed.sql

Now you should be able to load the fixed dump file

现在您应该能够加载固定的转储文件

mysql my_database -u my_username -p -h rds_host < mysqldump.fixed.sql

As said in earlier answer, you should set the DB Parameter:

如前所述，您应该设置数据库参数：

log_bin_trust_function_creators = 1

回答by Yep_It's_Me

For me, there was only 2 commands in my dump file which required SUPER privileges:

对我来说，我的转储文件中只有 2 个命令需要 SUPER 权限：

• SET @@GLOBAL.gtid_purged
• SET @@SESSION.SQL_LOG_BIN

• SET @@GLOBAL.gtid_purged
• SET @@SESSION.SQL_LOG_BIN

According to the mysqldump docsyou can disable these with --set-gtid-purged=OFF.

根据mysqldump 文档，您可以使用--set-gtid-purged=OFF.

Then looking at man mysqldump:

然后看man mysqldump：

Use ON if the intention is to deploy a new replication slave using only some of the data from the dumped server. Use OFF if the intention is to repair a table by copying it within a topology. Use OFF if the intention is to copy a table between replication topologies that are disjoint and will remain so.

如果打算仅使用转储服务器中的一些数据来部署新的复制从属服务器，请使用 ON。如果打算通过在拓扑内复制表来修复表，请使用 OFF。如果打算在不相交的复制拓扑之间复制表，并且将保持如此，请使用 OFF。

So I decided to add --set-gtid-purged=OFFto my mysqldumpcommand and then I could successfully import the resulting dump file.

所以我决定添加--set-gtid-purged=OFF到我的mysqldump命令中，然后我可以成功导入生成的转储文件。

回答by Josh Woodcock

As defined in AWS documentation triggers, procedures, and functions are disabled by default because binary logging is enabled by default. Disabling basically makes your db more secure, but if you have properly secured through the network it won't matter.

正如 AWS 文档中定义的那样，触发器、过程和函数在默认情况下处于禁用状态，因为默认情况下启用了二进制日志记录。禁用基本上可以使您的数据库更安全，但是如果您通过网络正确保护了它就无所谓了。

Follow these steps and your problem will be fixed https://aws.amazon.com/premiumsupport/knowledge-center/rds-mysql-functions/

按照这些步骤操作，您的问题将得到解决 https://aws.amazon.com/premiumsupport/knowledge-center/rds-mysql-functions/

Also you shouldn't use definers when creating procedures. A simple sed command can remove it.

此外，在创建过程时不应使用定义器。一个简单的 sed 命令可以删除它。

回答by Jubba Smail

In addition to editing

除了编辑

log_bin_trust_function_creators = 1

log_bin_trust_function_creators = 1

you need to remove all DEFINERfrom your dump file, check the following link for SEDcommand that can help cleaning your sql dump file.

您需要从转储文件中删除所有DEFINER，请查看以下链接以获取可以帮助清理 sql 转储文件的SED命令。

https://www.percona.com/blog/2014/07/02/using-mysql-triggers-and-views-in-amazon-rds/#comment-10968243

https://www.percona.com/blog/2014/07/02/using-mysql-triggers-and-views-in-amazon-rds/#comment-10968243

回答by Damien Frances

After used arun-r answer, if the problem is not solved you need to modify your dump file. It is simple.

使用 arun -r 回答后，如果问题没有解决，您需要修改您的转储文件。很简单。

In the dump file you will find lines like :

在转储文件中，您会发现以下几行：

DELIMITER ;;
CREATE DEFINER=`username_from_dumped_database`@`host_from_dumped_database` PROCEDURE `procedure_or_function_name`()
BEGIN

You have to replace :

你必须更换：

• username_from_dumped_databaseby your username on rds database.
• host_from_dumped_databseby %

• username_from_dumped_database通过您在 rds 数据库上的用户名。
• host_from_dumped_databse经过 %

I don't know why but this trick worked for me. A simple text editor is enough to do this.

我不知道为什么，但这个技巧对我有用。一个简单的文本编辑器就足以做到这一点。