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.
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.
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.
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.
Mysql DB is great, it’s free and has good performance. Also has good combination with Apache and PHP.
But we need to use multiple DB servers for High availability. And the each db clusters need to be synced and keep the same data for each server while it delivers or updates records.
Percona XtraDB could be great solution for it. I would like to present how to install percona XtraDB on 3 servers.
We need to have 3 DB servers with Ubuntu 18.04. The original documentation can be found on Percona website.
If you are using AWS, you will need to open the ports as well on security groups. Just like this.
Remove apparmor or Mysql
I strongly recommend do not install mysql before installing Percona DB. It will conflict and may not working properly. Also remove apparmor before installing.
sudo apt-get remove apparmor
Install Percona XtraDB package
Using below command, install package. During installation, you will need to set up root password.
Prepare same for all 3 servers. If you are using cloud service, like AWS, you may want to take snapshot and duplicate the Percona servers.
Login into all servers and stop mysql service.
sudo service mysql stop
Config mysql on master
I have prepared these 3 nodes. Percona1 – 10.0.21.1 Percona2 – 10.0.21.2 Percona3 – 10.0.21.3
And config mysql on master server(10.0.21.1). All servers will be set up as master, but at the beginning, we need to designate one server for master and others could be synced into it.
Set all node with same configuration but you will need to change wsrep_cluster_address for all your nodes and configure below line.
wsrep_node_name=pxc1
wsrep_node_address=10.0.21.1
Also set your sstuser and password for this line:
wsrep_sst_auth=sstuser:sstuser_password
Create SST user
Login to each nodes and login to mysql. Then create SST users.
CREATE USER 'sstuser'@'%' IDENTIFIED BY 'sstuser_password';
GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT ON *.* TO 'sstuser'@'%';
CREATE USER 'sstuser'@'localhost' IDENTIFIED BY 'sstuser_password';
GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT ON *.* TO 'sstuser'@'localhost';
FLUSH PRIVILEGES;
Open port from terminal
Double check if port is opened and it communicate each other.
SSL certificates are used within server and client to encrypt the traffic. This gives extra security for users accessing the application. Let’s Encrypt is one of free certificates that easily installed on your web servers.
Here’s how to install multiple domains on single apache web server.
Step 1 – Configure vhost file.
We need to prepare apache vhost configuration for SSL.
Create new vhost file with different name.
For example, save to /etc/apache2/site-available/test.com-ssl.conf
SSH, also known as Secure Socket Shell, is a network protocol that provides administrators with a secure way to access a remote computer. SSH also refers to the suite of utilities that implement the protocol. Secure Shell provides strong authentication and secure encrypted data communications between two computers connecting over an insecure network such as the Internet.
Without SSH, browser marked as “Not Secure” on the address line in Chrome. This is essential to have secure browsing.