ProxySQL MySQL MGR8配置

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群版 2核4GB 100GB
推荐场景:
搭建个人博客
云数据库 RDS MySQL,高可用版 2核4GB 50GB
简介: ProxySQL MySQL8.0.12 MGR

上一篇博文记录了如何使用MySQL8.0.12部署mgr集群(单主模式或者多主模式都可以),那么在实际环境中应用如何去连接mgr集群呢?再比如单主模式如何实现读写分离呢?用代理软件需要配合各种脚本太麻烦,修改程序对开发是个考验,维护方面不是很方便。正好听说ProxySQL新版本1.4在最新的功能性增强,包含了对MGR的原生支持,不在需要使用第三方脚本进行配合使用。那么这一点对运维人员也是最大的福音。网上查了很多文档,包括官方文档等等,看的云里雾里的。说还得用什么sql存储过程。感觉都是坑。个人觉得没有一篇文档能有完全的参考价值。这里意思是说不能完全照着一篇文章做下来,综合了多个文档一天半才搞定。说多了全是眼泪~~~希望看到我这篇博客的小伙伴你们不会有此想法。
前期准备工作:
1)mysql8.0.12 mgr集群(一主两从)配置完成,172.17.0.5是主节点。不会配置的请参考上一篇文章。
image
2)再准备一台服务器,需要能通互联网。另外和mgr集群一个网段。
3)关闭所有服务器的防火墙。
开始工作:
1、proxysql的安装(使用最新稳定版本1.4.11)
既然mgr集群是基于docker环境上部署,那么再用docker创建一个proxysql容器
docker run -d -it --privileged --name=proxysql centos /usr/sbin/init
docker exec -it proxysql bash
rpm -ivh https://dev.mysql.com/get/mysql80-community-release-el7-1.noarch.rpm
yum-config-manager --disable mysql80-community
yum-config-manager --enable mysql57-community
yum install perl perl-DBD-mysql mysql -y
wget https://github.com/sysown/proxysql/releases/download/v1.4.11/proxysql-1.4.11-1-centos7.x86_64.rpm
rpm -ivh proxysql-1.4.11-1-centos7.x86_64.rpm
systemctl start proxysql
2、proxysql需要和mgr集群通信,那么肯定是通过用户,这里在mgr主节点上创建两个用户,一个监控用户用于取数据来判断mgr组里面mysql的读写状态,另外一个业务用户用于最后测试读写分离效果。
mysql> create user monitor@'%' identified by 'Monitor@123';
mysql> grant select on sys.* to monitor@'%';
mysql> create user test@'%' identified by 'Test@123';
mysql> grant all on *.* to test@'%';
mysql> FLUSH PRIVILEGES;
本来应该到这可以了。但是proxysql内置的mysql是8之前的版本,而mgr集群中的mysql是8.0.12版本。采用的是默认认证方式是caching_sha2_password,两者的身份认证不同。所以为了让mysql8能兼容新老版本的认证方式需要为上述创建的两个用户执行以下命令。这个问题卡了我很长时间,至少我看过的文档里面没有一个人提到这个问题。不然后续proxysql连接mgr会报错:
error 2059: Authentication plugin 'caching_sha2_password' cannot be loaded: /usr/lib64/mysql/plugin/caching_sha2_password.so: cannot open shared object file: No such file or directory
mysql> ALTER USER 'monitor'@'%' IDENTIFIED BY 'Monitor@123' PASSWORD EXPIRE NEVER;
mysql> alter user 'monitor'@'%' IDENTIFIED WITH mysql_native_password BY 'Monitor@123';
mysql> ALTER USER 'test'@'%' IDENTIFIED BY 'Test@123' PASSWORD EXPIRE NEVER;
mysql> alter user 'test'@'%' IDENTIFIED WITH mysql_native_password BY 'Test@123';
mysql> FLUSH PRIVILEGES;
到此mgr集群就不需要再做什么配置了。
3、配置proxysql,其实有两种方法,第一种是动态配置,也就是sql语句的这种。第二种是通过proxysql.cnf文件配置(个人觉得配置文件简单,不用敲那么多命令,但是不尝试第一种动态配置,或许你用配置文件可能不是那么很好理解proxysql的实现过程)。所以还是先看第一种吧!proxysql有两个端口,一个是管理端口6032,一个是业务连接端口6033。
mysql -u admin -padmin -h 127.0.0.1 -P6032
MySQL > \R Admin> 进入管理员模式
插入mgr集群的三个节点信息,组id都设置一样。
Admin>INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (1,'172.17.0.5',3306);
Admin>INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (1,'172.17.0.6',3306);
Admin>INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (1,'172.17.0.7',3306);
Admin>select * from mysql_servers;
image
更新proxysql的监控用户和密码及各种时间
Admin>UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_username';
Admin>UPDATE global_variables SET variable_value='Monitor@123' WHERE variable_name='mysql-monitor_password';
Admin>UPDATE global_variables SET variable_value='2000' WHERE variable_name IN ('mysql-monitor_connect_interval','mysql-monitor_ping_interval','mysql-monitor_read_only_interval');
进行验证
Admin>SELECT * FROM global_variables WHERE variable_name LIKE 'mysql-monitor_%';
image
将配置写入运行中,然后再保存到磁盘
Admin>LOAD MYSQL VARIABLES TO RUNTIME;
Admin>SAVE MYSQL VARIABLES TO DISK;
验证是否可以检测到后端mgr集群并获取数据
Admin>SELECT * FROM monitor.mysql_server_connect_log ORDER BY time_start_us DESC LIMIT 10;
image
Admin>SELECT * FROM monitor.mysql_server_ping_log ORDER BY time_start_us DESC LIMIT 10;
image
Admin>LOAD MYSQL SERVERS TO RUNTIME;
再查看一下第一步插入的后端mgr集群数据,似乎没什么变化
Admin>SELECT * FROM mysql_servers;
image
现在对后端的mgr集群进行分组,1为写,2为读,名称为cluster1
Admin>INSERT INTO mysql_replication_hostgroups VALUES (1,2,'cluster1');
Admin>LOAD MYSQL SERVERS TO RUNTIME;
从proxysql验证读写状态,已经区分很明显了
Admin>SELECT * FROM monitor.mysql_server_read_only_log ORDER BY time_start_us DESC LIMIT 10;
image
再查一下状态,貌似发生了改变,proxysql 已经将172.17.0.5划到了写组,172.17.0.6和172.17.0.7划到了读组
Admin>SELECT * FROM mysql_servers;
image
Admin>SAVE MYSQL SERVERS TO DISK;
Admin>SAVE MYSQL VARIABLES TO DISK;
这样就基本完成了proxysql配置。接下来我们去验证读写分离效果
插入用户名、密码、组
Admin>insert into mysql_users(username,password,default_hostgroup) values('test','Test@123',1);
插入读写分离规则,1是写,2是读,千万别弄混了。以select开头的请求到2组,但是还有一种请求是查询后更新的语句,这种要配置请求到1组
Admin>insert into mysql_query_rules(rule_id,active,username,match_digest,destination_hostgroup,apply) values(1,1,'test','^SELECT.*FRO UPDATE$',1,1),(2,1,'test','^SELECT',2,1);
Admin>load mysql users to runtime;
Admin>save mysql users to disk;
Admin>load mysql query rules to runtime;
Admin>save mysql query rules to disk;
退出管理员模式,在proxysql上测试读写分离
mysql -utest -pTest@123 -h 127.0.0.1 -P6033 -e 'create database test;'
mysql -utest -pTest@123 -h 127.0.0.1 -P6033 -e 'show databases;'
mysql -utest -pTest@123 -h 127.0.0.1 -P6033 -e 'select user,host from mysql.user;'
进入proxysql查看读写分离效果,不需要进管理模式即可
mysql -u admin -padmin -h 127.0.0.1 -P6032
mysql> SELECT * from stats_mysql_query_digest;
image
从上图看到create和show语句请求的是写,select请求的从,这样就完成了读写分离测试。
还有另外一个问题我们需要进行测试,当mgr主节点故障,新选举的主节点会不会被proxysql识别到?
手动停止mgr当前的主库
systemctl stop mysqld
随便进入剩下两个节点中的一个查看当前的主
mysql> select * from performance_schema.replication_group_members;
image
从图中我们没法看到IP,只能看到主机名,肯定是发生了切换,当前主是172.17.0.6
再进入proxysql查看读到的mgr状态
mysql> select hostgroup_id,hostname,port,status from runtime_mysql_servers;
image
已经看到172.1.0.5状态是SHUNNED,当前hostgroup_id为1的主是172.17.0.6
再次写入数据测试
mysql -utest -pTest@123 -h 127.0.0.1 -P6033 -e 'create database test1;'
在proxysql上进行查看是否创建成功
mysql -utest -pTest@123 -h 127.0.0.1 -P6033 -e 'show databases;'
image
test1库正常创建。到此第一种动态配置方法完成。
下面开始通过配置文件读取,然后也方便做到docker容器里面。配置文件内容如下:
cat /etc/proxysql.cnf

