Using MySQL Router to build read-write separated MGR cluster | MGR in simple terms

Posted by gregmiller on Wed, 09 Mar 2022 03:50:56 +0100

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

This paper introduces how to deploy MySQL Router in the front end of MGR cluster to realize read-write separation, read load balancing and automatic failover.

MySQL Router is a lightweight middleware. It adopts multi port scheme to realize read-write separation and read load balancing, and supports mysql and mysql x protocol at the same time.

It is recommended to deploy MySQL Router on the application server, one for each application server, so that the application can directly connect to the local IP, which is more efficient. Moreover, when the back-end database changes, the program side does not need to modify the IP configuration.

1. Deploy MySQL Router

MySQL Router needs to be initialized for the first time:

#
#Parameter interpretation
# Parameter -- bootstrap indicates that initialization is started
# Parameters GreatSQL@172.16.16.10:3306 is a special account for MGR service
# --user=mysqlrouter is the user name of the system running the mysqlrouter process
#
$ mysqlrouter --bootstrap GreatSQL@172.16.16.10:3306 --user=mysqlrouter
Please enter MySQL password for GreatSQL:   <-- Input password
# Then mysqlrouter starts to initialize automatically
# It will automatically read the metadata information of MGR and automatically generate the configuration file
Please enter MySQL password for GreatSQL:
# Bootstrapping system MySQL Router instance...

- Creating account(s) (only those that are needed, if any)
- Using existing certificates from the '/var/lib/mysqlrouter' directory
- Verifying account (using it to run SQL queries that would be run by Router)
- Storing account in keyring
- Adjusting permissions of generated files
- Creating configuration /etc/mysqlrouter/mysqlrouter.conf

# MySQL Router configured for the InnoDB Cluster 'MGR1'

After this MySQL Router has been started with the generated configuration

    $ /etc/init.d/mysqlrouter restart
or
    $ systemctl start mysqlrouter
or
    $ mysqlrouter -c /etc/mysqlrouter/mysqlrouter.conf

the cluster 'MGR1' can be reached by connecting to:

## MySQL classic protocol < -- two ports of MySQL protocol

- Read/Write Connections: localhost:6446
- Read/Only Connections:  localhost:6447

## MySQL x protocol < -- two ports of MySQL x protocol

- Read/Write Connections: localhost:6448
- Read/Only Connections:  localhost:6449

If you want to customize the name and directory, you can also specify the -- name and -- directory options during initialization, so that you can deploy multiple Router instances on the same server. Refer to this article: Can MySQL Router run multiple instances in the same system environment

2. Start mysqlrouter service

This completes the initialization. Follow the above prompts and directly start the mysqlrouter service:

[root@greatsql]# systemctl start mysqlrouter

[root@greatsql]# ps -ef | grep -v grep | grep mysqlrouter
mysqlro+  6026     1  5 09:28 ?        00:00:00 /usr/bin/mysqlrouter

[root@greatsql]# netstat -lntp | grep mysqlrouter
tcp        0      0 0.0.0.0:6446            0.0.0.0:*               LISTEN      6026/mysqlrouter
tcp        0      0 0.0.0.0:6447            0.0.0.0:*               LISTEN      6026/mysqlrouter
tcp        0      0 0.0.0.0:6448            0.0.0.0:*               LISTEN      6026/mysqlrouter
tcp        0      0 0.0.0.0:6449            0.0.0.0:*               LISTEN      6026/mysqlrouter
tcp        0      0 0.0.0.0:8443            0.0.0.0:*               LISTEN      6026/mysqlrouter

You can see that the mysqlrouter service is started normally.

The configuration file automatically generated during mysqlrouter initialization is / etc / mysqlrouter / mysqlrouter The main configurations of RO / conf and w / conf are:

[routing:greatsqlMGR_rw]
bind_address=0.0.0.0
bind_port=6446
destinations=metadata-cache://greatsqlMGR/?role=PRIMARY
routing_strategy=first-available
protocol=classic

You can modify the bound IP address and port as needed, or specify the -- conf base port option during initialization to customize the initial port number.

3. Confirm the read-write separation effect

Now, connect the client to the 6446 (read-write) port and confirm that the PRIMARY node is connected:

$ mysql -h172.16.16.10 -u GreatSQL -p -P6446
Enter password:
...
#Remember the following members_ ID
mysql> select MEMBER_ID,MEMBER_ROLE from performance_schema.replication_group_members;
+--------------------------------------+-------------+
| MEMBER_ID                            | MEMBER_ROLE |
+--------------------------------------+-------------+
| 4ebd3504-11d9-11ec-8f92-70b5e873a570 | PRIMARY     |
| 549b92bf-11d9-11ec-88e1-70b5e873a570 | SECONDARY   |
| 5596116c-11d9-11ec-8624-70b5e873a570 | SECONDARY   |
+--------------------------------------+-------------+

mysql> select @@server_uuid;
+--------------------------------------+
| @@server_uuid                        |
+--------------------------------------+
| 4ebd3504-11d9-11ec-8f92-70b5e873a570 |
+--------------------------------------+
# It is indeed a connected PRIMARY node

Similarly, connect the 6447 (read-only) port and confirm that the SECONDARY node is connected:

$ mysql -h172.16.16.10 -u GreatSQL -p -P6447
Enter password:
...
mysql> select @@server_uuid;
+--------------------------------------+
| @@server_uuid                        |
+--------------------------------------+
| 549b92bf-11d9-11ec-88e1-70b5e873a570 |
+--------------------------------------+
# It is indeed a connected SECONDARY node

4. Confirm the read-only load balancing effect

MySQL Router connects the read-write node (Primary node). The default policy is first available, that is, only the first available node is connected. The default policy of the Router connecting to the read-only node (Secondary node) is round robin with fallback, which will poll among the read-only nodes.

Keep the original connection of port 6447 and continue to create a new connection to port 6447. Check the server_uuid. At this time, you should find that the values read are those of other read-only nodes, because the read load balancing mechanism of mysqlrouter is to automatically poll among several read-only nodes. Under the default round robin with fallback policy, the read-only request will be sent to the PRIMARY node only when all read-only nodes are unavailable.

For the connection strategy of the Router, please refer to the following in the FAQ document: 24. Can MySQL router be configured to poll between MGR master and slave nodes , or MySQL Router official document: routing_strategy parameters / options

5. Confirm the automatic failover function

Next, when the PRIMARY node is down or switched, mysqlrouter can also realize automatic failover.

$ mysqlsh --uri GreatSQL@172.16.16.10:3306
...
MySQL  172.16.16.10:3306 ssl  JS >  var c=dba.getCluster();
MySQL  172.16.16.10:3306 ssl  JS >  c.setPrimaryInstance('172.16.16.11:3306');   <-- switch PRIMARY node
Setting instance '172.16.16.11:3306' as the primary instance of cluster 'MGR1'...

Instance '172.16.16.10:3306' was switched from PRIMARY to SECONDARY.   <-- Switched, from PRIMARY reach SECONDARY
Instance '172.16.16.11:3306' was switched from SECONDARY to PRIMARY.   <-- Switched, from SECONDARY reach PRIMARY
Instance '172.16.16.12:3306' remains SECONDARY.   <-- remain unchanged

WARNING: The cluster internal session is not the primary member anymore. For cluster management operations please obtain a fresh cluster handle using dba.getCluster().

The instance '172.16.16.11:3306' was successfully elected as primary.

Go back to the previous session connected to port 6446 and query the server again_ UUID, you will find that the connection is automatically disconnected:

mysql> select @@server_uuid;
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql> select @@server_uuid;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    157990
Current database: *** NONE ***

+--------------------------------------+
| @@server_uuid                        |
+--------------------------------------+
| 549b92bf-11d9-11ec-88e1-70b5e873a570 |   <-- confirm server_uuid Become new
+--------------------------------------+

This enables automatic failover.

So far, a set of MGR cluster supporting read-write separation, read load balancing and automatic failover has been deployed by using MySQL Router and GreatSQL.

6. Summary

This paper introduces how to use MySQL Router to realize read-write separation, read load balancing and automatic failover. Using MySQL Router can improve the transparency of the application end. When there are some changes in the back-end database, the application end does not need to change frequently.

Programmer Think