Mycat分库分表

0x00 数据切分

简单来说,就是指通过某种特定的条件,将我们存放在同一个数据库中的数据分散存放到多个数据库(主
机)上面,以达到分散单台设备负载的效果,即分库分表。

数据的切分根据其切分规则的类型,可以分为两种切分模式:

1
2
3
垂直(纵向)切分: 按照不同的表(或者Schema)来切分到不同的数据库(主机)之上;
水平(横向)切分: 根据表中的数据的逻辑关系,将同一个表中的数据按照某种条件拆分到多台数据库(主机)上面。

1. 垂直切分

一个数据库由多个表构成,每个表对应着不同的业务,垂直切分是指按照业务将表进行分类,分布到不同
的数据库上面,这样也就将数据或者说压力分担到不同的库上面(专库专用)。例:

1
2
3
4
5
#有如下几张表
--------------+--------------+------------------
用户信息(user)+ 交易记录(pay)+ 商品(commodity)|
--------------+--------------+------------------

垂直切分根据每个表的不同业务进行切割,user表,pay表,commodity表,每个表切分到不同的数据库上。

垂直切分

优点:

  • 拆分后业务清晰,拆分规则明确。
  • 系统之间整合或扩展容易。
  • 数据维护简单。

缺点:

  • 部分业务表无法 join,只能通过接口方式解决,提高了系统复杂度。
  • 受每种业务不同的限制存在单库性能瓶颈,不易数据扩展跟性能提高。
  • 事务处理复杂。

2. 水平切分

相对于垂直拆分,水平拆分不是将表做分类,而是按照某个字段的某种规则来分散到多个库之中,每个表中
包含一部分数据。简单来说,我们可以将数据的水平切分理解为是按照数据行的切分,就是将表中的某些行切分
到一个数据库,而另外的某些行又切分到其他的数据库中,如图:

水平切分

优点:

  • 拆分规则抽象好,join 操作基本可以数据库做。
  • 不存在单库大数据,高并发的性能瓶颈。
  • 应用端改造较少。
  • 提高了系统的稳定性跟负载能力。

缺点:

  • 拆分规则难以抽象。
  • 分片事务一致性难以解决。
  • 数据多次扩展难度跟维护量极大。
  • 跨库 join 性能较差。

共同特点缺点

  • 引入分布式事务的问题
  • 跨节点 Join 的问题
  • 跨节点合并排序分页问题
  • 多数据源管理问题

介绍完两种切分方式,我们就可以根据自己的业务需求来选择,接下来我们通过中间件来进行数据的切分

0x01 分库分表中间件Mycat

Mycat它是一个开源的分布式数据库系统, 强大的数据库中间件,实现了 MySQL 协议的的Server,前端用户可以把它看作是一个数据库代理,用 MySQL 客户端工具和命令行访问,而其后端可以用MySQL 原生(Native)协议与多个 MySQL 服务器通信,也可以用 JDBC 协议与大多数主流数据库服务器通信,其核心功能是分表分库,即将一个大表水平分割为 N 个小表,存储在后端 MySQL 服务器里或者其他数据库里。

实验机器环境CentOS6.8(2台), JDK8, Mycat1.6, MySql5.1.73

1
2
server1 192.168.1.214 mysql
server2 192.168.1.216 mycat JDK

1. 安装Mycat

Mycat依赖java环境,所以要安装jdk,这里直接使用yum -y install java 安装java环境

如果需要jps, jmap等命令则执行yum -y install java-devel

下载Mycat

1
2
3
4
5
6
7
#执行在server2
mkdir -p /usr/local/mycat && cd /usr/local/mycat
wget -c -t5 -O Mycat-1.6.tar.gz "http://dl.mycat.io/1.6-RELEASE/Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz"
tar xvf Mycat-1.6.tar.gz -C ./

