網易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上出現對應寫請求進程

?
?