MySQL/Amazon RDS error on import

Question

I'm attempting to dump all the databases from a 500Gb RDS instance into a smaller instance (100Gb). I have a lot of user permissions saved so I need to dump the mysql table.

mysqldump -h hostname -u username -ppassword --all-databases > dump.sql

Now when I try to upload the data to my new instance I get the following error:

mysql -h hostname -u username -ppassword < dump.sql`
ERROR 1044 (42000) at line 2245: Access denied for user 'staging'@'%' to database 'mysql'

I would just use a database snapshot to accomplish this, but my instance is smaller in size.

As a sanity check, I tried dumping the data into the original instance but got the same error. Can someone please advise on what I should do here? Thanks!

Answer 1

You may need to do the databases individually, or at least remove the mysql schema from the existing file (perhaps using grep to find the line counts for the USE database; statements and then sed to trim out the troublesome section, or see below), and then generate a dump file that doesn't monkey with the table structures or the proprietary RDS triggers in the MySQL schema.

I have not tried to restore the full mysql schema onto an RDS instance, but I can certainly see where it would go awry with the customizations in RDS and the lack of SUPER privilege... but it seems like these options on mysqldump should get you close, at least.

mysqldump --no-create-info      # don't try to drop and recreate the mysql schema tables
          --skip-triggers       # RDS has proprietary triggers in the mysql schema
          --insert-ignore       # write INSERT IGNORE statements to ignore duplicates
          --databases mysql     # only one database, "mysql"
          --skip-lock-tables    # don't generate statements to LOCK TABLES/UNLOCK TABLES during restore
          --single-transaction  # to avoid locking up the source instance during the dump

If this is still too aggressive, then you will need to resort to dumping only the rows from the specific tables whose content you need to preserve ("user" and the other grant tables).

THERE IS NO WARRANTY on the following, but it's one from my collection. It's a one-liner that reads "old_dumpfile.sql" and writes "new_dumpfile.sql"... but switching the output off when it sees the USE or CREATE DATABASE statements with `mysql` on the same line, and switching it back on again the next time such a statement occurs without `mysql` in it. This will need to be modified if your dump file also has the DROP DATABASE statements in it, or you could generate a new dumpfile with --skip-add-drop-database.

Running your existing dump file through this should essentially remove only the mysql schema from that file, allowing you to easily restore it manually, first, and then let the rest of the database data flow in more smoothly.

perl -pe 'if (/(^USE\s|^CREATE\sDATABASE.*\s)`mysql`/) { $x = 1; } elsif (/^USE\s`/ || /^CREATE\sDATABASE/) { $x = 0; }; $_ = "" if $x;' old_dumpfile.sql > new_dumpfile.sql