Group replication for MySQL

group replication for MySQL

Let's talk about the various forms of database replication

Asynchronous Replication Mode , the transaction generated binlog,slave on Master is received on the local relaylog by connecting the contents of master Fetch Binlog, and then apply the corresponding transaction, resulting in Binlog on the slave server itself (by- The-log-slave-update parameter is determined). The flowchart is as follows:

Next is the semi-synchronous replication , the flowchart is as follows

In asynchronous replication mode, if slave all goes down, there is a certain data security risk that transactions on master cannot be synchronized to slave.

Semi-synchronous replication solves the problem of data security risk, in the semi-synchronous environment requires at least one slave received the master Binlog and successfully written to the local relaylog, the transaction on Master can be successfully committed, so that the main library transaction commit speed will have a certain impact, Semi-synchronous has a neutralization between data security and database performance.

In the actual use of the process, you can configure the parameters (Rpl_semi_sync_master_timeout unit is milliseconds, the default is 10000, that is, 10s) set if the slave for how long no ACK return, synchronous mode is automatically modified by the semi-synchronous mode of asynchronous synchronization.

Group Replication single master mode and multi-master mode, MySQL replication technology only solves the problem of data synchronization, if the master outage means that the database administrator needs to intervene, the application system may need to modify the database connection address or restart to be implemented.

Features of group replication:

High consistency

Group replication technology based on native replication and Paxos protocol, and provided by plug-in, providing consistent data security assurance;

High level of fault tolerance

As long as not most of the nodes can continue to work, there is automatic detection mechanism, when the different nodes generate contention conflicts, will not appear error, according to first-come priority principle of processing, and built-in automatic brain crack protection mechanism;

High scalability

The addition and removal of the nodes are automatic, and when the new node joins, the state is automatically synchronized from the other nodes until the new node is consistent with the other nodes, and if a node is removed, the other nodes automatically update the group information and automatically maintain the new group information;

High flexibility

There is a single main mode and multi-master mode, the single main mode, will automatically select the master, all the update operations in the Lord;

In multi-master mode, all servers can handle update operations at the same time.

Currently group replication supports up to 9 servers in one group

when doing a group copy, pay attention to parse/etc/hosts, close the password plug-in, /etc/my.conf written in,

Validate_password=off

Or to comment on the vim/etc/init.d/mysqld.
[Object Object]

Experiment:

Server1 192.168.122.11

Server2 192.168.122.12

Server3 192.168.122.13

One, configuration file

/etc/my.cnf

The server2,3 configuration file differs only in Server-id and local_address

Copy Frame

The setting configures the server to use a unique identification number of 1 to enable the global transaction identifier and to store the replication metadata in the system tables instead of files. Additionally, it sets the server to open binary logging, uses the row-based format, and disables the binary log event checksum.

Group Replication settings

Line 1th indicates that the server must collect a write collection for each transaction and encode it as a hash using the XXHASH64 hash algorithm.

Line 2nd tells the plugin that the group being joined or created is to be named "Aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa".

Line 3rd indicates that the plug-in does not automatically start group replication when the server starts.

Line 4th tells the plug-in to use the local host or IP address 127.0.0.1, and port 24091 is used to accept incoming connections from other members of the group.

Line 5th tells the plugin that when the following servers need to join the group, they should be connected to these hosts and ports to access them, and these are the seed members.

Line 6th indicates whether the plug-in automatically guides the group. This option can only be used on one server instance at any time, usually when the first boot group (or when the entire group is crashed and then recovered). If you boot groups more than once, for example, when multiple server instances have this option set, they can artificially cause a split-brain situation where there are two different groups with the same name. Disable this option after the first server instance is joined to a group.

Second, the database configuration

1. User credentials

The distributed recovery process relies on the Group_replication_recovery replication channel, which is used to transfer transactions between group members. Therefore, you need to set up replication users with the correct permissions so that group replication can directly establish a member-to-member recovery replication channel.

mysql> SET sql_log_bin=0; Turn off binary logging

mysql> Grant Replication Slave on * * to [email protected] '% ' identified by ' lh=liuhuan123 '; Create a Rpl_user user and authorize the replication function

Mysql> flush Privileges;

mysql> SET sql_log_bin=1; Open Binary Log

mysql> change Master to master_user= ' Rpl_user ', master_password= ' lh=liuhuan123 ' for channel ' Group_replication_    Recovery '; The next time you need to restore its state from another member, use the Group_replication_recovery to copy the given credentials for the channel:

2. Start group replication

mysql> INSTALL PLUGIN group_replication SONAME ' group_replication.so '; Installing plugins

mysql> SET GLOBAL group_replication_bootstrap_group=on; Server1 Boot Group, start the Group replicator and use it only once in the group replication member. This parameter cannot be written in the configuration file because there will be two different groups with the same name when the next startup group is copied.

Mys Q  l> START group_replication; to start Server1 group replication

mysql> SET GLOBAL group_replication_bootstrap_group=off; The program that is copied by the boot group is closed and the remaining members are joined directly to the group.

Mysql> SELECT * from performance_schema.replication_group_members; view

Third, add an instance to the group

Server2 End

mysql> SET sql_log_bin=0;
mysql> Grant Replication Slave on * * to [email protected] '% ' identified by ' lh=liuhuan123 ';
mysql> SET sql_log_bin=1;
mysql> change MASTER to master_user= ' Rpl_user ', master_password= ' lh=liuhuan123 ' for CHANNEL ' Group_replication_ Recovery ';

mysql> INSTALL PLUGIN group_replication SONAME ' group_replication.so '; Installing plugins

mysql> start group_replication; Adding Server2 to a group

Check the log to:

Internet search (website bing.com)

Server3 Similarly, view mysql> SELECT * from Performance_schema.replication_group_members;

Iv. Highly Available validation

1. Verifying Data Write

Server1 end, writing data

server2,3 End View

2, simulation of a node verification of downtime

Close Server2

Writing data in Server1

Start Server2

View the group members again, and discover that Server2 has been re-added to the group

View data and find that the data is synchronized

Group replication for MySQL