MySQL Group Replication configuration, mysqlreplication

MySQL Group Replication configuration, mysqlreplication
MySQL Group Replication

MySQL group replication implements Multi-master Update (single-master mode) based on the replication protocol ).

A replication group consists of multiple server members, and each server member in the group can execute transactions independently. However, all read/write (RW) transactions are committed only after the conflict detection is successful. Read-Only (RO) transactions can be committed immediately without conflict detection.

For any RW transaction, the commit operation is not determined by the originating server, but by the Group. Accurately speaking, when the transaction is ready to be committed on the originating server, the server broadcasts the write value (changed rows) and the corresponding write set (the unique identifier of the updated row ). Then a global order is created for the transaction. Eventually, this means that all server members receive the same group of transactions in the same order. Therefore, all server members apply the same changes in the same order to ensure consistency within the group.

Group replication allows you to create a fault-tolerant system with redundancy based on the status of the replication system in a group of servers. Therefore, as long as not all or most of the servers fail, even if some servers fail, the system is still available, but at most it is performance and scalability reduction, but it is still available. Server faults are isolated and independent. They are monitored by the group member service, which relies on the Distributed Fault Detection System and can send signals when any server voluntarily or unexpectedly stops the group.

A Distributed Recovery Program ensures that when a server joins a group, it automatically updates the group information to the latest version. Multiple master updates ensure that updates are not blocked even if a single server fails, So server failover is not required. Therefore, MySQL group replication ensures the continuous availability of database services.

It is worth noting that, despite the availability of the Database Service, when a server crashes, the client connected to it must be directed or fail over to a different server. This is not a problem to be solved by group replication. Connectors, Server Load balancer, routers, or other types of middleware are more suitable for handling this problem. MySQL group replication provides high availability, high elasticity, and reliable MySQL services. Restrictions on MySQL group replication:

1 does not support XA transactions 2 tables require primary keys 3 adopts GTID + binlog for replication 4 only supports IPV4 networks 5 network performance has a great impact on the cluster, requiring low latency, the high-bandwidth 6 multi-master does not support the same object, but the concurrent DDL + DML hybrid operation of different instances 7 does not support the serial operation and does not support the RR mode gap lock, it is best to use RC mode in combination with group replication for more than 8 master nodes do not support foreign key constraint 9 does not support transaction storage point 10 cluster performance depends on the worst hardware machine, therefore, it is recommended that all hardware should be configured in a unified manner. 11 mysqldump cannot back up GR instance 12. Currently, a maximum of nine nodes are allowed in the cluster.

Based on some blogs on the Internet, there are still a lot of things on the Internet. You can use Baidu on your own.

For more details about group replication, see the new features of MySQL 8.0.2 replication in xingyao team)

1. installation environment

 

Machine	IP
SERVER1	10.103.16.31
SERVER2	10.103.16.34
SERVER3	10.103.16.35

The MySQL version is 5.7.20. We first install and start MySQL on three hosts. For detailed installation instructions, refer to MySQL 5.7.20 compiling and installation. After the basic environment is set up, let's take a look at the configuration of MySQL Group Replication.

2: Configure and install MySQL Group Replication

We have already configured three MySQL services on the three machines. We need to add some GR parameters on the basis of our current configuration file. We take SERVER1 as an example:

Binlog_checksum = NONE # MGR itself does not support the checksum check of binlog transaction_write_set_extraction = XXHASH64 loose-group_replication_group_name = "00e575aa-0cc0-11e8-9186-0050417341db" # group name, Here select uuid (); generate loose-group_replication_start_on_boot = off # do not automatically start group replication loose-group_replication_local_address = "10.103.16.31: 24901" loose-group_replication_group_seeds = "10.103.16.34: 24901, 10.103.16.35: 24901, 10.103.16.31: 24901" loose-group_replication_bootstrap_group = off when mysqld is started

 

We have added some required parameters before. Let's take a look at the other parameters required by GR:

Gtid-mode = onenforce_gtid_consistency = ON # GTID mode is the basic technology of group replication master_info_repository = TABLE relay_log_info_repository = TABLE # managing replication metadata and recovery, and recording synchronization information, easy to manage and recover log-bin = row # It must be in the ROW format log_slave_updates = ON # binlog for transaction record. It is used for future recovery, even if it is not a write point.

 

Next we will create the account used by GR:

set sql_log_bin=0;GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'repl'@'%' IDENTIFIED BY '123456';flush privileges;set sql_log_bin=1;

 

Run the following command in the master database:

CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='123456' 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;

 

Then run the following command on SERVER2 and server3:

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

 

Then let's take a look at the node:

