MySQL 5.7.17 Group Replication 01/27 Update SLTechnology News&Howtos

MySQL 5.7.17 Group Replication

2025-01-27 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Shulou(Shulou.com)06/01 Report--

The powerful function based on group replication was implemented as a plug-in after MySQL 5.7.17. This paper describes how to build a test environment for group replication on the basis of single machine and multiple instances.

Environment description:

Operating system:

CentOS Linux release 7.3.1611 (Core)

Kernel version:

Linux version 3.10.0-514.6.2.el7.x86_64

MySQL version:

Mysql-5.7.17-linux-glibc2.5-x86_64.tar.gz

Dependency package installation:

Yum-y install gcc gcc-c++ libaio-devel boost-devel autoconf automake zlib-devel libxml2-devel ncurses-devel libgcrypt-devel libtool-devel openssl-devel bison-devel unzip numactl-devel

At the same time, selinux and firewall need to be turned off.

Setenforce 0systemctl stop firewalldsystemctl disabled firewalld

MySQL5.7.17 installation and configuration

Download and extract to the installation directory

Wget http://mirrors.sohu.com/mysql/MySQL-5.7/mysql-5.7.17-linux-glibc2.5-x86_64.tartar xf mysql-5.7.17-linux-glibc2.5-x86_64.tarmv mysql-5.7.17-linux-glibc2.5-x86_64 / usr/local/mysql5.7.17

Add environment variabl

Echo 'PATH=/usr/local/mysql5.7.17/bin:$PATH' > > / etc/profilesource / etc/profile

Manually create a mysql user

Useradd-s / sbin/nologin-d / dev/null mysql

Add native hostname resolution

Echo "127.0.0.1$ HOSTNAME" > > / etc/host

MySQL multiple instances: (three instances)

We will build three examples: 3306, 3307 and 3308.

Create a data file directory

Mkdir-p / usr/local/mysql5.7.17/data/330 {6, 7, 7, 8}

Create a profile directory

Mkdir-p / usr/local/mysql5.7.17/conf

Create a profile

Configuration file of 3306 instance

Cat / usr/local/mysql5.7.17/conf/3306.cnf

[mysqld] basedir=/usr/local/mysql5.7.17datadir=/usr/local/mysql5.7.17/data/3306port = 3306socket = / usr/local/mysql5.7.17/data/3306/mysqld.socklog-error=/usr/local/mysql5.7.17/data/3306/mysqld.loguser = mysqlserver_id = 3306gtid_mode = ONenforce_gtid_consistency = ONmaster_info_repository = TABLErelay_log_info_repository = TABLEbinlog_checksum = NONElog_slave_updates = ONlog_bin = binlogbinlog_format = ROW# Make the basic configuration transaction_write_set_extraction = XXHASH64loose-group_replication_group_name= "01e5fb97-be64-41f7-bafd-3afc7a6ab555" loose-group_replication_start_on_boot=offloose-group_replication_local_address= "127.0.0.1 01e5fb97-be64 13306" loose-group_replication_group_seeds= "127.0.0.1 01e5fb97-be64 13306127.0.0.1" loose-group_replication_bootstrap_group = off#loose-group_replication_ip_whitelist= "10.30.0.0Universe 16, 10.31.0... 0.16, 10.27.0.0, 16"

Description:

The loose- prefix used by the group_replication variable indicates that the replication plug-in will continue to start when Server is enabled.

Transaction_write_set_extraction = XXHASH64 indicates that Server must collect write sets for each transaction and encode it as a hash using the XXHASH64 hash algorithm

Loose-group_replication_group_name= "01e5fb97-be64-41f7-bafd-3afc7a6ab555" means to join or create a replication group named 01e5fb97-be64-41f7-bafd-3afc7a6ab555, which can be customized (via cat / proc/sys/kernel/random/uuid)

Group replication does not start automatically when loose-group_replication_start_on_boot=off is set to Server startup

Loose-group_replication_local_address= "127.0.0.1 13306" binds local ports 127.0.0.1 and 13306 to accept connections from other group members. The IP address must be accessible to other group members.

This behavior tells the server that when the server joins the group, it should connect to the seed servers of loose-group_replication_group_seeds= 13306127.0.0.1, which are seed servers for configuration. This setting may not be the service address of all group members.

Loose-group_replication_bootstrap_group = whether the off configuration automatically guides the group

By default, only 127.0.0.1 is allowed to connect to the replication group for loose-group_replication_ip_whitelist= "10.30.0.0 IP 16 10.31.0... 0", and configuration is required in the case of other CPUs.

Configuration file of 3307 instance

Cat / usr/local/mysql5.7.17/conf/3307.cnf

[mysqld] basedir=/usr/local/mysql5.7.17datadir=/usr/local/mysql5.7.17/data/3307port = 3307socket = / usr/local/mysql5.7.17/data/3307/mysqld.socklog-error=/usr/local/mysql5.7.17/data/3307/mysqld.loguser = mysqlserver_id = 3307gtid_mode = ONenforce_gtid_consistency = ONmaster_info_repository = TABLErelay_log_info_repository = TABLEbinlog_checksum = NONElog_slave_updates = ONlog_bin = binlogbinlog_format = ROWtransaction_write_ Set_extraction = XXHASH64loose-group_replication_group_name= "01e5fb97-be64-41f7-bafd-3afc7a6ab555" loose-group_replication_start_on_boot=offloose-group_replication_local_address= "127.0.0.1 01e5fb97-be64 13307" loose-group_replication_group_seeds= "127.0.0.1 01e5fb97-be64 13306127.0.0.1 Vera 13307127.0.1RV 13308" loose-group_replication_bootstrap_group = offloose-group_replication_ip_whitelist= "10.30.0.0According to 16Mague 10. 31.0... Universe 16, 10.27.0, 16 ".

3308 instance profile

Cat / usr/local/mysql5.7.17/conf/3308.cnf

[mysqld] basedir=/usr/local/mysql5.7.17datadir=/usr/local/mysql5.7.17/data/3308port = 3308socket = / usr/local/mysql5.7.17/data/3308/mysqld.socklog-error=/usr/local/mysql5.7.17/data/3308/mysqld.loguser = mysqlserver_id = 3308gtid_mode = ONenforce_gtid_consistency = ONmaster_info_repository = TABLErelay_log_info_repository = TABLEbinlog_checksum = NONElog_slave_updates = ONlog_bin = binlogbinlog_format = ROWtransaction_write_ Set_extraction = XXHASH64loose-group_replication_group_name= "01e5fb97-be64-41f7-bafd-3afc7a6ab555" loose-group_replication_start_on_boot=offloose-group_replication_local_address= "127.0.0.1 01e5fb97-be64 13308" loose-group_replication_group_seeds= "127.0.0.1 01e5fb97-be64 13306127.0.0.1 Vera 13307127.0.1" loose-group_replication_bootstrap_group = offloose-group_replication_ip_whitelist= "10.30.0.0According to 16Mague 10. 31.0... Universe 16, 10.27.0, 16 ".

Initialize the database:

/ usr/local/mysql5.7.17/bin/mysqld-initialize-insecure-basedir=/usr/local/mysql5.7.17-datadir=/usr/local/mysql5.7.17/data/3306/usr/local/mysql5.7.17/bin/mysqld-initialize-insecure-basedir=/usr/local/mysql5.7.17-datadir=/usr/local/mysql5.7.17/data/3307/usr/local/mysql5.7.17/bin/mysqld-initialize-insecure-basedir= / usr/local/mysql5.7.17-- datadir=/usr/local/mysql5.7.17/data/3308chown-R mysql.mysql / usr/local/mysql5.7.17/data/*

Start the database:

/ usr/local/mysql5.7.17/bin/mysqld-defaults-file=/usr/local/mysql5.7.17/conf/3306.cnf & / usr/local/mysql5.7.17/bin/mysqld-defaults-file=/usr/local/mysql5.7.17/conf/3307.cnf & / usr/local/mysql5.7.17/bin/mysqld-defaults-file=/usr/local/mysql5.7.17/conf/3308.cnf &

3306 examples:

Mysql-S / usr/local/mysql5.7.17/data/3306/mysqld.sock# create replication user and password set sql_log_bin=0;grant replication slave,replication client on. * to 'repluser'@'%' identified by' replpass';flush privileges;set sql_log_bin=1;# set replication username and password change master to master_user='repluser',master_password='replpass' for channel 'group_replication_recovery';# installation group replication plug-in install plugin group_replication soname' group_replication.so' # check whether the plug-in is installed successfully. Show plugins;# starts the group replication set global group_replication_bootstrap_group=ON;START group_replication;set global group_replication_bootstrap_group=OFF;exit.

3307 examples:

Mysql-S / usr/local/mysql5.7.17/data/3307/mysqld.sockset sql_log_bin=0;grant replication slave,replication client on *. * to 'repluser'@'%' identified by' replpass';flush privileges;set sql_log_bin=1;change master to master_user='repluser',master_password='replpass' for channel 'group_replication_recovery';install plugin group_replication soname' group_replication.so';show plugins;# initiates group replication START group_replication;exit

3308 examples:

Mysql-S / usr/local/mysql5.7.17/data/3308/mysqld.sockset sql_log_bin=0;grant replication slave,replication client on *. * to 'repluser'@'%' identified by' replpass';flush privileges;set sql_log_bin=1;change master to master_user='repluser',master_password='replpass' for channel 'group_replication_recovery';install plugin group_replication soname' group_replication.so';show plugins;# initiates group replication START group_replication;exit

Note: differences in replication of 3306, 3307, 3308 initiator groups

Instance name startup group replication command 3306

Set global group_replication_bootstrap_group=ON

START group_replication

Set global group_replication_bootstrap_group=OFF

3307START group_replication;3308START group_replication

View the results of group replication:

You can see that the MEMBER_STATE is all ONLINE, and the group replication configuration is successful.

Group replication Test:

Mysql-S / usr/local/mysql5.7.17/data/3306/mysqld.sockmysql > create database test;mysql > create table tb1 (id int not null); mysql > insert into tb1 values (1); The table does not comply with the requirements by an external plugin.# reports an error because the table in group_replication must have a primary key to guarantee uniqueness, otherwise it fails. Mysql > drop table tb1;mysql > create table tb1 (id int not null primary key auto_increment,username varchar); mysql > insert into tb1 values

Test in other examples:

Mysql-S / usr/local/mysql5.7.17/data/3307/mysqld.sockmysql > select * from test.tb1;+----+-+ | id | username | +-- +-+ | 1 | guest | +-+-- + mysql-S / usr/local/mysql5.7.17/data/3308/mysqld.sockmysql > select * from test.tb1 +-+-- +-+ | id | username | +-+-+ | 1 | guest | +-+-- + mysql > insert into test.tb1 values (2); The MySQL server is running with the-- super-read-only option so it cannot execute this statement

In single master mode, only the host is allowed to write, and the rest are read-only mode

Find the primary instance in single master mode

You can know that port 3306 of haproxy206 is the primary instance.

Pay attention to the above

Group_replication_enforce_update_everywhere_checks | OFF group_replication_single_primary_mode | ON

Change from single master mode to multi-master mode

3306 instance: mysql-S / usr/local/mysql5.7.17/data/3306/mysqld.sockSTOP group_replication;set global group_replication_single_primary_mode=OFF;set global group_replication_enforce_update_everywhere_checks= ON;set global group_replication_bootstrap_group=ON;START group_replication;set global group_replication_bootstrap_group=OFF;3307 example: mysql-S / usr/local/mysql5.7.17/data/3307/mysqld.sockSTOP group_replication;set global group_replication_single_primary_mode=OFF Set global group_replication_enforce_update_everywhere_checks= ON;START group_replication;3308 instance: mysql-S / usr/local/mysql5.7.17/data/3308/mysqld.sockSTOP group_replication;set global group_replication_single_primary_mode=OFF;set global group_replication_enforce_update_everywhere_checks= ON;START group_replication

After the above configuration, you can add, delete and modify each instance.

Add nodes

We have three members with group replication of 3306, 3307, and 3308, respectively, and now we have added 3309 instances.

Mkdir-p / usr/local/mysql5.7.17/data/3309

Cat / usr/local/mysql5.7.17/conf/3309.cnf

[mysqld] basedir=/usr/local/mysql5.7.17datadir=/usr/local/mysql5.7.17/data/3309port = 3309socket = / usr/local/mysql5.7.17/data/3309/mysqld.socklog-error=/usr/local/mysql5.7.17/data/3309/mysqld.loguser = mysqlserver_id = 3309gtid_mode = ONenforce_gtid_consistency = ONmaster_info_repository = TABLErelay_log_info_repository = TABLEbinlog_checksum = NONElog_slave_updates = ONlog_bin = binlogbinlog_format = ROWtransaction_write_ Set_extraction = XXHASH64loose-group_replication_group_name= "01e5fb97-be64-41f7-bafd-3afc7a6ab555" loose-group_replication_start_on_boot=offloose-group_replication_local_address= "127.0.0.1 01e5fb97-be64 13309" loose-group_replication_group_seeds= "127.0.0.1purl 13306127.0.0.1 Vera 13307127.0.1VOF 13308" loose-group_replication_bootstrap_group = off#loose-group_replication_ip_whitelist= "10.30.0.0 / 16pr 10.31.0... 0pr 16pm 10.27.0.0Compact 16 "

Initialize the database and start the database

/ usr/local/mysql5.7.17/bin/mysqld-initialize-insecure-basedir=/usr/local/mysql5.7.17-datadir=/usr/local/mysql5.7.17/data/3309chown-R mysql.mysql / usr/local/mysql5.7.17/data/3309/usr/local/mysql5.7.17/bin/mysqld-defaults-file=/usr/local/mysql5.7.17/conf/3309.cnf &

Configuration

Mysql-S / usr/local/mysql5.7.17/data/3309/mysqld.sockset sql_log_bin=0;grant replication slave,replication client on *. * to 'repluser'@'%' identified by' replpass';flush privileges;set sql_log_bin=1;change master to master_user='repluser',master_password='replpass' for channel 'group_replication_recovery';install plugin group_replication soname' group_replication.so';show plugins;START group_replication

View group members

If the amount of data is large, 3309 of the status may be recovering

Problem handling:

2017-06-21T15:06:01.854288+08:00 0 [Warning] Plugin group_replication reported:'[GCS] Connection attempt from IP address 192.168.0.2 refused. Address is not in the IP whitelist.'

Reason: the default value of group_replication_ip_whitelist is 127.0.0.1 Universe 8, which is set to the required configuration

Processing: set global group_replication_ip_whitelist='192.168.0.0/24' (multiple network segments are separated by commas)

Slave O for channel 'group_replication_recovery': Fatal error: Invalid (empty) username when attempting to connect to the master server. Connection attempt terminated. Error_code: 1593

Reason: no synchronization account and password are configured, but an empty password is used for synchronization. Synchronization information needs to be set for replication channel group_replication_recovery. Be sure to pay attention to synchronization channel group_replication_recovery.

Processing: change master to master_user='repluser',master_password='replpass' for channel 'group_replication_recovery'

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.