Teguh Triharto Learning Center: .::: Install Percona XtraDB Cluster 5.7 in Centos 7 (Galera for MySQL ) :::.

A. Install Percona XtraDB 5.7

1. Install requirement lsof qpress socat
yum install https://mirrors.cloud.tencent.com/percona/centos/7/RPMS/x86_64/qpress-11-1.el7.x86_64.rpm -y
yum install lsof qpress socat -y

[root@teguhth01 data]# cat /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
10.10.10.51 teguhth01
10.10.10.52 teguhth02
[root@teguhth01 data]#

[root@teguhth02 data]# cat /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
10.10.10.51 teguhth01
10.10.10.52 teguhth02
[root@teguhth02 data]#

2. Install percona xtradb 01 (teguhth01) & xtradb 02 (teguhth02)

[root@teguhth01 data]# cat yumperconainstall.sh
yum install -y Percona-XtraDB-Cluster-shared*
yum install -y Percona-XtraDB-Cluster-client*
yum install -y percona-xtrabackup-24-2.4.20-1.el7.x86_64.rpm
yum install -y Percona-XtraDB-Cluster-server*
yum install -y Percona-XtraDB-Cluster-57*

[root@teguhth01 data]# cat installpercona.sh
rpm -ivh Percona-XtraDB-Cluster-shared*
rpm -ivh Percona-XtraDB-Cluster-client*
rpm -ivh perl-DBD-MySQL-4.023-6.el7.x86_64.rpm
rpm -ivh percona-xtrabackup-24-2.4.20-1.el7.x86_64.rpm
rpm -ivh Percona-XtraDB-Cluster-server*
rpm -ivh Percona-XtraDB-Cluster-57*

[root@teguhth01 data]#
[root@teguhth01 data]# cat removepercona.sh
yum remove -y Percona-XtraDB-Cluster-57*
yum remove -y Percona-XtraDB-Cluster-server*
yum remove -y percona-xtrabackup*
yum remove -y Percona-XtraDB-Cluster-client*
yum remove -y Percona-XtraDB-Cluster-shared*

[root@teguhth01 data]#

[root@teguhth01 data]# sh yumperconainstall.sh

3. configuration server01 galera to /etc/my.cnf or cat /etc/percona-xtradb-cluster.conf.d/wsrep.cnf

[mysqld]
wsrep_on=ON
wsrep_provider=/usr/lib64/galera3/libgalera_smm.so
wsrep_cluster_address=gcomm://10.10.10.51,10.10.10.52
binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0
wsrep_cluster_name=galeracluster
#wsrep_sst_method=rsync # kl mariadb galera pakai ini
wsrep_node_address=10.10.10.51
wsrep_node_name=teguhth01
#wsrep_sst_auth=teguh:triharto
wsrep_sst_method =xtrabackup-v2
wsrep_sst_auth =sst_user:admin

[root@teguhth01 data]# cat /etc/my.cnf
#
# The Percona XtraDB Cluster 5.7 configuration file.
#
#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#   Please make any edits and changes to the appropriate sectional files
#   included below.
#
!includedir /etc/my.cnf.d/
!includedir /etc/percona-xtradb-cluster.conf.d/

[mysqld]
wsrep_on=ON
wsrep_provider=/usr/lib64/galera3/libgalera_smm.so
wsrep_cluster_address=gcomm://10.10.10.51,10.10.10.52
binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0
wsrep_cluster_name=galeracluster
#wsrep_sst_method=rsync # kl mariadb galera pakai ini
wsrep_node_address=10.10.10.51
wsrep_node_name=teguhth01
#wsrep_sst_auth=teguh:triharto
wsrep_sst_method = xtrabackup-v2
wsrep_sst_auth = sst_user:admin
[root@teguhth01 data]#

4. restart server01 for first time using galera
systemctl start mysql@bootstrap.service

[root@teguhth01 data]# systemctl start mysql@bootstrap.service
[root@teguhth01 data]#

5. setting mysql_secure_installation on teguhth01
[root@teguhth01 data]# grep 'temporary password' /var/log/mysqld.log
2023-09-26T02:18:40.410463Z 1 [Note] A temporary password is generated for root@localhost: &Xg>S:6Htszg
[root@teguhth01 data]#
[root@teguhth01 data]# mysql_secure_installation