我们来大概看一下Mycat的目录结构:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
.
├── bin
│ ├── mycat Mycat启动程序
│ ├── ...
├── catlet
├── conf
│ ├── log4j2.xml 日志的配置 根据自己的需要可以调整输出级别为debug
│ ├── rule.xml 分片规则的配置文件,分片规则的具体参数信息单独存放为文件,也在当前目录,配置文件修改需要重启MyCAT。
│ ├── schema.xml 逻辑库定义和表以及分片定义的配置文件。
│ ├── server.xml Mycat服务器参数调整和用户授权的配置文件。
│ ├── wrapper.conf JVM内存配置文件
│ ├── zkconf Zookeeper配置目录
├── lib MyCAT自身的jar包或依赖的jar包的存放目录。
├── logs MyCAT日志的存放目录。日志存放在logs/log中,每天一个文件。
└── version.txt 版本信息

2. 配置

配置Mycat服务器参数

编辑Mycat服务器参数调整和用户授权的配置文件vim conf/server.xml(配置文件中有注释)首先备份一份,cp conf/server.xml conf/server.xml.bak
跳转到文件尾部编辑如下

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
<!--此处定义的用户,为Client连接Mycat的用户密码,可以配置相关数据库及表读写的权限,-->
<user name="root">
<property name="password">test</property>
<property name="schemas">TESTDB</property>
<!--<property name="readOnly">true</property>-->
</user>
<!-- 表级 DML 权限设置 -->
<!--
<privileges check="false">
<schema name="TESTDB" dml="0110" >
<table name="tb01" dml="0000"></table>
<table name="tb02" dml="1111"></table>
</schema>
</privileges>
-->
<!--此处定义连接IP白名单和 SQL 黑名单-->
<!-- 全局SQL防火墙设置 -->
<!--
<firewall>
<whitehost>
<host user="mycat" host="127.0.0.1"></host> ip 白名单 用户对应的可以访问的 ip 地址
</whitehost>
<blacklist check="true">
<property name="selelctAllow">false</property> 黑名单允许的 权限 后面为默认
</blacklist>
</firewall>
-->
#

配置逻辑库表,分片定义

编辑逻辑库定义和表以及分片定义的配置文件vim conf/schema.xml同样先备份一份cp conf/schema.xml conf/schema.xml.bak编辑为如下内容

有如下表结构:

1
2
3
4
5
6
7
8
9
10
11
12
13
+--------------------+-------+------------------------+--------------------+
| ID | LEVEL | EXTEND_ATTR | UID |
+--------------------+-------+------------------------+--------------------+
| 298599220716244992 | 1 | {} | 298321762779664384 |
| 298600412901019648 | 0 | {} | 298321762779664384 |
| 298682157746884608 | 3 | {"129":2} | 298667788531142656 |
| 298684200884637696 | 2 | {} | 298667788531142656 |
| 298685806279659520 | 2 | {} | 298667788531142656 |
| 298686594007699456 | 2 | {} | 298667788531142656 |
| 298687219567169536 | 2 | {} | 298667788531142656 |
| 298688215626289152 | 1 | {} | 298667788531142656 |
| 298689820996145152 | 1 | {} | 298667788531142656 |
+--------------------+-------+------------------------+--------------------+

这里选择根据UID进行水平切分,配置文件如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<!--定义逻辑数据库TESTDB,逻辑表t_rune,主键、切分规则(mod-long),和数据库节点(dn2,dn2,dn3)-->
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100">
<table name="t_rune" primaryKey="uid" dataNode="dn2,dn2,dn3" rule="mod-long" >
</table>
</schema>
<!--定义每个数据库节点相关信息-->
<dataNode name="dn2" dataHost="server1" database="db1" />
<dataNode name="dn2" dataHost="server1" database="db2" />
<dataNode name="dn3" dataHost="server1" database="db3" />
<!--等同于以上配置<dataNodename=“dn$1-3” dataHost=“server1” database=“db$1-3”/> -->
<!--定义主机信息,及Mysql用户密码-->
<dataHost name="server1" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<!--心跳检测语句-->
<heartbeat>select user()</heartbeat>
<!-- 远程数据库配置信息 -->
<writeHost host="192.168.99.21" url="192.168.99.214:3306" user="root"
password="root">
</writeHost>
</dataHost>
</mycat:schema>

