准备工作,如果根据之前的文章搭建了MyCat双主双从的读写分离架构,需要更改一些配置 —> 改为一主一从,如果还未搭建一主一从,请点击跳转MyCat之读写分离(一主一从)
-
更改MyCat配置,改为一主一从
vim conf/clusters/dataSource.cluster.json
切换到datasources下,注释rwSepW2和rwSepR2主从数据源
cd conf/datasources/
启动MyCat
登录MyCat
-
创建数据源、存储数据源
/*+ mycat:createDataSource{ "name":"dw0","url":"jdbc:mysql://192.168.171.101:3306", "user":"root", "password":"123456" } */; /*+ mycat:createDataSource{ "name":"dr0","url":"jdbc:mysql://192.168.171.101:3306", "user":"root", "password":"123456" } */; /*+ mycat:createDataSource{ "name":"dw1","url":"jdbc:mysql://192.168.171.102:3306", "user":"root", "password":"123456" } */; /*+ mycat:createDataSource{ "name":"dr1","url":"jdbc:mysql://192.168.171.102:3306", "user":"root", "password":"123456" } */;
查看数据源
/*+ mycat:showDataSources*/;
-
配置集群
/*! mycat:createCluster{"name":"c0","masters":["dw0"],"replicas":["dr0"]} */; /*! mycat:createCluster{"name":"c1","masters":["dw1"],"replicas":["dr1"]} */;
查看集群配置信息
/*+ mycat:showClusters{}*/;
-
执行建表语句
-- 客户表 CREATE TABLE customer( id INT AUTO_INCREMENT, NAME VARCHAR(200), PRIMARY KEY(id) ); -- 订单表 CREATE TABLE orders( id INT AUTO_INCREMENT, order_type INT, customer_id INT, amount DECIMAL(10,2), PRIMARY KEY(id) ); -- 订单详细表 CREATE TABLE orders_detail( id INT AUTO_INCREMENT, detail VARCHAR(2000), order_id INT, PRIMARY KEY(id) ); -- 订单状态字典表 CREATE TABLE dict_order_type( id INT AUTO_INCREMENT, order_type VARCHAR(200), PRIMARY KEY(id) );
-
创建全局表(广播表)
CREATE DATABASE mycat_01;
在建表语句上加上关键字BORADCAST;
CREATE TABLE mycat_01.`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;
-
创建分片表(分库分表)
CREATE TABLE mycat_01.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;
mod_hash 相当于Java 的 % 运算符,进行取余运算插入数据
INSERT INTO orders ( id, order_type, customer_id, amount )VALUES( 1, 1, 10, 259 ); INSERT INTO orders ( id, order_type, customer_id, amount )VALUES( 2, 1, 11, 399 ); INSERT INTO orders ( id, order_type, customer_id, amount )VALUES( 3, 1, 10, 219 ); INSERT INTO orders ( id, order_type, customer_id, amount )VALUES( 4, 1, 10, 998 ); INSERT INTO orders ( id, order_type, customer_id, amount )VALUES( 5, 2, 11, 2599 ); INSERT INTO orders ( id, order_type, customer_id, amount )VALUES( 6, 2, 11, 1999 );
查看物理表,根据(customer_id)进行分库分表
-
创建ER表
ER表-> 即与↑↑↑↑↑分片表相关联的表,对相关的关联表进行分表CREATE TABLE mycat_01.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 2;
插入数据
INSERT INTO orders_detail ( id, detail, order_id )VALUES( 1, '内存128G', 1 ); INSERT INTO orders_detail ( id, detail, order_id )VALUES( 2, 'intel 7', 2 ); INSERT INTO orders_detail ( id, detail, order_id )VALUES( 3, '固态硬盘', 3 ); INSERT INTO orders_detail ( id, detail, order_id )VALUES( 4, '硬盘存储512GB', 4 ); INSERT INTO orders_detail ( id, detail, order_id )VALUES( 5, '3年质保', 5 ); INSERT INTO orders_detail ( id, detail, order_id )VALUES( 6, '7天无理由退换货', 6 ); INSERT INTO orders_detail ( id, detail, order_id )VALUES( 7, '运存16G', 1 ); INSERT INTO orders_detail ( id, detail, order_id )VALUES( 8, '5亿快充', 1 );
查看物理库
在使用 JOIN ON 语句查询时,MyCat2会自动识别ER关系,查看ER关系命令:/*+ mycat:showErGroup*/;
groupId相同的为一组,自动识别
其他章节 -> 跳转
end...