MySQL分库分表

一、介绍两种拆分方式

1. • 垂直分库

不同的表存到不同的服务器当中

2.垂直分表

一张表不同的字段分到不同的服务器当中（表格垂直分下来，左右字段分开）

2. • 水平分库

将一个数据库拆分到多个服务器中（每个服务器的表相同）

3. • 水平分表

将一个表的数据拆分到多个表中（每个服务器的字段名相同）

二、Mycat概述

查看MySQL是否运行

systemctl status mysqld

（一）下载上传

服务器中要安装好MySQL、JDK、Mycat

Mycat下载地址http://www.mycat.org.cn/

Mycat云盘下载https://www.aliyundrive.com/s/urAAgT1HUQh

上传JDK、Mycat到服务器

（二）.安装jdk

1. • 解压jak 到 /usr/local目录中

tar -zxvf jdk-8u171-linux-x64.tar.gz -C /usr/local

在/usr/local目录下就可以看见解压后的jdk1.8.0_171

2.配置环境变量

进入profile配置vim /etc/profile

添加

JAVA_HOME=/usr/local/jdk1.8.0_171
PATH=$JAVA_HOME/bin:$PATH

3. 重新加载profile文件

source /etc/profile

4. 检查安装是否成功

java -version

（三）安装Mycat

1. • 解压

tar -zxvf Mycat-server-1.6.7.3-release-20210913163959-linux.tar.gz -C /usr/local/

出现mycat,进入mycat就可以看到文件了

bin:存放可执行文件，用于启动停止mycat

conf:存放mycat的配置文件

lib:存放mycat的项目依赖包(jar)（架包）

logs:存放mycat的日志文件

2. • 如果您的MySQL是高版本，则要将lib目录下的mysql-connector-java-5.1.35.jar更换为新的jar

mysql-connector-java下载连接：MySQL :: Download Connector/J

1.删掉

rm -rf mysql-connector-java-5.1.35.jar

2.上传一份高版本的在这个目录下

3.授权

chmod 777 mysql-connector-java-8.0.22.jar

三、Mycat入门

1.项目需求

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

2. 创建数据库

在各个服务器中，创建好db01的数据库

3.修改配置schema.xml

修改配置可以用Notepad++ 编辑这个文件

/usr/local/mycat/conf/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>

4.server.xml配置

修改用户名密码，添加DB01这张逻辑表

<user name="root" defaultAccount="true">
        <property name="password">123456</property>
        <property name="schemas">DB01</property>
        
        <!-- 表级 DML 权限设置 -->
        <!--         
        <privileges check="false">
            <schema name="TESTDB" dml="0110" >
                <table name="tb01" dml="0000"></table>
                <table name="tb02" dml="1111"></table>
            </schema>
        </privileges>        
         -->
    </user>

    <user name="user">
        <property name="password">123456</property>
        <property name="schemas">DB01</property>
        <property name="readOnly">true</property>
    </user>

5.启动服务

Mycat启动后，占用端口号8066

#启动 /usr/local/mycat/

bin/mycat start

查看wrapper.log日志 切换到路径cd /usr/local/mycat/

tail -f logs/wrapper.log

最底部如果显示 MyCAT Server startup successfully.则表达Mycat正常启动

#停止 =====省略

bin/mycat stop

登录Mycat --确认打开8066端口号

mysql -h 121.37.20.23 -P 8066 -uroot -p123456

连接进入 ，显示有Server version: 5.6.29-mycat-1.6.7.3-release-20210913163959 MyCat Server (OpenCloudDB)

6.在mycat中创建一张表

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(1000000,'goods1000000');
INSERT INTO TB_ORDER(id,title) VALUES(10000000,'goods10000000');

创建完成后，插入数据查看各个数据库的数据分布情况

四、Mycat配置

schema.xml

schema.xml作为MyCat中最重要的配置文件之一,涵盖了MyCat的逻辑库、逻辑表、分片规则、分片节点及数据源的配置。

