Mgr architecture scheme across data centers in MySQL financial application scenario (1)

Time:2022-5-26
  • The original content of greatsql community cannot be used without authorization. Please contact Xiaobian and indicate the source for reprint.

0. Content outline

    1. Operating environment
    1. Deploy Mgr A & B
    1. Deploy the replication channel between Mgr A and B
    1. Several precautions

How to deploy multiple sets of MySQL Mgr clusters in multiple data centers for fast switching.

In the financial application scenario, it is often required to deploy a highly available database architecture in multiple centers in the same city in order to achieve the goal of fast switching in case of failure.

In the same data center, Mgr clusters can be deployed to achieve fast and flexible switching.

Even in the same city and across data centers, if the network conditions are good, the delay may be within 1ms. Under such network conditions, it is also possible to try to deploy Mgr clusters in multiple centers in the same city (if the traffic concurrency is not particularly high). However, considering that there is a high probability that optical cables will be cut off between multiple data centers, it is not recommended to do so.

Therefore, it is better to deploy a set of independent Mgr cluster in the same data center, and then copy the data to the Mgr cluster in another data center through master-slave replication (asynchronous replication or semi synchronous replication), so that once the main computer room is abnormal, it can quickly switch to the standby computer room, and do not worry about the high availability guarantee level of the database.

Mgr architecture scheme across data centers in MySQL financial application scenario (1)

The architecture diagram of this scheme is shown above.

Next, work together to complete the implementation of this architecture scheme.

1. Operating environment

This time, three nodes are used to deploy this architecture. The purpose of each node is described below

Mgr architecture scheme across data centers in MySQL financial application scenario (1)

Two instances are running on each node, namely port 3306 and port 4306. The instances of port 3306 constitute Mgr a cluster and the instances of port 4306 constitute Mgr B cluster.

In addition to the official community version of MySQL, if you want to experience a more reliable, stable and efficient Mgr, you are recommended to use the great SQL version. This article adopts greatsql version 8.0.22. For the description of this version, see greatsql to create a better Mgr ecology.

  1. Deploy Mgr A & B
    Mgr can be deployed in the normal way. The following is a key configuration reference:
group_replication_single_primary_mode=ON
log_error_verbosity=3
group_replication_bootstrap_group=OFF 
group_ replication_ transaction_ size_ Limit = < the default value is 150MB, but it is recommended to lower it to less than 20MB. Do not use large transactions >
group_replication_communication_max_message_size=10M
group_ replication_ flow_ control_ Mode = "disabled" # the flow control mechanism in the official version is not reasonable. In fact, it can be considered to be closed
group_replication_exit_state_action=READ_ONLY
group_ replication_ member_ expel_ Timeout = 5 # if the network environment is bad, it can be raised appropriately

slave_parallel_type=LOGICAL_CLOCK
slave_ parallel_ Workers = 128 # can be set to 2-4 times the number of logical CPUs
binlog_transaction_dependency_tracking=writeset
transaction_write_set_extraction=XXHASH64
slave_checkpoint_period=2

For more information on Mgr and replication configurations, refer to this guide: Mgr best practices.

Start Mgr A and confirm that it works normally:

[[email protected] mgrA-1][(none)]> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 5499a6cb-91cb-11eb-966f-525400e802e2 |    mgrA-1   |        3306 | ONLINE       | PRIMARY     | 8.0.22         |
| group_replication_applier | ec2fcbeb-976c-11eb-a652-525400e2078a |    mgrA-2   |        3306 | ONLINE       | SECONDARY   | 8.0.22         |
| group_replication_applier | edfbdeda-91c8-11eb-a3c6-525400fb993a |    mgrA-3   |        3306 | ONLINE       | SECONDARY   | 8.0.22         |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+

[[email protected] mgrA-1][(none)]> select @@global.group_replication_group_name;
+---------------------------------------+
| @@global.group_replication_group_name |
+---------------------------------------+
| f195537d-19ac-11eb-b29f-5254002eb6d6  |
+---------------------------------------+

Deploy Mgr B in the same way and confirm that it works normally:

[[email protected] mgrB-1][(none)]> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 31f7accc-96ac-11eb-92f8-525400e802e2 |    mgrB-1   |        4306 | ONLINE       | PRIMARY     | 8.0.22         |
| group_replication_applier | b084f8a1-96a8-11eb-9a70-525400fb993a |    mgrB-2   |        4306 | ONLINE       | SECONDARY   | 8.0.22         |
| group_replication_applier | ed57ca6b-96a9-11eb-be28-525400e2078a |    mgrB-3   |        4306 | ONLINE       | SECONDARY   | 8.0.22         |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+

