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

What this article shares with you is about the high availability cluster solution MGR in MySQL. The editor thinks it is very practical, so I share it with you to learn. I hope you can get something after reading this article.

Recently, PUBG's game is so popular that when I ask my friends what you are doing in your spare time, the answer you basically tell me is to "eat chicken". Presumably many brothers also play, so you must know that there is a gun called AWM in PUBG. This gun damage 132 naked, head shot 330, even if the third-level head shot will die, no bulletproof vest or bulletproof vest is damaged, one shot to death. It is the most powerful sniper in this game, twice as much as 98K.

So is there such a powerful "gun" in the field of MySQL? the answer is there must be! But let me ask you a question first.

When you set up MySQL replication, are you still performing backup recovery and performing change master to operations from the library? If it is, then you really fall behind.

Today, we introduce MySQL Group Replication, a highly available cluster scheme officially recommended by MySQL. Abbreviation: MGR (Group replication). It is an official state machine replication based on Paxos protocol, which completely solves the problem of data consistency in traditional asynchronous replication and semi-synchronous replication. It also makes the MySQL database cover a wider range of fields and thoroughly open the door to the Internet financial industry. In December 2016, MySQL Group Replication launched its first version of GA to be released in MySQL5.7.17. However, at present, the risk is still relatively high when it is directly used in the production environment. It is suggested that when it becomes more and more mature, we will really put it into use.

With the MySQL8.0 version coming to us, coupled with the official maturing high-availability cluster Group Replication solution, we'll see who is still a competitor to our MySQL. It's all gone! MySQL database is the author's belief, love you with no regrets.

Principle of MGR group replication

Let's talk about what group replication is. Group replication is a technology that can be used to implement fault-tolerant systems. A replication group is a server cluster that interacts with each other through messaging. The replication group consists of multiple server members, such as the master1,master2,master3 in the figure below, all of which complete their own transactions independently. When the client initiates an update transaction, the transaction is executed locally, and the commit operation of the transaction is initiated after the execution is completed. The resulting replication write set needs to be broadcast and copied to other members before it is actually committed. If conflict detection is successful, the group decides that the transaction can be committed and other members can apply, otherwise it will be rolled back. Ultimately, this means that all members of the group receive the same set of transactions in the same order. Therefore, the members of the group apply the same changes in the same order to ensure strong consistency of data within the group.

MGR group replication mode

Group replication can be run in two modes. In single master mode, group replication has the function of automatically selecting master. Only one server member accepts the update at a time, and the other members only provide read service. When running in multi-master mode, all server members can accept updates at the same time, there is no distinction between master and slave, and the roles of members are completely equal. Group replication is single-master mode by default, and we can change it to 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 in multi-master mode.

Introduction to MGR characteristics

The real multi-node read-write cluster scheme is completed.

The group replication technology based on native replication and paxos protocol is provided as a plug-in to achieve strong data consistency.

Failover switching becomes easier because it is multi-node read and write

Add and delete nodes, automatically complete the operation of synchronizing data and updating information in the group. Scalable enhancement

Although in the multi-master mode of MGR, it is limited by the lack of support for serial isolation levels and foreign key cascading operations. And the current MySQL version of the DDL statement operation can not achieve rollback, resulting in MGR can not check the data conflict of DDL statements, so there is a hidden danger in the concurrent execution of DDL statements in MGR. But none of these can stop the significance of ushering in a new era brought about by the release of MGR. Let us look forward to the pursuit of the ultimate perfection of the MySQL database, the future more dazzling.

Introduction to MGR to build an actual combat environment: here we prepare three nodes, and the database version uses MySQL5.7.17

Database IP address database version information hostname 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

Actual combat step 1:

Install MySQL5.7 on all three nodes. (this step is omitted. For details, please see Chapter 2 of part one of the book.)

Step 2 of actual combat:

Set up the mapping of hostname and ip addresses on three nodes

Step 3 in actual combat:

Create a replication account on each of the three nodes with the following command

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

Step 4 of actual combat:

Install the GR plug-in on each of the three nodes with the following command

INSTALL PLUGIN group_replication SONAME 'group_replication.so'

After the component installation is complete, display the results:

Root@db 13:23: [(none)] > show plugins

Step 5 of actual combat:

Configure their respective cluster parameter files for each of the three nodes, and restart the database after the configuration is completed.

192.168.56.101 parameter file configuration display: (the remaining two nodes are the same)

Group Replicationserver_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

Detailed explanation of key parameters: (be sure to remember these)

MGR must enable the GTID function gtid_mode = ON,enforce_gtid_consistency = ON

MGR in the current MySQL version, binlog_checksum=none must be set, and binlog_format=row

MGR requires multi-source replication, so the information between master and slave libraries needs to be recorded in a table. Set master_info_repository = TABLE and relay_log_info_repository = TABLE

Enable the key information collection function in MGR: transaction_write_set_extraction= XXHASH64

This parameter group_replication_single_primary_mode is turned off in MGR in order to build a multi-master mode. If you build a single master model, keep the default parameters.

The name of the group needs to be set in MGR, and the group_replication_group_name is generated through select uuid ().

The address of the local member in the MGR is determined by the parameter group_replication_local_address, and in this case the current node is node2. The following port is that each node needs a separate TCP port number through which nodes communicate.

The addresses of other members of the MGR are determined by this parameter group_replication_group_seeds

When MGR takes multi-master mode, you need to make this parameter group_replication_single_primary_mode=off (the default is single-master mode).

At the same time, you need to group_replication_enforce_update_everywhere_checks=true this parameter. The purpose is to do multi-master mode limit detection.

Note: if you load it into the parameter file, you need to add loose before each parameter.

Another parameter, group_replication_auto_increment_increment, is emphasized here. This parameter represents a self-increasing attribute, and the default value is 7. We need to ensure that the value of each member is the same, and it is recommended that this parameter be set as much as possible than the number of members in the group to facilitate the expansion of the cluster at a later stage.

Step 6 of actual 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 needs this asynchronous replication channel to achieve the purpose of automatically replicating data from other nodes when new nodes join the cluster. There is no need to manually specify which node to copy, master_host and master_port information. It's all done automatically)

SET GLOBAL group_replication_bootstrap_group = ON

(perform initialization operation, which is required only by the first node)

START GROUP_REPLICATION

After the startup is successful, 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 status of the MEMBER_STATE field in the replication_group_ members table is ONLINE, and then execute the close initialization command.

Step 7 of actual combat:

Start the mgr cluster service for the second node above 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; START GROUP_REPLICATION

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

SELECT * FROM performance_schema.replication_group_members

Step 8 of actual combat:

Start the mgr cluster service for the third node above 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; START GROUP_REPLICATION

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

SELECT * FROM performance_schema.replication_group_members

It can be seen that after the startup is successful, the status of the three members is ONLINE, which proves that it has started to work properly. Can really achieve multi-node read and write operations.

This is what the high-availability cluster solution MGR in MySQL looks like. The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

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.