MariaDB Load Balancing with HAProxy on Centos 7
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 haproxydefaults
log global
retries 2
timeout connect 3000
timeout server 5000
timeout client 5000listen 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