Mycat数据库中间件

1、数据库中间件：

是一类连贯软件组件和利用的计算机软件，以便于软件各部件之间的沟通。 例子：Tomcat，web中间件。 数据库中间件：连贯java应用程序和数据库

2、为什么要用Mycat？

• Java与数据库紧耦合
• 高访问量高并发对数据库的压力
• 读写申请数据不统一

mysql单表瓶颈1000w数据,单库瓶颈5000w数据

数据库中间件比照:

Mycat的官网: http://www.mycat.io/

Mycat能干什么:

• 读写拆散
• 数据分片 垂直拆分（分库）、程度拆分（分表）、垂直+程度拆分（分库分表)
• 多数据源整合

原理:

Mycat 的原理中最重要的一个动词是“拦挡”，它拦挡了用户发送过去的 SQL 语句，首先对 SQL 语句做了一些特定的剖析：如分片剖析、路由剖析、读写拆散剖析、缓存剖析等，而后将此 SQL 发 往后端的实在数据库，并将返回的后果做适当的解决，最终再返回给用户

装置启动

1、解压后即可应用 解压缩文件拷贝到 linux 下 /usr/local/ 2、三个配置文件

• schema.xml：定义逻辑库，表、分片节点等内容
• rule.xml：定义分片规定
• server.xml：定义用户以及零碎相干变量，如端口等

3、批改配置文件server.xml 批改用户信息，与MySQL辨别

<!--用户名-->
<user name="root">
<property name="password">123456</property>     <!--明码-->
<property name="schemas">TESTDB</property> <!--治理的库-->
</user>

schemas:数据库名，这里会和schema.xml中的配置关联，多个用逗号离开，例如须要这个用户须要治理两个数据库db1,db2，则配置db1,db2

4.批改配置文件 schema.xml

删除<schema>标签间的表信息配置dataNode="dn1"， <dataNode>标签只留一个， <dataHost>标签只留一个， <writeHost>,<readHost>只留一对

<mycat:schema xmlns:mycat="http://io.mycat/">

<!-- 逻辑库 -->
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100"  dataNode="dn1">
<!-- 逻辑表 -->
</schema>

<!-- 数据节点 database填写实在数据库-->
<dataNode name="dn1" dataHost="host1" database="testdb" />

<!-- 数据主机 -->
<dataHost name="host1" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
<!-- 心跳检测 -->
<heartbeat>select user()</heartbeat>
<!-- 写主机 -->
<writeHost host="hostM1" url="192.168.107.132:3306" user="root"
password="000000">
<!-- 读主机 -->
<readHost host="hostS2" url="192.168.107.108:3306" user="hzy" password="000000"/>
</writeHost>

</dataHost>

</mycat:schema>

5.验证数据库拜访状况

Mycat 作为数据库中间件要和数据库部署在不同机器上，所以要验证近程拜访状况。

mysql -uhzy -p000000 -h 192.168.107.108 -P 3306
mysql -uroot -p000000 -h 192.168.107.132 -P 3306

如近程拜访报错，请建对应用户

grant all privileges on . to root@'短少的host' identified by '000000';

6.启动mycat

• 控制台启动 ：去 mycat/bin 目录下执行 ./mycat console
• 后盾启动 ：去 mycat/bin 目录下 ./mycat start

为了能第一工夫看到启动日志，不便定位问题，咱们抉择控制台启动。

7.登录mycat

• 登录后盾治理窗口

此登录形式用于治理保护 Mycat

mysql -umycat -p123456 -P 9066 -h 192.168.140.128

常用命令如下：

show database;
show @@help;

• 登录数据窗口

此登录形式用于通过 Mycat 查问数据，咱们抉择这种形式拜访Mycat

mysql -uroot -p123456 -P 8066 -h 192.168.107.132

读写拆散

咱们通过 Mycat 和 MySQL 的主从复制配合搭建数据库的读写拆散，实现 MySQL 的高可用性。 咱们将搭建：一主一从、双主双从两种读写拆散模式。

mysql主从复制原理:

与redis不同的是,redis从机会把主机的数据从头到尾复制,而mysql从机只会从接入点开始复制; 并且会产生屡次IO有延时问题

