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>