MySQL / Amazon RDS Error on Import

Question

I am trying to dump all databases from an RDS 500Gb instance into a smaller instance (100Gb). I have many user rights, so I need to reset the table mysql.

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

Now, when I try to load data into 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.

As a health check, I tried to dump the data to the original instance, but received the same error. Can someone please advise what should I do here? Thank!

Answer 1

, , , mysql (, grep, USE database;, sed, . ), , RDS MySQL.

mysql RDS, , , , RDS SUPER... , mysqldump , .

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

, , ( "" ).

, . , "old_dumpfile.sql" "new_dumpfile.sql"... , USE CREATE DATABASE `mysql` , `mysql` . , DROP DATABASE, --skip-add-drop-database.

mysql , , .

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

Answer 2

, workbench. , (100 ), 500 100 , , .

Answer 3

RDS MySQL. RDS - :

GRANT ALL ON `%`.* to '<type_the_usernamne_here>'@'%';

, . .