MYSQL HA-dengan ha proxy

 

MySQL High Available Cluster using HAProxy

https://medium.com/@yogi_r/mysql-high-available-cluster-using-haproxy-bc2c22ba8b5c

Yogesh

·

Follow

4 min read

A step by step guide to setup and configure a MySQL Highly Available(HA) cluster using semi-synchronous binary log file position based replication with one primary and multiple replicas. We will work with MySQL v5.7 database. Below steps assumes RHEL/CentOS installation.

Install MySQL database on the servers

yum install wget yum-utils
wget https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm
yum localinstall mysql80-community-release-el7–3.noarch.rpm
yum-config-manager — enable mysql57-community
yum-config-manager — disable mysql80-community
yum install mysql-community-server mysql-community-libs
systemctl start mysqld
systemctl status mysqld

Check for the MySQL root password from the log file. Change the root password on first login.

grep 'password' /var/log/mysqld.log
mysql -uroot -p

Edit the mysql configuration file /etc/my.cnf

[mysqld]
datadir=/opt/mysql/data
socket=/var/lib/mysql/mysql.sock
symbolic-links=0
max_connections=1000 #Default 151, is less
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
log_timestamps=SYSTEM
binlog-format=ROW
rpl_semi_sync_master_timeout=1000 # 1 second
master-info-repository=TABLE
relay-log-info-repository=TABLE
server-id=1 # Id needs to be unique for each MySQL database
innodb_flush_log_at_trx_commit=1
report-host=host.my.domain #Change the host FQDN
report-port=3306
rpl_semi_sync_master_timeout=5000 # 5 seconds
slave_net_timeout=4. #Default 60 (seconds) is high

If a Virtual Machine(VM) image is used as the MySQL database server, make sure the UUIDs are unique for all the databases in the cluster. i.e. Delete the auto.cnf file and restart the MySQL database, UUID will be recreated for the database.

rm /var/lib/mysql/auto.cnf

Enable Semi-Synchronous replication

Semi-synchronous replication guarantees that if the master crashes, all the transactions that it has committed have been transmitted to at least one replica and is preferred over asynchronous(default) replication.

As MySQL root, install the plugins on all MySQL databases and verify the plugin status.

INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE '%semi%';

Enable Replication

To configure a primary MySQL database server, as MySQL root

CREATE USER ‘repl’@’%.my.domain’ IDENTIFIED BY ‘password’
GRANT REPLICATION SLAVE ON *.* TO ‘repl’@’%.my.domain ‘;
SET GLOBAL rpl_semi_sync_master_enabled=1;
SET GLOBAL rpl_semi_sync_slave_enabled=0;
SET GLOBAL READ_ONLY=OFF;
FLUSH TABLES WITH READ LOCK;

Open a new mysql session, run the below query and take note of the LogFile and LogPosition.

SHOW MASTER STATUS\G

On the Replica servers, as MySQL root configure replication and verify the replication status. Use the LogFile and LogPosition from the above master status result.

stop slave;
reset slave;
SET GLOBAL READ_ONLY=ON;
CHANGE MASTER TO MASTER_HOST = 'host1.my.domain', MASTER_PORT = 3306, MASTER_USER = 'repl', MASTER_PASSWORD = '*****', MASTER_LOG_FILE = '<LogFile>', MASTER_LOG_POS = <LogPosition>, MASTER_CONNECT_RETRY = 1, MASTER_RETRY_COUNT = 86400 ;
SET GLOBAL rpl_semi_sync_master_enabled=0;
SET GLOBAL rpl_semi_sync_slave_enabled=1;
start slave;
show slave status\G

Configure Clients using HAProxy

Install HAProxy on clients(s). We will configure HAProxy to listen on port 6033 and connect to the Primary MySQL database on port 3306.

yum install haproxy

Configure rsyslog to enable HAProxy logging, edit /etc/rsyslog.conf

#Uncomment the below 2 lines 
$ModLoad imudp
$UDPServerRun 514#HAProxy log configuration, add this line
local2.* /var/log/haproxy.log

Restart rsyslog service

systemctl restart rsyslog

