Mysql5.7 MGR cluster creation

Mysql5.7 MGR cluster creation
Recently I checked that the MGR cluster of mysql5.7 is quite good, with single-master and multi-master modes. As a result, the setup and testing results are quite good. I mean that the setup and maintenance are relatively simple. The vast majority of the online models are single-master mode. Of course, I am also here. In order to deepen my impression, I will record the building process and try the multi-master mode later, I believe that the bottleneck of the database is basically writing. Although read/write splitting is a feasible solution, if the data volume is large, there will be problems like writing, although some solutions can deploy multiple master nodes for simultaneous read/write operations, split-brain is a serious problem, therefore, the MGR cluster has built-in automatic split-brain protection mechanism, which has been favored by many people, MGR (MySQL Group Replication) is a new high availability and high scalability solution officially launched by MySQL in December 2016. Note that this article will not elaborate on principles.
Note: I will use the compilation and installation method here. If you want to simply install mysql5.7 using yum, the disk space required for mysql compilation and installation is still relatively large, generally around 7 GB, therefore, it is necessary to plan in advance, use three nodes closer to the production environment, and make them clearer.
Detailed deployment information is as follows:

Host Name	IP address	Install software	Purpose
Apache	192.168.2.25	Cmake, boost, mysql	Node
Nginx	192.168.2.26	Cmake, boost, mysql	Node
Kibana	192.168.2.30	Cmake, boost, mysql	Node

1. Prepare three machines

 
 

  
  
rpm -qa mysql mariadb
 
 

If yes, uninstall it!
Write the hosts file ing relationship.
192.168.2.25 apache
192.168.2.26 nginx
192.168.2.30 kibana
2. Install the dependency package

 
 

  
  
yum install gcc gcc-c++ncurses-devel -y
 
 

3, install cmake,: https://cmake.org/download/

 
 

  
  
tar zxvf cmake-3.7.2.tar.gz
  
  
cd make-3.7.2
  
  
./configure
  
  
gmake && gmake install
 
 

4. Install boost because mysql5.7 is required. Note that the downloaded version is ipv59_0 and mysql. If your MySQL version is different from mine, if the-DWITH_BOOST parameter is not added, an error is reported to indicate which version of boost is to be downloaded.

 
 

  
  
tar zxvf boost_1_59_0.tar.gz
  
  
cp -r boost_1_59_0 /usr/local/boost
 
 

5. Install and initialize mysql5.7.17.

 
 

  
  
Groupadd mysql
  
  
Useradd-M-s/sbin/nologin mysql-g mysql
  
  
Tar zxvf mysql-5.7.17.tar.gz
  
  
Cd mysql-5.7.17
  
  
Cmake-DCMAKE_INSTALL_PREFIX =/data/mysql-DSYSCONFDIR =/etc-DDEFAULT_CHARSET = utf8-DDEFAULT_COLLATION = utf8_general_ci-keys = all-DWITH_BOOST =/usr/local/boost
  
  
Make
  
  
Make install
  
  
Chown-R mysql. mysql/data/mysql
  
  
Mv/etc/my. cnf/etc/my. cnf. default
  
  
Cp/data/mysql/support-files/my-default.cnf/etc/my. cnf
  
  
/Data/mysql/bin/mysqld -- initialize -- user = mysql -- basedir =/data/mysql -- datadir =/data/mysql/data // note that a random password will be generated during initialization., remember
  
  
Echo "PATH = $ PATH:/data/mysql/bin">/etc/profile

  
  
Source/etc/profile
  
  
Cp/data/mysql/support-files/mysql. server/etc/rc. d/init. d/mysqld
  
  
Chmod + x/etc/rc. d/init. d/mysqld

 
 

The preceding steps must be performed on all three machines.
6. Start to build the MGR Cluster Environment and modify the my. cnf file of the first node. The content is as follows:

 
 

  
  
# For advice on how to change settings please see
  
  
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
  
  
# *** DO NOT EDIT THIS FILE. It's a template which will be copied to the
  
  
# *** default location during install, and will be replaced if you
  
  
# *** upgrade to a newer version of MySQL.
  
  

  
  
[mysqld]
  
  

  
  
# Remove leading # and set to the amount of RAM for the most important data
  
  
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
  
  
# innodb_buffer_pool_size = 128M
  
  

  
  
# Remove leading # to turn on a very important data integrity option: logging
  
  
# changes to the binary log between backups.
  
  
# log_bin
  
  

  
  
# These are commonly set, remove the # and set as required.
  
  
basedir = /data/mysql
  
  
datadir = /data/mysql/data
  
  
port = 3306
  
  
socket = /data/mysql/data/mysql.sock
  
  
log-error = /data/mysql/data/mysqld.log
  
  
pid-file = /data/mysql/data/mysqld.pid
  
  

  
  
# Remove leading # to set options mainly useful for reporting servers.
  
  
# The server defaults are faster for transactions and fast SELECTs.
  
  
# Adjust sizes as needed, experiment to find the optimal values.
  
  
# join_buffer_size = 128M
  
  
# sort_buffer_size = 2M
  
  
# read_rnd_buffer_size = 2M
  
  

  
  
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
  
  

  
  
