mycat垂直分片

港控/mmm° 2022-08-28 08:46 174阅读 0赞

### mycat垂直分片 ###

*   *  1.1 垂直拆分
     *   *  1.1.1 概述
         *  1.1.2 案例场景
         *  1.1.3 准备工作
         *  1.1.4 schema.xml的配置
         *  1.1.5 server.xml的配置
         *  1.1.6 测试
         *  1.1.7 全局表配置

## 1.1 垂直拆分 ##

### 1.1.1 概述 ###

![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5Yir5Zui562Jc2h55ZOl5Y-R6IKy_size_12_color_FFFFFF_t_70_g_se_x_16_pic_center]

一种是按照不同的表（或者Schema）来切分到不同的数据库（主机）之上，这种切分可以称之为数据的垂直（纵向）切分。

### 1.1.2 案例场景 ###

![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5Yir5Zui562Jc2h55ZOl5Y-R6IKy_size_20_color_FFFFFF_t_70_g_se_x_16_pic_center]

在业务系统中, 有以下表结构 ,但是由于用户与订单每天都会产生大量的数据, 单台服务器的数据存储及处理能力是有限的, 可以对数据库表进行拆分, 原有的数据库表:

![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5Yir5Zui562Jc2h55ZOl5Y-R6IKy_size_16_color_FFFFFF_t_70_g_se_x_16_pic_center]

### 1.1.3 准备工作 ###

1). 准备三台数据库实例

192.168.192.33
    192.168.192.34
    192.168.192.35

将准备好的三个SQL脚本, 分别导入到三台MySQL实例中 ;  
登录MySQL数据库之后, 使用source命令导入 ;  
192.168.159.33:  
![在这里插入图片描述][3c1f71d6b9e74360a52a1b7a92c82b44.png_pic_center]  
![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5Yir5Zui562Jc2h55ZOl5Y-R6IKy_size_10_color_FFFFFF_t_70_g_se_x_16_pic_center]  
192.168.159.34  
![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5Yir5Zui562Jc2h55ZOl5Y-R6IKy_size_10_color_FFFFFF_t_70_g_se_x_16_pic_center 1]  
![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5Yir5Zui562Jc2h55ZOl5Y-R6IKy_size_8_color_FFFFFF_t_70_g_se_x_16_pic_center]

192.168.159.35:  
![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5Yir5Zui562Jc2h55ZOl5Y-R6IKy_size_13_color_FFFFFF_t_70_g_se_x_16_pic_center]  
![在这里插入图片描述][ddc4476187de4aac8c9139de219a6227.png_pic_center]

2). 在三台数据库实例中建库建表

### 1.1.4 schema.xml的配置 ###

<?xml version="1.0"?>
    <!DOCTYPE mycat:schema SYSTEM "schema.dtd">
    <mycat:schema xmlns:mycat="http://io.mycat/">
    
    	<schema name="ITCAST_DB" checkSQLschema="false" sqlMaxLimit="100">
    		<table name="tb_areas_city" dataNode="dn1" primaryKey="id" />
    		<table name="tb_areas_provinces" dataNode="dn1" primaryKey="id" />
    		<table name="tb_areas_region" dataNode="dn1" primaryKey="id" />
    		<table name="tb_user" dataNode="dn1" primaryKey="id" />
    		<table name="tb_user_address" dataNode="dn1" primaryKey="id" />
    		
    		<table name="tb_goods_base" dataNode="dn2" primaryKey="id" />
    		<table name="tb_goods_desc" dataNode="dn2" primaryKey="goods_id" />
    		<table name="tb_goods_item_cat" dataNode="dn2" primaryKey="id" />
    		
    		<table name="tb_order_item" dataNode="dn3" primaryKey="id" />
    		<table name="tb_order_master" dataNode="dn3" primaryKey="order_id" />
    		<table name="tb_order_pay_log" dataNode="dn3" primaryKey="out_trade_no" />
    	</schema>
    	
    	
    	<dataNode name="dn1" dataHost="host1" database="user_db" />
    	<dataNode name="dn2" dataHost="host2" database="goods_db" />
    	<dataNode name="dn3" dataHost="host3" database="order_db" />
    	
        
    	<dataHost name="host1" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
    		<heartbeat>select user()</heartbeat>
    		<writeHost host="hostM1" url="192.168.159.33:3306" user="root" password="123456"></writeHost>
    	</dataHost>	
        
        <dataHost name="host2" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
    		<heartbeat>select user()</heartbeat>
    		<writeHost host="hostM2" url="192.168.159.34:3306" user="root" password="123456"></writeHost>
    	</dataHost>	
        
        <dataHost name="host3" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
    		<heartbeat>select user()</heartbeat>
    		<writeHost host="hostM3" url="192.168.159.35:3306" user="root" password="123456"></writeHost>
    	</dataHost>	
    
    </mycat:schema>