mysql> select * from performance_schema.replication_group_members;ERROR 2006 (HY000): MySQL server has gone awayNo connection. Trying to reconnect...Connection id:    145Current database: mxq+---------------------------+--------------------------------------+-------------+-------------+--------------+| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |+---------------------------+--------------------------------------+-------------+-------------+--------------+| group_replication_applier | 096769f1-de4e-11e7-bc85-0050569355e1 | sdw1        |        3306 | ONLINE       || group_replication_applier | 6899e4bf-de91-11e7-a3bb-005056930bed | sdw2        |        3306 | ONLINE       || group_replication_applier | c6b0f3b3-de40-11e7-9dbd-0050569341db | mdw         |        3306 | ONLINE       |+---------------------------+--------------------------------------+-------------+-------------+--------------+3 rows in set (0.00 sec)

 

We can check the node status by checking the MEMBER_STATE:

Online offline recoving unreachable cannot be reached during recovery, view error log error synchronization error, view error Log

 

Check the master node:

mysql> SELECT b.member_id, b.member_host, b.member_port  FROM performance_schema.global_status a   JOIN performance_schema.replication_group_members b     -> ON a.variable_value = b.member_id WHERE a.variable_name= 'group_replication_primary_member';+--------------------------------------+-------------+-------------+| member_id                            | member_host | member_port |+--------------------------------------+-------------+-------------+| c6b0f3b3-de40-11e7-9dbd-0050569341db | mdw         |        3306 |+--------------------------------------+-------------+-------------+1 row in set (0.00 sec)

 

We can see that the current master node is SERVER1, and all slave nodes in GR are read_only by default. We can also use the following system table to check the GR information:

mysql> select * from performance_schema.replication_group_member_stats\G*************************** 1. row ***************************                      CHANNEL_NAME: group_replication_applier                           VIEW_ID: 15181587863063562:15                         MEMBER_ID: c6b0f3b3-de40-11e7-9dbd-0050569341db       COUNT_TRANSACTIONS_IN_QUEUE: 0        COUNT_TRANSACTIONS_CHECKED: 0          COUNT_CONFLICTS_DETECTED: 0COUNT_TRANSACTIONS_ROWS_VALIDATING: 0TRANSACTIONS_COMMITTED_ALL_MEMBERS: 00e575aa-0cc0-11e8-9186-0050569341db:1-17    LAST_CONFLICT_FREE_TRANSACTION: 1 row in set (0.00 sec)

 

We can see that the current three machines are in single-master mode. How can we change the mode to multi-master mode? However, we strongly recommend that you do not use multi-master mode, in the multi-master mode, it is easy to hang the entire cluster, and many restrictions are imposed on the Multi-master mode. If we want to change the single major to multi-master mode, we only need to do the following:

SERVER2, SERVER3:

STOP GROUP_REPLICATION;SET GLOBAL group_replication_single_primary_mode=FALSE;SET GLOBAL group_replication_enforce_update_everywhere_checks=TRUE;

SERVER1:

STOP GROUP_REPLICATION;SET GLOBAL group_replication_single_primary_mode=FALSE;SET GLOBAL group_replication_enforce_update_everywhere_checks=TRUE;SET GLOBAL group_replication_bootstrap_group=on;START GROUP_REPLICATION;SET GLOBAL group_replication_bootstrap_group=off;

Then SERVER2, SERVER3:

SET GLOBAL group_replication_bootstrap_group=on;START GROUP_REPLICATION;SET GLOBAL group_replication_bootstrap_group=off;

Let's check the node information:

mysql> select * from performance_schema.replication_group_members;+---------------------------+--------------------------------------+-------------+-------------+--------------+| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |+---------------------------+--------------------------------------+-------------+-------------+--------------+| group_replication_applier | 096769f1-de4e-11e7-bc85-0050569355e1 | sdw1        |        3306 | ONLINE       || group_replication_applier | 6899e4bf-de91-11e7-a3bb-005056930bed | sdw2        |        3306 | ONLINE       || group_replication_applier | c6b0f3b3-de40-11e7-9dbd-0050569341db | mdw         |        3306 | ONLINE       |+---------------------------+--------------------------------------+-------------+-------------+--------------+3 rows in set (0.00 sec)

If all three nodes are OK, perform a test:

SERVER1:

mysql> use mxq;Database changedmysql> create table gr(id int ,name  varchar(10),primary key(id));Query OK, 0 rows affected (0.06 sec)mysql> insert into gr(1,'a');ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1,'a')' at line 1mysql> insert into gr values(1,'a');Query OK, 1 row affected (0.05 sec)

SERVER2:

mysql> insert into gr values(2,'a');Query OK, 1 row affected (0.01 sec)

SERVER3:

mysql> insert into gr values(3,'a');Query OK, 1 row affected (0.04 sec)

Then view the data:

mysql> select * from gr;+----+------+| id | name |+----+------+|  1 | a    ||  2 | a    ||  3 | a    |+----+------+3 rows in set (0.00 sec)

 

In this case, OK.