MySQL 5.7: 使用MySQL Router实现应用程序的高可用

头像
developerworks
    阅读 9 分钟
    10

    MySQL 5.7: 使用组复制(MySQL Group Replication)
    MySQL 5.7: 使用MySQL Router实现应用程序的高可用
    MySQL 5.7: 把现有的复制组迁移到InnoDB Cluster
    MySQL 5.7: 使用PMM监视和管理数据库

    MySQL Router

    前面一片文章说了如何配置MySQL的分组复制. 基于分组复制的机制, 当主节点崩溃离开集群, 剩余的其他节点会相互协商, 然后选举一个新的主节点. 这里有一个问题, 就是应用程序端如果连接到了主节点, 这时主节点崩溃离开集群. 可用的数据库IP地址发生变化. 客户端应用程序这个时候还是会向失败的节点尝试连接, 虽然可以修改客户端应用程序的连接配置, 但是这种情况基本是不现实的.

    虽然我们可以通过下面的SQL语句获取主节点的IP地址

    SELECT * FROM performance_schema.replication_group_members
    WHERE MEMBER_ID = (
        SELECT VARIABLE_VALUE
        FROM performance_schema.global_status
        WHERE VARIABLE_NAME= 'group_replication_primary_member'
   );

    但是通过应用程序动态的获取可用数据库的IP地址. 这种方式感觉不怎么好. 很麻烦, 要写多余的代码.

    配置MySQL Router首先需要MySQL Shell 工具, 在 MySQL Shell 部分有详细的说明

    概述

    下面是MySQL Router 和集群的基本关系图

    mysql-router-positioning.png

    上图充分说明了, MySQL Router 在InnoDB集群里面的角色. 主要作用是为数据库集群提供一个虚拟IP. 作为应用程序单一连接点. 通过这个单一的连接点实现负载均衡, 读写分离, 故障转移等数据库高可用方案.

    推荐安装在应用程序所在的机器上, 原因包括:

    • 通过Unix套接字连接, 而不是TCP/IP, 提升性能

    • 降低网络延迟

    • MySQL实例不需要额外的账号, 只需要一个 router@198.51.100.45, 而不是 myapp@%

    • 提升应用程序服务器的弹性

    安装和配置 MySQL Router

    wget https://dev.mysql.com/get/mysql-apt-config_0.8.9-1_all.deb
dpkg -i mysql-apt-config_0.8.9-1_all.deb
aptitude update
aptitude install -y mysql-router

    生成配置和启动脚本

    mysqlrouter --bootstrap 172.18.149.213:3306 --directory /data/mysqlrouter --user=root --conf-use-sockets --force
    Please enter MySQL password for root:

Bootstrapping MySQL Router instance at /data/mysqlrouter...
MySQL Router  has now been configured for the InnoDB cluster 'dc'.

The following connection information can be used to connect to the cluster.

Classic MySQL protocol connections to cluster 'dc':
- Read/Write Connections: localhost:6446
- Read/Write Connections: /data/mysqlrouter/mysql.sock
- Read/Only Connections: localhost:6447
- Read/Only Connections: /data/mysqlrouter/mysqlro.sock

X protocol connections to cluster 'dc':
- Read/Write Connections: localhost:64460
- Read/Write Connections: /data/mysqlrouter/mysqlx.sock
- Read/Only Connections: localhost:64470
- Read/Only Connections: /data/mysqlrouter/mysqlxro.sock

    输入密码后, 文件就生成到 /data/mysqlrouter 目录了.

    --conf-use-sockets 选项还会监听Unix套接字
    关于 MySQL Router 命令行的所有参数说明, 参考这里: https://dev.mysql.com/doc/mys...

    启动MySQL Router

    cd /data/mysqlrouter
./start.sh
PID 3976 written to /data/mysqlrouter/mysqlrouter.pid

    查看MySQL Router的监听端口

    下面列出了MySQL Router 监听的TCP端口

    netstat -anpt |grep router
tcp        0      0 0.0.0.0:64460           0.0.0.0:*               LISTEN      3976/mysqlrouter
tcp        0      0 0.0.0.0:6446            0.0.0.0:*               LISTEN      3976/mysqlrouter
tcp        0      0 0.0.0.0:6447            0.0.0.0:*               LISTEN      3976/mysqlrouter
tcp        0      0 0.0.0.0:64470           0.0.0.0:*               LISTEN      3976/mysqlrouter
tcp        0      0 172.18.149.215:44350    172.18.149.213:3306     ESTABLISHED 3976/mysqlrouter

    各个端口的含义我们在之前创建MySQL Router配置时的输出已经说明了, 另外配置文件中也详细说明了每个端口的含义:

    cat /data/mysqlrouter/mysqlrouter.conf
    # File automatically generated during MySQL Router bootstrap
[DEFAULT]
user=root
logging_folder=/data/mysqlrouter/log
runtime_folder=/data/mysqlrouter/run
data_folder=/data/mysqlrouter/data
keyring_path=/data/mysqlrouter/data/keyring
master_key_path=/data/mysqlrouter/mysqlrouter.key

