fbpx

replication

HA Proxy For MySQL Master – Slave

by

There are scenarios where we have to provide the high ability to MySQL database instances and we use the Master and Slave replication method of MySQL database.

In the same case to segregate the Read and Write database traffic. We widly use HA- Proxy. It is a feature rich open source Load blancing tool with many unique features like reverse proxy but in out case we are going to use it only for Hight aviliblity purpose.

				
					root@haproxy01:~# haproxy -v
HA-Proxy version 2.0.13-2ubuntu0.3 2021/08/27 - https://haproxy.org/

 

How to Install it?

You simply use yum or apt commands to install it

				
					sudo apt install -y haproxy

 

Check  the version 

 

 

 

 

Install Mysql Client for HA Proxy Node to communicate with mysql master and slave databases.

 

				
					apt-get install -y mysql-client
cd /etc/haproxy/
cp haproxy.cfg haproxy.cfg_org
vim haproxy.cfg

				
			
				
					root@haproxy01:~# cat /etc/haproxy/haproxy.cfg
global
	log 127.0.0.1 local0 notice
        log /dev/log    local0
  	user haproxy
	group haproxy

	# Default SSL material locations

defaults
	log global
	mode tcp
        option tcplog
	retries 2
	timeout client 30m
	timeout connect 4s
    	timeout server 30m
	timeout check 5s

listen stats
        mode http
        bind *:9201
        stats enable
        stats uri /stats
        stats realm Strictly\ Private
        stats auth admin:admin

listen mysql-cluster
       bind *:3306
       mode tcp
       option mysql-check user haproxy_user
       balance roundrobin
       server master 192.168.56.205:3306 check
       server slave1 192.168.56.206:3306 check

listen mysql-cluster1
    bind 192.168.1.208:3306
    mode tcp
    option mysql-check user haproxy_user
    balance roundrobin
    server mysql-1 192.168.1.205:3306 check
    server mysql-2 192.168.1.206:3306 check

Create HA proxy user on mysql01/205 on primary node

				
					GRANT ALL PRIVILEGES ON *.* TO 'haproxy_root'@'%' IDENTIFIED BY 'Oracle@123' WITH GRANT OPTION;

flush privileges;

Test the configuration and it should start without error & Target should come up on GUI

				
					haproxy -f /etc/haproxy/haproxy.cfg -db

systemctl restart haproxy.service

Check HA proxy GUI and see all the MySQL target is up and running fine using HA Proxy Admin link:

HA Proxy Link Structure:

http://<localhost or IP/HostName/stats

http://192.168.1.208:9201/stats

Default Credentials : 

UserName : admin

Password: admin

Read More...

How to Rebuild MongoDB Replica-Set Node Fast in Few Minutes

by

Sometimes it happens that the MongoDB replica set node goes out of sync from replica set members and due to high lag, it cannot synchronise itself from the replica set members.

There are traditional ways to rebuild the node, like removing the replica set node from the cluster and adding it back, but the issue arises when your database size is in TBs, and node sync takes hours or days. Also, in some cases, it degrades the performance. You use the following steps and rebuild your replica set member in a few minutes.

Step1

Remove the node from cluster

				
					Prod:PRIMARY> rs.remove("<IP Address or hostname of the node to be removed>:27017")
Step2

login to ssh to Secondary Node and delete all the content of mongodb directory folder 

Step3

Log in to the Primary node. If you don’t know who is primary run this command rs. slave(ok)  from any node and then run rs.status(). rs. status will give you the [rimary node IP address or Hostname. Just ssh or login to primary and shut the MongoDB services

				
					--Stop MongoDB

--linux 
service mongod stop
or 
sudo systemctl stop mongodb

or 

mongod --dbpath /path/to/your/db --shutdown


-- Windows
net stop MongoDB

--MongoDB prompt
> use admin
> db.shutdownServer();

or 

mongo --eval "db.getSiblingDB('admin').shutdownServer()"

-- Mac

ps -ef| grep -i mongo
kill -9 <pid></pid>
Step 4

Zip/tar the MongoDB data folder from the Primary node and Copy or SCP to the secondary node Data folder according to mongo.conf. And start the MongoDB services of primary node

Step 5

Start the MongoDB process and if it fails check the MongoDB log. If required inrse the MongoDB service timeout to 800 (TimeoutStartSec=800) and reload the process using systemctl daemon-reload. Once MongoDB services are running fine add the node from the primary. After node addition check synchronization delay using rs. printslavereplicationinfo() and once it’s zero you are GOOD!!!.

				
					Prod:PRIMARY> rs.add("<IP Address or hostname of the node to be removed>:27017")


rs.printslavereplicationinfo()

Read More...