MySQL Replication from On-Premises to AWS

amazon-rdsawsmysql-5.5replication

I have an on-premises DB server running MySQL 5.5.37 with multiple databases.

I created database on the AWS RDS.

The idea is to set the replication from on-premises to the AWS, but for only one database.

So, I configured binlog and set binlog-do-db to use only one database which is needed to replicate. When slave is started on the AWS, I get an 1049 error.

Now, I guess the trouble is binlog_format, which is MIXED.

Does someone know is it possible to change binlog_format FROM MIXED to ROW and restart the master database or should I run the whole dumping process from the beginning?

Best Answer

You have to edi6t the my.ini/cnf and put following under the section mysqld

[mysqld]
binlog_format = ROW

If you add it under another section it will not work.

You have the right idea to flush first the logs and the switch at runtime to another format, and having the my.cnf changed.

FLUSH TABLES WITH READ LOCK;
FLUSH LOGS;
SET GLOBAL binlog_format = 'ROW';
FLUSH LOGS;
UNLOCK TABLES;

You should also check the manual for more information

ASPECT #1 : Replication

I don't think that

replicate-wild-do-table = db_backup.%
replicate-rewrite-db = db->db_backup

belong together.

Other people have wondered about this as well

Jun 14, 2012 : Configured MySQL replication but its not working
Jun 13, 2012 : What is the difference between 'replicate-rewrite-db' and 'replicate-do-db ' during mysql replication ?
May 31, 2012 : Mysql replicate-rewrite-db not working

The problem stems from the order replication rules are processed. According to the MySQL Documentation on Replication Rules:

If any --replicate-rewrite-db options were specified, they are applied before the --replicate-* filtering rules are tested.

Even the MySQL Documentation on replicate-rewrite-db says:

The database name translation is done before the --replicate-* rules are tested.

The replicate-wild-do-table is enforced after the rewrite. It would not be surprising if this ordering somehow imposed an INSERT into a table that has data already.

You are probably asking how did the data get there ?

ASPECT #2 : mysqldump

Doing mysqldump --single-transaction would seem to be the greatest way to point-in-time dumps of data. Unfortunately, mysqldump --single-transaction has an Achilles' Heel : ALTER TABLE. If a table is subject to any ALTER TABLE commands, such as a DROP TABLE and CREATE TABLE, that can break the integrity of the transaction the mysqldump was trying to do the dump in. Truncating a table (which is DDL in the MySQL Universe) and dropping and adding indexes can also be as disruptive.

You can find more information on that from MySQL Performance Blog's Best kept MySQLDump Secret. I actually addressed this point in a past question describing 12 commands that can break the integrity of a mysqldump's transaction : MySQL backup InnoDB

CAVEAT

Personally, I would switch binary-format to STATEMENT
Like I said in MySQL backup InnoDB, you should have another slave (without any replication filters) dedicated to doing mysqldumps.
63000+ seconds behind? You should look into doing parallel table dumps : How can I optimize a mysqldump of a large database?

EPILOGUE

One or both of the aspects may have contributed to letting a row slip in during the mysqldump that should not have existed due to either the rewrite rules or the isolation of the mysqldump being overridden.

SUGGESTIONS

I would do a mysqlbinlog dump of all the relay logs since the start of the mysqldump to see all INSERTs that the Slave will process and see if those rows already exist on the Slave. If they do, you could probably do two things:

1 : Skip all the Duplicate Key errors

Simply add this to my.cnf on the Slave

[mysqld]
slave-skip-errors=1062
skip-slave-start

and restart mysql. Then, run START SLAVE;

all the duplicate-key errors will get bypassed. When Seconds_Behind_Master gets to 0, remove those lines and restart mysql.

2 : Download percona tools

The tools you need are

Use these to find the differences in the Slave, and then correct them

Mysql – Can we disable binary logging for one specific session or user in AWS RDS

One solution proposed in https://forums.aws.amazon.com/thread.jspa?threadID=45258 requires setting backup retention period to zero. Which should mean no automatic backup! But you can still take manual snapshots.