主机配置

批改配置文件：
vim /etc/my.cnf

主服务器惟一ID

server-id=1

启用二进制日志

log-bin=mysql-bin

设置不要复制的数据库(可设置多个)

binlog-ignore-db=mysql
binlog-ignore-db=information_schema

设置须要复制的数据库

binlog-do-db=须要复制的主数据库名字

设置logbin格局

binlog_format=STATEMENT

binlog的三种模式:https://blog.csdn.net/vhomes/article/details/8082734

从机配置

vim /etc/my.cnf

从服务器惟一ID

server-id=2

启用中继日志

relay-log=mysql-relay

• 重启服务:systemctl restart mysqld
• 确认防火墙已敞开:systemctl status firewalld
• 在主机MySQL里执行受权命令: GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' IDENTIFIED BY '123123';

如果报错:Your password does not satisfy the current policy requirements,请执行:

set global validate_password_policy=0;
set global validate_password_length=4;

• 查看主机状态:

1 row in set (0.01 sec)

记录下File和Position的值, 执行完此步骤后不要再操作主服务器MySQL，避免主服务器状态值变动

• 在从机上配置须要复制的主机

复制主机的命令

CHANGE MASTER TO MASTER_HOST='主机的IP地址',
MASTER_USER='slave',
MASTER_PASSWORD='123123',
MASTER_LOG_FILE='mysql-bin.具体数字',
MASTER_LOG_POS=具体值;

启动从服务器复制性能

start slave;

查看从服务器状态

show slave statusG

例子:

CHANGE MASTER TO MASTER_HOST='192.168.107.132',
MASTER_USER='slave',
MASTER_PASSWORD='123123',
MASTER_LOG_FILE='mysql-bin.000003',
MASTER_LOG_POS=154;

如果之前配置过主从复制,须要先重置stop slave;, reset master;

接着启动从服务器复制性能 start slave;

查看从服务器状态 show slave statusG(不要加分号)

这两项尾Yes即为胜利

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

如果失败可查看Error项

Last_Errno: 0
Last_Error:
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:

主机新建库(my.cnf中规定的库)、新建表、insert 记录，从机复制

一主一从

<!-- 写主机 -->

    <writeHost host="hostM1" url="192.168.107.132:3306" user="root"               password="000000">

<!-- 读主机 -->

    <readHost host="hostS2" url="192.168.107.108:3306" user="hzy" password="000000"/>

启动mycat

在写主机数据库表mytbl中插入带零碎变量数据，造成主从数据不统一 INSERT INTO mytbl VALUES(2,@@hostname);

应用mycat查问,能够看到mycat操作的是主机数据

起因是咱们没有指定数据库拜访的负载策略balance

• 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 不累赘读压力

双主双从选1,单主单从或多从选3

批改schema.xml

<!-- 数据主机 -->

<dataHost name="host1" maxCon="1000" minCon="10" balance="3"          writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">

双主双从

一个主机 m1 用于解决所有写申请，它的从机 s1 和另一台主机 m2 还有它的从机 s2 负责所有读请 求。当 m1 主机宕机后，m2 主机负责写申请，m1、m2 互为备机。

Master1配置

批改配置文件：vim /etc/my.cnf

主服务器惟一ID

server-id=1

启用二进制日志

log-bin=mysql-bin

设置不要复制的数据库(可设置多个)

binlog-ignore-db=mysql
binlog-ignore-db=information_schema

设置须要复制的数据库

binlog-do-db=须要复制的主数据库名字

设置logbin格局

binlog_format=STATEMENT

在作为从数据库的时候，有写入操作也要更新二进制日志文件

log-slave-updates

示意自增长字段每次递增的量，指字段一次递增多少，其默认值是1，取值范畴是1 .. 65535

auto-increment-increment=2

示意自增长字段从哪个数开始，指自增字段的起始值，他的取值范畴是1 .. 65535

auto-increment-offset=1

Master2配置

批改配置文件：vim /etc/my.cnf

主服务器惟一ID

server-id=3

启用二进制日志

log-bin=mysql-bin

设置不要复制的数据库(可设置多个)

