How To Setup And Configure MySQL Percona Xtradb Cluster
With our websites increasingly richer with data, and apps that reach millions of users overnight, it often ain’t enough anymore to have a single database server handling all the traffic. Instead, using clusters of database servers becomes a viable variant, especially with virtual machine nodes becoming available for as little as 2.5$ a month for a single node.
Configuration of such clusters ain’t exactly a walk in the park though, and aim of this tutorial is to change that, well as much as possible at least...
In this tutorial, we will install and configure a cluster of database servers, running Percona XtraDB (Percona is a fully compatible, drop-in replacement for MySQL), version 5.7, and HAProxy high availability load balancing proxy server which will handle all the traffic directed to the database nodes.
What you will need for this, beside a little bit of experience with Linux operating system and some basics with networking, are four virtual machine nodes, which can be rented on Hetzner or DigitalOcean for as little as few bucks a month.
All nodes have to have an extra network adapter, for communication/replication between the nodes, and separation of nodes from the Internet for security reasons.
Create Virtual Machine Nodes
So, we will begin with creating a new virtual machine on Hetzner or Digital Ocean. Choose the smallest, least expensive node, with 2GM of RAM memory and a single vCPU, pick Debian 10 as operating system, and add your private key for authentication (this is very important, password authentication poses a great security risk today, which can be easily avoided by using private keys).
If you don’t have a private key, you can create one with the ssh-kegen application, built-in into every Linux box. Key will be automatically saved to the /home/your-account/.ssh directory, print it on the screen with cat command, and paste it/add it to the Hetzner account backend.
When creating a new virtual machine, do not forget to add an extra private network interface, which Digital Ocean or Hetzner will automatically add on the 10.0.0.0/24 subnet.
Once the virtual machine is up and running, log into it and install the nano editor, which is simplest to use for beginners, so let’s begin with:
Install Nano Editor On Unix
apt-get install nano
Once nano is installed, issue the following command:
apt-get -y update && apt-get -y full-upgrade
Install and Configure Percona XtraDB
Once the process is complete, reboot the machine and log in again.
Now we can continue with Percona XtraDB installation. In order for our nodes to be able to communicate, we will install, configure and enable the firewall:
apt-get install ufw
Once installed, add the following rules to it:
ufw default deny incoming ufw default allow outgoing ufw allow 2222/tcp ufw allow from 10.0.0.0/24
What we did here is the following:
- With first line, we forbid all incoming traffic to the node.
- With second line, we allowed all outgoing traffic from the node.
- With the third line, we “punched a hole” in our wall we built on the first line, allowing us to communicate with the node over SSH, which is secure enough due to only us having the private encryption key.
- With fourth line, we allowed all traffic on the private subnet, between the nodes themselves, which is fully separated from the public Internet and poses no risk whatsoever.
- This way, nodes will be able to synchronize between themselves and communicate on ports 873, 3306, 4444, 4567, 4568 and 9200.
In case you already had MySQL installed, you might have problems with the apparmor, and for that reason make sure its disabled by typing in:
apt-get remove apparmor
Now when we took care of that, we add a few prerequisites by issuing:
apt-get install -y wget gnupg2 lsb-release
Now download the repo:
And install it with:
dpkg -i percona-release_latest.generic_all.deb
We will now use percona’s scripts to add the repo:
percona-release enable pxc-80 release
Finish this with updating the apt with:
Now let’s search for the actual package:
apt search percona-xtradb-cluster root@testing:~# apt search percona-xtradb-cluster
Sorting... Done Full Text Search... Done percona-xtradb-cluster-5.6-dbg/unknown 5.6.46-28.38-1.bionic amd64 Debugging package for Percona XtraDB Cluster percona-xtradb-cluster-5.7-dbg/unknown 5.7.28-31.41-1.bionic amd64 Debugging package for Percona XtraDB Cluster percona-xtradb-cluster-56/unknown 5.6.46-28.38-1.bionic amd64 Percona XtraDB Cluster with Galera percona-xtradb-cluster-57/unknown 5.7.28-31.41-1.bionic amd64 Percona XtraDB Cluster with Galera percona-xtradb-cluster-client-5.6/unknown 5.6.46-28.38-1.bionic amd64 Percona XtraDB Cluster database client binaries percona-xtradb-cluster-client-5.7/unknown 5.7.28-31.41-1.bionic amd64 Percona XtraDB Cluster database client binaries percona-xtradb-cluster-common-5.6/unknown 5.6.46-28.38-1.bionic amd64 Percona XtraDB Cluster database common files (e.g. /etc/mysql/my.cnf) percona-xtradb-cluster-common-5.7/unknown 5.7.28-31.41-1.bionic amd64 Percona XtraDB Cluster database common files (e.g. /etc/mysql/my.cnf) percona-xtradb-cluster-full-56/unknown 5.6.46-28.38-1.bionic amd64 Percona XtraDB Cluster with Galera percona-xtradb-cluster-full-57/unknown 5.7.28-31.41-1.bionic amd64 Percona XtraDB Cluster with Galera percona-xtradb-cluster-garbd-5.7/unknown 5.7.28-31.41-1.bionic amd64 Garbd components of Percona XtraDB Cluster percona-xtradb-cluster-garbd-debug-5.7/unknown 5.7.28-31.41-1.bionic amd64 Debugging package for Percona XtraDB Cluster Garbd.
As can be seen, percona-xtradb-cluster-full-57 is the package, we are looking for, so we will now install it with:
apt-get install percona-xtradb-cluster-full-57
Installation script will ask for desired root password here, so please pick an enough secure one here, containing uppercase and lowercase characters, numbers and symbols.
Once the installation is done, it is time for us to configure the wsrep config file, where we will add the addresses of our nodes:
In this file, look for the line with:
Uncomment it (if commented) and add the addresses of our nodes, so that the line looks like this:
Off course, you will use the addresses your nodes received from the system. You can check them with a simple command:
Now look for the line that begins with:
Un-comment it and change the address to the IP of the current node, so that it looks like this:
And change the host name as well on the line:
Also, change the name of our cluster, on the line that says:
And change the following line, choosing appropriate password:
Once done, change these parameters on other two nodes as well, setting appropriately the node addresses and host names.
Once we are done with configuration, it is time to bootstrap our cluster, which is done just once, on our first node, with the following command:
Once bootstrapped and started, we need to add SST user to our database, which can be done after we log into our MySQL server with:
mysql -u root -p
Type in the root MySQL password you chose during installation, and once logged in issue the following:
CREATE USER 'sstuser'@'localhost' IDENTIFIED BY 'passw0rd';
While changing the password (in bold) to the one we used in our configuration file, on line:
Also, issue the following command:
GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT ON *.* TO 'sstuser'@'localhost';
And then flush privileges by:
FLUSH PRIVILEGES; exit;
Now we can proceed with other two nodes, where we will run a simple:
If everything went alright, we should now be able to check our cluster, by again logging to mysql server with: mysql -u root -p and issuing the following command:
'wsrep_cluster%';show status like
Output should look like something like this:
mysql> show status like 'wsrep_cluster%'; +--------------------------+--------------------------------------+ | Variable_name | Value | +--------------------------+--------------------------------------+ | wsrep_cluster_weight | 3 | | wsrep_cluster_conf_id | 3 | | wsrep_cluster_size | 3 | | wsrep_cluster_status | Primary | +--------------------------+--------------------------------------+ 5 rows in set (0.00 sec)
As we can see, our cluster numbers three nodes, which says everything goes according to plan.
We will now verify the replication, so while still logged in to MySQL, create a new database there with the following command:
create database foo;
Now log in to MySQL on another node, and list the database there with:
Output should now show us:
mysql> show databases; +---------------------------------+ | Database | +---------------------------------+ | information_schema | | foo | | mysql | | sys | +---------------------------------+ 4 rows in set (0.00 sec)
You can try and create a new database here as well:
create database bar;
And list the databases on the other node again, which should show us that now we have both foo and bar in our databases list:
mysql> show databases; +---------------------------------+ | Database | +---------------------------------+ | information_schema | | bar | | foo | | mysql | | sys | +---------------------------------+ 4 rows in set (0.00 sec)
We shall now enable remote access to the databases with following commands, issued while still logged in into MySQL server:
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'YOUR-ROOT-DB-PASSWORD' WITH GRANT OPTION; GRANT ALL ON *.* to root@'%' IDENTIFIED BY 'YOUR-ROOT-DB-PASSWORD'; GRANT ALL PRIVILEGES ON *.* TO root@'localhost' IDENTIFIED BY 'YOUR-ROOT-DB-PASSWORD'; GRANT ALL PRIVILEGES ON *.* TO root@'127.0.0.1' IDENTIFIED BY 'YOUR-ROOT-DB-PASSWORD'; GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'YOUR-ROOT-DB-PASSWORD' WITH GRANT OPTION;
Install MySQL load balancer
Now when our cluster is working correctly, it is time for us to set up the load balancer, which will check the nodes health and redirect the traffic appropriately.
We will start by installing xinetd with:
apt-get install xinetd
Once installed, edit the services list by issuing:
Scroll down to lines with ports in 9000 range and find the line that says:
Add a new line bellow it and add the following there:
mysqlchk 9200/tcp # mysqlchk
Save the file, and restart the xinetd daemon with:
services xinetd restart
You will need to do this on all three nodes.
Once you are done, deploy a new virtual machine, update the system and install nano as we did on other nodes, and then install the haproxy package with:
apt-get install haproxy
When HAProxy is done installing, we need to set its configuration file according to our needs, which we will do with:
Delete all there (or just rename the file and create a new one) and add the following:
global log 127.0.0.1 local0 log 127.0.0.1 local1 notice maxconn 4096 user haproxy group haproxy debug #quiet daemon defaults log global mode http option tcplog option dontlognull retries 3 option redispatch maxconn 2000 contimeout 5000 clitimeout 50000 srvtimeout 50000 frontend pxc-front bind 0.0.0.0:3307 mode tcp default_backend pxc-back frontend stats-front bind 0.0.0.0:22002 mode http default_backend stats-back backend pxc-back mode tcp balance leastconn option httpchk server database1 10.0.0.2:3306 check port 9200 inter 12000 rise 3 fall 3 server database2 10.0.0.3:3306 check port 9200 inter 12000 rise 3 fall 3 server database3 10.0.0.4:3306 check port 9200 inter 12000 rise 3 fall 3 backend stats-back mode http balance roundrobin stats uri /haproxy/stats
Change the bold lines with your own nodes addresses, save the file with CTRL+X and restart the haproxy with:
service haproxy restart
See if you can reach the nodes from the load balancer, by connecting with telnet to port 9200 (which is the port on which the load balancer checks the nodes health):
telnet 10.0.0.2 9200
This command should show you the clustercheck scripts output:
root@database3:~# clustercheck HTTP/1.1 200 OK Content-Type: text/plain Connection: close Content-Length: 40 Percona XtraDB Cluster Node is synced.
Only thing that’s left now is to sort out the firewall on this node, with following commands:
ufw default deny incoming ufw default allow outgoing ufw allow 2222/tcp ufw allow 3307/tcp ufw allow from 10.0.0.0/24
Reload and enable the firewall with
ufw reload ufw enable
And VOILA! Believe it or not, you now have a working High Availability MySQL database cluster, working on three nodes, with a HAProxy load balancer taking care of the load, checking nodes health, directing traffic to appropriate place! Happy querying. :-)