无论分片与否,每张表都需要定义, 生产,例:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="bigduck" checkSQLschema="true" sqlMaxLimit="100">
<!--分-->
<!--t_shop_visible-->
<table name="t_shop_visible" primaryKey="ID" dataNode="dn$1-15" rule="mod-long"> </table>
<!--t_friendlog-->
<table name="t_friendlog" primaryKey="ID" dataNode="dn$1-15" rule="mod-long"> </table>
<!--t_room-->
<table name="t_room" primaryKey="ID" dataNode="dn$1-15" rule="mod-long"> </table>
<!--t_npc-->
<table name="t_npc" primaryKey="ID" dataNode="dn$1-15" rule="mod-long"> </table>
<!--t_arena_fight-->
<table name="t_arena_fight" primaryKey="ID" dataNode="dn$1-15" rule="mod-long"> </table>
<!--不分-->
<!--alliance_id-->
<table name="alliance_id" primaryKey="seq_id" dataNode="dn2"> </table>
<!--t_alliance_boss-->
<table name="t_alliance_boss" primaryKey="ALLIANCEID" dataNode="dn2"> </table>
<!--t_alliance_boss_id-->
<table name="t_alliance_boss_id" primaryKey="ALLIANCEID" dataNode="dn2"> </table>
<!--t_alliance_boss_log-->
<table name="t_alliance_boss_log" primaryKey="ID" dataNode="dn2"> </table>
<!--t_alliance_member_boss-->
<table name="t_alliance_member_boss" primaryKey="UID" dataNode="dn2"> </table>
<!-- global table is auto cloned to all defined data nodes ,so can join
with any table whose sharding node is in the same data node -->
<!--全局表-->
<!--t_arena-->
<table name="t_arena" primaryKey="UID" type="global" dataNode="dn2"> </table>
</schema>
<dataNode name="dn$1-5" dataHost="mysql-server-01" database="db$11-15" />
<dataNode name="dn$6-10" dataHost="mysql-server-02" database="db$21-25" />
<dataNode name="dn$11-15" dataHost="mysql-server-03" database="db$31-35" />
<dataHost name="mysql-server-01" maxCon="2000" minCon="100" balance="0" dbType="mysql" dbDriver="native">
<heartbeat>select user()</heartbeat>
<writeHost host="172.17.0.2" url="172.17.0.2:3306" user="root"
password="pa33w0rd">
</writeHost>
</dataHost>
<dataHost name="mysql-server-02" maxCon="2000" minCon="100" balance="0" dbType="mysql" dbDriver="native">
<heartbeat>select user()</heartbeat>
<writeHost host="172.17.0.3" url="172.17.0.3:3306" user="root"
password="pa33w0rd">
</writeHost>
</dataHost>
<dataHost name="mysql-server-03" maxCon="2000" minCon="100" balance="0" dbType="mysql" dbDriver="native">
<heartbeat>select user()</heartbeat>
<writeHost host="172.17.0.4" url="172.17.0.4:3306" user="root"
password="pa33w0rd">
</writeHost>
</dataHost>
</mycat:schema>

配置切片规则

