1,什么是MyCat 2
Java语言编写的MySQL数据库网络协议的开源中间件,GPLv3协议开源
MyCat2http://mycatone.top/Mycat2是Mycat社区开发的一款分布式关系型数据库(中间件)。它支持分布式SQL查询,兼容MySQL通信协议,以Java生态支持多种后端数据库,通过数据分片提高数据查询处理能力。
2, 他有什么功能
优化查询计划
定制Calcite分布式查询引擎、编译SQL到关系代数表达式、规则优化引擎和代价优化引擎、生成物理执行计划、支持逻辑视图
SQL支持语法
任意跨库跨表join查询、支持跨库跨表非关联子查询、支持跨库跨表关联子查询、支持跨库跨表Window语法、支持全局二级索引、有限支持存储过程、支持可视化配置
高性能
支持并行拉取结果集、支持自动调动后端结果集、支持多种路由注释、优化器注释
优化
对请求的sql进行参数化、缓存物理执行计划、相同参数化sql的请求、将免去一些分析优化过程
支持原生协议
前端协议MySQL网络通信协议、MySQL原生网络协议异步非阻塞、JDBC接口支持多种数据库、生成物理执行计划
支持定制任意多字段路由
提供分片算法接口、优化器简化过滤条件、分片信息与关系表达式结合生成执行sql
学习之前需要知道:
单库单表
通常刚开始的手,应用的数据比较少,也不会很复杂,所以应用只有一个数据库,数据库中也是一个业务对应一张表,这也是我们刚开始接触数据层的形态。
2、读写分离
随着业务的发展,数据量和访问量不断增加,很多时候业务读多写少,比如:新闻网站,因为数据库的读压力远大于写压力。所以这时候再原来的数据库master基础上增加一个备用数据库slave,备库合主库存储着相同的数据。但是,备库只提供服务,主库提供写服务。以后的写操作已经事务中的读操作就走主库,其他走备库,这就是所谓的读写分离。
1)数据复制问题
因为最新写入的数据只会存储在主库中,之后想要在备库中读取到新数据就必须要从主库复制过来,这会带来一定的延迟,造成短期的数据不一致性。但这个问题应该也没有什么特别好的办法,主要依赖于数据库提供的数据复制机制,常用的是根据数据库日志 bin-log 实现数据复制
2)数据源选择问题
读写分离之后我们都知道写要找主库,读要找备库,但是程序不知道,所以我们在程序中应该根据 SQL 来判断出是读操作还是写操作,进而正确选择要访问的数据库。
数据量和访问量持续上升,读写分离已经无法满足业务,此时要考虑分库分表。
BINLOG:记录了所有的 DDL(数据定义语言)语句和 DML(数据操纵语言)语句,但是不包括数据查询语句。此日志对于灾难时的数据恢复起着极其重要的作用,MySQL的主从复制, 就是通过该binlog实现的。默认MySQL是未开启该日志的
复制过程是:
1. MySQL 主库在收到客户端提交事务的请求之后,会先写入 binlog,再提交事务,更新存储引擎中的数据,事务提交完成后,返回给客户端“操作成功”的响应
2. 从库会创建一个专门的 I/O 线程,连接主库的 log dump 线程,来接收主库的 binlog 日志,再把 binlog 信息写入 relay log 的中继日志里,再返回给主库“复制成功”的响应
3. 从库会创建一个用于回放 binlog 的线程,去读 relay log 中继日志,然后回放 binlog 更新存储引擎中的数据,最终实现主从的数据一致性
标准配置是 1主 2 从 一备用主库 (每建立一个从库就会多创建一个i/o 线程 log dump 资源有限)
环境准备 docker-compose.yml 构建两个容器 ,mysql mysql 01
version : '3.8'
services:
lwb-mysql:
container_name: lwb-mysql
image: mysql:5.7
build:
context: ./mysql
ports:
- "3306:3306"
volumes:
- ./mysql/conf:/etc/mysql/conf.d
- ./mysql/logs:/logs
- ./mysql/data:/var/lib/mysql
command: [
'mysqld',
'--skip-name-resolve',
'--innodb-buffer-pool-size=80M',
'--character-set-server=utf8mb4',
'--collation-server=utf8mb4_unicode_ci',
'--default-time-zone=+8:00',
'--lower-case-table-names=1'
]
environment:
MYSQL_DATABASE: 'ry-cloud'
MYSQL_ROOT_PASSWORD: password
lwb-mysql01:
container_name: lwb-mysql01
image: mysql:5.7
build:
context: ./mysql
ports:
- "3307:3306"
volumes:
- ./mysql01/conf:/etc/mysql/conf.d
- ./mysql01/logs:/logs
- ./mysql01/data:/var/lib/mysql
command: [
'mysqld',
'--skip-name-resolve',
'--innodb-buffer-pool-size=80M',
'--character-set-server=utf8mb4',
'--collation-server=utf8mb4_unicode_ci',
'--default-time-zone=+8:00',
'--lower-case-table-names=1'
]
environment:
MYSQL_DATABASE: 'ry-cloud'
MYSQL_ROOT_PASSWORD: password
启动后,分别创建两个用户 myscat 并赋予全部权限
CREATE USER 'mycat'@'%' IDENTIFIED BY '123456';
--必須要複的權限 mysql8才有的
GRANT XA_RECOVER_ADMIN ON *.* TO 'root'@'%';
---視情況賦值權限
GRANT ALL PRIVILEGES ON *.* TO 'mycat'@'%' ;
flush privileges;
3, 修改mycat的prototype的配置
4, 注意这里需要加到对应数据库的权限 我再测试时候创建再 ry-cloud 里了,导致连接 mysql 连接不上。。。。。
mycat 的连接配置 再安装目录./conf/user/root.user.json 文件中
mycat 服务的端口配置最大连接数,连接延时都在 在 ./confg/server.json 里配置的
集群配置读写分离
可选值:
BALANCE_ALL(默认值)
获取集群中所有数据源
BALANCE_ALL_READ
获取集群中允许读的数据源
BALANCE_READ_WRITE
获取集群中允许读写的数据源,但允许读的数据源优先
BALANCE_NONE
获取集群中允许写数据源,即主节点中选择
BalanceLeastActive
最少正在使用的连接数的mysql数据源被选中,如果连接数相同,则从连接数相同的数据源中的随机,使慢的机器收到更少。
BalanceRandom
利用随机算法产生随机数,然后从活跃的mysql数据源中进行选取。
BalanceRoundRobin
加权轮训算法,记录轮训的权值,每次访问加一,得到n,然后对mysql数据源进行轮训,如果权值已经为零,则跳过,如果非零则减一,n减1,直n为零则选中的节点就是需要访问的mysql数据源节点。
BalanceRunOnReplica
io.mycat.plug.loadBalance.BalanceRunOnReplica
把请求尽量发往从节点,不会把请求发到不可读(根据延迟值判断)与不可用的从节点
如果启动mysql 报错 dockers 启动 mysql error /var/run/mysqld/mysqld.sock.lock
解决办法: 是因为 docker 里的 用户是mysql 他没有权限去外部挂在的目录/mysql/data/
这里的/opt/data/mysql目录下的所示目录都是mysql容器启动时自动创建的,这里只有db目录的用户和组是:polkitd input,其它的就是root root,
cd /opt/data
sudo chown -R polkitd:input mysql
注意 修改权限之后要 删除 mysql docker 容器 ,然后重新启动
分库 分表
常用分片规则简介
MOD_HASH
[数据分片]hash形式的分片算法。如果分片键是字符串,会将字符串hash转换为数值类型。
- 分库键和分表键相同:
- 分表下标:分片值%(分库数量*分表数量)
- 分库下标:分表下表/分库数量
-
分库键和分表键是不同键
1.分表下标= 分片值%分表数量
2.分库下标= 分片值%分库数量
RIGHT_SHIFT
[数据分片]hash形式的分片算法。仅支持数值类型。
分片值右移两位,按分片数量取余。
YYYYMM
[数值分片]hash形式的分片算法。仅用于分库。
(YYYY*12+MM)%分库数量,MM为1–12。
MMDD
仅用于分表。仅DATE、DATETIME类型。
一年之中第几天%分表数。tbpartitions不能超过366。
我们将使用的分片方式:
MM
HASH型分片算法-MM
MM
仅用于分表
仅支持DATE/DATETIME
月份(1-12)%分表数
tbpartitions不超过12
详细讲解 分片方案具体实现一看就懂不懂你打我
mod_hash 分片算法规则
1,见库,表语句
-- 添加数据库db1
-- 创建数据库 db1
create database db1;
-- 创建数据表 orders 注意 分表分库规则使用mod_hash 并且分表键(就是表里的字段)相同:
CREATE TABLE db1.orders(
id BIGINT NOT NULL AUTO_INCREMENT,
order_type INT,
customer_id INT,
amount DECIMAL(10,2),
PRIMARY KEY(id),
KEY `id` (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8 dbpartition BY mod_hash(customer_id) tbpartition BY mod_hash(customer_id) tbpartitions 1 dbpartitions 2;
--- mod_hash:分片规则,里面值为字段,表示根据哪个字段进行取模哈希,然后分片数据 -------
---- tbpartitions 1 dbpartitions 2:表示分成2个库,每个库各1张表,具体要分多少库,每个库分多少表更改数字大小就行 -----
执行后结果如下:
解释:
首先创建 两个库 db1_0 和 db1_1 (注意这里不是1-0 ,前边得1 是我取的名字db1得1,不要弄混!)
当分片值为 101 时(分片值就是指的是 你要插入数据得分片键值)
DBPARTITIONS 2 会创建 2 个库,即db1_0、 db1_1
TBPARTITIONS 1 每个库中会有一个分片表,即db1_0.orders_0、 db1_1.orders_1、
分表下标 = 101 % (2 * 1)= 1 -> 会被分配到 orders_1 表中
分库下标 = 1 / 2 = 0 -> 会被分配到 db1_0
现在插入一条数据 分片值 101
-- 在mycat中执行
INSERT INTO db1.orders(id,order_type,customer_id,amount)
VALUES(101,101,100,100100);
结果如下:
MM
HASH型分片算法-MM
MM
仅用于分表
仅支持DATE/DATETIME
月份(1-12)%分表数
tbpartitions不超过12
-- 按 mm 创建 表
create table db1.logmonth (
`id` BIGINT(20) DEFAULT NULL,
`user_id` BIGINT(20) DEFAULT NULL,
`service_id` INT(11) DEFAULT NULL,
`create_date` DATETIME DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
dbpartition by MOD_HASH(id) dbpartitions 2
tbpartition by MM(create_date) tbpartitions 12;
创建两个库 每个库12个表
2.分库下标= 分片值%分库数量 == 1%2 = 1 所以库是在db1_1
分表 下表 = 月份(1-12)%分表数 == 1%12 = 1 所以在表cloudlogmonth_1
执行sql
insert into db1.logmonth(id, user_id, service_id, create_date) VALUES (1, 1, 1, '2023-01-11 11:11:11');
结果:
重点来了,我们实际项目中用的分库分表逻辑
使用 yyyymm 分库
使用 mm 分表
代码演示如下:
CREATE TABLE db1.testtable (`id` BIGINT(20) DEFAULT NULL,
`user_id` BIGINT(20) DEFAULT NULL,
`service_id` INT(11) DEFAULT NULL,
`create_date` DATETIME DEFAULT NULL
) ENGINE = INNODB CHARSET = utf8
DBPARTITION BY YYYYMM(create_date)
DBPARTITIONS 2 TBPARTITION BY YYYYMM(create_date) TBPARTITIONS 12
-- (YYYY*12+MM)%分库数 = (2023*12+1)%2 = 1
-- 月份(1-12)%分表数 = 1 % 12 = 1
insert into db1.testtable(id, user_id, service_id, create_date) VALUES (2, 1, 1, '2023-01-12 12:12:11');
select * from db1.testtable
最后的落库位置:
db1 testtable_13 即testtable_1 (12 是0 ,13是1 如是者反复多次也。。。)
分表是固定的12个一个库,分多少库 就看各位的数据量有多大,一般是八个库。。具体是几个问你们老大,我订不了。。。