How to configure MGR single master and multiple slaves in MySQL 8.0.15

1. Introduction

MySQL Group Replication (MGR for short) literally means MySQL group replication, but it is actually a highly available cluster architecture that currently only supports MySQL 5.7 and MySQL 8.0 versions.

It is a new high-availability and high-scalability solution officially launched by MySQL in December 2016, providing high-availability, high-scalability and high-reliability MySQL cluster services.

It is also a new high-availability cluster architecture based on the concept of group replication and fully referenced by MariaDB Galera Cluster and Percona XtraDB Cluster.

MySQL Group Replication is built on top of XCom, which is based on Paxos. It is precisely because of the XCom infrastructure that the transaction consistency of the database state machine between nodes can be guaranteed in theory and practice.

Expanding from the general master-slave replication concept, multiple nodes together form a database cluster. The submission of a transaction must be agreed upon by more than half of the nodes. A database state machine is maintained on each node in the cluster to ensure the consistency of transactions between nodes.

advantage:

High consistency, group replication technology based on native replication and Paxos protocol.

High fault tolerance, with automatic detection mechanism. When a downtime occurs, the problem node will be automatically removed, and other nodes can be used normally (similar to zk cluster). When resource contention conflicts occur between different nodes, they will be handled on a first-come-first-served basis, and an automatic brain-split protection mechanism is built in.

High scalability, nodes can be added and removed online at any time, and the status of all nodes will be automatically synchronized until the new node is consistent with other nodes, and the new group information will be automatically maintained.

High flexibility, direct plug-in installation (5.7.17 and later come with .so plug-in), single-master mode and multi-master mode. In single-master mode, only the master database can read and write, and other slave databases will be added with super_read_only status, which can only read but not write. In case of failure, the master will be automatically selected.

shortcoming:

It is still too new and not very stable. Its performance is still slightly worse than PXC. It requires very high network stability, at least in the same computer room.

2. Environment Introduction

MySQL version: 8.0.15

Database port: 3306

Cluster replication port: 33006

MGR plugin: group_replication.so

10.115.88.18: Single master

10.115.88.19: From

10.115.88.20: From

3. Install MySQL 8.0.15

3.1.1 Download address:

https://cdn.mysql.com//Downloads/MySQL-8.0/mysql-8.0.15-linux-glibc2.12-x86_64.tar

3.1.2: Install the database

Unzip

tar -xf mysql-8.0.15-linux-glibc2.12-x86_64.tar -C /home/work

Enter the decompressed directory

cd /home/work

tar xf mysql-8.0.15-linux-glibc2.12-x86_64.tar.gz

mv mysql-8.0.15-linux-glibc2.12-x86_64 mysql

rm -rf *.xz

Grant permissions and create the data directory

cd /home/work

tar xf mysql-8.0.15-linux-glibc2.12-x86_64.tar.gz

mv mysql-8.0.15-linux-glibc2.12-x86_64 mysql

rm -rf *.xz
  chown -R work:work mysql
  mkdir data
  chown work:work data
  cd /home/work/mysql

   cd /home/work mkdir /home/work/relaylog chown -R work:work relaylog
  cd /home/work/mysql mkdir run chown -R work:work run

Add my.cnf file

[client]
default-character-set=utf8
socket=/home/work/mysql/run/mysql.sock

[mysqld]
user = work 
port = 3306
basedir=/home/work/mysql
datadir=/home/work/data
socket=/home/work/mysql/run/mysql.sock
pid-file=/home/work/mysql/run/mysqld.pid
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
lower_case_table_names = 1


server-id = 183306 
log-bin = mysql-bin
log-bin-index = binlogs.index
binlog_format = row
binlog_rows_query_log_events = on
binlog_checksum = none

slave-parallel-type = LOGICAL_CLOCK
slave-parallel-workers = 4
slave_preserve_commit_order = 1

#GITD
gtid_mode = on
enforce_gtid_consistency = 1
log-slave-updates = 1
binlog_gtid_simple_recovery = 1

relay_log = /home/work/relaylog/relay.log
relay-log-index = /home/work/relaylog/relay.index
master_info_repository = table
relay_log_info_repository = table

plugin_load="group_replication=group_replication.so"

#MGR
: : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : :
skip-name-resolve
skip-external-locking
character-set-server=utf8
lower_case_table_names=1
event_scheduler=on
log_bin_trust_function_creators=on

max_connections = 3000 
external-locking = FALSE 
max_allowed_packet = 32M 
sort_buffer_size = 8M 
join_buffer_size = 2M 
thread_cache_size = 300 
#query_cache_size = 64M 
#query_cache_limit = 4M 
read_rnd_buffer_size = 8M

innodb_buffer_pool_size = 8096M
innodb_log_file_size = 128M
innodb_log_files_in_group = 2
innodb_log_buffer_size = 2M
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1