binlog-ignore-db=mysql
binlog-ignore-db=information_schema

设置须要复制的数据库

binlog-do-db=须要复制的主数据库名字

设置logbin格局

binlog_format=STATEMENT

在作为从数据库的时候，有写入操作也要更新二进制日志文件

log-slave-updates

示意自增长字段每次递增的量，指自增字段的起始值，其默认值是1，取值范畴是1 .. 65535

auto-increment-increment=2

示意自增长字段从哪个数开始，指自增字段的起始值，他的取值范畴是1 .. 6553

65535 auto-increment-offset=2

留神:auto-increment-offset不能够是反复的

Slave1配置

从服务器惟一ID

server-id=2

启用中继日志

relay-log=mysql-relay

Slave2配置

从服务器惟一ID

server-id=4

启用中继日志

relay-log=mysql-relay

• 双主机、双从机重启 mysql 服务
• 主机从机都敞开防火墙
• 在两台主机上建设帐户并受权 slave

在主机MySQL里执行受权命令

GRANT REPLICATION SLAVE ON . TO 'slave'@'%' IDENTIFIED BY '123123';

• 查看两台主机的状态show master status;,记录binlogid和接入点
• 设置两台从机的主机,Slava1 复制 Master1，Slava2 复制 Master2
• 两台主机互备,Master2 复制 Master1，Master1 复制 Master2

stop slave;
reset master;

复制主机的命令

CHANGE MASTER TO MASTER_HOST='主机的IP地址',
MASTER_USER='slave',
MASTER_PASSWORD='123123',
MASTER_LOG_FILE='mysql-bin.具体数字',
MASTER_LOG_POS=具体值;

启动从服务器复制性能

start slave;

查看从服务器状态

show slave statusG

• 批改schema.xml

<dataNode name="dn1" dataHost="host1" database="testdb" />
<dataHost name="host1" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100" >

<heartbeat>select user()</heartbeat>                <!-- can have multi write hosts -->                 <writeHost host="hostM1" url="192.168.140.128:3306" user="root"                                    password="123123">                             <!-- can have multi read hosts -->                             <readHost host="hostS1" url="192.168.140.127:3306" user="root" password="123123"/>                 </writeHost>    <writeHost host="hostM2" url="192.168.140.126:3306" user="root"                                    password="123123">                             <!-- can have multi read hosts -->                             <readHost host="hostS2" url="192.168.140.125:3306" user="root" password="123123"/>                 </writeHost>         

</dataHost>

balance="1": 全副的readHost与stand by writeHost参加select语句的负载平衡。

writeType="0": 所有写操作发送到配置的第一个writeHost，第一个挂了切到还生存的第二个

writeHost，重新启动后以切换后的为准，切换记录在配置文件中:dnindex.properties 。

switchType="1": 1 默认值，主动切换。

-1 示意不主动切换

2 基于 MySQL 主从同步的状态决定是否切换。

• 验证主从复制:Master1 主机新建库、新建表、insert 记录，Master2 和从机复制
• 验证读写拆散:

在写主机Master1数据库表mytbl中插入带零碎变量数据，造成主从数据不统一

INSERT INTO mytbl VALUES(3,@@hostname);,

在Mycat里查问mytbl表,能够看到查问语句在Master2（host81）、Slava1（host80）、Slava2（host82） 主从三个主机间切换;

进行数据库Master1 在Mycat里插入数据仍然胜利，Master2主动切换为写主机

垂直拆分——分库

依照业务将表进行分类（或者 Schema）来切分到不同的数据库（主机）之上，这种切能够称之为数据的垂直（纵向）切分

分库的准则：有严密关联关系的表应该在一个库里，互相没有关联关系的表能够分到不同的库里,因为分库的表不能够关联查问

客户表 rows:20万

CREATE TABLE customer(

id INT AUTO_INCREMENT, NAME VARCHAR(200), PRIMARY KEY(id) 

);

订单表 rows:600万

CREATE TABLE orders(

id INT AUTO_INCREMENT, order_type INT, customer_id INT, amount DECIMAL(10,2),     PRIMARY KEY(id)

);

订单具体表 rows:600万

