北京高端网站建设服务,免费ftp服务器空间,软件开发,网站的超级链接怎么做目录
1、日志
1、错误日志
2、二进制日志 3、查询日志
4、慢查询日志
2、主从复制 搭建
1、主库配置
2、从库配置
3、分库分表
1、简介
编辑 1、垂直拆分
2、水平拆分
3、实现技术
2、MyCat
3、MyCat使用和配置
配置
4、MyCat分片
1、垂直拆分
2、水平拆分…目录
1、日志
1、错误日志
2、二进制日志 3、查询日志
4、慢查询日志
2、主从复制 搭建
1、主库配置
2、从库配置
3、分库分表
1、简介
编辑 1、垂直拆分
2、水平拆分
3、实现技术
2、MyCat
3、MyCat使用和配置
配置
4、MyCat分片
1、垂直拆分
2、水平拆分
3、分片规则
5、MyCat管理及监控 MyCat-eye 4、读写分离 1、一主一从 2、一主一从读写分离
3、双主双从
4、双主双从读写分离 1、日志
1、错误日志
错误日志是 MySQL 中最重要的日志之一它记录了当 mysqld 启动和停止时以及服务器在运行过程中发生任何严重错误时的相关信息。当数据库出现任何故障导致无法正常使用时建议首先查看此日志。
该日志是默认开启的默认存放目录 /var/log/默认的日志文件名为 mysqld.log 。查看日志位置
show variables like %log_error%;
2、二进制日志
二进制日志BINLOG记录了所有的 DDL数据定义语言语句和 DML数据操纵语言语句但不包括数据查询SELECT、SHOW语句。
作用①. 灾难时的数据恢复②. MySQL的主从复制。在MySQL8版本中默认二进制日志是开启着的涉及到的参数如下
show variables like %log_bin%; 参数说明
log_bin_basename当前数据库服务器的binlog日志的基础名称(前缀)具体的binlog文件名需要再该basename的基础上加上编号(编号从000001开始)。log_bin_indexbinlog的索引文件里面记录了当前服务器关联的binlog文件有哪些。
MySQL服务器中提供了多种格式来记录二进制日志具体格式及特点如下 show variables like %binlog_format%; 如果我们需要配置二进制日志的格式只需要在 /etc/my.cnf 中配置 binlog_format 参数即可。 由于日志是以二进制方式存储的不能直接读取需要通过二进制日志查询工具 mysqlbinlog 来查看具体语法 mysqlbinlog [ 参数选项 ] logfilename 参数选项 -d 指定数据库名称只列出指定的数据库相关操作。 -o 忽略掉日志中的前n行命令。 -v 将行事件(数据变更)重构为SQL语句 -vv 将行事件(数据变更)重构为SQL语句并输出注释信息 对于比较繁忙的业务系统每天生成的binlog数据巨大如果长时间不清除将会占用大量磁盘空间。可以通过以下几种方式清理日志 也可以在mysql的配置文件中配置二进制日志的过期时间设置了之后二进制日志过期会自动删除。
show variables like %binlog_expire_logs_seconds%; 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 文件。之后所有的客户端的增删改查操作都会记录在该日志文件之中长时间运行后该日志文件将会非常大。
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、主从复制
主从复制是指将主数据库的 DDL 和 DML 操作通过二进制日志传到从库服务器中然后在从库上对这些日志重新执行也叫重做从而使得从库和主库的数据保持同步。
MySQL支持一台主库同时向多台从库进行复制 从库同时也可以作为其他从服务器的主库实现链状复制。 MySQL 复制的优点主要包含以下三个方面
主库出现问题可以快速切换到从库提供服务。实现读写分离降低主库的访问压力。可以在从库中执行备份以避免备份期间影响主库服务。
MySQL主从复制的核心就是 二进制日志具体的过程如下 搭建 准备好两台服务器之后在上述的两台服务器中分别安装好MySQL并完成基础的初始化准备(安装、密码配置等操作)工作。 其中
192.168.200.200 作为主服务器master192.168.200.201 作为从服务器slave
1、主库配置
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、从库配置
1、修改配置文件 /etc/my.cnf #mysql 服务ID保证整个集群环境中唯一取值范围1 – 2^32-1和主库不一样即可 server-id2 #是否只读,1 代表只读, 0 代表读写 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、开启同步操作 start replica ; #8.0.22之后 start slave ; #8.0.22之前 5、查看主从同步状态 show replica status ; #8.0.22之后 show slave status ; #8.0.22之前 3、分库分表
1、简介 随着互联网及移动互联网的发展应用系统的数据量也是成指数式增长若采用单数据库进行数据存储存在以下性能瓶颈 IO瓶颈热点数据太多数据库缓存不足产生大量磁盘IO效率较低。 请求数据太多带宽不够网络IO瓶颈。 CPU瓶颈排序、分组、连接查询、聚合统计等SQL会耗费大量的CPU资源请求数太多CPU出现瓶颈。
为了解决上述问题我们需要对数据库进行分库分表处理。 分库分表的中心思想都是将数据分散存储使得单一数据库/表的数据量变小来缓解单一数据库的性能问题从而达到提升数据库性能的目的。
分库分表的形式主要是两种垂直拆分和水平拆分。而拆分的粒度一般又分为分库和分表所以组成的拆分策略最终如下 1、垂直拆分
1、垂直分库 垂直分库以表为依据根据业务将不同表拆分到不同库中。
特点
每个库的表结构都不一样。每个库的数据也不一样。所有库的并集是全量数据。
2、垂直分表 垂直分表以字段为依据根据字段属性将不同字段拆分到不同表中。 特点
每个表的结构都不一样。每个表的数据也不一样一般通过一列主键/外键关联。所有表的并集是全量数据。
2、水平拆分
1、水平分库 水平分库以字段为依据按照一定策略将一个库的数据拆分到多个库中。
特点
每个库的表结构都一样。每个库的数据都不一样。所有库的并集是全量数据。
2、水平分表 水平分表以字段为依据按照一定策略将一个表的数据拆分到多个表中。
特点
每个表的表结构都一样。每个表的数据都不一样。所有表的并集是全量数据。
在业务系统中为了缓解磁盘IO及CPU的性能瓶颈到底是垂直拆分还是水平拆分具体是分库还是分表都需要根据具体的业务需求具体分析。
3、实现技术
shardingJDBC基于AOP原理在应用程序中对本地执行的SQL进行拦截解析、改写、路由处理。需要自行编码配置实现只支持java语言性能较高。MyCat数据库分库分表中间件不用调整代码即可实现分库分表支持多种语言性能不及前者。2、MyCat
Mycat是开源的、活跃的、基于Java语言编写的MySQL数据库中间件。可以像使用mysql一样来使用mycat对于开发人员来说根本感觉不到mycat的存在。
开发人员只需要连接MyCat即可而具体底层用到几台数据库每一台数据库服务器里面存储了什么数据都无需关心。 具体的分库分表的策略只需要在MyCat中配置即可。 优势
性能可靠稳定强大的技术团队体系完善社区活跃
下载地址http://dl.mycat.org.cn/
在MyCat的整体结构中分为两个部分上面的逻辑结构、下面的物理结构。 在MyCat的逻辑结构主要负责逻辑库、逻辑表、分片规则、分片节点等逻辑结构的处理而具体的数据存储还是在物理结构也就是数据库服务器中存储的。
3、MyCat使用和配置
由于 tb_order 表中数据量很大磁盘IO及容量都到达了瓶颈现在需要对 tb_order 表进行数据分片分为三个数据节点每一个节点主机位于不同的服务器上, 具体的结构参考下图 准备3台服务器
192.168.200.210MyCat中间件服务器同时也是第一个分片服务器。192.168.200.213第二个分片服务器。192.168.200.214第三个分片服务器。并且在上述3台数据库中创建数据库 db01 。
1、schema.xml
在schema.xml中配置逻辑库、逻辑表、数据节点、节点主机等相关信息。具体的配置如下
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台分片服务器然后启动MyCat服务器。切换到Mycat的安装目录执行如下指令启动Mycat
#启动
bin/mycat start#停止
bin/mycat stop
Mycat启动之后占用端口号 8066。
启动完毕之后可以查看logs目录下的启动日志查看Mycat是否启动完成。
通过如下指令就可以连接并登陆MyCat。
mysql -h 192.168.200.210 -P 8066 -uroot -p123456
通过MySQL的指令来连接的MyCat因为MyCat在底层实际上是模拟了MySQL的协议。
然后就可以在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 决定的而这个参数配置的就是分片规则
配置
1、schema.xml
schema.xml 作为MyCat中最重要的配置文件之一 , 涵盖了MyCat的逻辑库 、 逻辑表 、 分片规则、分片节点及数据源的配置。 主要包含以下三组标签
schema标签datanode标签datahost标签
1schema标签
schema 定义逻辑库 schema 标签用于定义 MyCat实例中的逻辑库 , 一个MyCat实例中, 可以有多个逻辑库 , 可以通过 schema 标签来划分不同的逻辑库。MyCat中的逻辑库的概念等同于MySQL中的database概念, 需要操作某个逻辑库下的表时, 也需要切换逻辑库(use xxx)。
核心属性
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
2datanode标签 核心属性
name定义数据节点名称dataHost数据库实例主机名称引用自 dataHost 标签中name属性database定义分片所属数据库
3datahost标签
该标签在MyCat逻辑库中作为底层标签存在, 直接定义了具体的数据库实例、读写分离、心跳语句。
核心属性
name唯一标识供上层标签使用maxCon/minCon最大连接数/最小连接数balance负载均衡策略取值 0,1,2,3writeType写操作分发方式0写操作转发到第一个writeHost第一个挂了切换到第二个1写操作随机分发到配置的writeHostdbDriver数据库驱动支持 native、jdbc
2、rule.xml
rule.xml中定义所有拆分表的规则, 在使用过程中可以灵活的使用分片算法, 或者对同一个分片算法使用不同的参数, 它让分片过程可配置化。主要包含两类标签tableRule、Function。 3、server.xml
server.xml配置文件包含了MyCat的系统配置信息主要有两个重要的标签system、user。 主要配置MyCat中的系统配置信息对应的系统配置项及其含义如下 user标签
配置MyCat中的用户、访问密码以及用户针对于逻辑库、逻辑表的权限信息具体的权限描述方式及配置说明如下 在测试权限操作时我们只需要将 privileges 标签的注释放开。 在 privileges 下的schema标签中配置的dml属性配置的是逻辑库的权限。 在privileges的schema下的table标签的dml属性中配置逻辑表的权限。
4、MyCat分片
1、垂直拆分
在业务系统中, 涉及以下表结构 ,但是由于用户与订单每天都会产生大量的数据, 单台服务器的数据存储及处理能力是有限的, 可以对数据库表进行拆分, 原有的数据库表如下。 现在考虑将其进行垂直分库操作将商品相关的表拆分到一个数据库服务器订单表拆分的一个数据库服务器用户及省市区表拆分到一个服务器。最终结构如上图
准备三台服务器IP地址如图所示 并且在192.168.200.210192.168.200.213, 192.168.200.214上面创建数据库shopping。 1schema.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 balance0writeType0 dbTypemysql dbDriverjdbc switchType1 slaveThreshold100heartbeatselect user()/heartbeatwriteHost hostmaster urljdbc:mysql://192.168.200.213:3306?useSSLfalseamp;serverTimezoneAsia/Shanghaiamp;characterEncodingutf8 userroot password1234 /
/dataHostdataHost namedhost3 maxCon1000 minCon10 balance0writeType0 dbTypemysql dbDriverjdbc switchType1 slaveThreshold100heartbeatselect user()/heartbeatwriteHost hostmaster urljdbc:mysql://192.168.200.214:3306?useSSLfalseamp;serverTimezoneAsia/Shanghaiamp;characterEncodingutf8 userroot password1234 /
/dataHost
2server.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
重新启动MyCat后在mycat的命令行中通过source指令导入表结构以及对应的数据查看数据分布情况。
source /root/shopping-table.sql
source /root/shopping-insert.sql 查询用户的收件人及收件人地址信息(包含省、市、区)。
在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 ;
查询每一笔订单及订单的收件地址信息(包含省、市、区)。
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 数据库服务器中。
SQL语句执行报错。原因就是因为MyCat在执行该SQL语句时需要往具体的数据库服务器中路由而当前没有一个数据库服务器完全包含了订单以及省市区的表结构造成SQL语句失败报错。
全局表
对于省、市、区/县表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删除原来每一个数据库服务器中的所有表结构重新导入表结构和数据之前执行失败的sql能执行成功
当在MyCat中更新全局表的时候我们可以看到所有分片节点中的数据都发生了变化每个节点的全局表数据时刻保持一致。
2、水平拆分
在业务系统中, 有一张表(日志表), 业务系统每天都会产生大量的日志数据 , 单台服务器的数据存储及处理能力是有限的, 可以对数据库表进行拆分。 在三台数据库服务器中分表创建一个数据库itcast。 1schema.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数据库中。
2server.xml
user nameroot defaultAccounttrueproperty namepassword123456/propertyproperty nameschemasSHOPPING,ITCAST/property!-- 表级 DML 权限设置 --!--privileges checktrueschema nameDB01 dml0110 table nameTB_ORDER dml1110/table/schema/privileges--
/user
配置root用户既可以访问 SHOPPING 逻辑库又可以访问ITCAST逻辑库。
3、分片规则
范围分片
根据指定的字段及其配置的范围与数据节点的对应情况 来决定该数据属于哪一个分片。 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该配置文件的配置如下
# range start-end ,data node index
# K1000,M10000.0-500M0
500M-1000M1
1000M-1500M2 含义0-500万之间的值存储在0号数据节点(数据节点的索引从0开始) 500万-1000万之间的数据存储在1号数据节点 1000万-1500万的数据节点存储在2号节点
该分片规则主要是针对于数字类型的字段适用。 在MyCat的入门程序中我们使用的就是该分片规则。
取模分片
根据指定的字段值与节点数量进行求模运算根据运算结果 来决定该数据属于哪一个分片。 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
分片规则属性说明如下 该分片规则主要是针对于数字类型的字段适用。 一致性hash分片
所谓一致性哈希相同的哈希因子计算值总是被划分到相同的分区表中不会因为分区节点的增加而改变原来数据的分区位置有效的解决了分布式数据的拓容问题。 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.PartitionByMurmurHashproperty nameseed0/property!-- 默认是0 --property namecount3/propertyproperty namevirtualBucketTimes160/property
/function
分片规则属性含义 枚举分片
通过在配置文件中配置可能的枚举值, 指定数据分布到不同数据节点上, 本规则适用于按照省份、性别、状态拆分数据等业务 。 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 内容如下 :
10
21
32
分片规则属性含义 应用指定算法
运行阶段由应用自主决定路由到那个分片 , 直接根据字符子串必须是数字计算分片号。 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.PartitionDirectBySubStringproperty namestartIndex0/property !-- zero-based --property namesize2/propertyproperty namepartitionCount3/propertyproperty namedefaultPartition0/property
/function
分片规则属性含义 示例id05-100000002 , 在此配置中代表根据id中从 startIndex0开始截取siz2位数字即0505就是获取的分区如果没找到对应的分片则默认分配到defaultPartition 。
固定分片hash算法
该算法类似于十进制的求模运算但是为二进制的操作例如取 id 的二进制低 10 位 与1111111111 进行位 运算位与运算最小值为 0000000000最大值为1111111111转换为十进制也就是位于0-1023之间。 特点
如果是求模连续的值分别分配到各个不同的分片但是此算法会将连续的值可能分配到相同的分片降低事务处理的难度。可以均匀分配也可以非均匀分配。分片字段必须为数字类型。
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
分片规则属性含义 约束 :
分片长度 : 默认最大2^10 , 为 1024 ;count, length的数组长度必须是一致的 ;以上分为三个分区:0-255,256-511,512-1023字符串hash解析算法
截取字符串中的指定位置的子字符串, 进行hash算法 算出分片。 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.PartitionByStringproperty namepartitionLength512/property !-- zero-based --property namepartitionCount2/propertyproperty namehashSlice0:2/property
/function 分片规则属性含义 按天分片算法
按照日期及对应的时间周期来分片。 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
/tableRulefunction namesharding-by-date classio.mycat.route.function.PartitionByDateproperty namedateFormatyyyy-MM-dd/propertyproperty namesBeginDate2022-01-01/propertyproperty namesEndDate2022-01-30/propertyproperty namesPartionDay10/property
/function!--从开始时间开始每10天为一个分片到达结束时间之后会重复开始分片插入配置表的 dataNode 的分片必须和分片规则数量一致例如 2022-01-01 到 2022-12-31 每10天一个分片一共需要37个分片。
-- 自然月分片
使用场景为按照月份来分片, 每个自然月为一个分片。 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
/tableRulefunction namepartbymonth classio.mycat.route.function.PartitionByMonthproperty namedateFormatyyyy-MM-dd/propertyproperty namesBeginDate2022-01-01/propertyproperty namesEndDate2022-03-31/property
/function!--从开始时间开始一个月为一个分片到达结束时间之后会重复开始分片插入配置表的 dataNode 的分片必须和分片规则数量一致例如 2022-01-01 到 2022-12-31 一共需要12个分片。
--
分片规则属性含义 5、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 提供依据。
Mycat默认开通2个端口可以在server.xml中进行修改。
8066 数据访问端口即进行 DML 和 DDL 操作。9066 数据库管理端口即 mycat 服务管理控制功能用于管理mycat的整个集群状态连接MyCat的管理控制台
mysql -h 192.168.200.210 -p 9066 -uroot -p123456 MyCat-eye
Mycat-web(Mycat-eye)是对mycat-server提供监控服务功能不局限于对mycat-server使用。他通过JDBC连接对Mycat、Mysql监控监控远程服务器(目前仅限于linux系统)的cpu、内存、网络、磁盘。
Mycat-eye运行过程中需要依赖zookeeper因此需要先安装zookeeper。
访问地址http://192.168.200.210:8082/mycat 开启MyCat的实时统计功能(server.xml)
property nameuseSqlStat1/property !-- 1为开启实时统计、0为关闭 --
在Mycat监控界面配置服务地址 4、读写分离
读写分离,简单地说是把对数据库的读和写操作分开,以对应不同的数据库服务器。主数据库提供写操作从数据库提供读操作这样能有效地减轻单台数据库的压力。
通过MyCat即可轻易实现上述功能不仅可以支持MySQL也可以支持Oracle和SQL Server。 1、一主一从
MySQL的主从复制是基于二进制日志binlog实现的。 2、一主一从读写分离
MyCat控制后台数据库的读写分离和负载均衡由schema.xml文件datahost标签的balance属性控制。
1schema.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都是可以完成读写分离的。
2server.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
配置完毕MyCat后重新启动MyCat。当主节点Master宕机之后业务系统就只能够读而不能写入数据了。
3、双主双从
一个主机 Master1 用于处理所有写请求它的从机 Slave1 和另一台主机 Master2 还有它的从机 Slave2 负责所有读请求。当 Master1 主机宕机后Master2 主机负责写请求Master1 、Master2 互为备机。架构图如下: 1主库配置
Master1(192.168.200.211) 修改配置文件 /etc/my.cnf
#mysql 服务ID保证整个集群环境中唯一取值范围1 – 2^32-1默认为1
server-id1#指定同步的数据库
binlog-do-dbdb01
binlog-do-dbdb02
binlog-do-dbdb03# 在作为从数据库的时候有写入操作也要更新二进制日志文件
log-slave-updates
重启MySQL服务器
systemctl restart mysqld
创建账户并授权
#创建itcast用户并设置密码该用户可在任意主机连接该MySQL服务
CREATE USER itcast% IDENTIFIED WITH mysql_native_password BY Root123456;#为 itcast% 用户分配主从复制权限
GRANT REPLICATION SLAVE ON *.* TO itcast%;
通过指令查看两台主库的二进制日志坐标
show master status ;
Master2(192.168.200.213) 修改配置文件 /etc/my.cnf
#mysql 服务ID保证整个集群环境中唯一取值范围1 – 2^32-1默认为1
server-id3#指定同步的数据库
binlog-do-dbdb01
binlog-do-dbdb02
binlog-do-dbdb03# 在作为从数据库的时候有写入操作也要更新二进制日志文件
log-slave-updates
重启MySQL服务器
systemctl restart mysqld
创建账户并授权
#创建itcast用户并设置密码该用户可在任意主机连接该MySQL服务
CREATE USER itcast% IDENTIFIED WITH mysql_native_password BY Root123456;#为 itcast% 用户分配主从复制权限
GRANT REPLICATION SLAVE ON *.* TO itcast%;
通过指令查看两台主库的二进制日志坐标
show master status ;
2从库配置
Slave1(192.168.200.212)
修改配置文件 /etc/my.cnf
#mysql 服务ID保证整个集群环境中唯一取值范围1 – 232-1默认为1
server-id2
重启MySQL服务器
systemctl restart mysqld
Slave2(192.168.200.214)
修改配置文件 /etc/my.cnf
#mysql 服务ID保证整个集群环境中唯一取值范围1 – 232-1默认为1
server-id4
重启MySQL服务器
systemctl restart mysqld
3从库关联主库
两台从库配置关联的主库 需要注意slave1对应的是master1slave2对应的是master2。
在 slave1(192.168.200.212)上执行
CHANGE MASTER TO MASTER_HOST192.168.200.211, MASTER_USERitcast,
MASTER_PASSWORDRoot123456, MASTER_LOG_FILEbinlog.000002,
MASTER_LOG_POS663; 在 slave2(192.168.200.214)上执行
CHANGE MASTER TO MASTER_HOST192.168.200.213, MASTER_USERitcast,
MASTER_PASSWORDRoot123456, MASTER_LOG_FILEbinlog.000002,
MASTER_LOG_POS663;
启动两台从库主从复制查看从库状态
start slave;
show slave status \G;
两台主库相互复制 Master2 复制 Master1Master1 复制 Master2。
在 Master1(192.168.200.211)上执行
CHANGE MASTER TO MASTER_HOST192.168.200.213, MASTER_USERitcast,
MASTER_PASSWORDRoot123456, MASTER_LOG_FILEbinlog.000002,
MASTER_LOG_POS663;
在 Master2(192.168.200.213)上执行
CHANGE MASTER TO MASTER_HOST192.168.200.211, MASTER_USERitcast,
MASTER_PASSWORDRoot123456, MASTER_LOG_FILEbinlog.000002,
MASTER_LOG_POS663;
启动两台从库主从复制查看从库状态
start slave;
show slave status \G;
4、双主双从读写分离
MyCat控制后台数据库的读写分离和负载均衡由schema.xml文件datahost标签的balance属性控制通过writeType及switchType来完成失败自动切换的。
1schema.xml
schema nameITCAST_RW2 checkSQLschematrue sqlMaxLimit100 dataNodedn7
/schema
dataNode namedn7 dataHostdhost7 databasedb01 /
dataHost namedhost7 maxCon1000 minCon10 balance1 writeType0dbTypemysql 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 : 自动切换 2user.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