[logger]
level = DEBUG

[metadata_cache:dc]
router_id=1
bootstrap_server_addresses=mysql://172.18.149.213:3306,mysql://172.18.149.215:3306,mysql://172.18.149.214:3306
user=mysql_router1_4d98tioywyow
metadata_cluster=dc
ttl=300

[routing:dc_default_rw]
bind_address=0.0.0.0
bind_port=6446
socket=/data/mysqlrouter/mysql.sock
destinations=metadata-cache://dc/default?role=PRIMARY
mode=read-write
protocol=classic

[routing:dc_default_ro]
bind_address=0.0.0.0
bind_port=6447
socket=/data/mysqlrouter/mysqlro.sock
destinations=metadata-cache://dc/default?role=SECONDARY
mode=read-only
protocol=classic

[routing:dc_default_x_rw]
bind_address=0.0.0.0
bind_port=64460
socket=/data/mysqlrouter/mysqlx.sock
destinations=metadata-cache://dc/default?role=PRIMARY
mode=read-write
protocol=x

[routing:dc_default_x_ro]
bind_address=0.0.0.0
bind_port=64470
socket=/data/mysqlrouter/mysqlxro.sock
destinations=metadata-cache://dc/default?role=SECONDARY
mode=read-only
protocol=x

    下面列举除了MySQL Router监听的Unix套接字(如果使用了--conf-use-sockets选项)

    netstat -anpx |grep router
unix  2      [ ACC ]     STREAM     LISTENING     41668    3791/mysqlrouter    /tmp/myrouter/mysql.sock
unix  2      [ ACC ]     STREAM     LISTENING     42187    3791/mysqlrouter    /tmp/myrouter/mysqlro.sock
unix  2      [ ACC ]     STREAM     LISTENING     43361    3791/mysqlrouter    /tmp/myrouter/mysqlx.sock
unix  2      [ ACC ]     STREAM     LISTENING     42189    3791/mysqlrouter    /tmp/myrouter/mysqlxro.sock

    我们看到有4个Unix套接字

    Unix 套接字 说明 读写模式
    mysql.sock MySQL协议 读写
    mysqlro.sock MySQL协议 只读
    mysqlx.sock X 协议 读写
    mysqlxro.sock X 协议 只读

    注意: 该表只是说明了每个Unix套接字承担的角色, 没有限制你连接到只读模式的端口执行写操作.

    连接到 MySQL Router 执行SQL

    mysql -u root -h 172.18.149.215 -P 6446 -p
# 创建数据库
mysql> create database b;
Query OK, 1 row affected (0.00 sec)
# 切换
mysql> use b;
Database changed
# 创建表
mysql> create table a (c int);
Query OK, 0 rows affected (0.00 sec)
# 插入值
# 出错了, 因为没有主键
mysql> insert into a values(1);
ERROR 3098 (HY000): The table does not comply with the requirements by an external plugin.

# 添加主键列

mysql> alter table a add column a_id int(4);
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table a add primary key pk (a_id);
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

# 插入值
mysql> insert into a values (1,2);
Query OK, 1 row affected (0.01 sec)

    OK, MySQL Router到这里就配置好了, 在应用程序代码里面直接连接到MySQL Router的IP地址可以了.

    关于数据节点变更

    MySQL Router 在初始化配置的时候是连接到集群节点读取集群的元数据的. 如果在集群中新增或减少节点. 需要同步更新MySQL Router的配置, 重新执行以下命令即可:

    mysqlrouter --bootstrap 172.18.149.213:3306 --directory /data/mysqlrouter --user=root --conf-use-sockets --force

    当然, 更新了MySQL Router的配置的配置, 需要重启MySQL Router:

    cd /data/mysqlrouter
