I've been trying to move an existing db from MySQL running on EC2 to a new Amazon RDS instance (an experiment to see if we can move across). So far, it's not going well. I'm stuck at the initial import before setting up replication (instructions here).
I've prepared the RDS instance as described and can connect to it from the EC2 instance using mysql. I ran the mysqldump command as:
mysqldump --master-data --databases db1 db2 > dump.sql
Then attempted to upload it to RDS with:
mysql -h RDSHost -P 3306 -u rdsuser --password=rdspassword < dump.sql
The first problem was at line 22 of the dump:
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=106;
This line caused error
ERROR 1227 (42000) at line 22: Access denied; you need (at least one of) the SUPER privilege(s) for this operation. No problem, just commented out that line and hope to fix it later via mysql.rds_set_external_master(). Retried the upload, and got a very similar error:
ERROR 1227 (42000) at line 7844: Access denied; you need (at least one of) the SUPER privilege(s) for this operation. The section around line 7844 looks like this:
/*!50001 CREATE ALGORITHM=UNDEFINED */ /*!50013 DEFINER=`dev`@`localhost` SQL SECURITY DEFINER */ /*!50001 VIEW `jos_contributor_ids_view` AS select `jos_resource_contributors_view`.`uidNumber` AS `uidNumber` from `jos_resource_contributors_view` union select `jos_wiki_contributors_view`.`uidNumber` AS `uidNumber` from `jos_wiki_contributors_view` */;
By commenting out the first 2 lines and adding a 'CREATE' to the third,I was able to get past this one. But there are tons of sections like this. Is there some way round this without all the editing? Like an option to
mysqldump to not produce anything which needs SUPER privileges?
It seems like lots of people have had similar problems, like having to run
sed against the output of mysqldump / mysqlbinlog! I'm going to post on the AWS forum too - really I think RDS should have a more tolerant way of importing from mysqldump, or a specific tool which can be run against an existing db to create a dump which is complaint with RDS security. Just wondered if anyone had any other recipes or tricks which might help here.