MySQL 分库分表

[toc]

分库 分表

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

目的

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

水平分割

横向切分

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

1550144306328

垂直分割

纵向切分

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

1550144227127

mycat 软件

mycat 介绍

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

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

分片规则

mycat 支持提供10中分片规则

分片规则	对应英文
枚举法	shareding-by-intfile
固定分片	rule1
范围约定	auto-shareding-long
求模法	mod-long
日期列分区法	shareding-by-date
通配取模	shareding-by-pattern
ASCII码求模通配	shareding-by-prefixpattern
编程指定	shareding-by-substring
字符串拆分hash解析	shareding-by-stringhash
一致性hash	shareding-by-murmur

工作过程

1550146505485

当mycat 收到一个SQL查询时

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

配置 mycat

环境部署

拓扑结构

1550147364869

IP规划

拓扑名称	主机名 ( mycat 配置使用 )	角色	数据库名	IP地址
host A	client	客户端	无	192.168.1.106/24
host B	mycat	mycat 服务器	无	192.168.1.101/24
host C	c1	数据库服务器	db1	192.168.1.102/24
host D	c2	数据库服务器	db2	192.168.1.103/24

配置 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

配置

目录结构说明

目录名 或 文件名	说明
bin	mycat 可执行命令
catlet	扩展功能
conf	配置文件
lib	mycat 使用的 jar
log	日志
wrapper.log	mycat 服务启动日志
mycat.log	记录 SQL 囧啊本执行后的报错内容

重要配置文件说明

文件名	说明
server.xml	设置连 mycat 的账号信息
schema.xml	配置 mycat 的真实库表
rule.xml	定义 mycat 分片规则

配置标签说明

标签	说明
<user>.. ..</user>	定义连 mycat 用户信息
<datanode>.. ..</datanode>	指定数据节点
<datahost>.. ..</datahost>	指定数据库地址及用户信息

修改配置文件注意

1550162799660 1550162840398

配置步骤

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

用户名	密码	权限	虚拟数据库名称
root	123456	读写权限	TESTDB
user	user	只读权限	TESTDB

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>