• The original content of GreatSQL community cannot be used without authorization. Please contact Xiaobian and indicate the source for reprint.

[toc]

This paper introduces how to use GreatSQL 8.0.25 to build a three node MGR cluster.

1. Installation Preparation

Prepare the following three servers:

Ensure that the network between the three nodes can be interconnected, and there are no firewall interception rules for ports 3306 and 33061.

Download the GreatSQL binary package at: https://gitee.com/GreatSQL/Gr... .

This article is based on CentOS x86_64 environment as an example, the downloaded binary package name is: greatsql-8.0.25-15-linux-glibc2 28-x86_ 64.tar. XZ, put it in / usr/local directory and extract it:

$ cd /usr/local
$ tar xf GreatSQL-8.0.25-15-Linux-glibc2.28-x86_64.tar.xz
$ cd GreatSQL-8.0.25-15-Linux-glibc2.28-x86_64
$ ls
bin    COPYING-jemalloc  include  LICENSE         LICENSE-test  mysqlrouter-log-rotate  README.router  run    support-files
cmake  docs              lib      LICENSE.router  man           README                  README-test    share  var

2. Initialize MySQL Server

First prepare / etc / my CNF profile:

#/etc/my.cnf
[mysqld]
user = mysql
basedir=/usr/local/GreatSQL-8.0.25-15-Linux-glibc2.28-x86_64
datadir=/data/GreatSQL
port=3306
server_id=103306
log-bin
log_slave_updates=1
gtid_mode=ON
enforce_gtid_consistency=ON

This article only aims to start MySQL Server and deploy MGR normally, so this configuration file is very simple. If you want to use it in a formal occasion, you can refer to it This configuration file.

Initialize MySQL Server first:

$ mkdir -p /data/GreatSQL && chown -R mysql:mysql /data/GreatSQL
$ /usr/local/GreatSQL-8.0.25-15-Linux-glibc2.28-x86_64/bin/mysqld --defaults-file=/etc/my.cnf --initialize-insecure

Note: do not use the -- initialize execute option to initialize the installation in the production environment, because if you do so, the super administrator root account defaults to an empty password, and anyone can log in to the database with this account. There is a security risk. This is only done for the convenience of demonstration in this article.

Start MySQL Server:

$ /usr/local/GreatSQL-8.0.25-15-Linux-glibc2.28-x86_64/bin/mysqld --defaults-file=/etc/my.cnf &

If there is no accident, MySQL Server can be started normally. Use the same method to initialize the other two nodes.

In addition, it is recommended to add GreatSQL to the systemd service of the system to facilitate management. For specific methods, please refer to this article: Add GreatSQL to the systemd service of the system.

3. Initialize the first MGR node

Next, prepare to initialize the first node of MGR, also known as the boot node.

Modify / etc / my CNF, add the following lines and MGR related configuration parameters:

plugin_load_add='group_replication.so'
group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaa1"
group_replication_local_address= "172.16.16.10:33061"
group_replication_group_seeds= "172.16.16.10:33061,172.16.16.11:33061,172.16.16.12:33061"
report-host=172.16.16.10

The report host option is used to report the address used by this node to other MGR nodes, so as to avoid the problem that MGR cannot be started because the corresponding relationship may not be found correctly when there are multiple host names on a server. In addition, after setting report host, it is not necessary to modify the / etc/hosts system file and add the address and host name of each node.

In addition, note that the port configured above writes 33061 instead of 3306, which specifies a special communication port for MGR service, which is different from the normal read-write service port of MySQL. The 33061 port number here can be customized, such as 12345. Note that this port cannot be blocked by the firewall.

Using this configuration file, restart MySQL Server, and you should see that the group has been successfully loaded_ The replicaton plug-in:

mysql> show plugins;
...
+---------------------------------+----------+--------------------+----------------------+---------+
| Name                            | Status   | Type               | Library              | License |
+---------------------------------+----------+--------------------+----------------------+---------+
...
| group_replication               | ACTIVE   | GROUP REPLICATION  | group_replication.so | GPL     |
...

If it is not loaded correctly, you can also log in to MySQL Server and load the plugin manually:

myqsl> install plugin group_replication soname 'group_replication.so';

Next, create a special account for MGR service and prepare to configure MGR service channel:

#Each node needs to create a user separately, so there is no need to record binlog and copy it to other nodes
mysql> set session sql_log_bin=0;
mysql> create user repl@'%' identified by 'repl';
mysql> GRANT BACKUP_ADMIN, REPLICATION SLAVE ON *.* TO `repl`@`%`;
#After creating the user, continue to enable binlog logging
mysql> set session sql_log_bin=1;

#Configure MGR service channel
#Channel name group_replication_recovery is fixed and cannot be modified
mysql> CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='repl' FOR CHANNEL 'group_replication_recovery';

Then execute the following command and set it as the boot node of MGR (only the first node needs to do this) to directly start the MGR service:

mysql> set global group_replication_bootstrap_group=ON;

mysql> start group_replication;

Reminder: when the whole MGR cluster restarts, the first node to start must also be set to boot mode, and then start other nodes. Do not set boot mode except.

