AI 助理
备案控制台
开发者社区 数据库 文章 正文

ProxySQL MySQL MGR8配置

2018-09-17 4122
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
RDS MySQL Serverless 基础系列，0.5-2RCU 50GB
推荐场景：
学生管理系统数据库设计 搭建个人博客
云数据库 RDS MySQL，集群系列 2核4GB
推荐场景：
搭建个人博客
云数据库 RDS MySQL，高可用系列 2核4GB
简介： 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查看数据是否都存在即可！这里就不再进行测试！

文章标签：
云数据库 RDS MySQL 版
关系型数据库
MySQL
测试技术
容器
Docker
关键词：
云数据库 RDS MySQL 版配置
proxysql云数据库 RDS MySQL 版
相关实践学习
如何在云端创建MySQL数据库
开始实验后，系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心，利用清洁计算为万物互联的新世界提供源源不断的能源动力，目前开服的区域包括中国（华北、华东、华南、香港）、新加坡、美国（美东、美西）、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程，来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品：云服务器ECS 云服务器 ECS（Elastic Compute Service）是一种弹性可伸缩的计算服务，助您降低 IT 成本，提升运维效率，使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
split_two
目录
相关文章
小王老师呀
|
1月前
|
SQL 关系型数据库 MySQL
mysql主从复制概述和配置
【10月更文挑战第22天】MySQL 主从复制是一种将主服务器的数据复制到一个或多个从服务器的技术，实现读写分离，提高系统性能和可用性。主服务器记录变更日志，从服务器通过 I/O 和 SQL 线程读取并应用这些变更。适用于读写分离、数据备份和恢复、数据分析等场景。配置步骤包括修改配置文件、创建复制用户、配置从服务器连接主服务器并启动复制进程。
小王老师呀
60 1
pbeskoyq7ffm4
|
3月前
|
存储 SQL 关系型数据库
MySQL体系结构与配置
MySQL体系结构与配置
pbeskoyq7ffm4
53 0
肾透侧视攻城狮
|
23天前
|
存储 SQL 关系型数据库
2024Mysql And Redis基础与进阶操作系列（1）作者——LJS[含MySQL的下载、安装、配置详解步骤及报错对应解决方法]
Mysql And Redis基础与进阶操作系列（1）之[MySQL的下载、安装、配置详解步骤及报错对应解决方法]
肾透侧视攻城狮
40 3
众所周知
|
24天前
|
关系型数据库 MySQL Linux
在 CentOS 7 中通过编译源码方式安装 MySQL 数据库的详细步骤，包括准备工作、下载源码、编译安装、配置 MySQL 服务、登录设置等。
本文介绍了在 CentOS 7 中通过编译源码方式安装 MySQL 数据库的详细步骤，包括准备工作、下载源码、编译安装、配置 MySQL 服务、登录设置等。同时，文章还对比了编译源码安装与使用 RPM 包安装的优缺点，帮助读者根据需求选择最合适的方法。通过具体案例，展示了编译源码安装的灵活性和定制性。
众所周知
73 2
互联网课堂
|
29天前
|
SQL 关系型数据库 MySQL
Mysql中搭建主从复制原理和配置
主从复制在数据库管理中广泛应用，主要优点包括提高性能、实现高可用性、数据备份及灾难恢复。通过读写分离、从服务器接管、实时备份和地理分布等机制，有效增强系统的稳定性和数据安全性。主从复制涉及I/O线程和SQL线程，前者负责日志传输，后者负责日志应用，确保数据同步。配置过程中需开启二进制日志、设置唯一服务器ID，并创建复制用户，通过CHANGE MASTER TO命令配置从服务器连接主服务器，实现数据同步。实验部分展示了如何在两台CentOS 7服务器上配置MySQL 5.7主从复制，包括关闭防火墙、配置静态IP、设置域名解析、配置主从服务器、启动复制及验证同步效果。
互联网课堂
31 0
Mysql中搭建主从复制原理和配置
杀死一只知更鸟debug
|
2月前
|
关系型数据库 MySQL 数据安全/隐私保护
docker应用部署---MySQL的部署配置
这篇文章介绍了如何使用Docker部署MySQL数据库，包括搜索和拉取MySQL镜像、创建容器并设置端口映射和目录映射、进入容器操作MySQL，以及如何使用外部机器连接容器中的MySQL。
杀死一只知更鸟debug
553 1
docker应用部署---MySQL的部署配置
java冯坚持
|
1月前
|
关系型数据库 MySQL Java
Django学习二：配置mysql，创建model实例，自动创建数据库表，对mysql数据库表已经创建好的进行直接操作和实验。
这篇文章是关于如何使用Django框架配置MySQL数据库，创建模型实例，并自动或手动创建数据库表，以及对这些表进行操作的详细教程。
java冯坚持
66 0
Django学习二：配置mysql，创建model实例，自动创建数据库表，对mysql数据库表已经创建好的进行直接操作和实验。
游客j4mujezz7vm7y
|
2月前
|
关系型数据库 MySQL Go
go抽取mysql配置到yaml配置文件
go抽取mysql配置到yaml配置文件
游客j4mujezz7vm7y
42 4
胡东旭
|
3月前
|
弹性计算 关系型数据库 MySQL
centos7 mysql安装及配置
本文详细介绍了在阿里云服务器ECS上通过yum源安装MySQL 8.0.12的过程，包括更新yum源、下载并安装MySQL源、解决安装过程中可能遇到的问题等步骤。此外，还介绍了如何启动MySQL服务、设置开机自启、配置登录密码、添加远程登录用户以及处理远程连接异常等问题。适合初学者参考，帮助快速搭建MySQL环境。
胡东旭
468 8
centos7 mysql安装及配置
蓝易云
|
2月前
|
关系型数据库 MySQL Unix
MySQL配置不区分大小写的方法
结论 通过适当配置 lower_case_table_names参数以及在数据定义和查询中选择合适的校对规则，可以灵活地控制MySQL中的大小写敏感性，以适应不同的应用场景和需求。这样的设置既可以增加数据库的兼容性，又可以在必要时利用大小写敏感性进行精确的数据处理。需要注意的是，修改 lower_case_table_names参数后，最好在数据库初始化时进行，以避免现有表名的大小写问题。
蓝易云
315 3

