读写分离

本文描述如何为MGR集群构建读写分离方案。

1. InnoDB Cluster简介

MySQL InnoDB Cluster（简称MIC）是MySQL推出的整套解决方案，由几个部分组成：

  • MySQL Server，核心是Group Replication（组复制），简称MGR。
  • MySQL Shell，可编程的高级客户端，支持标准SQL语法、JavaScript语法、Python语法，以及API接口，可以更方便的管理和使用MySQL服务器。
  • MySQL Router，轻量级中间件，支持透明路由规则（读写分离及读负载均衡）。

MySQL Router是一个轻量级的中间件，它采用多端口的方案实现读写分离以及读负载均衡，而且同时支持mysql和mysql x协议。

整体系统架构如下图所示：
MySQL InnoDB Cluser架构

2. MySQL Router安装&初始化

MySQL Router最好和应用服务器部署在一起，所以本次将MySQL Router安装在另一个服务器上，IP地址是 172.16.16.14。

将MySQL Router和应用服务器部署在一起的好处在于，当某个后端数据库服务器发生宕机并下线及导致MGR发生切换时，部署在应用程序端的router程序能通过MGR的metadata信息感知到这个变化，并自动更新MGR拓扑结构，无需在应用程序上做任何变更，也无需针对router再次部署高可用切换方案。

戳此下载MySQL Router RPM安装包读写分离 - 图2 (opens new window)，选择下面的安装包：

  • greatsql-mysql-router-8.0.32-24.1.el8.x86_64.rpm

下载到本地后，执行安装：

  1. $ rpm -ivh greatsql-mysql-router-8.0.32-24.1.el8.x86_64.rpm
  2. Verifying...                          ################################# [100%]
  3. Preparing...                          ################################# [100%]
  4. Updating / installing...
  5.    1:greatsql-mysql-router-8.0.32-24.1################################# [100%]

MySQL Router对应的服务器端程序文件是 /usr/bin/mysqlrouter，第一次启动时要先进行初始化：

  1. #
  2. #参数解释
  3. # 参数 --bootstrap 表示开始初始化
  4. # 参数 GreatSQL@172.16.16.10:3306 是MGR集群管理员账号
  5. # --user=mysqlrouter 是运行mysqlrouter进程的系统用户名
  6. #
  7. $ mysqlrouter --bootstrap GreatSQL@172.16.16.10:3306 --user=mysqlrouter
  8. Please enter MySQL password for GreatSQL:   <-- 输入密码
  9. # 然后mysqlrouter开始自动进行初始化
  10. # 它会自动读取MGR的元数据信息，自动生成配置文件
  11. # Bootstrapping system MySQL Router instance...
  13. - Creating account(s) (only those that are needed, if any)
  14. - Verifying account (using it to run SQL queries that would be run by Router)
  15. - Storing account in keyring
  16. - Adjusting permissions of generated files
  17. - Creating configuration /etc/mysqlrouter/mysqlrouter.conf
  19. Existing configuration backed up to '/etc/mysqlrouter/mysqlrouter.conf.bak'
  21. # MySQL Router configured for the InnoDB Cluster 'GreatSQLMGR'
  23. After this MySQL Router has been started with the generated configuration
  25.     $ /etc/init.d/mysqlrouter restart
  26. or
  27.     $ systemctl start mysqlrouter
  28. or
  29.     $ mysqlrouter -c /etc/mysqlrouter/mysqlrouter.conf
  31. the cluster 'GreatSQLMGR' can be reached by connecting to:
  33. ## MySQL Classic protocol  <-- MySQL协议的两个端口
  35. - Read/Write Connections: localhost:6446
  36. - Read/Only Connections:  localhost:6447
  38. ## MySQL X protocol  <-- MySQL X协议的两个端口
  40. - Read/Write Connections: localhost:6448
  41. - Read/Only Connections:  localhost:6449

