LinuxQuestions.org
LinuxAnswers - the LQ Linux tutorial section.
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Server
User Name
Password
Linux - Server This forum is for the discussion of Linux Software used in a server related context.

Notices

Reply
 
Search this Thread
Old 05-21-2010, 01:41 PM   #1
tdnnash25
Member
 
Registered: Apr 2009
Posts: 63

Rep: Reputation: 15
Question mysql master-master replication


Does anyone have any experience with mysql master-master replication? I have mysql master-slave replication setup and just read this: http://www.howtoforge.com/mysql_mast...er_replication

The article makes sense. But, if server A dies ... and server B's configuration contains the line: master-host=ip_of_master_a ... then failover isn't automatic is it?

I'm trying to find a mysql implementation in which failover is seamless. We have web servers using mysql ... they are both behind a load balancer. If server A dies, I want server B to automatically become the master and things resume as if nothing happened.
 
Old 05-21-2010, 02:01 PM   #2
EricTRA
Guru
 
Registered: May 2009
Location: Gibraltar, Gibraltar
Distribution: Fedora 20 with Awesome WM
Posts: 6,805
Blog Entries: 1

Rep: Reputation: 1290Reputation: 1290Reputation: 1290Reputation: 1290Reputation: 1290Reputation: 1290Reputation: 1290Reputation: 1290Reputation: 1290
Hello,

I've set up MySQL master-master replication following this guide from the same site little over a year ago and haven't had any problem yet. The replication works perfectly. Before putting it in a production environment I tested both servers when one node going down, leaving it down for two days and then just rebooting it. And the replication kicked off like a charm. I've set it up so that we can have one of both nodes failing for a maximum of 7 days.

Furthermore the databases that are replicated are used by a web application, running in a high availability cluster with load balancing. We have our users connecting to a virtual IP which is 'covering' the http service and distributes the load on two nodes. Between those two nodes I have set up the MySQL master-master replication. At any given time I can see how many users are connected to each node. Everything that the users do, gets saved into the database of the server on which they are connected and almost instantly replicated to the other node.

If you like I can post the configuration tomorrow so you can have a look for yourself. I have the complete procedure I followed documented.

Kind regards,

Eric
 
Old 05-21-2010, 02:49 PM   #3
tdnnash25
Member
 
Registered: Apr 2009
Posts: 63

Original Poster
Rep: Reputation: 15
Quote:
Originally Posted by EricTRA View Post
Hello,

I've set up MySQL master-master replication following this guide from the same site little over a year ago and haven't had any problem yet. The replication works perfectly. Before putting it in a production environment I tested both servers when one node going down, leaving it down for two days and then just rebooting it. And the replication kicked off like a charm. I've set it up so that we can have one of both nodes failing for a maximum of 7 days.

Furthermore the databases that are replicated are used by a web application, running in a high availability cluster with load balancing. We have our users connecting to a virtual IP which is 'covering' the http service and distributes the load on two nodes. Between those two nodes I have set up the MySQL master-master replication. At any given time I can see how many users are connected to each node. Everything that the users do, gets saved into the database of the server on which they are connected and almost instantly replicated to the other node.

If you like I can post the configuration tomorrow so you can have a look for yourself. I have the complete procedure I followed documented.

Kind regards,

Eric
Eric, that would be incredibly helpful. Your setup sounds much like mine. So, if Server A bites the dust, do you have to do anything on Server B? Or does the my.cnf config take care of all of that, thus making it seamless?
 
Old 05-21-2010, 03:09 PM   #4
EricTRA
Guru
 
Registered: May 2009
Location: Gibraltar, Gibraltar
Distribution: Fedora 20 with Awesome WM
Posts: 6,805
Blog Entries: 1

Rep: Reputation: 1290Reputation: 1290Reputation: 1290Reputation: 1290Reputation: 1290Reputation: 1290Reputation: 1290Reputation: 1290Reputation: 1290
Hi,