./stop.sh
./start.sh

    关于 MySQL Router 的部署问题

    MySQL Router 目前只支持500并发连接(https://dev.mysql.com/doc/mys... 官方建议把 MySQL Router 部署到和应用程序相同的一台机器上. 通过MySQL Router 的 --conf-use-sockets 启动选项, 我们可以把MySQL Router作为一个本地代理来使用.

    为了解决MySQL Router的单点问题. 我们可以在MySQL Router的上层在搭建一个负载均衡服务器. 我现在的环境全部是基于阿里云的, 因此很自然的选择了阿里云的SLB作为负载均衡解决方案. 如果自建服务器可以选择LVS, HAProxy等方案.

    在MySQL Router之上再做负载均衡. 管理上更复杂了, 这里还是采用一个应用程序部署一个本地MySQL Router的策略.

    MySQL Shell

    • 交互式代码执行

    • 支持的语言: Javascript, Python, SQL

    • 批处理

    • 输出格式: 制表符, 表格, JSON

    • 多行支持

    • 日志

    • MySQL协议支持, X Protocol支持

    安装

    wget https://dev.mysql.com/get/mysql-apt-config_0.8.9-1_all.deb
dpkg -i mysql-apt-config_0.8.9-1_all.deb
aptitude update
aptitude install -y mysql-shell

    连接方式: 通过URL的方式

    mysqlsh --uri ${uri}

    URI 支持系列各种形式

    mysqlx://user@localhost:33065
mysql://user@localhost:3333
mysqlx://user@server.example.com/
mysqlx://user@198.51.100.14:123
mysqlx://user@[2001:db8:85a3:8d3:1319:8a2e:370:7348]
mysqlx://user@198.51.100.1/world%5Fx
mysqlx://user@198.51.100.2:33060/world

    连接方式: 通过参数方式

    --dbuser (-u) value
--dbpassword value
--host (-h) value
--port (-P) value
--schema (-D) value
--password (-p)
--socket (-S)

    参数别名

    --user is equivalent to --dbuser
--password is equivalent to --dbpassword
--database is equivalent to --schema

    覆盖问题

    参数的优先级比URL高, 下面的例子通过 otheruser 用户进行连接而不是URL中指定的 user

    mysqlsh --uri user@localhost:33065 --user otheruser
    # MySQL协议
mysqlsh --mysql -u user -h localhost
# X协议
mysqlsh --mysqlx -u user -h localhost -P 33065

    通过Javascript连接

    # X协议

var session=mysqlx.getSession('root@localhost:33060', 'password');

# MySQL协议

var session = mysql.getClassicSession('root@localhost:3306', 'password');

    在代码中使用加密连接

    var session=mysqlx.getSession({host: 'localhost',
  dbUser: 'root',
  dbPassword: 'password',
  ssl_ca: "path_to_ca_file",
  ssl_cert: "path_to_cert_file",
  ssl_key: "path_to_key_file"
});

    安装X插件的几种方式

    如果从支持X Protocol的客户端连接到数据库, 在数据库端需要安装X Plugin以提供对 X Protocol的支持, 安装X Plugin只需要对 mysql.plugin 表有 INSERT 权限即可.

    1.通过 mysqlsh 安装

    # 安装需要的仓库, 选中 MySQL Tools & Connectors (Currently selected: Enabled)
wget https://dev.mysql.com/get/mysql-apt-config_0.8.9-1_all.deb
dpkg -i mysql-apt-config_0.8.9-1_all.deb

    执行如下命令, 输入密码:

    mysqlsh -u root -h localhost --classic --dba enableXProtocol

    如果看到如下输出, 标识安装完成:

    mysqlsh -u root -h 172.18.149.213 --classic --dba enableXProtocol
    Creating a Classic Session to 'root@172.18.149.213'
Enter password:
Your MySQL connection id is 70
Server version: 5.7.20-log MySQL Community Server (GPL)
No default schema selected; type \use <schema> to set one.
enableXProtocol: Installing plugin mysqlx...
enableXProtocol: done

    2.通过MySQL客户端

    mysql -u user -p
mysql> INSTALL PLUGIN mysqlx SONAME 'mysqlx.so';

    1. 插件的加载需要 mysql.session 用户, mysql.session 用户在 MySQL 5.7.19 中已经添加. 早期的版本需要执行 mysql_upgrade, 否则加载X插件的时候回报错:

    There was an error when trying to access the server with user: mysql.session@localhost. Make sure the user is present in the server and that mysql_upgrade was ran after a server update..

    验证

    mysqlsh -u root --sqlc -e "show plugins"
mysql -u root -p -e "show plugins"

mysql> select * from plugin;
+-------------------+----------------------+
| name              | dl                   |
+-------------------+----------------------+
| group_replication | group_replication.so |
| mysqlx            | mysqlx.so            |
+-------------------+----------------------+

    如果在输出中看到 mysqlx.so 标识X插件安装成功. X插件安装过程会自动创建一个用户: mysqlxsys@localhost

    卸载X插件

    UNINSTALL PLUGIN mysqlx;

    删除插件的同时也会删除 mysqlxsys@localhost 用户.

    参考资料

    https://dev.mysql.com/doc/ref...
    https://dev.mysql.com/doc/mys...
    https://dev.mysql.com/doc/mys...
    https://dev.mysql.com/doc/mys...

    mysqlha
    developerworks
    1.7k 声望266 粉丝
    « 上一篇
    MySQL 5.7: 使用组复制(MySQL Group Replication)
    下一篇 »
    MySQL 5.7: 把现有的复制组迁移到InnoDB Cluster

    引用和评论

    被 4 篇内容引用
    推荐阅读
    头像
    JRebel 远程热部署 Spring boot 项目

    developerworks阅读 782

    头像
    在Java程序中监听mysql的binlog

    huan1993赞 7阅读 1k

    头像
    Mysql数据库sql优化案例

    杜若赞 4阅读 1.9k

    头像
    MySQL 亿级数据平滑迁移实战

    vivo互联网技术赞 2阅读 812

    头像
    MySQL间隙锁，next-key锁

    杜若赞 2阅读 9.8k评论 1

    头像
    高效工程师的七个习惯

    Bytebase赞 3阅读 368

    头像
    sqlmap注入语句学习，帮助你排查SQL注入漏洞

    TANKING赞 2阅读 431

    0 条评论
    得票最新