Percona XtraDB Cluster 5.7, follow the steps provided below on all instances. These steps will guide you through the installation and configuration process for a successful cluster deployment.

STEP1:  Install Percona Repository

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

STTEP2: Install Percona XtraDB Cluster

yum install Percona-XtraDB-Cluster-57

STEP3: Start MySQL Service

systemctl start mysqld

STEP4: Check Log for Initialization

grep 'temp' /var/log/mysqld.log

STEP5: Access MySQL Console

mysql -uroot -pTest@123

STEP6: Stop MySQL Service

systemctl stop mysqld

STEP7: Edit Configuration File Percona

vi /etc/percona-xtradb-cluster.conf.d/wsrep.cnf

MASTER Node Configuration:

On each instance, perform the following configuration steps

STEP8: Create User for State Snapshot Transfer (SST)

CREATE USER 'sstuser'@'%' IDENTIFIED BY 'Siva@123!';

STEP9: Grant Necessary Privileges to SST User

GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT ON *.* TO 'sstuser'@'%';
FLUSH PRIVILEGES;

STEP10: Stop MySQL Service

systemctl stop mysqld

STEP11: MASTER Node  Configuration Start MySQL Service in Bootstrap Mode

systemctl start mysql@bootstrap

STEP12: Node 1 and Node 2 Configuration

systemctl start mysqld

Test Replication: To confirm successful replication, execute the following on any of the nodes

Create a Test Database

CREATE DATABASE testdb;

Notes: By following these steps on all instances, you’ll establish a Percona XtraDB Cluster 5.7 setup with proper user privileges and configuration. This will enable seamless communication and replication among the nodes, ensuring the stability and reliability of your database cluster.

Output Master :

[root@ip-172-31-33-166 centos]# mysql -uroot -pTest@123
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.7.42-46-57-log Percona XtraDB Cluster (GPL), Release rel46, Revision ebc42be, WSREP version 31.65, wsrep_31.65

Copyright (c) 2009-2023 Percona LLC and/or its affiliates
Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql> create database clusteraugust;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| clusteraugust      |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> show status like 'wsrep%';
+----------------------------------+--------------------------------------------------------+
| Variable_name                    | Value                                                  |
+----------------------------------+--------------------------------------------------------+
| wsrep_local_state_uuid           | daf89864-400e-11ee-bcb4-c6d10c39933b                   |
| wsrep_protocol_version           | 9                                                      |
| wsrep_last_applied               | 7                                                      |
| wsrep_last_committed             | 7                                                      |
| wsrep_replicated                 | 1                                                      |
| wsrep_replicated_bytes           | 208                                                    |
| wsrep_repl_keys                  | 1                                                      |
| wsrep_repl_keys_bytes            | 32                                                     |
| wsrep_repl_data_bytes            | 112                                                    |
| wsrep_repl_other_bytes           | 0                                                      |
| wsrep_received                   | 10                                                     |
| wsrep_received_bytes             | 855                                                    |
| wsrep_local_commits              | 0                                                      |
| wsrep_local_cert_failures        | 0                                                      |
| wsrep_local_replays              | 0                                                      |
| wsrep_local_send_queue           | 0                                                      |
| wsrep_local_send_queue_max       | 1                                                      |
| wsrep_local_send_queue_min       | 0                                                      |
| wsrep_local_send_queue_avg       | 0.000000                                               |
| wsrep_local_recv_queue           | 0                                                      |
| wsrep_local_recv_queue_max       | 2                                                      |
| wsrep_local_recv_queue_min       | 0                                                      |
| wsrep_local_recv_queue_avg       | 0.100000                                               |
| wsrep_local_cached_downto        | 7                                                      |
| wsrep_flow_control_paused_ns     | 0                                                      |
| wsrep_flow_control_paused        | 0.000000                                               |
| wsrep_flow_control_sent          | 0                                                      |
| wsrep_flow_control_recv          | 0                                                      |
| wsrep_flow_control_interval      | [ 173, 173 ]                                           |
| wsrep_flow_control_interval_low  | 173                                                    |
| wsrep_flow_control_interval_high | 173                                                    |
| wsrep_flow_control_status        | OFF                                                    |
| wsrep_flow_control_active        | false                                                  |
| wsrep_flow_control_requested     | false                                                  |
| wsrep_cert_deps_distance         | 1.000000                                               |
| wsrep_apply_oooe                 | 0.000000                                               |
| wsrep_apply_oool                 | 0.000000                                               |
| wsrep_apply_window               | 1.000000                                               |
| wsrep_apply_waits                | 0                                                      |
| wsrep_commit_oooe                | 0.000000                                               |
| wsrep_commit_oool                | 0.000000                                               |
| wsrep_commit_window              | 1.000000                                               |
| wsrep_local_state                | 4                                                      |
| wsrep_local_state_comment        | Synced                                                 |
| wsrep_cert_index_size            | 1                                                      |
| wsrep_cert_bucket_count          | 22                                                     |
| wsrep_gcache_pool_size           | 2384                                                   |
| wsrep_causal_reads               | 0                                                      |
| wsrep_cert_interval              | 0.000000                                               |
| wsrep_open_transactions          | 0                                                      |
| wsrep_open_connections           | 0                                                      |
| wsrep_ist_receive_status         |                                                        |
| wsrep_ist_receive_seqno_start    | 0                                                      |
| wsrep_ist_receive_seqno_current  | 0                                                      |
| wsrep_ist_receive_seqno_end      | 0                                                      |
| wsrep_incoming_addresses         | 52.91.181.105:3306,18.209.36.40:3306,54.92.162.73:3306 |
| wsrep_cluster_weight             | 3                                                      |
| wsrep_desync_count               | 0                                                      |
| wsrep_evs_delayed                |                                                        |
| wsrep_evs_evict_list             |                                                        |
| wsrep_evs_repl_latency           | 0/0/0/0/0                                              |
| wsrep_evs_state                  | OPERATIONAL                                            |
| wsrep_gcomm_uuid                 | 739cfb20-4015-11ee-9866-823618c1c5cd                   |
| wsrep_gmcast_segment             | 0                                                      |
| wsrep_cluster_conf_id            | 3                                                      |
| wsrep_cluster_size               | 3                                                      |
| wsrep_cluster_state_uuid         | daf89864-400e-11ee-bcb4-c6d10c39933b                   |
| wsrep_cluster_status             | Primary                                                |
| wsrep_connected                  | ON                                                     |
| wsrep_local_bf_aborts            | 0                                                      |
| wsrep_local_index                | 0                                                      |
| wsrep_provider_name              | Galera                                                 |
| wsrep_provider_vendor            | Codership Oy <info@codership.com>                      |
| wsrep_provider_version           | 3.65(rf47405c)                                         |
| wsrep_ready                      | ON                                                     |
+----------------------------------+--------------------------------------------------------+
75 rows in set (0.01 sec)

mysql>

Node1, Node2 Output :

[root@ip-172-31-38-228 centos]# systemctl start mysqld
[root@ip-172-31-38-228 centos]# mysql -uroot -pTest@123
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.7.42-46-57-log Percona XtraDB Cluster (GPL), Release rel46, Revision ebc42be, WSREP version 31.65, wsrep_31.65

Copyright (c) 2009-2023 Percona LLC and/or its affiliates
Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| clusteraugust      |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql>

Leave a Reply

Your email address will not be published. Required fields are marked *