If one of the nodes goes down, I don't have to do anything. Up to now haven't had a node go down in our production environment but tested it extensively before putting it there. While I was testing, as stated before, I just shut one node down and left it powered off for two days. Just booted and checked everything. In a very short time period everything was replicated and synchronized. I use HeartBeat for the high availability part, ldirectord as load balancer (which I believe is now replaced by Pacemaker, but haven't had the time to check that out or test it so not putting it in our production environment), MySQL master-master replication, and my configuration files (for the web app) are synchronized using Unison Synchronizer, so that development only has to keep track of one location/set of files.

One thing more I tested was the 'split-brain' thing, I just forced a difference between the two MySQL databases meaning that they were not correctly replicated any more, I just broke the thing... And using the 'newest' database I was able to put it back in sync in 30 minutes using the same procedure as in the setup again.

I'm connecting to work tomorrow morning, so in about 10-12 hours I'll post the procedure I followed on our Debian servers.

Kind regards,

Eric
 
Old 05-22-2010, 01:55 AM   #5
EricTRA
Guru
 
Registered: May 2009
Location: Gibraltar, Gibraltar
Distribution: Fedora 20 with Awesome WM
Posts: 6,805
Blog Entries: 1

Rep: Reputation: 1290Reputation: 1290Reputation: 1290Reputation: 1290Reputation: 1290Reputation: 1290Reputation: 1290Reputation: 1290Reputation: 1290
Hello tdnnash25 and good morning,

As promised I'm posting the procedure I wrote for my colleagues at work who don't have any Linux experience so that in case of failure when I'm not there they have something to refer to (so it should make this little documentation 'fool proof' )

I changed the server names and IP addresses of course wherever necessary for the obvious reasons. I named the primary server SRV1, the secondary server SRV2, the IP numbers are structured XXX.XXX.XXX.SRV1_IP (and SRV2_IP). The virtual IP is identified as XXX.XXX.XXX.VIRT_IP. Of course you'll have to read through the document carefully, changing any and all database names, passwords and all of that where applicable (I didn't change any of the 4 database names that are replicated so you have the basics set for 4 databases).

You'll understand that this document comes with no guaranties at all that it will work in your environment. I've performed this setup little over a year ago on Debian 5 servers with MySQL 5 and it works like a charm. If you use another OS then of course you'll have to change the commands where necessary. Of course if you encounter problems or errors you can always PM me, drop me an email or post here on LQ.

Have fun with Linux and with this small tutorial.

Kind regards,

Eric

Last edited by EricTRA; 10-12-2010 at 03:22 AM.
 
Old 05-26-2010, 11:01 AM   #6
tdnnash25
Member
 
Registered: Apr 2009
Posts: 63

Original Poster
Rep: Reputation: 15
thanks!

Quote:
Originally Posted by EricTRA View Post
Hello tdnnash25 and good morning,

As promised I'm posting the procedure I wrote for my colleagues at work who don't have any Linux experience so that in case of failure when I'm not there they have something to refer to (so it should make this little documentation 'fool proof' )

I changed the server names and IP addresses of course wherever necessary for the obvious reasons. I named the primary server SRV1, the secondary server SRV2, the IP numbers are structured XXX.XXX.XXX.SRV1_IP (and SRV2_IP). The virtual IP is identified as XXX.XXX.XXX.VIRT_IP. Of course you'll have to read through the document carefully, changing any and all database names, passwords and all of that where applicable (I didn't change any of the 4 database names that are replicated so you have the basics set for 4 databases).

You'll understand that this document comes with no guaranties at all that it will work in your environment. I've performed this setup little over a year ago on Debian 5 servers with MySQL 5 and it works like a charm. If you use another OS then of course you'll have to change the commands where necessary. Of course if you encounter problems or errors you can always PM me, drop me an email or post here on LQ.

Have fun with Linux and with this small tutorial.

Kind regards,

Eric
Thanks Eric; I understand all of the risks. I plan on implementing this in a Stage environment prior to rolling it out to production. I will probably begin to do this today. Thanks again. I will definitely get back with you if I have any questions.
 
Old 05-26-2010, 11:15 AM   #7
EricTRA
Guru
 
Registered: May 2009
Location: Gibraltar, Gibraltar
Distribution: Fedora 20 with Awesome WM
Posts: 6,805
Blog Entries: 1

