记录分享工作的点点滴滴

0%

MySQL Router 高可用

发表于 更新于 分类于

MySQL Router是轻量级的中间件，可在您的应用程序与任何后端MySQL Server之间提供透明的路由。它可以用于多种使用案例，例如通过有效地将数据库流量路由到适当的后端MySQL服务器来提供高可用性和可伸缩性

MySQL router 介绍

建议的MySQL Router部署与应用程序位于同一主机上,建议将一个MySQL路由器实例部署到用于承载一个客户端应用程序的每台计算机上

故障转移

通常，高可用性的MySQL设置由一个主服务器和多个从服务器组成，如果MySQL主服务器不可用，则由应用程序来处理故障转移。使用MySQL Router，将基于负载平衡策略透明地路由应用程序连接，而无需实现自定义应用程序代码。

负载均衡

MySQL Router通过在服务器池中分布数据库连接来提供额外的可伸缩性和性能。例如，如果您有一组复制的MySQL服务器，则MySQL Router可以循环方式向它们分发应用程序连接。

可插拔架构

MySQL Router的可插拔体系结构使MySQL开发人员可以轻松扩展产品的附加功能，并为MySQL用户提供创建自己的自定义插件的能力，从而提供无限的可能性

MySQL router 安装

1
2
官方MySQL包：mysql-router-8.0.19-linux-glibc2.12-x86_64.tar.xz 
解压到：/opt/soft/mysqlrouter8019

innodb 集群信息

MySQL Server 节点信息

Role Hostname IP Port
Primary bjm6-184-40 192.168.184.40 3008
Second bjm6-196-59 192.168.196.59 3009
Second bjm6-196-85 192.168.196.85 3010

MySQL router 节点信息

Role Hostname IP
router bjm6-184-40 192.168.184.40
router bjm6-196-59 192.168.196.59

创建 router 实例

创建账号

Router实例通过，访问–bootstrap指定的集群实例地址（集群中的任意一个节点）获取集群元数据信息。这个访问账号至少需要具备以下最小权限

1
2
3
4
5
6
create user 'bootstrapuser'@'%' identified by '123456';
GRANT CREATE USER ON *.* TO 'bootstrapuser'@'%' WITH GRANT OPTION;
GRANT SELECT, INSERT, UPDATE, DELETE ON mysql_innodb_cluster_metadata.* TO 'bootstrapuser'@'%';
GRANT SELECT ON mysql.user TO 'bootstrapuser'@'%';
GRANT SELECT ON performance_schema.replication_group_members TO 'bootstrapuser'@'%';
GRANT SELECT ON performance_schema.replication_group_member_stats TO 'bootstrapuser'@'%';

引导启动 生成配置文件

通过 –directory 指定router目录，可以在同一台服务器上创建多个router（1个router只能针对1个集群）；
引导过程中会向v2.router表写入路由节点信息。如果指定的是second节点(只读)无法操作，会自动尝试连接primary节点。

注意：提前创建 router目录

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
[/usr/local/mysqlrouter8019/bin]# ./mysqlrouter --bootstrap bootstrapuser@192.168.196.59:3009 --directory=/work/myrouter --user=root
Please enter MySQL password for bootstrapuser:
# Bootstrapping MySQL Router instance at '/work/myrouter'...

Executing statements failed with: 'Error executing MySQL query "INSERT INTO mysql_innodb_cluster_metadata.v2_routers        (address, product_name, router_name) VALUES ('bjm6-196-59.58os.org', 'MySQL Router', '')": The MySQL server is running with the --read-only option so it cannot execute this statement (1290)' (1290), trying to connect to another node
Fetching Cluster Members
disconnecting from mysql-server
trying to connect to mysql-server at bjm6-184-40.58os.org:3008
- Creating account(s) (only those that are needed, if any)
- 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 /work/myrouter/mysqlrouter.conf

# MySQL Router configured for the InnoDB Cluster 'testcluster'

After this MySQL Router has been started with the generated configuration

    $ ./mysqlrouter -c /work/myrouter/mysqlrouter.conf

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

## MySQL Classic protocol

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

## MySQL X protocol

- Read/Write Connections: localhost:64460
- Read/Only Connections:  localhost:64470

生成配置文件

1
2
3
4
5
6
7
8
9
10
11
12
/work/myrouter/
├── data
│   ├── keyring
│   └── state.json
├── log
│   └── mysqlrouter.log
├── mysqlrouter.conf
├── mysqlrouter.key
├── mysqlrouter.pid
├── run
├── start.sh
└── stop.sh

指定配置文件启动

执行 start.sh脚本启动，客户端就可以通过 router访问集群了

/work/myrouter/start.sh

一个集群启动两个router

针对testcluster集群，安照上述步骤 部署两个router节点；
集群的第二个节点启动时，会复用第一个router里的部分配置，比如：访问信息端口。
但是配置里生成的user是不同的，每次初始化启动router会生成新的，这个user用于router和集群元数据的交互。

账号

1
2
3
4
5
6
7
8
9
mysql>  select user,host from mysql.user where user like 'mysql_router1\_%';  
+----------------------------+------+
| user                       | host |
+----------------------------+------+
| mysql_router1_2zv093v642oo | %    |
| mysql_router1_mqehrdp7cykn | %    |
| mysql_router1_pxor3vv2tzhz | %    |
+----------------------------+------+
3 rows in set (0.01 sec)

账号权限

