How to Set Up MySQL 8 Multi-Master Group Replication on Ubuntu 22.04
In this video, I will set up MySQL multi-primary group replication using a set of three Ubuntu 22.04 servers.
Join this channel to get access to perks: https://www.youtube.com/@lazysysad/join
PLEASE SUBSCRIBE :)
PLEASE HIT LIKE IF IT HELPED :)
GIVE SUPPORT - / lazysysad
BUY ME A COFFEE - https://www.buymeacoffee.com/lazysysad
PAYPAL - https://paypal.me/lazysysad
Steps:
Install MySQL
apt-get update; apt-get -y install mysql-server-8.0
Open up access to the ports 3306 and 33061 in the firewall
ufw allow 3306
Server 1
ufw allow from server2_ip to any port 33061
ufw allow from server3_ip to any port 33061
Server 2
ufw allow from server1_ip to any port 33061
ufw allow from server3_ip to any port 33061
Server 3
ufw allow from server1_ip to any port 33061
ufw allow from server2_ip to any port 33061
Generate UUID
uuidgen
Output
168dcb64-7cce-473a-b338-6501f305e561
Modify the configuration of server 1.
vi /etc/mysql/my.cnf
ALL MYSQL
[mysqld]
General replication settings
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
gtid_mode = ON
enforce_gtid_consistency = ON
master_info_repository = TABLE
relay_log_info_repository = TABLE
binlog_checksum = NONE
log_replica_updates = ON
log_bin = binlog
binlog_format = ROW
transaction_write_set_extraction = XXHASH64
loose-group_replication_bootstrap_group = OFF
loose-group_replication_start_on_boot = OFF
loose-group_replication_ssl_mode = REQUIRED
loose-group_replication_recovery_use_ssl = 1
Shared replication group configuration
loose-group_replication_group_name = "168dcb64-7cce-473a-b338-6501f305e561"
loose-group_replication_ip_allowlist = "mysql1,mysql2,mysql3"
loose-group_replication_group_seeds = "mysql1:33061,mysql2:33061,mysql3:33061"
For multi-primary mode, where any host can accept writes
loose-group_replication_single_primary_mode = OFF
loose-group_replication_enforce_update_everywhere_checks = ON
Host specific replication configuration
bind-address = "0.0.0.0"
server_id = " "
report_host = " "
loose-group_replication_local_address = " :33061"
Restart mysql
systemctl restart mysql
Configuring Replication Users and Enabling Group Replication Plugin
mysql
SET SQL_LOG_BIN=0;
CREATE USER 'repl'@'%' IDENTIFIED BY 'Password123!' REQUIRE SSL;
GRANT REPLICATION SLAVE ON . TO 'repl'@'%';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;
CHANGE REPLICATION SOURCE TO SOURCE_USER='repl', SOURCE_PASSWORD='Password123!' FOR CHANNEL 'group_replication_recovery';
INSTALL PLUGIN group_replication SONAME 'group_replication.so';
SHOW PLUGINS;
Starting Group Replication
Server 1
SET GLOBAL group_replication_bootstrap_group=ON;
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group=OFF;
SELECT * FROM performance_schema.replication_group_members\G;
Server 2 and Server 3
START GROUP_REPLICATION;
SELECT * FROM performance_schema.replication_group_members;
Test replication
Server 3
CREATE DATABASE lazy;
Server 1
SHOW DATABASES;
USE lazy;
CREATE TABLE admins (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL
);
Server 2
SHOW DATABASES;
USE lazy;
SHOW TABLES;
Chapters:
00:00 Intro
00:17 Install MySQL
00:50 Setup Firewall
02:10 Generate UUID
02:26 Setup my.cnf
05:51 Configure Replication User
06:58 Enable Group Replication Plugin
07:33 Start Group Replication
08:36 Test and Verification
Drop me your feedback and comments below.
That's all for now.
If this video helped you in any way, please like share and subscribe!
Thank you!!!
Watch video How to Set Up MySQL 8 Multi-Master Group Replication on Ubuntu 22.04 online without registration, duration hours minute second in high quality. This video was added by user The Lazy SysAdmin 05 December 2023, don't forget to share it with your friends and acquaintances, it has been viewed on our site 3,264 once and liked it 77 people.