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

This article is about how to build a MySQL Group Replication test environment. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

Recently, I took a look at the shiny feature Group Replication in MySQL 5.7.I also spent a lot of time doing some tests, and found that some aspects performed really well. Of course, it takes a lot of work to simulate such a set of environment, generally speaking, it is a three-node environment, in fact, it is not very easy to find such an environment. How can we simulate quickly. One way is to build multiple instances on one server.

In this way, the problem of the server is solved, and the problem to be solved is much more difficult, that is, the deployment environment.

You can see that there are detailed explanations in various blogs, and a lot of space has been spent on the construction process in the official documents to explain the meaning of each step, every operation, and each parameter. But in spite of this, the probability of success in building this environment at once is still very low. Not to mention the initialization part, there are always some problems when adding nodes, these problems have tortured me for several days, and most of the time we have to do subtraction for troubleshooting problems, and practice makes perfect. After building it more than ten times, I also briefly summed up my own experience. Such an essence is the script.

Of course, this is a very initial script, no dynamic variables, no complex condition judgment, I am just tired of so much code, try again and again, can be built in a minute to meet the basic requirements of the environment.

I'll just go straight to a relatively simple script, which is not technically a script. Initialize the part of the environment

Mysqld-initialize-insecure-basedir=/usr/local/mysql-datadir=/home/mysql/data/s1-explicit_defaults_for_timestamp

Mysqld-initialize-insecure-basedir=/usr/local/mysql-datadir=/home/mysql/data/s2-explicit_defaults_for_timestamp

Mysqld-initialize-insecure-basedir=/usr/local/mysql-datadir=/home/mysql/data/s3-explicit_defaults_for_timestamp

Configuration parameter file

Chown-R mysql:mysql S1 S2 S3

Cp s1.cnf s1

Cp s2.cnf s2

Cp s3.cnf s3

Chown-R mysql:mysql S1 S2 S3

Start the MySQL service

/ usr/local/mysql/bin/mysqld_safe-defaults-file=/home/mysql/data/s1/s1.cnf &

/ usr/local/mysql/bin/mysqld_safe-defaults-file=/home/mysql/data/s2/s2.cnf &

/ usr/local/mysql/bin/mysqld_safe-defaults-file=/home/mysql/data/s3/s3.cnf &

Pause for a few seconds to ensure that the service can start normally

Sleep 10

Connect to each instance to see if the database is available

/ usr/local/mysql/bin/mysql-P24081-S / home/mysql/s1.sock-e "show databases"

/ usr/local/mysql/bin/mysql-P24082-S / home/mysql/s2.sock-e "show databases"

/ usr/local/mysql/bin/mysql-P24083-S / home/mysql/s3.sock-e "show databases"

Connect to the first node, run the following script, initialize the installation of the GR plug-in, and do a basic configuration mysql-P24081-S / home/mysql/s1.sock-e "

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

"

Connect to the second node and initialize the GR plug-in and basic configuration, which is slightly different from the first node. Mysql-P24082-S / home/mysql/s2.sock-e "

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

Start group_replication

Select * from performance_schema.replication_group_members

"

Connect to the second node and initialize the GR plug-in and basic configuration, which is slightly different from the first node. Mysql-P24083-S / home/mysql/s3.sock-e "

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

Start group_replication

Select * from performance_schema.replication_group_members

"the template of the parameter file is as follows, except for the red part, everything else remains the same. The port I use here is 24801, not the default 3306, of course there is no limit to this.

[mysqld]

# server configuration

Datadir=/home/mysql/data/s1

Basedir=/usr/local/mysql

Port=24801

Socket=/home/mysql/s1.sock

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= "1bb1b861-f776-11e6-be42-782bcb377193"

Loose-group_replication_start_on_boot=off

Loose-group_replication_local_address= "127.0.0.1purl 24901"

Loose-group_replication_group_seeds= "127.0.0.1VOR 24901127.0.0.1VOF 24902127.0.1VOF 24903"

Loose-group_replication_bootstrap_group= off

The output of the running script is as follows, and finally it shows that all the nodes have been added successfully.

+-+

| | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | |

+-+

| | group_replication_applier | b3684636-f83d-11e6-bd53-782bcb377193 | grtest | 24801 | ONLINE | |

| | group_replication_applier | b4a7d208-f83d-11e6-be4e-782bcb377193 | grtest | 24802 | ONLINE | |

| | group_replication_applier | b5eecf1d-f83d-11e6-80d7-782bcb377193 | grtest | 24803 | ONLINE |

+-+

Thank you for reading! This is the end of this article on "how to build a MySQL Group Replication test environment". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it for more people to see!

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.