PSOrbit MySQL Cluster

Mysql Cluster Setup

Percona XtraDB Cluster Setup

Cluster Configuration

  • Cluster Name: psorbit-pxc01
  • Node Details:
    1. psorbit-node01
    2. psorbit-node02
    3. psorbit-node03

Ensure the above hostnames are resolvable via DNS or added to the /etc/hosts file on all cluster nodes.

Setup Steps

1. Open Required Ports

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

2. Configure the Initial Bootstrap Node

  1. Set Cluster-Specific Variables
    Update /etc/mysq/mysql.conf.d/mysqld.conf:

    wsrep_cluster_name=psorbit-pxc01
wsrep_node_name=psorbit-node01
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"

  2. SST Configuration
    Add the following in the [sst] section:

    [sst]
encrypt=4

  3. Start the Bootstrap Node

    systemctl restart mysql@bootstrap

  4. Check Error Logs For the mysql log file path /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.

3. Configure Joining Nodes

  1. 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=psorbit-pxc01
wsrep_node_name=psorbit-node02  # Change for each node
wsrep_cluster_address=gcomm://psorbit-node01,psorbit-node02,psorbit-node03
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"

  2. Place Certificates
    Ensure the SSL certificates (server-key.pem, server-cert.pem, ca.pem) are located in /etc/mysql/certs.

  3. Restart MySQL Service

    systemctl restart mysql

You can check the status of cluster size after joined one node.

4. Validate the Cluster

  1. 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 3
    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               | 3                   
| wsrep_evs_state                  | OPERATIONAL                                                                                                                                    |
| wsrep_incoming_addresses         | psorbit-node01:3306,psorbit-node02:3306, psorbit-node03:3306                                                          |
| wsrep_cluster_weight             | 3                      
| wsrep_local_state_comment        | Synced

  2. Ensure wsrep_cluster_size equals 3, indicating all nodes are connected.

5. Handling Failures

  1. If the cluster fails to bootstrap or a node is marked as unsafe, edit grastate.dat on the node that was last active:

    vi /data/db/pxc/mysql/grastate.dat

    Set safe_to_bootstrap to 1:

    safe_to_bootstrap: 1

    Restart the node as a bootstrap node:

    systemctl restart mysql@bootstrap

  2. Use this process to recover after a full cluster shutdown.

6. Finalizing the Setup

  1. After all nodes are joined, update the cluster address on the bootstrap node: 
    wsrep_cluster_address=gcomm://psorbit-node01,psorbit-node02,psorbit-node03
  2. Restart the MySQL service on the bootstrap node: 
    systemctl restart mysql

AppArmor Issues

If permissions errors occur:

  1. Disable AppArmor profiles: 
    aa-disable usr.sbin.mysqld
aa-disable usr.bin.wsrep_sst_xtrabackup-v2
systemctl reload apparmor
  2. Verify AppArmor status: 
    aa-status | grep mysqld
aa-status | grep wsrep
    Example Ouput 
    No Ouput
Important Notes
  • Ensure all nodes have synchronized system clocks using ntp or chrony.
  • Regularly monitor the cluster health using wsrep status variables.