MySQL集群读写分离方案D-(MySQL+GTID+MyCat2+Keepalive)之5/6：MyCat2的分表分库
一、配置文件
Mycat2一大优势就是可以在终端直接创建数据源、集群、库表，并在创建时指定分库、分表。与1.6版本比大大简化了分库分表的操作。
1、配置数据源
（1）数据源配置
/*+ mycat:createDataSource{
"name":"dw0",
"url":"jdbc:mysql://192.168.21.191:3306",
"user":"root",
"password":"rootroot"
} */;
/*+ mycat:createDataSource{
"name":"dw1",
"url":"jdbc:mysql://192.168.21.192:3306",
"user":"root",
"password":"rootroot"
} */;
/*+ mycat:createDataSource{
"name":"dw2",
"url":"jdbc:mysql://192.168.21.193:3306",
"user":"root",
"password":"rootroot"
} */;
/*+ mycat:createDataSource{
"name":"dw3",
"url":"jdbc:mysql://192.168.21.194:3306",
"user":"root",
"password":"rootroot"
} */;
（2）通过注释命名添加数据源后，在对应目录会生成相关配置文件
cd /mysql/app/mycat/conf/datasources/
#如下图
2、配置集群
把新添加的数据源配置成集群
（1）在 mycat 终端执行
注意：创建集群，所有的主库都要配置一个c*集群，作为主库，否则创建库表时，主库是无法生成库表的。
/*! mycat:createCluster{"name":"c0","masters":["dw0"],"replicas":["dr0"]} */;
/*! mycat:createCluster{"name":"c1","masters":["dw1"],"replicas":["dr1"]} */;
/*! mycat:createCluster{"name":"c2","masters":["dw2"],"replicas":["dr2"]} */;
/*! mycat:createCluster{"name":"c3","masters":["dw3"],"replicas":["dr3"]} */;
失败的情况展示：
这种情况，在mycat中create database和表时，只能在dw0,dw1,dw2的主机数据库中创建表，在dw3是创建不了的
/*! mycat:createCluster{"name":"c0","masters":["dw0"],"replicas":["dw0"]} */;
/*! mycat:createCluster{"name":"c1","masters":["dw1"],"replicas":["dw1"]} */;
/*! mycat:createCluster{"name":"c2","masters":["dw2","dw3"],"replicas":["dw2","dw"]} */;
（2）可以查看集群配置信息
cd /usr/local/mycat/conf/clusters
3、创建全局表
（1）添加数据库db1
CREATE DATABASE db1;
（2）在建表语句中加上关键字 BROADCAST（广播，即为全局表）
CREATE TABLE db1.`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;
（2）进入相关目录查看 schema 配置
cat /mysql/app/mycat/conf/schemas/db1.schema.json
可以看到自动生成的全局表配置信息
4、创建分片表(分库分表)
（1）在 Mycat 终端直接运行建表语句进行数据分片
CREATE TABLE db1.orders( id BIGINT NOT NULL AUTO_INCREMENT, order_type INT, ustomer_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 4;
说明：
dbpartition BY mod_hash(customer_id) --数据库分片规则：哈希取模 tbpartition BY mod_hash(customer_id) --表分片规则：哈希取模 tbpartitions 1 --表分片-1片 （每个库上1个分片） dbpartitions 4 --分片表分到4个数据库上，每个库1个分片表（上述我们创建了4个数据源及4个集群）
（2）插入数据
数据库分片规则，表分片规则，以及各分多少片。
INSERT INTO orders(id,order_type,customer_id,amount) VALUES(1,101,100,100100); INSERT INTO orders(id,order_type,customer_id,amount) VALUES(2,101,100,100300); INSERT INTO orders(id,order_type,customer_id,amount) VALUES(3,101,101,120000); INSERT INTO orders(id,order_type,customer_id,amount) VALUES(4,101,101,103000); INSERT INTO orders(id,order_type,customer_id,amount) VALUES(5,102,102,100400); INSERT INTO orders(id,order_type,customer_id,amount) VALUES(6,102,102,100020); INSERT INTO orders(id,order_type,customer_id,amount) VALUES(7,103,103,100500); INSERT INTO orders(id,order_type,customer_id,amount) VALUES(8,103,103,100600); INSERT INTO orders(id,order_type,customer_id,amount) VALUES(9,103,104,130000); INSERT INTO orders(id,order_type,customer_id,amount) VALUES(10,104,104,104000); INSERT INTO orders(id,order_type,customer_id,amount) VALUES(11,104,105,100900); INSERT INTO orders(id,order_type,customer_id,amount) VALUES(12,104,105,100010);
（3）查看总的数据
SELECT * FROM orders;
注意：查看id等顺序，并非根据大小排列，目前这些数据在各个分片中。
（4）查看各分库数据片
select * from db1_0.orders_0; select * from db1_1.orders_1; select * from db1_2.orders_2; select * from db1_3.orders_3;
（5）分析上述分片
我们是根据customer_id进行分片的，所以插入的数据是有规律的：
-第1个库插入customer_id为100， -第2个库插入customer_id为101， -第3个库插入customer_id为102， -第4个库插入customer_id为103（此时4个库已经循环了一遍）； -插入customer_id为104的，则循环进入第1个库； -插入customer_id为105的，则循环进入第2个库；
（6）#进入相关目录查看 schema 配置
less /mysql/app/mycat/conf/schemas/db1.schema.json
5、ER表
与分片表关联的表如何分表，也就是ER表如何分表（有关联的，需要进行内连接或外连接的语句表）
（1）在Mycat终端直接运行建表语句进行数据分片
CREATE TABLE orders_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 4;
（2）插入数据
INSERT INTO orders_detail(id,detail,order_id) VALUES(1,'detail1',1); INSERT INTO orders_detail(id,detail,order_id) VALUES(2,'detail1',2); INSERT INTO orders_detail(id,detail,order_id) VALUES(3,'detail1',3); INSERT INTO orders_detail(id,detail,order_id) VALUES(4,'detail1',4); INSERT INTO orders_detail(id,detail,order_id) VALUES(5,'detail1',5); INSERT INTO orders_detail(id,detail,order_id) VALUES(6,'detail1',6); INSERT INTO orders_detail(id,detail,order_id) VALUES(7,'detail1',7); INSERT INTO orders_detail(id,detail,order_id) VALUES(8,'detail1',8); INSERT INTO orders_detail(id,detail,order_id) VALUES(9,'detail1',9); INSERT INTO orders_detail(id,detail,order_id) VALUES(10,'detail1',10); INSERT INTO orders_detail(id,detail,order_id) VALUES(11,'detail1',111); INSERT INTO orders_detail(id,detail,order_id) VALUES(12,'detail1',112);
（3）两个关联表的数据并未在一个同分库中
（4）查看关联信息
经查看，两个库的数据可以进行join查询。
select * from db1.orders inner join db1.orders_detail where db1.orders.id=db1.orders_detail.order_id;
#上述两表具有相同的分片算法,但是分片字段不相同
#Mycat2在涉及这两个表的join 分片字段等价关系的时候可以完成join的下推。
（5）查看配置的表是否具有ER关系，使用语句：
/*+ mycat:showErGroup{}*/
Mycat2无需指定ER表，是自动识别的，具体看分片算法的接口；且只要创建了这两个表（orders和orders_detail）就会自动关联，不用join操作的时候进行关联。
二、针对分库分表的解释
1、案例
create database afkfb; CREATE TABLE afkfb.test( 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) dbpartitions 4 tbpartitions 3 ;
2、解释
对数据库afdkb的test进行分库分表；
分库方式：dbpartition BY mod_hash(customer_id) ；哈希取模，取模字段：customer_id 分表方式：tbpartition BY mod_hash(customer_id) ；哈希取模，取模字段：customer_id
上述分片信息，共3*4个分区
有分库范围db_[0,3]，分表范围table_[0,2]，分区范围[0,11]
3、查看分库分表截图
4、插入数据
INSERT INTO afkfb.test(id,order_type,customer_id,amount) VALUES(1,101,100,100100); INSERT INTO afkfb.test(id,order_type,customer_id,amount) VALUES(2,101,100,100300); INSERT INTO afkfb.test(id,order_type,customer_id,amount) VALUES(3,101,101,120000); INSERT INTO afkfb.test(id,order_type,customer_id,amount) VALUES(4,101,101,103000); INSERT INTO afkfb.test(id,order_type,customer_id,amount) VALUES(5,102,102,100400); INSERT INTO afkfb.test(id,order_type,customer_id,amount) VALUES(6,102,102,100020); INSERT INTO afkfb.test(id,order_type,customer_id,amount) VALUES(7,103,103,100500); INSERT INTO afkfb.test(id,order_type,customer_id,amount) VALUES(8,103,103,100600); INSERT INTO afkfb.test(id,order_type,customer_id,amount) VALUES(9,103,104,130000); INSERT INTO afkfb.test(id,order_type,customer_id,amount) VALUES(10,104,104,104000); INSERT INTO afkfb.test(id,order_type,customer_id,amount) VALUES(11,104,105,100900); INSERT INTO afkfb.test(id,order_type,customer_id,amount) VALUES(12,104,105,100010); INSERT INTO afkfb.test(id,order_type,customer_id,amount) VALUES(13,104,106,100010); INSERT INTO afkfb.test(id,order_type,customer_id,amount) VALUES(14,104,106,100010); INSERT INTO afkfb.test(id,order_type,customer_id,amount) VALUES(15,104,107,100010); INSERT INTO afkfb.test(id,order_type,customer_id,amount) VALUES(16,104,107,100010); INSERT INTO afkfb.test(id,order_type,customer_id,amount) VALUES(17,104,108,100010); INSERT INTO afkfb.test(id,order_type,customer_id,amount) VALUES(18,104,108,100010); INSERT INTO afkfb.test(id,order_type,customer_id,amount) VALUES(19,104,109,100010); INSERT INTO afkfb.test(id,order_type,customer_id,amount) VALUES(20,104,109,100010); INSERT INTO afkfb.test(id,order_type,customer_id,amount) VALUES(21,104,110,100010); INSERT INTO afkfb.test(id,order_type,customer_id,amount) VALUES(22,104,110,100010); INSERT INTO afkfb.test(id,order_type,customer_id,amount) VALUES(23,104,111,100010); INSERT INTO afkfb.test(id,order_type,customer_id,amount) VALUES(24,104,111,100010);
5、查看数据分布
（1）192.168.21.191
（2）192.168.21.192
（3）192.168.21.193
（4）192.168.21.194