主要包含以下三组标签:

1. schema标签 逻辑库逻辑表

    <schema name="DB01" checkSQLschema="true" sqlMaxLimit="100">
        <table name="TB_ORDER" dataNode="dn1,dn2,dn3" rule="auto-sharding-long" />
    </schema>

schema 标签用于定义 MyCat实例中的逻辑库，一个MvCat实例中,可以有多个逻辑库，可以通过 schema 标签来划分不同的逻辑库，MyCat中的逻辑库的概念，等同于MySQL中的database概念，需要操作某个逻辑库下的表时,也需要切换逻辑库(use xxX)。

核心属性:

name: 指定自定义的逻辑库库名

checkSOLschema: 在SOL语句操作时指定了数据库名称，执行时是否自动去除;true: 自动去除，false: 不自动去。true可以不用 use DB01 进入数据库，直接select * from DB01.TB_ORDER

sglMaxLimit: 如果未指定limit进行查询，列表查询模式查询多少条记录。默认一次性只能查一百条

schema标签（table）

table 标签定义了MyCat中逻辑库schema下的逻辑表所有需要拆分的表都需要在table标签中定义

核心属性：

name: 定义逻辑表表名，在该逻辑库下唯一

dataNode: 定义逻辑表所属的dataNode，该属性需要与dataNode标签中name对应; 多个dataNode逗号分隔rule: 分片规则的名字，分片规则名字是在rule.xml中定义的

primaryKey: 逻辑表对应真实表的主键

type: 逻辑表的类型，目前逻辑表只有全局表和普通表，如果未配置，就是普通表;全局表，配置为 global

2. datanode标签 数据节点

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

dataNode标签中定义了MyCat中的数据节点,也就是我们通常说的数据分片。一个dataNode标签就是一个独立的数据分片。

核心属性:

name: 定义数据节点名称

dataHost: 数据库实例主机名称，引用自 dataHost 标签中name属性

database: 定义分片所属数据库

3.datahost标签 节点主机、数据源

    <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://121.37.20.23:3306?useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8" user="root" password="root" />
    </dataHost>

该标签在MyCat逻辑库中作为底层标签存在,直接定义了具体的数据库实例、读写分离、心跳语句。

核心属性:

name: 唯一标识，供上层标签使用

maxCon/minCon: 最大连接数/最小连接数

balance: 负载均衡策略，取值 0,1,2,3writeType: 写操作分发方式(0: 写操作转发到第一个

writeHost，第一个挂了，切换到第二个，1: 写操作随机分发到配置的writeHost)

dbDriver: 数据库驱动，支持 native、jdbc

rule.xml

    <tableRule name="auto-sharding-long">
        <rule>
            <columns>id</columns>
            <algorithm>rang-long</algorithm>
        </rule>
    </tableRule>

    <function name="rang-long"
        class="io.mycat.route.function.AutoPartitionByLong">
        <property name="mapFile">autopartition-long.txt</property>
    </function>

rule.xml中定义所有拆分表的规则，在使用过程中可以灵活的使用分片算法,或者对同一个分片算法使用不同的参数，它让分片过程可配置化。主要包含两类标签: tableRule、Function。

server.xml

详细的server配置详情 在网盘：https://www.aliyundrive.com/s/urAAgT1HUQh中“server系统配置信息含义” 有详细的介绍

server.xml配置文件包含了MyCat的系统配置信息,主要有两个重要的标签:system、user

五、Mycat分片

分片规则

rule引用rule.xml

rule.xml存放有很多写好的分别策略

（一）垂直拆分

1.项目需求

2.垂直拆分配置

schema.xml

server.xml加上逻辑数据库名字

重启mycat（关闭，开启）在mycat页面

bin/mycat stop

bin/mycat start

查看mycat日志

tail -f logs/wrapper.log

3.创建表结构

上传sql脚本(创建表格、插入数据)（网盘有）

shopping-table.sql

