installing Percona XtraDB Cluster

We will first show the server list where we will install Percona XtraDB Cluster
below are details for DB server

the below server will be running rocky linux 8.9

setup prerequisites

before we start installing its advisable to turn off the SELINUIX and disable firewall to avoid in issue during adding nodes to the cluster

disable SELINUIX

• for this task you require to have root user or user with sudo privilege
• open config file using any favorite note tool for this purpose I will be using vim

vi /etc/selinux/config

the variable SELINUX should be equal to enforcing change it to disabled and save and exit using :wq!

do the same to all all nodes and proxysql server and reboot server once done to apply the SELINUIX changes
Disable firewall

To disable the firewall, first, stop the firewall service and then disable it to prevent it from auto-starting. In Rocky Linux, the firewall daemon is handled by firewalld.service, which is different in Debian-based systems such as Ubuntu.

systemctl stop firewalld.service
systemctl disable  firewalld.service

systemctl status   firewalld.service

make sure to disable firewall on all nodes and proxysql server

installing and configuring Percona XtraDB Cluster

We will stat installing Percona XtraDB Cluster on three nodes

We will install Perocona repositories
Disable MySQL RPM intended on OS repositories
Then install Percona XtraDB Cluster
We will use mysql_secure_installation to setup the root password
And then stop MySQL services on all nodes

note : below steps should be done on below nodes

1.install perocna repositories

$ sudo yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm

**2.disable MySQL RPM

sudo percona-release setup pxc-80

3. install XtraDB Cluster

sudo yum install percona-xtradb-cluster

4.configre root password

first start MySQL services using below command

systemctl start mysqld

once MySQL is started a temporary password is generated in mysql.log file
you can use the grep command to filter the log for root password

sudo grep 'temporary password' /var/log/mysqld.log

the run command mysql_secure_installation provide temp password you obtain
setup new password for root and follow along with prompt base on you desire setting you would like

after that stop MySQL services

systemctl stop mysqld

make sure you do the same steps on three MySQL nodes and ensure that stop mysqld services once you done with the setup

Configure nodes for write-set replication

ensure mysqld services are stopped on all three nodes

start configuration on prod-db01:

• open the my.cnf file:

sudo vi /etc/my.cnf

• setup the server-id to be different on each node, example node1 server-id=1 , node 2 server-id=2 , the variable will be already setup on my.cnf file , you just need to make sure to assign a unique number for each node
• wsrep_cluster_name=pxc-cluster: you can keep this name for the cluster or change it, but ensure this value is the same on all three nodes
• wsrep_cluster_address=gcomm:// put the ip our host name of the three node
• wsrep_node_name=pxc1 change name or keep the same don’t not make name duplicated on three nodes
• wsrep_node_address= but the ip of the node your configuring my.cnf file in our case its prod-db01
• setup the encryption by adding the below lines

[mysqld]
wsrep_provider_options=”socket.ssl_key=server-key.pem;socket.ssl_cert=server-cert.pem;socket.ssl_ca=ca.pem”
[sst]
encrypt=4
ssl-key=server-key.pem
ssl-ca=ca.pem
ssl-cert=server-cert.pem

Set up node 2 and node 3 in the same way: Stop the server and update the configuration file applicable to your system. All settings are the same except for wsrep_node_name and wsrep_node_address.

below is image on how my.cnf file confige should look for three node

prod-db01

prod-db02

prod-db03

moving SSL certificate

perocna xtradb cluster uses encryption method between nodes in the cluster

its require that certificate are match in three nodes
bydefault when we install perocna xtradbcluster it create certificate in data dir

go to data dir , by default its located in /var/lib/mysql

the certificate we need are one mentioned in my.cnf file under [sst] section

we will move the certificate of prod-db01 to db2 and db3 using scp

scp server-key.pem [email protected]:/var/lib/mysql/
scp ca.pem [email protected]:/var/lib/mysql/

scp server-cert.pem [email protected]:/var/lib/mysql/



scp server-key.pem [email protected]:/var/lib/mysql/
scp ca.pem [email protected]:/var/lib/mysql/

scp server-cert.pem [email protected]:/var/lib/mysql/

Bootstrap the first node

this step involve starting up the first node mysqld services with Bootstrap

use the following command on first node which is in our case its prod-db01

systemctl start [email protected]

login to mysql and use the below query which will show cluster size , in our case it will be 1 since we didn’t start remaining node

show status like 'wsrep%';

add the remaining nodes

to add the node to the cluster simple steps of starting MySQL service normally on prod-db02 & prod-db03
systemctl start mysqld

login to mysql and issue the below query it should say cluster size is 2

show status like 'wsrep%';

do the same steps on third node prod-db03

