运维03-分库分表

最新推荐文章于 2024-11-11 20:35:54 发布

秦川Code

最新推荐文章于 2024-11-11 20:35:54 发布

阅读量144

点赞数

分类专栏： MySQL 文章标签：运维

本文链接：https://blog.csdn.net/m0_57288751/article/details/134285778

版权

MySQL 专栏收录该内容

21 篇文章 0 订阅

订阅专栏

分库分表的形式，主要是两种：垂直拆分和水平拆分。而拆分的粒度，一般又分为分库和分表，所以组成的拆分策略最终如下：

垂直拆分

垂直分库

每个库的表结构都不一样。每个库的数据也不一样。所有库的并集是全量数据

垂直分表

每个表的结构都不一样。
每个表的数据也不一样，一般通过一列（主键/外键）关联。
所有表的并集是全量数据。

水平拆分

水平分库

每个库的表结构都一样。每个库的数据都不一样。所有库的并集是全量数据。

水平分表

每个表的表结构都一样。每个表的数据都不一样。所有表的并集是全量数据。

shardingJDBC：基于AOP原理，在应用程序中对本地执行的SQL进行拦截，解析、改写、路由处理。需要自行编码配置实现，只支持java语言，性能较高。
MyCat：数据库分库分表中间件，不用调整代码即可实现分库分表，支持多种语言，性能不及前者。

MyCat

Mycat是开源的、活跃的、基于Java语言编写的MySQL数据库中间件。
可以像使用mysql一样来使用 mycat，对于开发人员来说根本感觉不到mycat的存在。开发人员只需要连接MyCat即可，而具体底层用到几台数据库，每一台数据库服务器里面存储了什么数据，都无需关心。具体的分库分表的策略，只需要在MyCat中配置即可。

性能可靠稳定强大的技术团队体系完善社区活跃

下载：
http://dl.mycat.org.cn/

安装：
https://www.yuque.com/qinchuan-xlpxi/apor60/0e24046208d502611d058f6557ec9db4

MyCat入门案例

需求：
由于 tb_order 表中数据量很大，磁盘IO及容量都到达了瓶颈，
现在需要对 tb_order 表进行数据分片，分为三个数据节点，
每一个节点主机位于不同的服务器上, 具体的结构，参考下图：

环境准备：
准备3台服务器：
192.168.200.210：MyCat中间件服务器，同时也是第一个分片服务器。
192.168.200.213：第二个分片服务器。
192.168.200.214：第三个分片服务器。

并且在上述3台数据库中创建数据库 db01 。

配置：
mycat配置文件配置
schema.xml
在schema.xml中配置逻辑库、逻辑表、数据节点、节点主机等相关信息。具体的配置如下：

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
  
  <schema name="DB01" checkSQLschema="true" sqlMaxLimit="100">
    <table name="TB_ORDER" dataNode="dn1,dn2,dn3" rule="auto-sharding-long"
      />
  </schema>
  
  <dataNode name="dn1" dataHost="dhost1" database="db01" />
  <dataNode name="dn2" dataHost="dhost2" database="db01" />
  <dataNode name="dn3" dataHost="dhost3" database="db01" />
  
  <dataHost name="dhost1" maxCon="1000" minCon="10" balance="0"
    writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1"
    slaveThreshold="100">
    <heartbeat>select user()</heartbeat>
    <writeHost host="master" url="jdbc:mysql://192.168.200.210:3306?
      useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8"
      user="root" password="1234" />
  </dataHost>
  
  <dataHost name="dhost2" maxCon="1000" minCon="10" balance="0"
    writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1"
    slaveThreshold="100">
    <heartbeat>select user()</heartbeat>
    <writeHost host="master" url="jdbc:mysql://192.168.200.213:3306?
      useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8"
      user="root" password="1234" />
  </dataHost>
  
  <dataHost name="dhost3" maxCon="1000" minCon="10" balance="0"
    writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1"
    slaveThreshold="100">
    <heartbeat>select user()</heartbeat>
    <writeHost host="master" url="jdbc:mysql://192.168.200.214:3306?
      useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8"
      user="root" password="1234" />
  </dataHost>
  
</mycat:schema>

server.xml
需要在server.xml中配置用户名、密码，以及用户的访问权限信息，具体的配置如下：

<user name="root" defaultAccount="true">
  <property name="password">123456</property>
  <property name="schemas">DB01</property>
  <!-- 表级 DML 权限设置 -->
  <!--
  <privileges check="true">
  <schema name="DB01" dml="0110" >
  <table name="TB_ORDER" dml="1110"></table>
</schema>
</privileges>
  -->