shopping-insert.sql

创建表格

source /root/shopping-table.sql

可以看到不同的表存放到不同的服务器中的

插入数据

source /root/shopping-insert.sql

完成！

4.多表联查

需要把要跨服务器的多表联查的表格设置为全局表（每个服务器都存在这几张表）

如设置省市区tb_areas_provinces , tb_areas_city , tb_areas_region为全局表，那么每台服务器中都会存在着三张表，就可以实现与这三张表的多表联查了。

1.修改schema.xml

dataNode="dn1,dn2,dn3" 分布到每一台服务器

添加type="global" 多表联查

<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" />

2. 停止mycat

3.把服务器中的表结构先全部清空

4.重启mycat

5.查看日志是否错误

6.重新导入表结构、数据

（二）水平拆分

1. • 项目需求

分别在三台MySQL中创建数据库itcast。

2.配置

配置与垂直拆分一样

重启mycat，导入表格、数据

（三）分片规则

1.范围auto-sharding-log

2.取模 mod_long

3.一致性hash sharding-by-murmur

4. 枚举 sharding-by-intfile-enumstatus

（适用于按照 省份、性别、状态拆分数据等业务）

5.应用指定 sharding-by-substring

应用指定（根据字符串子串（必须是数字））01****：0 、 02****：1

6. 固定分片hash算法 sharding-by-long-hash

该算法类似于十进制的求模运算，但是为二进制的操作，例如，取 id 的二进制低 10 位与 1111111111 进行位&运算。

7.字符串hash sharding-by-strtringhash

8.日期分片 sharding-by-date

9.按月分片 sharding-by-month

六、Mycat管理及监控

命令行管理

Mycat默认开通2个端口，可以在server.xml中进行修改。

8066数据访问端口，即进行DML和DDL操作。

9066数据库管理端口，即mycat 服务管理控制功能，用于管理mycat的整个集群状态

mysql -h 121.37.20.23 -P 9066 -uroot -p12346

Mycat-eye管理

Mycat-web(Mycat-eye)是对mycat-server提供监控服务，功能不局限于对mycat-server使用。他通过JDBC连接对Mycat、Mysql监控监控远程服务器(目前仅限于linux系统)的cpu、内存、网络、磁盘。Mycat-eye运行过程中需要依赖zookeeper，因此需要先安装zookeeper。

安装

Zookeeper安装

MyCat-web安装

网盘MyCat-Web安装文档有安装文档

---

安装Zookeeper

A. 上传安装包

zookeeper-3.4.6.tar.gz

B. 解压

    tar -zxvf zookeeper-3.4.6.tar.gz -C /usr/local/  

C. 创建数据存放目录

    cd /usr/local/zookeeper-3.4.6/

    mkdir data

D. 修改配置文件名称并配置

  cd conf

   mv zoo_sample.cfg zoo.cfg

E. 配置数据存放目录

    vim zoo.cfg

    dataDir=/usr/local/zookeeper-3.4.6/data

dataDir的路径在 C. 创建数据存放目录 中创建的文件目录

F. 启动Zookeeper

cd .. 退出到zookeeper安装目录

    bin/zkServer.sh start

查看是否启动成功

   bin/zkServer.sh status

看到Mode: standalone表示启动成功

安装Mycat-web

A. 上传安装包

Mycat-web.tar.gz

B. 解压

    tar -zxvf Mycat-web.tar.gz -C /usr/local/

C. 目录介绍

etc ----> jetty配置文件

lib ----> 依赖jar包

mycat-web ----> mycat-web项目

readme.txt

start.jar ----> 启动jar

start.sh ----> linux启动脚本

C1.修改

如果不是本机地址

  cd mycat-web/

  vim WEB-INF/classes/mycat.properties

把zookeeper=localhost:2181中的localhost修改为自己的ip地址

D. 启动

   cd /usr/local/mycat-web

    sh start.sh

访问查看

浏览器搜索

ip:8082/mycat