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