mysql中间件培训_MySQL中间件Cetus

准备：读写分离版本

1.配置好MySQL主从

2.建立cetus访问数据库的账号

GRANT USAGE ON *.* TO 'cetus_app'@'192.168.1.11' identified by 'cetus_app';

GRANT all ON `testdb`.* TO 'cetus_app'@'192.168.1.11';

3.创建心跳库和表(用于主从延迟检测)

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;

GRANT ALL ON `proxy_heart_beat`.* TO 'cetus_app'@'192.168.1.11';

3.安装依赖包

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

编译安装wget https://github.com/cetus-tools/cetus/archive/v2.3.7.tar.gz

tar zxf v2.3.7.tar.gz

cd /tools/cetus-2.3.7/

mkdir build

cd build

#读写分离版本

CFLAGS='-g -Wpointer-to-int-cast' cmake ../ -DCMAKE_BUILD_TYPE=release -DCMAKE_INSTALL_PREFIX=/home/cetus237_rw -DSIMPLE_PARSER=ON -DWITH_OPENSSL=ON

#分库版本

CFLAGS='-g -Wpointer-to-int-cast' cmake ../ -DCMAKE_BUILD_TYPE=release -DCMAKE_INSTALL_PREFIX=/home/cetus237_shard -DSIMPLE_PARSER=OFF -DWITH_OPENSSL=ON

make install

cd /home

ln -s cetus237_rw/ cetus

配置Cetus#配置文件目录:/home/cetus/conf

#users.json 用户设置文件

#proxy.conf 读写分离代理配置

#sharding.json 分库规则配置(分库版)

#shard.conf 分库版本启动配置文件(分库版)

#variables.json 变量处理配置文件(可选)

https://github.com/cetus-tools/cetus/blob/master/doc/cetus-rw-profile.md

user.json{

"users": [{

"user": "root",

"client_pwd": "123456",

"server_pwd": "654321"

}, {

"user": "cetus_app",

"client_pwd": "123456",

"server_pwd": "cetus_app"

}]

}

# user :数据库中建立的用户名

# client_pwd:前端应用连接cetus的密码

# server_pwd:cetus访问数据库的密码

proxy.conf[cetus]

# For mode-switch

daemon = true

# Loaded Plugins

plugins=proxy,admin

# Defines the number of worker processes.不能超过CPU个数

#每个线程都会向后端数据库建立default-pool-size个连接

worker-processes=1

# Proxy Configuration, For example: MySQL master and salve host ip are both 192.0.0.1

proxy-address=192.168.1.11:3306

proxy-backend-addresses=192.168.1.12:3306

proxy-read-only-backend-addresses=192.168.1.13:3306

# Admin Configuration

admin-address=0.0.0.0:7001

admin-username=admin

admin-password=admin

# Backend Configuration, use test db and username created

default-db=testdb

default-username=cetus_app

default-pool-size=100

max-resp-size=10485760

long-query-time=100

# File and Log Configuration, put log in /data and marked by proxy port, /data/cetus needs to be created manually an

d has rw authority for cetus os user

max-open-files = 65536

pid-file = cetus6001.pid

plugin-dir=lib/cetus/plugins

log-file=/home/cetus/logs/cetus_6001.log

log-level=debug

# Check salve delay

disable-threads=false

check-slave-delay=true

slave-delay-down=5

slave-delay-recover=1

# For trouble

keepalive=true

verbose-shutdown=true

log-backtrace-on-crash=true

# For performance

enable-tcp-stream=true

enable-fast-stream=true

# For MGR

group-replication-mode=0

启动cetuschmod 600 conf/proxy.conf

bin/cetus --default-file=conf/proxy.conf

访问cetus客户端访问：

mysql -ucetus_app -p123456 -h192.168.1.11 -P3306

管理Cetusmysql --prompt="cetus>" -uadmin -padmin -h192.168.1.11 -P7001

#用户名密码由proxy.conf文件进行配置

1.用户管理

-- cetus到mysql的用户

update user_pwd set password='1q2w3e4r' where user='xes_test'

-- app 到 cetus的用户

update app_user_pwd set password='123456' where user='xes_test'

#说明：以上操作添加或是修改了xes_test用户的后端连接密码为1q2w3e4r前端应用访问cetus的密码为123456

-- 查看用户

select * from user_pwd

2.后端服务器管理

-- 查看后端服务器

select * from backends

-- 查看后端连接状态

select conn_details from backends

-- 添加读写类型的后端

add master ''

-- 添加Slave

add slave ''

-- 删除后端服务器

delete from backends where address = ''

-- 修改后端状态

update backends set (type|state)='' WHERE (backend_ndx=|address='')

状态说明

unknown: 后端初始状态，还未建立连接;

up: 能与后端正常建立连接；

down: 与后端无法联通(如果开启后端状态检测，能连通后自动变为UP);

maintaining: 后端正在维护，无法建立连接或自动切换状态(此状态由管理员手动设置);

deleted: 后端已被删除，无法再建立连接。