ERROR 1465 "Triggers can not be created on system tables" after restore from SQL created by mysqldump

Question

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?

Answer 1

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

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

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

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

   1. Create Schema
   2. Create Users and grant necessary rights to them
   3. Import dumped data

4. 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.

Answer 2

Import with mysql -f to skip the offending proc.

In my case the skipped definition was this little gem:

DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`rdsadmin`@`localhost`*/ /*!50003 trigger block_proc_u before update on proc
for each row
BEGIN
DECLARE foo varchar(255);
if old.Definer = "rdsadmin@localhost" then
  select `ERROR (RDS): CANNOT MODIFY RDSDMIN OBJECT` into foo;
end if;
END */;;

Plus another four very similar ones (total five skipped).