MySQ分库分表与MyCat安装配置

ZZDICT

已于 2024-08-21 14:28:45 修改

阅读量414

点赞数 2

文章标签： mysql 水平分库分表垂直分库分表 schema.xml rule.xml server.xml

于 2024-08-21 10:37:42 首次发布

本文链接：https://blog.csdn.net/Lzcsfg/article/details/141347188

版权

续接下篇：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&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 -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

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

2. user标签

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

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

ZZDICT

关注

2
点赞
踩
5

收藏

觉得还不错? 一键收藏
打赏
0
评论
MySQ分库分表与MyCat安装配置

问题分析IO瓶颈：热点数据太多，数据库缓存不足，产生大量磁盘IO，效率较低。请求数据太多，带宽不够，网络IO瓶颈。CPU瓶颈：排序、分组、连接查询、聚合统计等SQL会耗费大量的CPU资源，请求数太多，CPU出现瓶颈。为了解决上述问题，我们需要对数据库进行分库分表处理。分库分表的中心思想都是将数据分散存储，使得单一数据库/表的数据量变小来缓解单一数据库的性能问题，从而达到提升数据库性能的目的。Mycat是开源的、活跃的、基于Java语言编写的MySQL数据库中间件。
复制链接

扫一扫