mycat2 分库分表

mysql 搭建 参考前面文章

mycat2搭建参考前面文章

配置mycat2

登录mycat

mysql -uroot -h 192.168.10.30 -P 8066 -p

服务器mysq8 192.168.10.30

服务器mysq8 192.168.10.31

创建四个数据源

/*+ mycat:createDataSource{
"name":"separatew0",
"url":"jdbc:mysql://192.168.10.30:3306/?useSSL=false&characterEncoding=UTF-8&useJDBCCompliantTimezoneShift=true",
"user":"root",
"password":"123456"
} */;

/*+ mycat:createDataSource{
"name":"separater0",
"url":"jdbc:mysql://192.168.10.30:3306/?useSSL=false&characterEncoding=UTF-8&useJDBCCompliantTimezoneShift=true",
"user":"root",
"password":"123456"
} */;

/*+ mycat:createDataSource{
"name":"separatew1",
"url":"jdbc:mysql://192.168.10.31:3306/?useSSL=false&characterEncoding=UTF-8&useJDBCCompliantTimezoneShift=true",
"user":"root",
"password":"123456"
} */;

 
/*+ mycat:createDataSource{
"name":"separater1",
"url":"jdbc:mysql://192.168.10.31:3306/?useSSL=false&characterEncoding=UTF-8&useJDBCCompliantTimezoneShift=true",
"user":"root",
"password":"123456"
} */;

 创建集群 

/*! mycat:createCluster{"name":"c0","masters":["separatew0"],"replicas":["separater0"]} */;
/*! mycat:createCluster{"name":"c1","masters":["separatew1"],"replicas":["separater1"]} */;

创建逻辑库

create database db1;

创建广播表

create table `travelrecord`(
`id` bigint not null auto_increment,
`user_id` varchar(100) default null,
`traveldate` date default null,
`fee` decimal(10,0) default null,
`days` int default null,
`blob` LONGBLOB,
primary key (`id`),
key `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 BROADCAST;

创建完成后 对应的30  31 mysql数据库就会出现travelrecord表  当插入时候回插入所有的数据库起到广播插入的作用

测试广播表

mycat插入

 30 查询

31查询

 创建分片表

customer_id进行哈希分配，分为两个库，每个库有一个分片。

create table `orders` (
id BIGINT NOT NULL AUTO_INCREMENT,
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(customer_id) 
tbpartition BY mod_hash(customer_id)  
tbpartitions 1  
dbpartitions 2;

测试分片表

insert into  orders (id ,order_type,customer_id,amount) values (1,12,200,10000);
insert into  orders (id ,order_type,customer_id,amount) values (1,12,200,10400);
insert into  orders (id ,order_type,customer_id,amount) values (2,12,200,10300);
insert into  orders (id ,order_type,customer_id,amount) values (3,12,200,10000);
insert into  orders (id ,order_type,customer_id,amount) values (4,12,200,10020);
insert into  orders (id ,order_type,customer_id,amount) values (5,13,201,10300);
insert into  orders (id ,order_type,customer_id,amount) values (6,13,201,10000);
insert into  orders (id ,order_type,customer_id,amount) values (7,14,201,10090);
insert into  orders (id ,order_type,customer_id,amount) values (8,14,201,10007);
insert into  orders (id ,order_type,customer_id,amount) values (9,15,201,100600);

30查询一个分片

 31上查询一个分片

mycat 查询

创建ER表

create table order_detail (
id BIGINT NOT NULL AUTO_INCREMENT,
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  
dbpartitions 2;

测试

mycat 插入数据

insert into order_detail values(1,"detail",1);
insert into order_detail values(2,"detail",1);
insert into order_detail values(3,"detail",2);
insert into order_detail values(4,"detail",3);
insert into order_detail values(5,"detail",5);
insert into order_detail values(6,"detail",6);
insert into order_detail values(7,"detail",7);
insert into order_detail values(8,"detail",8);
insert into order_detail values(9,"detail",9);

30查询

31 查询

 mycat 联合查询

 虽然分片没有按一样的字段分组 ，但是ER会自动分组

ER自动分组

/*+ mycat:showErGroup{}*/;

注解 切 库查询

/*+ MYCAT:SCHEma = profile1 */ select * from labor_plot_enviromental;

注意：

1 在创建分表的时候不要使用create table databaseName.order_detail 格式  直接use数据库 然后在数据库执行create table order_detail ，不然可能会导致 插入 查询没问题，但是删除数据找不到schame.

2 广播表默认的集群配置文件名为c开头 数字结束，例如c0 c1 c2， 不然不会生成对应的分库。