1
2
3
4
5
6
7
8
9
10
11
12
13
mysql>  show grants for mysql_router1_mqehrdp7cykn@'%';
+------------------------------------------------------------------------------------------------------------------+
| Grants for mysql_router1_mqehrdp7cykn@%                                                                          |
+------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `mysql_router1_mqehrdp7cykn`@`%`                                                           |
| GRANT SELECT, EXECUTE ON `mysql_innodb_cluster_metadata`.* TO `mysql_router1_mqehrdp7cykn`@`%`                   |
| GRANT INSERT, UPDATE, DELETE ON `mysql_innodb_cluster_metadata`.`routers` TO `mysql_router1_mqehrdp7cykn`@`%`    |
| GRANT INSERT, UPDATE, DELETE ON `mysql_innodb_cluster_metadata`.`v2_routers` TO `mysql_router1_mqehrdp7cykn`@`%` |
| GRANT SELECT ON `performance_schema`.`global_variables` TO `mysql_router1_mqehrdp7cykn`@`%`                      |
| GRANT SELECT ON `performance_schema`.`replication_group_member_stats` TO `mysql_router1_mqehrdp7cykn`@`%`        |
| GRANT SELECT ON `performance_schema`.`replication_group_members` TO `mysql_router1_mqehrdp7cykn`@`%`             |
+------------------------------------------------------------------------------------------------------------------+
7 rows in set (0.01 sec)

通过MySQLshell查看 集群的router列表

通过登录MySQLshell查看：在 192.168.184.40 上执行
/opt/soft/mysqlshell/bin/mysqlsh –mysql -uri root@localhost:3008

可以看到属于testcluster集群的router信息 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
 MySQL  192.168.196.59:3009 ssl  JS > cluster.listRouters()
{
    "clusterName": "testcluster",
    "routers": {
        "bjm6-184-40.58os.org::": {
            "hostname": "bjm6-184-40.58os.org",
            "lastCheckIn": "2020-04-23 17:25:00",
            "roPort": 6447,
            "roXPort": 64470,
            "rwPort": 6446,
            "rwXPort": 64460,
            "version": "8.0.19"
        },
        "bjm6-196-59.58os.org::": {
            "hostname": "bjm6-196-59.58os.org",
            "lastCheckIn": "2020-04-23 17:25:03",
            "roPort": 6447,
            "roXPort": 64470,
            "rwPort": 6446,
            "rwXPort": 64460,
            "version": "8.0.19"
        }
    }
}
 MySQL  192.168.196.59:3009 ssl  JS >

登录MySQL 查看router信息

router初始引导后，会自动向库里插入router记录

1
2
3
4
5
6
7
8
 mysql> select * from mysql_innodb_cluster_metadata.routers;
+-----------+-------------+--------------+----------------------+---------+---------------------+---------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------+---------+
| router_id | router_name | product_name | address              | version | last_check_in       | attributes                                                                                                                                  | cluster_id                           | options |
+-----------+-------------+--------------+----------------------+---------+---------------------+---------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------+---------+
|         1 |             | MySQL Router | bjm6-184-40.58os.org | 8.0.19  | 2020-04-23 17:25:42 | {"ROEndpoint": "6447", "RWEndpoint": "6446", "ROXEndpoint": "64470", "RWXEndpoint": "64460", "MetadataUser": "mysql_router1_pxor3vv2tzhz"}  | 7341fcc6-7c71-11ea-858b-74867aea62bc | NULL    |
|        17 |             | MySQL Router | bjm6-196-59.58os.org | 8.0.19  | 2020-04-23 17:25:40 | {"ROEndpoint": "6447", "RWEndpoint": "6446", "ROXEndpoint": "64470", "RWXEndpoint": "64460", "MetadataUser": "mysql_router17_l2g9bbewgpxc"} | 7341fcc6-7c71-11ea-858b-74867aea62bc | NULL    |
+-----------+-------------+--------------+----------------------+---------+---------------------+---------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------+---------+
2 rows in set (0.00 sec)

测试连接

登录 MySQL3008 primary节点，手动建立dbtest01库，和对应的业务访问账号：test01_rw.

访问router的连接信息

opt/soft/mysql80/bin/mysql -utest01_rw -p -h192.168.184.40 -P6446 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
#通过查看account表，发现test01_rw连接集群的IP，是router地址 ；
#并且 CURRENT_CONNECTIONS 可以看到连接个数； 
mysql> select * from performance_schema.accounts;
+----------------------------+----------------------+---------------------+-------------------+
| USER                       | HOST                 | CURRENT_CONNECTIONS | TOTAL_CONNECTIONS |
+----------------------------+----------------------+---------------------+-------------------+sss
| NULL                       | NULL                 |                  42 |                85 |
| root                       | localhost            |                   8 |                61 |
| rpl_user                   | bjm6-196-59.58os.org |                   0 |                 1 |
| rpl_user                   | bjm6-196-85.58os.org |                   0 |                 1 |
| routeruser                 | bjm6-184-40.58os.org |                   0 |                 1 |
| root                       | bjm6-184-40.58os.org |                   0 |                 9 |
| bootstrapuser              | bjm6-196-59.58os.org |                   0 |                 4 |
| root                       | bjm6-196-59.58os.org |                   0 |                11 |
| mysql_router1_mqehrdp7cykn | bjm6-184-40.58os.org |                   1 |            364983 |
| mysql_router1_pxor3vv2tzhz | bjm6-196-59.58os.org |                   1 |              1970 |
| test01_rw                  | bjm6-184-40.58os.org |                   2 |                 6 |
| test01_rw                  | bjm6-196-59.58os.org |                   0 |                 4 |
+----------------------------+----------------------+---------------------+-------------------+
14 rows in set (0.00 sec)