重要参考步骤---ProxySQL Cluster 集群搭建步骤
环境
proxysql-1:192.168.20.202
proxysql-2:192.168.20.203
均采用yum方式安装
# cat <<EOF | tee /etc/yum.repos.d/proxysql.repo
[proxysql_repo]
name= ProxySQL YUM repository
baseurl=https://repo.proxysql.com/ProxySQL/proxysql-2.4.x/centos/\$releasever
gpgcheck=0
gpgkey=https://repo.proxysql.com/ProxySQL/repo_pub_key
EOF
# yum -y install proxysql
# systemctl start proxysql.service
# systemctl stop proxysql.service
# systemctl enable proxysql.service
# 查询端口占用情况，端口6032用于查看、配置ProxySQL；端口为6033，用于接收SQL语句，这个接口类似于MySQL的3306端口
# netstat -tulnp|grep "proxysql"
tcp 0 0 0.0.0.0:6032 0.0.0.0:* LISTEN 2115/proxysql
tcp 0 0 0.0.0.0:6033 0.0.0.0:* LISTEN 2115/proxysql
# 安装MySQL 客户端
wget https://repo.mysql.com//mysql80-community-release-el7-5.noarch.rpm
yum localinstall -y mysql80-community-release-el7-5.noarch.rpm
yum -y install mysql-community-client # 8.0.29版本
rpm包方式安装，需要先安装mysql-community-libs-compat，否则会报如下错误：yum -y install mysql-community-libs-compat
# rpm -ivh perl-DBD-MySQL-4.023-6.el7.x86_64.rpm
错误：依赖检测失败：
libmysqlclient.so.18()(64bit) 被 perl-DBD-MySQL-4.023-6.el7.x86_64 需要
libmysqlclient.so.18(libmysqlclient_18)(64bit) 被 perl-DBD-MySQL-4.023-6.el7.x86_64 需要
# 批量安装命令：ls *.rpm | xargs rpm -ivh
# 分步安装命令
rpm -ivh trousers-0.3.14-2.el7.x86_64.rpm
rpm -ivh nettle-2.7.1-9.el7_9.x86_64.rpm
rpm -ivh perl-Net-Daemon-0.48-5.el7.noarch.rpm
rpm -ivh perl-Compress-Raw-Bzip2-2.061-3.el7.x86_64.rpm
rpm -ivh perl-Compress-Raw-Zlib-2.061-4.el7.x86_64.rpm
rpm -ivh perl-IO-Compress-2.061-2.el7.noarch.rpm
rpm -ivh perl-Data-Dumper-2.145-3.el7.x86_64.rpm
rpm -ivh perl-PlRPC-0.2020-14.el7.noarch.rpm
rpm -ivh gnutls-3.3.29-9.el7_6.x86_64.rpm
rpm -ivh perl-DBI-1.627-4.el7.x86_64.rpm
rpm -ivh perl-DBD-MySQL-4.023-6.el7.x86_64.rpm
rpm -ivh proxysql-2.4.1-1-centos7.x86_64.rpm
集群的搭建有很多种方式，如1+1+1的方式，还可以(1+1)+1的方式。
这里采用较简单的(1+1)+1，即先将两个节点作为集群启动，后续若有其他节点选择性加入的方式
数据库方面的操作
数据库环境
master mysql: 192.168.20.200
slave mysql: 192.168.20.201
只配置同步test库，自带的mysql库没有同步，因此主库添加账号密码，从库也得添加一遍
# 添加一个监控用账号(能监控到从库的复制情况) ,在这里要么主从库自带的mysql库需要同步，若是没同步则每个MySQL都需要做这一步操作
create user 'proxysql'@'192.168.20.%' identified with mysql_native_password by 'iD!^^EjU#Yxr5$p';
GRANT USAGE,process,replication slave,replication client ON *.* TO 'proxysql'@'192.168.20.%' with grant option;
flush privileges;
# 注意：这里的账号密码要和下面我们在proxysql里面的mysql_variables段的账号密码配置的一样
# 添加一个程序连接用的账号
create user 'sbuser'@'192.168.20.%' identified with mysql_native_password by 'iD!^^EjU#Yxr5$p';
GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO 'sbuser'@'192.168.20.%' with grant option;
flush privileges;
此时MySQL主从库中有如下账号信息
超级用户：root
主从库同步用户：repl (从库没这个用户)
普通用户：sbuser
proxysql监控MySQL主从情况使用的用户：proxysql
集群搭建
1.更改所有实例的配置文件
# cp /etc/proxysql.cnf /etc/proxysql.cnf.bak
# vim /etc/proxysql.cnf
admin_variables=
{
admin_credentials="admin:admin;cluster_20X:123456"
mysql_ifaces="0.0.0.0:6032"
cluster_username="cluster_20X"
cluster_password="123456"
cluster_check_interval_ms=200
cluster_check_status_frequency=100
cluster_mysql_query_rules_save_to_disk=true
cluster_mysql_servers_save_to_disk=true
cluster_mysql_users_save_to_disk=true
cluster_proxysql_servers_save_to_disk=true
cluster_mysql_query_rules_diffs_before_sync=3
cluster_mysql_servers_diffs_before_sync=3
cluster_mysql_users_diffs_before_sync=3
cluster_proxysql_servers_diffs_before_sync=3
}
mysql_variables=
{
threads=4
max_connections=10000
default_query_delay=0
default_query_timeout=36000000
have_compress=true
poll_timeout=2000
interfaces="0.0.0.0:6033"
default_schema="information_schema"
stacksize=1048576
server_version="8.0.29"
connect_timeout_server=3000
monitor_username="proxysql"
monitor_password="iD!^^EjU#Yxr5$p"
monitor_history=600000
monitor_connect_interval=60000
monitor_ping_interval=10000
monitor_read_only_interval=1500
monitor_read_only_timeout=500
ping_interval_server_msec=120000
ping_timeout_server=500
commands_stats=true
sessions_sort=true
connect_retries_on_failure=10
#default_charset='utf8mb4' # 这个参数加上无法启动，只能先不加，等程序启动后再手动修改
#default_collation_connection='utf8mb4_general_ci' # 这个参数加上无法启动，只能先不加，等程序启动后再手动修改
max_allowed_packet=67108864
query_retries_on_failure=0
monitor_connect_timeout=1000
default_max_latency_ms=2000
monitor_replication_lag_interval=500
connect_timeout_server_max=3000
set_query_lock_on_hostgroup=0
}
proxysql_servers =
(
{
hostname="192.168.20.202"
port=6032
comment="proxysql-202"
},
{
hostname="192.168.20.203"
port=6032
comment="proxysql-203"
}
)
# 其余的配置信息保持不动
特别注意：如果存在"proxysql.db"文件(在/var/lib/proxysql目录下)，则ProxySQL服务只有在第一次启动时才会去读取proxysql.cnf文件并解析；后面启动会就不会读取proxysql.cnf文件了！如果想要让proxysql.cnf文件里的配置在重启proxysql服务后生效(即想要让proxysql重启时读取并解析proxysql.cnf配置文件)，则需要先删除/var/lib/proxysql/proxysql.db数据库文件，然后再重启proxysql服务。这样就相当于初始化启动proxysql服务了，会再次生产一个纯净的proxysql.db数据库文件(如果之前配置了proxysql相关路由规则等，则就会被抹掉)。
# 俩proxysql节点启动proxysql进程
systemctl start proxysql
# 登录进去(无特殊说明均在192.168.20.202上操作)
/usr/bin/mysql -uadmin -padmin -h 127.0.0.1 -P 6032
# 观察集群状况
mysql> select * from proxysql_servers;
+----------------+------+--------+--------------+
| hostname | port | weight | comment |
+----------------+------+--------+--------------+
| 192.168.20.202 | 6032 | 0 | proxysql-202 |
| 192.168.20.203 | 6032 | 0 | proxysql-203 |
+----------------+------+--------+--------------+
2 rows in set (0.00 sec)
mysql> select * from stats_proxysql_servers_metrics;
+----------------+------+--------+--------------+------------------+----------+---------------+---------+------------------------------+----------------------------+
| hostname | port | weight | comment | response_time_ms | Uptime_s | last_check_ms | Queries | Client_Connections_connected | Client_Connections_created |
+----------------+------+--------+--------------+------------------+----------+---------------+---------+------------------------------+----------------------------+
| 192.168.20.203 | 6032 | 0 | proxysql-203 | 2 | 683 | 2593 | 0 | 0 | 0 |
| 192.168.20.202 | 6032 | 0 | proxysql-202 | 2 | 686 | 2923 | 0 | 0 | 0 |
+----------------+------+--------+--------------+------------------+----------+---------------+---------+------------------------------+----------------------------+
2 rows in set (0.00 sec)
mysql> select hostname,port,comment,Uptime_s,last_check_ms from stats_proxysql_servers_metrics;
+----------------+------+--------------+----------+---------------+
| hostname | port | comment | Uptime_s | last_check_ms |
+----------------+------+--------------+----------+---------------+
| 192.168.20.203 | 6032 | proxysql-203 | 1266 | 15622 |
| 192.168.20.202 | 6032 | proxysql-202 | 1266 | 18806 |
+----------------+------+--------------+----------+---------------+
2 rows in set (0.00 sec)
mysql> select hostname,name,checksum,updated_at from stats_proxysql_servers_checksums;
+----------------+-------------------+--------------------+------------+
| hostname | name | checksum | updated_at |
+----------------+-------------------+--------------------+------------+
| 192.168.20.203 | admin_variables | 0xF8E25295F13135A0 | 1653448141 |
| 192.168.20.203 | mysql_query_rules | 0x0000000000000000 | 1653448141 |
| 192.168.20.203 | mysql_servers | 0xE5A163C3AD6BD3A7 | 1653448141 |
| 192.168.20.203 | mysql_users | 0x0000000000000000 | 1653448141 |
| 192.168.20.203 | mysql_variables | 0x3ECA231EE02626C9 | 1653448141 |
| 192.168.20.203 | proxysql_servers | 0x75C8DA71CAF992E0 | 1653448141 |
| 192.168.20.202 | admin_variables | 0xF8E25295F13135A0 | 1653448141 |
| 192.168.20.202 | mysql_query_rules | 0x0000000000000000 | 1653448141 |
| 192.168.20.202 | mysql_servers | 0xE5A163C3AD6BD3A7 | 1653448141 |
| 192.168.20.202 | mysql_users | 0x0000000000000000 | 1653448141 |
| 192.168.20.202 | mysql_variables | 0x3ECA231EE02626C9 | 1653448141 |
| 192.168.20.202 | proxysql_servers | 0x75C8DA71CAF992E0 | 1653448141 |
+----------------+-------------------+--------------------+------------+
12 rows in set (0.00 sec)
# 观察ProxySQL集群中实例之间的数据同步
# 原有数据
mysql> select * from mysql_servers;
Empty set (0.00 sec)
# 新增一个后端MySQL主机信息
mysql> insert into mysql_servers(hostgroup_id,hostname,port,comment) values (10,'192.168.20.200',3306,'master_mysql');
# 该主机上查看
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 |
+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+--------------+
| 10 | 192.168.20.200 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | master_mysql |
+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+--------------+
1 row in set (0.00 sec)
# 此时到另一台proxysql主机上查看没有这个后端MySQL主机信息
# 持久化，并加载到运行环境中
mysql> save mysql servers to disk;
mysql> load mysql servers to runtime;
# 再次到另一台proxysql主机上查看，可以看到新插入的数据，发现有这个后端MySQL主机信息，已经被更新到192.168.20.203实例中的memory和runtime环境中。
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 |
+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+--------------+
| 10 | 192.168.20.200 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | master_mysql |
+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+--------------+
1 row in set (0.00 sec)
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 |
+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+--------------+
| 10 | 192.168.20.200 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | master_mysql |
+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+--------------+
1 row in set (0.00 sec)
# 查看另一台proxysql主机日志，会看到同步的具体信息
# tail -n 30 /var/lib/proxysql/proxysql.log
2022-05-25 11:03:27 [INFO] Cluster: Loading to runtime MySQL Servers from peer 192.168.20.202:6032
2022-05-25 11:03:27 [INFO] Dumping mysql_servers_incoming
+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+--------------+
| hostgroup_id | hostname | port | gtid_port | weight | status | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+--------------+
| 10 | 192.168.20.200 | 3306 | 0 | 1 | 0 | 0 | 1000 | 0 | 0 | 0 | master_mysql |
+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+--------------+
2022-05-25 11:03:27 [INFO] Dumping mysql_servers LEFT JOIN mysql_servers_incoming
+-------------+--------------+----------+------+
| mem_pointer | hostgroup_id | hostname | port |
+-------------+--------------+----------+------+
+-------------+--------------+----------+------+
2022-05-25 11:03:27 [INFO] Dumping mysql_servers JOIN mysql_servers_incoming
+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+--------------+-------------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+--------------+
| hostgroup_id | hostname | port | gtid_port | weight | status | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment | mem_pointer | gtid_port | weight | status | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+--------------+-------------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+--------------+
| 10 | 192.168.20.200 | 3306 | 0 | 1 | 0 | 0 | 1000 | 0 | 0 | 0 | master_mysql | 0 | 0 | 1 | 0 | 0 | 1000 | 0 | 0 | 0 | master_mysql |
+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+--------------+-------------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+--------------+
2022-05-25 11:03:27 [INFO] Creating new server in HG 10 : 192.168.20.200:3306 , gtid_port=0, weight=1, status=0
2022-05-25 11:03:27 [INFO] New mysql_group_replication_hostgroups table
2022-05-25 11:03:27 [INFO] New mysql_galera_hostgroups table
2022-05-25 11:03:27 [INFO] New mysql_aws_aurora_hostgroups table
2022-05-25 11:03:27 [INFO] Checksum for table mysql_servers is 6785001030445135624
2022-05-25 11:03:27 [INFO] MySQL_HostGroups_Manager::commit() locked for 1ms
2022-05-25 11:03:27 [INFO] Cluster: Saving to disk MySQL Servers from peer 192.168.20.202:6032
2022-05-25 11:03:27 [INFO] Cluster: checksum for admin_variables from peer 192.168.20.203:6032 matches with local checksum 0xF8E25295F13135A0, we won't sync.
2022-05-25 11:03:27 [INFO] Cluster: detected a new checksum for mysql_servers from peer 192.168.20.203:6032, version 2, epoch 1653447806, checksum 0xE5A163C3AD6BD3A7 . Not syncing yet ...
2022-05-25 11:03:27 [INFO] Cluster: checksum for mysql_servers from peer 192.168.20.203:6032 matches with local checksum 0xE5A163C3AD6BD3A7 , we won't sync.
注意：数据差异检查是根据runtime进行检查的，只对memory和disk进行更改，并不触发同步操作。
此时，两节点的proxysql cluster集群搭建完毕
后续若需要加入第三个proxysql节点到集群中的操作
这里以安装有MySQL master主机节点为例进行演示(也可以使用新主机)
192.168.20.200为全新的节点，使用conf文件启动，不使用更改global_variable的方式加入集群(操作复杂且容器出错)。
# 先安装好proxysql软件，不启动（mysql客户端已安装，此时不用再安装，若未安装也需要安装这个）
# 修改 /etc/proxysql.cnf (修改了admin_variables段、proxysql_servers段、mysql_variables段） 【要和proxysql cluster里面的其他节点运行配置一样，集群名称、各种账号密码要一致】
# cp /etc/proxysql.cnf /etc/proxysql.cnf.bak
# vim /etc/proxysql.cnf
admin_variables=
{
admin_credentials="admin:admin;cluster_20X:123456"
mysql_ifaces="0.0.0.0:6032"
cluster_username="cluster_20X"
cluster_password="123456"
cluster_check_interval_ms=200
cluster_check_status_frequency=100
cluster_mysql_query_rules_save_to_disk=true
cluster_mysql_servers_save_to_disk=true
cluster_mysql_users_save_to_disk=true
cluster_proxysql_servers_save_to_disk=true
cluster_mysql_query_rules_diffs_before_sync=3
cluster_mysql_servers_diffs_before_sync=3
cluster_mysql_users_diffs_before_sync=3
cluster_proxysql_servers_diffs_before_sync=3
}
mysql_variables=
{
threads=4
max_connections=10000
default_query_delay=0
default_query_timeout=36000000
have_compress=true
poll_timeout=2000
interfaces="0.0.0.0:6033"
default_schema="information_schema"
stacksize=1048576
server_version="8.0.29"
connect_timeout_server=3000
monitor_username="proxysql"
monitor_password="iD!^^EjU#Yxr5$p"
monitor_history=600000
monitor_connect_interval=60000
monitor_ping_interval=10000
monitor_read_only_interval=1500
monitor_read_only_timeout=500
ping_interval_server_msec=120000
ping_timeout_server=500
commands_stats=true
sessions_sort=true
connect_retries_on_failure=10
#default_charset='utf8mb4' # 这个参数加上无法启动，只能先不加，等程序启动后再手动修改
#default_collation_connection='utf8mb4_general_ci' # 这个参数加上无法启动，只能先不加，等程序启动后再手动修改
max_allowed_packet=67108864
query_retries_on_failure=0
monitor_connect_timeout=1000
default_max_latency_ms=2000
monitor_replication_lag_interval=500
connect_timeout_server_max=3000
set_query_lock_on_hostgroup=0
}
proxysql_servers =
(
{
hostname="192.168.20.202"
port=6032
comment="proxysql-202"
},
{
hostname="192.168.20.203"
port=6032
comment="proxysql-203"
}
)
# 其余的配置信息保持不动
# 在新节点里启动proxysql后， 可以看下 192.168.20.200 的 /var/lib/proxysql/proxysql.log 日志里面， 192.168.20.200 这个新加入的节点 会去其它节点拉取配置（但是其它节点不知道这个192.168.20.200到底是什么身份的存在）。
# 然后，我们在老的proxysql的任一节点上，将 192.168.20.200 这个新节点加入到集群环境：
# 插入一条proxysql_server的信息
insert into proxysql_servers(hostname,port,comment ) values('192.168.20.200',6032,'bak proxysql') ;
# 加载到runtime，并把配置持久化
load proxysql servers to runtime;
save proxysql servers to disk;
# 查下结果是否正常
mysql> select * from proxysql_servers;
+----------------+------+--------+--------------+
| hostname | port | weight | comment |
+----------------+------+--------+--------------+
| 192.168.20.202 | 6032 | 0 | proxysql-202 |
| 192.168.20.203 | 6032 | 0 | proxysql-203 |
| 192.168.20.200 | 6032 | 0 | bak proxysql |
+----------------+------+--------+--------------+
3 rows in set (0.00 sec)
mysql> select * from runtime_proxysql_servers ;
+----------------+------+--------+--------------+
| hostname | port | weight | comment |
+----------------+------+--------+--------------+
| 192.168.20.200 | 6032 | 0 | bak proxysql |
| 192.168.20.203 | 6032 | 0 | proxysql-203 |
| 192.168.20.202 | 6032 | 0 | proxysql-202 |
+----------------+------+--------+--------------+
3 rows in set (0.00 sec)
经过上面的步骤后， 192.168.20.200 就完成集群添加新节点的操作了。
添加下后端mysql信息， 添加读写分离的路由规则
在3节点中的任意一台proyxql的admin控制台执行下面的这些操作（这个新增的配置会在load runtime时候，自动同步到集群其它节点）：
mysql> delete from mysql_servers;
# 写节点组 100， 读节点组 1000
# 写组，master mysql节点，权重1
mysql> insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment) values(100,'192.168.20.200',3306,1,1000,10,'100-1-master_mysql');
# 读组，salve mysql节点，权重9
mysql> insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment) values(1000,'192.168.20.201',3306,9,1000,10,'1000-9-slave_mysql');
# 读组，master mysql节点，权重1 (当读组，salve mysql节点，权重9发生故障时，由该节点承担读任务，也就是说当读不到从库，回去主库查询)
mysql> insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment) values(1000,'192.168.20.200',3306,1,1000,10,'1000-1-master_mysql');
# 加载到runtime，并把配置持久化
mysql> load mysql servers to runtime;
mysql> save mysql servers to disk;
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 |
+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------------------+
| 100 | 192.168.20.200 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 10 | 0 | 0 | 100-1-master_mysql |
| 1000 | 192.168.20.200 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 10 | 0 | 0 | 1000-1-master_mysql |
| 1000 | 192.168.20.201 | 3306 | 0 | ONLINE | 9 | 0 | 1000 | 10 | 0 | 0 | 1000-9-slave_mysql |
+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------------------+
3 rows in set (0.00 sec)
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 |
+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------------------+
| 100 | 192.168.20.200 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 10 | 0 | 0 | 100-1-master_mysql |
| 1000 | 192.168.20.200 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 10 | 0 | 0 | 1000-1-master_mysql |
| 1000 | 192.168.20.201 | 3306 | 0 | ONLINE | 9 | 0 | 1000 | 10 | 0 | 0 | 1000-9-slave_mysql |
+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------------------+
3 rows in set (0.01 sec)
# 添加一个账号，用于proxysql和后端主机的连接,在这里使用开头MySQL数据库中已经添加的sbuser用户信息
mysql> insert into mysql_users(username,password,active,default_hostgroup,transaction_persistent) values('sbuser','iD!^^EjU#Yxr5$p',1,100,1);
mysql> load mysql users to runtime;
mysql> save mysql users to disk;
# 发现一个情况
# 在添加用户的主机上查看信息如下，密码没加密且只有一个账号
mysql> select * from mysql_users;
+----------+-----------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+------------+---------+
| username | password | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections | attributes | comment |
+----------+-----------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+------------+---------+
| sbuser | iD!^^EjU#Yxr5$p | 1 | 0 | 100 | NULL | 0 | 1 | 0 | 1 | 1 | 10000 | | |
+----------+-----------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+------------+---------+
1 row in set (0.00 sec)
# 但是在另外两个节点上查看信息如下。密码不仅是加密的，并且还有俩账号
mysql> select * from mysql_users;
+----------+-------------------------------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+------------+---------+
| username | password | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections | attributes | comment |
+----------+-------------------------------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+------------+---------+
| sbuser | *1A1EDDE95A2B74BFDCACFBCC83C7E0600C7D19A1 | 1 | 0 | 100 | | 0 | 1 | 0 | 0 | 1 | 10000 | | |
| sbuser | *1A1EDDE95A2B74BFDCACFBCC83C7E0600C7D19A1 | 1 | 0 | 100 | | 0 | 1 | 0 | 1 | 0 | 10000 | | |
+----------+-------------------------------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+------------+---------+
2 rows in set (0.00 sec)
mysql> select * from mysql_users\G
*************************** 1. row ***************************
username: sbuser
password: iD!^^EjU#Yxr5$p
active: 1
use_ssl: 0
default_hostgroup: 100
default_schema: NULL
schema_locked: 0
transaction_persistent: 1
fast_forward: 0
backend: 1
frontend: 1
max_connections: 10000
attributes:
comment:
1 row in set (0.00 sec)
mysql> select * from mysql_users\G
*************************** 1. row ***************************
username: sbuser
password: *1A1EDDE95A2B74BFDCACFBCC83C7E0600C7D19A1
active: 1
use_ssl: 0
default_hostgroup: 100
default_schema:
schema_locked: 0
transaction_persistent: 1
fast_forward: 0
backend: 0
frontend: 1
max_connections: 10000
attributes:
comment:
*************************** 2. row ***************************
username: sbuser
password: *1A1EDDE95A2B74BFDCACFBCC83C7E0600C7D19A1
active: 1
use_ssl: 0
default_hostgroup: 100
default_schema:
schema_locked: 0
transaction_persistent: 1
fast_forward: 0
backend: 1
frontend: 0
max_connections: 10000
attributes:
comment:
2 rows in set (0.00 sec)
# 定义读写分离的路由规则：
# 定义sql规则，发送到主库
mysql> INSERT INTO mysql_query_rules(active,match_pattern,destination_hostgroup,apply) VALUES(1,'^SELECT.*FOR UPDATE$',100,1);
Query OK, 1 row affected (0.00 sec)
# 定义sql规则，发送到从库
mysql> INSERT INTO mysql_query_rules(active,match_pattern,destination_hostgroup,apply) VALUES(1,'^SELECT',1000,1);
Query OK, 1 row affected (0.00 sec)
# 加载路由规则到runtime，并把配置持久化
mysql> load mysql query rules to runtime;
Query OK, 0 rows affected (0.01 sec)
mysql> save mysql query rules to disk;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from mysql_query_rules;
+---------+--------+----------+------------+--------+-------------+------------+------------+--------+--------------+----------------------+----------------------+--------------+---------+-----------------+-----------------------+-----------+--------------------+---------------+-----------+---------+---------+-------+-------------------+----------------+------------------+-----------+--------+-------------+-----------+---------------------+-----+-------+------------+---------+
| rule_id | active | username | schemaname | flagIN | client_addr | proxy_addr | proxy_port | digest | match_digest | match_pattern | negate_match_pattern | re_modifiers | flagOUT | replace_pattern | destination_hostgroup | cache_ttl | cache_empty_result | cache_timeout | reconnect | timeout | retries | delay | next_query_flagIN | mirror_flagOUT | mirror_hostgroup | error_msg | OK_msg | sticky_conn | multiplex | gtid_from_hostgroup | log | apply | attributes | comment |
+---------+--------+----------+------------+--------+-------------+------------+------------+--------+--------------+----------------------+----------------------+--------------+---------+-----------------+-----------------------+-----------+--------------------+---------------+-----------+---------+---------+-------+-------------------+----------------+------------------+-----------+--------+-------------+-----------+---------------------+-----+-------+------------+---------+
| 1 | 1 | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | ^SELECT.*FOR UPDATE$ | 0 | CASELESS | NULL | NULL | 100 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 1 | | NULL |
| 2 | 1 | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | ^SELECT | 0 | CASELESS | NULL | NULL | 1000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 1 | | NULL |
+---------+--------+----------+------------+--------+-------------+------------+------------+--------+--------------+----------------------+----------------------+--------------+---------+-----------------+-----------------------+-----------+--------------------+---------------+-----------+---------+---------+-------+-------------------+----------------+------------------+-----------+--------+-------------+-----------+---------------------+-----+-------+------------+---------+
2 rows in set (0.00 sec)
mysql> select * from runtime_mysql_query_rules;
+---------+--------+----------+------------+--------+-------------+------------+------------+--------+--------------+----------------------+----------------------+--------------+---------+-----------------+-----------------------+-----------+--------------------+---------------+-----------+---------+---------+-------+-------------------+----------------+------------------+-----------+--------+-------------+-----------+---------------------+-----+-------+------------+---------+
| rule_id | active | username | schemaname | flagIN | client_addr | proxy_addr | proxy_port | digest | match_digest | match_pattern | negate_match_pattern | re_modifiers | flagOUT | replace_pattern | destination_hostgroup | cache_ttl | cache_empty_result | cache_timeout | reconnect | timeout | retries | delay | next_query_flagIN | mirror_flagOUT | mirror_hostgroup | error_msg | OK_msg | sticky_conn | multiplex | gtid_from_hostgroup | log | apply | attributes | comment |
+---------+--------+----------+------------+--------+-------------+------------+------------+--------+--------------+----------------------+----------------------+--------------+---------+-----------------+-----------------------+-----------+--------------------+---------------+-----------+---------+---------+-------+-------------------+----------------+------------------+-----------+--------+-------------+-----------+---------------------+-----+-------+------------+---------+
| 1 | 1 | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | ^SELECT.*FOR UPDATE$ | 0 | CASELESS | NULL | NULL | 100 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 1 | | NULL |
| 2 | 1 | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | ^SELECT | 0 | CASELESS | NULL | NULL | 1000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 1 | | NULL |
+---------+--------+----------+------------+--------+-------------+------------+------------+--------+--------------+----------------------+----------------------+--------------+---------+-----------------+-----------------------+-----------+--------------------+---------------+-----------+---------+---------+-------+-------------------+----------------+------------------+-----------+--------+-------------+-----------+---------------------+-----+-------+------------+---------+
2 rows in set (0.00 sec)
测试
然后，连接proxysql做crud的测试（可以测试下读写分离情况，不是本文的重点）
端口6032用于查看、配置ProxySQL；端口为6033，用于接收SQL语句，这个接口类似于MySQL的3306端口
mysql -usbuser -h 172.100.2.13 -P6033 -p # 密码是：iD!^^EjU#Yxr5$p
scheduler打印状态到日志
编辑脚本和目录
# mkdir -p /opt/proxysql/log
# vim /opt/proxysql/log/status.sh
#!/bin/bash
DATE=`date "+%Y-%m-%d %H:%M:%S"`
echo "{\"dateTime\":\"$DATE\",\"status\":\"running\"}" >> /opt/proxysql/log/status_log
# chmod 777 /opt/proxysql/log/status.sh
输入scheduler信息
mysql> insert into scheduler(active,interval_ms,filename) values (1,60000,'/opt/proxysql/log/status.sh');
Query OK, 1 row affected (0.00 sec)
mysql> LOAD SCHEDULER TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)
mysql> SAVE SCHEDULER TO DISK;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from global_variables where variable_name LIKE 'admin-web%' or variable_name LIKE 'admin-stats%';
+----------------------------------------+----------------+
| variable_name | variable_value |
+----------------------------------------+----------------+
| admin-stats_credentials | stats:stats |
| admin-stats_mysql_connections | 60 |
| admin-stats_mysql_connection_pool | 60 |
| admin-stats_mysql_query_cache | 60 |
| admin-stats_mysql_query_digest_to_disk | 0 |
| admin-stats_system_cpu | 60 |
| admin-stats_system_memory | 60 |
| admin-web_enabled | true |
| admin-web_port | 6080 |
+----------------------------------------+----------------+
9 rows in set (0.01 sec)
查看日志：
# tail -f status_log
{"dateTime":"2020-04-05 00:07:40","status":"running"}
{"dateTime":"2020-04-05 00:08:41","status":"running"}
{"dateTime":"2020-04-05 00:10:52","status":"running"}
优化
proxysql检查主从同步延迟情况，若是符合条件则屏蔽掉从库
Monitor模块会每隔一段时间(mysql-monitor_replication_lag_interval)去检查一次拖后腿情况，检测的方式是获取show slave status中的Seconds_Behind_Master字段值，然后和mysql_servers表中max_replication_lag字段的值比较：
mysql> update mysql_servers set max_replication_lag=10 where hostgroup_id=1000 and hostname="192.168.0.36"; # 确保只有从库设置
mysql> load mysql servers to runtime;
Query OK, 0 rows affected (0.00 sec)
mysql> save mysql servers to disk;
Query OK, 0 rows affected (0.01 sec)
mysql> select hostgroup_id,hostname,port,max_replication_lag from mysql_servers;
+--------------+---------------+------+---------------------+
| hostgroup_id | hostname | port | max_replication_lag |
+--------------+---------------+------+---------------------+
| 1000 | 192.168.0.36 | 3306 | 10 | # 从库
| 100 | 192.168.0.218 | 3306 | 0 | # 主库
| 1000 | 192.168.0.218 | 3306 | 0 | # 主库
+--------------+---------------+------+---------------------+
3 rows in set (0.00 sec)
mysql> select * from global_variables where variable_name like 'mysql-monitor%lag%';
+-----------------------------------------------------+----------------+
| variable_name | variable_value |
+-----------------------------------------------------+----------------+
| mysql-monitor_replication_lag_group_by_host | false |
| mysql-monitor_replication_lag_timeout | 1000 |
| mysql-monitor_replication_lag_count | 1 |
| mysql-monitor_replication_lag_use_percona_heartbeat | |
| mysql-monitor_slave_lag_when_null | 60 |
| mysql-monitor_replication_lag_interval | 500 |
+-----------------------------------------------------+----------------+
6 rows in set (0.00 sec)
