• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 417
  • Last Modified:

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!
0
jasonslogan
Asked:
jasonslogan
  • 5
  • 4
1 Solution
 
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
 
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
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

 
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
 
arnoldCommented:
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

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now