### 1.1.5 server.xml的配置 ###

<user name="root" defaultAccount="true">
    		<property name="password">GO0bnFVWrAuFgr1JMuMZkvfDNyTpoiGU7n/Wlsa151CirHQnANVk3NzE3FErx8v6pAcO0ctX3xFecmSr+976QA==</property>
    		<property name="schemas">ITCAST_DB</property>
              	<property name="readOnly">false</property>
        		<property name="benchmark">1000</property> 
    		<property name="usingDecrypt">1</property>
    		
    		
    		
    	</user>
    
    	<user name="user">
    		<property name="password">123456</property>
    		<property name="schemas">ITCAST_DB</property>
    		<property name="readOnly">true</property>
    	</user>

### 1.1.6 测试 ###

登录mycat  
![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5Yir5Zui562Jc2h55ZOl5Y-R6IKy_size_17_color_FFFFFF_t_70_g_se_x_16_pic_center]

1). 查询数据

select * from tb_goods_base;
    select * from tb_user;
    select * from tb_order_master;

![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5Yir5Zui562Jc2h55ZOl5Y-R6IKy_size_20_color_FFFFFF_t_70_g_se_x_16_pic_center 1]  
![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5Yir5Zui562Jc2h55ZOl5Y-R6IKy_size_20_color_FFFFFF_t_70_g_se_x_16_pic_center 2]  
![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5Yir5Zui562Jc2h55ZOl5Y-R6IKy_size_20_color_FFFFFF_t_70_g_se_x_16_pic_center 3]  
2). 插入数据

insert  into tb_user_address(id,user_id,province_id,city_id,town_id,mobile,address,contact,is_default,notes,create_date,alias) values (null,'java00001',NULL,NULL,NULL,'13900112222','钟楼','张三','0',NULL,NULL,NULL)

![在这里插入图片描述][3975e382e0eb4114845f5d41eded2a5b.png]

insert  into tb_order_item(id,item_id,goods_id,order_id,title,price,num,total_fee,pic_path,seller_id) values (100,19,149187842867954,3,'3G 6','1.00',5,'5.00',NULL,'qiandu')

![在这里插入图片描述][98397abdb77c4bdc9d096882b9895677.png]  
3). 测试跨分片的查询

SELECT order_id , payment ,receiver, province , city , area FROM tb_order_master o , tb_areas_provinces p , tb_areas_city c , tb_areas_region r
    WHERE o.receiver_province = p.provinceid AND o.receiver_city = c.cityid AND o.receiver_region = r.areaid ;

当运行上述的SQL语句时, MyCat会报错, 原因是因为当前SQL语句涉及到跨域的join操作 ;  
**这时候我们需要将这些需要关联的字典表配置成全局表，也就是说让每个数据节点中都有这些表就不存在sql语句跨域join操作了。**

### 1.1.7 全局表配置 ###

1). 将数据节点user\_db中的关联的字典表 tb\_areas\_provinces , tb\_areas\_city , tb\_areas\_region中的数据备份 ;

mysqldump -uroot -pitcast user_db tb_areas_provinces  > provinces;
    mysqldump -uroot -pitcast user_db tb_areas_city > city;
    mysqldump -uroot -pitcast user_db tb_areas_region > region;

2). 将备份的表结构及数据信息, 远程同步到其他两个数据节点的数据库中;

