LinuxQuestions.org

LinuxQuestions.org (/questions/)
-   Linux - Server (http://www.linuxquestions.org/questions/linux-server-73/)
-   -   mysql master-master replication (http://www.linuxquestions.org/questions/linux-server-73/mysql-master-master-replication-809349/)

tdnnash25 05-21-2010 01:41 PM

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.

EricTRA 05-21-2010 02:01 PM

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

tdnnash25 05-21-2010 02:49 PM

Quote:

Originally Posted by EricTRA (Post 3976890)
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?

EricTRA 05-21-2010 03:09 PM

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

EricTRA 05-22-2010 01:55 AM

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

tdnnash25 05-26-2010 11:01 AM

thanks!
 
Quote:

Originally Posted by EricTRA (Post 3977310)
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.

EricTRA 05-26-2010 11:15 AM

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

tdnnash25 06-21-2010 08:52 PM

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?

EricTRA 06-22-2010 02:10 AM

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

tdnnash25 06-22-2010 10:29 AM

Quote:

Originally Posted by EricTRA (Post 4011039)
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?

EricTRA 06-22-2010 12:22 PM

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

linuxnorks 02-04-2011 12:20 AM

Quote:

Originally Posted by EricTRA (Post 3977310)
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.

eehmke 08-22-2011 04:33 AM

Quote:

Originally Posted by linuxnorks (Post 4247902)
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


All times are GMT -5. The time now is 10:49 AM.