ProxySQL+PXC实现读写分离
搭建PXC环境参考，
https://www.cnblogs.com/nanxiang/p/13948762.html
192.168.150.201 pxc1
192.168.150.202 pxc2
192.168.150.203 pxc3
192.168.150.250 ProxySQL
1、
使用ProxySQL实现对PXC的读写分离是官方推荐的一种方案，
https://www.percona.com/doc/percona-xtradb-cluster/5.7/howtos/proxysql.html
ProxySQL是高性能的SQL代理。ProxySQL作为监视程序监视的守护程序运行。该进程监视守护程序，并在发生崩溃的情况下重新启动它，以最大程度地减少停机时间。
守护程序接受来自MySQL客户端的传入流量，并将其转发到后端MySQL服务器。
代理被设计为无需重新启动即可连续运行。大多数配置可以在运行时使用类似于SQL语句的查询来完成。其中包括运行时参数，服务器分组以及与流量相关的设置。
可以从Percona软件存储库中获得两个版本的ProxySQL。ProxySQL v1本身不支持Percona XtraDB群集，并且需要自定义bash脚本以使用ProxySQL调度程序跟踪Percona XtraDB Cluster节点的状态。
ProxySQL v2本机支持Percona XtraDB群集。在此版本中，该 proxysql-admin工具不需要自定义脚本来跟踪Percona XtraDB群集状态。
我们直接安装ProxySQL v2版本。
2、
搭建步骤
新开一台虚拟机，192.168.150.250
由于ProxySQL需要mysql客户端去登录，需要先安装mysql客户端，官方推荐的是安装【Percona-XtraDB-Cluster-client-57】，因为网络问题，我安装的是mysql 5.7客户端。
yum-config-manager --enable mysql57-community yum-config-manager --disable mysql80-community yum install mysql-community-client -y
3、
先安装ProxySQL,
下载地址：
https://www.percona.com/downloads/proxysql2/
我选择使用yum安装，
yum install https://www.percona.com/downloads/proxysql2/proxysql2-2.0.14/binary/redhat/7/x86_64/proxysql2-2.0.14-1.1.el7.x86_64.rpm -y
service proxysql start [root@pxc_250 ~]# ps -ef|grep proxy root 6950 1 0 13:52 ? 00:00:00 /usr/sbin/gssproxy -D gdm 7914 7624 0 13:52 ? 00:00:00 /usr/libexec/gsd-screensaver-proxy proxysql 9035 1 0 14:38 ? 00:00:00 /usr/bin/proxysql --idle-threads -c /etc/proxysql.cnf proxysql 9036 9035 0 14:38 ? 00:00:00 /usr/bin/proxysql --idle-threads -c /etc/proxysql.cnf root 9089 8420 0 14:38 pts/0 00:00:00 grep --color=auto proxy
4、
配置参考：
https://www.percona.com/doc/percona-xtradb-cluster/5.7/howtos/proxysql.html
登录proxysql
mysql -u admin -padmin -h 127.0.0.1 -P 6032
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 | +-----+---------------+-------------------------------------+ 5 rows in set (0.00 sec)
mysql> show tables; +----------------------------------------------------+ | tables | +----------------------------------------------------+ | global_variables | | mysql_aws_aurora_hostgroups | | mysql_collations | | mysql_firewall_whitelist_rules | | mysql_firewall_whitelist_sqli_fingerprints | | mysql_firewall_whitelist_users | | mysql_galera_hostgroups | | mysql_group_replication_hostgroups | | mysql_query_rules | | mysql_query_rules_fast_routing | | mysql_replication_hostgroups | | mysql_servers | | mysql_users | | proxysql_servers | | restapi_routes | | runtime_checksums_values | | runtime_global_variables | | runtime_mysql_aws_aurora_hostgroups | | runtime_mysql_firewall_whitelist_rules | | runtime_mysql_firewall_whitelist_sqli_fingerprints | | runtime_mysql_firewall_whitelist_users | | runtime_mysql_galera_hostgroups | | runtime_mysql_group_replication_hostgroups | | runtime_mysql_query_rules | | runtime_mysql_query_rules_fast_routing | | runtime_mysql_replication_hostgroups | | runtime_mysql_servers | | runtime_mysql_users | | runtime_proxysql_servers | | runtime_restapi_routes | | runtime_scheduler | | scheduler | +----------------------------------------------------+ 32 rows in set (0.00 sec)
注意：
ProxySQL具有配置可以驻留的3个区域：
- 内存（您当前的工作地点）
- RUNTIME（生产设置）
- DISK（持久配置，保存在SQLITE数据库中）
更改参数时，可以在“存储器”区域中对其进行更改。这是设计使然的，可让您在进行生产之前测试更改（运行时）或将其保存到磁盘。
将群集节点添加到ProxySQL
要在ProxySQL中配置后端Percona XtraDB Cluster节点，请在mysql_servers表中插入相应的记录。
mysql> INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (0,'192.168.150.201',3306); Query OK, 1 row affected (0.01 sec) mysql> INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (0,'192.168.150.202',3306); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (0,'192.168.150.203',3306); Query OK, 1 row affected (0.00 sec)
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 | +--------------+-----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | 0 | 192.168.150.201 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 0 | 192.168.150.202 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 0 | 192.168.150.203 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | +--------------+-----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ 3 rows in set (0.00 sec)
创建ProxySQL监控用户，需要先在PXC上创建，再配置到ProxySQL中。
PXC创建：
mysql> CREATE USER 'proxysql'@'%' IDENTIFIED BY '123456'; Query OK, 0 rows affected (0.04 sec) mysql> GRANT USAGE ON *.* TO 'proxysql'@'%'; Query OK, 0 rows affected (0.13 sec)
ProxySQL中修改环境变量，ProxySQL会使用proxysql用户连接Pxc集群，检测集群状态
UPDATE global_variables SET variable_value='proxysql' WHERE variable_name='mysql-monitor_username'; UPDATE global_variables SET variable_value='123456' WHERE variable_name='mysql-monitor_password';
加载环境变量到RUNTIME状态，持久化环境变量到磁盘中。
LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;
检测连接日志和ping日志
mysql> SELECT * FROM monitor.mysql_server_connect_log ORDER BY time_start_us DESC LIMIT 6; +-----------------+------+------------------+-------------------------+---------------+ | hostname | port | time_start_us | connect_success_time_us | connect_error | +-----------------+------+------------------+-------------------------+---------------+ | 192.168.150.203 | 3306 | 1605164324106429 | 1408 | NULL | | 192.168.150.202 | 3306 | 1605164323643886 | 1439 | NULL | | 192.168.150.201 | 3306 | 1605164323181438 | 1315 | NULL | | 192.168.150.203 | 3306 | 1605164264180237 | 1263 | NULL | | 192.168.150.201 | 3306 | 1605164263681094 | 1348 | NULL | | 192.168.150.202 | 3306 | 1605164263181069 | 1538 | NULL | +-----------------+------+------------------+-------------------------+---------------+ 6 rows in set (0.00 sec)
mysql> SELECT * FROM monitor.mysql_server_ping_log ORDER BY time_start_us DESC LIMIT 6; +-----------------+------+------------------+----------------------+------------+ | hostname | port | time_start_us | ping_success_time_us | ping_error | +-----------------+------+------------------+----------------------+------------+ | 192.168.150.202 | 3306 | 1605164343613683 | 382 | NULL | | 192.168.150.201 | 3306 | 1605164343496221 | 536 | NULL | | 192.168.150.203 | 3306 | 1605164343379250 | 459 | NULL | | 192.168.150.202 | 3306 | 1605164333514780 | 639 | NULL | | 192.168.150.203 | 3306 | 1605164333446700 | 563 | NULL | | 192.168.150.201 | 3306 | 1605164333378854 | 755 | NULL | +-----------------+------+------------------+----------------------+------------+ 6 rows in set (0.00 sec)
加载MYSQL SERVERS到RUNTIME状态，持久化MYSQL SERVERS到磁盘中。
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;
创建ProxySQL客户端用户
INSERT INTO mysql_users (username,password) VALUES ('sbuser','sbpass'); LOAD MYSQL USERS TO RUNTIME; SAVE MYSQL USERS TO DISK;
登录PXC集群，创建sbuser用户
CREATE USER 'sbuser'@'%' IDENTIFIED BY 'sbpass'; GRANT ALL ON *.* TO 'sbuser'@'%';
测试读写分离，查询命令可以分发到各个节点上。
[root@pxc_250 ~]# mysql -u sbuser -psbpass -h 127.0.0.1 -P 6033 -e "select @@server_id"; mysql: [Warning] Using a password on the command line interface can be insecure. +-------------+ | @@server_id | +-------------+ | 201 | +-------------+ [root@pxc_250 ~]# mysql -u sbuser -psbpass -h 127.0.0.1 -P 6033 -e "select @@server_id"; mysql: [Warning] Using a password on the command line interface can be insecure. +-------------+ | @@server_id | +-------------+ | 202 | +-------------+ [root@pxc_250 ~]# mysql -u sbuser -psbpass -h 127.0.0.1 -P 6033 -e "select @@server_id"; mysql: [Warning] Using a password on the command line interface can be insecure. +-------------+ | @@server_id | +-------------+ | 201 | +-------------+ [root@pxc_250 ~]# mysql -u sbuser -psbpass -h 127.0.0.1 -P 6033 -e "select @@server_id"; mysql: [Warning] Using a password on the command line interface can be insecure. +-------------+ | @@server_id | +-------------+ | 203 | +-------------+
创建tt表，测试写入命令。
mysql> create table tt(id int primary key auto_increment); Query OK, 0 rows affected (0.04 sec)
[root@pxc_250 ~]# mysql -u sbuser -psbpass -h 127.0.0.1 -P 6033 -e "use ceshi;begin;insert into tt select null;select @@server_id;commit"; mysql: [Warning] Using a password on the command line interface can be insecure. +-------------+ | @@server_id | +-------------+ | 203 | +-------------+ [root@pxc_250 ~]# mysql -u sbuser -psbpass -h 127.0.0.1 -P 6033 -e "use ceshi;begin;insert into tt select null;select @@server_id;commit"; mysql: [Warning] Using a password on the command line interface can be insecure. +-------------+ | @@server_id | +-------------+ | 203 | +-------------+ [root@pxc_250 ~]# mysql -u sbuser -psbpass -h 127.0.0.1 -P 6033 -e "use ceshi;begin;insert into tt select null;select @@server_id;commit"; mysql: [Warning] Using a password on the command line interface can be insecure. +-------------+ | @@server_id | +-------------+ | 202 | +-------------+ [root@pxc_250 ~]# mysql -u sbuser -psbpass -h 127.0.0.1 -P 6033 -e "use ceshi;begin;insert into tt select null;select @@server_id;commit"; mysql: [Warning] Using a password on the command line interface can be insecure. +-------------+ | @@server_id | +-------------+ | 203 | +-------------+ [root@pxc_250 ~]# mysql -u sbuser -psbpass -h 127.0.0.1 -P 6033 -e "use ceshi;begin;insert into tt select null;select @@server_id;commit"; mysql: [Warning] Using a password on the command line interface can be insecure. +-------------+ | @@server_id | +-------------+ | 202 | +-------------+ [root@pxc_250 ~]# mysql -u sbuser -psbpass -h 127.0.0.1 -P 6033 -e "use ceshi;begin;insert into tt select null;select @@server_id;commit"; mysql: [Warning] Using a password on the command line interface can be insecure. +-------------+ | @@server_id | +-------------+ | 202 | +-------------+ [root@pxc_250 ~]# mysql -u sbuser -psbpass -h 127.0.0.1 -P 6033 -e "use ceshi;begin;insert into tt select null;select @@server_id;commit"; mysql: [Warning] Using a password on the command line interface can be insecure. +-------------+ | @@server_id | +-------------+ | 201 | +-------------+
模拟203节点宕掉，KILL掉MYSQL进程。203状态已经是SHUNNED
[root@pxc_250 ~]# mysql -u admin -padmin -h 127.0.0.1 -P 6032 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 | +--------------+-----------------+------+-----------+---------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | 0 | 192.168.150.201 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 0 | 192.168.150.203 | 3306 | 0 | SHUNNED | 1 | 0 | 1000 | 0 | 0 | 0 | | | 0 | 192.168.150.202 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | +--------------+-----------------+------+-----------+---------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ 3 rows in set (0.01 sec)
再测试查询，只能发送到201/202节点上
[root@pxc_250 ~]# mysql -u sbuser -psbpass -h 127.0.0.1 -P 6033 -e "select @@server_id"; mysql: [Warning] Using a password on the command line interface can be insecure. +-------------+ | @@server_id | +-------------+ | 201 | +-------------+ [root@pxc_250 ~]# mysql -u sbuser -psbpass -h 127.0.0.1 -P 6033 -e "select @@server_id"; mysql: [Warning] Using a password on the command line interface can be insecure. +-------------+ | @@server_id | +-------------+ | 202 | +-------------+ [root@pxc_250 ~]# mysql -u sbuser -psbpass -h 127.0.0.1 -P 6033 -e "select @@server_id"; mysql: [Warning] Using a password on the command line interface can be insecure. +-------------+ | @@server_id | +-------------+ | 202 | +-------------+ [root@pxc_250 ~]# mysql -u sbuser -psbpass -h 127.0.0.1 -P 6033 -e "select @@server_id"; mysql: [Warning] Using a password on the command line interface can be insecure. +-------------+ | @@server_id | +-------------+ | 201 | +-------------+ [root@pxc_250 ~]# mysql -u sbuser -psbpass -h 127.0.0.1 -P 6033 -e "select @@server_id"; mysql: [Warning] Using a password on the command line interface can be insecure. +-------------+ | @@server_id | +-------------+ | 202 | +-------------+ [root@pxc_250 ~]# mysql -u sbuser -psbpass -h 127.0.0.1 -P 6033 -e "select @@server_id"; mysql: [Warning] Using a password on the command line interface can be insecure. +-------------+ | @@server_id | +-------------+ | 202 | +-------------+
