龙华网站制作要多少钱,广西人才网官方网站,住房和城乡建设局待遇,网页升级维护每天更新1. 日志
1.1 错误日志
错误日志是 MySQL 中最重要的日志之一#xff0c;它记录了当 mysqld 启动和停止时#xff0c;以及服务器在运行过 程中发生任何严重错误时的相关信息。当数据库出现任何故障导致无法正常使用时#xff0c;建议首先查看此日志。
该日志是默认开启的它记录了当 mysqld 启动和停止时以及服务器在运行过 程中发生任何严重错误时的相关信息。当数据库出现任何故障导致无法正常使用时建议首先查看此日志。
该日志是默认开启的默认存放目录 /var/log/默认的日志文件名为 mysqld.log 。查看日志位置
show variables like %log_error%; 1.2 二进制日志
1.2.1 介绍
二进制日志BINLOG记录了所有的 DDL数据定义语言语句和 DML数据操纵语言语句但不包括数据查询SELECT、SHOW语句。
作用①. 灾难时的数据恢复②. MySQL的主从复制。在MySQL8版本中默认二进制日志是开启着的涉及到的参数如下
show variables like %log_bin%; 参数说明
log_bin_basename当前数据库服务器的binlog日志的基础名称(前缀)具体的binlog文件名需要在该basename的基础上加上编号(编号从000001开始)。 log_bin_indexbinlog的索引文件里面记录了当前服务器关联的binlog文件有哪些。
1.2.2 格式
MySQL服务器中提供了多种格式来记录二进制日志具体格式及特点如下 show variables like %binlog_format%; 如果需要配置二进制日志的格式只需要在 /etc/my.cnf 中配置 binlog_format 参数即可。
1.2.3 查看
由于日志是以二进制方式存储的不能直接读取需要通过二进制日志查询工具 mysqlbinlog 来查 看具体语法
mysqlbinlog [ 参数选项 ] logfilename参数选项-d 指定数据库名称只列出指定的数据库相关操作。-o 忽略掉日志中的前n行命令。-v 将行事件(数据变更)重构为SQL语句-vv 将行事件(数据变更)重构为SQL语句并输出注释信息
1.2.4 删除
对于比较繁忙的业务系统每天生成的binlog数据巨大如果长时间不清除将会占用大量磁盘空 间。可以通过以下几种方式清理日志 也可以在mysql的配置文件中配置二进制日志的过期时间设置了之后二进制日志过期会自动删除。
show variables like %binlog_expire_logs_seconds%;
1.3 查询日志
查询日志中记录了客户端的所有操作语句而二进制日志不包含查询数据的SQL语句。默认情况下查询日志是未开启的。 如果需要开启查询日志可以修改MySQL的配置文件 /etc/my.cnf 文件添加如下内容
#该选项用来开启查询日志 可选值 0 或者 1 0 代表关闭 1 代表开启
general_log1#设置日志的文件名 如果没有指定 默认的文件名为 host_name.log
general_log_filemysql_query.log
开启了查询日志之后在MySQL的数据存放目录也就是 /var/lib/mysql/ 目录下就会出现 mysql_query.log 文件。之后所有的客户端的增删改查操作都会记录在该日志文件之中长时间运 行后该日志文件将会非常大。
1.4 慢查询日志
慢查询日志记录了所有执行时间超过参数 long_query_time 设置值并且扫描记录数不小于 min_examined_row_limit 的所有的SQL语句的日志默认未开启。long_query_time 默认为 10 秒最小为 0 精度可以到微秒。
如果需要开启慢查询日志需要在MySQL的配置文件 /etc/my.cnf 中配置如下参数
#慢查询日志
slow_query_log1#执行时间参数
long_query_time2
默认情况下不会记录管理语句也不会记录不使用索引进行查找的查询。可以使用 log_slow_admin_statements 和 更改此行为 log_queries_not_using_indexes如下所 述。
#记录执行较慢的管理语句
log_slow_admin_statements 1#记录执行较慢的未使用索引的语句
log_queries_not_using_indexes 1 上述所有的参数配置完成之后都需要重新启动MySQL服务器才可以生效。 2. 主从复制
2.1 概述
主从复制是指将主数据库的 DDL 和 DML 操作通过二进制日志传到从库服务器中然后在从库上对这些日志重新执行也叫重做从而使得从库和主库的数据保持同步。
MySQL支持一台主库同时向多台从库进行复制 从库同时也可以作为其他从服务器的主库实现链状复制。 MySQL 复制的优点主要包含以下三个方面
主库出现问题可以快速切换到从库提供服务。 实现读写分离降低主库的访问压力。可以在从库中执行备份以避免备份期间影响主库服务。
2.2 原理
MySQL主从复制的核心就是二进制日志具体的过程如下 从上图来看复制分成三步
Master 主库在事务提交时会把数据变更记录在二进制日志文件 Binlog 中。 从库读取主库的二进制日志文件 Binlog 写入到从库的中继日志 Relay Log 。slave重做中继日志中的事件将改变反映它自己的数据。
2.3 搭建
2.3.1 准备 准备好两台服务器之后在上述的两台服务器中分别安装好MySQL并完成基础的初始化准备(安装、密码配置等操作)工作。 其中
192.168.200.200 作为主服务器master192.168.200.201 作为从服务器slave
2.3.2 主库配置
1. 修改配置文件 /etc/my.cnf
#mysql 服务ID保证整个集群环境中唯一取值范围1 – 232-1默认为1
server-id1#是否只读,1 代表只读, 0 代表读写
read-only0#忽略的数据, 指不需要同步的数据库
#binlog-ignore-dbmysql#指定同步的数据库
#binlog-do-dbdb01
2. 重启MySQL服务器
systemctl restart mysqld
3. 登录mysql创建远程连接的账号并授予主从复制权限
#创建itcast用户并设置密码该用户可在任意主机连接该MySQL服务
CREATE USER itcast% IDENTIFIED WITH mysql_native_password BY Root123456;#为 itcast% 用户分配主从复制权限
GRANT REPLICATION SLAVE ON *.* TO itcast%;
4. 通过指令查看二进制日志坐标
show master status; 字段含义说明
file : 从哪个日志文件开始推送日志文件
position 从哪个位置开始推送日志
binlog_ignore_db : 指定不需要同步的数据库
2.3.3 从库配置
1. 修改配置文件 /etc/my.cnf
#mysql 服务ID保证整个集群环境中唯一取值范围1 – 2^32-1和主库不一样即可
server-id2#是否只读,1 代表只读, 0 代表读写如果是管理员权限的还是可以写如果使管理员权限的也不可以写可以配置super-read-only1
read-only1
2. 重新启动MySQL服务
systemctl restart mysqld
3. 登录mysql设置主库配置
CHANGE REPLICATION SOURCE TO SOURCE_HOST192.168.200.200, SOURCE_USERitcast,
SOURCE_PASSWORDRoot123456, SOURCE_LOG_FILEbinlog.000004,
SOURCE_LOG_POS663;
上述是8.0.23中的语法。如果mysql是 8.0.23 之前的版本执行如下SQL
CHANGE MASTER TO MASTER_HOST192.168.200.200, MASTER_USERitcast,
MASTER_PASSWORDRoot123456, MASTER_LOG_FILEbinlog.000004,
MASTER_LOG_POS663; 4. 开启同步操作
#8.0.22之后
start replica; #8.0.22之前
start slave; 5. 查看主从同步状态
#8.0.22之后
show replica status ; #8.0.22之前
show slave status ; 2.3.4 测试
1. 在主库 192.168.200.200 上创建数据库、表并插入数据
create database db01;use db01;create table tb_user(id int(11) primary key not null auto_increment,name varchar(50) not null,sex varchar(1)
) engineinnodb default charsetutf8mb4;insert into tb_user (id, name, sex) values (null, Tom, 1), (null, Trigger, 0), (null, Dawn, 1);
2. 在从库 192.168.200.201 中查询数据验证主从是否同步
3. 分库分表
3.1 介绍
3.1.1 问题分析 随着互联网及移动互联网的发展应用系统的数据量也是成指数式增长若采用单数据库进行数据存储存在以下性能瓶颈
IO瓶颈热点数据太多数据库缓存不足产生大量磁盘IO效率较低。 请求数据太多带宽不够网络IO瓶颈。CPU瓶颈排序、分组、连接查询、聚合统计等SQL会耗费大量的CPU资源请求数太多CPU出现瓶颈。
为了解决上述问题需要对数据库进行分库分表处理。 分库分表的中心思想都是将数据分散存储使得单一数据库/表的数据量变小来缓解单一数据库的性能问题从而达到提升数据库性能的目的。
3.1.2 拆分策略
分库分表的形式主要是两种垂直拆分和水平拆分。而拆分的粒度一般又分为分库和分表所以组成的拆分策略最终如下 3.1.3 垂直拆分
1. 垂直分库 垂直分库以表为依据根据业务将不同表拆分到不同库中。
特点
每个库的表结构都不一样。 每个库的数据也不一样。所有库的并集是全量数据。
2. 垂直分表 垂直分表以字段为依据根据字段属性将不同字段拆分到不同表中。
特点
每个表的结构都不一样。每个表的数据也不一样一般通过一列主键/外键关联。所有表的并集是全量数据。
3.1.4 水平拆分
1. 水平分库 水平分库以字段为依据按照一定策略将一个库的数据拆分到多个库中。
特点
每个库的表结构都一样。每个库的数据都不一样。所有库的并集是全量数据。
2. 水平分表 水平分表以字段为依据按照一定策略将一个表的数据拆分到多个表中。
特点
每个表的表结构都一样。每个表的数据都不一样。所有表的并集是全量数据。 在业务系统中为了缓解磁盘IO及CPU的性能瓶颈到底是垂直拆分还是水平拆分具体是分库还是分表都需要根据具体的业务需求具体分析。 3.1.5 实现技术
shardingJDBC基于AOP原理在应用程序中对本地执行的SQL进行拦截解析、改写、路由处理。需要自行编码配置实现只支持java语言性能较高。MyCat数据库分库分表中间件不用调整代码即可实现分库分表支持多种语言性能不及前者。 本次选择了是MyCat数据库中间件通过MyCat中间件来完成分库分表操作。
3.2 MyCat概述
3.2.1 介绍
Mycat是开源的、活跃的、基于Java语言编写的MySQL数据库中间件。可以像使用mysql一样来使用mycat对于开发人员来说根本感觉不到mycat的存在。
开发人员只需要连接MyCat即可而具体底层用到几台数据库每一台数据库服务器里面存储了什么数据都无需关心。 具体的分库分表的策略只需要在MyCat中配置即可。 优势
性能可靠稳定强大的技术团队体系完善社区活跃
3.2.2 下载
下载地址 http://dl.mycat.org.cn/ 3.2.3 安装
Mycat是采用java语言开发的开源的数据库中间件支持Windows和Linux运行环境下面介绍 MyCat的Linux中的环境搭建。需要在准备好的服务器中安装如下软件。
MySQLJDKMycat 3.2.3.1MySQL安装
MySQL的安装前面已经介绍过。
3.2.3.2JDK安装
JDK具体安装步骤如下
1.上传安装包
使用FinalShell自带的上传工具将JDK的二进制发布包上传到Linux。
由于上述在进行文件上传时选择的上传目录为根目录 /上传完毕后我们执行指令 cd / 切换到根目录下查看上传的安装包。 2.解压安装包
执行如下指令将上传上来的压缩包进行解压并通过-C参数指定解压文件存放目录为 /usr/local。
tar -zxvf jdk-8u171-linux-x64.tar.gz -C /usr/local
3.配置环境变量
使用vim命令修改/etc/profile文件在文件末尾加入如下配置
JAVA_HOME/usr/local/jdk1.8.0_171
PATH$PATH:$JAVA_HOME/bin
具体操作指令如下 1).编辑/etc/profile文件进入命令模式 vim /etc/profile 2).在命令模式中输入指令 G切换到文件最后 G 3).在命令模式中输入 i/a/o 进入插入模式然后切换到文件左后一行 i 4).将上述的配置拷贝到文件中 JAVA_HOME/usr/local/jdk1.8.0_171
PATH$PATH:$JAVA_HOME/bin 5).从插入模式切换到指令模式 Esc键退出 ESC 4.重新加载profile文件
source /etc/profile 5.检查安装是否成功 3.2.3.3MyCat安装
1.上传MyCat压缩包到服务器 2.解压MyCat的压缩包
tar -zxvf Mycat-server-1.6.7.3-release-20210913163959-linux.tar.gz -C /usr/local/ 3.2.4 目录介绍 bin : 存放可执行文件用于启动停止mycat
conf存放mycat的配置文件
lib存放mycat的项目依赖包jar
logs存放mycat的日志文件
cd 进入 lib目录下可以看到MyCat依赖jar包如果使用的MySQL8可以删除其中mysql旧版的上传新版的驱动即可。 这时新上传的MySQL驱动文件的颜色是红色的这是由于权限问题 对驱动文件进行授权
chmod 777 mysql-connector-java-8.0.22.jar 3.2.5 概念介绍
在MyCat的整体结构中分为两个部分上面的逻辑结构、下面的物理结构。 在MyCat的逻辑结构主要负责逻辑库、逻辑表、分片规则、分片节点等逻辑结构的处理而具体的数据存储还是在物理结构也就是数据库服务器中存储的。
在后面讲解MyCat入门以及MyCat分片时还会讲到上面所提到的概念。
3.3 MyCat入门
3.3.1 需求
由于 tb_order 表中数据量很大磁盘IO及容量都到达了瓶颈现在需要对 tb_order 表进行数 据分片分为三个数据节点每一个节点主机位于不同的服务器上, 具体的结构参考下图 3.3.2 环境准备
准备3台服务器
192.168.200.210MyCat中间件服务器同时也是第一个分片服务器。192.168.200.213第二个分片服务器。192.168.200.214第三个分片服务器。 并且在上述3台数据库中创建数据库 db01 。
3.3.3 配置
1). schema.xml
在schema.xml中配置逻辑库、逻辑表、数据节点、节点主机等相关信息。具体的配置如下
?xml version1.0?
!DOCTYPE mycat:schema SYSTEM schema.dtd
mycat:schema xmlns:mycathttp://io.mycat/schema nameDB01 checkSQLschematrue sqlMaxLimit100table nameTB_ORDER dataNodedn1,dn2,dn3 ruleauto-sharding-long//schemadataNode namedn1 dataHostdhost1 databasedb01 /dataNode namedn2 dataHostdhost2 databasedb01 /dataNode namedn3 dataHostdhost3 databasedb01 /dataHost namedhost1 maxCon1000 minCon10 balance0 writeType0 dbTypemysql dbDriverjdbc switchType1 slaveThreshold100heartbeatselect user()/heartbeatwriteHost hostmaster urljdbc:mysql://192.168.200.210:3306?useSSLfalseamp;serverTimezoneAsia/Shanghaiamp;characterEncodingutf8 userroot password1234 //dataHostdataHost namedhost2 maxCon1000 minCon10 balance0 writeType0 dbTypemysql dbDriverjdbc switchType1 slaveThreshold100heartbeatselect user()/heartbeatwriteHost hostmaster urljdbc:mysql://192.168.200.213:3306?useSSLfalseamp;serverTimezoneAsia/Shanghaiamp;characterEncodingutf8 userroot password1234 //dataHostdataHost namedhost3 maxCon1000 minCon10 balance0 writeType0 dbTypemysql dbDriverjdbc switchType1 slaveThreshold100heartbeatselect user()/heartbeatwriteHost hostmaster urljdbc:mysql://192.168.200.214:3306?useSSLfalseamp;serverTimezoneAsia/Shanghaiamp;characterEncodingutf8 userroot password1234 //dataHost
/mycat:schema
2). server.xml
需要在server.xml中配置用户名、密码以及用户的访问权限信息具体的配置如下
user nameroot defaultAccounttrueproperty namepassword123456/propertyproperty nameschemasDB01/property!-- 表级 DML 权限设置 --!--privileges checktrueschema nameDB01 dml0110 table nameTB_ORDER dml1110/table/schema/privileges--
/useruser nameuserproperty namepassword123456/propertyproperty nameschemasDB01/propertyproperty namereadOnlytrue/property
/user
上述的配置表示定义了两个用户 root 和 user 这两个用户都可以访问 DB01 这个逻辑库访 问密码都是123456但是root用户访问DB01逻辑库既可以读又可以写但是 user用户访问 DB01逻辑库是只读的。
3.3.4 测试
3.3.4.1 启动
配置完毕后先启动涉及到的3台分片服务器然后启动MyCat服务器。切换到Mycat的安装目录执行如下指令启动Mycat
#启动
bin/mycat start#停止
bin/mycat stop
Mycat启动之后占用端口号 8066。
启动完毕之后可以查看logs目录下的启动日志查看Mycat是否启动完成。 3.3.4.2 测试
1). 连接MyCat
通过如下指令就可以连接并登陆MyCat。
mysql -h 192.168.200.210 -P 8066 -uroot -p123456
可以看到是通过MySQL的指令来连接的MyCat因为MyCat在底层实际上是模拟了MySQL的协议。
2). 数据测试
然后就可以在MyCat中来创建表并往表结构中插入数据查看数据在MySQL中的分布情况。
CREATE TABLE TB_ORDER (id BIGINT(20) NOT NULL,title VARCHAR(100) NOT NULL ,PRIMARY KEY (id)
) ENGINEINNODB DEFAULT CHARSETutf8;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 (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 决定的而这个参数配置的就是分片规则关于分片规则的配置在后面的会详细讲解。
3.4 MyCat配置
3.4.1 schema.xml
schema.xml 作为MyCat中最重要的配置文件之一 , 涵盖了MyCat的逻辑库 、 逻辑表 、 分片规 则、分片节点及数据源的配置。 主要包含以下三组标签
schema标签datanode标签datahost标签
3.4.1.1 schema标签
1). schema 定义逻辑库 schema 标签用于定义 MyCat实例中的逻辑库 , 一个MyCat实例中, 可以有多个逻辑库 , 可以通 过 schema 标签来划分不同的逻辑库。MyCat中的逻辑库的概念等同于MySQL中的database概念, 需要操作某个逻辑库下的表时, 也需要切换逻辑库(use xxx)。
核心属性
name指定自定义的逻辑库库名checkSQLschema在SQL语句操作时指定了数据库名称执行时是否自动去除true自动去除false不自动去除sqlMaxLimit如果未指定limit进行查询列表查询模式查询多少条记录
2). schema 中的table定义逻辑表 table 标签定义了MyCat中逻辑库schema下的逻辑表 , 所有需要拆分的表都需要在table标签中定 义 。
核心属性
name定义逻辑表表名在该逻辑库下唯一 dataNode定义逻辑表所属的dataNode该属性需要与dataNode标签中name对应多个 dataNode逗号分隔rule分片规则的名字分片规则名字是在rule.xml中定义的primaryKey逻辑表对应真实表的主键type逻辑表的类型目前逻辑表只有全局表和普通表如果未配置就是普通表全局表配置为 global
3.4.1.2 datanode标签 核心属性
name定义数据节点名称 dataHost数据库实例主机名称引用自 dataHost 标签中name属性database定义分片所属数据库
3.4.1.3 datahost标签 该标签在MyCat逻辑库中作为底层标签存在, 直接定义了具体的数据库实例、读写分离、心跳语句。
核心属性
name唯一标识供上层标签使用 maxCon/minCon最大连接数/最小连接数balance负载均衡策略取值 0,1,2,3writeType写操作分发方式0写操作转发到第一个writeHost第一个挂了切换到第二 个1写操作随机分发到配置的writeHostdbDriver数据库驱动支持 native、jdbc
3.4.2 rule.xml
rule.xml中定义所有拆分表的规则, 在使用过程中可以灵活的使用分片算法, 或者对同一个分片算法 使用不同的参数, 它让分片过程可配置化。主要包含两类标签tableRule、Function。 3.4.3 server.xml
server.xml配置文件包含了MyCat的系统配置信息主要有两个重要的标签system、user。
1). system标签 主要配置MyCat中的系统配置信息对应的系统配置项及其含义如下 2). user标签
配置MyCat中的用户、访问密码以及用户针对于逻辑库、逻辑表的权限信息具体的权限描述方式及配置说明如下 在测试权限操作时只需要将 privileges 标签的注释放开。 在 privileges 下的schema 标签中配置的dml属性配置的是逻辑库的权限。 在privileges的schema下的table标签的dml属性 中配置逻辑表的权限。
3.5 MyCat分片
3.5.1 垂直拆分
3.5.1.1 场景
在业务系统中, 涉及以下表结构 ,但是由于用户与订单每天都会产生大量的数据, 单台服务器的数据 存储及处理能力是有限的, 可以对数据库表进行拆分, 原有的数据库表如下。 现在考虑将其进行垂直分库操作将商品相关的表拆分到一个数据库服务器订单表拆分的一个数据库服务器用户及省市区表拆分到一个服务器。最终结构如下 3.5.1.2 准备
准备三台服务器IP地址如图所示 并且在192.168.200.210192.168.200.213, 192.168.200.214上面创建数据库shopping。
3.5.1.3 配置
1). schema.xml
schema nameSHOPPING checkSQLschematrue sqlMaxLimit100table nametb_goods_base dataNodedn1 primaryKeyid /table nametb_goods_brand dataNodedn1 primaryKeyid /table nametb_goods_cat dataNodedn1 primaryKeyid /table nametb_goods_desc dataNodedn1 primaryKeygoods_id /table nametb_goods_item dataNodedn1 primaryKeyid /table nametb_order_item dataNodedn2 primaryKeyid /table nametb_order_master dataNodedn2 primaryKeyorder_id /table nametb_order_pay_log dataNodedn2 primaryKeyout_trade_no /table nametb_user dataNodedn3 primaryKeyid /table nametb_user_address dataNodedn3 primaryKeyid /table nametb_areas_provinces dataNodedn3 primaryKeyid/table nametb_areas_city dataNodedn3 primaryKeyid/table nametb_areas_region dataNodedn3 primaryKeyid/
/schemadataNode namedn1 dataHostdhost1 databaseshopping /
dataNode namedn2 dataHostdhost2 databaseshopping /
dataNode namedn3 dataHostdhost3 databaseshopping /dataHost namedhost1 maxCon1000 minCon10 balance0 writeType0 dbTypemysql dbDriverjdbc switchType1 slaveThreshold100heartbeatselect user()/heartbeatwriteHost hostmaster urljdbc:mysql://192.168.200.210:3306?useSSLfalseamp;serverTimezoneAsia/Shanghaiamp;characterEncodingutf8 userroot password1234 /
/dataHostdataHost namedhost2 maxCon1000 minCon10 balance0 writeType0 dbTypemysql dbDriverjdbc switchType1 slaveThreshold100heartbeatselect user()/heartbeatwriteHost hostmaster urljdbc:mysql://192.168.200.213:3306?useSSLfalseamp;serverTimezoneAsia/Shanghaiamp;characterEncodingutf8 userroot password1234 /
/dataHostdataHost namedhost3 maxCon1000 minCon10 balance0 writeType0 dbTypemysql dbDriverjdbc switchType1 slaveThreshold100heartbeatselect user()/heartbeatwriteHost hostmaster urljdbc:mysql://192.168.200.214:3306?useSSLfalseamp;serverTimezoneAsia/Shanghaiamp;characterEncodingutf8 userroot password1234 /
/dataHost
2). server.xml
user nameroot defaultAccounttrueproperty namepassword123456/propertyproperty nameschemasSHOPPING/property!-- 表级 DML 权限设置 --!--privileges checktrueschema nameDB01 dml0110 table nameTB_ORDER dml1110/table/schema/privileges--
/useruser nameuserproperty namepassword123456/propertyproperty nameschemasSHOPPING/propertyproperty namereadOnlytrue/property
/user
3.5.1.4 测试
1). 上传测试SQL脚本到服务器的/root目录 2). 执行指令导入测试数据
重新启动MyCat后在mycat的命令行中通过source指令导入表结构以及对应的数据查看数据分布情况。
source /root/shopping-table.sqlsource /root/shopping-insert.sql
将表结构及对应的测试数据导入之后可以检查一下各个数据库服务器中的表结构分布情况。 检查是否和准备工作中规划的服务器一致。 3). 查询用户的收件人及收件人地址信息(包含省、市、区)。
在MyCat的命令行中当执行以下多表联查的SQL语句时可以正常查询出数据。
select ua.user_id, ua.contact, p.province, c.city, r.area, ua.address from
tb_user_address ua, tb_areas_city c, tb_areas_provinces p, tb_areas_region r
where ua.province_id p.provinceid and ua.city_id c.cityid and ua.town_id
r.areaid; 4). 查询每一笔订单及订单的收件地址信息(包含省、市、区)。
实现该需求对应的SQL语句如下
SELECT order_id, payment, receiver, province, city, area FROM tb_order_master o, tb_areas_provinces p, tb_areas_city c, tb_areas_region r WHERE
o.receiver_province p.provinceid AND o.receiver_city c.cityid AND
o.receiver_region r.areaid;
但是现在存在一个问题订单相关的表结构是在 192.168.200.213 数据库服务器中而省市区的数据库表是在 192.168.200.214 数据库服务器中。那么在MyCat中执行是否可以成功呢 经过测试可以看到SQL语句执行报错。原因就是因为MyCat在执行该SQL语句时需要往具体的数据库服务器中路由而当前没有一个数据库服务器完全包含了订单以及省市区的表结构造成SQL语句失败报错。
对于上述的这种现象如何来解决呢 下面介绍的全局表就可以轻松解决这个问题。
3.5.1.5 全局表
对于省、市、区/县表tb_areas_provinces , tb_areas_city , tb_areas_region是属于 数据字典表在多个业务模块中都可能会遇到可以将其设置为全局表利于业务操作。
修改schema.xml中的逻辑表的配置修改 tb_areas_provinces、tb_areas_city、 tb_areas_region 三个逻辑表增加 type 属性配置为global就代表该表是全局表就会在 所涉及到的dataNode中创建给表。对于当前配置来说也就意味着所有的节点中都有该表了。
table nametb_areas_provinces dataNodedn1,dn2,dn3 primaryKeyid typeglobal/
table nametb_areas_city dataNodedn1,dn2,dn3 primaryKeyid typeglobal/
table nametb_areas_region dataNodedn1,dn2,dn3 primaryKeyid typeglobal/ 配置完毕后重新启动MyCat。
1). 删除原来每一个数据库服务器中的所有表结构
2). 通过source指令导入表及数据
source /root/shopping-table.sqlsource /root/shopping-insert.sql
3). 检查每一个数据库服务器中的表及数据分布看到三个节点中都有这三张全局表
4). 然后再次执行上面的多表联查的SQL语句
SELECT order_id, payment, receiver, province, city, area FROM tb_order_master o, tb_areas_provinces p, tb_areas_city c, tb_areas_region r WHERE
o.receiver_province p.provinceid AND o.receiver_city c.cityid AND
o.receiver_region r.areaid; 是可以正常执行成功的。
5). 当在MyCat中更新全局表的时候可以看到所有分片节点中的数据都发生了变化每个节点的全局表数据时刻保持一致。
3.5.2 水平拆分
3.5.2.1 场景
在业务系统中, 有一张表(日志表), 业务系统每天都会产生大量的日志数据 , 单台服务器的数据存 储及处理能力是有限的, 可以对数据库表进行拆分。 3.5.2.2 准备
准备三台服务器具体的结构如下 并且在三台数据库服务器中分表创建一个数据库itcast。
3.5.2.3 配置
1). schema.xml
schema nameITCAST checkSQLschematrue sqlMaxLimit100table nametb_log dataNodedn4,dn5,dn6 primaryKeyid rulemod-long /
/schemadataNode namedn4 dataHostdhost1 databaseitcast /
dataNode namedn5 dataHostdhost2 databaseitcast /
dataNode namedn6 dataHostdhost3 databaseitcast /
tb_log表最终落在3个节点中分别是 dn4、dn5、dn6 而具体的数据分别存储在 dhost1、 dhost2、dhost3的itcast数据库中。
2). server.xml
配置root用户既可以访问 SHOPPING 逻辑库又可以访问ITCAST逻辑库。
user nameroot defaultAccounttrueproperty namepassword123456/propertyproperty nameschemasSHOPPING,ITCAST/property!-- 表级 DML 权限设置 --!--privileges checktrueschema nameDB01 dml0110 table nameTB_ORDER dml1110/table/schema/privileges--
/user
3.5.2.4 测试
配置完毕后重新启动MyCat然后在mycat的命令行中执行如下SQL创建表、并插入数据查看数据分布情况。
CREATE TABLE tb_log (id bigint(20) NOT NULL COMMENT ID,model_name varchar(200) DEFAULT NULL COMMENT 模块名,model_value varchar(200) DEFAULT NULL COMMENT 模块值,return_value varchar(200) DEFAULT NULL COMMENT 返回值,return_class varchar(200) DEFAULT NULL COMMENT 返回值类型,operate_user varchar(20) DEFAULT NULL COMMENT 操作用户,operate_time varchar(20) DEFAULT NULL COMMENT 操作时间,param_and_value varchar(500) DEFAULT NULL COMMENT 请求参数名及参数值,operate_class varchar(200) DEFAULT NULL COMMENT 操作类,operate_method varchar(200) DEFAULT NULL COMMENT 操作方法,cost_time bigint(20) DEFAULT NULL COMMENT 执行方法耗时, 单位 ms,source int(1) DEFAULT NULL COMMENT 来源 : 1 PC, 2 Android, 3 IOS,PRIMARY KEY (id)
) ENGINEInnoDB DEFAULT CHARSETutf8mb4;INSERT INTO tb_log (id, model_name, model_value, return_value, return_class,
operate_user, operate_time, param_and_value, operate_class, operate_method,
cost_timesource)
VALUES(1, user, insert, success, java.lang.String, 10001, 2022-01-06
18:12:28, {\age\:\20\,\name\:\Tom\,\gender\:\1\}, cn.itcast.contro
ller.UserController, insert, 10, 1);
INSERT INTO tb_log (id, model_name, model_value, return_value, return_class,
operate_user, operate_time, param_and_value, operate_class, operate_method,
cost_timesource)
VALUES(2, user, insert, success, java.lang.String, 10001, 2022-01-06
18:12:27, {\age\:\20\,\name\:\Tom\,\gender\:\1\}, cn.itcast.contro
ller.UserController, insert, 23, 1);
INSERT INTO tb_log (id, model_name, model_value, return_value, return_class,
operate_user, operate_time, param_and_value, operate_class, operate_method,
cost_timesource)
VALUES(3, user, update, success, java.lang.String, 10001, 2022-01-06
18:16:45, {\age\:\20\,\name\:\Tom\,\gender\:\1\}, cn.itcast.contro
ller.UserController, update, 34, 1);
INSERT INTO tb_log (id, model_name, model_value, return_value, return_class,
operate_user, operate_time, param_and_value, operate_class, operate_method,
cost_timesource)
VALUES(4, user, update, success, java.lang.String, 10001, 2022-01-06
18:16:45, {\age\:\20\,\name\:\Tom\,\gender\:\1\}, cn.itcast.contro
ller.UserController, update, 13, 2);
INSERT INTO tb_log (id, model_name, model_value, return_value, return_class,
operate_user, operate_time, param_and_value, operate_class, operate_method,
cost_timesource)
VALUES(5, user, insert, success, java.lang.String, 10001, 2022-01-06
18:30:31, {\age\:\200\,\name\:\TomCat\,\gender\:\0\}, cn.itcast.co
ntroller.UserController, insert, 29, 3);
INSERT INTO tb_log (id, model_name, model_value, return_value, return_class,
operate_user, operate_time, param_and_value, operate_class, operate_method,
cost_timesource)
VALUES(6, user, find, success, java.lang.String, 10001, 2022-01-06
18:30:31, {\age\:\200\,\name\:\TomCat\,\gender\:\0\}, cn.itcast.co
ntroller.UserController, find, 29, 2);
3.5.3 分片规则
3.5.3.1 范围分片
1). 介绍
根据指定的字段及其配置的范围与数据节点的对应情况 来决定该数据属于哪一个分片。 2). 配置
schema.xml逻辑表配置
table nameTB_ORDER dataNodedn1,dn2,dn3 ruleauto-sharding-long /
schema.xml数据节点配置
dataNode namedn1 dataHostdhost1 databasedb01 /
dataNode namedn2 dataHostdhost2 databasedb01 /
dataNode namedn3 dataHostdhost3 databasedb01 /
rule.xml分片规则配置
tableRule nameauto-sharding-longrulecolumnsid/columnsalgorithmrang-long/algorithm/rule
/tableRulefunction namerang-long classio.mycat.route.function.AutoPartitionByLongproperty namemapFileautopartition-long.txt/propertyproperty namedefaultNode0/property
/function
分片规则配置属性含义 在rule.xml中配置分片规则时关联了一个映射配置文件 autopartition-long.txt该配置文 件的配置如下 含义0-500万之间的值存储在0号数据节点(数据节点的索引从0开始) 500万-1000万之间的 数据存储在1号数据节点 1000万-1500万的数据节点存储在2号节点
该分片规则主要是针对于数字类型的字段适用。 在MyCat的入门程序中使用的就是该分片规则。
3.5.3.2 取模分片
1). 介绍
根据指定的字段值与节点数量进行求模运算根据运算结果 来决定该数据属于哪一个分片。 2). 配置
schema.xml逻辑表配置
table nametb_log dataNodedn4,dn5,dn6 primaryKeyid rulemod-long /
schema.xml数据节点配置
dataNode namedn4 dataHostdhost1 databaseitcast /
dataNode namedn5 dataHostdhost2 databaseitcast /
dataNode namedn6 dataHostdhost3 databaseitcast /
rule.xml分片规则配置
tableRule namemod-longrulecolumnsid/columnsalgorithmmod-long/algorithm/rule
/tableRulefunction namemod-long classio.mycat.route.function.PartitionByModproperty namecount3/property
/function
分片规则属性说明如下 该分片规则主要是针对于数字类型的字段适用。 在前面水平拆分的演示中选择的就是取模分片。
3). 测试
配置完毕后重新启动MyCat然后在mycat的命令行中执行如下SQL创建表、并插入数据查看数据分布情况。
3.5.3.3 一致性hash分片
1). 介绍
所谓一致性哈希相同的哈希因子计算值总是被划分到相同的分区表中不会因为分区节点的增加而改变原来数据的分区位置有效的解决了分布式数据的拓容问题。 2). 配置
schema.xml中逻辑表配置
!-- 一致性hash --
table nametb_order dataNodedn4,dn5,dn6 rulesharding-by-murmur / schema.xml中数据节点配置
dataNode namedn4 dataHostdhost1 databaseitcast /
dataNode namedn5 dataHostdhost2 databaseitcast /
dataNode namedn6 dataHostdhost3 databaseitcast /
rule.xml中分片规则配置
tableRule namesharding-by-murmurrulecolumnsid/columnsalgorithmmurmur/algorithm/rule
/tableRulefunction namemurmur classio.mycat.route.function.PartitionByMurmurHash!-- 默认是0 --property nameseed0/propertyproperty namecount3/propertyproperty namevirtualBucketTimes160/property
/function
分片规则属性含义 3). 测试
配置完毕后重新启动MyCat然后在mycat的命令行中执行如下SQL创建表、并插入数据查看数据分布情况。
create table tb_order(id varchar(100) not null primary key,money int null,content varchar(200) null
);INSERT INTO tb_order (id, money, content) VALUES (b92fdaaf-6fc4-11ec-b831-
482ae33c4a2d, 10, b92fdaf8-6fc4-11ec-b831-482ae33c4a2d);
INSERT INTO tb_order (id, money, content) VALUES (b93482b6-6fc4-11ec-b831-
482ae33c4a2d, 20, b93482d5-6fc4-11ec-b831-482ae33c4a2d);
INSERT INTO tb_order (id, money, content) VALUES (b937e246-6fc4-11ec-b831-
482ae33c4a2d, 50, b937e25d-6fc4-11ec-b831-482ae33c4a2d);
INSERT INTO tb_order (id, money, content) VALUES (b93be2dd-6fc4-11ec-b831-
482ae33c4a2d, 100, b93be2f9-6fc4-11ec-b831-482ae33c4a2d);
INSERT INTO tb_order (id, money, content) VALUES (b93f2d68-6fc4-11ec-b831-
482ae33c4a2d, 130, b93f2d7d-6fc4-11ec-b831-482ae33c4a2d);
INSERT INTO tb_order (id, money, content) VALUES (b9451b98-6fc4-11ec-b831-
482ae33c4a2d, 30, b9451bcc-6fc4-11ec-b831-482ae33c4a2d);
INSERT INTO tb_order (id, money, content) VALUES (b9488ec1-6fc4-11ec-b831-
482ae33c4a2d, 560, b9488edb-6fc4-11ec-b831-482ae33c4a2d);
INSERT INTO tb_order (id, money, content) VALUES (b94be6e6-6fc4-11ec-b831-
482ae33c4a2d, 10, b94be6ff-6fc4-11ec-b831-482ae33c4a2d);
INSERT INTO tb_order (id, money, content) VALUES (b94ee10d-6fc4-11ec-b831-
482ae33c4a2d, 123, b94ee12c-6fc4-11ec-b831-482ae33c4a2d);
INSERT INTO tb_order (id, money, content) VALUES (b952492a-6fc4-11ec-b831-
482ae33c4a2d, 145, b9524945-6fc4-11ec-b831-482ae33c4a2d);
INSERT INTO tb_order (id, money, content) VALUES (b95553ac-6fc4-11ec-b831-
482ae33c4a2d, 543, b95553c8-6fc4-11ec-b831-482ae33c4a2d);
INSERT INTO tb_order (id, money, content) VALUES (b9581cdd-6fc4-11ec-b831-
482ae33c4a2d, 17, b9581cfa-6fc4-11ec-b831-482ae33c4a2d);
INSERT INTO tb_order (id, money, content) VALUES (b95afc0f-6fc4-11ec-b831-
482ae33c4a2d, 18, b95afc2a-6fc4-11ec-b831-482ae33c4a2d);
INSERT INTO tb_order (id, money, content) VALUES (b95daa99-6fc4-11ec-b831-
482ae33c4a2d, 134, b95daab2-6fc4-11ec-b831-482ae33c4a2d);
INSERT INTO tb_order (id, money, content) VALUES (b9667e3c-6fc4-11ec-b831-
482ae33c4a2d, 156, b9667e60-6fc4-11ec-b831-482ae33c4a2d);
INSERT INTO tb_order (id, money, content) VALUES (b96ab489-6fc4-11ec-b831-
482ae33c4a2d, 175, b96ab4a5-6fc4-11ec-b831-482ae33c4a2d);
INSERT INTO tb_order (id, money, content) VALUES (b96e2942-6fc4-11ec-b831-
482ae33c4a2d, 180, b96e295b-6fc4-11ec-b831-482ae33c4a2d);
INSERT INTO tb_order (id, money, content) VALUES (b97092ec-6fc4-11ec-b831-
482ae33c4a2d, 123, b9709306-6fc4-11ec-b831-482ae33c4a2d);
INSERT INTO tb_order (id, money, content) VALUES (b973727a-6fc4-11ec-b831-
482ae33c4a2d, 230, b9737293-6fc4-11ec-b831-482ae33c4a2d);
INSERT INTO tb_order (id, money, content) VALUES (b978840f-6fc4-11ec-b831-
482ae33c4a2d, 560, b978843c-6fc4-11ec-b831-482ae33c4a2d);3.5.3.4 枚举分片
1). 介绍
通过在配置文件中配置可能的枚举值, 指定数据分布到不同数据节点上, 本规则适用于按照省份、性 别、状态拆分数据等业务 。 2). 配置
schema.xml中逻辑表配置
!-- 枚举 --
table nametb_user dataNodedn4,dn5,dn6 rulesharding-by-intfile-enumstatus /
schema.xml中数据节点配置
dataNode namedn4 dataHostdhost1 databaseitcast /
dataNode namedn5 dataHostdhost2 databaseitcast /
dataNode namedn6 dataHostdhost3 databaseitcast /
rule.xml中分片规则配置
tableRule namesharding-by-intfilerulecolumnssharding_id/columnsalgorithmhash-int/algorithm/rule
/tableRule!-- 自己增加 tableRule --
tableRule namesharding-by-intfile-enumstatusrulecolumnsstatus/columnsalgorithmhash-int/algorithm/rule
/tableRulefunction namehash-int classio.mycat.route.function.PartitionByFileMapproperty namedefaultNode2/propertyproperty namemapFilepartition-hash-int.txt/property
/function
partition-hash-int.txt 内容如下 : 分片规则属性含义 3). 测试
配置完毕后重新启动MyCat然后在mycat的命令行中执行如下SQL创建表、并插入数据查看数据分布情况。
CREATE TABLE tb_user (id bigint(20) NOT NULL COMMENT ID,username varchar(200) DEFAULT NULL COMMENT 姓名,status int(2) DEFAULT 1 COMMENT 1: 未启用, 2: 已启用, 3: 已关闭,PRIMARY KEY (id)
) ENGINEInnoDB DEFAULT CHARSETutf8mb4;insert into tb_user (id, username, status) values (1, Tom, 1);
insert into tb_user (id, username, status) values (2, Cat, 2);
insert into tb_user (id, username, status) values (3, Rose, 3);
insert into tb_user (id, username, status) values (4, Coco, 2);
insert into tb_user (id, username, status) values (5, Lily, 1);
insert into tb_user (id, username, status) values (6, Tom, 1);
insert into tb_user (id, username, status) values (7, Cat, 2);
insert into tb_user (id, username, status) values (8, Rose, 3);
insert into tb_user (id, username, status) values (9, Coco, 2);
insert into tb_user (id, username, status) values (10, Lily, 1); 3.5.3.5 应用指定算法
1). 介绍
运行阶段由应用自主决定路由到那个分片 , 直接根据字符子串必须是数字计算分片号。 2). 配置
schema.xml中逻辑表配置
!-- 应用指定算法 --
table nametb_app dataNodedn4,dn5,dn6 rulesharding-by-substring /
schema.xml中数据节点配置
dataNode namedn4 dataHostdhost1 databaseitcast /
dataNode namedn5 dataHostdhost2 databaseitcast /
dataNode namedn6 dataHostdhost3 databaseitcast /
rule.xml中分片规则配置
tableRule namesharding-by-substringrulecolumnsid/columnsalgorithmsharding-by-substring/algorithm/rule
/tableRulefunction namesharding-by-substring classio.mycat.route.function.PartitionDirectBySubString!-- zero-based --property namestartIndex0/property property namesize2/propertyproperty namepartitionCount3/propertyproperty namedefaultPartition0/property
/function
分片规则属性含义 示例说明 :
id05-100000002 , 在此配置中代表根据id中从 startIndex0开始截取size2位数字即 0505就是获取的分区如果没找到对应的分片则默认分配到defaultPartition 。
3). 测试
配置完毕后重新启动MyCat然后在mycat的命令行中执行如下SQL创建表、并插入数据查看数据分布情况。
CREATE TABLE tb_app (id varchar(10) NOT NULL COMMENT ID,name varchar(200) DEFAULT NULL COMMENT 名称,PRIMARY KEY (id)
) ENGINEInnoDB DEFAULT CHARSETutf8mb4;insert into tb_app (id, name) values(0000001, Testx00001);
insert into tb_app (id, name) values(0100001, Test100001);
insert into tb_app (id, name) values(0100002, Test200001);
insert into tb_app (id, name) values(0200001, Test300001);
insert into tb_app (id, name) values(0200002, TesT400001);
3.5.3.6 固定分片hash算法
1). 介绍
该算法类似于十进制的求模运算但是为二进制的操作例如取 id 的二进制低 10 位 与 1111111111 进行位 运算位与运算最小值为 0000000000最大值为1111111111转换为十 进制也就是位于0-1023之间。 特点
如果是求模连续的值分别分配到各个不同的分片但是此算法会将连续的值可能分配到相同的分片降低事务处理的难度。 可以均匀分配也可以非均匀分配。分片字段必须为数字类型。
2). 配置
schema.xml中逻辑表配置
!-- 固定分片hash算法 --
table nametb_longhash dataNodedn4,dn5,dn6 rulesharding-by-long-hash /
schema.xml中数据节点配置
dataNode namedn4 dataHostdhost1 databaseitcast /
dataNode namedn5 dataHostdhost2 databaseitcast /
dataNode namedn6 dataHostdhost3 databaseitcast /
rule.xml中分片规则配置
tableRule namesharding-by-long-hashrulecolumnsid/columnsalgorithmsharding-by-long-hash/algorithm/rule
/tableRule!-- 分片总长度为1024count与length数组长度必须一致 --
function namesharding-by-long-hash classio.mycat.route.function.PartitionByLongproperty namepartitionCount2,1/propertyproperty namepartitionLength256,512/property
/function
分片规则属性含义 约束 :
1). 分片长度 : 默认最大2^10 , 为 1024 ;
2). count, length的数组长度必须是一致的 ;
以上分为三个分区:0-255,256-511,512-1023
示例说明 : 3). 测试
配置完毕后重新启动MyCat然后在mycat的命令行中执行如下SQL创建表、并插入数据查看数据分布情况。
CREATE TABLE tb_longhash (id int(11) NOT NULL COMMENT ID,name varchar(200) DEFAULT NULL COMMENT 名称,firstChar char(1) COMMENT 首字母,PRIMARY KEY (id)
) ENGINEInnoDB DEFAULT CHARSETutf8mb4;insert into tb_longhash (id, name, firstChar) values (1, 七匹狼, Q);
insert into tb_longhash (id, name, firstChar) values (2, 八匹狼, B);
insert into tb_longhash (id, name, firstChar) values (3, 九匹狼, J);
insert into tb_longhash (id, name, firstChar) values (4, 十匹狼, S);
insert into tb_longhash (id, name, firstChar) values (5, 六匹狼, L);
insert into tb_longhash (id, name, firstChar) values (6, 五匹狼, W);
insert into tb_longhash (id, name, firstChar) values (7, 四匹狼, S);
insert into tb_longhash (id, name, firstChar) values (8, 三匹狼, S);
insert into tb_longhash (id, name, firstChar) values (9, 两匹狼, L); 3.5.3.7 字符串hash解析算法
1). 介绍
截取字符串中的指定位置的子字符串, 进行hash算法 算出分片。 2). 配置
schema.xml中逻辑表配置
!-- 字符串hash解析算法 --
table nametb_strhash dataNodedn4,dn5 rulesharding-by-stringhash / schema.xml中数据节点配置
dataNode namedn4 dataHostdhost1 databaseitcast /
dataNode namedn5 dataHostdhost2 databaseitcast /
rule.xml中分片规则配置
tableRule namesharding-by-stringhashrulecolumnsname/columnsalgorithmsharding-by-stringhash/algorithm/rule
/tableRulefunction namesharding-by-stringhash classio.mycat.route.function.PartitionByString!-- zero-based --property namepartitionLength512/propertyproperty namepartitionCount2/propertyproperty namehashSlice0:2/property
/function
分片规则属性含义 示例说明 3). 测试
配置完毕后重新启动MyCat然后在mycat的命令行中执行如下SQL创建表、并插入数据查看数据分布情况。
create table tb_strhash(name varchar(20) primary key,content varchar(100)
) engineInnoDB DEFAULT CHARSETutf8mb4;INSERT INTO tb_strhash (name, content) VALUES (T1001, UUID());
INSERT INTO tb_strhash (name, content) VALUES (ROSE, UUID());
INSERT INTO tb_strhash (name, content) VALUES (JERRY, UUID());
INSERT INTO tb_strhash (name, content) VALUES (CRISTINA, UUID());
INSERT INTO tb_strhash (name, content) VALUES (TOMCAT, UUID());
3.5.3.8 按天分片算法
1). 介绍
按照日期及对应的时间周期来分片。 2). 配置
schema.xml中逻辑表配置
!-- 按天分片 --
table nametb_datepart dataNodedn4,dn5,dn6 rulesharding-by-date / schema.xml中数据节点配置
dataNode namedn4 dataHostdhost1 databaseitcast /
dataNode namedn5 dataHostdhost2 databaseitcast /
dataNode namedn6 dataHostdhost3 databaseitcast /
rule.xml中分片规则配置
tableRule namesharding-by-daterulecolumnscreate_time/columnsalgorithmsharding-by-date/algorithm/rule
/tableRule!--从开始时间开始每10天为一个分片到达结束时间之后会重复开始分片插入配置表的 dataNode 的分片必须和分片规则数量一致例如 2022-01-01 到 2022-12-31 每10天一个分片一共需要37个分片。
--
function namesharding-by-date classio.mycat.route.function.PartitionByDateproperty namedateFormatyyyy-MM-dd/propertyproperty namesBeginDate2022-01-01/propertyproperty namesEndDate2022-01-30/propertyproperty namesPartionDay10/property
/function
分片规则属性含义 3). 测试
配置完毕后重新启动MyCat然后在mycat的命令行中执行如下SQL创建表、并插入数据查看数据分布情况。
create table tb_datepart(id bigint not null comment ID primary key,name varchar(100) null comment 姓名,create_time date null
);insert into tb_datepart(id, name, create_time) values(1, Tom, 2022-01-01);
insert into tb_datepart(id, name, create_time) values(2, Cat, 2022-01-10);
insert into tb_datepart(id, name, create_time) values(3, Rose, 2022-01-11);
insert into tb_datepart(id, name, create_time) values(4, Coco, 2022-01-20);
insert into tb_datepart(id, name, create_time) values(5, Rose2, 2022-01-21);
insert into tb_datepart(id, name, create_time) values(6, Coco2, 2022-01-30);
insert into tb_datepart(id, name, create_time) values(7, Coco3, 2022-01-31);
3.5.3.9 自然月分片
1). 介绍
使用场景为按照月份来分片, 每个自然月为一个分片。 2). 配置
schema.xml中逻辑表配置
!-- 按自然月分片 --
table nametb_monthpart dataNodedn4,dn5,dn6 rulesharding-by-month / schema.xml中数据节点配置
dataNode namedn4 dataHostdhost1 databaseitcast /
dataNode namedn5 dataHostdhost2 databaseitcast /
dataNode namedn6 dataHostdhost3 databaseitcast /
rule.xml中分片规则配置
tableRule namesharding-by-monthrulecolumnscreate_time/columnsalgorithmpartbymonth/algorithm/rule
/tableRule!--从开始时间开始一个月为一个分片到达结束时间之后会重复开始分片插入配置表的 dataNode 的分片必须和分片规则数量一致例如 2022-01-01 到 2022-12-31 一共需要12个分片。
--
function namepartbymonth classio.mycat.route.function.PartitionByMonthproperty namedateFormatyyyy-MM-dd/propertyproperty namesBeginDate2022-01-01/propertyproperty namesEndDate2022-03-31/property
/function分片规则属性含义 3). 测试
配置完毕后重新启动MyCat然后在mycat的命令行中执行如下SQL创建表、并插入数据查看数据分布情况。
create table tb_monthpart(id bigint not null comment ID primary key,name varchar(100) null comment 姓名,create_time date null
);insert into tb_monthpart(id, name, create_time) values (1, Tom, 2022-01-01);
insert into tb_monthpart(id, name, create_time) values (2, Cat, 2022-01-10);
insert into tb_monthpart(id, name, create_time) values (3, Rose, 2022-01-31);
insert into tb_monthpart(id, name, create_time) values (4, Coco, 2022-02-20);
insert into tb_monthpart(id, name, create_time) values (5, Rose2, 2022-02-25);
insert into tb_monthpart(id, name, create_time) values (6, Coco2, 2022-03-10);
insert into tb_monthpart(id, name, create_time) values (7, Coco3, 2022-03-31);
insert into tb_monthpart(id, name, create_time) values (8, Coco4, 2022-04-10);
insert into tb_monthpart(id, name, create_time) values (9, Coco5, 2022-04-30);
3.6 MyCat管理及监控
3.6.1 MyCat原理 在MyCat中当执行一条SQL语句时MyCat需要进行SQL解析、分片分析、路由分析、读写分离分析等操作最终经过一系列的分析决定将当前的SQL语句到底路由到那几个(或哪一个)节点数据库数据库将数据执行完毕后如果有返回的结果则将结果返回给MyCat最终还需要在MyCat中进行结果合并、聚合处理、排序处理、分页处理等操作最终再将结果返回给客户端。
而在MyCat的使用过程中MyCat官方也提供了一个管理监控平台MyCat-WebMyCat-eye。 Mycat-web 是 Mycat 可视化运维的管理和监控平台弥补了 Mycat 在监控上的空白。帮 Mycat 分担统计任务和配置管理任务。Mycat-web 引入了 ZooKeeper 作为配置中心可以管理多个节 点。Mycat-web 主要管理和监控 Mycat 的流量、连接、活动线程和内存等具备 IP 白名单、邮 件告警等模块还可以统计 SQL 并分析慢 SQL 和高频 SQL 等。为优化 SQL 提供依据。
3.6.2 MyCat管理
Mycat默认开通2个端口可以在server.xml中进行修改。
8066 数据访问端口即进行 DML 和 DDL 操作。9066 数据库管理端口即 mycat 服务管理控制功能用于管理mycat的整个集群状态。
连接MyCat的管理控制台
mysql -h 192.168.200.210 -p 9066 -uroot -p123456 3.6.3 MyCat-eye
3.6.3.1 介绍
Mycat-web(Mycat-eye)是对mycat-server提供监控服务功能不局限于对mycat-server使 用。他通过JDBC连接对Mycat、Mysql监控监控远程服务器(目前仅限于linux系统)的cpu、内 存、网络、磁盘。
Mycat-eye运行过程中需要依赖zookeeper因此需要先安装zookeeper。
3.6.3.2 安装
1). zookeeper安装
2). Mycat-web安装
3.6.3.3 访问
http://192.168.200.210:8082/mycat 3.6.3.4 配置
1). 开启MyCat的实时统计功能(server.xml)
!-- 1为开启实时统计、0为关闭 --
property nameuseSqlStat1/property
2). 在Mycat监控界面配置服务地址 3.6.3.5 测试
配置好了之后可以通过MyCat执行一系列的增删改查的测试然后过一段时间之后打开 mycat-eye的管理界面查看mycat-eye监控到的数据信息。
A. 性能监控 B. 物理节点 C. SQL统计 D. SQL表分析
E. SQL监控 F. 高频SQL 4. 读写分离
4.1 介绍
读写分离,简单地说是把对数据库的读和写操作分开,以对应不同的数据库服务器。主数据库提供写操作从数据库提供读操作这样能有效地减轻单台数据库的压力。
通过MyCat即可轻易实现上述功能不仅可以支持MySQL也可以支持Oracle和SQL Server。 4.2 一主一从
4.2.1 原理
MySQL的主从复制是基于二进制日志binlog实现的。 4.2.2 准备 备注主从复制的搭建可以参考前面 主从复制 章节讲解的步骤操作。 4.3 一主一从读写分离
MyCat控制后台数据库的读写分离和负载均衡由schema.xml文件datahost标签的balance属性控 制。
4.3.1 schema.xml配置
!-- 配置逻辑库 --
schema nameITCAST_RW checkSQLschematrue sqlMaxLimit100 dataNodedn7/schemadataNode namedn7 dataHostdhost7 databaseitcast /dataHost namedhost7 maxCon1000 minCon10 balance1 writeType0 dbTypemysql dbDriverjdbc switchType1 slaveThreshold100heartbeatselect user()/heartbeatwriteHost hostmaster1 urljdbc:mysql://192.168.200.211:3306?useSSLfalseamp;serverTimezoneAsia/Shanghaiamp;characterEncodingutf8 userroot password1234 readHost hostslave1 urljdbc:mysql://192.168.200.212:3306?useSSLfalseamp;serverTimezoneAsia/Shanghaiamp;characterEncodingutf8 userroot password1234 //writeHost
/dataHost
上述配置的具体关联对应情况如下 writeHost代表的是写操作对应的数据库readHost代表的是读操作对应的数据库。 所以要想 实现读写分离就得配置writeHost关联的是主库readHost关联的是从库。
而仅仅配置好了writeHost以及readHost还不能完成读写分离还需要配置一个非常重要的负责均衡的参数 balance取值有4种具体含义如下 所以在一主一从模式的读写分离中balance配置1或3都是可以完成读写分离的。
4.3.2 server.xml配置
配置root用户可以访问SHOPPING、ITCAST 以及 ITCAST_RW逻辑库。
user nameroot defaultAccounttrueproperty namepassword123456/propertyproperty nameschemasSHOPPING,ITCAST,ITCAST_RW/property!-- 表级 DML 权限设置 --!--privileges checktrueschema nameDB01 dml0110 table nameTB_ORDER dml1110/table/schema/privileges--
/user
4.3.3 测试
配置完毕MyCat后重新启动MyCat。
bin/mycat stopbin/mycat start
然后观察在执行增删改操作时对应的主库及从库的数据变化。 在执行查询操作时检查主库及从库对应的数据变化。
在测试中可以发现当主节点Master宕机之后业务系统就只能够读而不能写入数据了。 那如何解决这个问题呢这个时候就得通过另外一种主从复制结构来解决了也就是接下来讲解的双主双从。
4.4 双主双从
4.4.1 介绍
一个主机 Master1 用于处理所有写请求它的从机 Slave1 和另一台主机 Master2 还有它的从 机 Slave2 负责所有读请求。当 Master1 主机宕机后Master2 主机负责写请求Master1 、 Master2 互为备机。架构图如下: 4.4.2 准备
需要准备5台服务器具体的服务器及软件安装情况如下 关闭以上所有服务器的防火墙
systemctl stop firewalldsystemctl disable firewalld
4.4.3 搭建
4.4.3.1 主库配置
1). Master1(192.168.200.211) A. 修改配置文件 /etc/my.cnf
#mysql 服务ID保证整个集群环境中唯一取值范围1 – 2^32-1默认为1
server-id1#指定同步的数据库
binlog-do-dbdb01
binlog-do-dbdb02
binlog-do-dbdb03# 在作为从数据库的时候有写入操作也要更新二进制日志文件
log-slave-updates
B. 重启MySQL服务器
systemctl restart mysqld
C. 创建账户并授权
#创建itcast用户并设置密码该用户可在任意主机连接该MySQL服务
CREATE USER itcast% IDENTIFIED WITH mysql_native_password BY Root123456;#为 itcast% 用户分配主从复制权限
GRANT REPLICATION SLAVE ON *.* TO itcast%;
通过指令查看两台主库的二进制日志坐标
show master status; 2). Master2(192.168.200.213) A. 修改配置文件 /etc/my.cnf
#mysql 服务ID保证整个集群环境中唯一取值范围1 – 2^32-1默认为1
server-id3#指定同步的数据库
binlog-do-dbdb01
binlog-do-dbdb02
binlog-do-dbdb03# 在作为从数据库的时候有写入操作也要更新二进制日志文件
log-slave-updates
B. 重启MySQL服务器
systemctl restart mysqld
C. 创建账户并授权
#创建itcast用户并设置密码该用户可在任意主机连接该MySQL服务
CREATE USER itcast% IDENTIFIED WITH mysql_native_password BY Root123456;#为 itcast% 用户分配主从复制权限
GRANT REPLICATION SLAVE ON *.* TO itcast%;
通过指令查看两台主库的二进制日志坐标
show master status; 4.4.3.2 从库配置
1). Slave1(192.168.200.212) A. 修改配置文件 /etc/my.cnf
#mysql 服务ID保证整个集群环境中唯一取值范围1 – 232-1默认为1
server-id2
B. 重新启动MySQL服务器
systemctl restart mysqld
2). Slave2(192.168.200.214) A. 修改配置文件 /etc/my.cnf
#mysql 服务ID保证整个集群环境中唯一取值范围1 – 232-1默认为1
server-id4
B. 重新启动MySQL服务器
systemctl restart mysqld
4.4.3.3 从库关联主库
1). 两台从库配置关联的主库 需要注意slave1对应的是master1slave2对应的是master2。 A. 在 slave1(192.168.200.212)上执行
CHANGE MASTER TO MASTER_HOST192.168.200.211, MASTER_USERitcast,
MASTER_PASSWORDRoot123456, MASTER_LOG_FILEbinlog.000002,
MASTER_LOG_POS663;
B. 在 slave2(192.168.200.214)上执行
CHANGE MASTER TO MASTER_HOST192.168.200.213, MASTER_USERitcast,
MASTER_PASSWORDRoot123456, MASTER_LOG_FILEbinlog.000002,
MASTER_LOG_POS663;
C. 启动两台从库主从复制查看从库状态
start slave;show slave status \G; 2). 两台主库相互复制 Master2 复制 Master1Master1 复制 Master2。 A. 在 Master1(192.168.200.211)上执行
CHANGE MASTER TO MASTER_HOST192.168.200.213, MASTER_USERitcast,
MASTER_PASSWORDRoot123456, MASTER_LOG_FILEbinlog.000002,
MASTER_LOG_POS663;
B. 在 Master2(192.168.200.213)上执行
CHANGE MASTER TO MASTER_HOST192.168.200.211, MASTER_USERitcast,
MASTER_PASSWORDRoot123456, MASTER_LOG_FILEbinlog.000002,
MASTER_LOG_POS663;
C. 启动两台从库主从复制查看从库状态
start slave;show slave status \G; 经过上述的三步配置之后双主双从的复制结构就已经搭建完成了。 接下来可以来测试验证一下。
4.4.4 测试
分别在两台主库Master1、Master2上执行DDL、DML语句查看涉及到的数据库服务器的数据同步情况。
create database db01;use db01;create table tb_user(id int(11) not null primary key ,name varchar(50) not null,sex varchar(1)
) engineinnodb default charsetutf8mb4;insert into tb_user (id, name, sex) values (1, Tom, 1);
insert into tb_user (id, name, sex) values (2, Trigger, 0);
insert into tb_user (id, name, sex) values (3, Dawn, 1);
insert into tb_user (id, name, sex) values (4, Jack Ma, 1);
insert into tb_user (id, name, sex) values (5, Coco, 0);
insert into tb_user (id, name, sex) values (6, Jerry, 1);
在Master1中执行DML、DDL操作看看数据是否可以同步到另外的三台数据库中。在Master2中执行DML、DDL操作看看数据是否可以同步到另外的三台数据库中。
完成了上述双主双从的结构搭建之后接下来再来看看如何完成这种双主双从的读写分离。
4.5 双主双从读写分离
4.5.1 配置
MyCat控制后台数据库的读写分离和负载均衡由schema.xml文件datahost标签的balance属性控 制通过writeType及switchType来完成失败自动切换的。
1). schema.xml
配置逻辑库
schema nameITCAST_RW2 checkSQLschematrue sqlMaxLimit100 dataNodedn7/schema
配置数据节点
dataNode namedn7 dataHostdhost7 databasedb01 /
配置节点主机
dataHost namedhost7 maxCon1000 minCon10 balance1 writeType0 dbTypemysql dbDriverjdbc switchType1 slaveThreshold100heartbeatselect user()/heartbeatwriteHost hostmaster1 urljdbc:mysql://192.168.200.211:3306?useSSLfalseamp;serverTimezoneAsia/Shanghaiamp;characterEncodingutf8 userroot password1234 readHost hostslave1 urljdbc:mysql://192.168.200.212:3306?useSSLfalseamp;serverTimezoneAsia/Shanghaiamp;characterEncodingutf8 userroot password1234 //writeHostwriteHost hostmaster2 urljdbc:mysql://192.168.200.213:3306?useSSLfalseamp;serverTimezoneAsia/Shanghaiamp;characterEncodingutf8 userroot password1234 readHost hostslave2 urljdbc:mysql://192.168.200.214:3306?useSSLfalseamp;serverTimezoneAsia/Shanghaiamp;characterEncodingutf8 userroot password1234 //writeHost
/dataHost
具体的对应情况如下 属性说明 balance1 代表全部的 readHost 与 stand by writeHost 参与 select 语句的负载均衡简单的说当双主双从模式(M1-S1M2-S2并且 M1 与 M2 互为主备)正常情况下M2,S1,S2 都参与 select 语句的负载均衡 ; writeType 0 : 写操作都转发到第1台writeHost, writeHost1挂了, 会切换到writeHost2上; 1 : 所有的写操作都随机地发送到配置的writeHost上 ; switchType -1 : 不自动切换 1 : 自动切换 2). user.xml
配置root用户也可以访问到逻辑库 ITCAST_RW2。
user nameroot defaultAccounttrueproperty namepassword123456/propertyproperty nameschemasSHOPPING,ITCAST,ITCAST_RW2/property!-- 表级 DML 权限设置 --!--privileges checktrueschema nameDB01 dml0110 table nameTB_ORDER dml1110/table/schema/privileges--
/user
4.5.2 测试
登录MyCat测试查询及更新操作判定是否能够进行读写分离以及读写分离的策略是否正确。
当主库挂掉一个之后是否能够自动切换。