Securing the MySQL server deployment.

Enter password for user root:

The existing password for the user account root has expired. Please set a new password.

New password:

Re-enter new password:

VALIDATE PASSWORD PLUGIN can be used to test passwords
and improve security. It checks the strength of password
and allows the users to set only those passwords which are
secure enough. Would you like to setup VALIDATE PASSWORD plugin?

Press y|Y for Yes, any other key for No: n
Using existing password for root.
Change the password for root ? ((Press y|Y for Yes, any other key for No) : n

... skipping.
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.

Remove anonymous users? (Press y|Y for Yes, any other key for No) : y
Success.

Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.

Disallow root login remotely? (Press y|Y for Yes, any other key for No) : n

... skipping.
By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.

Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y
- Dropping test database...
Success.

- Removing privileges on test database...
Success.

Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.

Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y
Success.

All done!
[root@teguhth01 data]#

6. Create user for replication & other in server01

select @@hostname,@@version,host,user from mysql.user;
CREATE USER 'sst_user'@'%' IDENTIFIED BY 'admin';
GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT ON *.* TO 'sst_user'@'%';

CREATE USER 'sst_user'@'localhost' IDENTIFIED BY 'admin';
GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT ON *.* TO 'sst_user'@'localhost';

FLUSH PRIVILEGES;
select @@hostname,@@version,host,user from mysql.user;

or but not recomendation

select @@hostname,@@version,host,user from mysql.user;

create user 'sst_user'@'%' identified by 'admin';
grant all privileges on *.* to 'sst_user'@'%';

create user 'sst_user'@'localhost' identified by 'admin';
grant all privileges on *.* to 'sst_user'@'localhost';

create user 'root'@'%' identified by 'root';
grant all privileges on *.* to 'sst_user'@'%';

flush privileges;

select @@hostname,@@version,host,user from mysql.user;

mysql> select @@hostname,@@version,host,user from mysql.user;
+------------+------------------+-----------+---------------+
| @@hostname | @@version        | host      | user          |
+------------+------------------+-----------+---------------+
| teguhth01 | 5.7.31-34-57-log | localhost | mysql.session |
| teguhth01 | 5.7.31-34-57-log | localhost | mysql.sys     |
| teguhth01 | 5.7.31-34-57-log | localhost | root          |
+------------+------------------+-----------+---------------+
3 rows in set (0.00 sec)

mysql>
mysql> create user 'sst_user'@'%' identified by 'admin';
Query OK, 0 rows affected (0.01 sec)

mysql> grant all privileges on *.* to 'sst_user'@'%';
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> create user 'sst_user'@'localhost' identified by 'admin';
Query OK, 0 rows affected (0.00 sec)

mysql> grant all privileges on *.* to 'sst_user'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> create user 'root'@'%' identified by 'root';
Query OK, 0 rows affected (0.00 sec)

mysql> grant all privileges on *.* to 'sst_user'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@hostname,@@version,host,user from mysql.user;
+------------+------------------+-----------+---------------+
| @@hostname | @@version        | host      | user          |
+------------+------------------+-----------+---------------+
| teguhth01 | 5.7.31-34-57-log | %         | root          |
| teguhth01 | 5.7.31-34-57-log | %         | sst_user      |
| teguhth01 | 5.7.31-34-57-log | localhost | mysql.session |
| teguhth01 | 5.7.31-34-57-log | localhost | mysql.sys     |
| teguhth01 | 5.7.31-34-57-log | localhost | root          |
| teguhth01 | 5.7.31-34-57-log | localhost | sst_user      |
+------------+------------------+-----------+---------------+
6 rows in set (0.00 sec)

mysql>

7. check status wrep in server01
mysql -uroot -proot -e "show status like 'wsrep%';"

[root@teguhth01 data]# mysql -uroot -proot -e "show status like 'wsrep%';"
mysql: [Warning] Using a password on the command line interface can be insecure.
+----------------------------------+-------------------------------------------------+
| Variable_name                    | Value                                           |
+----------------------------------+-------------------------------------------------+
| wsrep_local_state_uuid           | 06a3359f-5c13-11ee-a9da-fe7c69ca0aa4            |
| wsrep_protocol_version           | 9                                               |
| wsrep_last_applied               | 18                                              |
| wsrep_last_committed             | 18                                              |
| wsrep_replicated                 | 0                                               |
| wsrep_replicated_bytes           | 0                                               |
| wsrep_repl_keys                  | 0                                               |
| wsrep_repl_keys_bytes            | 0                                               |
| wsrep_repl_data_bytes            | 0                                               |
| wsrep_repl_other_bytes           | 0                                               |
| wsrep_received                   | 2                                               |
| wsrep_received_bytes             | 144                                             |
| 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.500000                                        |
| wsrep_local_cached_downto        | 0                                               |
| 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      | [ 100, 100 ]                                    |
| wsrep_flow_control_interval_low | 100                                             |
| wsrep_flow_control_interval_high | 100                                             |
| wsrep_flow_control_status        | OFF                                             |
| wsrep_cert_deps_distance         | 0.000000                                        |
| wsrep_apply_oooe                 | 0.000000                                        |
| wsrep_apply_oool                 | 0.000000                                        |
| wsrep_apply_window               | 0.000000                                        |
| wsrep_commit_oooe                | 0.000000                                        |
| wsrep_commit_oool                | 0.000000                                        |
| wsrep_commit_window              | 0.000000                                        |
| wsrep_local_state                | 4                                               |
| wsrep_local_state_comment        | Synced                                          |
| wsrep_cert_index_size            | 0                                               |
| wsrep_cert_bucket_count          | 22                                              |
| wsrep_gcache_pool_size           | 1320                                            |
| 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         | 10.10.10.51:3306                                |
| wsrep_cluster_weight             | 1                                               |
| wsrep_desync_count               | 0                                               |
| wsrep_evs_delayed                |                                                 |
| wsrep_evs_evict_list             |                                                 |
| wsrep_evs_repl_latency           | 1.511e-06/0.000121749/0.000587483/0.000232899/5 |
| wsrep_evs_state                  | OPERATIONAL                                     |
| wsrep_gcomm_uuid                 | 624240f2-5c17-11ee-990a-7321d99a9b02            |
| wsrep_cluster_conf_id            | 1                                               |
| wsrep_cluster_size               | 1                                               |
| wsrep_cluster_state_uuid         | 06a3359f-5c13-11ee-a9da-fe7c69ca0aa4            |
| 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.45(ra60e019)                                  |
| wsrep_ready                      | ON                                              |
+----------------------------------+-------------------------------------------------+
[root@teguhth01 data]#

8. configuration server02 galera to /etc/my.cnf or cat /etc/percona-xtradb-cluster.conf.d/wsrep.cnf

[mysqld]
wsrep_on=ON
wsrep_provider=/usr/lib64/galera3/libgalera_smm.so
wsrep_cluster_address=gcomm://10.10.10.51,10.10.10.52
binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0
wsrep_cluster_name=galeracluster
#wsrep_sst_method=rsync
wsrep_node_address=10.10.10.52
wsrep_node_name=teguhth02
#wsrep_sst_auth=teguh:triharto
wsrep_sst_method =xtrabackup-v2
wsrep_sst_auth =sst_user:admin

[root@teguhth02 data]# cat /etc/my.cnf
#
# The Percona XtraDB Cluster 5.7 configuration file.
#
#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#   Please make any edits and changes to the appropriate sectional files
#   included below.
#
!includedir /etc/my.cnf.d/
!includedir /etc/percona-xtradb-cluster.conf.d/
[mysqld]
wsrep_on=ON
wsrep_provider=/usr/lib64/galera3/libgalera_smm.so
wsrep_cluster_address=gcomm://10.10.10.51,10.10.10.52
binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0
wsrep_cluster_name=galeracluster
#wsrep_sst_method=rsync
wsrep_node_address=10.10.10.52
wsrep_node_name=teguhth02
#wsrep_sst_auth=teguh:triharto
wsrep_sst_method =xtrabackup-v2
wsrep_sst_auth =sst_user:admin
[root@teguhth02 data]#

11. start mysql in server 02
systemctl start mysql

[root@teguhth02 data]# systemctl start mysql
[root@teguhth02 data]#

12. check status wrep in server02
mysql -uroot -proot -e "show status like 'wsrep%';"

[root@teguhth02 data]# mysql -uroot -proot -e "show status like 'wsrep%';"
mysql: [Warning] Using a password on the command line interface can be insecure.
+----------------------------------+--------------------------------------+
| Variable_name                    | Value                                |
+----------------------------------+--------------------------------------+
| wsrep_local_state_uuid           | 37807588-5c1d-11ee-9cb0-d3c9b01ab11c |
| wsrep_protocol_version           | 9                                    |
| wsrep_last_applied               | 10                                   |
| wsrep_last_committed             | 10                                   |
| wsrep_replicated                 | 0                                    |
| wsrep_replicated_bytes           | 0                                    |
| wsrep_repl_keys                  | 0                                    |
| wsrep_repl_keys_bytes            | 0                                    |
| wsrep_repl_data_bytes            | 0                                    |
| wsrep_repl_other_bytes           | 0                                    |
| wsrep_received                   | 3                                    |
| wsrep_received_bytes             | 224                                  |
| 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       | 1                                    |
| wsrep_local_recv_queue_min       | 0                                    |
| wsrep_local_recv_queue_avg       | 0.000000                             |
| wsrep_local_cached_downto        | 0                                    |
| 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      | [ 141, 141 ]                         |
| wsrep_flow_control_interval_low | 141                                  |
| wsrep_flow_control_interval_high | 141                                  |
| wsrep_flow_control_status        | OFF                                  |
| wsrep_cert_deps_distance         | 0.000000                             |
| wsrep_apply_oooe                 | 0.000000                             |
| wsrep_apply_oool                 | 0.000000                             |
| wsrep_apply_window               | 0.000000                             |
| wsrep_commit_oooe                | 0.000000                             |
| wsrep_commit_oool                | 0.000000                             |
| wsrep_commit_window              | 0.000000                             |
| wsrep_local_state                | 4                                    |
| wsrep_local_state_comment        | Synced                               |
| wsrep_cert_index_size            | 0                                    |
| wsrep_cert_bucket_count          | 22                                   |
| wsrep_gcache_pool_size           | 1456                                 |
| 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         | 10.10.10.51:3306,10.10.10.52:3306    |
| wsrep_cluster_weight             | 2                                    |
| 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                 | 74fd84c9-5c1d-11ee-91da-7ea234c4d603 |
| wsrep_cluster_conf_id            | 2                                    |
| wsrep_cluster_size               | 2                                    |
| wsrep_cluster_state_uuid         | 37807588-5c1d-11ee-9cb0-d3c9b01ab11c |
| wsrep_cluster_status             | Primary                              |
| wsrep_connected                  | ON                                   |
| wsrep_local_bf_aborts            | 0                                    |
| wsrep_local_index                | 1                                    |
| wsrep_provider_name              | Galera                               |
| wsrep_provider_vendor            | Codership Oy <info@codership.com>    |
| wsrep_provider_version           | 3.45(ra60e019)                       |
| wsrep_ready                      | ON                                   |
+----------------------------------+--------------------------------------+
[root@teguhth02 data]#

B. testing Insert data

from server01

mysql -uroot -proot
show databases;
create database abc;
show databases;

check from server02
show databases;

==

from server02

mysql -uroot -proot
show databases;
create database def;
show databases;

check from server01
show databases;

C. Query to monitor
show status like 'wsrep%';
select @@hostname,@@version;
SHOW VARIABLES LIKE '%version%';
show status like 'wsrep_connected';
show status like 'wsrep_incoming_addresses';
show status like 'wsrep_provider_name';

or

mysql -uroot -proot -e "show status like 'wsrep%';"
mysql -uroot -proot -e "select @@hostname,@@version;"
mysql -uroot -proot -e "SHOW VARIABLES LIKE '%version%';"
mysql -uroot -proot -e "show status like 'wsrep_connected';"
mysql -uroot -proot -e "show status like 'wsrep_incoming_addresses';"
mysql -uroot -proot -e "show status like 'wsrep_provider_name';"

Teguh Triharto Learning Center

Blog List

Tuesday, September 26, 2023

.::: Install Percona XtraDB Cluster 5.7 in Centos 7 (Galera for MySQL ) :::.

No comments:

Post a Comment

Popular Posts