MyCat(看这一篇就够了）
Mycat是数据库中间件，所谓中间件数据库中间件是连接Java应用程序和数据库中间的软件。一个彻底开源的，面向企业应用开发的大数据库集群支持事务、ACID、可以替代MySQL的加强版数据库一个可以视为MySQL集群的企业级数据库，用来替代昂贵的Oracle集群一个融合内存缓存技术、NoSQL技术、HDFS大数据的新型SQL Server结合传统数据库和新型分布式数据仓库的新一代企业级数据库产品一个
目录：
Mycat
什么是Mycat？
Mycat是数据库中间件，所谓中间件数据库中间件是连接Java应用程序和数据库中间的软件。
- 一个彻底开源的，面向企业应用开发的大数据库集群
- 支持事务、ACID、可以替代MySQL的加强版数据库
- 一个可以视为MySQL集群的企业级数据库，用来替代昂贵的Oracle集群
- 一个融合内存缓存技术、NoSQL技术、HDFS大数据的新型SQL Server
- 结合传统数据库和新型分布式数据仓库的新一代企业级数据库产品
- 一个新颖的数据库中间件产品
Mycat作用为：能满足数据库数据大量存储；提高了查询性能
Mycat应用场景
高可用性与MySQL读写分离
利用Mycat可以轻松实现热备份，当一台服务器停机时，可以由双机或集群中的另一台服务器自动接管其业务，从而在无须人工干预的情况下，保证系统持续提供服务。这个切换动作由Mycat自动完成。
注意：
Mycat 的读写分离及自动切换都依赖于数据库产品的主从数据同步。
100亿大表水平分表、集群并行计算
数据切分是Mycat的核心功能，是指通过某种特定的条件，将存放在同一个数据库中的数据分散存放在多个数据库（主机）中，以达到分散单台设备负载的效果。
数据切分有两种切分模式
- 按照不同的表将数据切分到不同的数据库中，这种切分可以叫作数据的垂直切分。
- 根据表中数据的逻辑关系，将同一个表中的数据按照某种条件拆分到多个数据库中，这种切分叫作数据的水平切分。当数据量超过800万行且需要做分片时，可以利用Mycat实现数据切分。
数据库路由器
Mycat基于MySQL 实例的连接池复用机制，可以让每个应用最大程度地共享一个MySQL实例的所有连接池，让数据库的并发访问能力大大提升。
整合多种数据源
当一个项目需要用到多种数据源如Oracle、MySQL、SQL Server、PostgreSQL时，可以利用Mycat进行整合，只需访问Mycat 这一个数据源就行。
Mycat概念
逻辑库schema
业务开发人员通常在实际应用中并不需要知道中间件的存在，只需要关注数据库，所以数据库中间件可以被当作一个或多个数据库集群构成的逻辑库。
注意：
逻辑库，与MySQL中的Database（数据库）对应，⼀个逻辑库中定义了所包括的Table。
逻辑表table
既然有逻辑库，就会有逻辑表。在分布式数据库中，对于应用来说，读写数据的表就是逻辑表。逻辑表可以分布在一个或多个分片库中，也可以不分片。
注意：
Table：表，即物理数据库中存储的某⼀张表，与传统数据库不同，这⾥的表格需要声明其所存储的逻辑数据节点DataNode。
节点主机DataNode
将数据切分后，每个分片节点不一定会独占一台机器，同一台机器上可以有多个分片数据库，这样一个或多个分片节点所在的机器就是节点主机。为了规避单节点主机并发数量的限制,尽量将读写压力高的分片节点均匀地放在不同的节点主机上。
数据库主机DataHost
数据切分后，每个分片节点(dataNode)不一定都会独占一台机器，同一机器上面可以有多个分片数据库，这样一个或多个分片节点(dataNode)所在的机器就是节点主机(dataHost),为了规避单节点主机并发数限制，尽量将读写压力高的分片节点(dataNode)均衡的放在不同的节点主机(dataHost)。
用户
MyCat的用户(类似于MySQL的用户，支持多用户）
Mycat原理
Mycat 的原理中最重要的一个动词是“拦截”，它拦截了用户发送过来的SQL 语句。
流程：
首先对 SQL 语句做了一些特定的分析：如分片分析、路由分析、读写分离 分析、缓存分析等，然后将此 SQL 发 往后端的真实数据库，并将返回的结果做适当的处理，最终再返回给用户。
流程示例
- 解析SQL语句涉及的表。
- 查看表的定义，如果表存在分片规则，则获取SQL语句的分片字段。
- 将SQL语句发送到相应的分片去执行。
- 最后处理所有分片返回的数据并返回给客户端。
部署安装
下载MySQL
- 下载YUM库
wget http://dev.mysql.com/get/mysql57-community-release-el7-10.noarch.rpm
- 安装YUM库
rpm -ivh mysql57-community-release-el7-10.noarch.rpm
- 安装MySQL服务，重启mysql
#预防MySQL GPG 密钥过期
rpm --import https://repo.mysql.com/RPM-GPG-KEY-mysql-2022
#安装mysql服务
yum -y install mysql-community-server
- 完成安装，重启mysql
systemctl restart mysqld
- 日志文件中找出密码
grep "password" /var/log/mysqld.log
- 修改密码策略
set global validate_password_policy=0;
set global validate_password_policy=LOW;
set global validate_password_length=6;
- 密码策略修改完以后可以修改密码
ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';
- 开启远程连接
use mysql;
update user set host ="%" where user = "root";
- 刷新信息
flush privileges;
搭建MySQL主从复制
克隆主节点机器
选择完整克隆
克隆虚拟机
修改MAC地址
开启bin-log日志
- 修改主服务器Master
修改配置文件：vim /etc/my.cnf
#主服务器唯一ID
server-id=1
#启用二进制日志
log-bin=mysql-bin
#设置不要复制的数据库（可设置多个）
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
#设置logbin格式
binlog_format=STATEMENT
- 修改从服务器slave
修改配置文件：vim /etc/my.cnf
#从服务器唯一ID
server-id=2
#启用中继日志
relay-log=mysql-relay
- 重启两台服务器的mysql
service mysqld restart
- 在主服务器上建立帐户并授权slave
mysql>GRANT REPLICATION SLAVE ON *.* to 'slave'@'%' identified by '123456';
注意：
一般不用root帐号，“%”表示所有客户端都可能连，只要帐号，密码正确，此处可用具体客户端IP代替，如192.168.145.226，加强安全。
查询Master的状态
mysql>show master status;
+------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------+-----------+--------------+--------------+
| mysql-bin.000004 | 308 | |
+-----------+----------+--------------+------------------+
1 row in set (0.00 sec)
注意：
执行完此步骤后不要再操作主服务器MYSQL，防止主服务器状态值变化。
配置从服务器Slave
mysql>change master to master_host='192.168.47.100',master_user='slave',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=430;
注意：
注意不要断开，308数字前后无单引号。
启动从服务器复制功能
mysql>start slave;
检查从服务器复制功能状态
show slave status \G;
注意：
Slave_IO及Slave_SQL进程必须正常运行，即YES状态，否则都是错误的状态(如：其中一个NO均属错误)。
主从服务器测试
主服务器Mysql，建立数据库，并在这个库中建表插入一条数据
mysql> create database hi_db;
Query OK, 1 row affected (0.00 sec)
mysql> use hi_db;
Database changed
mysql> create table hi_tb(id int(3),name char(10));
Query OK, 0 rows affected (0.00 sec)
mysql> insert into hi_tb values(001,'baizhan');
Query OK, 1 row affected (0.00 sec)
从服务器Mysql查询
mysql>show databases;
mysql>use hi_db
mysql>select * from hi_tb;
问题总结
1、The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.
#查看auto.cnf文件
[root@master ~] cat /var/lib/mysql/auto.cnf ### 主上的uuid
[root@slave ~] cat /var/lib/mysql/auto.cnf ### 主上的uuid
解决故障
原因是克隆了虚拟机，只改server_id不行。
#给master和slave两个节点机器执行下面命令
1、mv /var/lib/mysql/auto.cnf /var/lib/mysql/auto.cnf.bk
2、systemctl restart mysqld
2、注意防火墙的关闭
3、注意master_log_file和master_log_pos是否填写正确。
Mycat2.0安装
下载解压Mycat安装包
MyCAT 有提供编译好的安装包，支持 windows、Linux等系统上安装与运行。下载地址
# 下载install-template
wget http://dl.mycat.org.cn/2.0/install-template/mycat2-install-template-1.21.zip
# 解压
unzip mycat2-install-template-1.21.zip
# 下载依赖到指定目录
cd mycat/lib/
wget http://dl.mycat.org.cn/2.0/1.21-release/mycat2-1.21-release-jar-with-dependencies.jar
目录结构
bin：可执行命令
conf：配置文件
lib：存放jar包
logs：日志信息
tmlogs：临时日志
version.txt：版本信息
配置初始数据源
启动mycat之前需要确认prototype数据源所对应的mysql数据库配置,修改对应的user(用户),password(密码),url中的ip。
# /usr/local/mycat/conf/datasources/prototypeDs.datasource.json
#正确修改下方文件中的url、user、password
{
"dbType":"mysql",
"idleTimeout":60000,
"initSqls":[],
"initSqlsGetConnection":true,
"instanceType":"READ_WRITE",
"maxCon":1000,
"maxConnectTimeout":3000,
"maxRetryCount":5,
"minCon":1,
"name":"prototypeDs",
"password":"123456",
"type":"JDBC",
"url":"jdbc:mysql://localhost:3306/mysql?useUnicode=true&serverTimezone=Asia/Shanghai&characterEncoding=UTF-8",
"user":"root",
"weight":0
}
将MyCat配置到环境变量中
vim /etc/profile
#增加如下配置
export MYCAT_HOME=/usr/local/mycat
export PATH=$PATH:$MYCAT_HOME/bin
启动Mycat
#安装jdk或jre
yum install java -y
#赋予权限，注意目录
chmod u+x /usr/local/mycat/bin/*
#修改账户、密码（非必要步骤）
#文件中username、password是密码，请自行完成修改
vi /usr/local/mycat/conf/users/root.user.json
# 启动mycat
/data/mycat/bin/mycat start
验证是否可用
下图中端口默认是8066，可以在/usr/local/mycat/conf/server.json文件中修改，需要重启,账号和密码，可以在/usr/local/mycat/conf/users/root.user.json文件中配置的。
MyCat常用操作命令
mycat start #启动MyCat
mycat stop #停止MyCat
mycat console #前台运行MyCat带控制台输出
mycat restart #重启MyCat
mycat pause #暂停MyCat
mycat status #查看启动状态
使用MySQL命令进行MyCat的连接
Mycat 提供类似数据库的管理监控方式，可以通过MySQL命令行登录管理端口(8066）执行相应的SQL语句进行管理，也可以通过JDBC方式进行远程连接管理。
mysql -uroot -p123456 -h 192.168.47.100 -P8066 -DTESTDB
参数：
- -h：后面是主机。 即当前Mycat安装的主机IP地址。
- -u：Mycat server.xml中配置逻辑库的用户。
- -p：Mycat server.xml中配置逻辑库的密码。
- -P：后面是管理端口号。注意P是大写。
- -d：Mycat server.xml中配置逻辑库。
数据端口
8066用于进行数据的CRUD操作
mysql -uroot -p123456 -h 192.168.47.100 -P8066 -DTESTDB
MyCat的连接用户名相关信息在配置文件/usr/local/mycat/conf/users/root.user.json中查看编辑
{
"dialect":"mysql",
"ip":null,
"password":"123456",
"transactionType":"proxy",
"username":"root"
}
Mycat高级特性
读写分离
什么是读写分离
读写分离，基本的原理是让主数据库处理事务性增、改、删操作，而从数据库处理查询操作。
为什么使用读写分离
从集中到分布，最基本的一个需求不是数据存储的瓶颈，而是在于计算的瓶颈，即 SQL 查询的瓶颈，我们知道，正常情况下，Insert SQL 就是几十个毫秒的时间内写入完成，而系 统中的大多数 Select SQL 则要几秒到几分钟才能有结果，很多复杂的 SQL，其消耗服务器 CPU 的能力超强，不亚于死循环的威力。
读写分离方案
MyCat的读写分离是建立在MySQL主从复制基础之上实现的，所以必须先搭建MySQL的主从复制。数据库读写分离对于⼤型系统或者访问量很⾼的互联网应用来说，是必不可少的⼀个重要功能。
注意：
Mycat实现的读写分离和自动切换机制，需要MySQL的主从复制机制配合。
MySql 主从复制的常用拓扑结构
一主一从
是最基础的复制结构，用来分担之前单台数据库服务器的压力，可以进行读写分离。
一主多从
一台 Slave 承受不住读请求压力时，可以添加多台，进行负载均衡，分散读压力。
还可以对多台 Slave 进行分工，服务于不同的系统，例如一部分 Slave 负责网站前台的读请求，另一部分 Slave 负责后台统计系统的请求。
因为不同系统的查询需求不同，对 Slave 分工后，可以创建不同的索引，使其更好的服务于目标系统。
双主复制
Master 存在下线的可能，例如故障或者维护，需要把 Slave 切换为 Master。
在原来的 Master 恢复可用后，由于其数据已经不是最新的了，不能再做主，需要做为 Slave 添加进来。
那么就需要对其重新搭建复制环境，需要耗费一定的工作量。
双主结构就是用来解决这个问题的，互相将对方作为自己的 Master，自己作为对方的 Slave 来进行复制，但对外来讲，还是一个主和一个从。
当 主Master 下线时，备Master 切换为 主Master，当原来的 主Master 上线后，因为他记录了自己当前复制到对方的什么位置了，就会自动从之前的位置开始重新复制，不需要人为地干预，大大提升了效率。
级联复制
当直接从属于 Master 的 Slave 过多时，连到 Master 的 Slave IO 线程就比较多，对 Master 的压力是很大的。
级联结构就是通过减少直接从属于 Master 的 Slave 数量，减轻 Master 的压力，分散复制请求，从而提高整体的复制效率。
双主级联
级联复制结构解决了 Slave 过多导致的瓶颈问题，但还是有单主结构中切换主时的维护问题。
那么为了解决这个问题，就可以加入上面的双主结构。
在必要时，可以再对 Slaves 进行分级。
Mysql 的复制结构有很多种方式，复制的最大问题是数据延时，选择复制结构时需要根据自己的具体情况，并评估好目标结构的延时对系统的影响。
搭建读写分离
创建数据库
创建数据源
首先创建读写的数据源
/*+ mycat:createDataSource{
"dbType":"mysql",
"idleTimeout":60000,
"initSqls":[],
"initSqlsGetConnection":true,
"instanceType":"READ_WRITE",
"maxCon":1000,
"maxConnectTimeout":3000,
"maxRetryCount":5,
"minCon":1,
"name":"m1",
"password":"123456",
"type":"JDBC",
"url":"jdbc:mysql://192.168.47.100:3306?useUnicode=true&serverTimezone=UTC&characterEncoding=UTF-8",
"user":"root",
"weight":0
} */;
创建读的数据源
/*+ mycat:createDataSource{
"dbType":"mysql",
"idleTimeout":60000,
"initSqls":[],
"initSqlsGetConnection":true,
"instanceType":"READ",
"maxCon":1000,
"maxConnectTimeout":3000,
"maxRetryCount":5,
"minCon":1,
"name":"m1s1",
"password":"123456",
"type":"JDBC",
"url":"jdbc:mysql://192.168.47.101:3306?useUnicode=true&serverTimezone=UTC&characterEncoding=UTF-8",
"user":"root",
"weight":0
} */;
查询数据源
/*+ mycat:showDataSources{} */
创建集群
配置主从，masters(主)和replicas(从)分别对应着m1和m1s1
/*! mycat:createCluster{
"clusterType":"MASTER_SLAVE",
"heartbeat":{
"heartbeatTimeout":1000,
"maxRetry":3,
"minSwitchTimeInterval":300,
"slaveThreshold":0
},
"masters":[
"m1"
],
"maxCon":2000,
"name":"prototype",
"readBalanceType":"BALANCE_ALL",
"replicas":[
"m1s1"
],
"switchType":"SWITCH"
} */;
查询集群
/*+ mycat:showClusters{} */
关联着m1和m1s1数据源，m1负责读写，m1s1负责读
创建逻辑库，为其配置集群
CREATE DATABASE db1 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
执行完后发现除了mycat，主从库也新增了一个db1
修改数据源
[root@VM-0-11-centos mysqlms]~ vim /mydata/mycat/mycat/conf/schemas/db1.schema.json
在里面添加，让它指向刚刚创建的name为prototype的集群
"targetName":"prototype"
测试读写分离
在mycat的db1库中新建一张SYS_USER表
CREATE TABLE SYS_USER(
ID BIGINT PRIMARY KEY,
USERNAME VARCHAR(200) NOT NULL,
ADDRESS VARCHAR(500)
)
执行完成后查看db1.schema.json文件，发现它记录了sql语句
分别往主库和从库中添加一条数据，要不一样的
分片技术
前沿
目前很多互联网系统都存在单表数据量过大的问题，这就降低了查询速度，影响了客户体验。为了提高查询速度，我们可以优化sql语句，优化表结构和索引，不过对那些百万级千万级的数据库表，即便是优化过后，查询速度还是满足不了要求。
垂直分割
垂直拆分是指数据表列的拆分，把一张列比较多的表拆分为多张表。表的记录并不多，但是字段却很长，表占用空间很大，检索表的时候需要执行大量的IO，严重降低了性能。这时需要把大的字段拆分到另一个表，并且该表与原表是一对一的关系。
拆分原则：
- 把不常用的字段单独放在一张表
- 把text，blob等大字段拆分出来放在附表中
- 经常组合查询的列放在一张表中
举个例子
拆分思路：
如果我们只想查询id为8的学生的分数：select 分数 from 答题表 where id = 8;虽然知识查询分数，但是题目和回答这两个大字段也是要被扫描的，很消耗性能。但是我们只关心分数，并不想查询题目和回答。这就可以使用垂直分割。
水平拆分-分表
前言
上面谈到垂直切分只是把表按模块划分到不同数据库，但没有解决单表大数据量的问题。
水平拆分
相对于垂直拆分，水平拆分不是将表做分类，而是按照某个字段的某种规则来分散到多个库之中，每个表中包含一部分数据。
理解：
我们可以将数据的水平切分理解为是按照数据行的切分，就是将表中的某些行切分到一个数据库，而另外的某些行又切分到其他的数据库中。
实现分表
1、创建商品分类表 ProductClass
CREATE TABLE `ProductClass` (
`id` bigint(20) NOT NULL,
`className` varchar(40) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
dbpartition BY mod_hash(`id`)
tbpartition BY mod_hash(`id`)
tbpartitions 3 dbpartitions 1;
通过id进行分片,分成1个数据库3张表。
2、进行数据存储测试
INSERT INTO `ProductClass` VALUES (1, '手机');
INSERT INTO `ProductClass` VALUES (2, '电脑');
INSERT INTO `ProductClass` VALUES (3, '耳机');
INSERT INTO `ProductClass` VALUES (4, '小米手环');
INSERT INTO `ProductClass` VALUES (5, '摄像头');
INSERT INTO `ProductClass` VALUES (6, 'ipad');
INSERT INTO `ProductClass` VALUES (7, '电风扇');
INSERT INTO `ProductClass` VALUES (8, '冰箱');
ER表
问题引入
遇到问题：
orders表分片了,那和他相关的orders_detail 表未分片, join联查的时候, master1正常查询出结果, master2上由于没有 orders_detail 表,则报错, 最后聚合结果肯定也是错误的。
ER 表
将子表的存储位置依赖于主表，并且物理上紧邻存放因此彻底解决了JION的效率和性能问题，根据这一思路，提出了基于E-R关系的数据分片策略，子表的记录与所关联的父表记录存放在同一个数据分片上。
配置ER表
CREATE TABLE `travelrecord`(
`id` bigint NOT NULL AUTO_INCREMENT,
`user_id` varchar(100) DEFAULT NULL,
`traveldate` date DEFAULT NULL,
`fee` decimal(10,0) DEFAULT NULL,
`days` int DEFAULT NULL,
`blob` longblob,
PRIMARY KEY (`id`),
KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 dbpartition by mod_hash(id) tbpartition by mod_hash(id) tbpartitions 2 dbpartitions 2;
CREATE TABLE `travelrecord2` (
`id` bigint NOT NULL AUTO_INCREMENT,
`user_id` varchar(100) DEFAULT NULL,
`traveldate` date DEFAULT NULL,
`fee` decimal(10,0) DEFAULT NULL,
`days` int DEFAULT NULL,
`blob` longblob,
PRIMARY KEY (`id`),
KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 dbpartition by mod_hash(user_id) tbpartition by mod_hash(user_id) tbpartitions 2 dbpartitions 2;
查看配置的表是否具有 ER 关系,使用
/*+ mycat:showErGroup{}*/
全局表
全局表描述
在分片的情况下，当业务表因为规模而进行分片以后，业务表与这些附属的字典表之间的关联，就成了比较棘手的问题，考虑到字典表具有以下几个特性：
- 变动不频繁
- 数据量总体变化不大
- 数据规模不大，很少有超过数十万条记录。
全局表特征
- 全局表的插入更新操作会实时在所有节点上执行,保持各个分片的数据一致。
- 全局表的查询操作,只从一个节点获取。
- 全局表可以和任何一个表进行 JOIN 操作。
配置全局表
#在建表语句中加上关键字 BROADCAST（广播，即为全局表）
DROP TABLE IF EXISTS `china`;
CREATE TABLE `china` (
`Id` INT(11) NOT NULL,
`Name` VARCHAR(40) DEFAULT NULL,
`Pid` INT(11) DEFAULT NULL,
PRIMARY KEY (`Id`),
KEY `FK_CHINA_REFERENCE_CHINA` (`Pid`),
CONSTRAINT `FK_CHINA_REFERENCE_CHINA` FOREIGN KEY (`Pid`) REFERENCES `china` (`Id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 BROADCAST;
添加数据
INSERT INTO `china` VALUES ('0', '中国', '0');
INSERT INTO `china` VALUES ('110000', '北京市', '0');
INSERT INTO `china` VALUES ('110100', '东城区', '110000');
INSERT INTO `china` VALUES ('110200', '西城区', '110000');
INSERT INTO `china` VALUES ('110500', '朝阳区', '110000');
INSERT INTO `china` VALUES ('110600', '丰台区', '110000');
INSERT INTO `china` VALUES ('110700', '石景山区', '110000');
INSERT INTO `china` VALUES ('110800', '海淀区', '110000');
INSERT INTO `china` VALUES ('110900', '门头沟区', '110000');
INSERT INTO `china` VALUES ('111100', '房山区', '110000');
INSERT INTO `china` VALUES ('111200', '通州区', '110000');
INSERT INTO `china` VALUES ('111300', '顺义区', '110000');
INSERT INTO `china` VALUES ('111400', '昌平区', '110000');
INSERT INTO `china` VALUES ('111500', '大兴区', '110000');
INSERT INTO `china` VALUES ('111600', '怀柔区', '110000');
INSERT INTO `china` VALUES ('111700', '平谷区', '110000');
INSERT INTO `china` VALUES ('112800', '密云县', '110000');
INSERT INTO `china` VALUES ('112900', '延庆县', '110000');
进入相关目录查看 schema 配置
Mycat分片规则
分片算法
| 分片算法 | 描述 | 分库 | 分表 | 数值类型 |
|---|---|---|---|---|
| MOD_HASH | 取模哈希 | 是 | 是 | 数值，字符串 |
| UNI_HASH | 取模哈希 | 是 | 是 | 数值，字符串 |
| RIGHT_SHIFT | 右移哈希 | 是 | 是 | 数值 |
| RANGE_HASH | 两字段其一取模 | 是 | 是 | 数值，字符串 |
| YYYYMM | 按年月哈希 | 是 | 是 | DATE，DATETIME |
| YYYYDD | 按年日哈希 | 是 | 是 | DATE，DATETIME |
| YYYYWEEK | 按年周哈希 | 是 | 是 | DATE，DATETIME |
| HASH | 取模哈希 | 是 | 是 | 数值，字符串，如果不是，则转换成字符串 |
| MM | 按月哈希 | 否 | 是 | DATE，DATETIME |
| DD | 按日期哈希 | 否 | 是 | DATE，DATETIME |
| MMDD | 按月日哈希 | 是 | 是 | DATE，DATETIME |
| WEEK | 按周哈希 | 否 | 是 | DATE，DATETIME |
| STR_HASH | 字符串哈希 | 是 | 是 | 字符串 |
常用分片规则
MOD_HASH
[数据分片]hash形式的分片算法。如果分片键是字符串，会将字符串hash转换为数值类型。
- 分库键和分表键相同：
- . 分表下标：分片值%(分库数量*分表数量)
- 分库下标：分表下表/分库数量
- 分库键和分表键相同：
- 分表下标：分片值%分表数量
- 分库下标：分片值%分库数量
示例
CREATE TABLE orders (
id BIGINT NOT NULL AUTO_INCREMENT,
order_id INT,
amount DECIMAL(10,2),
PRIMARY KEY(id),
KEY `id` (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
---------------------------------------------------
-- 使用 mod_hash 分片算法，以 order_id 为分片键进行分片
DBPARTITION BY UNI_HASH(order_id)
TBPARTITION BY UNI_HASH(order_id)
-- 每个库中分配 1 个表
TBPARTITIONS 1
-- 分 3 个库 即 mydb_0、mydb_1、mydb_2
DBPARTITIONS 3;
详解：
当分片值为 101 时，
- DBPARTITIONS 3 会创建 3 个库，即 mydb_0 、mydb_1 、mydb_2 。
- TBPARTITIONS 1 每个库中会有一个分片表，即 mydb_0.orders_0、mydb_1.orders_1、mydb_2.orders_2。
- 分表下标 = 101 % （3 * 1）= 2 会被分配到 orders_2 中。
- 分库下标 = 2 / 1 = 2 -> 会被分配到 mydb_2 中。
YYYYMM
[数值分片]hash形式的分片算法。仅用于分库。(YYYY*12+MM)%分库数量，MM为1–12。
示例
/*建表语句*/
create table orders (
id INT AUTO_INCREMENT PRIMARY KEY,
customer_name VARCHAR(100),
order_date DATE,
total_amount DECIMAL(10, 2)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
------------------------------------------
dbpartition by YYYYMM(order_date) dbpartitions 1
tbpartition by YYYYMM(order_date) tbpartitions 3;
/*添加数据*/
INSERT into orders (id , customer_name,order_date , total_amount) value(1,"小米","2033-09-21 12:12:12",12.5)
INSERT into orders (id , customer_name,order_date , total_amount) value(2,"大米","2033-09-26 12:12:12",12.5)
INSERT into orders (id , customer_name,order_date , total_amount) value(3,"红豆","2033-07-21 12:12:12",12.5)
INSERT into orders (id , customer_name,order_date , total_amount) value(4,"黑豆","2033-07-26 12:12:12",12.5)
MMDD
仅用于分表。仅DATE、DATETIME类型。 一年之中第几天%分表数。tbpartitions不能超过366。
create table orders (
id INT AUTO_INCREMENT PRIMARY KEY,
customer_name VARCHAR(100),
order_date DATE,
total_amount DECIMAL(10, 2)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
dbpartition by MMDD(order_date) dbpartitions 8
tbpartition by MMDD(order_date) tbpartitions 12;
全局序列
在实现分库分表的情况下，数据库自增主键已无法保证全局唯一。Mycat2在1.x版本上简化全局序列，自动使用雪花算法生成全局序列号。如不需要Mycat默认的全局序列。可以通过配置关闭自动全局序列。
建表语句方式关闭全局序列
如果不需要使用 mycat 的自增序列,而使用 mysql 本身的自增主键的功能,需要在配置中更改对应的建表 sql,不设置 AUTO_INCREMENT 关键字,这样,mycat 就不认为这个表有自增主键的功能,就不会使用 mycat 的全局序列号.这样,对应的插入 sql 在 mysql处理,由 mysql 的自增主键功能补全自增值。
雪花算法：引入了时间戳和 ID 保持自增的分布式 ID 生成算法。
雪花算法的原理
就是生成一个的 64 位的 long 类型的唯一 id。
示例
#带 AUTO_INCREMENT 关键字使用默认全局序列
CREATE TABLE db1.`travelrecord` (
`id` bigint NOT NULL AUTO_INCREMENT,
`user_id` varchar(100) DEFAULT NULL,
`traveldate` date DEFAULT NULL,
`fee` decimal(10,0) DEFAULT NULL,
`days` int DEFAULT NULL,
`blob` longblob,
PRIMARY KEY (`id`),
KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 BROADCAST;
#去掉关键字，不使用
CREATE TABLE db1.`travelrecord` (
`id` bigint NOT NULL,
`user_id` varchar(100) DEFAULT NULL,
`traveldate` date DEFAULT NULL,
`fee` decimal(10,0) DEFAULT NULL,
`days` int DEFAULT NULL,
`blob` longblob,
PRIMARY KEY (`id`),
KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 BROADCAST;
Mycat高可用
MyCat实现读写分离架构
通过MyCat来实现MySQL的读写分离, 从而完成MySQL集群的负载均衡 , 如下面的结构图:
问题：
但是以上架构存在问题 , 由于MyCat中间件是单节点的服务, 前端客户端所有的压力过来都直接请求这一台MyCat , 存在单点故障。所以这个时候， 我们就需要考虑MyCat的集群 ；
MyCat集群架构
通过MyCat来实现后端MySQL的负载均衡 ， 通过HAProxy再实现MyCat集群的负载均衡。
介绍：
HAProxy负责将请求分发到MyCat上，起到负载均衡的作用，同时HAProxy也能检测到MyCat是否存活，HAProxy只会将请求转发到存活的 MyCat 上。如果一台MyCat服务器宕机，HAPorxy 转发请求时不会转发到宕机的MyCat 上，所以 MyCat 依然可用。
HAProxy介绍
HAProxy是一个开源的、高性能的基于TCP(第四层)和HTTP(第七层)应用的负载均衡软件。 使用HAProxy可以快速、可靠地实现基于TCP与HTTP应用的负载均衡解决方案。
问题：
因为所有的客户端请求都是先到达HAProxy, 由HAProxy再将请求再向下分发, 如果HAProxy宕机的话,就会造成整个Mycat集群不能正常运行, 依然存在单点故障。
MyCat的高可用集群
图解说明：
- HAProxy实现了MyCat多节点的集群高可用和负载均衡，而HAProxy自身的高可用则可以通过Keepalived来实现。因此，HAProxy主机上要同时安装 HAProxy和Keepalived，Keepalived负责为该服务器抢占vip（虚拟ip），抢占到vip后，对该主机的访问可以通过原来的ip访问，也可以直接通过vip访问。
- HAProxy负责将对vip的请求分发到MyCat集群节点上，起到负载均衡的作用。同时HAProxy也能检测到MyCat是否存活，HAProxy只会将请求转发到存活的MyCat上。
- 如果Keepalived+HAProxy高可用集群中的一台服务器宕机，集群中另外一台服务器上的 Keepalived会立刻抢占vip 并接管服务，此时抢占了 vip 的HAProxy节点可以继续提供服务。
- 如果一台MyCat服务器宕机，HAPorxy 转发请求时不会转发到宕机的 MyCat 上，所以 MyCat 依然可用。
keepalived介绍
Keepalived是一种基于VRRP协议来实现的高可用方案,可以利用其来避免单点故障。 通常有两台甚至多台服务器运行Keepalived，一台为主服务器(Master), 其他为备份服务器, 但是对外表现为一个虚拟IP(VIP), 主服务器会发送特定的消息给备份服务器, 当备份服务器接收不到这个消息时, 即认为主服务器宕机, 备份服务器就会接管虚拟IP, 继续提供服务, 从而保证了整个集群的高可用。
安装配置HAProxy
查看列表
yum list | grep haproxy
yum安装
yum -y install haproxy
修改配置文件
$ vim /etc/haproxy/haproxy.cfg
启动HAProxy
$ haproxy -f /etc/haproxy/haproxy.cfg
HAProxy配置文件
HAProxy配置文件主要由全局设定和代理设定两部分组成，包含5个域：global、default、frontend、backend、listen。
global
全局配置，定义haproxy进程的工作特性和全局配置
global
log 127.0.0.1 local2
chroot /var/lib/haproxy #chroot运行的路径
pidfile /var/run/haproxy.pid #haproxy pid的存放位置
maxconn 65536 #最大连接数
nbproc 10
ulimit-n 200000
user haproxy #haproxy的运行用户
group haproxy #haproxy的运行用户的所属组
daemon #守护进程的方式在后台工作
# turn on stats unix socket
stats socket /var/lib/haproxy/stats
注意：
全局配置，通常是一些进程级别的配置，与操作系统相关。
default
#---------------------------------------------------------------------
# common defaults that all the 'listen' and 'backend' sections will
# use if not designated in their block
#---------------------------------------------------------------------
defaults
mode http #默认使用的七层协议，也可以是tcp四层协议，如果配置为health，则表示健康检查，返回ok
log global
option tcplog #详细记录tcp日志
option redispatch
option dontlognull #不记录健康检查的日志信息
option forwardfor
retries 3 #重试次数为3次，失败3次以后则表示服务不可用
timeout http-request 5s #http请求超时时间，客户端建立连接5s但不请求数据的时候，关闭客户端连接
timeout queue 10s #等待最大时间，表示等待最大时长为10s
timeout connect 10s #连接超时时间，表示客户端请求转发至服务器所等待的时长为10s
timeout client 30m #客户端超时时间，表示客户端非活跃状态的时间为30min
timeout server 30m #服务器超时时间，表示客户端与服务器建立连接后，等待服务器的超时时间为30min
timeout http-keep-alive 10s #持久连接超时时间，表示保持连接的超时时长为10s
timeout check 10s #心跳检测超时时间，表示健康状态监测时的超时时间为10s
参数：
默认参数配置，主要是涉及的公共配置，在
defaults中一次性添加。frontend、backend、listen未配置时，都可以默认defaults中的参数配置。若配置了，会覆盖。
frontend & backend
frontend test
bind *:8082
default_backend test
option httplog
acl user-core path_beg /test/v1/user/
use_backend user-core_server if user-core
test
backend test
mode http
balance roundrobin
server node1 10.xxx.xxx.1:7000 check port 7000 inter 5000 rise 5 fall 5
server node2 10.xxx.xxx.2:7000 check port 7000 inter 5000 rise 5 fall 5
user-core_server
backend user-core_server
mode http
balance roundrobin
server node1 10.xxx.xxx.1:7001 check port 7001 inter 5000 rise 5 fall 5
server node2 10.xxx.xxx.2:7001 check port 7001 inter 5000 rise 5 fall 5 backup
frontend haproxy_statis_front
bind *:8081
mode http
default_backend statis_haproxy
backend statis_haproxy
mode http
balance roundrobin
stats uri /haproxy/stats
stats auth haproxy:zkK_HH@zz
stats refresh 30s
stats show-node
stats show-legends
stats hide-version
参数：
frontend可以看作是前端接收请求的部分，内部指定后端；backend可以看作是后端服务接收请求的部分；
listen
listen admin_stats
bind *:8080 #监听端口
mode http
option httplog
log global
#统计接口启用开关
stats enable
maxconn 10
#页面刷新时长
stats refresh 30s
#haproxy ui访问后缀
stats uri /haproxy?stats
#认证时的realm，作为提示用的
stats realm haproxy
#认证用户名和密码
stats auth admin:admin
#隐藏HAProxy版本号
stats hide-version
#管理界面只有认证通过后才能在ui上进行管理
stats admin if TRUE
参数：
listen是`frontend和backend的组合，haproxy的监控ui可以通过这个进行配置。
向配置文件中插入以下配置信息,并保存
global
log 127.0.0.1 local0
#log 127.0.0.1 local1 notice
#log loghost local0 info
maxconn 4096
chroot /var/lib/haproxy
pidfile /var/run/haproxy.pid
#uid 99
#gid 99
daemon
#debug
#quiet
defaults
log global
mode tcp
option abortonclose
option redispatch
retries 3
maxconn 2000
timeout connect 5000
timeout client 50000
timeout server 50000
listen proxy_status
bind :48066
mode tcp
balance roundrobin
server mycat_1 192.168.66.101:8066 check inter 10s
server mycat_2 192.168.66.102:8066 check inter 10s
frontend admin_stats
bind :7777
mode http
stats enable
option httplog
maxconn 10
stats refresh 30s
stats uri /admin
stats auth admin:123123
stats hide-version
stats admin if TRUE
启动验证
haproxy -f /etc/haproxy/haproxy.conf
查看HAProxy进程
ps -ef|grep haproxy
打开浏览器访问
http://192.168.47.100:7777/admin#在弹出框输入用户名：admin密码：123123
验证负载均衡，通过HAProxy访问Mycat
mysql -uroot -p123456 -h 192.168.66.101 -P 48066
安装配置Keepalived
高可用架构
查看列表
yum list | grep keepalived
在这里插入代码片
yum安装
yum install -y keepalived
查看yum安装的配置文件
rpm -ql keepalived
修改主节点配置文件
vim /etc/keepalived/keepalived.conf
! Configuration File for keepalived
global_defs {
## keepalived 自带的邮件提醒需要开启 sendmail 服务。建议用独立的监控或第三方 SMTP
router_id baizhan ## 标识本节点的字条串，通常为 hostname
}
## keepalived 会定时执行脚本并对脚本执行的结果进行分析，动态调整 vrrp_instance 的优先级。
## 如果脚本执行结果为 0，并且 weight 配置的值大于 0，则优先级相应的增加。
## 如果脚本执行结果非 0，并且 weight 配置的值小于 0，则优先级相应的减少。
## 其他情况，维持原本配置的优先级，即配置文件中 priority 对应的值。
vrrp_script chk_haproxy {
script "/etc/keepalived/haproxy_check.sh" ## 检测 haproxy 状态的脚本路径
interval 2 ## 检测时间间隔
weight 2 ## 如果条件成立，权重+2
}
## 定义虚拟路由， VI_1 为虚拟路由的标示符，自己定义名称
vrrp_instance VI_1 {
state MASTER ## 默认主设备（priority 值大的）和备用设备（priority 值小的）都设置为 BACKUP，
## 由 priority 来控制同时启动情况下的默认主备，否则先启动的为主设备
interface ens33 ## 绑定虚拟 IP 的网络接口，与本机 IP 地址所在的网络接口相同，我的是 eth3
virtual_router_id 35 ## 虚拟路由的 ID 号，两个节点设置必须一样，可选 IP 最后一段使用,
## 相同的 VRID 为一个组，他将决定多播的 MAC 地址
priority 120 ## 节点优先级，值范围 0-254， MASTER 要比 BACKUP 高
nopreempt ## 主设备（priority 值大的）配置一定要加上 nopreempt，否则非抢占也不起作用
advert_int 1 ## 组播信息发送间隔，两个节点设置必须一样，默认 1s
## 设置验证信息，两个节点必须一致
authentication {
auth_type PASS
auth_pass 1111 ## 真实生产，按需求对应该过来
}
## 将 track_script 块加入 instance 配置块
track_script {
chk_haproxy ## 检查 HAProxy 服务是否存活
}
## 虚拟 IP 池, 两个节点设置必须一样
virtual_ipaddress {
192.168.66.200
}
}
修改备用节点配置文件
vim /etc/keepalived/keepalived.conf
! Configuration File for keepalived
global_defs {
router_id baizhan
}
vrrp_script chk_haproxy {
script "/etc/keepalived/haproxy_check.sh"
interval 2
weight 2
}
vrrp_instance VI_1 {
state BACKUP
interface ens33
virtual_router_id 35
priority 110
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
track_script {
chk_haproxy
}
virtual_ipaddress {
192.168.66.200
}
}
编写 Haproxy 状态检测脚本
我们编写的脚本为/etc/keepalived/haproxy_check.sh (已在 keepalived.conf 中配置) 脚本要求：如果 haproxy 停止运行，尝试启动，如果无法启动则杀死本机的 keepalived 进程，keepalied将虚拟 ip 绑定到 BACKUP 机器上。 内容如下：
mkdir -p /usr/local/keepalived/log
vi /etc/keepalived/haproxy_check.sh
#!/bin/bash
START_HAPROXY="/usr/sbin/haproxy start"
STOP_HAPROXY="/usr/sbin/haproxy stop"
LOG_FILE="/usr/local/keepalived/log/haproxy-check.log"
HAPS=`ps -C haproxy --no-header |wc -l`
date "+%Y-%m-%d %H:%M:%S" >> $LOG_FILE
echo "check haproxy status" >> $LOG_FILE
if [ $HAPS -eq 0 ];then
echo $START_HAPROXY >> $LOG_FILE
$START_HAPROXY >> $LOG_FILE 2>&1
sleep 3
if [ `ps -C haproxy --no-header |wc -l` -eq 0 ];then
echo "start haproxy failed, killall keepalived" >> $LOG_FILE
killall keepalived
fi
fi
Mycat实施指南
分表分库原则
- 原则一：能不分就不分；
- 原则二：分片数量尽量少；
- 原则三：分片规则需要慎重选择；
- 原则四：尽量不要在一个事务中的SQL跨越多个分片；
- 原则五：查询条件尽量优化；
注意：
这里特别强调一下分片规则的选择问题，如果某个表的数据有明显的时间特征，比如订单、交易记录等，则他们通常比较合适用时间范围分片，因为具有时效性的数据。
数据拆分原则
- 达到一定数量级才拆分（800 万）；
- 不到 800 万但跟大表（超 800 万的表）有关联查询的表也要拆分，在此称为大表关联表 ；
- 小于100万的使用全局表；
- 大于100万小于800 万跟大表使用同样的拆分策略。
如果我的内容对你有帮助，请点赞，评论，收藏。创作不易，大家的支持就是我坚持下去的动力
开放原子开发者工作坊旨在鼓励更多人参与开源活动，与志同道合的开发者们相互交流开发经验、分享开发心得、获取前沿技术趋势。工作坊有多种形式的开发者活动，如meetup、训练营等，主打技术交流，干货满满，真诚地邀请各位开发者共同参与！
更多推荐
23
0- 0
已为社区贡献7条内容
所有评论(0)