MySQL master-master-%

I have three sites and I'd like the mysql databases to be replicated between all three sites. I would like them to all be writeable and then replicate to the other two servers.

I've been reading up on master-slave and it concerns me.  I've read about slaves getting out of sync.  Tools to make sure they are in sync would be nice too.

The servers are not in use in the middle of the night so I could run some tools that check db checksums and compare, then run tools to re-sync the db's between servers if needed.  I need a recommendation of what tools really work.

These aren't facebook servers, medium-lite to medium usage from 7-7pm Mon-Sat.

Thank you!
LVL 1
jasonsloganAsked:
Who is Participating?
 
arnoldConnect With a Mentor Commented:
You can setup a simple perl script that will connect to each database and run comparisons all day and all night. The problem is how do you know which is out of sync? Perhaps the one with the record is the one that did not get the delete record information.

Could you try looking at the show slave status to see whether there are replication errors?  Make sure to use explicit databasename.tablename in your sql statements.
This along with making sure that there are no replication errors will go a long way to get as close as possible to having the data current/valid without the need to use the methodology of taking two books and comparing them page by page, word by word and letter by letter versus checking the process logs to make sure there are no errors during the print run (out of ink, out of paper, paper jam, etc.)

But if you insist, http://stackoverflow.com/questions/225772/compare-two-mysql-databases points to a tool by redgate.


0
 
arnoldCommented:
http://onlamp.com/onlamp/2006/04/20/advanced-mysql-replication.html
http://www.howtoforge.com/mysql_master_master_replication

The important part is to use the offset and increment which will deal with avoiding data entry collision. This would happen on insert if care is not taken to make sure auto-incrementing columns are unique.
i.e. one will be adding 0,10,20,30 while the other will be 1,10,11,21.
To deal with synchronization is to check slave status to make sure there are no errors.
Managing the binary logs to make sure they do not accumulate to a point that your file system is full.
Another issue I've encounter with this before dealt with the sql statements not being explicit i.e. insert into table versus insert into databasename.table
Perhaps it is not an issue with the 5.1, 5.5 versions, but it at times would not replicate because it did not match the directive dealing with which db is being replicated.

If you have a circular replication and a failure that can not be easily updated i.e. small error that can be quickly corrected. You would need to reconfigure the node behind the failed node to get the data from the one before the failed node since it is the node that should have all the current data
A-> B -> C ->A
The change will become
A-> C -> A
The difficulty though is to have the application that relies on these databases to be adjusted accordingly such that node B instance of mysql does not get data written/read from until it is restored to operating state.
This example is explained in greater detail in the onlamp link.

MySQL has a clustered replication master/master setup example
http://dev.mysql.com/doc/refman/5.1/en/mysql-cluster-replication-multi-master.html
http://dev.mysql.com/doc/refman/5.5/en/mysql-cluster-replication-multi-master.html
0
 
jasonsloganAuthor Commented:
Thank you for your replay.

Are there any tools I can buy that will make sure all servers are in sync?
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
arnoldCommented:
the simple way is to check the status of the slave and the master to make sure they are on the same file and the slave instance does not have any errors.

I think the onlamp link provides the tools.

Are you looking for some tool that will make sure that will compare the databases on a regular basis?  This type of check will eat up you bandwidth.

You could setup that each system backup their database mysqldump and then diff the files.
0
 
jasonsloganAuthor Commented:
ok. Bandwidth isn't an issue because I can run it in the middle of the night if needed but diff'ing the files will tell me if there is a problem but not fix the slave.  I was hoping for tools that I could get or buy, either way, that would do checks at night and fix problems.
0
 
arnoldCommented:
The issue it is best to detect the error early and correct it "show slave status" versus trying to bring the databases into sync which require that the replication be terminated. Use the full backup from the node that has the most complete set of information and restore it on the subordinates, and then reset master/resume replication.
Did the onlamp discussion/article/post not cover what is being discussed?

0
 
jasonsloganAuthor Commented:
Yes but that's a painful process.  I can't believe there isn't a tool out there I can buy that can do these things for me then report to me the results.

I've done the manual stuff the whole time and now I would like something automatic that has reporting.
0
 
arnoldCommented:
You can setup the checking scripts and have them perform some of the transactions you would do manually.

i.e. you have a cron job on the node A check its slave status and then a script that will check the master node for information on the file being fed.

Convert the manual process you've been doing into an automated process with alerts.

Do you have/use system monitoring tools?
0
 
jasonsloganAuthor Commented:
Suggest any good ones? The only thing I've done is put a table is the test db then had a script run a query against that and if it pulls back information then it deems it as working.  Maybe I should do an insert then delete to check the server instead now that I think about it.  I've had servers in read-only when the hard drives are full.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.