[[email protected] mgrB-1][(none)]> select @@global.group_replication_group_name;
+---------------------------------------+
| @@global.group_replication_group_name |
+---------------------------------------+
| 476c0276-be03-11eb-bd34-525400e802e2  |
+---------------------------------------+

Confirm the above two Mgr clusters and the servers of each node_ UUIDs are different.

3. Deploy the replication channel between Mgr A and B

Starting from MySQL 5.7, it supports multi-source replication, so we can easily use multi-source replication to build a replication channel between two Mgr clusters.

This replication channel can be either asynchronous replication or semi synchronous replication. It can be determined according to the network conditions between the two Mgr clusters and the actual business needs.

In this case, the semi synchronous replication scheme is selected, which is only for experimental purposes. It does not mean that we recommend that you also adopt the semi synchronous scheme.

Accordingly, the following is also a semi synchronous reference configuration, which can be adjusted according to the actual situation:

rpl_semi_sync_master_timeout=2592000000
rpl_semi_sync_master_wait_for_slave_count=1
rpl_semi_sync_master_wait_point=AFTER_SYNC

Create a semi synchronous replication channel on the pirary node of Mgr B (remember to set the channel name):

[[email protected] mgrB-1][(none)]> CHANGE MASTER TO
MASTER_HOST='172.16.16.10', MASTER_PORT=3306, 
MASTER_USER='repl', MASTER_PASSWORD='repl',
MASTER_AUTO_POSITION=1
FOR CHANNEL 'mgrA-to-mgrB-semisync' ;

Confirm that the semi synchronous replication takes effect:

[[email protected] mgrB-1][(none)]> SHOW REPLICA STATUS\G
             Replica_IO_State: Queueing master event to the relay log
                  Source_Host: 172.16.16.10
                  Source_User: repl
                  Source_Port: 3306
...
           Replica_IO_Running: Yes
          Replica_SQL_Running: Yes
...
                  Source_UUID: 5499a6cb-91cb-11eb-966f-525400e802e2
...
    Replica_SQL_Running_State: waiting for handler commit
...
           Retrieved_Gtid_Set: f195537d-19ac-11eb-b29f-5254002eb6d6:17-36885051:36885053:36885057:36885059:36885064:36885067
            Executed_Gtid_Set: 476c0276-be03-11eb-bd34-525400e802e2:1-5,
f195537d-19ac-11eb-b29f-5254002eb6d6:1-36884719:36884727:36884729-36884732:36884734-36884737
                Auto_Position: 1
         Replicate_Rewrite_DB:
                 Channel_Name: mgrA-to-mgrB-semisync
...

P. S, the above information is intercepted after running for a period of time, so the value of gtid looks large.

You can also use similar methods to build traditional asynchronous replication channels and two-way replication channels.

4. Several precautions

  • Servers of nodes in two Mgr clusters_ UUID ensures no duplication.
  • The names of the two Mgr clusters (group_replication_group_name) ensure no duplication.
  • After the replication channel is built, the primary node in Mgr B should also be set to read-only (super_read_only = 1) to avoid misoperation and writing data.
  • The data consistency between the two Mgr clusters should also be checked regularly. Whether asynchronous replication or (enhanced) semi synchronous replication, or even Mgr, there are multiple bugs that lose data. We can’t be blindly optimistic. We believe that using enhanced synchronization / Mgr can ensure data consistency.

This paper first introduces the architecture scheme based on multiple data centers and multiple sets of Mgr. Next time, we will further introduce the scheme of high availability switching in case of failure or other abnormalities.

Enjoy GreatSQL 🙂

Article recommendation:

GreatSQL MGR FAQ
https://mp.weixin.qq.com/s/J6…

Wan Da #12, after the Mgr cluster hangs up, how can we choose the master automatically without manual intervention
https://mp.weixin.qq.com/s/07…

“2021 data technology Carnival · on line”: evolution and practice of MySQL high availability architecture
https://mp.weixin.qq.com/s/u7…

Packet capture analysis of the slowness of an SQL statement
https://mp.weixin.qq.com/s/AY…

Wan Da #15, what are the conditions that may cause the Mgr service to fail to start
https://mp.weixin.qq.com/s/in…

Technology sharing | why Mgr consistency mode does not recommend after
https://mp.weixin.qq.com/s/rN…

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 platformOpenWriterelease!