错误1465“无法在系统表上创建触发器".从mysqldump创建的SQL还原后 [英] ERROR 1465 "Triggers can not be created on system tables" after restore from SQL created by mysqldump

问题描述

我正在Amazon RDS上运行mysql 5.6服务器，并设置了一个只读副本，该副本用于使用mysqldump创建备份.

I'm running a mysql 5.6 server on amazon RDS and set up a read replica I use to create backups using mysqldump.

我尝试使用"--all-databases"选项，但是当我尝试导入mysqldump创建的SQL时，最终从mysql命令客户端收到此错误:

I tried with the "--all-databases" option, but when I'm trying to import the SQL created by mysqldump, I end up with this error from the mysql command client:

ERROR 1465 "Triggers can not be created on system tables"

我使用了"--all-databases"，因为我基本上想还原RDS中的所有内容，以防发生崩溃.

I used "--all-databases" because I basically want to restore everything in RDS as it was before in case of a crash.

也许这是错误的，我应该只选择我的模式(加上mysql.users表)?但是，如果"--all-databases"永远无法工作，那么它首先是什么呢?还是我使用错了?

Maybe that's wrong, and I should only select my schemas (plus the mysql.users table)? But what is "--all-databases" for in the first place, if it would never work? Or am I using it wrong?

推荐答案

在与Amazon支持小组进行了较长的交谈之后，这是我想出的:

After a longer conversation with the amazon support, this is what I came up with:

1. 可以通过将mysql配置临时设置为log_bin_trust_function_creators设置为1来解决触发问题

1. The trigger problem can be solved by temporarily setting the mysql config setting log_bin_trust_function_creators to 1

他们不建议拍摄完整快照，而是选择单个数据库.

They do not recommend to take a full snapshot, but rather select individual databases.

这意味着您必须在恢复中执行两个步骤:

That means you have to do two steps in an recovery:

1. 创建架构
2. 创建用户并授予他们必要的权限
3. 导入转储的数据

使用视图，您可能会遇到错误消息

With views, you might run into the error message

第12行的错误1227(42000):访问被拒绝；您需要(至少其中一种)SUPER权限才能进行此操作

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

之所以显示它，是因为mysqldump在create视图中创建了一个definer语句(甚至您的root用户也没有足够的权限来设置该语句).如此处所述，摆脱它们的唯一方法是过滤，即过滤我喜欢这样:

Which is displayed because mysqldump creates a definer statement into the create view (and even your root user doesn't have enough privileges to set this). As described here, the only way to get rid of them is filtering, which I do like this:

sed -i's/^/.. 50013 DEFINER =.* SQL安全定义器.. $/-删除了安全定义器语句/g'

sed -i 's/^/..50013 DEFINER=.* SQL SECURITY DEFINER ..$/-- removed security definer statement/g'

令人尴尬，需要大量的手动工作才能从RDS中取出数据库备份，然后又将其恢复.在任何情况下，您都应该依靠备份来进行备份只能由RDS自动制作，因为那些可以访问您的AWS帐户的恶意攻击者可以轻松删除它们.

It's embarrassing that there is so much manual work needed to get database backups out of RDS, and also back in. Under no circumstances you should rely on the backups which are automatically made by RDS only, as those could be easily deleted by a malicious attacker that gained access to your AWS account.

这篇关于错误1465“无法在系统表上创建触发器".从mysqldump创建的SQL还原后的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！