MySQL 分库分表

[toc]

分库 分表

• 将存放在一个数据库( 主机 )中的数据,按照特定方式进行拆分,分散存放到多个数据库 ( 主机 )中,以达到分散单台设备负载的效果

目的

• 解决单数据库服务器的访问压力和存储压力
• 解决单表过大的问题

水平分割

横向切分

• 按照表中某个字段的某种规则,把表中的许多记录按行切分,分散到多个数据库中.

1550144306328

垂直分割

纵向切分

1. 将单个表,拆分成多个表,并分散到不同的数据库.
2. 将单个数据库的多个表进行分类,按业务类别分散到不同的数据库上

1550144227127

mycat 软件

mycat 介绍

mycat 是基于 Java 的分布式数据库系统中间层,为高并发环境的分布式访问提供解决方案.

• 支持 JDBC 形式连接
• 支持 MySQL Oracle Sqlserver Mongodb 等
• 提供数据读写分离服务
• 可以实现数据库服务器的高可用
• 提供数据分片服务
• 基于阿里巴巴 Cobar 进行研发的开源软件
• 适合数据大量写入数据的存储需求.( 缺点因为分库分表导致查询效率变慢 )

分片规则

mycat 支持提供10中分片规则

工作过程

1550146505485

当mycat 收到一个SQL查询时

1. 先解析这个SQL查找涉及到的表
2. 然后看此表的定义,如果有分片规则,则获取SQL里分片字段的值,并匹配分片函数,获得分片列表
3. 然后将SQL发往这些分片去执行
4. 最后收集和处理所有分片结果数据,并返回到客户端

配置 mycat

环境部署

拓扑结构

1550147364869

IP规划

配置 mycat

安装

安装 JDK

yum install java-1.8.0-openjdk

rpm -qa|grep -i jdk
java-1.8.0-openjdk-headless-1.8.0.191.b12-1.el7_6.x86_64
java-1.8.0-openjdk-1.8.0.191.b12-1.el7_6.x86_64

java -version
openjdk version "1.8.0_191"
OpenJDK Runtime Environment (build 1.8.0_191-b12)
OpenJDK 64-Bit Server VM (build 25.191-b12, mixed mode)

安装 mycat

wget http://dl.mycat.io/1.6.6.1/Mycat-server-1.6.6.1-release-20181031195535-linux.tar.gz

tar -xf Mycat-server-1.6.6.1-release-20181031195535-linux.tar.gz -C /usr/local

ls /usr/local/mycat/
bin  catlet  conf  lib  logs  version.txt

配置

目录结构说明

重要配置文件说明

配置标签说明

修改配置文件注意

1550162799660

1550162840398

配置步骤

1. 定义连接 mycat 服务的 用户 和 密码 及 虚拟数据库名称.

vim conf/server.xml   
.. ..
        <user name="root" defaultAccount="true">
                <property name="password">123456</property>
                <property name="schemas">TESTDB</property>
        </user>

        <user name="user">
                <property name="password">user</property>
                <property name="schemas">TESTDB</property>
                <property name="readOnly">true</property>
        </user>
.. ..

2. 对哪些表做数据分片及使用的分片规则

   逻辑表名 使用的分片规则 存储到哪个数据库服务器 dn1 dn2

   指定dn1 存储数据库库名 db1

   指定dn2 存储数据库库名 db2

   指定dn1 对应的数据库服务器ip 地址

   指定dn2 对应的数据库服务器ip 地址

vim conf/schema.xml
.. ..
    <!-- 配置 去掉 所有 dn3 节点 因为测试环境只有两个 datanode 此修改启动时会报错,见排错-->
    <table name="travelrecord" dataNode="dn1,dn2" rule="auto-sharding-long" />
    <table name="company" primaryKey="ID" type="global" dataNode="dn1,dn2" />
    <table name="hotnews" primaryKey="ID" autoIncrement="true" dataNode="dn1,dn2" />

.. ..
        <!--配置 节点 dn1 主机名为 c1 数据存储至 db1 -->
        <dataNode name="dn1" dataHost="c1" database="db1" />

        <!--配置 节点 dn2 主机名为 c2 数据存储至 db2 -->
        <dataNode name="dn2" dataHost="c2" database="db2" />

        <!--配置 主机名 c1 ip 端口 mycat 访问 c1 使用 账户 密码 -->    
        <dataHost name="c1" 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.1.102:3306" user="root"
                                   password="123456">                       
                </writeHost>
        </dataHost>

        <!--配置 主机名 c2 ip 端口 mycat 访问 c2 使用 账户 密码 -->
        <dataHost name="c2" 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.1.103:3306" user="root"
                                   password="123456">
                </writeHost>
        </dataHost>
