网易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=ON

3、安装：执行make install进行安装
make install

4、配置文件

cd /data/cetus/cetus_install/conf/
cp XXX.json.example XXX.json
cp XXX.conf.example XXX.conf
vi XXX.json
vi XXX.conf

本文基于读写分离主要配置两个文件users.json和proxy.conf
1、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-switch
daemon = true

# Loaded Plugins
plugins=proxy,admin

# Proxy Configuration, For eaxmlpe: MySQL master and salve host ip are both 192.0.0.1
proxy-address=127.0.0.1:6001
proxy-backend-addresses=192.168.142.48:5700
proxy-read-only-backend-addresses=192.168.142.49:5700

# Admin Configuration
admin-address=127.0.0.1:7001
admin-username=admin
admin-password=admin

# Backend Configuration, use test db and username created
default-db=test
default-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 user
pid-file = cetus6001.pid
plugin-dir=lib/cetus/plugins
log-file=cetus_6001.log
log-level=debug

# support the MGR
group-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 -P6001
proxy> 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 -P7001
admin> 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上出现对应写请求进程