热门文章

最新文章

  • 1
    测试开启MySQL performance_schema后对性能的影响
  • 2
    RDS入门——Excel文件转存到RDS数据库实践
  • 3
    C语言连接MySQL数据库
  • 4
    JDBC操作MySQL5日期类型字段的问题解决方法
  • 5
    MySQL的innodb_flush_log_at_trx_commit配置值的设定
  • 6
    PG+MySQL第11课-多维向量搜索
  • 7
    FAQ系列 | 启用SELinux后，PHP连接MySQL异常
  • 8
    Mariadb/Mysql TEMPORARY表
  • 9
    云原生数据仓库产品使用合集之ADB MySQL湖仓版和 StarRocks 的使用场景区别，或者 ADB 对比 StarRocks 的优劣势
  • 10
    在华为鲲鹏OpenEuler20.03系统上安装MySQL8
  • 1
    服务器Linux系统配置mysql数据库主从自动备份
    202
  • 2
    mysqlTools 一分钟部署安装本mysql多个版本，解锁繁琐部署过程
    413
  • 3
    golang使用gorm操作mysql3，数据查询
    90
  • 4
    golang使用gorm操作mysql2
    55
  • 5
    golang使用gorm操作mysql1
    102
  • 6
    Hbase与MySQL对比，区别是什么？
    278
  • 7
    解决 mysql8.0 ERROR 1045 (28000): Access denied for user ‘ODBC‘@‘localhost‘ (using password: NO)用户访问拒绝
    2097
  • 8
    navicat过期了，直接用idea连接mysql
    58
  • 9
    DQL操作MySql
    49
  • 10
    【JAVA进阶篇教学】第八篇：Java链接MySql数据库异常
    249

    • 相关课程

    更多
  • MySQL企业常见架构与调优经验分享
  • 云数据库MySQL版快速上手教程
  • 阿里云云原生数据仓库AnalyticDB MySQL版 使用教程
  • MySQL实战进阶
  • 数据库及SQL/MySQL基础
  • 云数据库MySQL快速入门

    • 相关电子书

    更多
  • 搭建电商项目架构连接MySQL
  • 搭建4层电商项目架构，实战连接MySQL
  • PolarDB MySQL引擎重磅功能及产品能力盛大发布

    • 相关实验场景

    更多
  • MySQL基础-学生管理系统数据库设计
  • 如何快速连接云数据库RDS MySQL
  • MySQL数据库快速部署实践
  • MySQL引擎及架构优化
  • 基于EBS部署高性能的MySQL服务
  • 使用CloudLens采集RDS日志并进行审计分析

    • 推荐镜像

    更多
  • mysql
  • mariadb
  • postgresql
    • 下一篇
    无影云桌面