This article outlines the basic configurations for setting up and deploying Percona XtraDB Cluster 8.0 (PXC) on Amazon EC2, as well as what is new in the setup compared to Percona XtraDB Cluster 5.7.
What is Percona XtraDB Cluster an ideal fit for?
Percona XtraDB Cluster is a cost-effective, high-performance clustering solution for mission-critical data. It combines all the improvements, and functionality found in MySQL 8 with Percona Server for MySQL‘s Enterprise features and Percona’s upgraded Galera library.
A Percona XtraDB Cluster environment is an ideal fit for applications requiring 5-9s uptime with high read workloads; industries like financial or healthcare businesses that require in-house or externally dedicated database resources.
How is a three-node cluster configured in an EC2 environment?
In order to describe the setup procedures, I’ll be using Amazon EC2 instances to build the environment, and based on the business requirements, we may utilize alternative infrastructures to build the cluster environment.
Amazon EC2 settings are designed to provide uptime and high availability. When designing architecture in an EC2 environment, it is preferable to have one node situated in another Availability Zones to avoid the loss of an entire AZ and its data.
If a different region is planned for a node, we can prevent the loss of the entire region and its data. It is desirable that nodes and regions have appropriate network connectivity because network latency between the two regions affects synchronous replication write latency. Alternatively, an async replica in a different region is an option.
I’m not going into too much depth on Amazon EC2 to keep this blog brief and readable.
To build the three-node Percona XtraDB 8.0 cluster environment we first spin up the following three nodes in EC2. I’m using Amazon Linux but you can also use Ubuntu or any of the Percona-supported operating systems.
It is advised that a cluster’s nodes all have the same configuration.
PXCNode1 IP Address: 172.31.18.119
PXCNode2 IP Address: 172.31.86.114
PXCNode3 IP Address: 172.31.26.152
To install the Percona repository on all three nodes, use the following command.
1 | $ sudo yum install -y https://repo.percona.com/yum/percona-release-latest.noarch.rpm |
Enable the Percona Server for MySQL 8.0 repository in all three nodes by running the following command.
1 2 3 4 5 6 | $ sudo percona-release setup pxc-80 * Disabling all Percona Repositories * Enabling the Percona XtraDB Cluster 8.0 repository * Enabling the Percona Tools repository <*> All done! $ |
Using the following command, install the Percona XtraDB Cluster packages and software on all three nodes.
1 | $ sudo yum install percona-xtradb-cluster |
Before starting the nodes, update the basic variables listed below for the nodes
The following default variables must be modified with the first installations. Those that came from PXC 5.7 might wonder why wsrep_sst_auth is missing. The wsrep_sst_auth variable was removed in PXC 8 since it causes security concerns, as the user and password are saved in the config file and are easily visible to OS users.
In PXC 8, a temporary user is created when a new node joins the existing cluster. For additional details on this security enhancement, check this article.
1 2 3 4 5 6 7 8 9 10 11 12 13 | $ vi /etc/my.cnf .. ######## wsrep ############### wsrep_provider=/usr/lib64/galera4/libgalera_smm.so wsrep_cluster_address=gcomm://172.31.18.119,172.31.86.114,172.31.26.152 wsrep_slave_threads=8 wsrep_log_conflicts wsrep_node_address=172.31.18.119 wsrep_cluster_name=pxc-cluster #If wsrep_node_name is not specified, then system hostname will be used wsrep_node_name=pxc-cluster-node-1 wsrep_sst_method=xtrabackup-v2 .. |
Percona XtraDB Cluster nodes utilize the following ports by default, so we must open them and ensure that the nodes can communicate with one another.
1 2 3 4 5 6 7 | 3306 is used for MySQL client connections and SST (State Snapshot Transfer) via mysqldump. 4444 is used for SST via Percona XtraBackup. 4567 is used for write-set replication traffic (over TCP) and multicast replication (over TCP and UDP). 4568 is used for IST (Incremental State Transfer). |
For example, to test access.
1 2 3 4 5 6 7 | Node 1 # socat - TCP-LISTEN:4444 hello Node 2 # echo "hello" | socat - TCP:172.31.18.119:4444 # |
How is the first node bootstrapped?
After configuring each PXC node, you must bootstrap the cluster starting with the first node. All of the data that you wish to replicate to additional nodes must be present on the first node.
Run the command to bootstrap the first node.
1 2 | # systemctl start [email protected] # |
Since this is a brand new install, a temporary password is generated for the ‘root’ MySQL user which we can find in the mysqld.log
1 2 3 | # grep -i "A temporary password is generated " /var/log/mysqld.log 2022-09-13T06:52:37.700818Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: PmqQiGf*#6iy # |
Reset the temporary password using the following alter.
1 2 3 4 | $ mysql -uroot -p Enter password: mysql> SET PASSWORD = 'GdKG*12#ULmE'; Query OK, 0 rows affected (0.03 sec) |
How can the cluster’s remaining nodes be joined?
Before starting node2, you must copy the SSL certificates from node1 to node2 (and to node3). PXC 8 by default encrypts all replication communication, so this is a critical step that most users miss, causing cluster startup failures.
1 2 | PXCNode1# scp /var/lib/mysql/*.pem 172.31.86.114:/var/lib/mysql/ PXCNode2# chown -R mysql.mysql /var/lib/mysql/*.pem |
Start the node.
1 2 | PXCNode2# systemctl start mysql # |
Verify the following SET and make sure they appear as below once the node has been added to the cluster.
1 2 3 4 5 6 7 8 9 10 11 12 | PXCNode2$ mysql -uroot -p -e " show global status where variable_name IN ('wsrep_local_state','wsrep_local_state_comment','wsrep_cluster_size','wsrep_cluster_status','wsrep_connected','wsrep_ready');" Enter password: +---------------------------+---------+ | Variable_name | Value | +---------------------------+---------+ | wsrep_cluster_size | 2 | | wsrep_cluster_status | Primary | | wsrep_connected | ON | | wsrep_local_state | 4 | | wsrep_local_state_comment | Synced | | wsrep_ready | ON | +---------------------------+---------+ |
The third node may be added to the cluster using the same procedures, and its status will then look as follows.
1 2 3 4 5 6 7 8 9 10 11 12 | PXCNode3$ mysql -uroot -p -e " show global status where variable_name IN ('wsrep_local_state','wsrep_local_state_comment','wsrep_cluster_size','wsrep_cluster_status','wsrep_connected','wsrep_ready');" Enter password: +---------------------------+---------+ | Variable_name | Value | +---------------------------+---------+ | wsrep_cluster_size | 3 | | wsrep_cluster_status | Primary | | wsrep_connected | ON | | wsrep_local_state | 4 | | wsrep_local_state_comment | Synced | | wsrep_ready | ON | +---------------------------+---------+ |
Additional supporting factors
Additionally, the use of failover technologies like ProxySQL, which assist in removing failing nodes from the active read pool in some cases and shifting the primary, is advised.
It is advised to have a backup in place, with the open-source tool Percona XtraBackup taking physical copies of the dataset that are significantly faster to recover. It is strongly advised to back up binary logs using mysqlbinlog in order to do point-in-time recovery. Backups should be encrypted, compressed, and transferred to S3 as soon as possible.
To copy binlogs to an s3 bucket the command looks like this.
1 2 3 | aws s3 sync /backups/binlogs/ s3://backup-bucket/ upload: ../../../../backups/binlogs/binlog.000001.gz.gpg to s3://backup-bucket/binlog.000001.gpg |
For query analytics and time-based database performance insights, the open-source tool Percona Monitoring and Management is highly recommended. Using the Amazon Marketplace, this needs to be deployed on a separate host. This monitors the operating system and MySQL metrics and provides sophisticated query analytics.
That’s a really useful article. I am thinking to implement it for a web hosting service. What would you suggest as better for load balancing on production environment? Is it proxsql or AWS elastic load balancer?
You may use either elastic load balancer or proxysql depending on the application. Proxysql can provide flexible balancing depending on query regexp.