Percona的proxysql-admin负载pxc集群
percona的proxysql比官方的更方便，一个proxysql-admin就可以完成配置，也不用反复load
https://www.percona.com/doc/percona-xtradb-cluster/5.7/howtos/proxysql-v2.html#adduser
安装
下载地址：https://www.percona.com/downloads/proxysql2/
wget https://downloads.percona.com/downloads/proxysql2/proxysql2-2.2.0/binary/redhat/7/x86_64/proxysql2-2.2.0-1.1.el7.x86_64.rpm
yum -y localinstall proxysql2-2.2.0-1.1.el7.x86_64.rpm
#记得需要mysql-community-client
在配置文件/etc/proxysql.cnf中
admin_credentials中包含账号密码，默认admin:admin
建议在 ProxySQL 配置文件 ( /etc/proxysql-admin.cnf) 中提供连接和身份验证信息。不要在命令行上指定此信息。
默认配置解析
export PROXYSQL_DATADIR='/var/lib/proxysql'
# --------------------------------
# encrypted login credentials file options
#
#export LOGIN_FILE="/path/to/loginfile"
#export LOGIN_PASSWORD_FILE="/path/to/loginfile/password"
# --------------------------------
# proxysql admin interface credentials.
# proxysql管理端配置凭据
export PROXYSQL_USERNAME="admin"
export PROXYSQL_PASSWORD="admin"
export PROXYSQL_HOSTNAME='localhost'
export PROXYSQL_PORT='6032'
# --------------------------------
# PXC admin credentials for connecting to pxc-cluster-node.
# 用于连接到 pxc-cluster-node 的 PXC 管理员凭据
#我们需要修改这个
export CLUSTER_USERNAME='admin'
export CLUSTER_PASSWORD='admin'
export CLUSTER_HOSTNAME='localhost'
export CLUSTER_PORT='3306'
# --------------------------------
# proxysql monitoring user. proxysql admin script will create
# this user in pxc to monitor pxc-nodes.
# proxysql 监控用户。proxysql 管理脚本将在 pxc 中创建此用户以监视 pxc 节点。
export MONITOR_USERNAME='monitor'
export MONITOR_PASSWORD='monitor'
# --------------------------------
# Application user to connect to pxc-node through proxysql
#
export CLUSTER_APP_USERNAME='proxysql_user'
export CLUSTER_APP_PASSWORD='passw0rd'
# --------------------------------
# ProxySQL hostgroup IDs
# ProxySQL主机组ID
export WRITER_HOSTGROUP_ID='10'
export READER_HOSTGROUP_ID='11'
export BACKUP_WRITER_HOSTGROUP_ID='12'
export OFFLINE_HOSTGROUP_ID='13'
# --------------------------------
# ProxySQL read/write configuration mode.
# ProxySQL读/写配置模式
export MODE="singlewrite"
# --------------------------------
# max_connections default (used only when INSERTing a new mysql_servers entry)
# max_connections 默认值（仅在插入新的 mysql_servers 条目时使用）
export MAX_CONNECTIONS="1000"
# --------------------------------
# Determines the maximum number of writesets a node can have queued
# before the node is SHUNNED to avoid stale reads.
# 确定一个节点可以排队的最大写入集数
export MAX_TRANSACTIONS_BEHIND=100
# --------------------------------
# Connections to the backend servers (from ProxySQL) will use SSL
# 到后端服务器的连接（来自 ProxySQL）将使用 SSL
export USE_SSL="no"
# --------------------------------
# Determines if a node should be added to the reader hostgroup if it has
# been promoted to the writer hostgroup.
# If set to 'yes', then all writers (including backup-writers) are added to
# the read hostgroup.
# If set to 'no', then none of the writers (including backup-writers) are added.
# If set to 'backup', then only the backup-writers will be added to
# the read hostgroup.
#
export WRITERS_ARE_READERS="backup"
配置
将/etc/proxysql-admin.cnf中的相关设置
export CLUSTER_USERNAME='admin'
export CLUSTER_PASSWORD='admin'
export CLUSTER_HOSTNAME='localhost'
export CLUSTER_PORT='3306'
修改为自己的账号，proxysql-admin在这上装的更方便
systemctl start proxysql
#直接开启后，只要pxc集群的账号诶之没问题，将会自动把所有集群都加入进来，并创建proxysql_user、nonitor等proxysql需要的用户
[root@proxysql ~]# proxysql-admin --config-file=/etc/proxysql-admin.cnf --enable
This script will assist with configuring ProxySQL for use with
Percona XtraDB Cluster (currently only PXC in combination
with ProxySQL is supported)
ProxySQL read/write configuration mode is singlewrite
Configuring the ProxySQL monitoring user.
ProxySQL monitor user name as per command line/config-file is monitor
User 'monitor'@'192.%' has been added with USAGE privileges
Configuring the Percona XtraDB Cluster application user to connect through ProxySQL
Percona XtraDB Cluster application user name as per command line/config-file is proxysql_user
Percona XtraDB Cluster application user 'proxysql_user'@'192.%' has been added with ALL privileges, this user is created for testing purposes
Adding the Percona XtraDB Cluster server nodes to ProxySQL
Write node info
+-----------------+--------------+------+--------+
| hostname | hostgroup_id | port | weight |
+-----------------+--------------+------+--------+
| 192.168.190.128 | 10 | 3306 | 1000 |
+-----------------+--------------+------+--------+
ProxySQL configuration completed!
ProxySQL has been successfully configured to use with Percona XtraDB Cluster
You can use the following login credentials to connect your application through ProxySQL
mysql --user=proxysql_user -p --host=localhost --port=6033 --protocol=tcp
#这里的配置由/etc/proxysql-admin.cnf中的，将会在pxc节点创建用户
export CLUSTER_APP_USERNAME='proxysql_user'
export CLUSTER_APP_PASSWORD='passw0rd'
决定
##登录
mysql --user=proxysql_user --password=passw0rd --host=127.0.0.1 --port=6033 --protocol=tcp
#可以移除配置重新配置，当--disable后，相关配置也将从内存中移除，比活动去匹配方便
# proxysql-admin --config-file=/etc/proxysql-admin.cnf --diable
pxc节点上查看
mysql> select user,host from mysql.user where authentication_string!='' and user not in ('admin','mysql.sys');
+---------------+-----------+
| user | host |
+---------------+-----------+
| root | localhost |
| mysql.session | localhost |
| sstuser | localhost |
| sstuser | % |
| monitor | 192.% |
| proxysql_user | 192.% |
| root | % |
+---------------+-----------+
7 rows in set (0.00 sec)
#增加了monitor和proxysql_user，这是自动创建的
同步用户
将pxc中的用户同步到proxysql的数据库中
它还会从 ProxySQL 数据库中删除不在 Percona XtraDB Cluster 中的 ProxySQL 用户。
[root@proxysql ~]# proxysql-admin --syncusers
Syncing user accounts from PXC to ProxySQL
Note : 'admin' is in proxysql admin user list, this user cannot be added to ProxySQL
-- (For more info, see https://github.com/sysown/proxysql/issues/709)
Adding user to ProxySQL: monitor
Adding user to ProxySQL: root
Adding user to ProxySQL: sstuser
Synced PXC users to the ProxySQL database!
#proxysql中查看
mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='Admin> '
Admin> select hostgroup_id,hostname,port,status from runtime_mysql_servers;
+--------------+-----------------+------+---------+
| hostgroup_id | hostname | port | status |
+--------------+-----------------+------+---------+
| 10 | 192.168.190.128 | 3306 | SHUNNED |
| 11 | 192.168.190.129 | 3306 | ONLINE |
| 11 | 192.168.190.128 | 3306 | ONLINE |
| 12 | 192.168.190.129 | 3306 | ONLINE |
| 12 | 192.168.190.128 | 3306 | ONLINE |
| 10 | 192.168.190.130 | 3306 | ONLINE |
| 10 | 192.168.190.129 | 3306 | SHUNNED |
+--------------+-----------------+------+---------+
7 rows in set (0.01 sec)
Admin> select * from mysql_galera_hostgroups\G
*************************** 1. row ***************************
writer_hostgroup: 10
backup_writer_hostgroup: 12
reader_hostgroup: 11
offline_hostgroup: 13
active: 1
max_writers: 1
writer_is_also_reader: 2
max_transactions_behind: 100
comment: NULL
1 row in set (0.00 sec)
#因为同步了用户，所以直接在proxysql中使用pxc的用户登录
#**查看库**
[root@proxysql ~]# mysql --user=root --password=123456 --host=127.0.0.1 --port=6033 --protocol=tcp
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| percona |
| performance_schema |
| pxc01 |
| sys |
+--------------------+
6 rows in set (0.00 sec)
#没有同步用户也可以添加用户
proxysql-admin --config-file=/etc/proxysql-admin.cnf --adduser
#查看状态
mysql> select hostgroup_id,hostname,port,weight,comment from mysql_servers;
+--------------+-----------------+------+--------+---------+
| hostgroup_id | hostname | port | weight | comment |
+--------------+-----------------+------+--------+---------+
| 10 | 192.168.190.128 | 3306 | 1000 | |
| 12 | 192.168.190.129 | 3306 | 1000 | |
| 12 | 192.168.190.130 | 3306 | 1000 | |
+--------------+-----------------+------+--------+---------+
#默认的proxysql-admin.cnf中
export WRITER_HOSTGROUP_ID='10' #写组
export READER_HOSTGROUP_ID='11' #读组
export BACKUP_WRITER_HOSTGROUP_ID='12' #备用的写组
export OFFLINE_HOSTGROUP_ID='13' #失联组
设置负载均衡
proxysql-admin --config-file=/etc/proxysql-admin.cnf --mode=loadbal --enable
#设置之后在查看
mysql> select hostgroup_id,hostname,port,weight,comment from mysql_servers;
+--------------+-----------------+------+--------+---------+
| hostgroup_id | hostname | port | weight | comment |
+--------------+-----------------+------+--------+---------+
| 10 | 192.168.190.128 | 3306 | 1000 | |
| 11 | 192.168.190.129 | 3306 | 1000 | |
| 11 | 192.168.190.128 | 3306 | 1000 | |
| 12 | 192.168.190.129 | 3306 | 1000 | |
| 12 | 192.168.190.128 | 3306 | 1000 | |
| 10 | 192.168.190.130 | 3306 | 1000 | |
| 10 | 192.168.190.129 | 3306 | 1000 | |
+--------------+-----------------+------+--------+---------+
7 rows in set (0.00 sec)
#同时也load进内存了。所以说proxysql-admin真的很方便
mysql> select hostgroup_id,hostname,port,weight,comment from runtime_mysql_servers;
+--------------+-----------------+------+--------+---------+
| hostgroup_id | hostname | port | weight | comment |
+--------------+-----------------+------+--------+---------+
| 10 | 192.168.190.128 | 3306 | 1000 | |
| 11 | 192.168.190.129 | 3306 | 1000 | |
| 11 | 192.168.190.128 | 3306 | 1000 | |
| 12 | 192.168.190.129 | 3306 | 1000 | |
| 12 | 192.168.190.128 | 3306 | 1000 | |
| 10 | 192.168.190.130 | 3306 | 1000 | |
| 10 | 192.168.190.129 | 3306 | 1000 | |
+--------------+-----------------+------+--------+---------+
7 rows in set (0.00 sec)
模式
支持的模式是loadbal和singlewrite(默认)
--writers-are-readers也可以设置写节点支持读
配置单写
# grep "MODE" /etc/proxysql-admin.cnf
export MODE="singlewrite"
# proxysql-admin --config-file=/etc/proxysql-admin.cnf --write-node=192.168.190.130:3306 --enable
# proxysql-admin --config-file=/etc/proxysql-admin.cnf --write-node=192.168.190.130:3306 --update-cluster --enable
No new nodes detected.
Waiting for ProxySQL to process the new nodes...
Cluster node info
+---------------+-------+-----------------+------+---------+
| hostgroup | hg_id | hostname | port | weight |
+---------------+-------+-----------------+------+---------+
| writer | 10 | 192.168.190.128 | 3306 | 1000 |
| writer | 10 | 192.168.190.129 | 3306 | 1000 |
| writer | 10 | 192.168.190.130 | 3306 | 1000000 |
| reader | 11 | 192.168.190.128 | 3306 | 1000 |
| reader | 11 | 192.168.190.129 | 3306 | 1000 |
| backup-writer | 12 | 192.168.190.128 | 3306 | 1000 |
| backup-writer | 12 | 192.168.190.129 | 3306 | 1000 |
+---------------+-------+-----------------+------+---------+
Cluster membership updated in the ProxySQL database!
#配置单写，也就是更改了权重，然后proxysql中save进磁盘，load进内存
#与内存和磁盘中的数据一致
mysql> select hostgroup_id,hostname,port,weight,comment from mysql_servers;
+--------------+-----------------+------+---------+---------+
| hostgroup_id | hostname | port | weight | comment |
+--------------+-----------------+------+---------+---------+
| 10 | 192.168.190.128 | 3306 | 1000 | |
| 11 | 192.168.190.129 | 3306 | 1000 | |
| 11 | 192.168.190.128 | 3306 | 1000 | |
| 12 | 192.168.190.129 | 3306 | 1000 | |
| 12 | 192.168.190.128 | 3306 | 1000 | |
| 10 | 192.168.190.129 | 3306 | 1000 | |
| 10 | 192.168.190.130 | 3306 | 1000000 | |
+--------------+-----------------+------+---------+---------+
7 rows in set (0.00 sec)
mysql> select hostgroup_id,hostname,port,weight,comment from runtime_mysql_servers;
+--------------+-----------------+------+---------+---------+
| hostgroup_id | hostname | port | weight | comment |
+--------------+-----------------+------+---------+---------+
| 10 | 192.168.190.128 | 3306 | 1000 | |
| 11 | 192.168.190.129 | 3306 | 1000 | |
| 11 | 192.168.190.128 | 3306 | 1000 | |
| 12 | 192.168.190.129 | 3306 | 1000 | |
| 12 | 192.168.190.128 | 3306 | 1000 | |
| 10 | 192.168.190.130 | 3306 | 1000000 | |
| 10 | 192.168.190.129 | 3306 | 1000 | |
+--------------+-----------------+------+---------+---------+
7 rows in set (0.00 sec)
暂时记在这，日后在完善
