数据库中间件 Cetus 2.3.6 分库版

最新推荐文章于 2024-05-14 09:57:25 发布

取壳羊

最新推荐文章于 2024-05-14 09:57:25 发布

阅读量225

点赞数

分类专栏： #数据库中间件 MySQL 文章标签： Cetus

本文链接：https://blog.csdn.net/java_w/article/details/103554655

版权

MySQL 同时被 2 个专栏收录

49 篇文章 1 订阅

订阅专栏

#数据库中间件

4 篇文章 0 订阅

订阅专栏

数据库初始化

#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" != "" ];then
        CETUS_PID=$cetus_pid
fi

cetus_daemon=`sed '/^daemon=/!d;s/.*=//' ${CETUS_CONF}`
if [ "$cetus_daemon" != "" ];then
        CETUS_DAEMON=$cetus_daemon
fi

cetus_log=`sed '/^log-file=/!d;s/.*=//' ${CETUS_CONF}`
if [ "$cetus_log" != "" ];then
        CETUS_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"}
  ]
}
EOF

cat >> /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;