Linux - ServerThis forum is for the discussion of Linux Software used in a server related context.
Notices
Welcome to LinuxQuestions.org, a friendly and active Linux Community.
You are currently viewing LQ as a guest. By joining our community you will have the ability to post topics, receive our newsletter, use the advanced search, subscribe to threads and access many other special features. Registration is quick, simple and absolutely free. Join our community today!
Note that registered members see fewer ads, and ContentLink is completely disabled once you log in.
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.
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.
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?
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.
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.
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.
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.
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?
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.
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.
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.
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.
LinuxQuestions.org is looking for people interested in writing
Editorials, Articles, Reviews, and more. If you'd like to contribute
content, let us know.
mysql master-master replication
) 
