This document provides cluster installation of mysql, scylla and Redpanda.
Percona XtraDB Cluster Setup which is enhancement of Mysql
pssb-pxc01pssb1avm001pssb1avm002pssb1avm003pssb1avm004pssb1avm005Ensure the above hostnames are resolvable via DNS or added to the /etc/hosts file on all cluster nodes.
Ensure the following ports are open on all nodes:
3306: MySQL communication.
4444: State Snapshot Transfer (SST).
4567: Galera cluster communication.
4568: Incremental State Transfer (IST).
Example commands for UFW:
ufw allow 3306/tcp
ufw allow 4444/tcp
ufw allow 4567/tcp
ufw allow 4568/tcp
ufw reload
/etc/mysq/mysql.conf.d/mysqld.conf:
wsrep_cluster_name=pssb-pxc01
wsrep_node_name=pssb1avm001
wsrep_cluster_address=gcomm://
wsrep_provider_options="socket.ssl_key=/etc/mysql/certs/server-key.pem;socket.ssl_cert=/etc/mysql/certs/server-cert.pem;socket.ssl_ca=/etc/mysql/certs/ca.pem"
x1
2. SST Configuration
Add the following in the [sst] section:
[sst]
encrypt=4
systemctl restart mysql@bootstrap
/var/log/mysql/error.log. Here you can find the joining node status, which node it is selected as donor to get the data synced.Node-Specific Variables
Update /etc/mysq/mysql.conf.d/mysqld.conf on each joining node with unique node names and the complete cluster address:
wsrep_cluster_name=pssb-pxc01
wsrep_node_name=pssb1avm002 # Change for each node
wsrep_cluster_address=gcomm://pssb1avm001,pssb1avm002,pssb1avm003,pssb1avm004,pssb1avm005
wsrep_provider_options="socket.ssl_key=/etc/mysql/certs/server-key.pem;socket.ssl_cert=/etc/mysql/certs/server-cert.pem;socket.ssl_ca=/etc/mysql/certs/ca.pem"
Place Certificates
Ensure the SSL certificates (server-key.pem, server-cert.pem, ca.pem) are located in /etc/mysql/certs.
Restart MySQL Service
systemctl restart mysql
You can check the status of cluster size after joined one node.
Check Cluster Status
Execute the following on any node to verify the cluster size and status:
SHOW STATUS LIKE 'wsrep%';
wsrep_cluster_size should be 5
wsrep_local_state_comment should be synced or shows percentage of syncing process.
wsrep_cluster_status should be Primary
Example Ouput
| wsrep_cluster_status | Primary |
| wsrep_connected | ON
|
| wsrep_cluster_size | 5
| wsrep_evs_state | OPERATIONAL |
| wsrep_incoming_addresses | pssb1avm001:3306,pssb1avm002:3306,pssb1abm003:3306,pssb1avm004:3306,pssb1avm005:3306 |
| wsrep_cluster_weight | 5
| wsrep_local_state_comment | Synced
Ensure wsrep_cluster_size equals 5, indicating all nodes are connected.
If the cluster fails to bootstrap or a node is marked as unsafe, edit grastate.dat on the node that was last active:
vi /var/lib/mysql/grastate.dat
Set safe_to_bootstrap to 1:
safe_to_bootstrap: 1
Restart the node as a bootstrap node:
systemctl restart mysql@bootstrap
Use this process to recover after a full cluster shutdown.
wsrep_cluster_address=gcomm://pssb1avm001,pssb1avm002,pssb1avm003,pssb1avm004,pssb1avm005
systemctl restart mysql
If permissions errors occur:
aa-disable usr.sbin.mysqld
aa-disable usr.bin.wsrep_sst_xtrabackup-v2
systemctl reload apparmor
aa-status | grep mysqld
aa-status | grep wsrep
No Ouput
1. Update /etc/hosts on All Nodes
Add the new node’s hostname and IP address in /etc/hosts on all existing nodes:
echo "172.21.0.66 pssb1avm006" | sudo tee -a /etc/hosts
2. Update MySQL Configuration on All Nodes
Edit the MySQL configuration file (e.g., /etc/mysql/my.cnf or /etc/mysql/percona-xtradb-cluster.conf.d/) and update the gcomm:// line to include the new node:
wsrep_cluster_address=gcomm://pssb1avm001,pssb1avm002,pssb1avm003,pssb1avm004,pssb1avm005,pssb1avm006
Restart each node one by one, ensuring each node is up and synchronized before proceeding to the next:
sudo systemctl restart mysql
3. Prepare the New Node
Install MySQL if not already installed
Create the /etc/mysql/certs directory for SSL certificates:
sudo mkdir -p /etc/mysql/certs
sudo chown root:root /etc/mysql/certs -R
sudo chmod 644 /etc/mysql/certs -R
4. Copy SSL Certificates to the New Node
Copy server-key.pem, server-cert.pem, and ca.pem from an existing node to the new node’s /etc/mysql/certs/ directory.
5. Update MySQL Configuration on the New Node
Edit the configuration file to include the updated gcomm:// line:
wsrep_cluster_address=gcomm://pssb1avm001,pssb1avm002,pssb1abm003,pssb1avm004,pssb1avm005,pssb1avm006
Add SSL settings for secure communication:
wsrep_provider_options="socket.ssl_key=/etc/mysql/certs/server-key.pem;socket.ssl_cert=/etc/mysql/certs/server-cert.pem;socket.ssl_ca=/etc/mysql/certs/ca.pem"
6. Disable AppArmor Profiles
Disable AppArmor profiles for MySQL:
aa-disable usr.sbin.mysqld
aa-disable usr.bin.wsrep_sst_xtrabackup-v2
sudo systemctl reload apparmor
7. Start MySQL on the New Node
Start MySQL to join the cluster:
sudo systemctl start mysql
Verify Cluster Status
On any cluster node, check the cluster size:
SHOW STATUS LIKE 'wsrep_cluster_size';
Ensure the cluster size includes the new node.
Final Notes
journalctl or error.log to ensure successful synchronization./data/db/pxc/mysqlwsrep status variables.