Practice Mysql Group Replication Replication

Practice Process:

1. Install 3 MySQL (S1,S2,S3) on a single server

2. Configure S1, start Group Replication

3. Configure S2, add to Group

4. Configure S3, add to Group

5. Test

The content is long, may not be convenient to actually operate, I also made a PDF version , you can download view, send Message 'gr' will automatically reply

Detailed configuration procedures (1) Download mysql-5.7.17

https://cdn.mysql.com//Downloads/MySQL-5.7/mysql-5.7.17-linux-glibc2.5-x86_64.tar.gz

(2) Installation

Extract

tar zxf mysql-5.7.17-linux-glibc2.5-x86_64.tar.gzmv mysql-5.7.17-linux-glibc2.5-x86_64 /usr/local/mysql-5.7cd /usr/local

Initialize 3 MySQL (s1, S2, S3)

mkdir datamysql-5.7/bin/mysqld --initialize-insecure --basedir=$PWD/mysql-5.7 --datadir=$PWD/data/s1mysql-5.7/bin/mysqld --initialize-insecure --basedir=$PWD/mysql-5.7 --datadir=$PWD/data/s2mysql-5.7/bin/mysqld --initialize-insecure --basedir=$PWD/mysql-5.7 --datadir=$PWD/data/s3

(3) Create a MySQL user

groupadd mysql5.7useradd -g mysql5.7 mysql5.7chown -R mysql5.7:mysql5.7 /usr/local/mysql-5.7chown -R mysql5.7:mysql5.7 /usr/local/datasu mysql5.7

(4) Configure S1 start group replication

New configuration file data/s1/s1.cnf , content:

[mysqld]# server configurationdatadir=/usr/local/data/s1basedir=/usr/local/mysql-5.7/port=24801socket=/usr/local/data/s1/s1.sockserver_id=1gtid_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="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"loose-group_replication_start_on_boot=offloose-group_replication_local_address= "127.0.0.1:24901"loose-group_replication_group_seeds= "127.0.0.1:24901,127.0.0.1:24902,127.0.0.1:24903"loose-group_replication_bootstrap_group= offloose-group_replication_single_primary_mode=FALSEloose-group_replication_enforce_update_everywhere_checks= TRUE

Start S1

nohup mysql-5.7/bin/mysqld --defaults-file=data/s1/s1.cnf >data/s1/nohup.out 2>&1 &

Login S1

mysql-5.7/bin/mysql -uroot -h127.0.0.1 -P24801 --skip-password

Change the root login password

mysql> ALTER USER ‘root‘@‘localhost‘ IDENTIFIED BY ‘A123456‘;

Create a user for replication

mysql> SET SQL_LOG_BIN=0;mysql> CREATE USER [email protected]‘%‘;mysql> GRANT REPLICATION SLAVE ON *.* TO [email protected]‘%‘ IDENTIFIED BY ‘rpl_pass‘;mysql> FLUSH PRIVILEGES;mysql> SET SQL_LOG_BIN=1;mysql> CHANGE MASTER TO MASTER_USER=‘rpl_user‘, MASTER_PASSWORD=‘rpl_pass‘ FOR CHANNEL ‘group_replication_recovery‘;

Installing the group replication plug-in

mysql> INSTALL PLUGIN group_replication SONAME ‘group_replication.so‘;

Inspection

mysql> SHOW PLUGINS;

If the installation succeeds, you will see group_replication the record at the bottom of the result information

Start group replication

mysql> SET GLOBAL group_replication_bootstrap_group=ON;mysql> START GROUP_REPLICATION;mysql> SET GLOBAL group_replication_bootstrap_group=OFF;

View Group members

mysql> SELECT * FROM performance_schema.replication_group_members;

S1 configuration is complete, now create a test table to verify that replication succeeds when new members are added

mysql> CREATE DATABASE test;Query OK, 1 row affected (0,00 sec)mysql> use testDatabase changedmysql> CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 TEXT NOT NULL);Query OK, 0 rows affected (0,00 sec)mysql> INSERT INTO t1 VALUES (1, ‘Luis‘);Query OK, 1 row affected (0,01 sec)mysql> SELECT * FROM t1;+----+------+| c1 | c2   |+----+------+|  1 | Luis |+----+------+

(5) Add S2 to the replication group

New s2 configuration file data/s2/s2.cnf , content:

[mysqld]# server configurationdatadir=/usr/local/data/s1basedir=/usr/local/mysql-5.7/port=24802socket=/usr/local/data/s1/s1.sockserver_id=2gtid_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="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"loose-group_replication_start_on_boot=offloose-group_replication_local_address= "127.0.0.1:24902"loose-group_replication_group_seeds= "127.0.0.1:24901,127.0.0.1:24902,127.0.0.1:24903"loose-group_replication_bootstrap_group= offloose-group_replication_single_primary_mode=FALSEloose-group_replication_enforce_update_everywhere_checks= TRUE