[mysqld_safe]
log-error=/home/work/data/err.log

Initialize the database

bin/mysqld --initialize --basedir=/home/work/mysql --lower-case-table-names=1 --datadir=/home/work/data/ --user=work

Copy the startup file

cp support-files/mysql.server /etc/init.d/mysqld

Add environment variables

echo 'export PATH=/home/work/mysql/bin:$PATH' >>/etc/profile
source /etc/profile

Database startup

/etc/init.d/mysqld start

Change database password

alter user'root'@'localhost' IDENTIFIED BY 'dashuaige'; 

flush privileges;

4. Configure MGR master

4.1.1 Configure my.cnf file

Modify binlog as follows:

server-id = 103306
log-bin = mysql-bin
log-bin-index = binlogs.index
binlog_format = row
binlog_rows_query_log_events = on
binlog_checksum = none

slave-parallel-type = LOGICAL_CLOCK
slave-parallel-workers = 4
slave_preserve_commit_order = 1

Modify the GTID as follows:

gtid_mode = on
enforce_gtid_consistency = 1
log-slave-updates = 1
binlog_gtid_simple_recovery = 1

relay_log = /home/work/relaylog/relay.log
relay-log-index = /home/work/relaylog/relay.index
master_info_repository = table
relay_log_info_repository = table

Add MGR plugin

plugin_load="group_replication=group_replication.so"

Modify MGR parameters

: : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : :

Start the database

/etc/init.d/mysqld restart

4.1.2 Configure each hosts host resolution

[root@bj1-10-115-88-18 mysql]# cat /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
10.115.88.18 bj1-10-115-88-18
10.115.88.19 bj1-10-115-88-19
10.115.88.20 bj1-10-115-88-20

4.1.3 Configure the first master node of mgr

The following steps are executed in mysql on the 10.115.88.18 host:

Step 1: Create a user for replication

set sql_log_bin=0;
create user repuser@'%' identified by 'repuser123';

grant replication slave,replication client on *.* to repuser@'%';

create user repuser@'127.0.0.1' identified by 'repuser123';

grant replication slave,replication client on *.* to repuser@'127.0.0.1';

create user repuser@'localhost' identified by 'repuser123';

grant replication slave,replication client on *.* to repuser@'localhost';

set sql_log_bin=1;

Step 2: Configure the user used for replication

change master to master_user='repuser',master_password='repuser123' for channel 'group_replication_recovery';

Step 3: Install the mysql group replication plugin

Note: If you write plugin_load="group_replication=group_replication.so" in my.cnf, you don't need to install plugin group_replication soname 'group_replication.so';


Check whether the installation is successful by showing plugins;

Step 4: Create a group (officially, it is to initialize a replication group

set global group_replication_bootstrap_group=on;
start group_replication;
set global group_replication_bootstrap_group=off;

select * from performance_schema.replication_group_members;

5. Configure the other two nodes, from

The following steps are performed in mysql on all slave hosts:

Step 1: Create a user for replication

set sql_log_bin=0;
create user repuser@'%' identified by 'repuser123';

grant replication slave,replication client on *.* to repuser@'%';

create user repuser@'127.0.0.1' identified by 'repuser123';

grant replication slave,replication client on *.* to repuser@'127.0.0.1';

create user repuser@'localhost' identified by 'repuser123';

grant replication slave,replication client on *.* to repuser@'localhost';

set sql_log_bin=1;

Step 2: Configure the user used for replication

change master to master_user='repuser',master_password='repuser123' for channel 'group_replication_recovery';

Step 3: Install the mysql group replication plugin

Note: If you write plugin_load="group_replication=group_replication.so" in my.cnf, you don't need to install plugin group_replication soname 'group_replication.so';


Check whether the installation is successful by showing plugins;

Step 4: Join the replication group created earlier

start group_replication;
select * from performance_schema.replication_group_members;

The following error occurs

This is an error caused by binlog. Execute the following

reset master;
start group_replication;
select * from performance_schema.replication_group_members;

Check the MGR status:

View the read and write status of the MGR master

Check the read and write status of the two slaves of MGR

This is the end of this article about how to configure MGR single master and multiple slaves in MySQL 8.0.15. For more relevant MySQL MGR single master and multiple slaves content, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!

You may also be interested in:

What are the advantages of MySQL MGR?
Detailed explanation of mysql MGR single-master and multi-master mode switching knowledge points
mysql 8.0.18 mgr installation and its switching function
MySQL 8.0.18 uses clone plugin to rebuild MGR implementation
Detailed explanation of MySQL 5.7 MGR single master determination master node method
Common problems and solutions during MySQL MGR construction

<<: Detailed explanation of docker compose usage

>>: Vue implements the function of calling the mobile phone camera and album