数据库中间件 Cetus 2.3.6 读写分离版

 

• 搭建数据库主从集群并且初始化数据库

 

192.168.10.243 #cetus
192.168.10.250 #M
192.168.10.251 #S
#开启延迟检测需要创建心跳表
create database if not exists proxy_heart_beat; use proxy_heart_beat; 
CREATE TABLE if not exists tb_heartbeat ( p_id varchar(128) NOT NULL , p_ts timestamp(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), PRIMARY KEY (p_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

#注意授权情况  针对db 和具体授权 此处是事例
#grant all on *.* to 'cetus_app'@'%' identified by 'cetus_app';
#grant all on *.* to 'cetus_app1'@'%' identified by 'cetus_app1';

 

• 安装cetus

 

#MySQL建议使用5.7.16以上版本，若使用读写分离功能则需要搭建MySQL主从关系，若使用sharding功能则需要根据业务进行分库设计.
 yum install cmake gcc glib2-devel flex mysql-devel gperftools-libs zlib-devel -y
#下载最新版
#https://github.com/cetus-tools/cetus/releases
cd /tmp
wget https://github.com/cetus-tools/cetus/archive/v2.3.6.tar.gz
tar -xvf v2.3.6.tar.gz
cd  cetus-2.3.6
mkdir build/
cd build/
#安装目录
mkdir /usr/local/cetus -p
#读写分离版本：
CFLAGS='-g -Wpointer-to-int-cast' cmake ../ -DCMAKE_BUILD_TYPE=Debug -DCMAKE_INSTALL_PREFIX=/usr/local/cetus  -DSIMPLE_PARSER=ON

#配置 
#https://github.com/cetus-tools/cetus/blob/master/doc/cetus-rw-profile.md
cat >> /usr/local/cetus/conf/proxy.conf << EOF
[cetus]
verbose-shutdown=true
daemon=true
basedir=/usr/local/cetus
conf-dir=/usr/local/cetus/conf
pid-file=/usr/local/cetus/cetus6001.pid
plugin-dir=lib/cetus/plugins
plugins=proxy,admin
log-level=debug
log-file=/tmp/cetus_6001.log
max-open-files=65536
default-charset=utf8
default-username=cetus_app
default-db=testdb
ifname=eth0
#刚启动连接数量
default-pool-size=100

worker-processes=1
ssl=false
slave-delay-down=4.000000
slave-delay-recover=1.000000
long-query-time=100
enable-tcp-stream=true
enable-fast-stream=false
sql-log-bufsize=10485760
sql-log-switch=OFF
sql-log-prefix=cetus
sql-log-path=/usr/local/cetus/logs
sql-log-maxsize=1024
sql-log-mode=BACKEND
#
proxy-address=0.0.0.0:6001
#主库
proxy-backend-addresses=192.168.10.250:3306
#slave
proxy-read-only-backend-addresses=192.168.10.251:3306
#管理端
admin-address=0.0.0.0:7001
admin-username=admin
admin-password=admin
EOF

cat >> /usr/local/cetus/conf/users.json << EOF
{
	"users":	[{
			"user":	"cetus_app",
			"client_pwd":	"cetus_app",
			"server_pwd":	"cetus_app"
		}, {
			"user":	"cetus_app1",
			"client_pwd":	"cetus_app1",
			"server_pwd":	"cetus_app1"
		}]
}
EOF
#使用默认
cat >> /usr/local/cetus/conf/variables.json << EOF
{
    "variables": [
        {
            "name": "profiling",
            "type": "int",
            "allowed_values": ["*"],
            "silent_values": ["*"]
        },
        {
            "name": "sql_mode",
            "type": "string-csv",
            "allowed_values":  ["STRICT_TRANS_TABLES",
                                "NO_AUTO_CREATE_USER",
                                "NO_ENGINE_SUBSTITUTION"]
        },
        {
            "name": "connect_timeout",
            "type": "string",
            "allowed_values": ["*"],
            "silent_values": ["10", "100"]
        }
    ]
}
EOF

 

• 启动

 

chmod 660 /usr/local/cetus/conf/proxy.conf
#读写分离版本启动
/usr/local/cetus/bin/cetus --defaults-file=/usr/local/cetus/conf/proxy.conf
#服务连接
#https://github.com/cetus-tools/cetus/blob/master/doc/cetus-rw.md
mysql --prompt="proxy> " --comments -h192.168.10.243  -P6001 -ucetus_app  -pcetus_app

#管理连接
#https://github.com/cetus-tools/cetus/blob/master/doc/cetus-rw-admin.md
mysql --prompt="admin> " --comments -h192.168.10.243  -P7001 -uadmin -padmin
#查看后端
select * from backends;
#查看后端连接状态
select conn_details from backends;
#添加后端
add master '127.0.0.1:3307'
add slave '127.0.0.1:3360'
#删除后端
delete from backends where address = '127.0.0.1:3306'
#修改后端
update backends set type='rw' where address='127.0.0.1:3306'
update backends set state='up' where backend_ndx=1
#查看链接
show connectionlist;
#开启全量日志后可以看到请求具体被分发的节点
config set sql-log-switch = 'on';
sql log start;