mycat基础概念
1、什么是MyCat
- 一个彻底开源的,面向企业应用开发的大数据库集群
- 支持事务、ACID、可以替代MySQL的加强版数据库
- 一个可以视为MySQL集群的企业级数据库,用来替代昂贵的Oracle集群
- 一个融合内存缓存技术、NoSQL技术、HDFS大数据的新型SQL Server
- 结合传统数据库和新型分布式数据仓库的新一代企业级数据库产品
- 一个新颖的数据库中间件产品
2、为什么使用MyCat
如今随着互联网的发展,数据的量级也是撑指数的增长,从GB到TB到PB。对数据的各种操作也是愈加的困难,传统的关系性数据库已经无法满足快速查询与插入数据的需求。这个时候NoSQL的出现暂时解决了这一危机。它通过降低数据的安全性,减少对事务的支持,减少对复杂查询的支持,来获取性能上的提升。但是,在有些场合NoSQL一些折衷是无法满足使用场景的,就比如有些使用场景是绝对要有事务与安全指标的。这个时候NoSQL肯定是无法满足的,所以还是需要使用关系性数据库。如何使用关系型数据库解决海量存储的问题呢?此时就需要做数据库集群,为了提高查询性能将一个数据库的数据分散到不同的数据库中存储,为应对此问题就出现了——MyCat
综上所述:Mycat作用为:能满足数据库数据大量存储;提高了查询性能
- 读写分离
- 数据分片 垂直拆分(分库) 、 水平拆分(分表) 、 垂直+水平拆分(分库分表)
- 多数据源整合
3、 数据库中间件对比
① Cobar属于阿里B2B事业群,始于2008年,在阿里服役3年多,接管3000+个MySQL数据库的schema, 集群日处理在线SQL请求50亿次以上。由于Cobar发起人的离职, Cobar停止维护。
② Mycat是开源社区在阿里cobar基础上进行二次开发,解决了cobar存在的问题,并且加入了许多新 的功能在其中。青出于蓝而胜于蓝。
③ OneProxy基于MySQL官方的proxy思想利用c进行开发的, OneProxy是一款商业收费的中间件。舍 弃了一些功能,专注在性能和稳定性上。
④ kingshard由小团队用go语言开发,还需要发展,需要不断完善。
⑤ Vitess是Youtube生产在使用, 架构很复杂。不支持MySQL原生协议,使用需要大量改造成本。
⑥Atlas是360团队基于mysql proxy改写,功能还需完善,高并发下不稳定。
⑦ MaxScale是mariadb(MySQL原作者维护的一个版本) 研发的中间件
⑧ MySQLRoute是MySQL官方Oracle公司发布的中间件
4、支持的数据库
支持MySQL ORACLE SQLServer等一些主流的数据库
5、核心技术(分库分表)
数据库分片指:通过某种特定的条件,将存放在一个数据库中的数据分散存放在不同的多个数据库(主机)中,这样来达到分散单台设备的负载,根据切片规则,可分为以下两种切片模式MyCAT通过定义表的分片规则来实现分片,每个表格可以捆绑一个分片规则,每个分片规则指定一个分片字段并绑定一个函数,来实现动态分片算法
1.Schema:逻辑库,与MySQL中的Database(数据库)对应,一个逻辑库中定义了所包括的Table。
2.Table:逻辑表,即物理数据库中存储的某一张表,与传统数据库不同,这里的表格需要声明其所存储的逻辑数据节点DataNode。在此可以指定表的分片规则。
3.DataNode:MyCAT的逻辑数据节点,是存放table的具体物理节点,也称之为分片节点,通过DataSource来关联到后端某个具体数据库上
4.DataSource:定义某个物理库的访问地址,用于捆绑到Datanode上
5、分片规则:前面讲了数据切分,一个大表被分成若干个分片表,就需要一定的规则,这样按照某种业务规则把数据分到某个分片的规则就是分片规则,数据切分选择合适的分片规则非常重要,将极大的避免后续数据处理的难
mycat01环境:
master01 | 192.168.1.133 |
---|---|
slave01 | 192.168.1.134 |
mycat01 | 192.168.1.128 |
client(mysql客户端) | 192.168.1.66 |
mysql主从复制
配置master:
[root@master ~]# vim /etc/my.cnf
[mysqld]
……
server_id=1
log_bin = /usr/local/mysql/data/log-bin
[root@master ~]# systemctl restart mysqld
[root@master ~]# mysql -uroot -p123
mysql> grant replication slave on *.* to rep@'192.168.1.%' identified by '123';
mysql> show master status;
+----------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------+----------+--------------+------------------+-------------------+
| log-bin.000001 | 446 | | | |
+----------------+----------+--------------+------------------+-------------------+
配置slave:
[root@slave ~]# rm -f /usr/local/mysql/data/auto.cnf
[root@slave ~]# vim /etc/my.cnf
[mysqld]
……
server_id=2
relay_log = /usr/local/mysql/data/relay-bin
relay_log_index = /usr/local/mysql/data/slave-relay-bin.index
[root@slave ~]# systemctl restart mysqld
[root@slave ~]# mysql -uroot -p123
mysql> change master to master_host='192.168.1.133',master_user='rep',master_password='123',master_log_file='log-bin.000001',master_log_pos=446;
mysql> start slave;
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.133
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: log-bin.000001
Read_Master_Log_Pos: 446
Relay_Log_File: relay-bin.000002
Relay_Log_Pos: 318
Relay_Master_Log_File: log-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
mycat安装部署
先在master创建测试数据,用于mycat配置文件中指定
[root@master ~]# mysql -uroot -p123
mysql> create database hao;
mysql> use hao
mysql> create table test(id int primary key auto_increment,name varchar(22));
mysql> insert into test values(1,'zh'),(2,'li');
mysql> select * from hao.test;
+----+------+
| id | name |
+----+------+
| 1 | zh |
| 2 | li |
+----+------+
1、下载及安装 前提条件↓
1、jdk:要求jdk必须是1.7及以上版本
2、Mysql:推荐mysql是5.5以上版本
3、Mycat:
Mycat的官方网站:http://www.mycat.org.cn/官网下载地址:http://dl.mycat.org.cn/
下载地址:https://github.com/MyCATApache/Mycatdownload Mycat有windows、linux多种版本。
[root@mycat ~]# yum -y install java-1.7.0-openjdk* #安装jdk环境
[root@mycat ~]# cd /usr/local/
[root@mycat local]# wget http://dl.mycat.org.cn/1.6.7.6/20210303094759/Mycat-server-1.6.7.6-release-20210303094759-linux.tar.gz
[root@mycat local]# tar zxf Mycat-server-1.6.7.6-release-20210303094759-linux.tar.gz #将压缩包解压到/usr/local/mycat下
[root@mycat local]# cd mycat/
[root@mycat mycat]# ln -s /usr/local/mycat/bin/mycat /usr/local/bin/ #链接命令完整路径
2、配置文件的相关配置
① schema.xml:定义逻辑库,表、分片节点等内容
② rule.xml:定义分片规则
③ server.xml:定义用户以及系统相关变量,如端口等
1)修改配置文件server.xml 修改用户信息,与MySQL区分, 如下:
[root@mycat ~]# vim /usr/local/mycat/conf/server.xml #带#号为需要修改的行
……
<user name="mycat" defaultAccount="true"> #mycat为client登录用户
<property name="password">123</property> #mycat为client登录密码
<property name="schemas">TESTDB</property>
<property name="defaultSchema">TESTDB</property>
<!--No MyCAT Database selected 错误前会尝试使用该schema作为schema,不设置则为null,报错 -->
<!-- 表级 DML 权限设置 -->
<!--
<privileges check="false">
<schema name="TESTDB" dml="0110" >
<table name="tb01" dml="0000"></table>
<table name="tb02" dml="1111"></table>
</schema>
</privileges>
-->
</user>
2)修改schema.xml
[root@mycat ~]# vim /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="hao" /> #
<dataHost name="host1" maxCon="1000" minCon="10" balance="0" #
writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="hostM1" url="jdbc:mysql://192.168.1.133:3306" user="root" password="123"> #
</writeHost>
</dataHost>
</mycat:schema>
3)启动mycat
mycat支持的命令{ console | start | stop | restart | status | dump }
Mycat的默认端口号为:8066
[root@mycat ~]# mycat start
或↑↓
[root@mycat ~]# mycat console
Running Mycat-server...
wrapper | --> Wrapper Started as Console
wrapper | Launching a JVM...
jvm 1 | Wrapper (Version 3.2.3) http://wrapper.tanukisoftware.org
jvm 1 | Copyright 1999-2006 Tanuki Software, Inc. All Rights Reserved.
jvm 1 |
jvm 1 | MyCAT Server startup successfully. see logs in logs/mycat.log
3、设置root权限
root只有在本地才有root权限,所以需要创建远程root账号,方便在数据库中更改数据,如果没有权限在客户端登入成功也不能做更改
[root@master01 ~]# mysql -uroot -p123
mysql> grant all on *.* to root@'%' identified by '123';
4、测试连接
从主或从复制mysql客户端到client或者直接在client yum mysql
[root@master ~]# scp /usr/local/mysql/bin/mysql root@192.168.1.66:/usr/local/bin/
或↑↓
[root@client ~]# yum -y install mysql
client测试
[root@client ~]# mysql -umycat -p123 -h192.168.1.128 -P8066
mysql> show databases;
+----------+
| DATABASE |
+----------+
| TESTDB |
+----------+
mysql> use TESTDB
mysql> show tables;
+------------------+
| Tables_in_mytest |
+------------------+
| test |
+------------------+
mysql> select * from test;
+----+------+
| id | name |
+----+------+
| 1 | zh |
| 2 | li |
+----+------+
mysql> insert into test values(3,'ww');
mysql> select * from test;
+----+------+
| id | name |
+----+------+
| 1 | zh |
| 2 | li |
| 3 | ww |
+----+------+
master验证:
mysql> select * from hao.test;
+----+------+
| id | name |
+----+------+
| 1 | zh |
| 2 | li |
| 3 | ww |
+----+------+
mycat02环境
master01 | 192.168.1.133 |
---|---|
slave01 | 192.168.1.134 |
mycat01 | 192.168.1.128 |
client(mysql客户端) | 192.168.1.66 |
master02 | 192.168.1.132 |
mycat读写分离
1、Mycat的读写分离是建立在Mysq的主从复制的基础上的 修改配置文件 schema.xml
[root@mycat ~]# vim /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="hao" />
<dataHost name="host1" maxCon="1000" minCon="10" balance="3" # 此时balance的值为3
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="hostM1" url="192.168.1.133:3306" user="root" password="123">
<readHost host="hostS1" url="192.168.1.134:3306" user="root" password="123" />
</writeHost>
</dataHost>
</mycat:schema>
2、启动程序
[root@mycat ~]# mycat console
(1) 设置balance="1"与writeType="0"
Balance参数设置:修改的balance属性,通过此属性配置读写分离的类型 负载均衡类型,目前的取值有4 种:
balance=“0” | 不开启读写分离机制, 所有读操作都发送到当前可用的 writeHost 上 |
---|---|
balance=“1” | 全部的 readHost 与 stand by writeHost 参与 select 语句的负载均衡,简单的说,当双主双从 模式(M1->S1, M2->S2,并且 M1 与 M2 互为主备),正常情况下, M2,S1,S2 都参与 select 语句的负载均衡 |
balance=“2” | 所有读操作都随机的在 writeHost、 readhost 上分发 |
balance=“3” | 所有读请求随机的分发到 readhost 执行,writerHost 不负担读压力 |
WriteType参数设置:
writeType=“0” | 所有写操作都发送到可用的writeHost上 |
---|---|
writeType=“1” | 所有写操作都随机的发送到readHost |
writeType=“2” | 所有写操作都随机的在writeHost、readhost分上发 |
“readHost是从属于writeHost的,即意味着它从那个writeHost获取同步数据,因此,当它所属的writeHost宕机了,则它也不会再参与到读写分离中来,即“不工作了”,这是因为此时,它的数据已经“不可靠”了。基于这个考虑,目前mycat 1.3和1.4版本中,若想支持MySQL一主一从的标准配置,并且在主节点宕机的情况下,从节点还能读取数据,则需要在Mycat里配置为两个writeHost并设置banlance=1。”
(2) 设置switchType=“2” 与slaveThreshold="100"
switchType 目前有三种选择:
-1 | 表示不自动切换 |
---|---|
1 | 默认值,自动切换 |
2 | 基于MySQL主从同步的状态决定是否切换 |
“Mycat心跳检查语句配置为 show slave status ,dataHost 上定义两个新属性: switchType=“2” 与 slaveThreshold=“100”,此时意味着开启MySQL主从复制状态绑定的读写分离与切换机制。Mycat心跳机制通过检测 show slave status 中的 “Seconds_Behind_Master”, “Slave_IO_Running”, “Slave_SQL_Running” 三个字段来确定当前主从同步的状态以及Seconds_Behind_Master主从复制时延。“
3、修改master01的配置并重启mysql
[root@master01 ~]# vim /etc/my.cnf
……
binlog_format=statement
[root@master01 ~]# systemctl restart mysqld
4、验证:mycat 用户插入insert into hao.test(name) values(@@hostname);主从主机数据不一致,再查询
[root@client ~]# mysql -umycat -p123 -h192.168.1.128 -P8066
mysql> insert into hao.test(name) values(@@hostname);
mysql> select * from hao.test;
+------+-------+
| id | name |
+------+-------+
| 1 | zh |
| 2 | li |
| 3 | ww |
| 4 | slave | #可以看出读数据是在salve1上
+------+-------+
查看master01:
[root@master01 ~]# mysql -uroot -p123
mysql> select * from hao.test;
+----+----------+
| id | name |
+----+----------+
| 1 | zh |
| 2 | li |
| 3 | ww |
| 4 | master01 |
+----+----------+
查看slave1:
[root@slave ~]# mysql -uroot -p123
mysql> select * from hao.test;
+----+-------+
| id | name |
+----+-------+
| 1 | zh |
| 2 | li |
| 3 | ww |
| 4 | slave |
+----+-------+
垂直拆分——分库
一个数据库由很多表的构成,每个表对应着不同的业务,垂直切分是指按照业务将表进行分类, 分布到不同的数据库上面,这样也就将数据或者说压力分担到不同的库上面
分库的原则: 有紧密关联关系的表应该在一个库里,相互没有关联关系的表可以分到不同的库里。
1、 修改 schema 配置文件
[root@mycat ~]# vim /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">
<table name="customer" dataNode="dn2" />
</schema>
<dataNode name="dn1" dataHost="host1" database="hao" />
<dataNode name="dn2" dataHost="host2" database="hao" />
<dataHost name="host1" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="hostM1" url="jdbc:mysql://192.168.1.133:3306" user="root" password="123">
<readHost host="hostS1" url="jdbc:mysql://192.168.1.134:3306" user="root" password="123" />
</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>
<writeHost host="hostM2" url="jdbc:mysql://192.168.1.132:3306" user="root" password="123">
</writeHost>
</dataHost>
</mycat:schema>
[root@mycat ~]# mycat console
2、开启 master02 服务器,并且创建 test 库,在授予 mycat 所在主机远程访问权限:
在master02上:
#在数据节点master01、master02上分别创建数据库 hao,master01已有,只在master02创建即可
[root@master02 ~]# mysql -uroot -p123
mysql> create database hao;
mysql> grant all on *.* to root@'192.168.1.%' identified by '123';
访问 Mycat 进行分库:
[root@client ~]# mysql -umycat -p123 -h192.168.1.128 -P8066
mysql> use TESTDB
mysql> show tables;
+---------------+
| Tables_in_hao |
+---------------+
| test |
+---------------+
mysql> CREATE TABLE customer(
-> id INT AUTO_INCREMENT,
-> NAME VARCHAR(200),
-> PRIMARY KEY(id)
-> );
mysql> CREATE TABLE orders(
-> id INT AUTO_INCREMENT,
-> order_type INT,
-> customer_id INT,
-> amount DECIMAL(10,2),
-> PRIMARY KEY(id)
-> );
mysql> CREATE TABLE orders_detail(
-> id INT AUTO_INCREMENT,
-> detail VARCHAR(2000),
-> order_id INT,
-> PRIMARY KEY(id)
-> );
mysql> CREATE TABLE dict_order_type(
-> id INT AUTO_INCREMENT,
-> order_type VARCHAR(200),
-> PRIMARY KEY(id)
-> );
mysql> show tables;
+-----------------+
| Tables_in_hao |
+-----------------+
| customer |
| dict_order_type |
| orders |
| orders_detail |
| test |
+-----------------+
#查看表信息,可以看到成功分库
在master01上:
[root@master01 ~]# mysql -uroot -p123
mysql> use hao
mysql> show tables;
+-----------------+
| Tables_in_hao |
+-----------------+
| dict_order_type |
| orders |
| orders_detail |
| test |
+-----------------+
在master02上:
[root@master02 ~]# mysql -uroot -p123
mysql> use hao
mysql> show tables;
+---------------+
| Tables_in_hao |
+---------------+
| CUSTOMER |
+---------------+
mysql> desc CUSTOMER;
+-------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+----------------+
| ID | int(11) | NO | PRI | NULL | auto_increment |
| NAME | varchar(200) | YES | | NULL | |
+-------+--------------+------+-----+---------+----------------+
水平拆分——分表
相对于垂直拆分,水平拆分不是将表做分类,而是按照某个字段的某种规则来分散到多个库之中, 每个表中 包含一部分数据。简单来说,我们可以将数据的水平切分理解为是按照数据行的切分,就是将表中的某些行切分到一个数据库,而另外的某些行又切分到其他的数据库中,实现分表
选择要拆分的表 MySQL 单表存储数据条数是有瓶颈的,单表达到 1000 万条数据就达到了瓶颈,会影响查询效率, 需要进行水平拆分(分表) 进行优化。
例如:例子中的 orders、 orders_detail 都已经达到 600 万行数据,需要进行分表优化。 分表字段 以 orders 表为例,可以根据不同自字段进行分表
编号 | 分表字段 | 效果 |
---|---|---|
1 | id(主键、或创建时间) | 查询订单注重时效,历史订单被查询的次数少,如此分片会造成一个节点访问多,一个访问少,不平均 |
2 | customer_id(客户id) | 根据客户id去分,两个节点访问平均,一个客户的所有订单都在同一个节点 |
在master02上创建orders表:
CREATE TABLE orders(
id INT AUTO_INCREMENT,
order_type INT,
customer_id INT,
amount DECIMAL(10,2),
PRIMARY KEY(id)
);
修改配置文件 schema.xml
[root@mycat ~]# vim /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">
<table name="customer" dataNode="dn2" />
<table name="orders" dataNode="dn1,dn2" rule="mod_rule" /> #为 orders 表设置数据节点为 dn1、 dn2, 并指定分片规则为 mod_rule(自定义的名字)
</schema>
<dataNode name="dn1" dataHost="host1" database="hao" />
<dataNode name="dn2" dataHost="host2" database="hao" />
<dataHost name="host1" maxCon="1000" minCon="10" balance="0"
……
修改配置文件 rule.xml
<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>
[root@mycat ~]# mycat console
在mycat中插入数据,在dn1,dn2当中查看数据进行验证
[root@client ~]# mysql -umycat -p123 -h192.168.1.128 -P8066
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);
mysql> select * from orders;
+------+------------+-------------+-----------+
| id | order_type | customer_id | amount |
+------+------------+-------------+-----------+
| 1 | 101 | 100 | 100100.00 |
| 2 | 101 | 100 | 100300.00 |
| 6 | 102 | 100 | 100020.00 |
| 3 | 101 | 101 | 120000.00 |
| 4 | 101 | 101 | 103000.00 |
| 5 | 102 | 101 | 100400.00 |
+------+------------+-------------+-----------+
在master01上:
mysql> select * from hao.orders;
+----+------------+-------------+-----------+
| id | order_type | customer_id | amount |
+----+------------+-------------+-----------+
| 1 | 101 | 100 | 100100.00 |
| 2 | 101 | 100 | 100300.00 |
| 6 | 102 | 100 | 100020.00 |
+----+------------+-------------+-----------+
在master02上:
mysql> select * from hao.orders;
+----+------------+-------------+-----------+
| id | order_type | customer_id | amount |
+----+------------+-------------+-----------+
| 3 | 101 | 101 | 120000.00 |
| 4 | 101 | 101 | 103000.00 |
| 5 | 102 | 101 | 100400.00 |
+----+------------+-------------+-----------+
5、链接查询
在dn2 创建 orders_detail 表
CREATE TABLE orders_detail(
id INT AUTO_INCREMENT,
detail VARCHAR(2000),
order_id INT,
PRIMARY KEY(id)
);
修改配置文件 schema.xml
[root@mycat ~]# vim /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">
<table name="customer" dataNode="dn2" />
<table name="orders" dataNode="dn1,dn2" rule="mod_rule">
<childTable name="orders_detail" primaryKey="id" joinKey="order_id" parentKey="id" />
</table>
</schema>
……
[root@mycat ~]# mycat console
向 orders_detail 表插入数据
insert into orders_detail(id,detail,order_id) values (1,'detail',1);
insert into orders_detail(id,detail,order_id) values (2,'detail',2);
insert into orders_detail(id,detail,order_id) values (3,'detail',3);
insert into orders_detail(id,detail,order_id) values (4,'detail',4);
insert into orders_detail(id,detail,order_id) values (5,'detail',5);
insert into orders_detail(id,detail,order_id) values (6,'detail',6);
验证:
在mycat用户上:
mysql> select * from orders_detail;
+------+--------+----------+
| id | detail | order_id |
+------+--------+----------+
| 1 | detail | 1 |
| 2 | detail | 2 |
| 3 | detail | 3 |
| 4 | detail | 4 |
| 5 | detail | 5 |
| 6 | detail | 6 |
+------+--------+----------+
mysql> select orders.*,orders_detail.detail from orders_detail inner join orders on orders.id=orders_detail.order_id;
+----+------------+-------------+-----------+--------+
| id | order_type | customer_id | amount | detail |
+----+------------+-------------+-----------+--------+
| 1 | 101 | 100 | 100100.00 | detail |
| 2 | 101 | 100 | 100300.00 | detail |
| 6 | 102 | 100 | 100020.00 | detail |
| 3 | 101 | 101 | 120000.00 | detail |
| 4 | 101 | 101 | 103000.00 | detail |
| 5 | 102 | 101 | 100400.00 | detail |
+----+------------+-------------+-----------+--------+
在master01上:
mysql> select * from hao.orders_detail;
+----+--------+----------+
| id | detail | order_id |
+----+--------+----------+
| 1 | detail | 1 |
| 2 | detail | 2 |
| 6 | detail | 6 |
+----+--------+----------+
mysql> select orders.*,orders_detail.detail from orders_detail inner join orders on orders.id=orders_detail.order_id;
+----+------------+-------------+-----------+--------+
| id | order_type | customer_id | amount | detail |
+----+------------+-------------+-----------+--------+
| 1 | 101 | 100 | 100100.00 | detail |
| 2 | 101 | 100 | 100300.00 | detail |
| 6 | 102 | 100 | 100020.00 | detail |
+----+------------+-------------+-----------+--------+
在master02上:
mysql> select * from hao.orders_detail;
+----+--------+----------+
| id | detail | order_id |
+----+--------+----------+
| 3 | detail | 3 |
| 4 | detail | 4 |
| 5 | detail | 5 |
+----+--------+----------+
mysql> select orders.*,orders_detail.detail from orders_detail inner join orders on orders.id=orders_detail.order_id;
+----+------------+-------------+-----------+--------+
| id | order_type | customer_id | amount | detail |
+----+------------+-------------+-----------+--------+
| 3 | 101 | 101 | 120000.00 | detail |
| 4 | 101 | 101 | 103000.00 | detail |
| 5 | 102 | 101 | 100400.00 | detail |
+----+------------+-------------+-----------+--------+
五、全局分片
全局表 在分片的情况下,当业务表因为规模而进行分片以后,业务表与这些附属的字典表之间的关联, 就成了比较 棘手的问题,考虑到字典表具有以下几个特性:
① 变动不频繁 ② 数据量总体变化不大 ③ 数据规模不大
很少有超过数十万条记录 鉴于此, Mycat 定义了一种特殊的表,称之为“全局表”,全局表具有以下特性:
① 全局表的插入、更新操作会实时在所有节点上执行,保持各个分片的数据一致性
② 全局表的查询操作,只从一个节点获取
③ 全局表可以跟任何一个表进行 JOIN 操作 将字典表或者符合字典表特性的一些表定义为全局表
则从另外一个方面,很好的解决了数据 JOIN 的难题。 通过全局表+基于 E-R 关系的分片策略, Mycat 可以满足 80%以上的企业应用开发
修改 schema.xml 配置文件
[root@mycat ~]# vim /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">
<table name="customer" dataNode="dn2" />
<table name="orders" dataNode="dn1,dn2" rule="mod_rule">
<childTable name="orders_detail" primaryKey="id" joinKey="order_id" parentKey="id" />
</table>
<table name="dict_order_type" dataNode="dn1,dn2" type="global" />
</schema>
……
[root@mycat ~]# mycat console
在dn2 创建 dict_order_type 表
CREATE TABLE dict_order_type(
id INT AUTO_INCREMENT,
order_type VARCHAR(200),
PRIMARY KEY(id)
);
在mycat用户上:
insert into dict_order_type (id,order_type) values (101,'type1');
insert into dict_order_type (id,order_type) values (102,'type2');
mysql> select * from dict_order_type;
+-----+------------+
| id | order_type |
+-----+------------+
| 101 | type1 |
| 102 | type2 |
+-----+------------+
在master01:
mysql> select * from hao.dict_order_type;
+-----+------------+
| id | order_type |
+-----+------------+
| 101 | type1 |
| 102 | type2 |
+-----+------------+
在master02:
mysql> select * from hao.dict_order_type;
+-----+------------+
| id | order_type |
+-----+------------+
| 101 | type1 |
| 102 | type2 |
+-----+------------+
常用分片规则
1、 取模 此规则为对分片字段求摸运算。 也是水平分表最常用规则。 5.1 配置分表中, orders 表采用了此规则。
2、 分片枚举 通过在配置文件中配置可能的枚举 id,自己配置分片,本规则适用于特定的场景,比如有些业务 需要按照省份或区县来做保存,而全国省份区县固定的,这类业务使用本条规则。
mycat03环境
master01 | 192.168.1.133 |
---|---|
slave01 | 192.168.1.134 |
mycat01 | 192.168.1.128 |
client(mysql客户端) | 192.168.1.66 |
master02 | 192.168.1.132 |
mycat02 | 192.168.1.129 |
ha01/keepalived | 192.168.1.150 |
ha01/keepalived | 192.168.1.160 |
VIP | 192.168.1.200 |
Mycat高可用
在实际项目中, Mycat 服务也需要考虑高可用性,如果 Mycat 所在服务器出现宕机,或 Mycat 服务故障,需要有备机提供服务,需要考虑 Mycat 集群。
高可用方案:可以使用 HAProxy + Keepalived 配合两台 Mycat 搭起 Mycat 集群,实现高可用性。 HAProxy实现了MyCat 多节点的集群高可用和负载均衡, 而 HAProxy 自身的高可用则可以通过 Keepalived 来实现
安装配置 HAProxy
1、安装 HAProxy 准备好HAProxy安装包,解压到/usr/local/src
tar -zxvf haproxy-1.5.18.tar.gz -C /usr/local/src
进入解压后的目录,查看内核版本, 进行编译
cd /usr/local/src/haproxy-1.5.18
uname -r
make TARGET=linux310 PREFIX=/usr/local/haproxy ARCH=x86_64
# ARGET=linux310,内核版本,使用uname -r查看内核,如: 3.10.0-514.el7,此时该参数就为
linux310;
#ARCH=x86_64,系统位数;
#PREFIX=/usr/local/haprpxy #/usr/local/haprpxy,为haprpxy安装路径。
编译完成后,进行安装
make install PREFIX=/usr/local/haproxy
向配置文件中插入以下配置信息,并保存
vim /usr/local/haproxy/haproxy.conf
global
log 127.0.0.1 local0
#log 127.0.0.1 local1 notice
#log loghost local0 info
maxconn 4096
chroot /usr/local/haproxy
pidfile /usr/local/haproxy/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.18.5:8066 check inter 10s
server mycat_2 192.168.18.6: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
2、启动验证 启动HAProxy
/usr/local/haproxy/sbin/haproxy -f /usr/local/haproxy/haproxy.conf
查看HAProxy进程
ps -ef|grep haproxy
打开浏览器访问 http://192.168.140.125:7777/admin 在弹出框输入用户名: admin密码: 123123
验证负载均衡,通过HAProxy访问Mycat
mysql -umycat -p123456 -h 192.168.140.126 -P 48066
安装Keepalived
1、安装 Keepalived 准备好Keepalived安装包,解压到/usr/local/src
tar -zxvf keepalived-1.4.2.tar.gz -C /usr/local/src
安装依赖插件
yum install -y gcc openssl-devel popt-devel
进入解压后的目录, 进行配置, 进行编译
cd /usr/local/src/keepalived-1.4.2
./configure --prefix=/usr/local/keepalived
进行编译, 完成后进行安装
make && make install
运行前配置
cp /usr/local/src/keepalived-1.4.2/keepalived/etc/init.d/keepalived /etc/init.d/
mkdir /etc/keepalived
cp /usr/local/keepalived/etc/keepalived/keepalived.conf /etc/keepalived/
cp /usr/local/src/keepalived-1.4.2/keepalived/etc/sysconfig/keepalived /etc/sysconfig/
cp /usr/local/keepalived/sbin/keepalived /usr/sbin/
修改配置文件
vim /etc/keepalived/keepalived.conf
! Configuration File for keepalived
global_defs {
router_id LVS_DEVEL
}
vrrp_instance VI_1 {
state MASTER
interface eno16777736
virtual_router_id 51
priority 100
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
192.168.164.200
}
}
virtual_server 192.168.200.200 48066 {
delay_loop 6
lb_algo rr
lb_kind NAT
persistence_timeout 50
protocol TCP
real_server 192.168.164.5 48066 {
weight 1
TCP_CHECK {
connect_timeout 3
retry 3
delay_before_retry 3
}
}
real_server 192.168.164.6 48066 {
weight 1
TCP_CHECK {
connect_timeout 3
retry 3
delay_before_retry 3
}
}
}
2、启动验证
启动Keepalived
service keepalived start
登录验证
mysql -umycat -p123456 -h 192.168.140.200 -P 48066
测试高可用
关闭mycat 通过虚拟ip查询数据
mysql -umycat -p123456 -h 192.168.140.200 -P 48066
Mycat 安全设置
权限配置
1、user 标签权限控制
目前 Mycat 对于中间件的连接控制并没有做太复杂的控制,目前只做了中间件逻辑库级别的读写权限控制。是通过 server.xml 的 user 标签进行配置。
#server.xml配置文件user部分
<user name="mycat">
<property name="password">123456</property>
<property name="schemas">TESTDB</property>
</user>
<user name="user">
<property name="password">user</property>
<property name="schemas">TESTDB</property>
<property name="readOnly">true</property>
</user>
2、privileges 标签权限控制
在 user 标签下的 privileges 标签可以对逻辑库(schema)、表(table)进行精细化的 DML 权限控制。 privileges 标签下的 check 属性,如为 true 开启权限检查,为 false 不开启,默认为 false。 由于 Mycat 一个用户的 schemas 属性可配置多个逻辑库(schema) ,所以 privileges 的下级节点 schema 节点同样可配置多个,对多库多表进行细粒度的 DML 权限控制.
#server.xml配置文件privileges部分
#配置orders表没有增删改查权限
<user name="mycat">
<property name="password">123456</property>
<property name="schemas">TESTDB</property>
<!-- 表级 DML 权限设置 -->
<privileges check="true">
<schema name="TESTDB" dml="1111" >
<table name="orders" dml="0000"></table>
<!--<table name="tb02" dml="1111"></table>-->
</schema>
</privileges>
</user>
配置说明
DML 权限 | 增加(insert) | 更新(update) | 查询(select) | 删除(select) |
---|---|---|---|---|
0000 | 禁止 | 禁止 | 禁止 | 禁止 |
0010 | 禁止 | 禁止 | 可以 | 禁止 |
1110 | 可以 | 可以 | 可以 | 禁止 |
1111 | 可以 | 可以 | 可以 | 可以 |
SQL 拦截
firewall 标签用来定义防火墙; firewall 下 whitehost 标签用来定义 IP 白名单 , blacklist 用来定义SQL 黑名单。
1、白名单
可以通过设置白名单, 实现某主机某用户可以访问 Mycat,而其他主机用户禁止访问。 设置白名单:
#server.xml配置文件firewall标签
#配置只有192.168.140.128主机可以通过mycat用户访问
<firewall>
<whitehost>
<host host="192.168.140.128" user="mycat"/>
</whitehost>
</firewall>
2、黑名单
可以通过设置黑名单, 实现 Mycat 对具体 SQL 操作的拦截, 如增删改查等操作的拦截。
#设置黑名单
#server.xml配置文件firewall标签
#配置禁止mycat用户进行删除操作
<firewall>
<whitehost>
<host host="192.168.140.128" user="mycat"/>
</whitehost>
<blacklist check="true">
<property name="deleteAllow">false</property>
</blacklist>
</firewall>
可以设置的黑名单 SQL 拦截功能列表
配置项 | 缺省值 | 描述 |
---|---|---|
selelctAllow | true | 是否允许执行 SELECT 语句 |
deleteAllow | true | 是否允许执行 DELETE 语句 |
updateAllow | true | 是否允许执行 UPDATE 语句 |
insertAllow | true | 是否允许执行 INSERT 语句 |
createTableAllow | true | 是否允许创建表 |
setAllow | true | 是否允许使用 SET 语法 |
alterTableAllow | true | 是否允许执行 Alter Table 语句 |
dropTableAllow | true | 是否允许修改表 |
commitAllow | true | 是否允许执行 commit 操作 |
rollbackAllow | true | 是否允许执行 roll back 操作 |