网易cetus之读写分离

一、 简介

Cetus是由C语言开发的关系型数据库MySQL的中间件，主要提供了一个全面的数据库访问代理功能。Cetus连接方式与MySQL基本兼容，应用程序几乎不用修改即可通过Cetus访问数据库，实现了数据库层的水平扩展和高可用。
Cetus由网易乐得专家技术团队领队，经多部门协调合作设计出来的优秀中间件。

具体详细可参考官网：https://github.com/Lede-Inc/cetus

本文主要基于读写分离的实践

二、 读写分离的搭建

1、安装依赖

yum install cmake gcc glib2-devel flex libevent-devel mysql-devel gperftools-libs -y

2、下载源码

git clone https://github.com/Lede-Inc/cetus.git

3、编译安装

1、在源码主目录下创建独立的目录build，并转到该目录下mkdir build/cd build/2、编译：利用cmake进行编译，指令如下cmake ../ -DCMAKE_BUILD_TYPE=Debug -DCMAKE_INSTALL_PREFIX=/data/cetus/cetus_install -DSIMPLE_PARSER=ON3、安装：执行make install进行安装make install

4、配置文件

cd /data/cetus/cetus_install/conf/cp XXX.json.example XXX.jsoncp XXX.conf.example XXX.confvi XXX.jsonvi XXX.conf本文基于读写分离主要配置两个文件users.json和proxy.conf1、cp users.json.example  users.json && vim users.json{    "users":    [{            "user": "root",            "client_pwd":   "123",            "server_pwd":   "123456"        }]}2、cp proxy.conf.example proxy.conf && vim proxy.conf[cetus]# For mode-switchdaemon = true# Loaded Pluginsplugins=proxy,admin# Proxy Configuration, For eaxmlpe: MySQL master and salve host ip are both 192.0.0.1proxy-address=127.0.0.1:6001proxy-backend-addresses=192.168.142.48:5700proxy-read-only-backend-addresses=192.168.142.49:5700# Admin Configurationadmin-address=127.0.0.1:7001admin-username=adminadmin-password=admin# Backend Configuration, use test db and username createddefault-db=testdefault-username=root# File and Log Configuration, put log in /data and marked by proxy port, /data/cetus needs to be created manually and has rw authority for cetus os userpid-file = cetus6001.pidplugin-dir=lib/cetus/pluginslog-file=cetus_6001.loglog-level=debug# support the MGRgroup-replication-mode = 1

5、启动

./bin/cetus --defaults-file=conf/proxy.conf --conf-dir=/data/cetus/cetus_install/conf/

6、命令行登录

1、应用端口usr/local/mysql-5.7.18/bin/mysql --prompt="proxy> " --comments -uroot -p123 -h227.0.0.1 -P6001proxy> select version();+------------+| version()  |+------------+| 5.7.18-log |+------------+1 row in set (0.00 sec)2、管理端口/usr/local/mysql-5.7.18/bin/mysql --prompt="admin> " --comments -uadmin -padmin -h227.0.0.1 -P7001admin> select * from backends;+-------------+--------------------+-------+------+-------------+------+------------+------------+-------------+| backend_ndx | address            | state | type | slave delay | uuid | idle_conns | used_conns | total_conns |+-------------+--------------------+-------+------+-------------+------+------------+------------+-------------+| 1           | 192.168.142.48:5700 | up    | rw   | NULL        | NULL | 2          | 0          | 2           || 2           | 192.168.142.49:5700 | up    | ro   | 0           | NULL | 2          | 0          | 2           |+-------------+--------------------+-------+------+-------------+------+------------+------------+-------------+2 rows in set (0.00 sec)

三、 MGR的读写分离测试

部署环境

MGR环境192.168.142.48为MGR主节点192.168.142.49为MGR从节点写入节点配置proxy-backend-addresses=192.168.142.48:5700读取节点配置proxy-read-only-backend-addresses=192.168.142.49:5700

1、测试读：

1.1、主从组复制正常
proxy> select sleep(15) from t1 where a=1;

192.168.142.49上出现对应读请求进程

1.2、主从组复制异常
192.168.142.49停止组复制
root@192.168.142.49> STOP GROUP_REPLICATION;

proxy> select sleep(15) from t1 where a=1;

192.168.142.48上出现对应读请求进程

2、测试写：

2.1、主从组复制正常
proxy> insert into t1 select 6, sleep(15);

192.168.142.48上出现对应写请求进程

2.2、主从组复制异常
192.168.142.49停止组复制
root@192.168.142.49> STOP GROUP_REPLICATION;

proxy> insert into t1 select 7, sleep(15);

192.168.142.48上出现对应写请求进程

四、 一主两从的读写分离测试

部署环境

192.168.142.48为master节点192.168.142.49为slave节点192.168.142.50为slave节点写入节点配置proxy-backend-addresses=192.168.142.48:5700读取节点配置proxy-read-only-backend-addresses=192.168.142.49:5700,192.168.142.50:5700

1、测试读：

proxy> select sleep(15) from t1 where a=1;

192.168.142.49上出现对应读请求进程

再次读请求
proxy> select sleep(15) from t1 where a=1;

192.168.142.50上出现对应读请求进程

可以发现读请求是均匀随机到两个读节点

2、测试写：

proxy> insert into t1 select 2 ,sleep(15);

192.168.142.48上出现对应写请求进程