</user>
<user name="user">
  <property name="password">123456</property>
  <property name="schemas">DB01</property>
  <property name="readOnly">true</property>
</user>

这里的用户是用于连接到 Mycat 服务器的用户账号

测试：
配置完毕后，先启动涉及到的3台分片服务器，然后启动MyCat服务器。切换到Mycat的安装目录，执行如下指令，启动Mycat：

#启动
bin/mycat start
#停止
bin/mycat stop

Mycat启动之后，占用端口号 8066。启动完毕之后，可以查看logs目录下的启动日志，查看Mycat是否启动完成。
tail -f logs /warpper.log

通过如下指令，就可以连接并登陆MyCat。
mysql -h 192.168.200.210 -P 8066 -uroot -p123456

然后就可以在MyCat中来创建表，并往表结构中插入数据，查看数据在MySQL中的分布情况。

CREATE TABLE TB_ORDER (
  id BIGINT(20) NOT NULL,
  title VARCHAR(100) NOT NULL ,
  PRIMARY KEY (id)
) ENGINE=INNODB DEFAULT CHARSET=utf8 ;

INSERT INTO TB_ORDER(id,title) VALUES(1,'goods1');
INSERT INTO TB_ORDER(id,title) VALUES(2,'goods2');
INSERT INTO TB_ORDER(id,title) VALUES(3,'goods3');
INSERT INTO TB_ORDER(id,title) VALUES(1,'goods1');
INSERT INTO TB_ORDER(id,title) VALUES(2,'goods2');
INSERT INTO TB_ORDER(id,title) VALUES(3,'goods3');
INSERT INTO TB_ORDER(id,title) VALUES(5000000,'goods5000000');
INSERT INTO TB_ORDER(id,title) VALUES(10000000,'goods10000000');
INSERT INTO TB_ORDER(id,title) VALUES(10000001,'goods10000001');
INSERT INTO TB_ORDER(id,title) VALUES(15000000,'goods15000000');
INSERT INTO TB_ORDER(id,title) VALUES(15000001,'goods15000001');

经过测试，我们发现，在往 TB_ORDER 表中插入数据时：
如果id的值在1-500w之间，数据将会存储在第一个分片数据库中。
如果id的值在500w-1000w之间，数据将会存储在第二个分片数据库中。
如果id的值在1000w-1500w之间，数据将会存储在第三个分片数据库中。如果id的值超出1500w，在插入数据时，将会报错。

为什么会出现这种现象，数据到底落在哪一个分片服务器到底是如何决定的呢？这是由逻辑表配置时的一个参数** rule** 决定的，而这个参数配置的就是分片规则。

MyCat垂直拆分

准备三台服务器，IP地址如图所示：

并且在192.168.200.210，192.168.200.213, 192.168.200.214上面创建数据库 shopping。

配置：
schema.xml

<schema name="SHOPPING" checkSQLschema="true" sqlMaxLimit="100">
  <table name="tb_goods_base" dataNode="dn1" primaryKey="id" />
  <table name="tb_goods_brand" dataNode="dn1" primaryKey="id" />
  <table name="tb_goods_cat" dataNode="dn1" primaryKey="id" />
  <table name="tb_goods_desc" dataNode="dn1" primaryKey="goods_id" />
  <table name="tb_goods_item" dataNode="dn1" primaryKey="id" />
  <table name="tb_order_item" dataNode="dn2" primaryKey="id" />
  <table name="tb_order_master" dataNode="dn2" primaryKey="order_id" />
  <table name="tb_order_pay_log" dataNode="dn2" primaryKey="out_trade_no" />
  <table name="tb_user" dataNode="dn3" primaryKey="id" />
  <table name="tb_user_address" dataNode="dn3" primaryKey="id" />
  <table name="tb_areas_provinces" dataNode="dn3" primaryKey="id"/>
  <table name="tb_areas_city" dataNode="dn3" primaryKey="id"/>
  <table name="tb_areas_region" dataNode="dn3" primaryKey="id"/>
</schema>

<dataNode name="dn1" dataHost="dhost1" database="shopping" />
<dataNode name="dn2" dataHost="dhost2" database="shopping" />
<dataNode name="dn3" dataHost="dhost3" database="shopping" />

<dataHost name="dhost1" maxCon="1000" minCon="10" balance="0"
  writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1"
  slaveThreshold="100">
  <heartbeat>select user()</heartbeat>
  <writeHost host="master" url="jdbc:mysql://192.168.200.210:3306?
    useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8"
    user="root" password="1234" />
</dataHost>

<dataHost name="dhost2" maxCon="1000" minCon="10" balance="0"
  writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1"
  slaveThreshold="100">
  <heartbeat>select user()</heartbeat>
  <writeHost host="master" url="jdbc:mysql://192.168.200.213:3306?
    useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8"
    user="root" password="1234" />
