Load Balancer for MySQL MASTER MASTER Replication

Question

We are having typical requirement.

We are having MySQL MASTER-MASTER Replication setup in place. We require to setup Load Balancer between these 2 MySQL servers.

I am aware of setting up WEB based Load Balancer, but we require load balancer for MySQL servers.

Requirement is there are one Application server and 2 DB servers, Application server will connect to Load Balancer, Load balancer will connect to DB servers by sharing the load.

Please let me know the best approach for this, also let me know if there are any pros and cons.

Please note that on a regular replication M-M setup you should only write to one server at a time, or write to different datasets. Otherwise, you may break the replication due to change collisions in an asynchronous way. — jynus, Apr 21 '15 at 08:18

da_didi · Answer 1 · 2015-01-07T07:48:13.270

2

As you already have a running Master-Master replication, here a tip for the load balancer: I use HAProxy

It is a free software balancer, needs very less system resources and is quite easy to configure.

A tip: Install 2 loadbalancers with keepalived or keep in mind that you just created a critical bottleneck.

I use the setup keepalived + 2x haproxy + 2x master-mysql for 9 months now and I only noticed one problem with pdo_mysql - you can see my problem here: https://stackoverflow.com/questions/26871221/slow-pdo-mysql-mysqli-with-haproxy

There is also a small example config (in the example it is a hot standby master)

edited Jan 07 '15 at 07:48

answered Jan 07 '15 at 06:47

da_didi

301
2
12

I need Load Balancing at DB level, not at application level. That is one application connects to LB and LB sends request to DB. So will HAProxy supports this? and what are the limitations using this? – Phanindra Jan 07 '15 at 07:04
Yes, HAProxy supports this. Give it a try! – da_didi Jan 07 '15 at 07:14
Can you please list out any limitations on using LB directly on MySQL servers like Transaction limitations, so that we will take care of these.. – Phanindra Jan 07 '15 at 07:24
I updated my answer – da_didi Jan 07 '15 at 07:48

score 0 · Answer 2 · answered Jan 06 '15 at 20:50

Lest start with the advantage for Master-Master replication: allows data to be copied from either server to the other one, it adds redundancy and increases efficiency when dealing with accessing the data, high availability . Now about Disadvantage : cost ( instead of using one machine you will be using two machine with high speed connection).

Requirements: two servers ( you can use one server but it is not recommended unless its for testing)

How to setup: lets say you have machines one m1 with IP 1.1.1.1 and machine two m2 with IP 2.2.2.2

1) login to M1 you need to edit the cnf file for it by adding or comment\un-comment the following:

server-id               = 1
log_bin                 = /var/log/mysql/mysql-bin.log
binlog_do_db            = MyDB     -- if you want the replication for one db, comment this if you want to replicate all the SID
# bind-address            = 127.0.0.1

then restart the SID for M1 and log in to MySQL using the command

mysql -uroot -P[port] -p[password]

2) create a replica user using the following command

create user 'replicator'@'M2' identified by 'password'; -- note you can use the IP instead of the host name

3) Next grant the replication privliges to the replica user using:

grant replication slave on *.* to 'replicator'@'M2';

4) check the master status to get the log id ** after finish configuration the server one M1 you need to move to server 2 M2 (the same thing you did for server one but you need to make sure to change the host names as needed):

5) edit the cnf file but make sure the server id it not the same as M1

server-id               = 2
    log_bin                 = /var/log/mysql/mysql-bin.log
    binlog_do_db            = MyDB     -- if you want the replication for one db, comment this if you want to replicate all the SID
    # bind-address            = 127.0.0.1

6) restart the service

7) create a replication user and provide it with the replication privliges

create user 'replicator'@'M1' identified by 'password'; 
grant replication slave on *.* to 'replicator'@'M1';

now lets configure the master for M2:

slave stop; 

    CHANGE MASTER TO MASTER_HOST = '1.1.1.1', MASTER_USER = 'replicator', MASTER_PASSWORD = 'password', MASTER_LOG_FILE = 'mysql-bin..xxxxx', MASTER_LOG_POS = xxx; 
    slave start;

8) now check the master status

9) then go back to the M1 and start the load balance:

slave stop; 

    CHANGE MASTER TO MASTER_HOST = '2.2.2.2', MASTER_USER = 'replicator', MASTER_PASSWORD = 'password', MASTER_LOG_FILE = 'mysql-bin.xxxxx', MASTER_LOG_POS = xxx; 
    slave start;

Ahmad, We have already setup MySQL MASTER-MASTER replication. We require information on Load balancing on these 2 MySQL servers. — Phanindra, Jan 07 '15 at 06:24

Load Balancer for MySQL MASTER MASTER Replication

2 Answers2