- 搭建数据库主从集群并且初始化数据库
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;