MySQ分库分表与MyCat安装配置

介绍

拆分策略

垂直拆分

1. 垂直分库

2. 垂直分表

水平拆分

1. 水平分库‘

2. 水平分表

实现技术

MyCat概述

安装

概念介绍

MyCat入门

需求

环境准备

分片配置

启动服务

连接测试

执行SQL语句测试

MyCat配置

1. schema.xml

1. schema标签

2. datanode标签

3. datahost标签

2. rule.xml

3. server.xml

1. system标签

2. user标签

续接下篇：Mycat分片-垂直拆分-CSDN博客

介绍

问题分析

随着互联网及移动互联网的发展，应用系统的数据量也是成指数式增长，若采用单数据库进行数据存储，存在以下性能瓶颈：

IO瓶颈：热点数据太多，数据库缓存不足，产生大量磁盘IO，效率较低。请求数据太多，带宽不够，网络IO瓶颈。
CPU瓶颈：排序、分组、连接查询、聚合统计等SQL会耗费大量的CPU资源，请求数太多，CPU出现瓶颈。

为了解决上述问题，我们需要对数据库进行分库分表处理。

分库分表的中心思想都是将数据分散存储，使得单一数据库/表的数据量变小来缓解单一数据库的性能问题，从而达到提升数据库性能的目的。

拆分策略

分库分表的形式，主要是两种：垂直拆分和水平拆分。而拆分的粒度，一般又分为分库和分表，所以组成的拆分策略最终如下：

垂直拆分

1. 垂直分库

垂直分库：以表为依据，根据业务将不同表拆分到不同库中。

特点：

每个库的表结构都不一样。

每个库的数据也不一样。

所有库的并集是全量数据。

2. 垂直分表

垂直分表：以字段为依据，根据字段属性将不同字段拆分到不同表中。

特点：

每个表的结构都不一样。

每个表的数据也不一样，一般通过一列（主键/外键）关联。

所有表的并集是全量数据。

水平拆分

1. 水平分库‘

水平分库：以字段为依据，按照一定策略，将一个库的数据拆分到多个库中。

特点：

每个库的表结构都一样。

每个库的数据都不一样。

所有库的并集是全量数据。

2. 水平分表

水平分表：以字段为依据，按照一定策略，将一个表的数据拆分到多个表中。

特点：

每个表的表结构都一样。

每个表的数据都不一样。

所有表的并集是全量数据。

在业务系统中，为了缓解磁盘IO及CPU的性能瓶颈，到底是垂直拆分，还是水平拆分；具体是分库，还是分表，都需要根据具体的业务需求具体分析。

实现技术

shardingJDBC：基于AOP原理，在应用程序中对本地执行的SQL进行拦截，解析、改写、路由处理。需要自行编码配置实现，只支持java语言，性能较高。
MyCat：数据库分库分表中间件，不用调整代码即可实现分库分表，支持多种语言，性能不及前者。

MyCat概述

Mycat是开源的、活跃的、基于Java语言编写的MySQL数据库中间件。可以像使用mysql一样来使用 mycat，对于开发人员来说根本感觉不到mycat的存在。

开发人员只需要连接MyCat即可，而具体底层用到几台数据库，每一台数据库服务器里面存储了什么数据，都无需关心。具体的分库分表的策略，只需要在MyCat中配置即可。

优势：性能可靠稳定强大的技术团队体系完善社区活跃

安装

准备三台虚拟机，关闭防火墙和selinux，进行时间同步。

IP	安装软件	说明	配置	系统
192.168.226.100	JDK8,Mycat1.6.7.4,MySQL8.0.39	MyCat中间件服务器；分片服务器	2核心2G-20G磁盘	centos7-2009-mini
192.168.226.101	MySQL8.0.39	分片服务器	2核心2G-20G磁盘	centos7-2009-mini
192.168.226.102	MySQL8.0.39	分片服务器	2核心2G-20G磁盘	centos7-2009-mini

初始化脚本