Rep: Reputation: 1290Reputation: 1290Reputation: 1290Reputation: 1290Reputation: 1290Reputation: 1290Reputation: 1290Reputation: 1290Reputation: 1290
Hello,

No problem, whatever problem you encounter, I'm sure we'll find a solution for it. Keep us up to date please. Also, you might want to look at Pacemaker instead of ldirecdord if you plan on doing load balancing. Pacemaker is the replacement for ldirectord. I still haven't had time to look into it but plan on doing so shortly.

Kind regards,

Eric
 
Old 06-21-2010, 08:52 PM   #8
tdnnash25
Member
 
Registered: Apr 2009
Posts: 63

Original Poster
Rep: Reputation: 15
Just curious; is master-master replication instantaneous? That is a major concern of ours. If we are load balancing our public web server and a thousand users hit server A and a mysql insert occurs...well, we need to make sure that server B has that change immediately. Thoughts? Concerns? Proof?
 
Old 06-22-2010, 02:10 AM   #9
EricTRA
Guru
 
Registered: May 2009
Location: Gibraltar, Gibraltar
Distribution: Fedora 20 with Awesome WM
Posts: 6,805
Blog Entries: 1

Rep: Reputation: 1290Reputation: 1290Reputation: 1290Reputation: 1290Reputation: 1290Reputation: 1290Reputation: 1290Reputation: 1290Reputation: 1290
Hi,

In my situation the synchronization works like a charm, I don't have time to switch screens and hit the up arrow and enter to check if the query was executed (synchronized). But then again, that's my situation. I only have about 350 users (internal and external) that connect through http and https both from LAN and WAN, so that's not even close to the amount of users you're serving.

We use eGroupware a lot in this load balanced, high available service and when I open two tabs in Chrome, one for each real server, thus bypassing the virtual IP and load balancer, I have whatever I add on one server listed on the second one when I switch tabs and refresh the view. So I'd say that's pretty fast.

At this moment, not the busyest time of the day, these are some statistics on MySQL from Nagios:
Code:
Uptime: 29159 Threads: 12 Questions: 145914 Slow queries: 0 Opens: 489 Flush tables: 6 Open tables: 256 Queries per second avg: 5.4
Highest Queries per second I've had during this month was 18.6.

There are various ways you can test your installation out before putting it into production. You could use some kind of stress test program:
http://www.opensourcetesting.org/performance.php
http://dev.mysql.com/doc/mysqltest/1...s-test-pl.html
or you could write a script that executes lots of queries on one server and check the synchronization on the other end.

In the output of the SHOW SLAVE STATUS\G; command in MySQL you also have the option
Code:
Seconds_Behind_Master: 0
which you could monitor to find out if a node is falling behind on replication.

This site also has some good information and pointers that might be of use (blog of the writers of High Performance MySQL).

Hope this helps out a bit.

Kind regards,

Eric

Last edited by EricTRA; 06-22-2010 at 02:13 AM.
 
Old 06-22-2010, 10:29 AM   #10
tdnnash25
Member
 
Registered: Apr 2009
Posts: 63

Original Poster
Rep: Reputation: 15
Quote:
Originally Posted by EricTRA View Post
Hi,

In my situation the synchronization works like a charm, I don't have time to switch screens and hit the up arrow and enter to check if the query was executed (synchronized). But then again, that's my situation. I only have about 350 users (internal and external) that connect through http and https both from LAN and WAN, so that's not even close to the amount of users you're serving.

We use eGroupware a lot in this load balanced, high available service and when I open two tabs in Chrome, one for each real server, thus bypassing the virtual IP and load balancer, I have whatever I add on one server listed on the second one when I switch tabs and refresh the view. So I'd say that's pretty fast.

At this moment, not the busyest time of the day, these are some statistics on MySQL from Nagios:
Code:
Uptime: 29159 Threads: 12 Questions: 145914 Slow queries: 0 Opens: 489 Flush tables: 6 Open tables: 256 Queries per second avg: 5.4
Highest Queries per second I've had during this month was 18.6.

There are various ways you can test your installation out before putting it into production. You could use some kind of stress test program:
http://www.opensourcetesting.org/performance.php
http://dev.mysql.com/doc/mysqltest/1...s-test-pl.html
or you could write a script that executes lots of queries on one server and check the synchronization on the other end.