CREATE TABLE orders_detail(

id INT AUTO_INCREMENT,     detail VARCHAR(2000),     order_id INT,     PRIMARY KEY(id) 

);

订单状态字典表 rows:20

CREATE TABLE dict_order_type(

id INT AUTO_INCREMENT,     order_type VARCHAR(200),     PRIMARY KEY(id) 

);

客户表分在一个数据库，另外三张都须要关联查问，分在另外一个数据库。

1.批改 schema 配置文件

<!-- 逻辑库 -->

<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">    <!-- 配置customer表调配到dn2 -->    <table name="customer" dataNode="dn2" ></table> </schema><!-- 数据节点 --><dataNode name="dn1" dataHost="host1" database="orders" /><dataNode name="dn2" dataHost="host2" database="orders" /><!-- host1 --><dataHost name="host1" maxCon="1000" minCon="10" balance="0"          writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">    <!-- 心跳检测 -->    <heartbeat>select user()</heartbeat>    <!-- 写主机 -->    <writeHost host="hostM1" url="192.168.107.132:3306" user="root"               password="000000">    </writeHost>    </dataHost><!-- host2 --><dataHost name="host2" maxCon="1000" minCon="10" balance="0"          writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">    <!-- 心跳检测 -->    <heartbeat>select user()</heartbeat>    <!-- 写主机 -->    <writeHost host="hostM1" url="192.168.107.108:3306" user="hzy"               password="000000">    </writeHost>    </dataHost>

通过mycat操作逻辑库默认执行到dataNode="dn1",咱们指定了customer调配到dataNode="dn2"

balance="0"不设置读写拆散

2.两台机器别离创立新的的数据库

CREATE DATABASE orders;

3.启动 Mycat

./mycat console

4.拜访 Mycat 进行分库

mysql -umycat -p123456 -h 192.168.140.128 -P 8066

切换到 TESTDB

创立 4 张表

查看表信息，能够看到胜利分库

程度拆分——分表

依据表中的数据的逻辑关系依照某个字段的某种规定，将同一个表中的数据依照某种条件拆分到多台数据库（主机）下面，这种切分称之为数据的程度（横向）切分

MySQL 单表存储数据条数是有瓶颈的，单表白到 1000 万条数据就达到了瓶颈，会影响查问效率， 须要进行程度拆分（分表）进行优化。 例如：例子中的 orders、orders_detail 都曾经达到 600 万行数据，须要进行分表优化。

1.分表字段 ,以 orders 表为例，能够依据不同自字段进行分表

• id主键或创立工夫:查问订单重视时效，历史订单被查问的次数少，如此分片会造成一个节点拜访多，一个拜访少，不均匀。
• customer_id（客户 id）: 依据客户 id 去分，两个节点拜访均匀，一个客户的所有订单都在同一个节点

2.批改schema 配置文件

<!-- 逻辑库 -->

<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">    <!-- 配置customer表调配到dn2 -->    <table name="customer" dataNode="dn2" ></table>    <table name="orders" dataNode="dn1,dn2"  rule="mod_rule" ></table> </schema>

orders表被划分到两个数据节点dn1,dn2,mod_rule分片规定名称

3.批改rule 配置文件

定义mod_rule分片规定,并指定规定实用字段为 customer_id，抉择分片算法 mod-long（对字段求模运算），customer_id 对两个节点求模，依据后果分片

<tableRule name="mod_rule">        <rule>            <columns>customer_id</columns>            <algorithm>mod-long</algorithm>        </rule></tableRule><!--配置算法 mod-long 参数 count 为 2，两个节点--><function name="mod-long" class="io.mycat.route.function.PartitionByMod">        <!-- 多少个数据节点 -->        <property name="count">2</property></function>

4.测试分表

• 在数据节点 dn2 上建 orders 表
• 重启 Mycat，让配置失效
• 拜访 Mycat 实现分片

• 在 mycat 里向 orders 表插入数据，INSERT 字段不能省略 INSERT INTO

  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、dn1、dn2中查看orders表数据，分表胜利

Mycat 的分片 “join”

Orders 订单表曾经进行分表操作了，和它关联的 orders_detail 订单详情表如何进行 join 查问。