</dataHost>

<dataHost name="dhost3" maxCon="1000" minCon="10" balance="0"
  writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1"
  slaveThreshold="100">
  <heartbeat>select user()</heartbeat>
  <writeHost host="master" url="jdbc:mysql://192.168.200.214:3306?
    useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8"
    user="root" password="1234" />
</dataHost>

server.xml

<user name="root" defaultAccount="true">
  <property name="password">123456</property>
  <property name="schemas">SHOPPING</property>
  <!-- 表级 DML 权限设置 -->
  <!--
  <privileges check="true">
  <schema name="DB01" dml="0110" >
  <table name="TB_ORDER" dml="1110"></table>
</schema>
</privileges>
  -->
</user>
<user name="user">
  <property name="password">123456</property>
  <property name="schemas">SHOPPING</property>
  <property name="readOnly">true</property>
</user>

测试：
链接：https://pan.baidu.com/s/1EnMCkJbW9e70ZPKrr8-0Yg?pwd=6666
提取码：6666

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

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

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

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

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
where ua.province_id = p.provinceid and ua.city_id = c.cityid and ua.town_id =
r.areaid ;

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

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语句执行报错。
订单相关的表结构是在 192.168.200.213 数据库服务器中，而省市区的数据库表是在 192.168.200.214

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

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

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

配置完毕后，重新启动MyCat
删除原来每一个数据库服务器中的所有表结构
通过source指令，导入表及数据
source /root/shopping-table.sql
source /root/shopping-insert.sql
然后再次执行上面的多表联查的SQL语句
可以执行成功

MyCat水平拆分

配置：
schema.xml

<schema name="ITCAST" checkSQLschema="true" sqlMaxLimit="100">
  <table name="tb_log" dataNode="dn4,dn5,dn6" primaryKey="id" rule="mod-long" />
</schema>

<dataNode name="dn4" dataHost="dhost1" database="itcast" />
<dataNode name="dn5" dataHost="dhost2" database="itcast" />
<dataNode name="dn6" dataHost="dhost3" database="itcast" />

server.xml

<user name="root" defaultAccount="true">
  <property name="password">123456</property>
  <property name="schemas">SHOPPING,ITCAST</property>
  <!-- 表级 DML 权限设置 -->
  <!--
  <privileges check="true">
  <schema name="DB01" dml="0110" >
  <table name="TB_ORDER" dml="1110"></table>
</schema>
</privileges>
  -->
</user>

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

CREATE TABLE tb_log (
  id bigint(20) NOT NULL COMMENT 'ID',
  model_name varchar(200) DEFAULT NULL COMMENT '模块名',
  model_value varchar(200) DEFAULT NULL COMMENT '模块值',
  return_value varchar(200) DEFAULT NULL COMMENT '返回值',
  return_class varchar(200) DEFAULT NULL COMMENT '返回值类型',
  operate_user varchar(20) DEFAULT NULL COMMENT '操作用户',
  operate_time varchar(20) DEFAULT NULL COMMENT '操作时间',
  param_and_value varchar(500) DEFAULT NULL COMMENT '请求参数名及参数值',
  operate_class varchar(200) DEFAULT NULL COMMENT '操作类',
  operate_method varchar(200) DEFAULT NULL COMMENT '操作方法',
  cost_time bigint(20) DEFAULT NULL COMMENT '执行方法耗时, 单位 ms',
  source int(1) DEFAULT NULL COMMENT '来源 : 1 PC , 2 Android , 3 IOS',
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


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.contro
       ller.UserController','insert','10',1);
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.contro
       ller.UserController','insert','23',1);
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.contro
       ller.UserController','update','34',1);
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.contro
       ller.UserController','update','13',2);
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.co
       ntroller.UserController','insert','29',3);
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.co
       ntroller.UserController','find','29',2);

MyCat管理监控

Mycat-web(Mycat-eye)是对mycat-server提供监控服务，功能不局限于对mycat-server使用。他通过JDBC连接对Mycat、Mysql监控，监控远程服务器(目前仅限于linux系统)的cpu、内存、网络、磁盘。
Mycat-eye运行过程中需要依赖zookeeper，因此需要先安装zookeeper。
教程：
https://www.yuque.com/qinchuan-xlpxi/apor60/9c688b04a16e4e7d09861d44103c2ccb
下载地址：
链接：https://pan.baidu.com/s/12ISJLBRow9ButZVV8CTeDA?pwd=6666
提取码：6666

访问：
http://192.168.200.210:8082/mycat

配置：