本文详细介绍了数据库中间件Mycat的原理、作用，以及如何在Docker环境下搭建MySQL和Mycat，包括环境准备、安装配置、读写分离、数据分片、主从复制（一主一从、双主双从）和Mycat的分库分表策略。此外，还探讨了Mycat的常见分片规则和抗风险测试。
Mycat从入门到精通
数据库中间件Mycat
Mycat原理
Mycat 的原理中最重要的一个动词是“拦截”，它拦截了用户发送过来的 SQL 语句，首先对 SQL
语句做了一些特定的分析：如分片分析、路由分析、读写分离分析、缓存分析等，然后将此 SQL 发
往后端的真实数据库，并将返回的结果做适当的处理，最终再返回给用户。
MyCat作用
读写分离
数据分片
垂直拆分（分库）、水平拆分（分表）、垂直+水平拆分（分库分表）
环境准备
1.利用docker环境搭建高可用Mycat分布式集群
2.利用docker环境搭建高可用MySQL分布式集群（一主一从，双主双从）
下载MySQL镜像
启动MySQL
docker run --name mysql1 -p 33060:3306 -v /home/lieyan:/home/lieyan -e MYSQL_ROOT_PASSWORD=root -d mysql:5.7.37
进入MySQL运行环境
docker exec -it mysql1 /bin/bash
Docker环境中安装vim
1.编辑文件/etc/apt/sources.list文件，添加如下镜像
deb-src http://mirrors.aliyun.com/debian-security buster/updates main
deb http://mirrors.aliyun.com/debian/ buster main non-free contrib
2.执行镜像更新
apt-get update
3.安装vim
apt-get install vim
Docker环境中安装Mycat
docker pull manondidi/mycat
运行Mycat容器
docker run --name mycat -p 8066:8066 -p 9066:9066 -v /home/lieyan:/home/lieyan -d manondidi/mycat:latest
进入Mycat容器运行环境
docker exec -it mycat /bin/bash
配置Mycat
设置Mycat登录账户
在/usr/local/mycat/conf/server.xml中进行配置
配置Mycat逻辑库、表、分片节点
在/usr/local/mycat/conf/schema.xml中进行配置
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
</schema>
<dataNode name="dn1" dataHost="host1" database="lieyan"/>
<dataHost name="host1" maxCon="1000" minCon="10" balance="2"
writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<!-- can have multi write hosts -->
<writeHost host="hostM1" url="jdbc:mysql://192.168.1.16:33060" user="root"
password="root">
<readHost host="hostS1" url="jdbc:mysql://192.168.1.16:33061" user="root" password="root"/>
</writeHost>
<!-- <writeHost host="hostM2" url="localhost:3316" user="root" password="123456"/> -->
</dataHost>
</mycat:schema>
docker cp schema.xml mycat:/usr/local/mycat/conf/schema.xml
连接Mycat
mysql -umycat -p123456 -P 8066 -h 192.168.1.16
读写分离
INSERT INTO tmp001 values(2,@@hostname);
MySQL主从复制（一主一从）
MySQL主从复制原理
主机配置
从机配置
重启主机跟从机
docker restart mysql1
docker restart mysql2
主机上建立账户并授权
GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' IDENTIFIED BY '123456';
查询主机状态
从机配置需要复制的主机
CHANGE MASTER TO MASTER_HOST='192.168.1.16',
MASTER_PORT=33060,
MASTER_USER='slave',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=438;
启动从机复制功能
查看从机状态
主从复制测试
停止主从复制
stop slave;
重新配置主从复制
stop slave;
reset master;
MySQL双主双从
一个主机 m1 用于处理所有写请求，它的从机 s1 和另一台主机 m2 还有它的从机 s2 负责所有读请
求。当 m1 主机宕机后，m2 主机负责写请求，m1、m2 互为备机。
双主机双从机配置
Master1
[mysqld]
server-id=1
log-bin=mysql-bin
binlog-do-db=lieyan
binlog-format=STATEMENT
log-slave-updates
auto-increment-increment=2
auto-increment-offset=1
Master2
[mysqld]
server-id=3
log-bin=mysql-bin
binlog-do-db=lieyan
binlog-format=STATEMENT
log-slave-updates
auto-increment-increment=2
auto-increment-offset=2
Slave1
[mysqld]
server-id=2
relay-log=mysql-relay
Slave2
[mysqld]
server-id=4
relay-log=mysql-relay
重启MySQL服务
docker restart mysql1
docker restart mysql2
docker restart mysql3
docker restart mysql4
双主机建立账户并授权
GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' IDENTIFIED BY '123456';
查询双主机状态
show master status
从机配置需要复制的主机
CHANGE MASTER TO MASTER_HOST='192.168.1.16',
MASTER_PORT=33060,
MASTER_USER='slave',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=154;
CHANGE MASTER TO MASTER_HOST='192.168.1.16',
MASTER_PORT=33062,
MASTER_USER='slave',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=154;
启动从机复制功能
start slave;
查看从机状态
show slave status\G
两个主机配置相互复制
CHANGE MASTER TO MASTER_HOST='192.168.1.16',
MASTER_PORT=33062,
MASTER_USER='slave',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=154;
CHANGE MASTER TO MASTER_HOST='192.168.1.16',
MASTER_PORT=33060,
MASTER_USER='slave',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=154;
启动两台主机复制功能
start slave;
查看从机状态
show slave status\G
测试主从复制、读写分离、抗风险
主从复制（双主双从）
读写分离
修改Mycat配置文件
数据在Master2，Slave1，Slave2之间切换
抗风险测试
停掉Master1
Mycat中插入数据依然成功
启动Master1
数据在Master1，Slave1，Slave2之间切换
Mycat垂直分库
一个数据库由很多表的构成，每个表对应着不同的业务，垂直切分是指按照业务将表进行分类，
分布到不同 的数据库上面，这样也就将数据或者说压力分担到不同的库上面。
配置Mycat分库
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="DIVIDEDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
<table name="customer" dataNode="dn2" >
</table>
</schema>
<dataNode name="dn1" dataHost="host1" database="lieyan" />
<dataNode name="dn2" dataHost="host2" database="lieyan" />
<dataHost name="host1" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<!-- can have multi write hosts -->
<writeHost host="hostM1" url="jdbc:mysql://192.168.1.16:33065" user="root"
password="root">
</writeHost>
</dataHost>
<dataHost name="host2" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<!-- can have multi write hosts -->
<writeHost host="hostM2" url="jdbc:mysql://192.168.1.16:33066" user="root"
password="root">
</writeHost>
</dataHost>
</mycat:schema>
创建空数据库
在数据节点 dn1、dn2 上分别创建数据库 lieyan，两个数据节点可以是如下情况：
1.dataHost相同，database不同
2.dataHost不同，database相同
3.dataHost不同，database不同
使用Mycat分库
CREATE TABLE customer(
id INT AUTO_INCREMENT,
NAME VARCHAR(200),
PRIMARY KEY(id)
);
CREATE TABLE orders(
id INT AUTO_INCREMENT,
order_type INT,
customer_id INT,
amount DECIMAL(10,2),
PRIMARY KEY(id));
CREATE TABLE orders_detail(
id INT AUTO_INCREMENT,
detail VARCHAR(2000),
order_id INT,
PRIMARY KEY(id)
);
CREATE TABLE dict_order_type(
id INT AUTO_INCREMENT,
order_type VARCHAR(200),
PRIMARY KEY(id)
);
Mycat水平分表
MySQL 单表存储数据条数是有瓶颈的，单表达到 1000 万条数据就达到了瓶颈，会影响查询效率，
需要进行水平拆分（分表）进行优化。相对于垂直拆分，水平拆分不是将表做分类，而是按照某个字段的某种规则来分散到多个库之中，每个表中 包含一部分数据。
Mycat配置文件
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="DIVIDEDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
<table name="customer" dataNode="dn2" ></table>
<table name="orders" dataNode="dn1,dn3" rule="mod_rule"></table>
</schema>
<dataNode name="dn1" dataHost="host1" database="shop" />
<dataNode name="dn2" dataHost="host2" database="shop" />
<dataNode name="dn3" dataHost="host3" database="shop" />
<dataHost name="host1" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<!-- can have multi write hosts -->
<writeHost host="hostM1" url="jdbc:mysql://192.168.1.16:33065" user="root"
password="root">
</writeHost>
</dataHost>
<dataHost name="host2" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<!-- can have multi write hosts -->
<writeHost host="hostM2" url="jdbc:mysql://192.168.1.16:33066" user="root"
password="root">
</writeHost>
</dataHost>
<dataHost name="host3" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<!-- can have multi write hosts -->
<writeHost host="hostM3" url="jdbc:mysql://192.168.1.16:33067" user="root"
password="root">
</writeHost>
</dataHost>
</mycat:schema>
分片配置文件
<tableRule name="mod_rule">
<rule>
<columns>customer_id</columns>
<algorithm>mod-long</algorithm>
</rule>
</tableRule>
<function name="mod-long" class="io.mycat.route.function.PartitionByMod">
<!-- how many data nodes -->
<property name="count">2</property>
</function>
重启Mycat，创建表
插入数据
INSERT INTO orders(id,order_type,customer_id,amount) VALUES (1,101,100,100100);
INSERT INTO orders(id,order_type,customer_id,amount) VALUES(2,101,100,100300);
INSERT INTO orders(id,order_type,customer_id,amount) VALUES(3,101,101,120000);
INSERT INTO orders(id,order_type,customer_id,amount) VALUES(4,101,101,103000);
INSERT INTO orders(id,order_type,customer_id,amount) VALUES(5,102,101,100400);
INSERT INTO orders(id,order_type,customer_id,amount) VALUES(6,102,100,100020);
测试
Mycat分片join
修改配置文件
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="DIVIDEDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
<table name="customer" dataNode="dn2" ></table>
<table name="orders" dataNode="dn1,dn3" rule="mod_rule" autoIncrement="true" fetchStoreNodeByJdbc="true">
<childTable name="orders_detail" primaryKey="id" joinKey="order_id" parentKey="id" />
</table>
</schema>
<dataNode name="dn1" dataHost="host1" database="shop" />
<dataNode name="dn2" dataHost="host2" database="shop" />
<dataNode name="dn3" dataHost="host3" database="shop" />
<dataHost name="host1" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<!-- can have multi write hosts -->
<writeHost host="hostM1" url="jdbc:mysql://192.168.1.16:33065" user="root"
password="root">
</writeHost>
</dataHost>
<dataHost name="host2" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<!-- can have multi write hosts -->
<writeHost host="hostM2" url="jdbc:mysql://192.168.1.16:33066" user="root"
password="root">
</writeHost>
</dataHost>
<dataHost name="host3" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<!-- can have multi write hosts -->
<writeHost host="hostM3" url="jdbc:mysql://192.168.1.16:33067" user="root"
password="root">
</writeHost>
</dataHost>
</mycat:schema>
重启Mycat
如果Mycat建表出现大小写不一致，那就使用MySQL进行建表
插入数据
INSERT INTO orders_detail(id,detail,order_id) VALUES(1,'detail1',1);
INSERT INTO orders_detail(id,detail,order_id) VALUES(2,'detail2',2);
INSERT INTO orders_detail(id,detail,order_id) VALUES(3,'detail3',3);
INSERT INTO orders_detail(id,detail,order_id) VALUES(4,'detail4',4);
INSERT INTO orders_detail(id,detail,order_id) VALUES(5,'detail5',5);
INSERT INTO orders_detail(id,detail,order_id) VALUES(6,'detail6',6);
测试
Select o.*,od.detail from orders o inner join orders_detail od on o.id=od.order_id;
MyCat1.6 RE 子表数据插入，抛出异常
错误如下
ERROR 1064 (HY000): can't find (root) parent sharding node for sql:INSERT INTO....
修改schema.xml
## autoIncrement： 开启主键自增策略
## fetchStoreNodeByJdbc： 启用ER表使用JDBC方式获取DataNode
<table name="orders" dataNode="dn1,dn2" rule="mod_rule" autoIncrement="true" fetchStoreNodeByJdbc="true">
<childTable name="orders_detail" primaryKey="id" joinKey="order_id" parentKey="id" />
</table>
全局表
在分片的情况下，当业务表因为规模而进行分片以后，业务表与这些附属的字典表之间的关联，
就成了比较 棘手的问题，考虑到字典表具有以下几个特性：
① 变动不频繁
② 数据量总体变化不大
③ 数据规模不大，很少有超过数十万条记录
鉴于此，Mycat 定义了一种特殊的表，称之为“全局表”，全局表具有以下特性：
① 全局表的插入、更新操作会实时在所有节点上执行，保持各个分片的数据一致性
② 全局表的查询操作，只从一个节点获取
③ 全局表可以跟任何一个表进行 JOIN 操作
配置文件
<table name="dict_order_type" dataNode="dn1,dn2,dn3" type="global"></table>
重启Mycat
插入数据
INSERT INTO dict_order_type(id,order_type) VALUES(101,'type1');
INSERT INTO dict_order_type(id,order_type) VALUES(102,'type2');
测试
常用分片规则
取模
此规则为对分片字段求摸运算。也是水平分表最常用规则。
分片枚举
通过在配置文件中配置可能的枚举 id，自己配置分片，本规则适用于特定的场景，比如有些业务
需要按照省份或区县来做保存，而全国省份区县固定的，这类业务使用本条规则。
<table name="orders_ware_info" dataNode="dn2,dn3" rule="sharding_by_intfile"></table>
<tableRule name="sharding_by_intfile">
<rule>
<columns>areacode</columns>
<algorithm>hash-int</algorithm>
</rule>
</tableRule>
110=0
120=1
范围约定
此分片适用于，提前规划好分片字段某个范围属于哪个分片。
<table name="payment_info" dataNode="dn2,dn3" rule="auto_sharding_long"></table>
<tableRule name="auto_sharding_long">
<rule>
<columns>order_id</columns>
<algorithm>rang-long</algorithm>
</rule>
</tableRule>
0-102=0
103-200=1
日期分片
此规则为按天分片。
<table name="login_info" dataNode="dn2,dn3" rule="sharding_by_date"></table>
<tableRule name="sharding_by_date">
<rule>
<columns>login_date</columns>
<algorithm>partbyday</algorithm>
</rule>
</tableRule>
扫一扫
255
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
