基于proxysql实现mysql读写分离

发表于 | 阅读次数:
字数统计: 1.4k | 阅读时长 ≈ 6

读写分离应用：

1
2
3
4
5
6
7
8
mysql-proxy：Oracle，https://downloads.mysql.com/archives/proxy/
Atlas：Qihoo，https://github.com/Qihoo360/Atlas/blob/master/README_ZH.md
dbproxy：美团，https://github.com/Meituan-Dianping/DBProxy
Cetus：网易乐得，https://github.com/Lede-Inc/cetus
Amoeba：https://sourceforge.net/projects/amoeba/
Cobar：阿里巴巴，Amoeba的升级版
Mycat：基于Cobar， http://www.mycat.io/
ProxySQL：https://proxysql.com/

ProxySQL

ProxySQL：MySQL中间件，两个版本：官方版和percona版，percona版是基于官方版基础上修改，C++语言开发，轻量级但是性能优异（支持处理千亿级数据），具有中间件所需的绝大多数功能，包括：

1
2
3
4
多种方式的读/写分离
定制基于用户、基于schema、基于语句的规则对SQL语句进行路由
缓存查询结果
后端节点监控

官方站点：https://proxysql.com/
官方手册：https://github.com/sysown/proxysql/wiki

proxysql安装方法

  1. 基于yum安装proxysql

    1
    2
    3
    4
    5
    6
    7
    [root@node01 ~]#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

  2. 基于RPM下载安装
    https://github.com/sysown/proxysql/releases

ProxySQL组成

1
2
3
服务脚本：/etc/init.d/proxysql
配置文件：/etc/proxysql.cnf
主程序：/usr/bin/proxysql

proxy实现读写分离

  1. 实现读写分离之前，先实现主从复制
    注意：slave节点需要设置read_only=1

  2. 启动ProxySQL

    1
    [root@node01 ~]#service proxysql start

    启动后，会默认监听两个端口
    6032：proxysql的管理端口
    6033：proxysql对外提供服务的端口

  3. 使用mysql客户端连接到proxysql的管理端口6032，默认管理员用户和密码都是admin：

    1
    [root@node01 ~]#mysql -uadmin -padmin -P6032 -h127.0.0.1

    说明：在main和monitor数据库中的表， runtime_开头的是运行时的配置，不能修改，只能修改非runtime_表，修改后必须执行LOAD … TO RUNTIME才能加载到RUNTIME生效，执行save … to disk将配置持久化保存到磁盘

  4. 向proxysql中添加所有mysql节点，以下操作不需要use main也可成功

    1
    2
    3
    4
    5
    6
    7
    MySQL > select * from mysql_servers;
    MySQL > insert into mysql_servers(hostgroup_id,hostname,port)
    values(10,'192.168.34.17',3306);
    MySQL > insert into mysql_servers(hostgroup_id,hostname,port)
    values(10,'192.168.34.27',3306);
    MySQL > load mysql servers to runtime;
    MySQL > save mysql servers to disk;

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

在master上添加监控用户

1
MySQL > grant replication client on *.* to monitor@'192.168.34.%' identified by 'centos';

在proxysql上配置监控用户

1
2
MySQL [(none)]> set mysql-monitor_username='monitor';
MySQL [(none)]> set mysql-monitor_password='magedu';

加载到RUNTIME，并保存到disk

1
2
MySQL [(none)]> load mysql variables to runtime;
MySQL [(none)]> save mysql variables to disk;

此时，监控模块的指标保存在monitor库的log表中
查看监控连接是否正常（对connect指标的监控）：（如果connect_error的结果为NULL则表示正常）

1
mysql> select * from mysql_server_connect_log;

查看监控心跳信息（对ping指标的监控）：

1
mysql> select * from mysql_server_ping_log;

查看read_only和replication_lag的监控日志：(此时应该为空)

1
2
mysql> select * from mysql_server_read_only_log;
mysql> select * from mysql_server_replication_lag_log;
  1. 设置分组信息
    需要修改的是main库中的mysql_replication_hostgroups表，该表有3个字段：
    writer_hostgroup，reader_hostgroup，comment, 指定写组的id为10，读组的id为20
    1
    mysql > insert into mysql_replication_hostgroups values(10,20,"test");

将mysql_replication_hostgroups表的修改加载到RUNTIME生效

1
2
mysql > load mysql servers to runtime;
mysql > save mysql servers to disk;

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

1
2
3
4
5
6
mysql > select hostgroup_id,hostname,port,status,weight from mysql_servers;
+--------------+--------------+------+--------+--------+
| hostgroup_id | hostname | port | status | weight |
+--------------+--------------+------+--------+--------+
| 10 | 192.168.34.17 | 3306 | ONLINE | 1 |
| 20 | 192.168.34.27 | 3306 | ONLINE | 1 |
  1. 配置发送sql语句的用户

在master节点上创建访问用户

1
mysql > grant all on *.* to sqluser@'192.168.8.%' identified by 'centos';

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

1
2
3
mysql > insert into mysql_users(username,password,default_hostgroup) values('sqluser','magedu',10);
mysql > load mysql users to runtime;
mysql > save mysql users to disk;

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

1
2
3
4
[root@node01 ~]#mysql -usqluser -pmagedu -P6033 -h127.0.0.1 -e 'select @@server_id'
[root@node01 ~]#mysql -usqluser -pmagedu -P6033 -h127.0.0.1 -e 'create database testdb'
[root@node01 ~]#mysql -usqluser -pmagedu -P6033 -h127.0.0.1 -e 'use testdb;create table t(id
int)
  1. 配置路由规则，实现读写分离
    与规则有关的表：mysql_query_rules和mysql_query_rules_fast_routing，后者是前者的扩展表，1.4.7之后支持

插入路由规则：将select语句分离到20的读组，select语句中有一个特殊语句SELECT…FOR UPDATE它会申请写锁，应路由到10的写组

1
2
3
4
5
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

至此，已经实现mysql的读写分离

  1. 测试读写分离是否成功
    测试读操作是否路由给20的读组
    1
    [root@node01 ~]#mysql -usqluser -pmagedu -P6033 -h127.0.0.1 -e 'select @@server_id'

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

1
2
3
[root@node01 ~]#mysql -usqluser -pmagedu -P6033 -h127.0.0.1 -e 'start transaction;select @@server_id;commit;select @@server_id'
[root@node01 ~]#mysql -usqluser -pmagedu -P6033 -h127.0.0.1 -e 'insert testdb.t values (1)'
[root@node01 ~]#mysql -usqluser -pmagedu -P6033 -h127.0.0.1 -e 'select id from testdb.t'

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

1
mysql > SELECT hostgroup hg,sum_time, count_star, digest_text FROM stats_mysql_query_digest ORDER BY sum_time DESC;
分享到： 收藏夹 复制网址 邮件 微信 QQ空间 腾讯微博 豆瓣 一键分享 更多