Steps to build a MySQL MGR cluster 03/13 Update SLTechnology News&Howtos

Steps to build a MySQL MGR cluster

2025-03-13 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

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

This article introduces the relevant knowledge of "steps to build a MySQL MGR cluster". Many people will encounter such a dilemma in the operation of actual cases, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

1. Background

MySQL Group Replication (MGR) is a database solution with high availability and high expansion introduced by MySQL officially in version 5.7.17. It is provided as a plug-in to achieve the ultimate consistency of distributed data. The characteristics of MGR are summarized as follows:

* High consistency: group replication based on distributed paxos protocol to ensure data consistency

* High fault tolerance: automatic detection mechanism, which can continue to work as long as most nodes are not down, with built-in anti-brain fissure protection mechanism.

* High scalability: the addition and removal of nodes will automatically update the group member information. After the new node is added, the incremental data is automatically synchronized from other nodes until it is consistent with the data of other nodes.

* High flexibility: single-master mode and multi-master mode are provided. The single-master mode can automatically select the master when the master database is down. All writes are carried out on the master node, and the multi-master mode supports multi-node writes.

two。 Environment:

Three servers set up a 3-node MGR cluster, MySQL version 5.7.27, operating system version CentOS 7.3.

* 192.168.8.71

* 192.168.8.72

* 192.168.8.73

3. Installation steps

3.1 install MySQL5.7

MySQL official version 5.7.27, installed with Linux Generic, downloads the package directly and decompresses it to the directory / usr/local/mysql.

3.2 modify the configuration file

Edit configuration file / etc/my.cnf,3 nodes are consistent except for server_id, loose-group_replication_local_address and report_host.

[mysqld]

Port=3306

Basedir=/usr/local/mysql

Datadir=/usr/local/mysql/data

Server_id=1

Gtid_mode=ON

Enforce_gtid_consistency=ON

Binlog_checksum=NONE

Log_bin=binlog

Log_slave_updates=ON

Binlog_format=ROW

Master_info_repository=TABLE

Relay_log_info_repository=TABLE

Transaction_write_set_extraction=XXHASH64

Loose-group_replication_group_name= "aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"

Loose-group_replication_start_on_boot=OFF

Loose-group_replication_local_address= "192.168.8.71 purl 33061"

Loose-group_replication_group_seeds= "192.168.8.71" 33061192.168.72 "33061192.168.73"

Loose-group_replication_bootstrap_group=OFF

Report_host=192.168.8.71

Report_port=3306

3.3 initialize the database (executed by all nodes)

Slightly

3.4 start the database, install the MGR plug-in, and set the replication account (executed by all nodes)

-install the MGR plug-in

Mysql > INSTALL PLUGIN group_replication SONAME 'group_replication.so'

-- set up replication account

Mysql > SET SQL_LOG_BIN=0

Mysql > CREATE USER repl@'%' IDENTIFIED BY 'repl'

Mysql > GRANT REPLICATION SLAVE ON *. * TO repl@'%'

Mysql > FLUSH PRIVILEGES

Mysql > SET SQL_LOG_BIN=1

Mysql > CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='repl' FOR CHANNEL 'group_replication_recovery'

3.5 start MGR single main mode

# start MGR and execute on the main library (192.168.8.71)

Mysql > SET GLOBAL group_replication_bootstrap_group=ON

Mysql > START GROUP_REPLICATION

Mysql > SET GLOBAL group_replication_bootstrap_group=OFF

# View MGR group information

Mysql > SELECT * FROM performance_schema.replication_group_members

+-+

+-+

+-+

1 row in set (0.00 sec)

# other nodes join the MGR and execute on the slave library (192.168.72192.168.73)

Mysql > START GROUP_REPLICATION

# View MGR group information

Mysql > SELECT channel_name

-> member_id

-> member_host

-> member_port

-> member_state

-> IF (global_status.variable_name IS NOT NULL, 'PRIMARY',' SECONDARY') AS member_role

-> FROM performance_schema.replication_group_members

-> LEFT JOIN performance_schema.global_status

-> ON global_status.variable_name = 'group_replication_primary_member'

-> AND global_status.variable_value = replication_group_members.member_id

+-+

+-+

+-+

3 rows in set (0.00 sec)

As you can see, the state of the three nodes is online, and the primary node is 192.168.8.71. Only the primary node can be written, and the other nodes are read-only. The MGR single master mode has been successfully built.

3.6 switch to multi-master mode

MGR switching mode requires restarting group replication, so you need to turn off group replication on all nodes, set parameters such as group_replication_single_primary_mode=OFF, and then start group replication.

# stop group replication (executed by all nodes):

Mysql > stop group_replication

Mysql > set global group_replication_single_primary_mode=OFF

Mysql > set global group_replication_enforce_update_everywhere_checks=ON

# randomly select a node to execute

Mysql > SET GLOBAL group_replication_bootstrap_group=ON

Mysql > START GROUP_REPLICATION

Mysql > SET GLOBAL group_replication_bootstrap_group=OFF

# execution by other nodes

Mysql > START GROUP_REPLICATION

# View group information. The MEMBER_ROLE of all nodes is PRIMARY.

Mysql > SELECT channel_name

-> member_id

-> member_host

-> member_port

-> member_state

-> IF (global_status.variable_name IS NOT NULL, 'PRIMARY',' SECONDARY') AS member_role

-> FROM performance_schema.replication_group_members

-> LEFT JOIN performance_schema.global_status

-> ON global_status.variable_name = 'group_replication_primary_member'

-> AND global_status.variable_value = replication_group_members.member_id

+-+

+-+

+-+

3 rows in set (0.00 sec)

You can see that the status of all nodes is online, and the roles are all PRIMARY,MGR multi-master modes built successfully.

3.7 switch back to single main mode

# all nodes execute

Mysql > stop group_replication

Mysql > set global group_replication_enforce_update_everywhere_checks=OFF

Mysql > set global group_replication_single_primary_mode=ON

# Master node (192.168.8.71) execution

SET GLOBAL group_replication_bootstrap_group=ON

START GROUP_REPLICATION

SET GLOBAL group_replication_bootstrap_group=OFF

# execute from node (192.168.8.72, 192.168.8.73)

START GROUP_REPLICATION

# View MGR group information

Mysql > SELECT channel_name

-> member_id

-> member_host

-> member_port

-> member_state

-> IF (global_status.variable_name IS NOT NULL, 'PRIMARY',' SECONDARY') AS member_role

-> FROM performance_schema.replication_group_members

-> LEFT JOIN performance_schema.global_status

-> ON global_status.variable_name = 'group_replication_primary_member'

-> AND global_status.variable_value = replication_group_members.member_id

+-+

+-+

+-+

This is the end of the content of "steps for Building a MySQL MGR Cluster". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.