mysql 1465,错误1465“无法在系统表上创建触发器”。从mysqldump创建的SQL还原后

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

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"

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

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?

解决方案

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

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:

Create Schema

Create Users and grant necessary rights to them

Import dumped data

With views, you might run into the error message

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

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 SECURITY DEFINER ..$/-- removed security definer statement/g'

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.