MySQL Forums
Forum List  »  Synchronization

Master-Master Resyncronization Problem
Posted by: j sarma
Date: September 22, 2012 11:48PM

I have two InnoDB's set up in "circular" replication so that each is a slave to the other. I'm doing this for failover purposes, so clients will only write to one at a time which should prevent sync problems. It seems to work well in most scenarios, but I'm trying to really kick the tires and find out if I can recover from synchronization errors before I go live. Here's the test I'm doing that results in the sync error that I'm having trouble recovering from:

1) Assume we have the following simplified table definition and we're properly synced:

table clients (
id INT(11) PRIMARY KEY,
name VARCHAR(255)
);

2) insert record (id=1, name=X)

3) stop slave on both servers so communication stops between them.
4) On server A, update clients set name='A' where id=1
5) On server B, update clients set name='B' where id=1
6) start slaves on both machines.
7) Amusingly enough they've swapped records. ie: server A has id=1, name='B'. Server B has id=1, name='A'.

I don't find this sync error surprising, because they both read each other's relay messages. But now I'm trying to create a procedure to recover from this scenario.

Recovery Procedure:

1) issue "service mysql stop" on both servers.
2) scp -pr user@serverA:/var/lib/mysql/dbname user@serverB:/var/lib/mysql/
3) fix permissions on the new files on server B with chown mysql:mysql
4) issue service mysql start on both machines

Strangely enough, after doing this, server B still has name='A'. You would think overwriting the data files would cause them to both have name='B'. My suspicion is it's rereading the binlogs, but I don't understand why it would do that since it's already read them once, and I haven't reset their positions. I've tried deleting the relay logs on both machines before restart mysql, but that didn't help either

The only thing I did try that worked is using mysqldump instead of copying the data files. But I'd rather not do this because it needs to be an automated process, and I don't want to have to feed mysqldump the password at the cmdline, because that's insecure.

I have innodb_file_per_table enabled.

Can someone please tell me what is wrong with this procedure and how it can be corrected?

Thank you all for your time.

-Justin

Options: ReplyQuote


Subject
Views
Written By
Posted
Master-Master Resyncronization Problem
3104
September 22, 2012 11:48PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.