scp city root@192.168.192.34:/root
    scp city root@192.168.192.35:/root
    
    scp provinces root@192.168.192.34:/root
    scp provinces root@192.168.192.35:/root
    
    scp region root@192.168.192.34:/root
    scp region root@192.168.192.35:/root

![在这里插入图片描述][7cc503c81c0b46cf9b933f72a2927589.png_pic_center]

3). 导入到对应的数据库中

mysql -uroot -p goods_db < city
    mysql -uroot -p goods_db < provinces 
    mysql -uroot -p goods_db < region

4). MyCat逻辑表中的配置

此时完整的schema.xml

<?xml version="1.0"?>
    <!DOCTYPE mycat:schema SYSTEM "schema.dtd">
    <mycat:schema xmlns:mycat="http://io.mycat/">
    
    	<schema name="ITCAST_DB" checkSQLschema="false" sqlMaxLimit="100">
    		<table name="tb_areas_city" dataNode="dn1,dn2,dn3" primaryKey="id" type="global" />
    		<table name="tb_areas_provinces" dataNode="dn1,dn2,dn3" primaryKey="id" type="global"/>
    		<table name="tb_areas_region" dataNode="dn1,dn2,dn3" primaryKey="id" type="global" />
    		<table name="tb_user" dataNode="dn1" primaryKey="id" />
    		<table name="tb_user_address" dataNode="dn1" primaryKey="id" />
    		
    		<table name="tb_goods_base" dataNode="dn2" primaryKey="id" />
    		<table name="tb_goods_desc" dataNode="dn2" primaryKey="goods_id" />
    		<table name="tb_goods_item_cat" dataNode="dn2" primaryKey="id" />
    		
    		<table name="tb_order_item" dataNode="dn3" primaryKey="id" />
    		<table name="tb_order_master" dataNode="dn3" primaryKey="order_id" />
    		<table name="tb_order_pay_log" dataNode="dn3" primaryKey="out_trade_no" />
    	</schema>
    	
    	
    	<dataNode name="dn1" dataHost="host1" database="user_db" />
    	<dataNode name="dn2" dataHost="host2" database="goods_db" />
    	<dataNode name="dn3" dataHost="host3" database="order_db" />
    	
        
    	<dataHost name="host1" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
    		<heartbeat>select user()</heartbeat>
    		<writeHost host="hostM1" url="192.168.159.33:3306" user="root" password="123456"></writeHost>
    	</dataHost>	
        
        <dataHost name="host2" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
    		<heartbeat>select user()</heartbeat>
    		<writeHost host="hostM2" url="192.168.159.34:3306" user="root" password="123456"></writeHost>
    	</dataHost>	
        
        <dataHost name="host3" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
    		<heartbeat>select user()</heartbeat>
    		<writeHost host="hostM3" url="192.168.159.35:3306" user="root" password="123456"></writeHost>
    	</dataHost>	
    
    </mycat:schema>

5). 重启MyCat

bin/mycat restart

![在这里插入图片描述][adc50205717c40679fde865c553dd7e2.png_pic_center]  
6). 测试

再次执行相同的连接查询 , 是可以正常查询出对应的数据的 ;

![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5Yir5Zui562Jc2h55ZOl5Y-R6IKy_size_20_color_FFFFFF_t_70_g_se_x_16_pic_center 4]  
当我们对Mycat全局表进行增删改的操作时, 其他节点主机上的后端MySQL数据库中的数据时会同步变化的;  
修改之前：  
![在这里插入图片描述][ca5cf95c47a04058b6856f192974dd26.png_pic_center]  
执行修改的sql`update tb_areas_city set city = '石家庄' where id = 5;`将city从石家庄市改为石家庄  
![在这里插入图片描述][88242e054f844c0db8d9534f686395f8.png_pic_center]  
再查看三个节点中的数据是否也被修改了  
192.168.159.33 192.168.159.34 192.168.159.35都是下面的结果。  
![在这里插入图片描述][fee064d1b6c34e099988baaf99a0a0e4.png_pic_center]