datadir="/var/lib/proxysql"
admin_variables=
{

admin_credentials="admin:admin"
mysql_ifaces="0.0.0.0:6032"

}

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"
default_schema="information_schema"
stacksize=1048576
server_version="5.5.30"
connect_timeout_server=3000
monitor_username="monitor"
monitor_password="Monitor@123"
monitor_history=600000
monitor_connect_interval=2000
monitor_ping_interval=2000
monitor_read_only_interval=2000
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

}

mysql_servers =
(

{ address="172.17.0.5" , port=3306 , hostgroup=1, max_connections=1000 },
{ address="172.17.0.6" , port=3306 , hostgroup=1, max_connections=1000 },
{ address="172.17.0.7" , port=3306 , hostgroup=1, max_connections=1000 }

)

mysql_users=
(

{ username = "test" , password = "Test@123" , default_hostgroup = 1 , active = 1 }

)

mysql_query_rules=
(

{
    rule_id=1
    active=1
    match_pattern="^SELECT .* FOR UPDATE$"
    destination_hostgroup=1
    apply=1
},
{
    rule_id=2
    active=1
    match_pattern="^SELECT"
    destination_hostgroup=2
    apply=1
}

)

mysql_replication_hostgroups=
(

    {
            writer_hostgroup=1
            reader_hostgroup=2
            comment="cluster1"
   }

)`
其实就是把第一种方法里面配置的内容都写到proxysql.cnf文件里面。第二种静态配置文件测试方法很简单,删掉/var/lib/proxysql目录下db结尾的文件,然后重启proxysql服务。最后进入proxysql查看数据是否都存在即可!这里就不再进行测试!

相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
1月前
|
SQL 关系型数据库 MySQL
MySQL Workbench的安装与配置
MySQL Workbench的安装与配置
|
10天前
|
运维 关系型数据库 MySQL
【实操记录】MySQL主从配置
本文使用MySQL原生支持的主从同步机制,详细记录了配置步骤及运维操作方法,可供大家直接参考、使用。 本文假设已经部署了两台主机的MySQL软件,且数据库服务正常,详细部署步骤可本站搜索:"mysql二进制安装包部署"
20 0
|
20天前
|
XML Java 关系型数据库
Action:Consider the following: If you want an embedde ,springBoot配置数据库,补全springBoot的xml和mysql配置信息就好了
Action:Consider the following: If you want an embedde ,springBoot配置数据库,补全springBoot的xml和mysql配置信息就好了
|
19天前
|
SQL 关系型数据库 MySQL
【MySQL】主从异步复制配置
【MySQL】主从异步复制配置
18 1
|
24天前
|
关系型数据库 MySQL 数据库
使用ZIP包安装MySQL及配置教程
使用ZIP包安装MySQL及配置教程
35 4
|
24天前
|
安全 关系型数据库 MySQL
CentOS 8 中安装与配置 MySQL
CentOS 8 中安装与配置 MySQL
95 3
|
26天前
|
关系型数据库 MySQL 数据库
Django与MySQL:配置数据库的详细步骤
Django与MySQL:配置数据库的详细步骤
|
3天前
|
分布式计算 DataWorks 关系型数据库
MaxCompute操作报错合集之配置mysql数据源querysql模式,同步到MC时遇到报错,该怎么处理
MaxCompute是阿里云提供的大规模离线数据处理服务,用于大数据分析、挖掘和报表生成等场景。在使用MaxCompute进行数据处理时,可能会遇到各种操作报错。以下是一些常见的MaxCompute操作报错及其可能的原因与解决措施的合集。
|
5天前
|
SQL DataWorks 关系型数据库
DataWorks产品使用合集之如何配置MySQL数据源
DataWorks作为一站式的数据开发与治理平台,提供了从数据采集、清洗、开发、调度、服务化、质量监控到安全管理的全套解决方案,帮助企业构建高效、规范、安全的大数据处理体系。以下是对DataWorks产品使用合集的概述,涵盖数据处理的各个环节。
|
6天前
|
存储 SQL 关系型数据库
实时计算 Flink版产品使用问题之要配置MySQL集群存储节点,该如何配置
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。