• 【MySQL | 运维篇】05、MySQL 分库分表之 使用 MyCat 分片

    目录

    一、垂直拆分

    1.1 场景

    1.2 准备

    1.3 配置

    1). schema.xml

    2). server.xml

    1.4 测试

    1). 上传测试SQL脚本到服务器的 /root/sql 目录

    2). 执行指令导入测试数据

     3). 查询用户的收件人及收件人地址信息(包含省、市、区)。

    4). 查询每一笔订单及订单的收件地址信息(包含省、市、区)。

    1.5 全局表 

    二、水平拆分

    2.1 场景

    2.2 准备

    2.3 配置

    1). schema.xml 

    2). server.xml

    2.4 测试

     

    一、垂直拆分

    1.1 场景

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

            现在考虑将其进行垂直分库操作，将商品相关的表拆分到一个数据库服务器，订单表拆分的一个数据库服务器，用户及省市区表拆分到一个服务器。最终结构如下： 

    1.2 准备

    准备三台服务器，IP地址如下： 

    • 192.168.78.139：MyCat中间件服务器，同时也是第一个分片服务器。
    • 192.168.78.140：第二个分片服务器。
    • 192.168.78.141：第三个分片服务器。

    并且在三台 MySQL 服务器上面创建数据库 shopping。 

    1.3 配置

    1). schema.xml

    1. 	<schema name="SHOPPING" checkSQLschema="true" sqlMaxLimit="100">
    2. 		
    3. 		<table name="tb_goods_base" dataNode="dn1" primaryKey="id" />
    4. 		<table name="tb_goods_brand" dataNode="dn1" primaryKey="id" />
    5. 		<table name="tb_goods_cat" dataNode="dn1" primaryKey="id" />
    6. 		<table name="tb_goods_desc" dataNode="dn1" primaryKey="goods_id" />
    7. 		<table name="tb_goods_item" dataNode="dn1" primaryKey="id" />
    8. 		
    9. 		<table name="tb_order_item" dataNode="dn2" primaryKey="id" />
    10. 		<table name="tb_order_master" dataNode="dn2" primaryKey="order_id" />
    11. 		<table name="tb_order_pay_log" dataNode="dn2" primaryKey="out_trade_no" />
    12. 		
    13. 		<table name="tb_user" dataNode="dn3" primaryKey="id" />
    14. 		<table name="tb_user_address" dataNode="dn3" primaryKey="id" />
    15. 		<table name="tb_areas_provinces" dataNode="dn3" primaryKey="id"/>
    16. 		<table name="tb_areas_city" dataNode="dn3" primaryKey="id"/>
    17. 		<table name="tb_areas_region" dataNode="dn3" primaryKey="id"/>
    18. 	
    19. 	</schema>
    20.  
    21. 	<dataNode name="dn1" dataHost="dhost1" database="shopping" />
    22. 	<dataNode name="dn2" dataHost="dhost2" database="shopping" />
    23. 	<dataNode name="dn3" dataHost="dhost3" database="shopping" />
    24.  
    25. 	<dataHost name="dhost1" maxCon="1000" minCon="10" balance="0"
    26. 			  writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1"  slaveThreshold="100">
    27. 		<heartbeat>select user()</heartbeat>
    28.  
    29. 		<writeHost host="master" url="jdbc:mysql://192.168.78.139:3306?useSSL=false&serverTimezone=Asia/Shanghai&characterEncoding=utf8" user="root" password="Qwe123456." />
    30. 	</dataHost>
    31. 	
    32. 	<dataHost name="dhost2" maxCon="1000" minCon="10" balance="0"
    33. 			  writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1"  slaveThreshold="100">
    34. 		<heartbeat>select user()</heartbeat>
    35.  
    36. 		<writeHost host="master" url="jdbc:mysql://192.168.78.140:3306?useSSL=false&serverTimezone=Asia/Shanghai&characterEncoding=utf8" user="root" password="Qwe123456." />
    37. 	</dataHost>	
    38. 	
    39. 	<dataHost name="dhost3" maxCon="1000" minCon="10" balance="0"
    40. 			  writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1"  slaveThreshold="100">
    41. 		<heartbeat>select user()</heartbeat>
    42.  
    43. 		<writeHost host="master" url="jdbc:mysql://192.168.78.141:3306?useSSL=false&serverTimezone=Asia/Shanghai&characterEncoding=utf8" user="root" password="Qwe123456." />
    44. 	</dataHost>

    2). server.xml

    1. 	<user name="root" defaultAccount="true">
    2. 		<property name="password">Qwe123456.</property>
    3. 		<property name="schemas">SHOPPING</property>
    4. 		
    5. 		<!-- 表级 DML 权限设置 -->
    6. 		<!-- 		
    7. 		<privileges check="false">
    8. 			<schema name="TESTDB" dml="0110" >
    9. 				<table name="tb01" dml="0000"></table>
    10. 				<table name="tb02" dml="1111"></table>
    11. 			</schema>
    12. 		</privileges>		
    13. 		 -->
    14. 	</user>
    15.  
    16. 	<user name="user">
    17. 		<property name="password">123456</property>
    18. 		<property name="schemas">SHOPPING</property>
    19. 		<property name="readOnly">true</property>
    20. 	</user>

    1.4 测试

    1). 上传测试SQL脚本到服务器的 /root/sql 目录

    2). 执行指令导入测试数据

            重新启动MyCat后，在mycat的命令行中，通过source指令导入表结构，以及对应的数据，查看数据分布情况。

    1. [root@sql-master sql]# /usr/local/mycat/bin/mycat stop
    2.  
    3. [root@sql-master sql]# /usr/local/mycat/bin/mycat start
    4.  
    5. [root@sql-master sql]# mysql -h 192.168.78.139 -u root -p -P 8066
    6.  
    7. source /root/sql/shopping-table.sql
    8.  
    9. source /root/sql/shopping-insert.sql

            将表结构及对应的测试数据导入之后，可以检查一下各个数据库服务器中的表结构分布情况。 检查是否和我们准备工作中规划的服务器一致。

     3). 查询用户的收件人及收件人地址信息(包含省、市、区)。

    在MyCat的命令行中，当我们执行以下多表联查的SQL语句时，可以正常查询出数据。

    1. select ua.user_id, ua.contact, p.province, c.city, r.area , ua.address from tb_user_address ua ,tb_areas_city c , tb_areas_provinces p ,tb_areas_region r 
    2. where ua.province_id = p.provinceid and ua.city_id = c.cityid and ua.town_id = r.areaid ;

    4). 查询每一笔订单及订单的收件地址信息(包含省、市、区)。

    实现该需求对应的SQL语句如下： 

    1. 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 
    2. WHERE o.receiver_province = p.provinceid AND o.receiver_city = c.cityid AND o.receiver_region = r.areaid ;

            经过测试，我们看到，SQL语句执行报错。原因就是因为MyCat在执行该SQL语句时，需要往具体的数据库服务器中路由，而当前没有一个数据库服务器完全包含了订单以及省市区的表结构，造成SQL语句失败，报错。

    对于上述的这种现象，我们如何来解决呢？ 下面我们介绍的全局表，就可以轻松解决这个问题。

    1.5 全局表 

            对于省、市、区/县表tb_areas_provinces , tb_areas_city , tb_areas_region，是属于
    数据字典表，在多个业务模块中都可能会遇到，可以将其设置为全局表，利于业务操作。

            修改schema.xml中的逻辑表的配置，修改 tb_areas_provinces、tb_areas_city、tb_areas_region 三个逻辑表，增加 type 属性，配置为global，就代表该表是全局表，就会在
    所涉及到的dataNode中创建给表。对于当前配置来说，也就意味着所有的节点中都有该表了。

    1. 		<table name="tb_areas_provinces" dataNode="dn1,dn2,dn3" primaryKey="id" type="global" />
    2. 		<table name="tb_areas_city" dataNode="dn1,dn2,dn3" primaryKey="id" type="global" />
    3. 		<table name="tb_areas_region" dataNode="dn1,dn2,dn3" primaryKey="id" type="global" />

    配置完毕后，重新启动MyCat。

    1). 删除原来每一个数据库服务器中的所有表结构

    2). 通过source指令，导入表及数据 

    1. source /root/sql/shopping-table.sql
    2.  
    3. source /root/sql/shopping-insert.sql

    3). 检查每一个数据库服务器中的表及数据分布，看到三个节点中都有这三张全局表

    4). 然后再次执行上面的多表联查的SQL语句

    1. mysql> 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
    2. WHERE o.receiver_province = p.provinceid AND o.receiver_city = c.cityid AND o.receiver_region = r.areaid;

    是可以正常执行成功的。

    5). 当在MyCat中更新全局表的时候，我们可以看到，所有分片节点中的数据都发生了变化，每个节点的全局表数据时刻保持一致。 

    二、水平拆分

    2.1 场景

            在业务系统中, 有一张表(日志表), 业务系统每天都会产生大量的日志数据 , 单台服务器的数据存储及处理能力是有限的, 可以对数据库表进行拆分。

    2.2 准备

    准备三台服务器，具体的结构如下：

    • 192.168.78.139：MyCat中间件服务器，同时也是第一个分片服务器。
    • 192.168.78.140：第二个分片服务器。
    • 192.168.78.141：第三个分片服务器。

    并且在三台 MySQL 服务器上面创建数据库 itcast。 

    2.3 配置

    1). schema.xml 

            tb_log表最终落在3个节点中，分别是 dn4、dn5、dn6 ，而具体的数据分别存储在 dhost1、
    dhost2、dhost3的itcast数据库中。

    1. 	<schema name="ITCAST" checkSQLschema="true" sqlMaxLimit="100">		
    2. 		<table name="tb_log" dataNode="dn4,dn5,dn6" primaryKey="id" rule="mod-long" />	
    3. 	</schema>	
    4. 	
    5. 	<dataNode name="dn4" dataHost="dhost1" database="itcast" />
    6. 	<dataNode name="dn5" dataHost="dhost2" database="itcast" />
    7. 	<dataNode name="dn6" dataHost="dhost3" database="itcast" />

    2). server.xml

    配置root用户既可以访问 SHOPPING 逻辑库，又可以访问ITCAST逻辑库。 

    1. 	<user name="root" defaultAccount="true">
    2. 		<property name="password">Qwe123456.</property>
    3. 		<property name="schemas">SHOPPING,ITCAST</property>
    4. 		
    5. 		<!-- 表级 DML 权限设置 -->
    6. 		<!-- 		
    7. 		<privileges check="false">
    8. 			<schema name="TESTDB" dml="0110" >
    9. 				<table name="tb01" dml="0000"></table>
    10. 				<table name="tb02" dml="1111"></table>
    11. 			</schema>
    12. 		</privileges>		
    13. 		 -->
    14. 	</user>
    15.  
    16. 	<user name="user">
    17. 		<property name="password">123456</property>
    18. 		<property name="schemas">SHOPPING,ITCAST</property>
    19. 		<property name="readOnly">true</property>
    20. 	</user>

     

    2.4 测试

            配置完毕后，重新启动MyCat，然后在mycat的命令行中，执行如下SQL创建表、并插入数据，查看数据分布情况。 

    1. use ITCAST;
    2.  
    3. CREATE TABLE tb_log (
    4.   id bigint(20) NOT NULL COMMENT 'ID',
    5.   model_name varchar(200) DEFAULT NULL COMMENT '模块名',
    6.   model_value varchar(200) DEFAULT NULL COMMENT '模块值',
    7.   return_value varchar(200) DEFAULT NULL COMMENT '返回值',
    8.   return_class varchar(200) DEFAULT NULL COMMENT '返回值类型',
    9.   operate_user varchar(20) DEFAULT NULL COMMENT '操作用户',
    10.   operate_time varchar(20) DEFAULT NULL COMMENT '操作时间',
    11.   param_and_value varchar(500) DEFAULT NULL COMMENT '请求参数名及参数值',
    12.   operate_class varchar(200) DEFAULT NULL COMMENT '操作类',
    13.   operate_method varchar(200) DEFAULT NULL COMMENT '操作方法',
    14.   cost_time bigint(20) DEFAULT NULL COMMENT '执行方法耗时, 单位 ms',
    15.   source int(1) DEFAULT NULL COMMENT '来源 : 1 PC , 2 Android , 3 IOS',
    16.   PRIMARY KEY (id)
    17. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    18.  
    19.  
    20. INSERT INTO tb_log (id, model_name, model_value, return_value, return_class, operate_user, operate_time, param_and_value, operate_class, operate_method, cost_time，source) VALUES('1','user','insert','success','java.lang.String','10001','2022-01-06 18:12:28','{\"age\":\"20\",\"name\":\"Tom\",\"gender\":\"1\"}','cn.itcast.controller.UserController','insert','10',1);
    21. INSERT INTO tb_log (id, model_name, model_value, return_value, return_class, operate_user, operate_time, param_and_value, operate_class, operate_method, cost_time，source) VALUES('2','user','insert','success','java.lang.String','10001','2022-01-06 18:12:27','{\"age\":\"20\",\"name\":\"Tom\",\"gender\":\"1\"}','cn.itcast.controller.UserController','insert','23',1);
    22. INSERT INTO tb_log (id, model_name, model_value, return_value, return_class, operate_user, operate_time, param_and_value, operate_class, operate_method, cost_time，source) VALUES('3','user','update','success','java.lang.String','10001','2022-01-06 18:16:45','{\"age\":\"20\",\"name\":\"Tom\",\"gender\":\"1\"}','cn.itcast.controller.UserController','update','34',1);
    23. INSERT INTO tb_log (id, model_name, model_value, return_value, return_class, operate_user, operate_time, param_and_value, operate_class, operate_method, cost_time，source) VALUES('4','user','update','success','java.lang.String','10001','2022-01-06 18:16:45','{\"age\":\"20\",\"name\":\"Tom\",\"gender\":\"1\"}','cn.itcast.controller.UserController','update','13',2);
    24. INSERT INTO tb_log (id, model_name, model_value, return_value, return_class, operate_user, operate_time, param_and_value, operate_class, operate_method, cost_time，source) VALUES('5','user','insert','success','java.lang.String','10001','2022-01-06 18:30:31','{\"age\":\"200\",\"name\":\"TomCat\",\"gender\":\"0\"}','cn.itcast.controller.UserController','insert','29',3);
    25. INSERT INTO tb_log (id, model_name, model_value, return_value, return_class, operate_user, operate_time, param_and_value, operate_class, operate_method, cost_time，source) VALUES('6','user','find','success','java.lang.String','10001','2022-01-06 18:30:31','{\"age\":\"200\",\"name\":\"TomCat\",\"gender\":\"0\"}','cn.itcast.controller.UserController','find','29',2);

  • 相关阅读:
    已更新!宝藏教程！MYSQL-第六章节多表查询(一对一，多对多，一对多)，连接查询（内，外连接），联合查询，子查询 代码例题详解这一篇就够了（附数据准备代码）
    代码随想录|583. 两个字符串的删除操作，72. 编辑距离（有进一步理解到)
    第十一章第一节：JavaString类介绍和常用方法
    idea显示maven或者gradle无法从仓库获取到项目中的jar包，jar包所在仓库无法访问解决方法，百试百灵
    安防视频监控平台EasyCVR集成到ios系统不能播放是什么原因？如何解决？
    postman---postman参数化
    超声功率放大器使用范围有哪些
    5_1 计算机网络
    Hive 开窗函数如何运用？简单例子说明
    贝锐向日葵亮相云栖大会，携手无影推出全新“云桌面”功能
  • 原文地址：https://blog.csdn.net/weixin_46560589/article/details/127862450