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"
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:
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.