First attempt to migrate EC2 MySQL to Amazon RDS no going well - SUPER privileges

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.

Thanks,

Dave

You do likely need log_bin_trust_function_creators = 1 on RDS but that isn't the issue, here.

You can specify a DEFINER value other than your own account only if you have the SUPER privilege.

^{— http://dev.mysql.com/doc/refman/5.6/en/stored-programs-security.html}

When a stored program (proc, function, event, or trigger) is running, everything it does has the permissions of the user who defined it, or of the user explicitly stated with a DEFINER declaration. This allows, among other things, for stored programs to permit other users to do things to data they don't directly have permission to manipulate, as long as they have permission to use the stored program itself.

It would be a serious vulnerability, then, if a non-SUPER user could create a procedure with an arbitrary definer, because the user could escalate his or her privileges at will.

This is also true of views, of course, when the definer security context is used, as in the example you posted.

One of the biggest complaints I have with RDS is that you can't have SUPER... and now it can be one of yours, too :) because that fact is the cause of the problem you are having.

Of course, if I were running a managed MySQL service, I wouldn't give anybody SUPER, either, so their security model makes sense, even if it is sometimes unwieldy.

If all of your objects have the same definer, a workaround would be to restore the dump using that account instead of the one you're using now, but that seems unlikely.

Deleting just the line with the DEFINER declaration should make the dumpfile work in cases where it appears on a line by itself, or you could use sed or perl to modify the file... an idea that I already know you're not fond of, but it really is a nice thing about MySQL that such hackery is quite legitimate, and not really all that far afield from the kinds of things I have to do as a DBA even in a non-RDS envirnoment.

perl -pe 's/\sDEFINER=`[^`]+`@`[^`]+`//' < oldfile.sql > newfile.sql

...possibly not the answer you hoped for, but you could run that against your dumpfile and should end up with a slightly more usable file.

In my case was the "CHANGE MASTER TO MASTER_LOG_FILE= ... " line in the dump that was giving me the error. This line was added by the "--master-data" option of mysqldump. In Amazon AWS you need to start replication by setting the master details with "mysql.rds_set_external_master" procedure instead read here

So I just toke notes of that line "head 22 backup.dump" where the line 22 reported in the error. Then remove it before importing, for my big file I use: "sed '22d' backup.dump > backup_clean.dump"