Mysql中间件Proxysql实现mysql主从架构读写分离

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了Mysql中间件Proxysql实现mysql主从架构读写分离，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含4532字，纯文字阅读大概需要7分钟。

内容图文

• Master: 192.168.0.17
• Slave: 192.168.0.20
• Proxysql:192.168.0.30
  建议关闭防火墙
  建立Master: 192.168.0.17与Slave: 192.168.0.20的MySQL主从复制架构

Proxysql:192.168.0.30主机上基于yum安装proxysql

[root@centos6 ~ 20:21:05]#cat <<EOF | tee /etc/yum.repos.d/proxysql.repo
> [proxysql_repo]
> name= ProxySQL YUM repository
> baseurl=http://repo.proxysql.com/ProxySQL/proxysql-1.4.x/centos/\$releasever
> gpgcheck=1
> gpgkey=http://repo.proxysql.com/ProxySQL/repo_pub_key
> EOF

查看yum源配置是否成功

成功了可以开始安装proxysql
yum install proxysql

用mysql工具进入proxy管理页面

[root@centos6 ~ 20:08:36]#mysql -uadmin -padmin -P6032 -h127.0.0.1

查看proxy的表

对表mysql_servers中插入主从节点信息，加载到RUNTIME，并保存到disk

MySQL [(none)]> insert into mysql_servers(hostgroup_id,hostname,port) values(10,‘192.168.0.17‘,3306);
MySQL[(none)]>insert into mysql_servers(hostgroup_id,hostname,port) values(10,‘192.168.0.20‘,3306);
MySQL [(none)]> load mysql servers to runtime;
MySQL [(none)]> save mysql servers to disk;

添加监控后端节点的用户。ProxySQL通过每个节点的read_only值来自动调整它们是属于读组还是写组

MariaDB [(none)]> grant replication client on *.* to monitor@‘192.168.0.%‘ identified by ‘123‘;
MariaDB [(none)]> flush privileges;

ProxySQL上配置监控用户，加载到RUNTIME，并保存到disk

MySQL [(none)]> set mysql-monitor_username=‘monitor‘;
MySQL [(none)]> set mysql-monitor_password=‘123‘;
MySQL [(none)]> load mysql variables to runtime;
MySQL [(none)]> save mysql variables to disk;

查看监控连接是否正常

MySQL> select * from mysql_server_connect_log;

查看监控心跳信息 (对ping指标的监控)：

MySQL> select * from mysql_server_ping_log;

查看read_only和replication_lag的监控日志

MySQL> select * from mysql_server_read_only_log;
MySQL> select * from mysql_server_replication_lag_log;

设置分组信息：
需要修改的是main库中的mysql_replication_hostgroups表，该表有3个字段：writer_hostgroup，reader_hostgroup，comment, 指定写组的id为10，读组的id为20

MySQL> insert into mysql_replication_hostgroups values(10,20,"test");

读组proxysql会自动根据my.cnf的read_only来判断你是否是读组自动更改
将mysql_replication_hostgroups表的修改加载到RUNTIME生效

MySQL> load mysql servers to runtime;
MySQL> save mysql servers to disk;

Monitor模块监控后端的read_only值，按照read_only的值将节点自动移动到读/写组

MySQL> select hostgroup_id,hostname,port,status,weight from mysql_servers;

在master节点上创建访问用户

MariaDB [(none)]> grant all on *.* to sqluser@‘192.168.0.%‘ identified by ‘123‘;

MariaDB [(none)]> flush privileges;

在slave节点上创建访问用户

MariaDB [(none)]> grant all on *.* to sqluser@‘192.168.0.%‘ identified by ‘123‘;
MariaDB [(none)]> flush privileges;

在ProxySQL配置，将用户sqluser添加到mysql_users表中， default_hostgroup默认组设置为写组10，当读写分离的路由规则不符合时，会访问默认组的数据库

MySQL> insert into mysql_users(username,password,default_hostgroup) values(‘sqluser‘,‘123‘,10);
MySQL> load mysql users to runtime;
MySQL> save mysql users to disk;

使用sqluser用户测试是否能路由到默认的10写组实现读、写数据

mysql -usqluser –p123 -P6033 -h127.0.0.1 -e ‘select @@server_id‘
mysql -usqluser –p123 -P6033 -h127.0.0.1 -e ‘create database testdb‘
mysql -usqluser –p123 testdb -P6033 -h127.0.0.1 -e ‘create table t(id int)‘

在proxysql上配置路由规则，实现读写分离
与规则有关的表：mysql_query_rules和mysql_query_rules_fast_routing，后者是前者的扩展表，1.4.7之后支持
插入路由规则：将select语句分离到20的读组，select语句中有一个特殊语句SELECT...FOR UPDATE它会申请写锁，应路由到10的写组

MySQL> insert into mysql_query_rules
(rule_id,active,match_digest,destination_hostgroup,apply)VALUES
(1,1,‘^SELECT.*FOR UPDATE$‘,10,1),(2,1,‘^SELECT‘,20,1);
MySQL> load mysql query rules to runtime;
MySQL> save mysql query rules to disk;

注意：因ProxySQL根据rule_id顺序进行规则匹配，select ... for update规则的rule_id必须要小于普通的select规则的rule_id

测试读操作是否路由给20的读组

mysql -usqluser -pmagedu -P6033 -h127.0.0.1 -e ‘select @@server_id‘

测试写操作，以事务方式进行测试

mysql -usqluser -pmagedu -P6033 -h127.0.0.1 -e ‘start transaction;select @@server_id;commit;select @@server_id‘

路由的信息：查询stats库中的stats_mysql_query_digest表

MySQL > SELECT hostgroup hg,sum_time, count_star, digest_text FROM stats_mysql_query_digest ORDER BY sum_time DESC;

Mysql中间件Proxysql实现mysql主从架构读写分离

标签：data   测试   ups   digest   run   进入   sql   monit   ext   

本文系统来源：https://blog.51cto.com/14233913/2391050

内容总结

以上是互联网集市为您收集整理的Mysql中间件Proxysql实现mysql主从架构读写分离全部内容，希望文章能够帮你解决Mysql中间件Proxysql实现mysql主从架构读写分离所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端