使用 ProxySQL 实现 MySQL Group Replication 的故障转移, 读写分离 (一)
导读:
在之前, 我们搭建了 MySQL 组复制集群环境, MySQL 组复制集群环境解决了 MySQL 集群内部的自动故障转移, 但是, 组复制并没有解决外部业务的故障转移. 举个例子, 在 A,B,C 3 台机器上搭建了组复制环境, 且运行在单主模式下, 这里假设 A 为主节点, 应用程序连接 A 写数据, 如果 A 节点发生宕机, 主节点切换到 B 机器上, 此时, 应用程序是不会自动连接到 B 服务器上的, 需要人工进行切换.
在这篇文章中, 我们要介绍的 ProxySQL 就能够解决上面的问题, ProxySQL 能够实现业务层面故障转移, 读写分离功能, 当然 ProxySQL 不仅仅只有这两项功能, 还有更多的其它功能. 其架构如下:
我们不妨来了解一下.
(一)ProxySQL 简介
ProxySQL 是一款 MySQL 代理软件, 其核心特点为读写分离, 故障转移, 详细其功能如下:
应用层代理. ProxySQL 不仅能够实现负载均衡, 还可提供端到端连接处理, 实时信息统计和数据库流量检查;
零停机时间变更. ProxySQL 在内存中直接进行配置修改, 然后可以持久存储到磁盘和推送到运行中;
数据库防火墙. 可充当应用程序与数据库之间的防火墙, 使 DBA 可以保护数据库免受恶意活动或有问题的程序的影响;
高级查询规则. 使用 ProxySQL 丰富的查询规则定义查询路由, 有效的分发和缓存数据, 从而最大的提高数据库服务缓存效率;
数据分片与转换
故障转移检测. ProxySQL 通过连续监视数据库后端并在拓扑更改时将流量重新路由到运行正常的节点.
这里, 我们使用 ProxySQL 来对 MySQL 组复制环境实现读写分离以及故障转移. 我的环境如下:
| IP 地址 | 主机名 | 用途 |
| 192.168.10.11 | mgr-node1 | MySQL 组复制成员 |
| 192.168.10.12 | mgr-node2 | MySQL 组复制成员 |
| 192.168.10.13 | mgr-node3 | MySQL 组复制成员 |
| 192.168.10.10 | proxysql | ProxySQL 代理服务器 |
MySQL 采用多主模式, 搭建过程见文档:《MySQL 组复制 MGR(二)-- 组复制搭建》, 本文把重点放在 ProxySQL 的搭建与配置上.
(二)安装 ProxySQL
安装 ProxySQL, 有 2 种方法, 如果有网络, 可以直接使用 yum 安装, 如果没有网络, 可以下载 ProxySQL 发行包安装, 下载地址为: https://github.com/sysown/proxysql/releases . 这里为了方便, 直接使用 yum 在线安装.
添加 yum 源, 使用 Linux root 用户执行如下配置:
- cat <<EOF | tee /etc/yum.repos.d/proxysql.repo
- [proxysql_repo]
- name= ProxySQL YUM repository
- baseurl=https://repo.proxysql.com/ProxySQL/proxysql-2.0.x/centos/\$releasever
- gpgcheck=1
- gpgkey=https://repo.proxysql.com/ProxySQL/repo_pub_key
- EOF
安装 proxysql:
yum install -y proxysql OR yum install proxysql-version
如果要查看安装的文件在哪, 可以使用如下命令:
- [root@proxysql yum.repos.d]# rpm -ql proxysql
- /etc/logrotate.d/proxysql
- /etc/proxysql.cnf
- /etc/systemd/system/proxysql-initial.service
- /etc/systemd/system/proxysql.service
- /usr/bin/proxysql
- /usr/share/proxysql/tools/proxysql_galera_checker.sh
- /usr/share/proxysql/tools/proxysql_galera_writer.pl
查看 ProxySQL 进程:
- [root@proxysql yum.repos.d]# ps -ef|grep proxy
- avahi 740 1 0 15:52 ? 00:00:00 avahi-daemon: registering [proxysql-65.local]
- root 761 1 0 15:52 ? 00:00:00 /usr/sbin/gssproxy -D
- proxysql 2058 1 0 16:09 ? 00:00:00 /usr/bin/proxysql --idle-threads -c /etc/proxysql.cnf
- proxysql 2059 2058 1 16:09 ? 00:00:00 /usr/bin/proxysql --idle-threads -c /etc/proxysql.cnf
- root 2087 1589 0 16:09 pts/0 00:00:00 grep --color=auto proxy
查看端口, 6032 是 proxysql 的管理端口, 6033 是对外服务端口
- [root@proxysql yum.repos.d]# netstat -anlp | grep proxysql
- tcp 0 0 0.0.0.0:6032 0.0.0.0:* LISTEN 2059/proxysql
- tcp 0 0 0.0.0.0:6033 0.0.0.0:* LISTEN 2059/proxysql
(三)启动关闭 ProxySQL
启动 ProxySQL
service proxysql start
关闭 ProxySQL
service proxysql stop
重启 ProxySQL
service proxysql restart
查看 ProxySQL 的状态
service proxysql status
(四)ProxySQL 基础知识了解
ProxySQL 的配置, 相对而言还是比较复杂的. 因此, 在配置 ProxySQL 之前, 我们需要对其架构有一些了解, 这样在配置的时候, 才不会一脸懵逼.
(4.1)ProxySQL 多层配置系统
前面我们说到 ProxySQL 具有 "零停机时间变更" 功能, 它是通过 3 层配置来实现的, 3 层配置包括: Runtime,Memory,Disk & Configuration File.
Runtime 层表示 ProxySQL 工作线程使用的内存数据结构;
Memory(也被称为 main)层经由一个 MySQL 兼容接口露出的内存数据库, 用户可以使用 MySQL 客户端连接到管理界面, 查看, 编辑 ProxySQL 配置表;
Disk & Configuration File.Disk 层是一个存放在磁盘上的 SQLite3 数据库, Disk 层可将内存中的配置信息保存到磁盘, 以便 ProxySQL 重新启动后配置还可用.
3 个层面的信息有什么区别呢? 我个人的理解是: 3 个层面保存的都是 ProxySQL 的配置信息, 如果管理员未作修改, 那么 3 个层面的配置信息是相同的. 如果管理员要修改配置信息, 首先需要修改 Memory 层, 要让修改的信息立刻生效, 则需要把 Memory 层的变更信息推到 Runtime 层; 要让修改的配置信息在 ProxySQL 重启后还能保存下来, 则需要把 Memory 层的信息推到 Disk 层. Runtime 层是 ProxySQL 正在使用的配置信息, Memory 层是用户可以编辑的信息, Disk 层可以把配置信息永久保存在磁盘上.
各层之间数据如何同步呢? 我们可以看上图的箭头部分, 通过 load/save 命令来实现同步. 具体命令如下:
[1] LOAD <item> FROM MEMORY/LOAD <item> TO RUNTIME
将配置项从内存数据库加载到运行时数据结构
[2] SAVE <item> TO MEMORY/SAVE <item> FROM RUNTIME
将配置项从运行时保存到内存数据库中
[3] LOAD <item> TO MEMORY/LOAD <item> FROM DISK
将持久性配置项目从磁盘数据库加载到内存数据库
[4] SAVE <item> FROM MEMORY/SAVE <item> TO DISK
将配置项从内存数据库保存到磁盘数据库
[5] LOAD <item> FROM CONFIG
将配置项从配置文件加载到内存数据库中
常用的配置有:
- # 激活用户配置到 RUNTIME
- LOAD MySQL USERS TO RUNTIME;
- # 保存用户信息到磁盘上
- SAVE MySQL USERS TO DISK;
- ---------------------------------
- # 激活 MySQL 服务器信息到 RUNTIME
- LOAD MySQL SERVERS TO RUNTIME;
- # 保存 MySQL 服务器信息到磁盘
- SAVE MySQL SERVERS TO DISK;
- ---------------------------------
- # 激活查询路由规则到 RUNTIME
- LOAD MySQL QUERY RULES TO RUNTIME;
- # 保存查询路由规则到磁盘
- SAVE MySQL QUERY RULES TO DISK;
- ----------------------------------
- # 激活 MySQL 变量到 RUNTIME
- LOAD MySQL VARIABLES TO RUNTIME;
- # 保存 MySQL 变量到磁盘
- SAVE MySQL VARIABLES TO DISK;
- ----------------------------------
- # 激活 proxySQL admin 变量到 RUNTIME
- LOAD ADMIN VARIABLES TO RUNTIME;
- # 保存 proxySQL admin 变量到磁盘
- SAVE ADMIN VARIABLES TO DISK;
(4.2)ProxySQL 的配置管理接口
ProxySQL 有 2 种配置方式:
使用 ProxySQL 的命令行管理接口进行配置
使用配置文件进行配置
通常使用第一种方法进行配置, 这里我们只了解第 1 种方法.
ProxySQL 管理界面使用的是 MySQL 协议的界面, 通过使用 MySQL 客户端连接到 SQLite3 进行配置的查询, 管理. 可以使用默认的 admin 用户连接到 proxySQL 数据库.
- [root@proxysql ~]# MySQL -uadmin -padmin -h127.0.0.1 -P6032
- MySQL> show databases;
- +-----+---------------+-------------------------------------+
- | seq | name | file |
- +-----+---------------+-------------------------------------+
- | 0 | main | |
- | 2 | disk | /var/lib/proxysql/proxysql.db |
- | 3 | stats | |
- | 4 | monitor | |
- | 5 | stats_history | /var/lib/proxysql/proxysql_stats.db |
- +-----+---------------+-------------------------------------+
这些数据库作用如下:
main: 内存配置数据库, 使用此数据库, 可以方便的查询和更新 ProxySQL 的配置. 与上面所曾配置系统的 Memory 层对应;
disk:"main" 数据库的磁盘镜像. 重新启动 ProxySQL 时, main 中的数据就是从该数据库加载的. 与上面所曾配置系统的 disk 层对应;
stats:ProxySQL 收集的一些指标. 如每个查询规则的匹配次数, 当前正在运行的查询等;
monitor: 包含于 ProxySQL 连接的后端服务器的指标. 如连接带后端服务器对其进行 ping 操作的最小, 最大时间;
ProxySQL 设定了 2 个用户来管理配置数据库:
账号 admin 密码 admin : 该用户具有全部标的读写权限;
账号 stats 密码 stats : 该用户具有统计信息表的只读权限;
(五)一步一步配置 ProxySQL-- 基础配置
(5.1)检查配置信息
查看相关配置表是否存在信息, 因为还没开始配置, 所以是不存在信息的, 如果已经配置过了, 可以先删除信息.
- MySQL> select * from mysql_servers;
- Empty set (0.00 sec)
- MySQL> select * from mysql_users;
- Empty set (0.01 sec)
- MySQL> select * from mysql_query_rules;
- Empty set (0.00 sec)
- MySQL> select * from mysql_group_replication_hostgroups;
- Empty set (0.00 sec)
(5.2)组的配置
所谓组的配置, 即定义读组, 写组等, 可以使用如下两个表来定义读写组:
mysql_replication_hostgroups: 该表用于传统的 master/slave 的异步复制或者半同步复制的配置.
mysql_group_replication_hostgroups: 该表用于 MySQL Group Replication,InnoDB Cluster or Galera/Percona XtraDB Cluster 的配置
因为我们这里是使用 proxySQL 来实现 MGR 集群业务层面的实现故障转移以及读写分离的, 所以配置 mysql_group_replication_hostgroups 表即可, 该表定义如下:
- show create table mysql_group_replication_hostgroups;
- ------------------------------------------------------------
- CREATE TABLE mysql_group_replication_hostgroups (
- writer_hostgroup INT CHECK (writer_hostgroup>=0) NOT NULL PRIMARY KEY,
- backup_writer_hostgroup INT CHECK (backup_writer_hostgroup>=0 AND backup_writer_hostgroup<>writer_hostgroup) NOT NULL,
- reader_hostgroup INT NOT NULL CHECK (reader_hostgroup<>writer_hostgroup AND backup_writer_hostgroup<>reader_hostgroup AND reader_hostgroup>0),
- offline_hostgroup INT NOT NULL CHECK (offline_hostgroup<>writer_hostgroup AND offline_hostgroup<>reader_hostgroup AND backup_writer_hostgroup<>offline_hostgroup AND offline_hostgroup>=0),
- active INT CHECK (active IN (0,1)) NOT NULL DEFAULT 1,
- max_writers INT NOT NULL CHECK (max_writers>= 0) DEFAULT 1,
- writer_is_also_reader INT CHECK (writer_is_also_reader IN (0,1,2)) NOT NULL DEFAULT 0,
- max_transactions_behind INT CHECK (max_transactions_behind>=0) NOT NULL DEFAULT 0,
- comment VARCHAR,
- UNIQUE (reader_hostgroup),
- UNIQUE (offline_hostgroup),
- UNIQUE (backup_writer_hostgroup))
这些字段含义如下:
write_hostgroup: 默认情况下会将所有流量发送到这个组. 具有 read_only=0 的节点也将分配到这个组;
backup_writer_hostgroup: 如果集群有多个写节点 (read_only=0) 且超过了 max_writers 规定数量, 则会把多出来的写节点放到备用写组里面;
reader_hostgroup: 读取的流量应该发送到该组, 只读节点 (read_only=1) 会被分配到该组;
offline_hostgroup: 当 ProxySQL 监视到某个节点不正常时, 会被放入该组;
active: 是否启用主机组, 当启用时, ProxySQL 将监视主机在各族之间移动;
max_writers: 最大写节点的数量, 超过该值的节点应该被放入 backup_write_hostgroup;
writer_is_also_reader: 一个节点既做写节点也做读节点, 如果该值为 2, 则 backup_writer_hostgroup 的节点做读写点, 但是 writer_hostgroup 不会做读节点;
我们对该表进行如下配置:
- MySQL> select * from mysql_group_replication_hostgroups;
- +------------------+-------------------------+------------------+-------------------+--------+-------------+-----------------------+-------------------------+---------+
- | writer_hostgroup | backup_writer_hostgroup | reader_hostgroup | offline_hostgroup | active | max_writers | writer_is_also_reader | max_transactions_behind | comment |
- +------------------+-------------------------+------------------+-------------------+--------+-------------+-----------------------+-------------------------+---------+
- | 1 | 2 | 3 | 4 | 1 | 1 | 0 | 100 | NULL |
- +------------------+-------------------------+------------------+-------------------+--------+-------------+-----------------------+-------------------------+---------+
(5.3)视图添加
如果 ProxySQL 是与组复制 MGR 一起使用的, 那么还需要在 MGR 集群添加如下视图:
- USE sys;
- DELIMITER $$
- CREATE FUNCTION IFZERO(a INT, b INT)
- RETURNS INT
- DETERMINISTIC
- RETURN IF(a = 0, b, a)$$
- CREATE FUNCTION LOCATE2(needle TEXT(10000), haystack TEXT(10000), offset INT)
- RETURNS INT
- DETERMINISTIC
- RETURN IFZERO(LOCATE(needle, haystack, offset), LENGTH(haystack) + 1)$$
- CREATE FUNCTION GTID_NORMALIZE(g TEXT(10000))
- RETURNS TEXT(10000)
- DETERMINISTIC
- RETURN GTID_SUBTRACT(g, '')$$
- CREATE FUNCTION GTID_COUNT(gtid_set TEXT(10000))
- RETURNS INT
- DETERMINISTIC
- BEGIN
- DECLARE result BIGINT DEFAULT 0;
- DECLARE colon_pos INT;
- DECLARE next_dash_pos INT;
- DECLARE next_colon_pos INT;
- DECLARE next_comma_pos INT;
- SET gtid_set = GTID_NORMALIZE(gtid_set);
- SET colon_pos = LOCATE2(':', gtid_set, 1);
- WHILE colon_pos != LENGTH(gtid_set) + 1 DO
- SET next_dash_pos = LOCATE2('-', gtid_set, colon_pos + 1);
- SET next_colon_pos = LOCATE2(':', gtid_set, colon_pos + 1);
- SET next_comma_pos = LOCATE2(',', gtid_set, colon_pos + 1);
- IF next_dash_pos <next_colon_pos AND next_dash_pos < next_comma_pos THEN
- SET result = result +
- SUBSTR(gtid_set, next_dash_pos + 1,
- LEAST(next_colon_pos, next_comma_pos) - (next_dash_pos + 1)) -
- SUBSTR(gtid_set, colon_pos + 1, next_dash_pos - (colon_pos + 1)) + 1;
- ELSE
- SET result = result + 1;
- END IF;
- SET colon_pos = next_colon_pos;
- END WHILE;
- RETURN result;
- END$$
- CREATE FUNCTION gr_applier_queue_length()
- RETURNS INT
- DETERMINISTIC
- BEGIN
- RETURN (SELECT sys.gtid_count( GTID_SUBTRACT( (SELECT
- Received_transaction_set FROM performance_schema.replication_connection_status
- WHERE Channel_name = 'group_replication_applier' ), (SELECT
- @@global.GTID_EXECUTED) )));
- END$$
- CREATE FUNCTION gr_member_in_primary_partition()
- RETURNS VARCHAR(3)
- DETERMINISTIC
- BEGIN
- RETURN (SELECT IF( MEMBER_STATE='ONLINE' AND ((SELECT COUNT(*) FROM
- performance_schema.replication_group_members WHERE MEMBER_STATE != 'ONLINE')>=
- ((SELECT COUNT(*) FROM performance_schema.replication_group_members)/2) = 0),
- 'YES', 'NO' ) FROM performance_schema.replication_group_members JOIN
- performance_schema.replication_group_member_stats USING(member_id));
- END$$
- CREATE VIEW gr_member_routing_candidate_status AS SELECT
- sys.gr_member_in_primary_partition() as viable_candidate,
- IF( (SELECT (SELECT GROUP_CONCAT(variable_value) FROM
- performance_schema.global_variables WHERE variable_name IN ('read_only',
- 'super_read_only')) != 'OFF,OFF'), 'YES', 'NO') as read_only,
- sys.gr_applier_queue_length() as transactions_behind, Count_Transactions_in_queue as 'transactions_to_cert' from performance_schema.replication_group_member_stats;$$
- DELIMITER ;
然后授权给监控用户, 这里需要特别注意, 我的监控用户在 5.5.1 步才创建, 因此这一步需要放到 5.5.1 后执行:
grant select on sys.* to monitoring_user;
(5.4)MySQL 服务器添加
mysql_server 表是用来存储 ProxySQL 路由转换的 MySQL 节点的信息.
- MySQL> insert into mysql_servers (hostgroup_id, hostname, port) values(1,'192.168.10.11',3306);
- MySQL> insert into mysql_servers (hostgroup_id, hostname, port) values(1,'192.168.10.12',3306);
- MySQL> insert into mysql_servers (hostgroup_id, hostname, port) values(1,'192.168.10.13',3306);
- MySQL> select * from mysql_servers;
- +--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
- | hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
- +--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
- | 1 | 192.168.10.11 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
- | 1 | 192.168.10.12 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
- | 1 | 192.168.10.13 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
- +--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
然后执行下面的命令生效:
- LOAD MySQL SERVERS TO RUNTIME;
- SAVE MySQL SERVERS TO DISK;
(5.5)监控配置及检查
这里配置监控信息, 用来监控 ProxySQL 与后端的 MySQL 通信是否正常.
(5.5.1)监控用户配置
在 ProxySQL 的变量表里面设定监控用户密码, 用于 ProxySQL 监控后端 MySQL 服务器的用户信息
- MySQL> UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_username';
- Query OK, 1 row affected (0.00 sec)
- MySQL> UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_password';
- Query OK, 1 row affected (0.00 sec)
- MySQL> select variable_name,variable_value from global_variables where variable_name in ('mysql-monitor_username','mysql-monitor_password');
- +------------------------+----------------+
- | variable_name | variable_value |
- +------------------------+----------------+
- | MySQL-monitor_password | monitor |
- | MySQL-monitor_username | monitor |
- +------------------------+----------------+
需要注意, 既然使用该用户监控后台 MySQL 数据库, 那么后台 MySQL 数据库也需要创建该用户并授权, monitor 用户需要有 usage 权限去连接, ping 和检查 read_only 信息, 如果要检测复制延迟, 还需要具有 replication client 权限. 特别注意, 不能使用 mysql_users 里面的用户来做监控用户.
-- 在 MySQL 服务器上创建监控用户
-- 需要注意, 这里 MySQL 使用的是 MGR, 所以只需要在一台节点创建用户即可, 其它节点会自动同步用户信息
- create user monitor@'%' identified by 'monitor';
- grant usage,replication client on *.* to monitor@'%';
- flush privileges;
注意: 因为 ProxySQL + 组复制添加了新的视图, 见 "5.3 视图添加", 因此还需授权:
grant select on sys.* to monitor;
(5.5.2)配置监控间隔
这里把连接, ping,read_only 监控间隔改为 2s, 也可以根据需要改成其它, 也可以不做修改
- MySQL> update global_variables set variable_value='2000'
- -> where variable_name in('mysql-monitor_connect_interval','mysql-monitor_ping_interval','mysql-monitor_read_only_interval');
- Query OK, 3 rows affected (0.00 sec)
- MySQL> select * from global_variables where variable_name like 'mysql-monitor%';
- +--------------------------------------------------------------+----------------+
- | variable_name | variable_value |
- +--------------------------------------------------------------+----------------+
- | MySQL-monitor_enabled | true |
- | MySQL-monitor_connect_timeout | 600 |
- | MySQL-monitor_ping_max_failures | 3 |
- | MySQL-monitor_ping_timeout | 1000 |
- | MySQL-monitor_read_only_max_timeout_count | 3 |
- | MySQL-monitor_replication_lag_interval | 10000 |
- | MySQL-monitor_replication_lag_timeout | 1000 |
- | MySQL-monitor_groupreplication_healthcheck_interval | 5000 |
- | MySQL-monitor_groupreplication_healthcheck_timeout | 800 |
- | MySQL-monitor_groupreplication_healthcheck_max_timeout_count | 3 |
- | MySQL-monitor_groupreplication_max_transactions_behind_count | 3 |
- | MySQL-monitor_galera_healthcheck_interval | 5000 |
- | MySQL-monitor_galera_healthcheck_timeout | 800 |
- | MySQL-monitor_galera_healthcheck_max_timeout_count | 3 |
- | MySQL-monitor_replication_lag_use_percona_heartbeat | |
- | MySQL-monitor_query_interval | 60000 |
- | MySQL-monitor_query_timeout | 100 |
- | MySQL-monitor_slave_lag_when_null | 60 |
- | MySQL-monitor_threads_min | 8 |
- | MySQL-monitor_threads_max | 128 |
- | MySQL-monitor_threads_queue_maxsize | 128 |
- | MySQL-monitor_wait_timeout | true |
- | MySQL-monitor_writer_is_also_reader | true |
- | MySQL-monitor_username | monitor |
- | MySQL-monitor_password | monitor |
- | MySQL-monitor_history | 600000 |
- | MySQL-monitor_connect_interval | 2000 |
- | MySQL-monitor_ping_interval | 2000 |
- | MySQL-monitor_read_only_interval | 2000 |
- | MySQL-monitor_read_only_timeout | 500 |
- +--------------------------------------------------------------+----------------+
- 30 rows in set (0.01 sec)
在修改完变量之后, 一定要加载到内存中生效以及永久保存到磁盘中:
- LOAD MySQL VARIABLES TO RUNTIME;
- SAVE MySQL VARIABLES TO DISK;
(5.5.3)检查监控信息是否存在异常
监控配置完成后, 我们需要检查 ProxySQL 与后端 MySQL 通信是否有异常, monitor 数据库中的表用于存储监视信息, 需要注意的是, 这些表并非都已经被使用.
- MySQL> show tables from monitor;
- +--------------------------------------+
- | tables |
- +--------------------------------------+
- | mysql_server_aws_aurora_check_status |
- | mysql_server_aws_aurora_failovers |
- | mysql_server_aws_aurora_log |
- | mysql_server_connect_log |
- | mysql_server_galera_log |
- | mysql_server_group_replication_log |
- | mysql_server_ping_log |
- | mysql_server_read_only_log |
- | mysql_server_replication_lag_log |
- +--------------------------------------+
查看 ProxySQL 与后台服务器连接是否正常:
- MySQL> select * from monitor.mysql_server_connect_log order by time_start_us desc limit 10;
- +---------------+------+------------------+-------------------------+---------------+
- | hostname | port | time_start_us | connect_success_time_us | connect_error |
- +---------------+------+------------------+-------------------------+---------------+
- | 192.168.10.13 | 3306 | 1596263409501584 | 2191 | NULL |
- | 192.168.10.11 | 3306 | 1596263409480641 | 1911 | NULL |
- | 192.168.10.12 | 3306 | 1596263409459524 | 3671 | NULL |
- | 192.168.10.13 | 3306 | 1596263407504677 | 1451 | NULL |
- | 192.168.10.11 | 3306 | 1596263407481776 | 1398 | NULL |
- | 192.168.10.12 | 3306 | 1596263407458859 | 1378 | NULL |
- | 192.168.10.12 | 3306 | 1596263405490389 | 3480 | NULL |
- | 192.168.10.13 | 3306 | 1596263405474367 | 2804 | NULL |
- | 192.168.10.11 | 3306 | 1596263405458569 | 1612 | NULL |
- | 192.168.10.13 | 3306 | 1596263403497485 | 2132 | NULL |
- +---------------+------+------------------+-------------------------+---------------+
- 10 rows in set (0.00 sec)
查看组复制是否正常, 检查节点是否只读和交易滞后时间:
- MySQL> select * from mysql_server_group_replication_log order by time_start_us desc limit 10;
- +---------------+------+------------------+-----------------+------------------+-----------+---------------------+-------+
- | hostname | port | time_start_us | success_time_us | viable_candidate | read_only | transactions_behind | error |
- +---------------+------+------------------+-----------------+------------------+-----------+---------------------+-------+
- | 192.168.10.13 | 3306 | 1596263494597039 | 5671 | YES | NO | 0 | NULL |
- | 192.168.10.12 | 3306 | 1596263494596052 | 3231 | YES | NO | 0 | NULL |
- | 192.168.10.11 | 3306 | 1596263494595139 | 3245 | YES | NO | 0 | NULL |
- | 192.168.10.13 | 3306 | 1596263489596357 | 3027 | YES | NO | 0 | NULL |
- | 192.168.10.12 | 3306 | 1596263489595491 | 3306 | YES | NO | 0 | NULL |
- | 192.168.10.11 | 3306 | 1596263489594645 | 3110 | YES | NO | 0 | NULL |
- | 192.168.10.13 | 3306 | 1596263484595710 | 3680 | YES | NO | 0 | NULL |
- | 192.168.10.12 | 3306 | 1596263484594839 | 3618 | YES | NO | 0 | NULL |
- | 192.168.10.11 | 3306 | 1596263484594114 | 3214 | YES | NO | 0 | NULL |
- | 192.168.10.13 | 3306 | 1596263479595072 | 1887 | YES | NO | 0 | NULL |
- +---------------+------+------------------+-----------------+------------------+-----------+---------------------+-------+
- 10 rows in set (0.01 sec)
查看 ProxySQL ping 后端 MySQL 服务器是否正常:
- MySQL> select * from mysql_server_ping_log order by time_start_us desc limit 10;
- +---------------+------+------------------+----------------------+------------+
- | hostname | port | time_start_us | ping_success_time_us | ping_error |
- +---------------+------+------------------+----------------------+------------+
- | 192.168.10.12 | 3306 | 1596263541810631 | 496 | NULL |
- | 192.168.10.11 | 3306 | 1596263541786903 | 612 | NULL |
- | 192.168.10.13 | 3306 | 1596263541762973 | 749 | NULL |
- | 192.168.10.12 | 3306 | 1596263539796079 | 565 | NULL |
- | 192.168.10.13 | 3306 | 1596263539779040 | 403 | NULL |
- | 192.168.10.11 | 3306 | 1596263539762769 | 1141 | NULL |
- | 192.168.10.12 | 3306 | 1596263537797512 | 848 | NULL |
- | 192.168.10.11 | 3306 | 1596263537779520 | 845 | NULL |
- | 192.168.10.13 | 3306 | 1596263537761840 | 742 | NULL |
- | 192.168.10.12 | 3306 | 1596263535814945 | 843 | NULL |
- +---------------+------+------------------+----------------------+------------+
- 10 rows in set (0.00 sec)
通过监控信息, 我们可以得出结论, 所有配置都是健康的, 继续下一步.
(5.6)用户配置
(5.6.1)ProxySQL 的双层用户认证机制
如果使用了 ProxySQL 来做中间路由, 那么与我们平时登录数据库有一些区别: 平时我们直接使用数据库的用户密码, 即可访问到数据库, 如果使用了 ProxySQL, 则要先使用账号密码访问到 ProxySQL 的数据库, 然后再由 ProxySQL 进行用户请求的转发, 那么, ProxySQL 中的用户与数据库层的用户有什么关联呢? 很奇怪, 这部分 ProxySQL 居然没在文档里面给出来.
只能自己测试了, 经过个人测试, 发现: 当中间件用户与数据库用户以及密码一致时, 才能正常访问数据库. 测试结果如下:
| MySQL 数据库用户 (mysql.user 表) | ProxySQL 用户（main.mysql_users 表） | 使用 ProxySQL 的 6033 端口访问数据库 |
| usera | usera | 正常访问 |
| userb | 无法登入 proxysql | |
| userc | 可以登入 proxysql，但是无法读写 |
这里是我的测试过程:
在 MySQL 数据库上创建用户: usera 和 userb
- create user `usera`@`%` identified by '123456';
- grant all privileges on *.* to `usera`@`%`;
- create user `userb`@`%` identified by '123456';
- grant all privileges on *.* to `userb`@`%`;
- flush privileges;
在 ProxySQL 上创建用户: usera 和 userc
- insert into mysql_users(username,password,default_hostgroup) values('usera','123456',1);
- insert into mysql_users(username,password,default_hostgroup) values('userc','123456',1);
- load MySQL users to runtime;
- save MySQL users to disk;
登入测试(分为 2 步: 先登入, 再查询):
(1)usera 用户登入无问题, 查询无问题
- [root@proxysql ~]# MySQL -uusera -p123456 -P6033 -h192.168.10.10
- MySQL> select count(*) from lijiamandb.test03;
- +----------+
- | count(*) |
- +----------+
- | 1 |
- +----------+
- 1 row in set (0.01 sec)
(2)userb 无法登入, 提示用户名密码错误
[root@proxysql ~]# MySQL -uuserb -p123456 -P6033 -h192.168.10.10 MySQL: [Warning] Using a password on the command line interface can be insecure. ERROR 1045 (28000): ProxySQL Error: Access denied for user 'userb'@'192.168.10.10' (using password: YES)
(3)userc 可以正常登入, 但是查询的时候提示密码不对
[root@proxysql ~]# MySQL -uuserc -p123456 -P6033 -h192.168.10.10 MySQL> MySQL> select count(*) from lijiamandb.test03; ERROR 1045 (28000): Access denied for user 'userc'@'192.168.10.10' (using password: YES)
用户认证小结: 只有 ProxySQL 中的用户名密码与 MySQL 中的用户名密码相同时, 才能正常访问底层 MySQL 数据库. 因此, 如果要使用 ProxySQL 访问数据库, 需要在 MySQL 和 ProxySQL 中都要创建相同的账号, 并且密码也要保持一致.
(5.6.2)ProxySQL 用户创建
ProxySQL 的用户保存在 mysql_users 表中, 用户创建直接执行 insert 插入即可. 如创建一个用户名为 "lijiaman", 密码为 "123456", 默认用户组为 1 的用户:
insert into mysql_users(username,password,default_hostgroup) values('lijiaman','123456',1);
需要特别注意, 现在该用户只在 Memory 层进行了修改, 没有同步到 RUNTIME 层生效, 也没有保存到磁盘, 需要使用下面的命令来完成操作.
load MySQL users to runtime; save MySQL users to disk;
mysql_users 表最重要的字段为:
username password
default_hostgroup: 默认组. 如果此用户发送的查询没有匹配的规则, 则它生成的流量将发送到指定的主机组
transaction_persistent: 如果为与 MySQL 客户端连接到 ProxySQL 的用户设置了此选项, 则在主机组内启动的事务将保留在该主机组内, 而不管其他任何规则. 例如, 一个事务中存在读与写操作, 如果不指定该选项, 可能会把读与写请求分发到不同的主机上, 造成数据不一致.
(六)故障转移 (failover) 测试
在上一节, 我们已经配置了:
组: 写组, 备用写组, 读组, 离线组. 并且读组最多只有 1 台 server;
MySQL Server: 配置了 3 台 Server, 并且将其放入到了写组中;
监控信息:
用户信息
此时, ProxySQL 已经具备故障转移的能力了, 我们进行测试一下.
STEP1: 现在的配置如下, 192.168.10.13 主机是写节点, 其它 2 个节点是备用写节点:
-- mysql_serve 在 memory 层 r 的配置信息
MySQL> select * from mysql_servers; +--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment | +--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | 1 | 192.168.10.11 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 1 | 192.168.10.12 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 1 | 192.168.10.13 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | +--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ 3 rows in set (0.00 sec)
-- 加载到 RUNTIME 后, 由于组定义中最多只有 1 个写节点, 其余的主节点移动到备用写组里面
MySQL> select * from runtime_mysql_servers; +--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment | +--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | 2 | 192.168.10.11 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 1 | 192.168.10.13 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 2 | 192.168.10.12 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | +--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ 3 rows in set (0.01 sec)
使用 ProxySQL 来访问 MyQSL 集群, 发现可以支持读写
-- 使用 ProxySQL 6033 端口访问 MySQL 数据库
[root@proxysql ~]# MySQL -uusera -p123456 -P6033 -h192.168.10.10 MySQL> use testdb
-- 通过主机名, 额可以看到, 我们访问到的是写节点
MySQL> select @@hostname; +------------+ | @@hostname | +------------+ | mgr-node3 | +------------+ 1 row in set (0.00 sec)
-- 可以这次插入, 查询数据
MySQL> insert into test01 values(1,'a'); Query OK, 1 row affected (0.00 sec) MySQL> select * from test01; +----+------+ | id | name | +----+------+ | 1 | a | +----+------+ 1 row in set (0.00 sec)
STEP2: 关闭写节点
# 直接关闭主机 [root@mgr-node3 ~]# reboot Connection closed by foreign host. Disconnected from remote host(mgr-node3) at 19:00:31. Type `help' to learn how to use Xshell prompt. [c:\~]$
-- 需要注意的是, 以前连接在主节点上的会话会断开, 不会转移到新的主节点, 很正常, Oracle 也不会
MySQL> select * from test01; ERROR 2013 (HY000): Lost connection to MySQL server during query
STEP3: 查看是否会有备用写节点转为写节点, 可以看到 192.168.10.12 服务器已经转为写节点, 而已经关闭的 192.168.10.13 服务器已经进入离线组.
MySQL> select * from runtime_mysql_servers; +--------------+---------------+------+-----------+---------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment | +--------------+---------------+------+-----------+---------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | 2 | 192.168.10.11 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 1 | 192.168.10.12 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 4 | 192.168.10.13 | 3306 | 0 | SHUNNED | 1 | 0 | 1000 | 0 | 0 | 0 | | +--------------+---------------+------+-----------+---------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ 3 rows in set (0.00 sec)
STEP4: 再次使用 ProxySQL 来访问 MyQSL 集群, 发现可以支持读写, 业务不会因主节点的改变而受影响.
-- 使用 ProxySQL 6033 端口访问 MySQL 数据库
[root@proxysql ~]# MySQL -uusera -p123456 -P6033 -h192.168.10.10 MySQL> use testdb
-- 通过主机名, 额可以看到, 我们访问到的是新的写节点
MySQL> select @@hostname; +------------+ | @@hostname | +------------+ | mgr-node2 | +------------+ 1 row in set (0.00 sec)
-- 可以这次插入, 查询数据
MySQL> insert into test01 values(2,'b'); Query OK, 1 row affected (0.00 sec)
通过上面的测试, 可以看到, MGR 结合 ProxySQL 已经可以实现业务的自动故障转移.
接下来, 我们开始研究 ProxySQL 的读写分离功能.
来源: https://www.cnblogs.com/lijiaman/p/13416313.html