跳至主要內容

MySQL数据库代理中间件ProxySQL安装

大约 6 分钟约 1810 字

MySQL数据库代理中间件ProxySQL安装

ProxySQL架构

ProxySQL官方文档open in new windowProxySQL Clusteropen in new window

MGR实现了服务端高可用，ProxySQL实现了集群的客户端高可用，二者结合来用才是完美的。

ProxySQL安装

不同操作系统安装参考官方部署安装文档open in new window。

以下以CentOS 7系统为例，主要也是参考官方文档部署配置。

# 在CentOS 上部署ProxySQL，可以先配置yum源， 也可以直接从官网下载rpm包安装，下面的是yum源配置示例。
cat > /etc/yum.repos.d/proxysql.repo <<EOF
[proxysql_repo]
name= ProxySQL YUM repository
baseurl=https://repo.proxysql.com/ProxySQL/proxysql-2.4.x/centos/7
gpgcheck=0
gpgkey=https://repo.proxysql.com/ProxySQL/repo_pub_key
EOF

# proxysql rpm包安装会以来操作系统基础镜像中的软件包，务必先配置好，我们此处配置好aliyun的CentOS 7镜像仓库源
curl -s -o /etc/yum.repos.d/CentOS-Base.repo https://mirrors.aliyun.com/repo/Centos-7.repo
mkdir -p /etc/yum.repos.d/old
mv /etc/yum.repos.d/CentOS-*.repo /etc/yum.repos.d/old
sed -i '/aliyuncs.com/d' /etc/yum.repos.d/CentOS-Base.repo 
yum clean all && yum makecache && yum grouplist > /dev/null 2>&1

# 我们采用从官网直接下载rpm包，然后本地安装rpm包方式安装
yum localinstall -y proxysql-2.4.1-1-centos7.x86_64.rpm 


# 按需要修改proxysql主配置文件/etc/proxysql.cnf，通常不需要做任何修改，proxysql提供的专用配置接口，我们需要部署ProxySQL Cluster，此处仅需添加集群及相关节点配置即可，后期的所有代理配置都通过配置管理接口来操作。

# 如果存在如果存在"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主配置文件Cluster配置

cat /etc/proxysql.cnf  
datadir="/var/lib/proxysql"
errorlog="/var/lib/proxysql/proxysql.log"

admin_variables=
{
        admin_credentials="admin:admin.;cluster_admin:123456."
#       mysql_ifaces="127.0.0.1:6032;/tmp/proxysql_admin.sock"
        mysql_ifaces="0.0.0.0:6032"
        refresh_interval=2000
        cluster_username="cluster_admin"
        cluster_password="x123456."
        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
#       debug=true
}

mysql_variables=
{
        threads=4
        max_connections=2048
        default_query_delay=0
        default_query_timeout=36000000
        have_compress=true
        poll_timeout=2000
#       interfaces="0.0.0.0:6033;/tmp/proxysql.sock"
        interfaces="0.0.0.0:3306"
        default_schema="information_schema"
        stacksize=1048576
        server_version="5.7.38"
        connect_timeout_server=3000
# make sure to configure monitor username and password
# https://github.com/sysown/proxysql/wiki/Global-variables#mysql-monitor_username-mysql-monitor_password
        monitor_username="proxysql"
        monitor_password="Lead.123456"
        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
}


# ProxySQL Cluster集群相关节点
proxysql_servers = 
(
    {
       hostname="10.xx.yy.z2"
       port=6032
       weight=1
       comment="ProxySQL-n1"
    },
    {
       hostname="10.xx.yy.z3"
       port=6032
       weight=1
       comment="ProxySQL-n2"
    },
    {
       hostname="10.xx.yy.z4"
       port=6032
       weight=1
       comment="ProxySQL-n3"
    }
)

ProxySQL配置支持MGR集群读写分离配置

# 选择登录一个ProxySQL节点的配置接口
mysql -uadmin -padmin. -h127.0.0.1 -P6032
[root@n1 ~]#   mysql -uadmin -padmin -h127.0.0.1 -P6032
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 142
Server version: 5.7.38 (ProxySQL Admin Module)

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

admin@127.0.0.1 [(none)]>select * from proxysql_servers;
+--------------+------+--------+-------------+
| hostname     | port | weight | comment     |
+--------------+------+--------+-------------+
| 10.xx.yy.z5 | 6032 | 1      | ProxySQL-n1 |
| 10.xx.yy.z6 | 6032 | 1      | ProxySQL-n2 |
| 10.xx.yy.z7 | 6032 | 1      | ProxySQL-n3 |
+--------------+------+--------+-------------+
3 rows in set (0.00 sec)

