使用ProxySQL实现MySQL Group Replication的故障转移、读写分离（一）

最新推荐文章于 2024-08-24 03:48:19 发布

johnricgtsd8br

最新推荐文章于 2024-08-24 03:48:19 发布

阅读量834

点赞数

原文链接：https://www.cnblogs.com/lijiaman/p/13416313.html

版权

使用ProxySQL实现MySQL Group Replication的故障转移、读写分离（一）

image

导读：

在之前，我们搭建了MySQL组复制集群环境，MySQL组复制集群环境解决了MySQL集群内部的自动故障转移，但是，组复制并没有解决外部业务的故障转移。举个例子，在A、B、C 3台机器上搭建了组复制环境，且运行在单主模式下，这里假设A为主节点，应用程序连接A写数据，如果A节点发生宕机，主节点切换到B机器上，此时，应用程序是不会自动连接到B服务器上的，需要人工进行切换。

Snipaste_2020-07-29_22-00-09

在这篇文章中，我们要介绍的ProxySQL就能够解决上面的问题，ProxySQL能够实现业务层面故障转移、读写分离功能，当然ProxySQL不仅仅只有这两项功能，还有更多的其它功能。其架构如下：

Snipaste_2020-07-29_22-10-45

我们不妨来了解一下。

（一）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层可以把配置信息永久保存在磁盘上。

Snipaste_2020-07-29_21-31-12

各层之间数据如何同步呢？我们可以看上图的箭头部分，通过load/save命令来实现同步。具体命令如下：

复制代码
[1] LOAD FROM MEMORY/LOAD TO RUNTIME
将配置项从内存数据库加载到运行时数据结构

[2] SAVE TO MEMORY/SAVE FROM RUNTIME
将配置项从运行时保存到内存数据库中

[3] LOAD TO MEMORY/LOAD FROM DISK
将持久性配置项目从磁盘数据库加载到内存数据库

[4] SAVE FROM MEMORY/SAVE TO DISK
将配置项从内存数据库保存到磁盘数据库

[5] LOAD 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

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)

LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;

https://github.com/zxc222000
https://github.com/users/zxc222000/projects/2
https://github.com/users/zxc222000/projects/2/
https://github.com/users/zxc222000/projects/2?asd=
https://github.com/users/zxc222000/projects/2?as
https://github.com/users/zxc222000/projects/2?a
https://github.com/users/zxc222000/projects/2?
(5.5.3)检查监控信息是否存在异常

监控配置完成后，我们需要检查ProxySQL与后端MySQL通信是否有异常，monitor数据库中的表用于存储监视信息，需要注意的是，这些表并非都已经被使用。

查看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居然没在文档里面给出来。

Snipaste_2020-08-01_18-00-42

只能自己测试了，经过个人测试，发现：当中间件用户与数据库用户以及密码一致时，才能正常访问数据库。测试结果如下：

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的读写分离功能。