mysql replication & load balancing

HuMan-BiEnG · 08-20-2010, 07:20 PM

Hello everybody ,,

i have a couples of questions ,as i always get help when i need from this great forum discussion ,

i'm going to implement mysql replication for the first time on two servers through master-to-master in active-passive mode

and i want to use a load balancer software with this replication system

this load balancing software have the ability to configure easily without making any troubles to point or direct database writes to the active server not to the passive server

mean that i want a load balancer software which can direct apache web server read requests to server B (passive server) or to A (active server) if it is not busy, while only direct apache web server write requests to server A

my question is which load balancing software that will help me in this situation cuz there are alot ,like pen, LVS, or ultramonkey

load balancing will be on apache httpd server only beside right directing write or read from database

ps : i'm still begining my search on using replication so i still doesnt have or get the complete idea of replication & didnt start my search on any load balancer software untill i find the one that fit for my case

thanks alot to everyone

EricTRA · 08-21-2010, 02:58 AM

Hello,

I have a similar environment setup with MySQL master-master replication and load balancing for websites that use either Tomcat or Apache, and it works great. I've used the ultramonkey package, being Heartbeat and ldirectord. The latter has been replaced by pacemaker but I still haven't upgraded (since it's working like it is there's no urgency).

I found it pretty easy to set the thing up but if you have questions just post them here and I'm sure someone will provide an answer.

Kind regards,

Eric

HuMan-BiEnG · 08-21-2010, 01:25 PM

Quote:

Originally Posted by EricTRA

Hello,

I have a similar environment setup with MySQL master-master replication and load balancing for websites that use either Tomcat or Apache, and it works great. I've used the ultramonkey package, being Heartbeat and ldirectord. The latter has been replaced by pacemaker but I still haven't upgraded (since it's working like it is there's no urgency).

I found it pretty easy to set the thing up but if you have questions just post them here and I'm sure someone will provide an answer.

Kind regards,

Eric

Hello Eric ,,

thanks alot man for your answer ,

as i said before i'm just begining this project & still search on implementing it in a good way as much as i could , really Eric you helped me by your answer cuz now i know what software for loadbalancing that i will use ,please man if you have any source or articles that would help me in this project please post it cuz it will help me so much ,

& if i have any questions about a problem i will renew this thread

thanks again Eric,

& accept my best regards man

EricTRA · 08-21-2010, 02:03 PM

Hello,

You're welcome! When I started with MySQL replication I got a lot of helpful information from this site: http://www.howtoforge.com. If you type MySQL Master Master in the search box of that site you'll see what I mean. All of the howtos on that site are easy to follow and you'll learn a lot of the workings by setting it up yourself.

And for the high availability and load balancing part you can check out this wiki: Linux HA which has all the necessary info or at least links to it.

Kind regards,

Eric

HuMan-BiEnG · 08-21-2010, 03:20 PM

Quote:

Originally Posted by EricTRA

Hello,

You're welcome! When I started with MySQL replication I got a lot of helpful information from this site: http://www.howtoforge.com. If you type MySQL Master Master in the search box of that site you'll see what I mean. All of the howtos on that site are easy to follow and you'll learn a lot of the workings by setting it up yourself.

And for the high availability and load balancing part you can check out this wiki: Linux HA which has all the necessary info or at least links to it.

Kind regards,

Eric

thank you alot Eric ,

accept my best regards man

EricTRA · 08-21-2010, 03:22 PM

Hi,

You're welcome, hope it works out like you want it too.

Kind regards,

Eric

HuMan-BiEnG · 08-27-2010, 05:22 AM

Hello Eric ,,

i readed more about LVS ,but stillnt finish yet & actually the problem is that i'm not professional in networking, so i have a question !!

which way for forwarding packets that i should use :
LVS-NAT
or
LVS-DR (Direct Routing)

another question

in this article

Code:

http://www.howtoforge.com/high_availability_loadbalanced_apache_cluster

the following lines

Code:

# Apache node 1: webserver1.example.com (webserver1) - IP address: 192.168.0.101; Apache document root: /var/www
# Apache node 2: webserver2.example.com (webserver2) - IP address: 192.168.0.102; Apache document root: /var/www
# Load Balancer node 1: loadb1.example.com (loadb1) - IP address: 192.168.0.103
# Load Balancer node 2: loadb2.example.com (loadb2) - IP address: 192.168.0.104

is that mean that i should have four servers ?
two for apache web servers & two for loadbalancer

thank you man ,,
& accept my best regards

quanta · 08-27-2010, 05:52 AM

Quote:

Originally Posted by HuMan-BiEnG

Hello Eric ,,

i readed more about LVS ,but stillnt finish yet & actually the problem is that i'm not professional in networking, so i have a question !!

which way for forwarding packets that i should use :
LVS-NAT
or
LVS-DR (Direct Routing)

http://www.austintek.com/LVS/LVS-HOW...O.LVS-NAT.html
http://www.austintek.com/LVS/LVS-HOW...TO.LVS-DR.html

In my opinion, you should begin with LVS-NAt first.

Quote:

Originally Posted by HuMan-BiEnG

another question

in this article

Code:

http://www.howtoforge.com/high_availability_loadbalanced_apache_cluster

the following lines

Code:

