High-Availability MySQL cluster with load balancing using HAProxy and Heartbeat.

Also you can split an incoming requests and take care about high load.In this example I will show creating a MySQL cluster from two master nodes, main idea is to create a pair of servers with same configurations and one virtual IP for taking requests..This cluster will continue working even after totally losing one of the nodes.We’ll use a two servers (virtual or bare metal) with pair of MySQL masters and a pair of HAProxy installed on them, the main virtual IP will be configured with Heartbeat. Please mind that in this example only one HAProxy be using in one time periode, the second HAProxy will be standing in hot reserve. MySQL servers will be utilized with round robin type of load balancing.The schema with two servers was choosen for making an example more simple. Of course if you have a additional servers you can create a more complicated configurations, putting HAProxy with Heartbeat in external LB cluster and so on. But anyway, this example will be a quite enough for building a strong looking DB cluster inside your project.0. Preparing.First we’ll need to choose few subnets for the MySQL replications and for the HAProxy with Heartbeat, better separate them and if your server have a few network interface put this subnets on different interfaces as well. – network for DB traffic192.168.0.1 IP for MySQL1, IP for MySQL2. – network for the Heartbeat & HAProxy. Virtual IP for taking requests, main IP for sever1, main IP for server2.The /29 subnets will be quite enough in fact :)1..Connect to each MySQL servers and create this user with IP from opposite server:server1# mysql -u root -pMariaDB> GRANT REPLICATION SLAVE ON *.* TO 'replicauser'@'' IDENTIFIED BY 'somestrongpassword';server2# mysql -u root -pMariaDB> GRANT REPLICATION SLAVE ON *.* TO 'replicauser'@'' IDENTIFIED BY 'somestrongpassword';Check that replicauser have access for each MySQL server.server1# mysql -u replicauser -p -h password: somestrongpasswordWelcome to the MariaDB monitor..Configuring HAProxy on both servers.In second stage we’ll install and configure two absolutely identical HAProxy on both of our servers, for balancing a incoming requests between MySQL servers.First we need to add additional user on our MySQL servers (user must be created without any password), this user will be used by HAProxy for checking a health status of MySQL servers.server1# mysql -u root -pMariaDB> CREATE USER 'haproxy_check'@'%';MariaDB> FLUSH PRIVILEGES;You can create this user on any of our MySQL servers, as we have a replication configured between them..Check that user was added, using this command:server1# mysql -u root -p -e "SELECT User, Host FROM mysql.user"Enter password: +—————+————-+| User | Host |+—————+————-+| haproxy_check | % || replicauser | || root | localhost |+—————+————-+server2# mysql -u root -p -e "SELECT User, Host FROM mysql.user"Enter password: +—————+————-+| User | Host |+—————+————-+| haproxy_check | % || replicauser | || root | localhost |+—————+————-+Also let’s create a user with root privileges, for making some test requests later:server1# mysql -u root -pMariaDB> CREATE USER 'haproxy_root'@'%' IDENTIFIED BY 'password';MariaDB> GRANT ALL PRIVILEGES ON *.* TO 'haproxy_root'@'%';Now it’s time for HAProxy installation:server1# apt-get install haproxyserver2# apt-get install haproxySave original config and create new one:server1# mv /etc/haproxy/haproxy.cfg{,.back}server1# vi /etc/haproxy/haproxy.cfgNext add this configuration on both servers:lobal user haproxy group haproxydefaults mode http log global retries 2 timeout connect 3000ms timeout server 5000ms timeout client 5000mslisten stats bind stats enable stats hide-version stats uri /stats stats auth statadmin:statadminpasslisten mysql-cluster bind mode tcp option mysql-check user haproxy_check balance roundrobin server mysql-1 check server mysql-2 checkAs you can see, both HAProxy services will use, shared IP address.. More details

Leave a Reply