0

We are moving a production site to EC2/RDS

Followed these instructions: http://geehwan.posterous.com/moving-a-production-mysql-database-to-amazon

I have set up row-based binary logging on the production server did a:

mysqldump --single-transaction --master-data=2 -C -q -u root -p > backup.sql

then imported to RDS instance. No dramas.

Due to the size of the db, and minimal downtime requirements, I've got to update the ec2 db to the latest datas via the binlogs, and it won't let me.

mysqlbinlog mysql-bin.000004 --start-position=360812488 | mysql -uroot -p -h

and it says:

ERROR 1227 (42000) at line 6: Access denied; you need (at least one of) the SUPER privilege(s) for this operation

My guess, based on what is on line 6 of the binlog, is that it's the 'write to the BINLOG' statements in the SQL backup, and because RDS doesn't support this, it can't run these statements, or something, I don't really know.

Please help.

timoxley
  • 133
  • 1
  • 7

1 Answers1

2

Most likely this is happening because mysqlbinlog writes BINLOG command as the first command to the output, and execution of that command requires SUPER privileges that you don't have on RDS.

To drop that command use --base64-output=NEVER option (note, it'll require also --read-from-remote-server --host=localhost --user username --password).

However after you overcome this problem, there's likely to another one, this time with @@session commands that mysqlbinlog writes to the output. They also require SUPER privileges.

I haven't found a better way that to use sed -i '/SET @@session/d' binlog_dump.sql to remove them and then execute import into MySQL.

Ivan Kuznetsov
  • 136
  • 3
  • 1
    Thanks I ended up doing it the most slow, stupid but reliable way possible: just taking the site down, taking a backup and moving the data in one big chunk. Thanks though, I have been wondering how people get around this for nearly a year now. – timoxley Jun 07 '12 at 10:25