MySQ分库分表与MyCat安装配置

我可以不吃啊 · 2024-8-26 17:18:38

目次
介绍
拆分计谋
垂直拆分
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/config
echo "——>>> 创建阿里仓库 <<<——"
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&serverTimezone=Asia/Shanghai&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&serverTimezone=Asia/Shanghai&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&serverTimezone=Asia/Shanghai&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>
<!--
<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 -A
Database 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 - GPL
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中的系统配置信息，对应的系统配置项及其含义，如下：
属性取值含义charsetutf8设置 MyCat 的字符集，字符集需要与 MySQL 的字符集保持一致。nonePasswordLogin0, 10: 需要密码登录；1: 不需要密码登录。默认值为 0。假如设置为 1，则需要指定默认账户。useHandshakeV100, 1是否使用 HandshakeV10Packet 来与客户端举行通信。1: 使用；0: 不使用。重要用于兼容高版本的 JDBC 驱动。useSqlStat0, 10: 关闭 SQL 实时统计；1: 开启 SQL 实时统计。开启后 MyCat 会主动统计 SQL 执行情况。useGlobleTableCheck0, 10: 关闭全局表一致性检测；1: 开启全局表一致性检测。sqlExecuteTimeout1000 (秒)SQL 语句执行的超时时间，单位为秒。假如 SQL 执行超时，则会关闭连接。sequnceHandlerType0, 1, 20: 本地文件方式；1: 数据库方式；2: 时间戳列方式。默认使用本地文件方式。sequnceHandlerPattern正则表达式必须带有 MYCATSEQ 或 mycatseq 进入序列匹配流程。注意 MYCATSEQ_ 有空格的情况。subqueryRelationshipChecktrue, false在子查询中存在关联查询的情况下，查抄关联字段中是否有分片字段。默认值为 false。useCompression0, 10: 关闭 MySQL 压缩协议；1: 开启 MySQL 压缩协议。fakeMySQLVersion5.5, 5.6设置模拟的 MySQL 版本号。defaultSqlParserdruidparser, fdbparser指定默认的 SQL 剖析器。MyCat 1.4 之后，默认是 druidparser，fdbparser 已经废除。processors1, 2, ...指定系统可用的线程数目，默认值为 CPU 焦点 × 每个焦点运行线程数目。影响性能调优时的线程池配置。processorBufferChunk字节数指定每次分配 Socket Direct Buffer 的巨细，默认值为 4096 字节。processorExecutor数字指定 NIOProcessor 上共享的 businessExecutor 固定线程池的巨细。packetHeaderSize数字指定 MySQL 协议中的报文头长度，默认值为 4 个字节。maxPacketSize字节数指定 MySQL 协议可以携带的数据最大巨细，默认值为 16MB。idleTimeout30指定连接的空闲时间的超时长度，单位为分钟。假如超时，将关闭资源并接纳。默认值为 30 分钟。txIsolation1, 2, 3, 4初始化前端连接的事务隔离级别。默认值为 REPEATED_READ（数字 3）。serverPort8066界说 MyCat 的使用端口，默认值为 8066。managerPort9066界说 MyCat 的管理端口，默认值为 9066。
2. user标签

配置MyCat中的用户、访问密码，以及用户针对于逻辑库、逻辑表的权限信息，具体的权限形貌方式及配置说明如下：

在测试权限操作时，我们只需要将 privileges 标签的注释放开。在 privileges 下的schema 标签中配置的dml属性配置的是逻辑库的权限。在privileges的schema下的table标签的dml属性中配置逻辑表的权限。

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。

		自动登录	找回密码
密码			立即注册

MySQ分库分表与MyCat安装配置

本帖子中包含更多资源

0 个回复

快速回复

楼主热帖

标签云