MySQL Balancing

This is a brief tutorial on how to set up a load-balancing failover cluster of 2 MySQL servers. Baseline data 2 freshly installed MySQL server. It is necessary to adjust the operation in such a way that, in a normal situation, queries are balanced between MySQL servers, in case of failure of one of the MySQL servers, all queries go to the second.



First you need to set up a master-master replication of MySQL. How to do this, sure to find no problem. So omit this step. To configure balancing, install the HAProxy package, socat (to view database statuses).
')
On one of the servers, we execute a query that will create a user to view the status of the database:

INSERT INTO mysql.user (Host, User) values ('haproxy_ip', 'haproxy'); FLUSH PRIVILEGES;



Now we configure HAproxy, the actual configuration itself, and then some comments:

 global log 127.0.0.1 local2 chroot /var/lib/haproxy pidfile /var/run/haproxy.pid maxconn 4000 user haproxy group haproxy daemon stats socket /var/lib/haproxy/stats mode 777 level admin listen mysql bind 0.0.0.0:3306 timeout connect 10s timeout client 1m timeout server 1m mode tcp option mysql-check user haproxy server server_db1 <FQDN >:3306 weight 1 check inter 1s rise 3 fall 1 server server_db2 <FQDN >:3306 weight 1 check inter 1s rise 3 fall 1 

option mysql-check user haproxy - sets the user to connect to mysql for verification.
server server_db1 <FQDN server>: 3306 weight 1 check inter 1s rise 3 fall 1 - sets the display name of the server, the server address, the number of checks before becoming unavailable and how many checks before switching to available.

And finally, to check the status of servers from the command line, you can use the command:

echo "show stat" | socat stdio /var/lib/haproxy/stats | cut -d, -f1,2,18,20,21


Author: Magvai69 System Administrator