orders_detail 也要进行分片操作。Join 的原理如下图：

ER 表

Mycat 借鉴了 NewSQL 畛域的新秀 Foundation DB 的设计思路，Foundation DB 创新性的提 出了 Table Group 的概念，其将子表的存储地位依赖于主表，并且物理上紧邻寄存，因而彻底解决了 JION 的效率和性能问 题，依据这一思路，提出了基于 E-R 关系的数据分片策略，子表的记录与所 关联的父表记录寄存在同一个数据分片上

批改 schema.xml 配置文件

配置orders的子表orders_detail,应用外键order_id进行分片,这样与orders表的数据分布严密

在 dn2 创立 orders_detail 表 重启 Mycat 拜访 Mycat 向 orders_detail 表插入数据

INSERT INTO orders_detail(id,detail,order_id) values(1,'detail1',1);
INSERT INTO orders_detail(id,detail,order_id) VALUES(2,'detail1',2);
INSERT INTO orders_detail(id,detail,order_id) VALUES(3,'detail1',3);
INSERT INTO orders_detail(id,detail,order_id) VALUES(4,'detail1',4);
INSERT INTO orders_detail(id,detail,order_id) VALUES(5,'detail1',5);
INSERT INTO orders_detail(id,detail,order_id) VALUES(6,'detail1',6);

在mycat、dn1、dn2中运行两个表join语句

Select o.*,od.detail from orders o
inner join orders_detail od on o.id=od.order_id;

全局表

在分片的状况下，当业务表因为规模而进行分片当前，业务表与这些从属的字典表之间的关联， 就成了比拟 辣手的问题，思考到字典表具备以下几个个性： ① 变动不频繁 ② 数据量总体变动不大 ③ 数据规模不大，很少有超过数十万条记录

鉴于此，Mycat 定义了一种非凡的表，称之为“全局表”，全局表具备以下个性：

• 全局表的插入、更新操作会实时在所有节点上执行，放弃各个分片的数据一致性
• 全局表的查问操作，只从一个节点获取
• 全局表能够跟任何一个表进行 JOIN 操作

将字典表或者合乎字典表个性的一些表定义为全局表，则从另外一个方面，很好的解决了数据 JOIN 的难题。通过全局表+基于 E-R 关系的分片策略，Mycat 能够满足 80%以上的企业应用开发

批改 schema.xml 配置文件

<!-- 定义全局字典表 -->

在 dn2 创立 orders_detail 表 重启 Mycat 拜访 Mycat 向 orders_detail 表插入数据

INSERT INTO orders_detail(id,detail,order_id) values(1,'detail1',1);
INSERT INTO orders_detail(id,detail,order_id) VALUES(2,'detail1',2);
INSERT INTO orders_detail(id,detail,order_id) VALUES(3,'detail1',3);
INSERT INTO orders_detail(id,detail,order_id) VALUES(4,'detail1',4);
INSERT INTO orders_detail(id,detail,order_id) VALUES(5,'detail1',5);
INSERT INTO orders_detail(id,detail,order_id) VALUES(6,'detail1',6);

在mycat、dn1、dn2中运行两个表join语句

Select o.*,od.detail from orders o
inner join orders_detail od on o.id=od.order_id;

全局表

在分片的状况下，当业务表因为规模而进行分片当前，业务表与这些从属的字典表之间的关联， 就成了比拟 辣手的问题，思考到字典表具备以下几个个性： ① 变动不频繁 ② 数据量总体变动不大 ③ 数据规模不大，很少有超过数十万条记录

鉴于此，Mycat 定义了一种非凡的表，称之为“全局表”，全局表具备以下个性：

• 全局表的插入、更新操作会实时在所有节点上执行，放弃各个分片的数据一致性
• 全局表的查问操作，只从一个节点获取
• 全局表能够跟任何一个表进行 JOIN 操作

将字典表或者合乎字典表个性的一些表定义为全局表，则从另外一个方面，很好的解决了数据 JOIN 的难题。通过全局表+基于 E-R 关系的分片策略，Mycat 能够满足 80%以上的企业应用开发

批改 schema.xml 配置文件

<!-- 定义全局字典表 -->