跳至主要內容

MySQL MGR+ProxySQL+Keepalived集群搭建

大约 5 分钟约 1428 字

MySQL MGR+ProxySQL+Keepalived集群搭建

MySQL MGR+ProxySQL+Keepalived集群节点规划

节点名称软件包节点IP地址
n1MySQL（8.0.28）、ProxySQL（2.4.1）、Keepalived（2.1.5）10.30.162.51
n2MySQL（8.0.28）、ProxySQL（2.4.1）、Keepalived（2.1.5）10.30.162.52
n3MySQL（8.0.28）、ProxySQL（2.4.1）、Keepalived（2.1.5）10.30.162.53

MySQL MGR集群部署

参考文档 MySQL80搭建MGR集群文档

ProxySQL集群部署

参考文档 ProxySQL读写分离配置 open in new window

# 预期效果
# 三个MySQL MGR集群节点上都安装ProxySQL数据库代理服务，这些ProxySQL节点组成ProxySQL高可用集群，同时这三个节点上都安装keepalived服务组成集群提供一个VIP地址，用于为应用程序提供统一的访问入口。ProxySQL Cluster各节点间的配置可自动同步。每次配置操作仅需要在一个ProxySQL节点上执行配置变更修改。

# proxysql软件安装（三个节点都需要安装）
# 1. 下载proxysql安装包
wget -q -c http://download-soft.lead.cn/MySQL/linux/proxysql-2.4.1-1-centos7.x86_64.rpm
# 2. 确保操作系统已正常配置基础镜像yum源，执行proxysql软件包安装操作
yum localinstall -y proxysql-2.4.1-1-centos7.x86_64.rpm
# 3. 修改proxysql配置，添加配置账号及cluster配置
# 修改配置文件/etc/proxysql.cnf中的如下配置
datadir="/var/lib/proxysql"
errorlog="/var/lib/proxysql/proxysql.log"
# proxysql管理接口相关变量配置
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"   # proxysql节点管理端口
        refresh_interval=2000
        debug=true
        cluster_username="cluster_admin"
        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=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:3309"    # 应用程序访问端口
        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="monitor"   # 监控账号
        monitor_password="monitor"   # 监控密码
        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.30.162.55"
       port=6032    # proxysql节点管理端口
       weight=1
       comment="ProxySQL-n1"
    },
    {
       hostname="10.30.162.56"
       port=6032    # proxysql节点管理端口
       weight=1
       comment="ProxySQL-n2"
    },
    {
       hostname="10.30.162.57"
       port=6032    # proxysql节点管理端口
       weight=1
       comment="ProxySQL-n3"
    }
)

# 3. 同步proxysql配置/etc/prxysql.cnf到另外两个节点
# 4. 启动三个节点上的proxysql服务
systemctl start proxysql
systemctl enable proxysql

ProxySQL管理接口配置MGR集群读写分离操作

# 仅需登录ProxySQL Cluster的三个节点中的一个进行操作即可，配置会自动同步至另外两个节点上
# 1. 使用mysql客户端登录proxysql的配置管理接口
mysql -uadmin -padmin -h127.0.0.1 -P6032
# 在配置管理接口中可以使用SQL方式来查询和修改配置
select * from proxysql_servers;
select * from stats_proxysql_servers_metrics;
select hostname,port,comment,Uptime_s,last_check_ms from stats_proxysql_servers_metrics; 
select hostname,name,checksum,updated_at from stats_proxysql_servers_checksums;

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

# 3. ProxySQL官方视图脚本
# 执行./gr_member_routing_candidate_status.sql中创建官方视图脚本

# 4. 在MGR集群的主节点上创建ProxySQL监控数据库状态的数据库用户
CREATE USER 'proxysql'@'%' IDENTIFIED BY 'proxysql'; 
GRANT ALL ON * . * TO  'proxysql'@'%';
FLUSH PRIVILEGES; 

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

# 6. 在ProxySQL集群的任意节点配置后端MySQL Server
insert into mysql_servers (hostgroup_id, hostname, port) values(1,'10.30.162.55',3306);
insert into mysql_servers (hostgroup_id, hostname, port) values(1,'10.30.162.56',3306);
insert into mysql_servers (hostgroup_id, hostname, port) values(1,'10.30.162.57',3306);

insert into mysql_group_replication_hostgroups(writer_hostgroup,backup_writer_hostgroup,reader_hostgroup,offline_hostgroup,active) values(1,2,3,4,1);

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;

# 7. 配置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/proxysql_groupreplication_checker.sh','1','3','1','/var/lib/proxysql/proxysql_groupreplication_checker.log','');
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

# 7. 配置组复制主机组
insert into mysql_group_replication_hostgroups 
(writer_hostgroup,backup_writer_hostgroup,reader_hostgroup, offline_hostgroup,active,max_writers,writer_is_also_reader,max_transactions_behind) 
values (1,2,3,4,1,1,0,100);


# 8. 配置读写分离
insert into mysql_query_rules (active, match_pattern, destination_hostgroup, apply) values (1,"^SELECT",2,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;

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

部署keepalived服务提供统一代理入口

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

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

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