The old driver takes you in the MySQL field "Good luck, eat chicken at night"

Lao Zhang Superzs to update the blog, everyone New Year good Ah, for a long time did not meet with you. Say a word walk heart words, miss you very much! Recently, because of the publication of his first book, "MySQL King to qualify the road" has been busy propaganda. I'm really sorry that I didn't make it in time. The future will be more dedicated to the wonderful article!

The recent Jedi survival game is so hot that when I ask my friends what you are doing in your leisure time, basically tell me the answer is "eat chicken" Ah! Presumably a lot of old iron also play, that must know Jedi survival has a gun called AWM. This gun damage bare 132 points, headshot 330, even if three head headshot will die, no bulletproof vest or bulletproof vest has broken, one shot dead. This game is the most powerful sniper xxx, a full 98K twice times.

So in the MySQL field there is no such a powerful "gun"-the answer is must have AH! But Lao Zhang asked you a question first.

When you build MySQL replication, you are still performing backup recovery, do you change from the library to the master to? If that's the case, you'll be really behind.

This chapter introduces MySQL's official recommendation for a highly available cluster solution MySQL Group Replication. Abbreviation: MGR (group copy). It is an official launch of a state machine replication based on the Paxos protocol, which completely solves the problem that data consistency is not guaranteed in traditional asynchronous replication and semi-synchronous replication. It also makes the MySQL database more widely involved and has the door to open the Internet financial industry completely. December 2016 MySQL Group replication launched its first GA release in MySQL5.7.17. But the current direct into the production environment to use, the risk is still relatively large. After the proposal is more and more mature, we really put into use.

With the MySQL8.0 version is about to meet with us, and then with the official gradually mature high-availability cluster Group replication solution, then to see who is our MySQL opponent, all are not! MySQL database is the author's faith, Love You no regrets.

MGR Group replication principle

Let's start by talking about what is called group replication. Group replication is a technique that can be used to implement fault-tolerant systems. A replication group is a server cluster that interacts with one another through message delivery. A replication group consists of multiple server members, such as Master1,master2,master3, in which all members complete their own transactions independently. When the client initiates an update transaction, the transaction is executed locally, and a commit operation is initiated on the transaction after the execution is completed. It is necessary to broadcast the resulting copy write set to other members before it is actually committed. If the conflict detection succeeds, the group decides that the transaction can be committed, other members can apply, or rollback. Ultimately, this means that all members in the group receive the same set of transactions in the same order. Therefore, members of the group apply the same changes in the same order, ensuring strong consistency of data within the group.

MGR Group copy mode

Group replication can run in two modes. In single-master mode, group replication has the auto-select feature, with only one server member accepting updates at a time, and other members providing only read services. When running in multi-master mode, all server members can accept updates at the same time, without the master and slave points, and the member roles are fully equivalent. Group replication is single-master mode by default, and we can make it into a multi-master mode by setting the parameter Group_replication_single_primary_mode=off. The actual combat part of this chapter is the construction of MGR replication with multi-master mode.

MGR Feature Introduction

1. Complete the real multi-node read-write cluster scheme.
2. Group replication technology based on native replication and Paxos protocol, which is provided by plug-in to achieve strong data consistency
3. Failover switching is easier due to multi-node read and write
4. Increase the deletion of nodes, automatically complete the synchronization of data and update the information within the group operations. Extensibility Enhancements
   Although in MGR's multi-master mode, constraints that cannot support the serial isolation level and foreign key cascade operations are not supported. And the current MySQL version of the DDL statement operations can not be implemented rollback, resulting in MGR cannot do data conflict validation of DDL statements, so the DDL statements in MGR Concurrent execution of the hidden dangers. But none of this will stop the MGR's release from creating a new era. Let's look forward to the quest for the perfect MySQL database for a more dazzling future.

Mgr Build a practical walkthrough

环境介绍：这里我们准备三个节点，数据库版本使用MySQL5.7.17

Database IP Address database version information host name Server-id

192.168.56.101  MySQL-5.7.17    node2   1013306192.168.56.102  MySQL-5.7.17    node3   1023306192.168.56.103  MySQL-5.7.17    proxysql    1033306

The 1th step in combat:
Perform an installation MySQL5.7 operation on three nodes. (This step is omitted, please see the second chapter in the first part of the book for details.)
The 2nd step in combat:
Set hostname and IP address mappings on three nodes, respectively

The 3rd step in combat:
Create a copy account on three nodes, the command is as follows

GRANT REPLICATION SLAVE ON *.* TO ‘repl‘@‘192.168.56.%‘ IDENTIFIED BY ‘123456‘;

The 4th step in combat:
To install the GR plugin on three nodes, the command is as follows

