数据库中间件 Cetus 2.3.6 分库版

article/2025/9/23 19:19:14

数据库初始化

#cetus
192.168.10.243#数据库节点192.168.10.250
192.168.10.251
192.168.10.252
192.168.10.253#四台数据库初始化数据库和表
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;create database employees;
use employees;
#hash
CREATE TABLE `dept_emp` (`id` int(11) NOT NULL AUTO_INCREMENT,`emp_no` int(11) DEFAULT NULL,`datetime` datetime DEFAULT NULL,PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
delete * from dept_emp;#range
CREATE TABLE `employees` (`id` int(11) NOT NULL AUTO_INCREMENT,`emp_no` int(11) DEFAULT NULL,`datetime` datetime DEFAULT NULL,PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;#datetime range
CREATE TABLE `purchase` (`id` int(11) NOT NULL AUTO_INCREMENT,`t_time` datetime DEFAULT NULL,PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;#只在250执行测试单点全局表
CREATE TABLE `regioncode` (`id` int(11) NOT NULL AUTO_INCREMENT,`emp_no` int(11) DEFAULT NULL,PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

安装

yum install cmake gcc glib2-devel flex mysql-devel gperftools-libs zlib-devel -y
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/
CFLAGS='-g -Wpointer-to-int-cast' cmake ../ -DCMAKE_BUILD_TYPE=Debug -DCMAKE_INSTALL_PREFIX=/usr/local/cetus -DSIMPLE_PARSER=OFF
make install
cp /tmp/cetus-2.3.6/scripts/cetus.service  /etc/init.d/cetus
#修改配置文件
/etc/init.d/cetus
#注意CETUS_HOME，CETUS_CONF，CETUS_CONF配置的和配置文件中一样
#删除
cetus_pid=`sed '/^pid-file=/!d;s/.*=//' ${CETUS_CONF}`
if [ "$cetus_pid" != "" ];thenCETUS_PID=$cetus_pid
ficetus_daemon=`sed '/^daemon=/!d;s/.*=//' ${CETUS_CONF}`
if [ "$cetus_daemon" != "" ];thenCETUS_DAEMON=$cetus_daemon
ficetus_log=`sed '/^log-file=/!d;s/.*=//' ${CETUS_CONF}`
if [ "$cetus_log" != "" ];thenCETUS_LOG=$cetus_log
fi#
systemctl daemon-reload

配置

#配置
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
cat >> /usr/local/cetus/conf/sharding.json << EOF
{"vdb": [{"id": 1,"type": "int","method": "hash","num": 8,"partitions": {"data1": [0,1], "data2": [2,3], "data3": [4,5], "data4": [6,7]}},{"id": 2,"type": "int","method": "range","num": 0,"partitions": {"data1": 100000, "data2": 200000, "data3": 300000,"data4": 400000}},{"id": 3,"type": "datetime","method": "range","num": 0,"partitions": {"data1": "2019-03-25 00:00:00", "data2": "2019-04-25 00:00:00", "data3": "2019-05-25 00:00:00","data4": "2019-06-25 00:00:00"}}],"table": [{"vdb": 1, "db": "employees", "table": "dept_emp", "pkey": "emp_no"},{"vdb": 2, "db": "employees", "table": "employees", "pkey": "emp_no"},{"vdb": 3, "db": "employees", "table": "purchase", "pkey": "t_time"}],"single_tables": [{"table": "regioncode", "db": "employees", "group": "data1"}]
}
EOFcat >> /usr/local/cetus/conf/shard.conf << EOF
[cetus]
# For mode
daemon=true
# Loaded Plugins
plugins=shard,admin
# Defines the number of worker processes. 
worker-processes=1
# Proxy Configuration, For eaxmlpe: MySQL master host ip is 192.0.0.1 and salve host ip is 192.0.0.2
proxy-address=0.0.0.0:6001
proxy-backend-addresses=192.168.10.250:3306@data1,192.168.10.251:3306@data2,192.168.10.252:3306@data3,192.168.10.253:3306@data4
proxy-read-only-backend-addresses=192.168.10.250:3306@data1,192.168.10.251:3306@data2,192.168.10.252:3306@data3,192.168.10.253:3306@data4
# 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-pool-size=400
max-resp-=10485760
long-query-time=100
# 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
plugin-dir=lib/cetus/plugins
max-open-files=65536
pid-file=cetus.pid
log-xa-file = /usr/local/cetus/logs/xa.log
log-file= /usr/local/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=false
enable-fast-stream=false
#For MGR
group-replication-mode=0
EOF

启动

chmod 660 /usr/local/cetus/conf/shard.conf
/etc/init.d/cetus start
#添加开机自启
chmod +x /etc/rc.d/init.d/cetus
chkconfig --add cetus
chkconfig --level 2345 cetus on
chkconfig
#业务
mysql --prompt="admin> " --comments -h192.168.10.243  -P7001 -uadmin -padmin
#管理
mysql --prompt="proxy> " --comments -h192.168.10.243  -P6001 -ucetus_app  -pcetus_app

测试

#测试
use employees;
# "partitions": {"data1": [0,1], "data2": [2,3], "data3": [4,5], "data4": [6,7]}
insert into dept_emp(emp_no,datetime) values(0,now()),(1,now()),(2,now()),(3,now()),(4,now()),(5,now()),(6,now()),(7,now());
#没有分配8的hash添加会被分配到第一个节点
insert into dept_emp(emp_no,datetime) values(8,now());#测试range
#"partitions": {"data1": 100000, "data2": 200000, "data3": 300000,"data4": 400000}
insert into employees(emp_no,datetime) values(1,now()),(100000,now()),(100001,now()),(200000,now()),(200001,now()),(300000,now()),(300001,now()),(400000,now());
#添加越过边界的数据会提示ERROR 1046 (3D000): no group yet
insert into employees(emp_no,datetime) values(400001,now());#datetime range
#"partitions": {"data1": "2019-03-25 00:00:00", "data2": "2019-04-25 00:00:00", "data3": "2019-05-25 00:00:00","data4": "2019-06-25 00:00:00"}
insert into purchase(t_time) values("2019-03-21"),("2019-03-26"),("2019-04-26"),("2019-05-26"),("2019-06-26");
#超过边界ERROR 1046 (3D000): no group yet
insert into purchase(t_time) values("2019-06-26")#单点全局表 读写都在250一台数据库上
insert into regioncode(emp_no) values(1),(2),(3),(4);
select * from regioncode;