# Apache node 1: webserver1.example.com (webserver1) - IP address: 192.168.0.101; Apache document root: /var/www
# Apache node 2: webserver2.example.com (webserver2) - IP address: 192.168.0.102; Apache document root: /var/www
# Load Balancer node 1: loadb1.example.com (loadb1) - IP address: 192.168.0.103
# Load Balancer node 2: loadb2.example.com (loadb2) - IP address: 192.168.0.104

is that mean that i should have four servers ?
two for apache web servers & two for loadbalancer

thank you man ,,
& accept my best regards

Exactly. Let's see the diagrams in above links to figure out.

EricTRA · 08-27-2010, 06:22 AM

Hello,

I'm using NAT here and not direct routing, so couldn't tell you what's preferred. NAT works in my environment.

About the number of nodes, you read the howto for a 4 node cluster.
Here's one for two node cluster for Debian Etch (you can easily adapt it for your distro).

If you want to I can mail you my step-by-step guide for a two node cluster with MySQL master-master replication and Load Balancing on the webserver part. Just let me know and I'll send it to you. Mine also includes file synchronisation between the two nodes with Unison.

Kind regards,

Eric

HuMan-BiEnG · 08-27-2010, 09:33 AM

Hello Eric ,,

actually i jave read many articles & a book (some chapters from it) called Linux Cluster, but i feel that i still couldnt get the point yet

i thought that load balancer is installed on the same real server, not alone in another server

, or maybe i still need some time to understand the whole idea

really man ,offerring me this guide is a very kind & great help from you ,& will help me better to get the point quickly

could you please man send it to my email address

again thank you man
& accept my best regards

EricTRA · 08-27-2010, 09:44 AM

Hello,

You have the choice basically. At first when I set up the similar environment at work, I used two nodes just like you. I installed the HeartBeat package on both of them and set up the complete load balancing and MySQL master master replication on that cluster.

Later on as need arose to include other services to the load balancing scheme, I separated the load balancing from the two nodes (that were also the webservers) and installed and configured it on two separate servers.

At this time we have load balancing on 4 virtual services that have each 2 nodes (and two services with 4 nodes in the planning).

As you see you don't HAVE to install the load balancing part on the same servers or on separate ones, the choice is up to you.

I advice you to delete the email address from your post if you don't want to receive lots of spam. I already saved it so no problem there. And if you want to send an email to a LQ user you can do so by clicking on his/her username and select that option from the pull down menu.

I'll mail you the guide as soon as possible.

Kind regards,

Eric

HuMan-BiEnG · 08-27-2010, 12:57 PM

Hello Eric

,,

i get your file & many thanks man

Code:

As you see you don't HAVE to install the load balancing part on the same servers or on separate ones, the choice is up to you.

thanks Eric, as i told you i'm new in this project & this thing made me

, but i got it know because of you man

Code:

I advice you to delete the email address from your post if you don't want to receive lots of spam. I already saved it so no problem there. And if you want to send an email to a LQ user you can do so by clicking on his/her username and select that option from the pull down menu.

sorry man ,i'm new to this kind of forums & we always get the advise & know the right thing to do from kind expert people like you Eric
& i will remove it now

Code:

I'll mail you the guide as soon as possible.

yeah man i got it, you really helped me so much in this new project & all i can say is that i hope that you will always be the best in every thing you want

i will study & read it very well beside what i got from howforge & the ebook linux cluster, to get the complete point

thank you good & kind Eric
& accept my best regards to you man

EricTRA · 08-27-2010, 12:59 PM

Hi,

You're welcome, hope it works out like you want. Any question you might have you can post it here, I'm sure someone will pick it up.

Kind regards,

Eric

HuMan-BiEnG · 08-29-2010, 12:28 AM

Hello Eric ,,

how are you man

??

hope that everything with you is going very well ,,

actually you helped me so much & i read your file & learned & get the whole idea very well

but, i will use MASTER-MASTER Active-Passive not MASTER-MASTER Active-Active topology ,cuz of the problems that will be in MASTER-MASTER Active-Active as i'm not an expert in mysql, while i'm just system who is trying to live & work in the best way

you know that writes in mysql will be only in Active server, while reads will be in Passive server, & i want to know how to configure ultramonkey to direct write requests (to mysql) to Active server while directing read requests to both Active & Passive server ???

another question, about the physical network ,that heartbeat should use
what i will ask for the data center to do ?
connecting the two servers through serial cable or through Ethernet network card using cross cable ??

thank you alot for your help

& accept my best regards to you Eric

EricTRA · 08-29-2010, 12:59 AM

Hello

Can you clarify what problem in MySQL master master replication you are referring to when using an active-active setup? I've got that setup for over a year now with very little problems, so I'm curious to what you are referring to.

Also, what's the profit by using active-passive when all else is load balanced? I'm not the expert on MySQL neither but how do you write to one host and read from another? Isn't using MySQL on localhost for both web instances a more secure option? And consequently using the replication in an active-active environment more adequate to have high availability on all levels?

At least that's how I set it up, the only thing that gets load balanced in the environment as I set it up are the webserver sessions. The load balancer directs the web requests to the nodes, the nodes work on localhost level with MySQL, and the synchronisation between them is done as master master in an active-active.

If you want to set up a failover environment, so that only one server is active all the time and synchronize everything in a active-passive way, that's another case. But at that time you'll be writing and reading from the same database server and only replicating to the other to use in case the primary node fails.

Again, I'm not the MySQL expert so I'm all ears if I can learn something new.

Kind regards,

Eric