Then, check the MGR service status:

mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST  | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 4ebd3504-11d9-11ec-8f92-70b5e873a570 | 172.16.16.10 |        3306 | ONLINE       | PRIMARY     | 8.0.25         |
+---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+----------------+

OK, the initialization of the first node is completed.

4. Continue to set the other two nodes

Continue to use the following / etc / my CNF profile template:

#my.cnf
[mysqld]
user = mysql
basedir=/usr/local/GreatSQL-8.0.25-15-Linux-glibc2.28-x86_64
datadir=/data/GreatSQL
port=3306
server_id=113306
log-bin
log_slave_updates=1
gtid_mode=ON
enforce_gtid_consistency=ON

#mgr
plugin_load_add='group_replication.so'
group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaa1"
group_replication_local_address= "172.16.16.11:33061"
group_replication_group_seeds= "172.16.16.10:33061,172.16.16.11:33061,172.16.16.12:33061"
report-host=172.16.16.11

Reminder: among the above options, server_id,group_replication_local_address and report_ The three options of host should be modified to the correct value. In an MGR cluster, each node sets its own server_id and server_uuid is unique, but group_ replication_ group_ The value of name should be the same, which is the unique ID of the MGR cluster.

After restarting the MySQL Server instance (report host is a read-only option and needs to be restarted to take effect), create a special account for MGR service and configure MGR service channel:

mysql> set session sql_log_bin=0;
mysql> create user repl@'%' identified by 'repl';
mysql> GRANT BACKUP_ADMIN, REPLICATION SLAVE ON *.* TO `repl`@`%`;
mysql> set session sql_log_bin=1;

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

Next, you can directly start the MGR service (except the first node, other nodes do not need to set the boot mode):

mysql> start group_replication;

Check MGR node status again:

mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST  | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 4ebd3504-11d9-11ec-8f92-70b5e873a570 | 172.16.16.10 |        3306 | ONLINE       | PRIMARY     | 8.0.25         |
| group_replication_applier | 549b92bf-11d9-11ec-88e1-70b5e873a570 | 172.16.16.11 |        3306 | ONLINE       | SECONDARY   | 8.0.25         |
| group_replication_applier | 5596116c-11d9-11ec-8624-70b5e873a570 | 172.16.16.12 |        3306 | ONLINE       | SECONDARY   | 8.0.25         |
+---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+----------------+

We can see that there are three nodes in the above cluster in the ONLINE state, of which 172.16.16.10 is the PRIMARY node and the other two are the SECONDARY nodes. That is to say, the current cluster adopts the single master mode. If the multi master mode is adopted, the roles of all nodes are PRIMARY.

5. Write data to MGR cluster

Next, we connect to the PRIMARY node, create a test library table and write data:

$mysql -h172.16.16.10 -uroot -Spath/mysql.sock
mysql> create database mgr;
mysql> use mgr;
mysql> create table t1(c1 int unsigned not null primary key);
mysql> insert into t1 select rand()*10240;
mysql> select * from t1;
+------+
| c1   |
+------+
| 8078 |
+------+

Then connect to one of the SECONDARY nodes and check whether the data just written in PRIMARY can be seen:

$mysql -h172.16.16.11 -uroot -Spath/mysql.sock
mysql> use mgr;
mysql> select * from t1;
+------+
| c1   |
+------+
| 8078 |
+------+

Confirm that the data can be read.

Here, you have completed the installation and deployment of the three node MGR cluster.

References, documents

• MySQL 8.0 Reference Manual
• Database Kernel Development - Wen Zhenghu
• Group Replication principle - song libing

Disclaimers

Due to the limited personal level, mistakes and omissions are inevitable in the column. Do not directly copy the commands and methods in the document and apply them directly to the online production environment. Readers must fully understand and verify the test environment before formal implementation, so as to avoid damage or damage to the production environment.

Enjoy GreatSQL :)

Article recommendation:

Quarterly report of GreatSQL (December 26, 2021)
https://mp.weixin.qq.com/s/FZ...

Analysis on the usage of sysbench pressure measuring tool
https://mp.weixin.qq.com/s/m1...

Fault analysis | linux disk io utilization is high, and the correct posture of analysis
https://mp.weixin.qq.com/s/7c...

Technology sharing | implementation and improvement of flashback in MySQL
https://mp.weixin.qq.com/s/6j...

How to filter 200000 data #
https://mp.weixin.qq.com/s/pt...

About GreatSQL

GreatSQL is a MySQL branch maintained by Wanli database. It focuses on improving the reliability and performance of MGR and supports the parallel query feature of InnoDB. It is a branch version of MySQL suitable for financial applications.

Gitee:
https://gitee.com/GreatSQL/Gr...

GitHub:
https://github.com/GreatSQL/G...

Bilibili:
https://space.bilibili.com/13...

Wechat & QQ group:
You can search and add GreatSQL community assistant wechat friends, send verification information "add group" to join GreatSQL/MGR communication wechat group

QQ group: 533341697
Wechat assistant: wanlidbc

This article is composed of blog one article multi posting platform OpenWrite release!