I am currently planning a migration of AWS resources from one account to another. This includes several MySQL RDS instances.
After some research it looks like a "dump and load" strategy is needed. My goal was to dump everything that was part of the server; users, databases, grants, any triggers, events, what have you.
I did the backup using the following command:
$ mysqldump -h migration-test-src1.cmgwbovklkqq.us-west-2.rds.amazonaws.com -u mysql_admin -ppswd --hex-blob --routines --triggers --events --all-databases > migration-test-src1.dump
which worked fine, but I get the following error when trying to restore to the destination RDS instance:
$ mysql -h migration-test-dest1.cmgwbovklkqq.us-west-2.rds.amazonaws.com -u mysql_admin -ppswd < migration-test-src1.dump
ERROR 1044 (42000) at line 38: Access denied for user 'mysql_admin'@'%' to database 'mysql'
I'm not quite sure why the restore isn't working. I'm not a DBA, so I was hoping I might find someone here that is willing to help.
Is there something I can do to get the file restored? Is the strategy I am using to do that back and restore flawed?
Thanks in advance for any advice.