admin@127.0.0.1 [(none)]>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 |
+--------------+------+--------+-------------+------------------+----------+---------------+---------+------------------------------+----------------------------+
| 10.xx.yy.z7 | 6032 | 1      | ProxySQL-n3 | 3                | 238299   | 14032         | 16      | 0                            | 8                          |
| 10.xx.yy.z6 | 6032 | 1      | ProxySQL-n2 | 3                | 238318   | 8019          | 18      | 0                            | 9                          |
| 10.xx.yy.z5 | 6032 | 1      | ProxySQL-n1 | 1                | 238344   | 11294         | 240     | 0                            | 118                        |
+--------------+------+--------+-------------+------------------+----------+---------------+---------+------------------------------+----------------------------+
3 rows in set (0.01 sec)

admin@127.0.0.1 [(none)]>select hostname,port,comment,Uptime_s,last_check_ms from stats_proxysql_servers_metrics; 
+--------------+------+-------------+----------+---------------+
| hostname     | port | comment     | Uptime_s | last_check_ms |
+--------------+------+-------------+----------+---------------+
| 10.xx.yy.z7 | 6032 | ProxySQL-n3 | 238319   | 18717         |
| 10.xx.yy.z6 | 6032 | ProxySQL-n2 | 238338   | 12704         |
| 10.xx.yy.z5 | 6032 | ProxySQL-n1 | 238364   | 16005         |
+--------------+------+-------------+----------+---------------+
3 rows in set (0.00 sec)

admin@127.0.0.1 [(none)]>select hostname,name,checksum,updated_at from stats_proxysql_servers_checksums;
+--------------+-------------------+--------------------+------------+
| hostname     | name              | checksum           | updated_at |
+--------------+-------------------+--------------------+------------+
| 10.xx.yy.z7 | admin_variables   | 0x5E7A2016DCD0CDC5 | 1654479806 |
| 10.xx.yy.z7 | mysql_query_rules | 0xF654D5BAAAF87997 | 1654479806 |
| 10.xx.yy.z7 | mysql_servers     | 0xA7ED6B3E4E4C1ED8 | 1654479806 |
| 10.xx.yy.z7 | mysql_users       | 0x1BD7A4FE3EE4D845 | 1654479806 |
| 10.xx.yy.z7 | mysql_variables   | 0xDF63E3115CB8F34E | 1654479806 |
| 10.xx.yy.z7 | proxysql_servers  | 0x91DCB3856AA39EDA | 1654479806 |
| 10.xx.yy.z6 | admin_variables   | 0x5E7A2016DCD0CDC5 | 1654479806 |
| 10.xx.yy.z6 | mysql_query_rules | 0xF654D5BAAAF87997 | 1654479806 |
| 10.xx.yy.z6 | mysql_servers     | 0xA7ED6B3E4E4C1ED8 | 1654479806 |
| 10.xx.yy.z6 | mysql_users       | 0x1BD7A4FE3EE4D845 | 1654479806 |
| 10.xx.yy.z6 | mysql_variables   | 0xDF63E3115CB8F34E | 1654479806 |
| 10.xx.yy.z6 | proxysql_servers  | 0x91DCB3856AA39EDA | 1654479806 |
| 10.xx.yy.z5 | admin_variables   | 0x5E7A2016DCD0CDC5 | 1654479806 |
| 10.xx.yy.z5 | mysql_query_rules | 0xF654D5BAAAF87997 | 1654479806 |
| 10.xx.yy.z5 | mysql_servers     | 0xA7ED6B3E4E4C1ED8 | 1654479806 |
| 10.xx.yy.z5 | mysql_users       | 0x1BD7A4FE3EE4D845 | 1654479806 |
| 10.xx.yy.z5 | mysql_variables   | 0xDF63E3115CB8F34E | 1654479806 |
| 10.xx.yy.z5 | proxysql_servers  | 0x91DCB3856AA39EDA | 1654479806 |
+--------------+-------------------+--------------------+------------+
18 rows in set (0.00 sec)

# 1. 在MGR集群的主节点上创建ProxySQL官方提供的系统视图sys.gr_member_routing_candidate_status


# 2. 在MGR集群的主节点上创建ProxySQL监控账号及应用账号
CREATE USER 'proxysql'@'%' IDENTIFIED BY 'Lead.China2022'; 
GRANT ALL ON * . * TO  'proxysql'@'%';
FLUSH PRIVILEGES; 