在上步的schema.xml文件中定义了分片规则:rule="mod-long"此时编辑conf/rule.xml文件添加分片规则的具体参数信息

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
<tableRule name="mod-long">
<rule>
<columns>uid</columns>
<algorithm>mod-long</algorithm>
</rule>
</tableRule>
<!--
name 属性指定唯一的名字,用于标识不同的表规则。
内嵌的 rule 标签则指定对物理表中的哪一列进行拆分和使用什么路由算法。
columns 内指定要拆分的列名字。
algorithm 使用 function 标签中的 name 属性。连接表规则和具体路由算法。当然,多个表规则可以连接到
同一个路由算法上。table 标签内使用。让逻辑表使用这个规则进行分片。
-->
<function name="mod-long" class="io.mycat.route.function.PartitionByMod">
<!--注意!这里填写数据库节点数,否则无法分片-->
<property name="count">3</property>
</function>
<!--
name 指定算法的名字。
class 制定路由算法具体的类名字。(此处使用求模法)
property 为具体算法需要用到的一些属性。
-->

具体切片算法规则:Mycat切片算法规则详解

0x02 配置数据库节点

配置server1数据库,用户密码为schema.xml文件中配置相同,并允许server2运程连接

1
2
grant all privileges on *.* to 'root'@'192.168.99.216' identified by 'root' with grant option;
flush privileges;

创建配置文件中数据库节点db1,db2.db3

1
2
3
create database db1 character set utf8;
create database db2 character set utf8;
create database db3 character set utf8;

测试环境中确认2台机器防火墙关闭server iptables stop

返回到server1启动Mycat

1
2
3
4
5
6
7
8
9
10
11
12
13
14
cd /usr/local/mycat
./bin/mycat start
[[email protected] mycat]# netstat -ntlp
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
tcp 0 0 127.0.0.1:32000 0.0.0.0:* LISTEN 8613/java
tcp 0 0 :::1984 :::* LISTEN 8613/java
tcp 0 0 :::8066 :::* LISTEN 8613/java
#确保8066端口监听此为Mycat服务器默认端口
tcp 0 0 :::60743 :::* LISTEN 8613/java
tcp 0 0 :::43303 :::* LISTEN 8613/java
tcp 0 0 :::9066 :::* LISTEN 8613/java

0x03 数据迁移

编辑/etc/my.cnf添加

1
2
3
4
[mysqldump]
max_allowed_packet = 16M
#default-character-set = utf8mb4

mysqldump导入报ERROR 1153 (08S01) at line 1133809: Got a packet bigger than 'max_allowed_packet' bytes错误,因为MySQL允许的默认大小为1MB。

注意,Mycat在执行INSERT语句时需要完整INSERT语句(用列名)

1
2
3
4
5
6
mysqldump -uroot -p -c --skip-add-locks --skip-extended-insert --no-autocommit databaseName > databaseName.sql
# -c 参数不可少,-c, 全称为–complete-insert 表示使用完整的 insert 语句(用列名字)。
# --skip-add-locks 表示导数据时不加锁,如果加锁涉及多分片时容易导致死锁。
# --skip-extended-insert 将每行数据输出为一个单独的insert语句
# --no-autocommit 参数在每个表格所有的插入语句的前后分别增加SET autocommit = 0和COMMIT语句。相比没有这个参数,插入速度能差出至少200倍,分别是10000QPS和50QPS

导入数据

1
2
3
mysql -uroot -ptest -h192.168.99.216 -P8006 TESTDB #连接Mycat
source /t_rune.sql #导入数据

成功后可以在server2中看到db1,db2.db3已经存入数据。错误日志在 logs/mycat.log文件中

连接Mycat select * from table 时,Mycat默认limit 100此配置可从schema.xml文件中修改sqlMaxLimit="100"定义

0x04 会用到的sql

查询所有表的主键

1
SELECT k.column_name,k.table_name FROM information_schema.table_constraints t JOIN information_schema.key_column_usage k USING (constraint_name,table_schema,table_name) WHERE t.constraint_type='PRIMARY KEY' AND t.table_schema='数据库';

查询所有表的记录数(行数)

1
2
3
select table_name,table_rows from information_schema.tables where TABLE_SCHEMA = '数据库' order by table_rows desc;
#对于InnoDB表,table_rows行计数仅是大概估计值。