Start s2

nohup mysql-5.7/bin/mysqld --defaults-file=data/s2/s2.cnf >data/s2/nohup.out 2>&1 &

Log in to S2

mysql-5.7/bin/mysql -uroot -h127.0.0.1 -P24802 --skip-password

Execute the following command to add the replication user

mysql> SET SQL_LOG_BIN=0;Query OK, 0 rows affected (0,00 sec)mysql> CREATE USER [email protected]‘%‘;Query OK, 0 rows affected (0,00 sec)mysql> GRANT REPLICATION SLAVE ON *.* TO [email protected]‘%‘ IDENTIFIED BY ‘rpl_pass‘;Query OK, 0 rows affected, 1 warning (0,00 sec)mysql> FLUSH PRIVILEGES;Query OK, 0 rows affected (0,00 sec)mysql> SET SQL_LOG_BIN=1;Query OK, 0 rows affected (0,00 sec)mysql> CHANGE MASTER TO MASTER_USER=‘rpl_user‘, MASTER_PASSWORD=‘rpl_pass‘ FOR CHANNEL ‘group_replication_recovery‘;Query OK, 0 rows affected, 2 warnings (0,01 sec)

Add a group replication plug-in

mysql> INSTALL PLUGIN group_replication SONAME ‘group_replication.so‘;Query OK, 0 rows affected (0,01 sec)

Start replication

mysql> START GROUP_REPLICATION;Query OK, 0 rows affected (44,88 sec)

View member information in the group now

mysql> SELECT * FROM performance_schema.replication_group_members;

Can see 2 members, S2 added successfully

Check the test table to see if the data has been copied over.

mysql> SHOW DATABASES LIKE ‘test‘;+-----------------+| Database (test) |+-----------------+| test            |+-----------------+1 row in set (0,00 sec)mysql> SELECT * FROM test.t1;+----+------+| c1 | c2   |+----+------+|  1 | Luis |+----+------+1 row in set (0,00 sec)

(6) Add a 3rd MySQL instance S3

New configuration file data/s3/s3.cnf

[mysqld]# server configurationdatadir=/usr/local/data/s3basedir=/usr/local/mysql-5.7/port=24803socket=/usr/local/data/s3/s3.sockserver_id=3gtid_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="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"loose-group_replication_start_on_boot=offloose-group_replication_local_address= "127.0.0.1:24903"loose-group_replication_group_seeds= "127.0.0.1:24901,127.0.0.1:24902,127.0.0.1:24903"loose-group_replication_bootstrap_group= offloose-group_replication_single_primary_mode=FALSEloose-group_replication_enforce_update_everywhere_checks= TRUE

Start S3

nohup mysql-5.7/bin/mysqld --defaults-file=data/s3/s3.cnf >data/s3/nohup.out 2>&1 &

Login S3

mysql-5.7/bin/mysql -uroot -h127.0.0.1 -P24803 --skip-password

Create a replication user

mysql> SET SQL_LOG_BIN=0;mysql> CREATE USER [email protected]‘%‘;mysql> GRANT REPLICATION SLAVE ON *.* TO [email protected]‘%‘ IDENTIFIED BY ‘rpl_pass‘;mysql> FLUSH PRIVILEGES;mysql> SET SQL_LOG_BIN=1;mysql> CHANGE MASTER TO MASTER_USER=‘rpl_user‘, MASTER_PASSWORD=‘rpl_pass‘ FOR CHANNEL ‘group_replication_recovery‘;

Install the group replication plug-in and start

INSTALL PLUGIN group_replication SONAME ‘group_replication.so‘;START GROUP_REPLICATION;

View Group members

mysql> SELECT * FROM performance_schema.replication_group_members;

Already 3 members, S3 successfully joined the

See if the data is copied.

mysql> SHOW DATABASES LIKE ‘test‘;+-----------------+| Database (test) |+-----------------+| test            |+-----------------+1 row in set (0,00 sec)mysql> SELECT * FROM test.t1;+----+------+| c1 | c2   |+----+------+|  1 | Luis |+----+------+1 row in set (0,00 sec)

No problem

(7) Test insert data in S3

Execution in S3

mysql> use testmysql> INSERT INTO t1 VALUES (2, ‘s3 test‘);mysql> select * from t1;+----+---------+| c1 | c2      |+----+---------+|  1 | Luis    ||  2 | s3 test |+----+---------+2 rows in set (0.00 sec)

Log S1 to see if the data was replicated successfully

mysql-5.7/bin/mysql -uroot -h127.0.0.1 -P24801 -pA123456

Inquire

mysql> use testmysql> select * from t1;+----+---------+| c1 | c2      |+----+---------+|  1 | Luis    ||  2 | s3 test |+----+---------+2 rows in set (0.00 sec)

Replication successful, group replication configuration complete

Practice Mysql Group Replication Replication