Trong bài viết này ta sẽ thiết lập MySQL Group Replication trên 2 máy chủ CentOS 8. Sau khi thiết lập xong chúng ta sẽ có cụm máy chủ có khả năng đọc ghi ở trên cùng nhiều cơ sở dữ liệu. Các bạn tham khảo video bên dưới

1. Thiết lập Reposistory và cài đặt MySQL 8

– Cài đặt MySQL 8 cho máy chủ DB-1

[root@db1 ~]# dnf update
[root@db1 ~]# rpm -Uvh https://repo.mysql.com/mysql80-community-release-el8-1.noarch.rpm
[root@db1 ~]# cd /etc/yum.repos.d/
[root@db1 ~]# sed -i ‘s/enabled=1/enabled=0/’ /etc/yum.repos.d/mysql-community.repo
[root@db1 ~]# dnf –enablerepo=mysql80-community install mysql-server

– Cài đặt MySQL 8 cho máy chủ DB-2

[root@db2 ~]# dnf update
[root@db2 ~]# rpm -Uvh https://repo.mysql.com/mysql80-community-release-el8-1.noarch.rpm
[root@db2 ~]# cd /etc/yum.repos.d/
[root@d2 ~]# sed -i ‘s/enabled=1/enabled=0/’ /etc/yum.repos.d/mysql-community.repo
[root@db2 ~]# dnf –enablerepo=mysql80-community install mysql-server

2. Thiết lập tài khoản để đăng nhập

– Thiết lập tại DB1

[root@db1 ~]# systemctl stop firewalld
[root@db1 ~]# systemctl disable firewalld
Removed /etc/systemd/system/multi-user.target.wants/firewalld.service.
Removed /etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service.

[root@db1 ~]# systemctl start mysqld
[root@db1 ~]# systemctl enable mysqld
[root@db1 ~]# netstat -ltunp | grep 3306
[root@db1 ~]# mysql_secure_installation

– Thiết lập tại DB2

[root@db2 ~]# systemctl stop firewalld
[root@db2 ~]# systemctl disable firewalld
Removed /etc/systemd/system/multi-user.target.wants/firewalld.service.
Removed /etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service.

[root@db2 ~]# systemctl start mysqld
[root@db2 ~]# systemctl enable mysqld
[root@db2 ~]# netstat -ltunp | grep 3306
[root@db2 ~]# mysql_secure_installation

3. Thiết lập MySQL Group Replication

– Khởi tạo UUID

[root@db1 ~]# uuidgen
28bc07af-5493-4814-8968-7cecf58aa080

– Thiết lập file cấu hình tại DB1 ở IP 192.168.255.170

[root@db1 ~]# cd /etc/my.cnf.d/
[root@db1 my.cnf.d]# ls
client.cnf mysql-default-authentication-plugin.cnf mysql-server.cnf

[root@db1 ~]# cat mysql-server.cnf > mysql-server.cnf.backup
[root@db1 my.cnf.d]# cat /dev/null > mysql-server.cnf
[root@db1 ~]# vi mysql-server.cnf

[mysqld]
max_connect_errors=10000
sql_mode = ONLY_FULL_GROUP_BY,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
disabled_storage_engines=”MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY”
default_authentication_plugin=mysql_native_password

#General replication settings
gtid_mode = ON
enforce_gtid_consistency = ON
master_info_repository = TABLE
relay_log_info_repository = TABLE
binlog_checksum = NONE
log_slave_updates = ON
log_bin = binlog
binlog_format = ROW
transaction_write_set_extraction = XXHASH64
group_replication_bootstrap_group = OFF
group_replication_start_on_boot = ON
group_replication_ssl_mode = REQUIRED
group_replication_recovery_use_ssl = 1

#Shared replication group configuration
group_replication_group_name = “28bc07af-5493-4814-8968-7cecf58aa080”
group_replication_ip_whitelist = “192.168.255.170,192.168.255.171”
group_replication_group_seeds = “192.168.255.170:33061,192.168.255.171:33061”

# Single or Multi-primary mode? Uncomment these two lines
# for multi-primary mode, where any host can accept writes
group_replication_single_primary_mode = OFF
group_replication_enforce_update_everywhere_checks = ON

# Host specific replication configuration
server_id = 1
bind-address = “192.168.255.170”
report_host = “192.168.255.170”
group_replication_local_address = “192.168.255.170:33061″

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysql/mysqld.log
pid-file=/run/mysqld/mysqld.pid

– Thiết lập file cấu hình tại DB2

[root@db2 ~]# cd /etc/my.cnf.d/
[root@db2 my.cnf.d]# ls
client.cnf mysql-default-authentication-plugin.cnf mysql-server.cnf

[root@db2 ~]# cat mysql-server.cnf > mysql-server.cnf.backup
[root@db2 my.cnf.d]# cat /dev/null > mysql-server.cnf
[root@db2 ~]# vi mysql-server.cnf

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/8.0/en/server-configuration-defaults.html

# General replication settings
gtid_mode = ON
enforce_gtid_consistency = ON
master_info_repository = TABLE
relay_log_info_repository = TABLE
binlog_checksum = NONE
log_slave_updates = ON
log_bin = binlog
binlog_format = ROW
transaction_write_set_extraction = XXHASH64
group_replication_bootstrap_group = OFF
group_replication_start_on_boot = OFF
group_replication_ssl_mode = REQUIRED
group_replication_recovery_use_ssl = 1

# Shared replication group configuration
group_replication_group_name = “28bc07af-5493-4814-8968-7cecf58aa080”
group_replication_ip_whitelist = “192.168.255.171,192.168.255.170”
group_replication_group_seeds = “192.168.255.171:33061,192.168.255.170:33061”

# Host specific replication configuration
server_id = 2
bind-address = “192.168.255.171”
report_host = “192.168.255.171”
group_replication_local_address = “192.168.255.171:33061”

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysql/mysqld.log
pid-file=/run/mysqld/mysqld.pid

3. Chuẩn Bị Thông Số Cấu Hình Cho Từng Node Database

– Thiết lập tại DB-1 và DB-2

[root@db1 my.cnf.d]# mysql -u root -p
Enter password: 12345678

mysql> SET SQL_LOG_BIN=0;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE USER ‘repl’@’%’ IDENTIFIED BY ‘matkhau’ REQUIRE SSL;
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT REPLICATION SLAVE ON *.* TO ‘repl’@’%’;
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

mysql> SET SQL_LOG_BIN=1;
Query OK, 0 rows affected (0.00 sec)

mysql> CHANGE MASTER TO MASTER_USER=’repl’, MASTER_PASSWORD=’matkhau’ FOR CHANNEL ‘group_replication_recovery’;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

Lưu ý: Password phải giống nhau

– Khởi tạo Group Replicate tại DB-1

mysql> SET GLOBAL group_replication_bootstrap_group=ON;
Query OK, 0 rows affected (0.00 sec)

mysql> START GROUP_REPLICATION;
Query OK, 0 rows affected (3.10 sec)

mysql> SET GLOBAL group_replication_bootstrap_group=OFF;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM performance_schema.replication_group_members;

– Thiết lập DB-2 tham gia group replication tại DB-2

mysql> START GROUP_REPLICATION;
Query OK, 0 rows affected (4.81 sec)

mysql> SELECT * FROM performance_schema.replication_group_members;

Thiết Lập MySQL 8 Group Replication