I have posted how to install Percona XtraDB cluster previously. And I want to show how to use the DB clusters from web server.
To use the multiple nodes from one or multiple web servers, it need load balancer. There’s several LB but HAProxy is one of most popular and easy to install.
Here’s documentation from Percona how to set up HAProxy on Percona XtraDB clusters.
Let’s say there are Percona DB nodes synced each other and one web server.
10.7.13.81 web 10.7.13.91 node1 10.7.13.92 node2
And I would like to make web server connected to 2 db nodes.
Config and install HAProxy on web server
Download and Install HAProxy on web server.
sudo add-apt-repository ppa:vbernat/haproxy-1.8 sudo apt-get update sudo apt-get install haproxy
Then config HAProxy.
sudo vi /etc/haproxy/haproxy.cfg
Then add this below. We will use port 3307 and localhost to connect Mysql nodes. And HAProxy will routes the traffic to db nodes using port 3306.
frontend pxc-front bind *:3307 mode tcp default_backend pxc-back frontend stats-front bind *:80 mode http default_backend stats-back frontend pxc-onenode-front bind *:3306 mode tcp default_backend pxc-onenode-back backend pxc-back mode tcp balance leastconn option httpchk server node1 10.7.13.91:3306 check port 9200 inter 12000 rise 3 fall 3 server node2 10.7.13.92:3306 check port 9200 inter 12000 rise 3 fall 3 backend stats-back mode http balance roundrobin stats uri /haproxy/stats stats auth pxcstats:secret backend pxc-onenode-back mode tcp balance leastconn option httpchk server node1 10.7.13.91:3306 check port 9200 inter 12000 rise 3 fall 3 server node2 10.7.13.92:3306 check port 9200 inter 12000 rise 3 fall 3 backup
Install clustercheck on nodes
Install Clustercheck on each db nodes. Clustercheck is checking mysql health and display the status on web port 80. So that HAProxy knows which node is live and available.
First create clustercheckuser on mysql.
GRANT PROCESS ON *.* TO 'clustercheckuser'@'localhost' IDENTIFIED BY 'clustercheckpassword!' FLUSH PRIVILEGES;
Then download clustercheck from git repository and place into /usr/bin/clustercheck on node server.
git clone git@github.com:olafz/percona-clustercheck.git mv /root/clustercheck /usr/bin/clustercheck
The edit the downloaded file.
vi /usr/bin/clustercheck
Here’s important part, there’s typo in the programming where the mysql username and password recorded. Fix it like below, you can change the user name and password but it should be matched with the mysql user information created above. For me, this took an hour to find out this bug. No body reported this bug on the git repository although this is at least 3 years old.
MYSQL_USERNAME="${MYSQL_USERNAME:-clustercheckuser}" MYSQL_PASSWORD="${MYSQL_PASSWORD:-clustercheckpassword!}"
Add clustercheck in mysqlchk
Configure mysqlchk file and designate where is clustercheck file located. (/usr/bin/clustercheck)
vi /etc/xinetd.d/mysqlchk
# default: on # description: mysqlchk service mysqlchk { disable = no flags = REUSE socket_type = stream port = 9200 wait = no user = nobody server = /usr/bin/clustercheck log_on_failure += USERID only_from = 0.0.0.0/0 per_source = UNLIMITED }
Install xinetd
xinetd is service where we can monitor using port 80. Add mysqlchk service in the xinetd.
vi /etc/services
Then searching for xinetd and add after below.
mysqlchk 9200/tcp # MySQL check
We need to install xinetd if it is not installed.
sudo apt-get update -y sudo apt-get install -y xinetd
Start xinetd using below command.
sudo service xinetd start
You can check health status of nodes from web browser, port 9200.
http://10.7.13.91:9200/
http://10.7.13.92:9200/
Make sure the message saying: Percona XtraDB Cluster Node is synced.
If it says Percona XtraDB Cluster Node is not synced. then check if the clusteruser login information matched with mysql user and credential on the file (/usr/bin/clustercheck)
You can also check through terminal.
curl http://10.7.13.91:9200/
Connectivity from web server
From web server, check if the connection to db node is working through port 3306.
mysql -uyourmysqluser -p -P 3306 -h 10.7.13.91 -e "show variables like 'wsrep_node_name';"
If there’s no problem, also check connection using port 3307, through HAProxy.
mysql -uyourmysqluser -p -P 3307 -h 127.0.0.1 -e "show variables like 'wsrep_node_name';"
If everything works fine, you will see below and now mysql is connected using host 127.0.0.1 and port 3307.
