mycat2

本文 参考 两个blog

[Docker 部署Mycat2](https://blog.csdn.net/weixin_45623111/article/details/125973225)

[mycat2 简单使用与配置](https://blog.csdn.net/yinlongtao/article/details/129380437)



首先是 docker 启动 mycat2

1、创建mycat2的文件夹


 

mkdir /usr/local/mycat2/ -p

进入文件夹



 

cd /usr/local/mycat2/

创建 Dockerfile


 

vim Dockerfile

内容


 

FROM openjdk:8-jre



ENV AUTO_RUN_DIR ./mycat2

ENV DEPENDENCE_FILE mycat2-1.22-release-jar-with-dependencies.jar

ENV TEMPLATE_FILE mycat2-install-template-1.21.zip



RUN sed -i "s@http://.*archive.ubuntu.com@http://mirrors.aliyun.com@g" /etc/apt/sources.list

RUN sed -i "s@http://.*security.ubuntu.com@http://mirrors.aliyun.com@g" /etc/apt/sources.list



RUN buildDeps='procps wget unzip' \

&& apt-get update \

&& apt-get install -y $buildDeps



# 安装地址 http://dl.mycat.org.cn/2.0/

# http://dl.mycat.org.cn/2.0/1.22-release/

# http://dl.mycat.org.cn/2.0/install-template/

RUN wget -P $AUTO_RUN_DIR/ http://dl.mycat.org.cn/2.0/1.22-release/$DEPENDENCE_FILE \

&& wget -P $AUTO_RUN_DIR/ http://dl.mycat.org.cn/2.0/install-template/$TEMPLATE_FILE



RUN cd $AUTO_RUN_DIR/ \

&& unzip $TEMPLATE_FILE \

&& ls -al . \

&& mv $DEPENDENCE_FILE mycat/lib/ \

&& chmod +x mycat/bin/* \

&& chmod 755 mycat/lib/* \

&& mv mycat /usr/local



#copy mycat /usr/local/mycat/

VOLUME /usr/local/mycat/conf

VOLUME /usr/local/mycat/logs



EXPOSE 8066 1984

CMD ["/usr/local/mycat/bin/mycat", "console"]

编译mycat2镜像



 

docker build -t mycat2:1.22 .

成功构建


 

Successfully built b8897ed67faf

Successfully tagged mycat2:1.22

复制配置


 

docker run -d --name=mycat2 -p 8066:8066 -p 1984:1984 mycat2:1.22



# 复制容器内配置

docker cp mycat2:/usr/local/mycat/conf .

docker cp mycat2:/usr/local/mycat/logs .

关掉 mycat2，并挂载数据

 

docker run -d --name=mycat2 -p 8066:8066 -p 1984:1984 -v $PWD/conf:/usr/local/mycat/conf -v $PWD/logs:/usr/local/mycat/logs mycat2:1.22



# 以root用户权限进入容器

docker exec -it -u root mycat2 bash

# 查看容器启动日志

docker logs -f mycat2

数据连接







配置真实数据源


 

# 创建数据源 # 创建dbw写库，指向集群中的master服务

/*+ mycat:createDataSource{"name":"dbW","url":"jdbc:mysql://192.168.232.128:3306/mysql?useSSL=false&characterEncoding=UTF-8&useJDBCCompliantTimezoneShift=true", "user":"root",

"password":"root"}*/;

/*+ mycat:createDataSource{"name":"dbR","url":"jdbc:mysql://192.168.232.128:3306/mysql?useSSL=false&characterEncoding=UTF-8&useJDBCCompliantTimezoneShift=true", "user":"root",

"password":"root"}*/;





#查询数据源

/*+ mycat:showDataSources{} */

或者修改mycat\conf\datasources下配置文件，复制新的进行修改



一共建两个



dbW：用来写



dbR：用来读



配置Mysql集群



创建一个集群，起名:WRSplitCluster，设置dbW为主节点，dbR为从节点

#查看配置集群信息

/*+ mycat:showClusters{} */;
 

#更新集群信息,添加dr从节点

/*! mycat:createCluster{"name":"WRSplitCluster","masters": ["dbW"],"replicas":["dbR"]} */;



#查看配置集群信息

/*+ mycat:showClusters{} */;

或者修改/mycat/conf/clusters下配置文件，复制新的进行修改



配置逻辑库



 

create database wrdb;

--2、在mycat2的部署目录下，找到对应的配置文件,conf/schema/wrdb.schema.json。在其中增加targetName:WRSplitcluster属性。指向真实的集群。


 

{

"customTables" :{},

"globalTables":{},

"normalProcedures":{},

"normalTables":{},

"schemaName" : "wrdb",

"targetName":"WRSplitCluster",

"shardingTables":{},

"views":{}

}

--3、手动修改配置文件之后，需要重启MyCat2服务，让配置文件生效。



--4.进行读写分离



配置数据源



 

#第一个写库

/*+ mycat:createDataSource{"name":"dw0","url":"jdbc:mysql://116.205.132.18:3307 ","user":"root","password":"root"} */;



#第一个读库

/*+ mycat:createDataSource{"name":"dr0","url":"jdbc:mysql://116.205.132.18:3307 ","user":"root","password":"root"} */;



#第二个写库

/*+ mycat:createDataSource{"name":"dw1","url":"jdbc:mysql://116.205.132.18:3309 ","user":"root","password":"root"} */;



#第二个读库

/*+ mycat:createDataSource{"name":"dr1","url":"jdbc:mysql://116.205.132.18:3309 ","user":"root","password":"root"} */;

配置集群



 

#在mycat2终端输入

#配置第一组集群

/*!mycat:createCluster{"name":"c0","masters":["dw0"],"replicas":["dr0"]}*/;



#配置第二组集群

/*!mycat:createCluster{"name":"c1","masters":["dw1"],"replicas":["dr1"]}*/;

创建数据库和表



 

#添如数据库db1

CREATE DATABASE shardingdb;



#在建表语句中加上关链字 BROADCAST(广播，即为全局表)

CREATE TABLE shardingdb.t_dict(

`id` INT NOT NULL,

`dict_id` INT,

`item_id` INT,

`item_value` VARCHAR ( 32 ) NULL,

PRIMARY KEY (`id` ),

KEY `id`(`id`)

)ENGINE = INNODB DEFAULT CHARSET = utf8 BROADCAST;





INSERT INTO shardingdb.t_dict VALUES(1,1,0,'正常');

INSERT INTO shardingdb.t_dict VALUES(2,1,1,'正常');

INSERT INTO shardingdb.t_dict VALUES(3,2,0,'男');

INSERT INTO shardingdb.t_dict VALUES(4,2,1,'女');

INSERT INTO shardingdb.t_dict VALUES(5,3,1,'会员');

INSERT INTO shardingdb.t_dict VALUES(6,3,1,'非会员');





SELECT * FROM shardingdb.t_dict

分片表配置


 

CREATE TABLE shardingdb.orders (

id BIGINT NOT NULL,

order_type INT,

customer_id INT,

amount DECIMAL ( 10，2 ),

PRIMARY KEY ( id ),

KEY `id` (`id` )

)ENGINE = INNODB DEFAULT CHARSET = utf8

dbpartition BY mod_hash ( id ) tbpartition BY mod_hash ( id ) tbpartitions 1 dbpartitions 2;



INSERT INTO shardingdb.orders(id,order_type,customer_id,amount)VALUES(1,101,100,100100);

INSERT INTO shardingdb.orders(id,order_type,customer_id,amount)VALUES(2,101,101,100300);

INSERT INTO shardingdb.orders(id,order_type,customer_id,amount)VALUES(3,101,101,120000);

INSERT INTO shardingdb.orders(id,order_type,customer_id,amount)VALUES(4,101,101,103000);

INSERT INTO shardingdb.orders(id,order_type,customer_id,amount)VALUES(5,102,101,100400);

INSERT INTO shardingdb.orders(id,order_type,customer_id,amount)VALUES(6,102,100,100020);

dbpartition BY mod_hash ( id ) tbpartition BY mod_hash ( id ) tbpartitions 1 dbpartitions 2; 这句代办分库分表



dbpartition：代办对库进行分片



tbpartition：代表对表进行分片



mod_hash ：根据该方法指定哪个列去分区



tbpartitions：指定表有几个分区



dbpartitions：指定库有几个分区



配置关联表

关联表也成为绑定表或者ER表。表示数据逻辑上有关联性的两个或多个表，例如订单和订单详情表。对于关联表，通常希望他们能够有相同的分片规则，这样在进行关联查询时，能够快速定位到同一个数据分片中。



 

#在 Mycat 终端创建一个与order表关联的订单详情表

CREATE TABLE shardingdb.orders_detail(

`id` BIGINT NOT NULL,

`detail` VARCHAR(2000),

`order_id` INT,

PRIMARY KEY(id)

)ENGINE=INNODB DEFAULT CHARSET=utf8

dbpartition BY mod_hash(order_id) tbpartition BY mod_hash(order_id) tbpartitions 1;



#查看关联关系表

/*+ mycat:showErGroup{}*/





INSERT INTO shardingdb.orders_detail(id,detail,order_id) VALUES(1,'detail1',1);

INSERT INTO shardingdb.orders_detail(id,detail,order_id) VALUES(2,'detail1',2);

INSERT INTO shardingdb.orders_detail(id,detail,order_id) VALUES(3,'detail1',3);

INSERT INTO shardingdb.orders_detail(id,detail,order_id) VALUES(4,'detail1',4);

INSERT INTO shardingdb.orders_detail(id,detail,order_id) VALUES(5,'detail1',5);

INSERT INTO shardingdb.orders_detail(id,detail,order_id) VALUES(6,'detail1',6);