Once completed, the system should indicate that the cluster size is 3, meaning all three nodes are now connected to the cluster.

You can test replication by creating a database and checking if it replicates across all three nodes.

Installing and configuring proxySQL

Since the Percona XtraDB Cluster is set up to be active-active, it is best to set up load balancing to distribute the load across the three nodes. This can be achieved by setting up a separate server for load balancing, which will also handle the connections between the application and the database.

To achieve this, we will install ProxySQL and configure it for load balancing.

installing ProxySQL

to install ProxySQL we need to add perocna repo
by using the below command

yum install Percona-XtraDB-Cluster-client-

Next, we need to install the Percona-XtraDB-Cluster-client. I will download the RPM package directly from Percona using wget. Please visit the Percona website and obtain the correct package for your OS.

wget https://downloads.percona.com/downloads/Percona-XtraDB-Cluster-80/Percona-XtraDB-Cluster-8.0.36/binary/redhat/8/x86_64/percona-xtradb-cluster-client-8.0.36-28.1.el8.x86_64.rpm?_gl=1*b5c6np*_gcl_au*MTMxMzQzNzU4OC4xNzE3NDMzOTYx



yum localinstall  percona-xtradb-cluster-client-8.0.36-28.1.el8.x86_64.rpm

once done start proxySQL service

systemctl start proxysql.service

Configure ProxySQL

login to ProxySQL console using below command
note : default password is admin you can change in ProxySQL config

mysql -u admin -p -h 127.0.0.1 -P 6032

we will add three node ip address to database using the below format

INSERT INTO mysql_servers (hostname, port, hostgroup_id) VALUES ('node1_host', 3306, 10);
INSERT INTO mysql_servers (hostname, port, hostgroup_id) VALUES ('node2_host', 3306, 10);
INSERT INTO mysql_servers (hostname, port, hostgroup_id) VALUES ('node3_host', 3306, 10);

INSERT INTO mysql_servers (hostname, port, hostgroup_id) VALUES ('10.10.10.130', 3306, 10);
INSERT INTO mysql_servers (hostname, port, hostgroup_id) VALUES ('10.10.10.131', 3306, 10);
INSERT INTO mysql_servers (hostname, port, hostgroup_id) VALUES ('10.10.10.132', 3306, 10);

We will create a user called monitor on one of the nodes in the cluster. You don’t need to create the user on each node individually; just create it on one node, and it should replicate to the other nodes in the cluster.

CREATE USER 'monitor'@'%' IDENTIFIED BY 'Monitor@passw0rd';

GRANT SELECT ON sys.* TO 'monitor'@'%';

fLUSH PRIVILEGES

Set the monitoring user in ProxySQL: by adding the user information inside Proxysql database

UPDATE global_variables SET variable_value='monitor' WHERE
variable_name='mysql-monitor_username';
UPDATE global_variables SET variable_value='Monitor@passw0rd' WHERE
variable_name='mysql-monitor_password';

add user in proxsql that we will use for connecting to cluster for read/write operation

INSERT INTO mysql_users (username,password) VALUES ('sbuser','sbpass');

Load the user into runtime space and save these changes to disk :

LOAD MYSQL USERS TO RUNTIME; SAVE MYSQL USERS TO DISK;

test and check if the user able to connect to proxysql

mysql -u sbuser -psbpass -h 127.0.0.1 -P 6033

We will create a user inside the cluster node that will be allowed to access the cluster through ProxySQL. This user will have full privileges and will be allowed to connect from anywhere.

CREATE USER 'sbuser'@'%' IDENTIFIED BY 'sbpass';
GRANT ALL ON *.* TO 'sbuser'@'%';
FLUSH PRIVILEGE

Define the rules for read-write and read-only queries:

INSERT INTO mysql_query_rules (rule_id, match_pattern, destination_hostgroup) VALUES (1,
'^SELECT.*FOR UPDATE', 10);
INSERT INTO mysql_query_rules (rule_id, match_pattern, destination_hostgroup) VALUES (2,
'^SELECT', 20);
INSERT INTO mysql_query_rules (rule_id, match_pattern, destination_hostgroup) VALUES (3, '^',
10);

Load and save the configuration:

LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;
LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK;
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;
LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;

You can now connect to the XtraDB Cluster through ProxySQL.

To test the connection, use the following connection string for the appuser we created. Keep in mind that ProxySQL uses port 6032 instead of the MySQL default port, so make sure to configure this correctly in your application setup

mysql -u appuser -p -h <proxysql_host> -P 6032

For your setup, since you are connecting directly from the ProxySQL server, you will use the localhost or APIPA (Automatic Private IP Addressing) IP

mysql -u appuser -p -h 127.0.0.1 -P 6032