mysql数据库分库分表6_16 MySQL 分库分表

MySQL 分库分表

[toc]

分库 分表

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

目的

解决单数据库服务器的访问压力和存储压力

解决单表过大的问题

水平分割

横向切分

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

1550144306328

垂直分割

纵向切分

将单个表,拆分成多个表,并分散到不同的数据库.

将单个数据库的多个表进行分类,按业务类别分散到不同的数据库上

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查询时

先解析这个SQL查找涉及到的表

然后看此表的定义,如果有分片规则,则获取SQL里分片字段的值,并匹配分片函数,获得分片列表

然后将SQL发往这些分片去执行

最后收集和处理所有分片结果数据,并返回到客户端

配置 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 分片规则

配置标签说明

标签

说明

.. ..

定义连 mycat 用户信息

.. ..

指定数据节点

.. ..

指定数据库地址及用户信息

修改配置文件注意

1550162799660

1550162840398

配置步骤

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

用户名

密码

权限

虚拟数据库名称

root

123456

读写权限

TESTDB

user

user

只读权限

TESTDB

vim conf/server.xml

.. ..

123456

TESTDB

user

TESTDB

true

.. ..

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

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

指定dn1 存储数据库库名 db1

指定dn2 存储数据库库名 db2

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

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

vim conf/schema.xml

.. ..

.. ..

writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">

select user()

password="123456">

writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">

select user()

password="123456">

.. ..

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

添加对应设置后重启 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

#查看 sharding-by-intfile 表规则 引用 partition-hash-int.txt 规则

vim conf/rule.xml

class="io.mycat.route.function.PartitionByFileMap">

partition-hash-int.txt

#查看 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

class="io.mycat.route.function.AutoPartitionByLong">

autopartition-long.txt

编辑配置 注释掉最后一个 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即可

2