Ensure SELinux is not running on the server, so that HAProxy can listen on port 6033. If SELinux is enabled, then

setsebool -P haproxy_connect_any=1

Configure HAProxy file:- /etc/haproxy/haproxy.cfg

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/statsdefaults
 mode tcp
 log global
 option dontlognull
 option logasap
 no option log-separate-errors
 option tcplog
 retries 3
 timeout connect 2s
 timeout check 2s
 maxconn 3000listen mysql
 bind 0.0.0.0:6033
 maxconn 1000
 timeout server 3600s
 timeout client 3600s
 default-server port 3306 fall 3 inter 1s rise 2 downinter 1s on-marked-down shutdown-sessions
server master1 master1.my.domain:3306

Update the highlighted server name appropriately and start HAProxy service.

systemctl start haproxy

Configure application database connections to point to the HAProxy client(s). i.e. proxyhostname:6033, instead of MySQLhostname:3306
This is a one-time application configuration and don’t need to be changed in case there is a failure.

Test Graceful Failover

1. Shutdown current primary database

As MySQL root,

SET GLOBAL READ_ONLY=ON; # Stops new transactions on the primary
SET GLOBAL rpl_semi_sync_master_enabled=0;
SHOW MASTER STATUS;

Make note of the Log file, Log Position of the query result. Shutdown MySQL service.

systemctl stop mysqld

2. Choose a replica as a new primary
As MySQL root,

SHOW SLAVE STATUS\G

Confirm the Master_Log_file, Read_Master_Log_Pos to match the step1 query result. Confirm Seconds_Behind_Master = 0

3. Promote the replica as primary
On the chosen replica (e.g. host2) As MySQL root,

stop slave;
reset slave;
CHANGE MASTER TO MASTER_HOST = ‘host2.my.domain’
SET GLOBAL rpl_semi_sync_master_enabled=1;
SET GLOBAL rpl_semi_sync_slave_enabled=0;
SET GLOBAL READ_ONLY=OFF;
SHOW MASTER STATUS\G

Make note of the LogFile and LogPosition of the query result.

4. Reconfigure proxy
New MySQL primary database has been configured. Now, point the Proxy(ies) to the new MySQL primary. Below script can be used to reconfigure the proxy(ies). Update the highlighted hostnames as appropriate.

sed -i ‘s/master_old/master_new/g’ /etc/haproxy/haproxy.cfg
systemctl restart haproxy

Clients will automatically connect to the new MySQL primary database and continue operations.

5. Reconfigure old replica
On the other current replica, as MySQL root

SET GLOBAL READ_ONLY=ON;
stop slave;
reset slave;
CHANGE MASTER TO MASTER_HOST = 'host2.my.domain', MASTER_PORT = 3306, MASTER_USER = 'repl', MASTER_PASSWORD = '*****', MASTER_LOG_FILE = '<LogFile>', MASTER_LOG_POS = <LogPosition>, MASTER_CONNECT_RETRY = 1, MASTER_RETRY_COUNT = 86400 ; 
SET GLOBAL rpl_semi_sync_master_enabled=0;
SET GLOBAL rpl_semi_sync_slave_enabled=1;
start slave;
show slave status\G

Verify the replica status to be in sync with the primary.

6. Reconfigure old primary to a replica server
Important Note: Don’t restart the old primary if step4 is not completed. We don’t want clients to connect to the old primary server/now replica server. Start MySQL service.

systemctl start mysqld

As MySQL root, Run all the SQL statements as highlighted in step5

Conclusion

It is important to have pro-active monitoring on the MySQL HA cluster. Periodic monitoring of the replica status to ensure the replicas are in sync with the primary database is a must. When the replicas are in sync with the primary, we can achieve a quick return to operational status even during an unexpected primary failure.

References:

https://dev.mysql.com/doc/refman/5.7/en/replication-administration-status.htmlhttps://dev.mysql.com/doc/refman/5.7/en/binlog-replication-configuration-overview.htmlhttps://dev.mysql.com/doc/refman/5.7/en/replication-semisync.htmlhttps://www.haproxy.com/blog/the-four-essential-sections-of-an-haproxy-configuration/

Semi Synchronous

Haproxy