Galera Cluster is a very useful flavour of MySQL with its strengths in High Availability and easiness of provisioning new nodes and keeping the existing nodes in sync. We have a couple of blog posts covering different features of the Galera cluster. Quite common question we hear is: how to add a new Galera node to the cluster? What is the best way to do it? Can I avoid SST? Let’s take a quick look at this topic.
How to add a Galera node to the cluster?
Installing new PXC node
First of all we should start with deploying a new Galera node. Let’s say we have three node cluster and we want to add another node. The first step will be to, well, install the Galera. In our case we have three nodes of PXC running 8.0.25. We have another empty VM that will be used for another node. We need to install required software. For that we follow the instructions on the Percona website.
First, we install gnupg2
root@vagrant:~# apt-get install gnupg2 Reading package lists... Done Building dependency tree Reading state information... Done The following NEW packages will be installed: gnupg2 . . . Unpacking gnupg2 (2.2.19-3ubuntu2.1) ... Setting up gnupg2 (2.2.19-3ubuntu2.1) ... Processing triggers for man-db (2.9.1-1) ...
Then we download the percona-release package:
root@vagrant:~# wget https://repo.percona.com/apt/percona-release_latest.$(lsb_release -sc)_all.deb --2021-12-03 10:34:13-- https://repo.percona.com/apt/percona-release_latest.focal_all.deb Resolving repo.percona.com (repo.percona.com)... 141.95.32.160, 167.71.73.251 Connecting to repo.percona.com (repo.percona.com)|141.95.32.160|:443... connected. HTTP request sent, awaiting response... 200 OK Length: 11804 (12K) [application/x-debian-package] Saving to: ‘percona-release_latest.focal_all.deb’ percona-release_latest.focal_all.deb 100%[=======================================================================================================================================================================>] 11.53K --.-KB/s in 0s 2021-12-03 10:34:13 (312 MB/s) - ‘percona-release_latest.focal_all.deb’ saved [11804/11804]
Then we install it:
root@vagrant:~# sudo dpkg -i percona-release_latest.$(lsb_release -sc)_all.deb Selecting previously unselected package percona-release. (Reading database ... 41730 files and directories currently installed.) Preparing to unpack percona-release_latest.focal_all.deb ... Unpacking percona-release (1.0-27.generic) ... Setting up percona-release (1.0-27.generic) ... * Enabling the Percona Original repository <*> All done! ==> Please run "apt-get update" to apply changes * Enabling the Percona Release repository <*> All done! ==> Please run "apt-get update" to apply changes The percona-release package now contains a percona-release script that can enable additional repositories for our newer products. For example, to enable the Percona Server 8.0 repository use: percona-release setup ps80 Note: To avoid conflicts with older product versions, the percona-release setup command may disable our original repository for some products. For more information, please visit: https://www.percona.com/doc/percona-repo-config/percona-release.html
and use it to enable pxc-80 repository:
root@vagrant:~# percona-release enable pxc-80 * Enabling the Percona XtraDB Cluster 8.0 repository <*> All done! ==> Please run "apt-get update" to apply changes
Finally, update apt cache:
root@vagrant:~# apt-get update Get:1 http://repo.percona.com/percona/apt focal InRelease [15.8 kB] Hit:2 http://archive.ubuntu.com/ubuntu focal InRelease Get:3 http://security.ubuntu.com/ubuntu focal-security InRelease [114 kB] Get:4 http://repo.percona.com/prel/apt focal InRelease [9,779 B] Get:5 http://archive.ubuntu.com/ubuntu focal-updates InRelease [114 kB] . . . Reading package lists... Done Building dependency tree Reading state information... Done 101 packages can be upgraded. Run 'apt list --upgradable' to see them.
and install the PXC:
root@vagrant:~# apt install percona-xtradb-cluster
Configuring new PXC node
Once this is done, we have to make some changes in the MySQL configuration. Ideally, if you have access to the configuration files from another Galera node, you would use that one. This will help to avoid configuration discrepancies across the cluster. Last thing you want to do is to waste time on debugging performance issues only to find that they were caused by one node having incorrect configuration and impacting the rest of the cluster. If you do not have access to the existing configuration, Percona website has a template for the configuration file that you can use to configure the new PXC node. Generally speaking, what you want to set up is binary log configuration (ROW format), configure innodb_autoinc_lock_mode, define proper wsrep_provider path, set wsrep_sst_method, wsrep_cluster_name and wsrep_node_name. What is different from the template is that we want to configure wsrep_cluster_address and fill it with existing nodes. This list of nodes will be used for our new node as an entry point in joining the cluster. Galera node, when starting, will reach out to the nodes on the list trying to establish the communication and, later, decide on the donor.
In our case we just copied the existing configuration and made following changes:
wsrep_node_address=192.168.10.160 wsrep_cluster_address=gcomm://192.168.10.121,192.168.10.122,192.168.10.123,192.168.10.160 wsrep_node_name=192.168.10.160
Basically, we have replaced the original node’s IP or hostname in wsrep_node_address and wsrep_node_name with the new node’s IP. We also added a new IP to the wsrep_cluster_address.
What is also very important to check is any configuration related to XtraBackup – SST, by default, is done by XtraBackup and it requires credentials to be defined. You should look for [xtrabackup] section which may contain user and password credentials. It can also be defined in wsrep_sst_auth.
Another thing to check is SSL configuration. If you have it set up on your PXC, you may want to configure it on the new node as well, including copying the certificates. It is especially important if you encrypted intra-cluster communication. Without proper SSL setup new node won’t be able to join the cluster because it will not understand encrypted communications.
Once this is all done, we can start the new node. It should join the cluster, transfer the data and eventually become online.
root@vagrant:~# systemctl start mysql
We can follow the progress in the error log of MySQL:
2021-12-03T11:42:03.120254Z 0 [Warning] [MY-010084] [Server] options --log-slow-admin-statements, --log-queries-not-using-indexes and --log-slow-slave-statements have no effect if --slow-query-log is not set 2021-12-03T11:42:03.120739Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.25-15.1) starting as process 21641 2021-12-03T11:42:03.123243Z 0 [Warning] [MY-010068] [Server] CA certificate /etc/mysql/certs/server_ca.crt is self signed. 2021-12-03T11:42:03.123270Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel. 2021-12-03T11:42:03.123287Z 0 [Note] [MY-000000] [WSREP] New joining cluster node configured to use specified SSL artifacts 2021-12-03T11:42:03.123315Z 0 [Note] [MY-000000] [Galera] Loading provider /usr/lib/libgalera_smm.so initial position: d39a950e-5424-11ec-b260-caa9d61ed01f:1 2021-12-03T11:42:03.123337Z 0 [Note] [MY-000000] [Galera] wsrep_load(): loading provider library '/usr/lib/libgalera_smm.so' 2021-12-03T11:42:03.123727Z 0 [Note] [MY-000000] [Galera] wsrep_load(): Galera 4.7(8c375ca) by Codership Oy <info@codership.com> (modified by Percona <https://percona.com/>) loaded successfully. 2021-12-03T11:42:03.123765Z 0 [Note] [MY-000000] [Galera] CRC-32C: using 64-bit x86 acceleration. . . . 2021-12-03T11:42:03.677212Z 0 [Note] [MY-000000] [WSREP] Initiating SST/IST transfer on JOINER side (wsrep_sst_xtrabackup-v2 --role 'joiner' --address '192.168.10.160' --datadir '/var/lib/mysql/' --basedir '/usr/' --plugindir '/usr/lib/mysql/plugin/' --defaults-file '/etc/mysql/my.cnf' --defaults-group-suffix '' --parent '21641' --mysqld-version '8.0.25-15.1' --binlog 'binlog' ) 2021-12-03T11:42:04.504189Z 2 [Note] [MY-000000] [WSREP] Prepared SST request: xtrabackup-v2|192.168.10.160:4444/xtrabackup_sst//1 2021-12-03T11:42:04.504406Z 2 [Note] [MY-000000] [Galera] Check if state gap can be serviced using IST 2021-12-03T11:42:04.504571Z 2 [Note] [MY-000000] [Galera] Local UUID: d39a950e-5424-11ec-b260-caa9d61ed01f != Group UUID: 625a34e6-4784-11ec-899c-27548f28aad3 2021-12-03T11:42:04.504729Z 2 [Note] [MY-000000] [Galera] ####### IST uuid:d39a950e-5424-11ec-b260-caa9d61ed01f f: 0, l: 2938348, STRv: 3 2021-12-03T11:42:04.504913Z 2 [Note] [MY-000000] [Galera] IST receiver addr using ssl://192.168.10.160:4568 2021-12-03T11:42:04.505089Z 2 [Note] [MY-000000] [Galera] IST receiver using ssl 2021-12-03T11:42:04.505429Z 2 [Note] [MY-000000] [Galera] Prepared IST receiver for 0-2938348, listening at: ssl://192.168.10.160:4568 2021-12-03T11:42:04.510374Z 0 [Note] [MY-000000] [Galera] Member 0.0 (192.168.10.160) requested state transfer from '*any*'. Selected 1.0 (192.168.10.121)(SYNCED) as donor. 2021-12-03T11:42:04.510637Z 0 [Note] [MY-000000] [Galera] Shifting PRIMARY -> JOINER (TO: 2938348) . . . 2021-12-03T11:42:36.861354Z 0 [Note] [MY-000000] [Galera] Shifting JOINER -> JOINED (TO: 2938348) 2021-12-03T11:42:36.862550Z 0 [Note] [MY-000000] [Galera] Member 0.0 (192.168.10.160) synced with group. 2021-12-03T11:42:36.862572Z 0 [Note] [MY-000000] [Galera] Shifting JOINED -> SYNCED (TO: 2938348) 2021-12-03T11:42:36.862845Z 2 [Note] [MY-000000] [Galera] Server 192.168.10.160 synced with group 2021-12-03T11:42:36.862862Z 2 [Note] [MY-000000] [WSREP] Server status change joined -> synced 2021-12-03T11:42:36.862867Z 2 [Note] [MY-000000] [WSREP] Synchronized with group, ready for connections
As you can see, the node started, connected to the cluster and then received state transfer using SST. Eventually it got synchronized with the rest of the cluster and became available for connections.
SST is a process of transferring data between Galera nodes and it puts some load on the donor node. We can try to avoid it by provisioning the node with the data prior to joining the cluster. In one of the following blogs we are going to dig into this, discussing the options and the process.