# 3. 在ProxySQL集群任意一节点上添加监控账号
INSERT INTO MySQL_users(username,password,default_hostgroup) VALUES ('proxysql','Lead.China2022',1);
INSERT INTO MySQL_users(username,password,default_hostgroup) VALUES ('tsign','Tsign.2022',1);
UPDATE global_variables SET variable_value='proxysql' where variable_name='mysql-monitor_username';
UPDATE global_variables SET variable_value='Lead.China2022' where variable_name='mysql-monitor_password';
INSERT INTO MySQL_users(username,password,default_hostgroup) VALUES ('tsign','Tsign.2022',1);
LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK;
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;

# 4. 在ProxySQL集群的任意节点配置后端MySQL Server
insert into mysql_servers (hostgroup_id, hostname, port) values(1,'10.xx.yy.z2',3306);
insert into mysql_servers (hostgroup_id, hostname, port) values(1,'10.xx.yy.z3',3306);
insert into mysql_servers (hostgroup_id, hostname, port) values(1,'10.xx.yy.z4',3306);

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

# 测试通过ProxySQL中间件是否可以正常访问
mysql -uproxysql -pLead.China2022 -h 127.0.0.1 -P3306 -e"select @@hostname"


# 5. 配置ProxySQL自动检查组复制状态定时检查作业（ProxySQL集群的每个节点都要手动配置，因为Scheduler不会在集群间同步）
# 具体脚本放在proxysql_groupreplication_checker目录
# proxysql_groupreplication_checker.sh：用于multi-primary模式，可以实现读写分离，以及故障切换，同一时间点多个节点可以多写；
# gr_mw_mode_cheker.sh：用于multi-primary模式，可以实现读写分离，以及故障切换，不过在同一时间点只能有一个节点能写；
# gr_sw_mode_checker.sh：用于single-primary模式，可以实现读写分离，以及故障切换；
# 此处测试single-primary模式，选择gr_sw_mode_checker.sh脚本，丢掉proxysql数据目录/var/lib/proxysql。
chmod a+x /var/lib/proxysql/gr_sw_mode_checker.sh
delete from scheduler;
INSERT INTO scheduler(id,interval_ms,filename,arg1,arg2,arg3,arg4, arg5) VALUES (1,'10000','/var/lib/proxysql/gr_sw_mode_checker.sh','1','3','1','/var/lib/proxysql/gr_sw_mode_checker.log','0');
LOAD SCHEDULER TO RUNTIME;
SAVE SCHEDULER TO DISK;


scheduler各column的说明：
active : 1: enable scheduler to schedule the script we provide
interval_ms : invoke one by one in cycle (eg: 5000(ms) = 5s represent every 5s invoke the script)
filename: represent the script file path
arg1~arg5: represent the input parameters the script received
   
脚本proxysql_groupreplication_checker.sh对应的参数说明如下:
arg1 is the hostgroup_id for write
arg2 is the hostgroup_id for read
arg3 is the number of writers we want active at the same time
arg4 represents if we want that the member acting for writes is also candidate for reads
arg5 is the log file


# 6. 配置读写分离
insert into mysql_query_rules (active, match_pattern, destination_hostgroup, apply) values (1,"^SELECT",3,1);
insert into mysql_query_rules(active,match_pattern,destination_hostgroup,apply) values(1,'^SELECT.*FOR UPDATE$',1,1);
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;

insert into mysql_group_replication_hostgroups(writer_hostgroup,backup_writer_hostgroup,reader_hostgroup,offline_hostgroup,active,writer_is_also_reader,max_transactions_behind) values(1,2,3,4,1,1,1000);
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;

# 查看读写分离执行情况
select hostgroup,username,digest_text,count_star from stats_mysql_query_digest;

ProxySQL节点keepalived服务部署

# 为ProxySQL集群的三个节点部署keepalived服务，提供一个VIP地址，供应用程序客户端通过该VIP地址访问数据库读写分离服务。

# keepalived服务需要在ProxySQL集群的三个节点上都部署，仅有VIP地址的节点才会向客户端提供ProxySQL数据库代理访问服务。

# keepalived服务配置文件及相关脚本参考keepalived_proxysql目录中的文件。

ProxySQL官方监控MGR集群主从的系统视图

ProxySQL官方视图脚本

给ProxySQL的监控用户授予查询权限

grant select on sys.* to monitoring_user;