1

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.

NimbusScale
  • 297
  • 1
  • 2
  • 10
  • Well, your log message indicates that your mysql_admin user's access was denied to the mysql table. Fix that problem and try again. – EEAA Sep 01 '15 at 23:50
  • Maybe this is related to ie being an RDS instance and not a MySQL server I fully control. `mysql> SHOW GRANTS for 'mysql_admin'@'localhost'; ERROR 1141 (42000): There is no such grant defined for user 'mysql_admin' on host 'localhost' mysql> select User from mysql.user; +-------------+ | User | +-------------+ | mysql_admin | | rdsadmin | +-------------+ 2 rows in set (0.00 sec) mysql> GRANT ALL PRIVILEGES ON *.* TO 'mysql_admin'@'localhost'; ERROR 1045 (28000): Access denied for user 'mysql_admin'@'%' (using password: YES)` Am I doing something wrong? – NimbusScale Sep 02 '15 at 00:28
  • I don't know how to get new lines into a comment, – NimbusScale Sep 02 '15 at 00:30
  • Edit your answer to include the new information. – EEAA Sep 02 '15 at 00:44
  • You can't `GRANT ALL PRIVILEGES` in RDS because you don't *have* all privileges. You have to grant privs explicitly by name. Also, `@'localhost'` is meaningless in RDS because you can't connect from localhost (the RDS machine itself). Use `@'%'` or an appropriate IP address or IP wildcard instead. Finally, the MySQL schema (which is where the error on line 38 claims to be) is subject to additional restrictions on RDS and a full mysqldump is unlikely to work. – Michael - sqlbot Sep 02 '15 at 02:52

0 Answers0