这就初始化完毕了，按照上面的提示，直接启动 mysqlrouter 服务即可：

  1. $ systemctl start mysqlrouter
  3. $ ps -ef | grep -v grep | grep mysqlrouter
  4. mysqlro+  6026     1  5 09:28 ?        00:00:00 /usr/bin/mysqlrouter
  6. $ netstat -lntp | grep mysqlrouter
  7. tcp        0      0 0.0.0.0:6446            0.0.0.0:*               LISTEN      6026/mysqlrouter
  8. tcp        0      0 0.0.0.0:6447            0.0.0.0:*               LISTEN      6026/mysqlrouter
  9. tcp        0      0 0.0.0.0:6448            0.0.0.0:*               LISTEN      6026/mysqlrouter
  10. tcp        0      0 0.0.0.0:6449            0.0.0.0:*               LISTEN      6026/mysqlrouter
  11. tcp        0      0 0.0.0.0:8443            0.0.0.0:*               LISTEN      6026/mysqlrouter

可以看到 mysqlrouter 服务正常启动了。

3. MySQL Router配置

MySQL Router初始化时自动生成的配置文件是 /etc/mysqlrouter/mysqlrouter.conf，主要是关于R/W、RO不同端口以及请求转发规则等配置，例如：

  1. [routing:GreatSQLMGR_rw]
  2. bind_address=0.0.0.0
  3. bind_port=6446
  4. destinations=metadata-cache://GreatSQLMGR/?role=PRIMARY
  5. routing_strategy=first-available
  6. protocol=classic
  8. [routing:GreatSQLMGR_ro]
  9. bind_address=0.0.0.0
  10. bind_port=6447
  11. destinations=metadata-cache://GreatSQLMGR/?role=SECONDARY
  12. routing_strategy=round-robin-with-fallback
  13. protocol=classic

可以根据需要自行修改绑定的IP地址和端口，以及请求转发规则。

关于请求转发规则，更详细的解释可参考以下内容：

修改完配置后，重启mysqlrouter服务即可。

4. 确认读写分离

现在，用客户端连接到6446（读写）端口，确认连接的是PRIMARY节点：

  1. $ mysql -h172.16.16.14 -uGreatSQL -p -P6446
  2. Enter password:
  3. ...
  4. greatsql> select @@server_uuid;
  5. +--------------------------------------+
  6. | @@server_uuid                        |
  7. +--------------------------------------+
  8. | 66c5a894-07e6-11ed-b1ff-00155d064000 |
  9. +--------------------------------------+
  11. # 确实是连接的PRIMARY节点
  12. greatsql> select MEMBER_ID,MEMBER_HOST,MEMBER_ROLE from performance_schema.replication_group_members;
  13. +--------------------------------------+--------------+-------------+
  14. | MEMBER_ID                            | MEMBER_HOST  | MEMBER_ROLE |
  15. +--------------------------------------+--------------+-------------+
  16. | 62edd23f-07fa-11ed-aad1-00155d064000 | 172.16.16.13 | SECONDARY   |
  17. | 66c5a894-07e6-11ed-b1ff-00155d064000 | 172.16.16.10 | PRIMARY     |
  18. | 6e65ef68-07e6-11ed-a6d8-00155d064000 | 172.16.16.11 | SECONDARY   |
  19. | 6f367f17-07e6-11ed-825d-00155d064000 | 172.16.16.12 | ARBITRATOR  |
  20. +--------------------------------------+--------------+-------------+

同样地，连接6447（只读）端口，确认连接的是SECONDARY节点：

  1. $ mysql -h172.16.16.14 -uGreatSQL -p -P6447
  2. Enter password:
  3. ...
  4. greatsql> select @@server_uuid;
  5. +--------------------------------------+
  6. | @@server_uuid                        |
  7. +--------------------------------------+
  8. | 62edd23f-07fa-11ed-aad1-00155d064000 |
  9. +--------------------------------------+
  10. # 确实是连接的SECONDARY节点

该连接保持住不退出，继续新建到6447端口的连接，查看 server_uuid，应该会发现读取到的是另一个 SECONDARY 节点的值，因为 MySQL Router 默认的读负载均衡机制是在几个只读节点间自动轮询，除非所有 SECONDARY 节点都不可用，否则只读请求不会转发到PRIMARY节点。

特别说明： 由于ARBITRATOR角色是在GreatSQL中特有的，原生的MySQL Router并不支持。这个节点不存储用户数据、日志等，仅参与MGR的网络投票，因此当MySQL Router轮询连接到该节点时，可能会出现类似下面的提示：

  1. $ mysql -h172.16.16.14 -uGreatSQL -p -P6447
  2. mysql: [Warning] Using a password on the command line interface can be insecure.
  3. ERROR 1045 (28000): Access denied for user 'GreatSQL'@'172.16.16.14' (using password: YES)

