MariaDB Load Balancing with HAProxy on Centos 7

Seal by Five Furlongs © Some rights reserved.

Tutorial on how to setup MariaDB 5.5 Load Balancing with HAProxy 1.5 on Centos 7 using 3 server instances (2 for MariaDB and 1 for HAProxy).

Prepare MariaDB Servers

On each MariaDB server, edit your /etc/hosts so that they can ping each others via the hostname.

10.140.0.1 mariadb1
10.140.0.2 mariadb2

On each MariaDB server, install mariadb-server package and harden the installation

# yum install mariadb-server
# systemctl start mariadb
# mysql_secure_installation

On mariadb1 server, edit your /etc/my.cnf file and on the top enter the following parameter:

[mysqld]
server-id=1
log-bin=mysql-bin

Then restart the service

# systemctl restart mariadb

Then login to MariaDB and create a user for replication slave followed by a flush privileges and the tables for read only access.

MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* to replicator@’%’ IDENTIFIED BY ‘secret’;
MariaDB [(none)]> FLUSH PRIVILEGES;
MariaDB [(none)]> FLUSH TABLES WITH READ LOCK;

At this point, if you do a show master status, you need to have a file with the mysql-bin incrementing number as well a position number.

MariaDB [(none)]> SHOW MASTER STATUS;
+ — — — — — — — — — + — — — — — + — — — — — — — + — — — — — — — — — +
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+ — — — — — — — — — + — — — — — + — — — — — — — + — — — — — — — — — +
| mysql-bin.000004 | 466 | | |
+ — — — — — — — — — + — — — — — + — — — — — — — + — — — — — — — — — +
1 row in set (0.00 sec)

On mariadb2, edit your /etc/my.cnf file and on top enter the following parameter:.

[mysqld]
server-id=2

Then restart the service

# systemctl restart mariadb

Back to mariadb1, dump the mysql database and import it to mariadb2

$ mysqldump -u root -p --database mysql > mysql.sql
$ scp mysql.sql user@mariadb2:~/

On mariadb2

$ mysql -u root -p --database mysql < mysql.sql 

Login to MariaDB and we will temporarily run mariadb2 as slave.

MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST=’mariadb1', MASTER_USER=’replicator’, MASTER_PASSWORD=’secret’, MASTER_LOG_FILE=’mysql-bin.000001', MASTER_LOG_POS=466;
MariaDB [(none)]> START SLAVE;

Value for the MASTER_LOG_POS is from the Position in the result when you run show master status. At this point, you would noticed that the Slave is running correctly by verifying with a show slave status

MariaDB [(none)] SHOW SLAVE STATUS;

Now that mariadb1 is the master and mariadb2 is the slave, we will perform the reverse order to reach our goal. At this level, you can unlock the tables (on mariadb1) which we did previously.

MariaDB [(none)] UNLOCK TABLE;

On mariadb2 (which is actually a slave), re-edit the my.cnf file and under the [mysqld] and server-id add the following parameter. It should look like this

[mysqld]
server-id=2
log-bin=mysql-bin

Then restart

# systemctl restart mariadb

Login to the MariaDB and execute a show master status

MariaDB[(none)] SHOW MASTER STATUS;
+ — — — — — — — — — + — — — — — + — — — — — — — + — — — — — — — — — +
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+ — — — — — — — — — + — — — — — + — — — — — — — + — — — — — — — — — +
| mysql-bin.000001 | 245 | | |
+ — — — — — — — — — + — — — — — + — — — — — — — + — — — — — — — — — +
1 row in set (0.00 sec)

On mariadb1, a show slave status will prompt you an empty set. Now since we have dump the mysql database to mariadb2, we can now just run mariadb1 as slave.

MariaDB[(none)] CHANGE MASTER TO MASTER_HOST=’mariadb2', MASTER_USER=’replicator’, MASTER_PASSWORD=’secret’, MASTER_LOG_FILE=’mysql-bin.000001', MASTER_LOG_POS=245;
MariaDBp(none)] START SLAVE;

Test by creating database and tables on both servers, it will replicate on each other.

Now login to one of the MariaDB server and create user for HAProxy

MariaDB[(none)] INSERT INTO mysql.user (Host,User) values (‘10.140.0.4’,’haproxy_check’); FLUSH PRIVILEGES;
NariaDB[(none)] GRANT ALL PRIVILEGES ON *.* TO ‘haproxy_root’@’10.140.0.4' IDENTIFIED BY ‘secret’ WITH GRANT OPTION; FLUSH PRIVILEGES

Please note that 10.140.0.4 is the HAProxy server IP.

Configuring HAProxy

Install HAProxy

# yum install haproxy

Backup the original configuration file

# mv /etc/haproxy/haproxy.cfg{,.original}

Create a new one

# vi /etc/haproxy/haproxy.cfg

Paste configuration below

global
    log 127.0.0.1 local0 notice
    user haproxy
    group haproxy
defaults
    log global
    retries 2
    timeout connect 3000
    timeout server 5000
    timeout client 5000
listen mysql-cluster
    bind 10.140.0.4:3306
    mode tcp
    option mysql-check user haproxy_check
    balance roundrobin
    server mysql-1 10.140.0.2:3306 check
    server mysql-2 10.140.0.3:3306 check

In my case, 10.140.0.4 is my HAProxy server IP.

Start HAProxy

# setsebool -P haproxy_connect_any=1
# systemctl start haproxy

Test connection to MariaDB servers through HAProxy

$ mysql -u haproxy_root -p -h 10.140.0.4

Testing Failover

Stop one of the MariaDB server service:

# systemctl stop mariadb

You should still be able to connect to MariaDB through HAProxy

$ mysql -u haproxy_root -p -h 10.140.0.4

Further Reading

• HAProxy
• Replication, Cluster, & Multi-Master
• Simple Master-Master replication on MariaDB
• How To Use HAProxy to Set Up MySQL Load Balancing