# Group Replication
  
  
server_id = 1
  
  
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
  
  
loose-group_replication_group_name = 'ce9be252-2b71-11e6-b8f4-00212844f856'
  
  
loose-group_replication_start_on_boot = off
  
  
loose-group_replication_local_address = '192.168.2.25:33061'
  
  
loose-group_replication_group_seeds ='192.168.2.25:33061,192.168.2.26:33061,192.168.2.30:33061'
  
  
loose-group_replication_bootstrap_group = off
  
  

  
  
[client]
  
  
socket = /data/mysql/data/mysql.sock
 
 

Start mysql Service
/Etc/init. d/mysqld start

 
 

  
  
set sql_log_bin=0;
  
  
create user rpl_user@'%';
  
  
grant replication slave on *.* to rpl_user@'%' identified by 'rpl_pass';
  
  
flush privileges;
  
  
set sql_log_bin=1;
  
  
change master to master_user='rpl_user',master_password='rpl_pass' for channel 'group_replication_recovery';
  
  
install PLUGIN group_replication SONAME 'group_replication.so';
  
  
set global group_replication_bootstrap_group=ON;
  
  
start group_replication;
  
  
set global group_replication_bootstrap_group=OFF;
  
  
select * from performance_schema.replication_group_members;

 
 

The result is as follows:

If ONLINE occurs, it means it is normal. This is the master node, and two slave nodes are created.
7. Add the second node to the cluster and copy the master configuration file my. cnf of the first node. You only need to modify the configuration file in red.

 
 

  
  
# For advice on how to change settings please see
  
  
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
  
  
# *** DO NOT EDIT THIS FILE. It's a template which will be copied to the
  
  
# *** default location during install, and will be replaced if you
  
  
# *** upgrade to a newer version of MySQL.
  
  

  
  
[mysqld]
  
  

  
  
# Remove leading # and set to the amount of RAM for the most important data
  
  
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
  
  
# innodb_buffer_pool_size = 128M
  
  

  
  
# Remove leading # to turn on a very important data integrity option: logging
  
  
# changes to the binary log between backups.
  
  
# log_bin
  
  

  
  
# These are commonly set, remove the # and set as required.
  
  
basedir = /data/mysql
  
  
datadir = /data/mysql/data
  
  
port = 3306
  
  
socket = /data/mysql/data/mysql.sock
  
  
log-error = /data/mysql/data/mysqld.log
  
  
pid-file = /data/mysql/data/mysqld.pid
  
  

  
  
# Remove leading # to set options mainly useful for reporting servers.
  
  
# The server defaults are faster for transactions and fast SELECTs.
  
  
# Adjust sizes as needed, experiment to find the optimal values.
  
  
# join_buffer_size = 128M
  
  
# sort_buffer_size = 2M
  
  
# read_rnd_buffer_size = 2M
  
  

  
  
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
  
  

  
  
# Group Replication
  
  
server_id = 2
  
  
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
  
  
loose-group_replication_group_name = 'ce9be252-2b71-11e6-b8f4-00212844f856'
  
  
loose-group_replication_start_on_boot = off
  
  
loose-group_replication_local_address = '192.168.2.26:33061'
  
  
loose-group_replication_group_seeds ='192.168.2.25:33061,192.168.2.26:33061,192.168.2.30:33061'
  
  
loose-group_replication_bootstrap_group = off
  
  

  
  
[client]
  
  
socket = /data/mysql/data/mysql.sock
 
 

Run the following command on the second node:

 
 

  
  
set sql_log_bin=0;
  
  
create user rpl_user@'%';

  
  
grant replication slave on *.* to rpl_user@'%' identified by 'rpl_pass';
  
  
set sql_log_bin=1;
  
  
change master to master_user='rpl_user',master_password='rpl_pass' for channel 'group_replication_recovery';
  
  
install plugin group_replication SONAME 'group_replication.so';
  
  
set global group_replication_allow_local_disjoint_gtids_join=ON;
  
  
startgroup_replication;
 
 

The result is as follows:

Similarly, adding a third node is the same as adding a second node.
As follows:

Query which is the master node:

Obviously, the apache host is the master node.
Test procedure:
1. Create a database in the master database, create a table, and query data synchronization between the two slave databases?
2. Can I query only two slave databases?
2. manually close the master database and check whether one of the two slave databases changes to the master database? What is the master database of MEMBER_ID sorted by priority by the first letter?
Routine maintenance steps:
1. If a slave database node is closed

 
 

  
  
start group_replication;
 
 

2. If all databases are closed, the first database is executed as the master database first.

 
 

  
  
set global group_replication_bootstrap_group=ON;
  
  
start group_replication;
 
 

Execute the remaining databases directly!

 
 

  
  
start group_replication;
 
 

3. If the master database fails, one master database is automatically selected from the two slave databases. After the master database is started, the following command is executed again and then changed to the slave database.

 
 

  
  
start group_replication;