[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5Yir5Zui562Jc2h55ZOl5Y-R6IKy_size_12_color_FFFFFF_t_70_g_se_x_16_pic_center]: /images/20220828/a3f385c025784dcfb20c2bd44dfecc3b.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5Yir5Zui562Jc2h55ZOl5Y-R6IKy_size_20_color_FFFFFF_t_70_g_se_x_16_pic_center]: /images/20220828/daeeb855195442178aa423dd0331aab1.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5Yir5Zui562Jc2h55ZOl5Y-R6IKy_size_16_color_FFFFFF_t_70_g_se_x_16_pic_center]: /images/20220828/6e9b1f4413ef498fb998f9707206ea05.png
[3c1f71d6b9e74360a52a1b7a92c82b44.png_pic_center]: /images/20220828/b199aada540e42e5a6a950bf8b7339e4.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5Yir5Zui562Jc2h55ZOl5Y-R6IKy_size_10_color_FFFFFF_t_70_g_se_x_16_pic_center]: /images/20220828/c90f028d2a8f4f6e9c0e4771c7a08bb4.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5Yir5Zui562Jc2h55ZOl5Y-R6IKy_size_10_color_FFFFFF_t_70_g_se_x_16_pic_center 1]: /images/20220828/09cd5560cf9f4ba4b9835ab6a8ef756f.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5Yir5Zui562Jc2h55ZOl5Y-R6IKy_size_8_color_FFFFFF_t_70_g_se_x_16_pic_center]: /images/20220828/700e2d818c694c68bd5facd162056203.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5Yir5Zui562Jc2h55ZOl5Y-R6IKy_size_13_color_FFFFFF_t_70_g_se_x_16_pic_center]: /images/20220828/c5e9901b5548460084d6624cf0faaead.png
[ddc4476187de4aac8c9139de219a6227.png_pic_center]: /images/20220828/192d2e4cc52a4197b3167691fff35ae8.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5Yir5Zui562Jc2h55ZOl5Y-R6IKy_size_17_color_FFFFFF_t_70_g_se_x_16_pic_center]: /images/20220828/01ebda26418c48cf989c938206386649.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5Yir5Zui562Jc2h55ZOl5Y-R6IKy_size_20_color_FFFFFF_t_70_g_se_x_16_pic_center 1]: /images/20220828/a2a50379613c47558c8f885673b2f736.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5Yir5Zui562Jc2h55ZOl5Y-R6IKy_size_20_color_FFFFFF_t_70_g_se_x_16_pic_center 2]: /images/20220828/b13f2ed26b6a4e12a436fdd72e016bdc.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5Yir5Zui562Jc2h55ZOl5Y-R6IKy_size_20_color_FFFFFF_t_70_g_se_x_16_pic_center 3]: /images/20220828/f88ab606f93d4a5c9150d4c307ec0438.png
[3975e382e0eb4114845f5d41eded2a5b.png]: /images/20220828/12ad62f545a241ec9d0b7fac337fd396.png
[98397abdb77c4bdc9d096882b9895677.png]: /images/20220828/337ee45defb4473f98a24f165ab1634c.png
[7cc503c81c0b46cf9b933f72a2927589.png_pic_center]: /images/20220828/dc001eed860d4a73b72428cf35fec2cd.png
[adc50205717c40679fde865c553dd7e2.png_pic_center]: /images/20220828/f8c8ebfa347b442fa978c3468bb31b92.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5Yir5Zui562Jc2h55ZOl5Y-R6IKy_size_20_color_FFFFFF_t_70_g_se_x_16_pic_center 4]: /images/20220828/c43aeaab0b3947b7b07ec1c10695bbca.png
[ca5cf95c47a04058b6856f192974dd26.png_pic_center]: /images/20220828/a090fba0ffc64b4b8a18ce9610c1c89c.png
[88242e054f844c0db8d9534f686395f8.png_pic_center]: /images/20220828/7cae81bc6db54d60837815e99c1d5334.png
[fee064d1b6c34e099988baaf99a0a0e4.png_pic_center]: /images/20220828/ba5a5a05369d4de5ac8a6ea9ce9e4054.png