Percona的proxysql-admin负载pxc集群

percona的proxysql比官方的更方便，一个proxysql-admin就可以完成配置，也不用反复load

https://www.percona.com/doc/percona-xtradb-cluster/5.7/howtos/proxysql.html#load-balancing-with-proxysql

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)