- 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 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:6449If 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.
Log in to any node of MGR cluster:
$ 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.
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...
Wan Da #20, how to filter data in index push down
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!