12

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

2
  • Sorry - forgot to say that I've tried changing the log_bin_trust_function_creators parameter to 1, but still get the same error at line 7844.
    – dsl101
    Nov 19, 2013 at 11:21
  • Old post, but the first error you can avoid by saying --masterdata=2. The line will then be commented out in the dump.
    – Halfgaar
    Apr 8, 2015 at 10:10

2 Answers 2

27

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.

4
  • Many thanks for the comprehensive reply, and for confirming what I thought about sed / perl being the only solution. The app I'm using is highly customised Joomla, and whilst open-source I don't have the time / skill to analyse it to work out if it will move across to RDS well. My thought was 'try it and see' - but it seems like the first step is the hardest - in no small part due to Amazon's inadequate (in my view) migration tools. I agree on the security model, but if the default output of mysqldump creates so many problems, why don't they have a better solution? I will ask this of them too.
    – dsl101
    Nov 20, 2013 at 12:15
  • 1
    AWESOME. thank you so much for that perl script. my backup was 4+ GB and just opening it was a challenge. this saved my day. Jan 22, 2014 at 18:35
  • 2
    Great explanation, great answer, and great solution! Stupid definer.
    – rkaregaran
    Feb 6, 2014 at 2:24
  • 4
    Thanks so much for your perl one liner. It's the only one I've been able to find that actually works. Many others on these boards simply don't. I'd upvote again if I could.
    – lucian303
    Nov 16, 2015 at 16:56
1

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"

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .