Shulou(Shulou.com)05/31 Report--

This article mainly introduces "the building process of MYSQL Group Replication". In the daily operation, I believe many people have doubts about the building process of MYSQL Group Replication. The editor consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful for you to answer the doubts about "the building process of MYSQL Group Replication"! Next, please follow the editor to study!

1. Environmental preparation

Operating system redhat linux 6.8

3 virtual machines

One for primary and the other two for secondary

Note:

Please set up the hostname and / etc/hosts of each host first

Mysql version: mysql-5.7.20

The role of principal name ip address in mgr

Mgrhost01 192.168.43.143 primary

Mgrhost02 192.168.43.144 seconde

Mgrhost03 192.168.43.145 seconde

two。 Install the mysql data service

Cd / tmp/

Wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz

Tar-xvf mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz-C / usr/local/

Cd / usr/local/

Ln-s mysql-5.7.20-linux-glibc2.12-x86_64 mysql

Note: all three environments need to be installed

Execute the following command on the three hosts:

Mkdir-p / database/mysql/data/3306

Useradd mysql

Chown-R mysql:mysql / database/mysql/data/3306

Chown-R mysql:mysql / usr/local/mysql*

3. Configure the mysql service

All three hosts add configuration files: / etc/my.cnf

The configuration file of 143 is as follows:

Basedir=/usr/local/mysql/

Datadir=/database/mysql/data/3306

Port=3306

Socket=/tmp/mysql.sock

Server_id=143

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=mysql-bin

Binlog_format=row

Relay-log-recovery=1

Transaction_write_set_extraction=XXHASH64

Loose-group_replication_group_name= "aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"

Loose-group_replication_start_on_boot=off

Loose-group_replication_local_address= "192.168.43.143 purl 33060"

Loose-group_replication_group_seeds= "192.168.43.143Vera 33060192.168.43.144WR 33060192.168.43.145WR 33060"

Loose-group_replication_bootstrap_group= off

/ etc/my.cnf of 192.168.43.144

[mysqld]

Basedir=/usr/local/mysql/

Datadir=/database/mysql/data/3306

Port=3306

Socket=/tmp/mysql.sock

Server_id=144

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=mysql-bin

Binlog_format=row

Relay-log-recovery=1

Transaction_write_set_extraction=XXHASH64

Loose-group_replication_group_name= "aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"

Loose-group_replication_start_on_boot=off

Loose-group_replication_local_address= "192.168.43.144purl 33060"

Loose-group_replication_group_seeds= "192.168.43.143Vera 33060192.168.43.144WR 33060192.168.43.145WR 3306"

Loose-group_replication_bootstrap_group= off

192.168.43.145 / etc/my.cnf

[mysqld]

Basedir=/usr/local/mysql/

Datadir=/database/mysql/data/3306

Port=3306

Socket=/tmp/mysql.sock

Server_id=145

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=mysql-bin

Binlog_format=row

Relay-log-recovery=1

Transaction_write_set_extraction=XXHASH64

Loose-group_replication_group_name= "aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"

Loose-group_replication_start_on_boot=off

Loose-group_replication_local_address= "192.168.43.145 purl 33060"

Loose-group_replication_group_seeds= "192.168.43.143Vera 33060192.168.43.144WR 33060192.168.43.145WR 3306"

Loose-group_replication_bootstrap_group= off has some technical details to explain here:

The above three configuration files omit all unnecessary configuration items, but they still seem to be a bit too many, which are required by the mgr environment.

Should each instance of server_id be sampled or not?

Loose-group_replication_group_name: give a name to the mgr high availability group, which must be in uuid format.

All instances of loose-group_replication_local_address:mgr need to communicate before. This configuration item sets the ip: Port that this instance listens to.

Loose-group_replication_group_seeds: the ip: Port information that each mgr instance listens to initialize mysql in three environments (note that the root user password of mysql will be set to a null value):

/ usr/local/mysql/bin/mysqld-defaults-file=/etc/my.cnf-datadir=/database/mysql/data/3306/-user=mysql-initialize-insecure

Three environments add mysql to system services

Cp / usr/local/mysql/support-files/mysql.server / etc/rc.d/init.d/mysqld

Chmod + x / etc/rc.d/init.d/mysqld

Chkconfig mysqld on

Modify the environment variable:

Echo 'PATH=/usr/local/mysql/bin/:$PATH' > > / etc/profile

Start the mysql service:

Service mysqld start

Stop the mysql service:

Service mysqld stop

4. Configure MGR

4.1 users required to create mgr (first node)

Set sql_log_bin=0

Create user mgruser@'%' identified by 'mtls@352'

Grant replication slave,replication client on *. * to mgruser@'%'

Create user mgruser@'127.0.0.1' identified by 'mtls@352'

Grant replication slave,replication client on *. * to mgruser@'127.0.0.1'

Create user mgruser@'localhost' identified by 'mtls@352'

Grant replication slave,replication client on *. * to mgruser@'localhost'

Set sql_log_bin=1

4.2 configure the users used for replication

Change master to

Master_user='mgruser'

Master_password='123456'

For channel 'group_replication_recovery'

4.3 install the MGR plug-in install plugin group_replication soname 'group_replication.so'

4.4. Initialize a replication group

Set global group_replication_bootstrap_group=on

Start group_replication

Set global group_replication_bootstrap_group=off

4.5. Configure the second node of the MGR

Set sql_log_bin=0

Create user mgruser@'%' identified by '123456'

Grant replication slave,replication client on *. * to mgruser@'%'

Create user mgruser@'127.0.0.1' identified by '123456'

Grant replication slave,replication client on *. * to mgruser@'127.0.0.1'

Create user mgruser@'localhost' identified by '123456'

Grant replication slave,replication client on *. * to mgruser@'localhost'

Set sql_log_bin=1

Change master to

Master_user='mgruser'

Master_password='123456'

For channel 'group_replication_recovery'

Install plugin group_replication soname 'group_replication.so'; start group_replication

Use the above script to process the remaining nodes in turn.

Verify:

At this point, the configuration is complete

All the nodes in mgr belong to a logical group, which is like a QQ group, which is built by the group owner. With this upper group, other nodes can be added to this group.

Attention items in the building:

1. When installing the virtual machine operating system, because hostname could not be set up during installation, hostname was modified and group_replication could not start normally after rebooting the operating system.

Mysql > start group_replication

Set global group_replication_bootstrap_group=off

ERROR 3094 (HY000): The START GROUP_REPLICATION command failed as the applier module failed to start.

Replication is not started normally until relay-log-recovery=1 is added to the parameter file / etc/my.cnf

two。 It is best to set up hostname and / etc/hosts before installing the mysql service

The content of / etc/hosts in this case is:

192.168.43.143 mgrhost01

192.168.43.144 mgrhost02

192.168.43.145 mgrhost03

3. Select * from performance_schema.replication_group_members; it is normal that the member_state fields of the column of this SQL query should all be state. This is the end of the study on "the process of building MYSQL Group Replication". I hope you can solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.