mysql build Refer to the previous article

mycat2 Refer to the previous article

To configure mycat2

Sign in mycat

mysql -uroot -h 192.168.10.30 -P 8066 -p

The server mysq8 192.168.10.30

The server mysq8 192.168.10.31

Create four data sources

/*+ 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"
} */;

  Create clusters  

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

Create a logical library

create database db1;

Create broadcast table

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;

After creation Corresponding 30  31 mysql The database will appear travelrecord surface   When inserting, insert all databases back to play the role of broadcast insertion

Test broadcast table

mycat Insert

 30 Inquire about

31 Inquire about

  Create a shard table

customer_id Hash allocation , It is divided into two libraries , Each library has a partition .

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;

Test slice table

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 Query a fragment

 31 Query a fragment on

mycat Inquire about

establish ER surface

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;

test

mycat insert data

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 Inquire about

31 Inquire about

 mycat The joint query

  Although the fragments are not grouped by the same field , however ER Will automatically group

ER Auto Group

/*+ mycat:showErGroup{}*/;

annotation cut Library query

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

Be careful ：

1 Don't use when creating sub tables create table databaseName.order_detail Format   direct use database Then execute in the database create table order_detail , Otherwise, it may lead to Insert No problem with the inquiry , But the deleted data cannot be found schame.

2 The default cluster configuration file name of the broadcast table is c start The number ends , for example c0 c1 c2, Otherwise, the corresponding sub database will not be generated .