#!/bin/bash
echo "=====系统环境初始化脚本====="
sleep 3
echo "——>>> 关闭防火墙与SELinux <<<——"
sleep 3
systemctl stop firewalld
systemctl disable firewalld &> /dev/null
setenforce 0
sed -i '/SELINUX/{s/enforcing/disabled/}' /etc/selinux/configecho "——>>> 创建阿里仓库 <<<——"
sleep 3
rm -rf /etc/yum.repos.d/*
curl -o /etc/yum.repos.d/CentOS-Base.repo https://mirrors.aliyun.com/repo/Centos-7.repo 
yum -y install wget
wget -O /etc/yum.repos.d/epel.repo http://mirrors.aliyun.com/repo/epel-7.repo  echo "——>>> 设置时区并同步时间 <<<——"
sleep 3
timedatectl set-timezone Asia/Shanghai
yum -y install chrony
systemctl start chronyd
systemctl enable chronyd
reboot

给三台主机下载MySQL，并给root用户设置了简单密码为1234

sudo yum remove mysql-server -y && sudo yum autoremove -y
sudo yum remove *mysql* -y
sudo rm -rf /var/lib/mysql/ 
sudo rm -rf /etc/mysql/ yum install -y yum-utils > /dev/null
yum install -y https://dev.mysql.com/get/mysql80-community-release-el7-11.noarch.rpm > /dev/null
yum-config-manager --enable mysql80-community > /dev/null
yum-config-manager --disable mysql57-community > /dev/null
yum install -y mysql-server
systemctl start mysqld && systemctl enable mysqld
mysqladmin -p"`awk '/temporary password/{p=$NF}END{print p}' /var/log/mysqld.log`" password 'TianPFh@123'
mysql -p'TianPFh@123' -e "UNINSTALL COMPONENT 'file://component_validate_password'"
mysqladmin -p'TianPFh@123' password '1234'

接下来仅对192.168.226.100主机操作

下载JDK8的rpm包形式：Java Archive Downloads - Java SE 8 | Oracle 台灣

上传到192.168.226.100主机安装

yum install -y jdk-8u202-linux-x64.rpm

下载Mycat1.6.7.4版本地址：链接：https://pan.baidu.com/s/1MGzS9TtBz7A9PEKjshvzRg?pwd=e2do
提取码：e2do

tar -zxf Mycat-server-1.6.7.3-release-20210913163959-linux.tar.gz

mv mycat/ /usr/local/

更换驱动包

cd /usr/local/mycat/lib/
# 删除旧版本
rm -rf mysql-connector-java-5.1.35.jar# 然后将mysql-connector-java-8.0.22.jar上传到本目录里
# 授权
chmod +777 mysql-connector-java-8.0.22.jar[root@master lib]# ll |grep mysql-connec*
-rwxrwxrwx 1 root root 2389216 2月  22 2022 mysql-connector-java-8.0.22.jar

概念介绍

在MyCat的整体结构中，分为两个部分：上面的逻辑结构、下面的物理结构。

在MyCat的逻辑结构主要负责逻辑库、逻辑表、分片规则、分片节点等逻辑结构的处理，而具体的数据存储还是在物理结构，也就是数据库服务器中存储的。

MyCat入门

需求

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

环境准备

在上面已经准备好啦这三台服务器并安装好了相关软件了

192.168.226.100：MyCat中间件服务器，同时也是第一个分片服务器。

192.168.226.101：第二个分片服务器。

192.168.226.102：第三个分片服务器。

并且在上述3台数据库中创建数据库 db01 。并创建一个可以远程并授权的root用户，此远程root密码为admin 下面是sql语句一键完成。

CREATE DATABASE db01;
create user 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'admin';
grant all on *.* to 'root'@'%';
FLUSH PRIVILEGES;

分片配置

这里我配置使用Notepad++连接服务器进行编辑文件，较为方便，连接方式：Notepad++使用SFTP连接虚拟机编辑文档_notepad ftp-CSDN博客

编辑 /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.226.100:3306?useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8" user="root" password="admin" /></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.226.101:3306?useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8" user="root" password="admin" /></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.226.102:3306?useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8" user="root" password="admin" /></dataHost></mycat:schema>

这里定义了DB01这个逻辑库，逻辑库关联travelrecord这个逻辑表，将其分为dn1,dn2,dn3这三个分片，并将三个分片分别关联到了dhost1，dhost3，dhost3这三个节点，然后定义了这三个节点要关联的数据都为db01库，然后下面将每个节点主机关联到实际mysql节点主机，并配置了节点主机的URL，用户名和密码。

编辑 /usr/local/mycat/conf/server.xml 文件

需要在server.xml中配置用户名、密码，以及用户的访问权限信息，具体的配置如下：

下滑到文件尾部，按照如下修改配置，password为root登录mycat的登录密码，schemas对应的是逻辑库的名字

这里用户名和密码是登陆mycat的，和mysql无关。

此文件仅需要修改下述代码对应的模块即可，其它内容不用改动。

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

启动服务

# 启动
/usr/local/mycat/bin/mycat start# 停止命令：/usr/local/mycat/bin/mycat stop

[root@master ~]# ss -tnlp
State       Recv-Q Send-Q                           Local Address:Port                                          Peer Address:Port              
LISTEN      0      128                                          *:22                                                       *:*                   users:(("sshd",pid=913,fd=3))
LISTEN      0      1                                    127.0.0.1:32000                                                    *:*                   users:(("java",pid=16698,fd=4))
LISTEN      0      128                                       [::]:22                                                    [::]:*                   users:(("sshd",pid=913,fd=4))
LISTEN      0      50                                        [::]:1984                                                  [::]:*                   users:(("java",pid=16698,fd=85))
LISTEN      0      100                                       [::]:8066                                                  [::]:*                   users:(("java",pid=16698,fd=111))
LISTEN      0      50                                        [::]:44003                                                 [::]:*                   users:(("java",pid=16698,fd=88))
LISTEN      0      70                                        [::]:33060                                                 [::]:*                   users:(("mysqld",pid=963,fd=21))
LISTEN      0      50                                        [::]:40069                                                 [::]:*                   users:(("java",pid=16698,fd=84))
LISTEN      0      100                                       [::]:9066                                                  [::]:*                   users:(("java",pid=16698,fd=107))
LISTEN      0      128                                       [::]:3306                                                  [::]:*                   users:(("mysqld",pid=963,fd=24))

连接测试

可以看到mycat中的逻辑库与逻辑表

[root@master ~]# mysql -h 192.168.226.100 -P8066 -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.29-mycat-1.6.7.3-release-20210913163959 MyCat Server (OpenCloudDB)Copyright (c) 2000, 2024, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> show databases;
+----------+
| DATABASE |
+----------+
| DB01     |
+----------+
1 row in set (0.00 sec)mysql> use DB01;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -ADatabase changed
mysql> show tables;
+----------------+
| Tables in DB01 |
+----------------+
| tb_order       |
+----------------+
1 row in set (0.00 sec)

实际的mysql库中是没有变化的

[root@slave ~]# mysql -p1234
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 37
Server version: 8.0.39 MySQL Community Server - GPLCopyright (c) 2000, 2024, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db01               |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)mysql> use db01
Database changed
mysql> show tables;
Empty set (0.00 sec)

执行SQL语句测试

在mycat中执行sql语句

[root@master ~]# mysql -h 192.168.226.100 -P8066 -p123456   # 登录mycat执行--登录mycat执行这些sql语句，然后登录这三台主机的mysql，看哪个mysql存储了数据.
--因为当前使用的默认分片规则，下面插入的数据会根据一定规则分别进入不同的数据库中.
--规则文件的位置在：/usr/local/mycat/conf/rule.xml  引用规则的文件就在/usr/local/mycat/conf/schema.xml里-- 创建表TB_ORDER  
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(5000000,'goods5000000');
INSERT INTO TB_ORDER(id,title) VALUES(10000000,'goods10000000');
INSERT INTO TB_ORDER(id,title) VALUES(10000001,'goods10000001');
INSERT INTO TB_ORDER(id,title) VALUES(15000000,'goods15000000');
INSERT INTO TB_ORDER(id,title) VALUES(15000001,'goods15000001');

经过测试，我们发现，在往 TB_ORDER 表中插入数据时：

如果id的值在1-500w之间，数据将会存储在第一个分片数据库中。

如果id的值在500w-1000w之间，数据将会存储在第二个分片数据库中。

如果id的值在1000w-1500w之间，数据将会存储在第三个分片数据库中。

如果id的值超出1500w，在插入数据时，将会报错。

这是由逻辑表配置时的一个参数 rule 决定的，而这个参数配置的就是分片规则。

MyCat配置

1. schema.xml

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

主要包含以下三组标签：

schema标签：schema 标签定义了一个数据库模式，包含多个数据节点、路由规则和分片策略。

datanode标签：schema 标签定义了一个数据库模式，包含多个数据节点、路由规则和分片策略。

datahost标签：datahost 标签定义了数据源的连接信息，包括数据库的地址、用户名、密码等。

1. schema标签

schema 定义逻辑库

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

核心属性：

name：指定自定义的逻辑库库名
checkSQLschema：在SQL语句操作时指定了数据库名称，执行时是否自动去除；true：自动去除，false：不自动去除
sqlMaxLimit：如果未指定limit进行查询，列表查询模式查询多少条记录

schema 中的table定义逻辑表

table 标签定义了MyCat中逻辑库schema下的逻辑表 , 所有需要拆分的表都需要在table标签中定义核心属性
name：定义逻辑表表名，在该逻辑库下唯一
dataNode：定义逻辑表所属的dataNode，该属性需要与dataNode标签中name对应；多个dataNode逗号分隔
rule：分片规则的名字，分片规则名字是在rule.xml中定义的
primaryKey：逻辑表对应真实表的主键
type：逻辑表的类型，目前逻辑表只有全局表和普通表，如果未配置，就是普通表；全局表，配置为global

2. datanode标签

核心属性：

name：定义数据节点名称
dataHost：数据库实例主机名称，引用自 dataHost 标签中name属性
database：定义分片所属数据库

3. datahost标签

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

核心属性：

name：唯一标识，供上层标签使用
maxCon/minCon：最大连接数/最小连接数
balance：负载均衡策略，取值 0,1,2,3
writeType：写操作分发方式（0：写操作转发到第一个writeHost，第一个挂了，切换到第二个；1：写操作随机分发到配置的writeHost）
dbDriver：数据库驱动，支持 native、jdbc

2. rule.xml

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

3. server.xml

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

1. system标签

此图为部分展示

主要配置MyCat中的系统配置信息，对应的系统配置项及其含义，如下：

属性	取值	含义
charset	utf8	设置 MyCat 的字符集，字符集需要与 MySQL 的字符集保持一致。
nonePasswordLogin	0, 1	0: 需要密码登录；1: 不需要密码登录。默认值为 0。如果设置为 1，则需要指定默认账户。
useHandshakeV10	0, 1	是否使用 HandshakeV10Packet 来与客户端进行通信。1: 使用；0: 不使用。主要用于兼容高版本的 JDBC 驱动。
useSqlStat	0, 1	0: 关闭 SQL 实时统计；1: 开启 SQL 实时统计。开启后 MyCat 会自动统计 SQL 执行情况。
useGlobleTableCheck	0, 1	0: 关闭全局表一致性检测；1: 开启全局表一致性检测。
sqlExecuteTimeout	1000 (秒)	SQL 语句执行的超时时间，单位为秒。如果 SQL 执行超时，则会关闭连接。
sequnceHandlerType	0, 1, 2	0: 本地文件方式；1: 数据库方式；2: 时间戳列方式。默认使用本地文件方式。
sequnceHandlerPattern	正则表达式	必须带有 MYCATSEQ 或 mycatseq 进入序列匹配流程。注意 MYCATSEQ_ 有空格的情况。
subqueryRelationshipCheck	true, false	在子查询中存在关联查询的情况下，检查关联字段中是否有分片字段。默认值为 false。
useCompression	0, 1	0: 关闭 MySQL 压缩协议；1: 开启 MySQL 压缩协议。
fakeMySQLVersion	5.5, 5.6	设置模拟的 MySQL 版本号。
defaultSqlParser	druidparser, fdbparser	指定默认的 SQL 解析器。MyCat 1.4 之后，默认是 druidparser，fdbparser 已经废除。
processors	1, 2, ...	指定系统可用的线程数量，默认值为 CPU 核心 × 每个核心运行线程数量。影响性能调优时的线程池配置。
processorBufferChunk	字节数	指定每次分配 Socket Direct Buffer 的大小，默认值为 4096 字节。
processorExecutor	数字	指定 NIOProcessor 上共享的 businessExecutor 固定线程池的大小。
packetHeaderSize	数字	指定 MySQL 协议中的报文头长度，默认值为 4 个字节。
maxPacketSize	字节数	指定 MySQL 协议可以携带的数据最大大小，默认值为 16MB。
idleTimeout	30	指定连接的空闲时间的超时长度，单位为分钟。如果超时，将关闭资源并回收。默认值为 30 分钟。
txIsolation	1, 2, 3, 4	初始化前端连接的事务隔离级别。默认值为 REPEATED_READ（数字 3）。
serverPort	8066	定义 MyCat 的使用端口，默认值为 8066。
managerPort	9066	定义 MyCat 的管理端口，默认值为 9066。