INSTALL PLUGIN group_replication SONAME ‘group_replication.so‘;

After the component installation is complete, the results are displayed:

[email protected] 13:23:  [(none)]> show plugins;

The 5th step in combat:
Configure the respective cluster parameter files on three nodes, and restart the database after configuration is complete
192.168.56.101 parameter file configuration display: (Two nodes left)

Group Replication

server_id = 1013306gtid_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 = ‘1f2cee29-f9a2-11e7-8cbb-08002783b39d‘loose-group_replication_start_on_boot = offloose-group_replication_local_address = ‘node2:33061‘loose-group_replication_group_seeds =‘node2:33061,node3:33062,proxysql:33063‘loose-group_replication_bootstrap_group = offloose-group_replication_single_primary_mode=off loose-group_replication_enforce_update_everywhere_checks=true

Key parameters: (Be sure to remember these)
Mgr Be sure to turn on the Gtid functiongtid_mode = ON，enforce_gtid_consistency = ON
Mgr in the current MySQL version, you must set thebinlog_checksum=noneAndbinlog_format=row
MGR requires multi-source replication, so information between the master and slave libraries needs to be recorded in the table. Set upmaster_info_repository = TABLEAndrelay_log_info_repository = TABLE
Primary key information acquisition function in MGR:transaction_write_set_extraction= XXHASH64
Mgr The parameter in thegroup_replication_single_primary_modeThe purpose of closing is to build a multi-master mode. If you build a single master model, leave the default parameters.
You need to set the name of the group in Mgr, whichgroup_replication_group_nameis throughselect uuid()To generate the.
The address of the local member in the MGR is determined by this parametergroup_replication_local_addressDecision, the current node in this example is Node2. The subsequent ports are each node that requires a separate TCP port number to communicate between the nodes through this port number.
The address of the other members in the MGR is determined by this parametergroup_replication_group_seedsDecided
Mgr When you take a multi-master mode, you need to make this parametergroup_replication_single_primary_mode=off(The default is single-master mode).
At the same time, you need to add this parametergroup_replication_enforce_update_everywhere_checks=true。 The goal is to do more main mode limit detection.
Note: Loaded into the parameter file, you need to add loose to the front of each parameter.
Here we emphasize one more parametergroup_replication_auto_increment_increment。 This parameter represents the self-increment property, and the default value is 7. We want to make sure that the value of each member is the same, and it is recommended that the setting of this parameter is as much as possible than the number of members in the group, facilitating later cluster expansion.

The 6th step in combat:
Start the MGR Cluster service for the first node on 192.168.56.101. The command is as follows:

CHANGE MASTER TO MASTER_USER=‘repl‘, MASTER_PASSWORD=‘123456‘ FOR CHANNEL ‘group_replication_recovery‘

(Mgr requires this asynchronous replication channel to enable new nodes to join the cluster to automatically replicate data from other nodes, without the need to manually specify from which node to copy, Master_host, and master_port information.) are automatically completed)
SET GLOBAL group_replication_bootstrap_group = on;
(performs the initialization operation, only the first node needs this step)
START group_replication;
After successful startup, view the node status information. The command is as follows

SELECT * FROM performance_schema.replication_group_members;

The Shutdown initialization Operation command is as follows:

SET GLOBAL group_replication_bootstrap_group = off;

Note: The Member_state field in the Replication_group_members table has the status of online, and then the shutdown initialization command is executed.

The 7th step in combat:
Start the MGR Cluster service for the second node on the 192.168.56.102. The command is as follows

CHANGE MASTER TO MASTER_USER=‘repl‘, MASTER_PASSWORD=‘123456‘ FOR CHANNEL ‘group_replication_recovery‘;set global group_replication_allow_local_disjoint_gtids_join=ON;  

After successful startup, view the node status information. The command is as follows

 SELECT * FROM performance_schema.replication_group_members;

The 8th step in combat:
Start the third node's MGR Cluster service on 192.168.56.103. The command is as follows

CHANGE MASTER TO MASTER_USER=‘repl‘, MASTER_PASSWORD=‘123456‘ FOR CHANNEL ‘group_replication_recovery‘;set global group_replication_allow_local_disjoint_gtids_join=ON;  

After successful startup, view the node status information. The command is as follows:

 SELECT * FROM performance_schema.replication_group_members;

After successful startup, the status of the three members is online, proving that it has started working properly. Can actually realize the multi-node read and write operation.

This is the principle of MGR Multi-master mode and its construction process. We should have a new knowledge of the ability to learn, more real, more drills, from which to get its true meaning, feel mysql to bring us the simple happiness! In the future will be more published more technical articles, we communicate more!

The old driver takes you in the MySQL field "Good luck, eat chicken at night"