忽略这个错误提示，并尝试重连即可。

当然了，也可以通过修改MySQL Router的配置文件，把ARBITRATOR节点从只读节点列表中排除，例如：

  1. [routing:GreatSQLMGR_ro]
  2. bind_address=0.0.0.0
  3. bind_port=6447
  4. #destinations=metadata-cache://GreatSQLMGR/?role=SECONDARY
  5. destinations=172.16.16.11,172.16.11.13
  6. #routing_strategy=round-robin-with-fallback
  7. routing_strategy=round-robin
  8. protocol=classic

由于直接指定了只读节点列表，就无法再使用 round-robin-with-fallback 策略了，可以改成 round-roubin 策略。

5. 确认故障自动转移

如果PRIMARY节点宕机或切换，mysqlrouter也能实现自动故障转移，应用端不需要做任何变更，只需最多尝试重连或重新发起请求。

登入MGR集群任意节点：

  1. $ mysqlsh --uri GreatSQL@172.16.16.10:3306
  2. ...
  3. MySQL  172.16.16.10:3306 ssl  Py > c=dba.get_cluster();
  4. MySQL  172.16.16.10:3306 ssl  Py > c.set_primary_instance('172.16.16.11:3306');   <-- 切换PRIMARY节点
  5. Setting instance '172.16.16.11:3306' as the primary instance of cluster 'GreatSQLMGR'...
  7. Instance '172.16.16.10:3306' was switched from PRIMARY to SECONDARY.   <-- 切换了，从PRIMARY到SECONDARY
  8. Instance '172.16.16.11:3306' was switched from SECONDARY to PRIMARY.   <-- 切换了，从SECONDARY到PRIMARY
  9. Instance '172.16.16.12:3306' remains ARBITRATOR.   <-- 保持不变
  10. Instance '172.16.16.13:3306' remains SECONDARY.   <-- 保持不变
  12. WARNING: The cluster internal session is not the primary member anymore. For cluster management operations please obtain a fresh cluster handle using dba.get_cluster().
  14. The instance '172.16.16.11:3306' was successfully elected as primary.

回到前面连接6446端口的那个会话，再次查询 server_uuid，此时会发现连接自动断开了：

  1. greatsql> select @@server_uuid;
  2. ERROR 2013 (HY000): Lost connection to MySQL server during query
  3. greatsql> select @@server_uuid;
  4. ERROR 2006 (HY000): MySQL server has gone away
  5. No connection. Trying to reconnect...
  6. Connection id:    157990
  7. Current database: *** NONE ***
  9. +--------------------------------------+
  10. | @@server_uuid                        |
  11. +--------------------------------------+
  12. | 6e65ef68-07e6-11ed-a6d8-00155d064000 |   <-- 确认server_uuid变成172.16.16.11节点的值
  13. +--------------------------------------+

这就实现了自动故障转移。

再次查看切换后的MGR集群状态：

  1. MySQL  172.16.16.10:3306 ssl  Py >  c.status();
  2. ...
  3.         "topology": {
  4.             "172.16.16.10:3306": {
  5.                 "address": "172.16.16.10:3306",
  6.                 "memberRole": "SECONDARY",   <-- 切换成SECONDARY节点
  7.                 "mode": "R/O",
  8.                 "readReplicas": {},
  9.                 "replicationLag": null,
  10.                 "role": "HA",
  11.                 "status": "ONLINE",
  12.                 "version": "8.0.32"
  13.             },
  14.             "172.16.16.11:3306": {
  15.                 "address": "172.16.16.11:3306",
  16.                 "memberRole": "PRIMARY",   <-- 新的PRIMARY节点
  17.                 "mode": "R/W",
  18.                 "readReplicas": {},
  19.                 "replicationLag": null,
  20.                 "role": "HA",
  21.                 "status": "ONLINE",
  22.                 "version": "8.0.32"
  23.             },
  24. ...

利用MySQL Router构建一套支持读写分离、读负载均衡以及故障自动转移的MGR集群就部署完成。

问题反馈

联系我们

扫码关注微信公众号

greatsql-wx