In the output of the SHOW SLAVE STATUS\G; command in MySQL you also have the option
Code:
Seconds_Behind_Master: 0
which you could monitor to find out if a node is falling behind on replication.

This site also has some good information and pointers that might be of use (blog of the writers of High Performance MySQL).

Hope this helps out a bit.

Kind regards,

Eric
Again, thanks for your input. Does mysql officially support master-master?
 
Old 06-22-2010, 12:22 PM   #11
EricTRA
Guru
 
Registered: May 2009
Location: Gibraltar, Gibraltar
Distribution: Fedora 20 with Awesome WM
Posts: 6,805
Blog Entries: 1

Rep: Reputation: 1290Reputation: 1290Reputation: 1290Reputation: 1290Reputation: 1290Reputation: 1290Reputation: 1290Reputation: 1290Reputation: 1290
Hello,

You're welcome. Apparently MySQL officially support it according to this quote:
Quote:
Beginning with MySQL 5.1.18, it is possible to use MySQL Cluster in multi-master replication, including circular replication between a number of MySQL Clusters.
which was not officially supported before, from the MySQL documentation site.

Other references from the same site:
http://dev.mysql.com/doc/refman/5.1/...solutions.html
There even is a MySQL Master Master Replication Manager (MMM)
MMM
MMM 2

Kind regards,

Eric
 
Old 02-04-2011, 12:20 AM   #12
linuxnorks
LQ Newbie
 
Registered: Jan 2006
Location: Philippines
Posts: 5

Rep: Reputation: 0
Quote:
Originally Posted by EricTRA View Post
Hello tdnnash25 and good morning,

As promised I'm posting the procedure I wrote for my colleagues at work who don't have any Linux experience so that in case of failure when I'm not there they have something to refer to (so it should make this little documentation 'fool proof' )

I changed the server names and IP addresses of course wherever necessary for the obvious reasons. I named the primary server SRV1, the secondary server SRV2, the IP numbers are structured XXX.XXX.XXX.SRV1_IP (and SRV2_IP). The virtual IP is identified as XXX.XXX.XXX.VIRT_IP. Of course you'll have to read through the document carefully, changing any and all database names, passwords and all of that where applicable (I didn't change any of the 4 database names that are replicated so you have the basics set for 4 databases).

You'll understand that this document comes with no guaranties at all that it will work in your environment. I've performed this setup little over a year ago on Debian 5 servers with MySQL 5 and it works like a charm. If you use another OS then of course you'll have to change the commands where necessary. Of course if you encounter problems or errors you can always PM me, drop me an email or post here on LQ.

Have fun with Linux and with this small tutorial.

Kind regards,

Eric
Hi sir,
Can I also take a look at your procedure used? I find your post as a solution to my problem also.
 
Old 08-22-2011, 04:33 AM   #13
eehmke
LQ Newbie
 
Registered: Aug 2011
Distribution: Debian, Gentoo
Posts: 22

Rep: Reputation: 0
Quote:
Originally Posted by linuxnorks View Post
Hi sir,
Can I also take a look at your procedure used? I find your post as a solution to my problem also.
Me too. It seems your procedure is not in this forum anymore, at least I can't find it?
Chris
 
  


Reply

Tags
master, mysql, replication


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
LXer: Setting Up Master-Master Replication On Four Nodes With MySQL 5 On Debian Etch LXer Syndicated Linux News 0 11-20-2008 07:30 PM
LXer: Master-Master Replication With MySQL 5 On Fedora 8 LXer Syndicated Linux News 0 02-19-2008 05:40 AM
LXer: Setting Up Master-Master Replication With MySQL 5 On Debian Etch LXer Syndicated Linux News 0 10-25-2007 10:42 PM
MYSQL master-master replication monitor zafar466 Linux - General 0 10-06-2007 10:42 AM
LXer: MySQL Master Master Replication LXer Syndicated Linux News 0 10-08-2006 10:54 AM


All times are GMT -5. The time now is 09:21 AM.

Main Menu
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
identi.ca: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration