How to set up a MySQL load balancer with HAProxy and replication

Setting up a load-balanced MySQL environment with HAProxy and replication improves performance and reliability. HAProxy acts as a load balancer, routing requests between the master and slave databases. This setup is useful for distributing read operations across multiple slave servers while the master server handles write operations.

In this configuration, MySQL replication keeps the data in sync between the master and the slaves. The master sends changes to the slaves, which replicate the data. This setup is typically used for scaling read-heavy applications.

The following steps guide you through setting up a MySQL master-slave replication with HAProxy. This includes configuring the master and slave databases, enabling replication, and setting up HAProxy for load balancing.

1. Install MySQL on the master and slave servers.

   $ sudo apt-get install mysql-server

2. Enable binary logging and set the server-id on the master server.

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

3. Restart the MySQL service.

   $ sudo systemctl restart mysql

4. Create a user with replication privileges on the master server.

   CREATE USER 'repl_user'@'%' IDENTIFIED BY 'password';
   GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'%';
   FLUSH PRIVILEGES;

5. Set a unique server-id on each slave. Connect the slave to the master using the binary log position.

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

6. Restart MySQL on the slave.

   $ sudo systemctl restart mysql

7. Configure replication on the slave.

   CHANGE MASTER TO MASTER_HOST='master_ip', MASTER_USER='repl_user', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=0;
   START SLAVE;

8. Check the replication status on each slave.

   SHOW SLAVE STATUS\G;

   Ensure that Slave_IO_Running and Slave_SQL_Running are both set to Yes.

9. Install HAProxy to act as a load balancer for the MySQL servers.

   $ sudo apt-get install haproxy

10. Modify the HAProxy configuration to distribute queries across the master and slave servers.

    $ sudo nano /etc/haproxy/haproxy.cfg

11. Add the following configuration.

    frontend mysql_front
        bind *:3306
        default_backend mysql_back
    
    backend mysql_back
        balance roundrobin
        server mysql1 192.168.0.101:3306 check
        server mysql2 192.168.0.102:3306 check

12. Start HAProxy and check if it is balancing traffic correctly between the master and slave databases.

    $ sudo systemctl start haproxy

13. Test the load balancer by connecting to the MySQL cluster.

    $ mysql -h haproxy_ip -u root -p