.. ..

3. 修改数据库服务器配置文件

• 添加对应设置后重启 mysqld 服务

vim /etc/my.cnf
[mysqld]
#表名不区分字母大小写
lower_case_table_names=1

• 添加 mycat 访问 数据库授权用户 和 对应库

#c1 db1 
mysql> create database db1;
mysql> grant all on *.* to root@'192.168.1.101' identified by "123456";
#c2 db2
mysql> create database db2;
mysql> grant all on *.* to root@'192.168.1.101' identified by "123456";

启动

usr/local/mycat/bin/mycat start
Starting Mycat-server...

netstat -nltp|grep 8066
tcp6       0      0 :::8066                 :::*                    LISTEN      4708/java

1550162878162

测试

mysql -h192.168.1.101 -uroot -p123456 -P8066
mysql> show databases;
+----------+
| DATABASE |
+----------+
| TESTDB   |
+----------+
mysql> use TESTDB

#mycat 上定义的逻辑表
mysql> show tables;
+------------------+
| Tables in TESTDB |
+------------------+
| company          |
| customer         |
| customer_addr    |
| employee         |
| goods            |
| hotnews          |
| orders           |
| order_items      |
| travelrecord     |
+------------------+
#逻辑表是假表,不存在
mysql> desc employee;
ERROR 1146 (HY000): Table 'db1.employee' doesn't exist

#查看 schema.xml employee表 配置,使用 sharding-by-intfile 表规则,此规则是 枚举法分片
vim conf/schema.xml
<table name="employee" primaryKey="ID" dataNode="dn1,dn2" rule="sharding-by-intfile" />
#查看 sharding-by-intfile 表规则 引用 partition-hash-int.txt 规则
vim conf/rule.xml
<function name="hash-int"
        class="io.mycat.route.function.PartitionByFileMap">
        <property name="mapFile">partition-hash-int.txt</property>
</function>
#查看 partition-hash-int.txt 规则, 10000=0 放入库dn1 10010=1 放入库dn2 可以添加10020 =2
vim conf/partition-hash-int.txt
10000=0
10010=1
#10020=2

#创建 employee 表,必须有 id 和 sharding_id 字段.会在 dn1 dn2 两个库 同时建立 此表.
mysql> create table employee(
    -> id int not null primary key,
    -> name varchar(100),
    -> age int(2),
    -> sharding_id int not null
    -> );
    
#插入数据
mysql> insert into employee(id,name,age,sharding_id) values(1,"bob",21,10000),(2,"lucy",18,100010);

# dn1 上查看
mysql> select * from employee;
+----+------+------+-------------+
| id | name | age  | sharding_id |
+----+------+------+-------------+
|  1 | bob  |   21 |       10000 |
+----+------+------+-------------+
# dn2 上查看
mysql> select * from employee;
+----+------+------+-------------+
| id | name | age  | sharding_id |
+----+------+------+-------------+
|  2 | lucy |   18 |       10010 |
+----+------+------+-------------+

排错

错误1

table [ TRAVELRECORD ] rule function [ rang-long ] partition size : 3 > table datanode size : 2, please make sure table datanode size = function partition size

解决方法

错误说明:

rang-long 算法默认需要 3个 dotanode,测试拓扑只有两个dotanode,所以需要修改 autopartition-long.txt文件

#查看 rule.xml 中配置,找到 rang-long 算法的函数对应配置 autopartition-long.txt
<function name="rang-long"
        class="io.mycat.route.function.AutoPartitionByLong">
        <property name="mapFile">autopartition-long.txt</property>
</function>

编辑配置 注释掉最后一个 datanode

vim conf/autopartition-long.txt
#默认是三个，我们需要删除最后一个，不然就会报错，说节点少了
#K=1000条记录，M=10000条记录，那么下面三个配置就是0~500万的记录会存在数据库节点1的表中，500万~1000万会存在节点2的表中
0-500M=0
500M-1000M=1
#1000M-1500M=2

错误2

table [ HOTNEWS ] rule function [ mod-long ] partition size : 3 > table datanode size : 2, please make sure table datanode size = function partition size

解决方法

错误说明:

和错误1类似, mod-long 算法默认需要 3个 dotanode,测试拓扑只有两个dotanode,需要修改 rule.xml 文件,修改count数为2即可

        <function name="mod-long" class="io.mycat.route.function.PartitionByMod">
                <!-- how many data nodes -->
                <property name="count">2</property>
        </function>