16-MyCat

一 Mycat概述

1 什么是Mycat

什么是Mycat

Mycat是数据库中间件，所谓数据库中间件是连接Java应用程序和数据库中间的软件。

为什么要用Mycat

遇到问题：

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

2 Mycat与其他中间件区别

目前的数据库中间件有很多，并从各个维度将其与 Mycat进行对比。

Mycat优势

性能可靠稳定

基于阿里开源的Cobar产品而研发，Cobar的稳定性、可靠性、优秀 的架构和性能以及众多成熟的使用案例使得MYCAT一开始就拥有一 个很好的起点，站在巨人的肩膀上，我们能看到更远。

强大的技术团队

MyCat现在由一支强大的技术团队维护 , 吸引和聚集了一大批业内 大数据和云计算方面的资深工程师、架构师、DBA，优秀的团队保 障了MyCat的稳定高效运行。而且MyCat不依托于任何商业公司， 而且得到大批开源爱好者的支持。

体系完善

MyCat已经形成了一系列的周边产品,比较有名的是 Mycat-web、 Mycat-NIO、Mycat-Balance 等,已经形成了一个比较完整的解决方案,而不仅仅是一个中间件。

3 Mycat应用场景

Mycat还形成了一系列周边产品，比较有名的是 Mycat-web、 Mycat-NIO、Mycat-Balance等，已成为一个比较完整的数据处理解决方案，而不仅仅是中间件。

高可用性与MySQL读写分离

利用Mycat可以轻松实现热备份，当一台服务器停机时，可以由双机或集群中的另一台服务器自动接管其业务，从而在无须人工干预的情况下，保证系统持续提供服务。这个切换动作由Mycat自动完成。

注意：

Mycat 的读写分离及自动切换都依赖于数据库产品的主从数据同步。

100亿大表水平分表、集群并行计算

数据切分是Mycat的核心功能，是指通过某种特定的条件，将存放在同一个数据库中的数据分散存放在多个数据库（主机）中，以达到分散单台设备负载的效果。

数据切分有两种切分模式

• 按照不同的表将数据切分到不同的数据库中，这种切分可以叫作数据的垂直切分。
• 根据表中数据的逻辑关系，将同一个表中的数据按照某种条件拆分到多个数据库中，这种切分叫作数据的水平切分。当数据量超过800万行且需要做分片时，可以利用Mycat实现数据切分。

数据库路由器

Mycat基于MySQL 实例的连接池复用机制，可以让每个应用最大程度地共享一个MySQL实例的所有连接池，让数据库的并发访问能力大大提升。

整合多种数据源

当一个项目需要用到多种数据源如Oracle、MySQL、SQL Server、 PostgreSQL时，可以利用Mycat进行整合，只需访问Mycat 这一个数据源就行。

4 核心概念详解

逻辑库schema

业务开发人员通常在实际应用中并不需要知道中间件的存在，只需要关注数据库，所以数据库中间件可以被当作一个或多个数据库集群构成的逻辑库。

注意：

逻辑库，与MySQL中的Database（数据库）对应，⼀个逻辑库中定义了所包括的Table。

逻辑表table

既然有逻辑库，就会有逻辑表。在分布式数据库中，对于应用来说，读写数据的表就是逻辑表。逻辑表可以分布在一个或多个分片库中，也可以不分片。

注意：
Table：表，即物理数据库中存储的某⼀张表，与传统数据库不同，这⾥的表格需要声明其所存储的逻辑数据节点DataNode。

分片节点DataNode

数据切分后，一个大表被分到不同的分片数据库上面，每个表分片所在的数据库就是分片节点dataNode。

节点主机DataHost

数据切分后，每个分片节点(dataNode)不一定都会独占一台机器， 同一机器上面可以有多个分片数据库，这样一个或多个分片节点 (dataNode)所在的机器就是节点主机(dataHost),为了规避单节点主机并发数限制，尽量将读写压力高的分片节点(dataNode)均衡的放 在不同的节点主机(dataHost)。

用户

MyCat的用户(类似于MySQL的用户，支持多用户）

5 Mycat原理

Mycat 的原理中最重要的一个动词是“拦截”，它拦截了用户发送过来的SQL 语句。

流程：

首先对 SQL 语句做了一些特定的分析：如分片分析、路由分析、读写分离分析、缓存分析等，然后将此 SQL 发往后端的真实数据库，并将返回的结果做适当的处理，最终再返回给用户。

流程示例

• 解析SQL语句涉及的表。
• 查看表的定义，如果表存在分片规则，则获取SQL语句的分片字段。
• 将SQL语句发送到相应的分片去执行。
• 最后处理所有分片返回的数据并返回给客户端。

二 Mycat部署安装

1 MySQL主从复制概述

为什么要主从复制

注意：

MySQL是现在普遍使用的数据库，但是如果宕机了必然会造成数据丢失。为了保证MySQL数据库的可靠性。就要会一些提高可靠性的技术。

如何解决性能问题

生活中有很形象的例子，比如你在自助咖啡厅买咖啡（如果只有一 台咖啡机)

如果有多台咖啡机，很明显大家买咖啡的效率就上去了：

MySQL主从复制原理

原理：

主从复制的原理则是采用binlog文件进行复制，我们都知道 MySQL的数据库会产生一个二进制日志，就是binlog，该日志文件记录了数据的任何修改,所以我们的从机Slave会往主机读取二进制的binlog日志到本机的I/O thread中,然后写入一个Relay log文件中,从机开启一个SQL thread 定时检查Realy log 文件,如果发现有更新立即把更新的内容在本地的数据库上执行。

binlog 日志的三种格式

• Statement：每一条会修改数据的sql都会记录在binlog中
• Row: 仅保存哪条记录被修改
• Mixed: 以上两种的混合使用，一般的语句修改用statement，全表更新使用Row，但是无法使用 @@host name。

二进制日志管理

二进制日志位置

在my.cnf文件中使用 log-bin = 指定；命名规则为 mysqlbin.000000 （后为6位数字）

mysql> show variables like '%log_bin%' ;

查看二进制日志格式

mysql> show variables like '%format%';

查看当前使用的二进制日志文件

mysql> show master status;

2 搭建MySQL主从复制

安装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服务

yum -y install mysql-community-server

遇到报错

Failing package is: mysql-community-libscompat-5.7.37-1.el7.x86_64
 GPG Keys are configured as:
file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

失败原因： GPG对于包的源key的验证没有通过。

解决办法

在yum install 版本后面加上 --nogpgcheck

yum -y install mysql-community-server --nogpgcheck

完成安装，重启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;

开启bin-log日志

修改主服务器Master

修改配置文件：vim /etc/my.cnf
#主服务器唯一ID
server-id=1
#启用二进制日志
log-bin=mysql-bin

修改从服务器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';

注意：

mysql权限管理语法：GRANT 权限 ON 数据库.表 TO 用户名@登录主机 IDENTIFIED BY “密码”

登陆主机：

字段	含义
%	匹配所有主机

权限列表：

权 限	作用范围	作 用
replication slave	服务器	主从复制

一般不用root帐号，“%”表示所有客户端都可能连，只要帐号， 密码正确，此处可用具体客户端IP代替，如192.168.145.226， 加强安全。

查询Master的状态

mysql>show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      430 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

注意：

执行完此步骤后不要再操作主服务器MYSQL，防止主服务器状态值变化。

配置从服务器Slave

mysql>change master to master_host='192.168.66.111',master_user='slave',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=430;

注意： 注意不要断开，430数字前后无单引号。

启动从服务器复制功能

mysql>start slave;

检查从服务器复制功能状态

show slave status \G;

注意：

Slave_IO及Slave_SQL进程必须正常运行，即YES状态，否则都是错误的状态(如：其中一个NO均属错误)。

主从服务器测试

主服务器Mysql，建立数据库，并在这个库中建表插入一条数据

mysql> create database test;
Query OK, 1 row affected (0.00 sec)

mysql> use test;
Database changed

mysql> create table dog(id int(3),name varchar(255));
Query OK, 0 rows affected (0.00 sec)

mysql> insert into dog values(001,'zhuti');
Query OK, 1 row affected (0.00 sec)

从服务器Mysql查询

mysql>show databases;
mysql>use test;
mysql>select * from dog; 

3 基于Docker搭建MySQL主从复制

首先拉取Docker镜像

docker pull mysql:5.7

启动两个容器

#启动Master
docker run -p 3350:3306 --name master -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7
#启动Slave
docker run -p 3340:3306 --name slave -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7

注意：

• –name：为容器指定名称，这里是master
• -p：将容器的指定端口映射到主机的指定端口，这里是将容器的3306端口映射到主机的3306 端口
• -e：设置环境变量，这里是指定root账号的密码为123456
• -d：后台运行容器，并返回容器ID

获取两个容器IP地址

[root@localhost mycat]# docker inspect master
[root@localhost mycat]# docker inspect slave

检验是否启动成功

docker ps -a 显示所有的容器，包括未运行的

注意：

是UP状态，表示正在运行中。

配置Master

进入master容器内部

docker exec -it master /bin/bash

修改mysqld.cnf配置

#切换目录
vim /etc/mysql/mysql.conf.d/mysqld.cnf
#修改配置文件加入
[mysqld]
## 同一局域网内注意要唯一
server-id=100  
## 开启二进制日志功能，可以随便取（关键）
log-bin=mysql-bin

注意：

vi my.cnf 对my.cnf进行编辑。此时会报出 bash: vi: command not found ， 需要我们在docker容器内部自行安装vim。使用 apt-get install vim 命令安装vim

如果报错：

Reading package lists… Done Building dependency tree Reading state information… Done E: Unable to locate package vim

更新apt-get

apt-get update

安装vim

apt-get install vim

CentOS5.4

# apt-get install vim

bash: apt-get: command not found

解答

CentOS的软件安装工具不是apt-get 是yum
# yum -y install vim

重启mysql服务

service mysql restart
docker start master

在Master数据库创建数据同步用户

mysql> GRANT replication SLAVE ON *.* TO 'slave' @'%' IDENTIFIED BY '123456';

配置Slave

和配置Master(主)一样，在Slave配置文件mysqld.cnf中添加如下配置

#切换目录
vim /etc/mysql/mysql.conf.d/mysqld.cnf
[mysqld]
## 设置server_id,注意要唯一
server-id=101  
## 开启二进制日志功能，以备Slave作为其它Slave的Master时使用
log-bin=mysql-slave-bin  
## relay_log配置中继日志
relay_log=edu-mysql-relay-bin

注意：

配置完成后也需要重启mysql服务和docker容器，操作和配置 Master(主)一致。

连接接Master(主)和Slave(从)

在Master进入mysql

show master status;

在Slave 中进入 mysql

mysql>change master to master_host='172.17.0.2',master_user='slave',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=443;

参数：

• master_host: Master 的IP地址
• master_user: 在 Master 中授权的用于数据同步的用户
• master_password: 同步数据的用户的密码
• master_port: Master 的数据库的端口号
• master_log_file: 指定 Slave 从哪个日志文件开始复制数据，即上文中提到的 File 字段的值
• master_log_pos: 从哪个 Position 开始读，即上文中提到的 Position 字段的值
• master_connect_retry: 当重新建立主从连接时，如果连接失败，重试的时间间隔，单位是秒，默认是60秒。

在Slave中开启主从复制

mysql> start slave;

在 Slave 的 MySQL 终端执行查看主从同步状态

show slave status \G;

4 Mycat安装以及管理命令详解

由于mycat是java语言编写，所以得先安装jdk

解压jdk安装包

tar -zxvf jdk-8u201-linux-x64.tar.gz -C /usr/local

配置环境变量

vim /etc/profile
底部加入如下配置
export JAVA_HOME=/usr/local/jdk
export PATH=$PATH:$JAVA_HOME/bin

生效环境变量

source /etc/profile

下载解压Mycat安装包

MyCAT 有提供编译好的安装包，支持 windows、Linux、Mac、 Solaris 等系统上安装与运行。

tar -zxvf Mycat-server-1.6.7.1-release-20190627191042-linux.tar.gz -C /usr/local

将MyCat配置到环境变量中

vim /etc/profile
#增加如下配置
export MYCAT_HOME=/usr/local/mycat
export PATH=$PATH:$MYCAT_HOME/bin

MyCat常用操作命令

mycat start           #启动MyCat
mycat stop           #停止MyCat
mycat console         #前台运行MyCat带控制台输出
mycat restart         #重启MyCat
mycat pause           #暂停MyCat
mycat status         #查看启动状态

使用MySQL命令进行MyCat的连接

Mycat 提供类似数据库的管理监控方式，可以通过MySQL命令行登录管理端口(9066）执行相应的SQL语句进行管理，也可以通过 JDBC方式进行远程连接管理。

管理端口

9066用于查看MyCat的运行状态

mysql -uroot -p123456 -h 192.168.66.111 -P9066 -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.66.111 -P8066 -DTESTDB

MyCat的连接用户名相关信息在配置文件 /usr/local/mycat/conf/server.xml 中查 看编辑

<user name="root">
        <property name="password">123456</property>
        <property name="schemas">TESTDB</property>
</user>

Show命令

-- 查看所有的可用命令
show @@help;  
-- 查看所有的逻辑数据库
show @@database;
-- 查看所有的数据节点
show @@datanode;
-- 查看所有的数据源
show @@datasource;

5 核心配置文件及目录结构

目录结构

说明：

• bin：可执行命令
• conf：配置文件
• lib：存放jar包
• logs：日志信息
• tmlogs：临时日志
• version.txt：版本信息

三个配置文件

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

三 Mycat高级特性

1 读写分离概述

什么是读写分离

读写分离，基本的原理是让主数据库处理事务性增、改、删操作， 而从数据库处理查询操作。

为什么使用读写分离

从集中到分布，最基本的一个需求不是数据存储的瓶颈，而是在于计算的瓶颈，即 SQL 查询的瓶颈，我们知道，正常情况下，Insert SQL 就是几十个毫秒的时间内写入完成，而系统中的大多数 Select SQL 则要几秒到几分钟才能有结果，很多复杂的 SQL，其消耗服务器 CPU 的能力超强，不亚于死循环的威力。

读写分离方案

MyCat的读写分离是建立在MySQL主从复制基础之上实现的，所以必须先搭建MySQL的主从复制。数据库读写分离对于大型系统或者访问量很高的互联网应用来说，是必不可少的⼀个重要功能。

注意：

Mycat实现的读写分离和自动切换机制，需要MySQL的主从复制机制配合。

Mysql 主从复制的常用拓扑结构

一主一从

注意：

最基础的复制结构，用来分担之前单台数据库服务器的压力， 可以进行读写分离。

一主多从

注意：

一台 Slave 承受不住读请求压力时，可以添加多台，进行负载均衡，分散读压力。

双主复制

注意：

双主结构就是用来解决这个问题的，互相将对方作为自己的 Master，自己作为对方的 Slave 来进行复制，但对外来讲，还是一个主和一个从。

级联复制

注意：

级联结构就是通过减少直接从属于 Master 的 Slave 数量，减轻 Master 的压力，分散复制请求，从而提高整体的复制效率。

双主级联

注意：

Mysql 的复制结构有很多种方式，复制的最大问题是数据延时，选择复制结构时需要根据自己的具体情况，并评估好目标结构的延时对系统的影响。

2 搭建读写分离

修改配置文件schema.xml

<schema name="TESTDB" checkSQLschema="false" dataNode="db_node" sqlMaxLimit="100">
</schema>

参数：

• schema：逻辑库
• name ：逻辑库名称
• sqlMaxLimit：一次取多少条数据
• table：逻辑表
• dataNode：分片节点 对应 dataNode标签
• rule：分片规则 对应 rule.xml
• primaryKey: 分片主键 可缓存

分片配置

<dataNode name="db_node" dataHost="db_host" database="db_test" />

参数：

• name：分片名字
• dataHost：节点主机
• database：真实的数据库

配置读写分离

<dataHost name="host1" maxCon="1000" minCon="10" balance="2"
writeType="0" dbType="mysql" dbDriver="native" switchType="1"
slaveThreshold="100">
    <writeHost host="" url="" user="" password=""></writeHost>
</dataHost>

参数：

• dataHost：数据主机（节点主机）
• dbType：数据库驱动native:MySQL JDBC: oracle SQLServer
• switchType： 是否主动读 1

Balance参数设置：

• balance=“0”, 所有读操作都发送到当前可⽤的writeHost上。
• balance=“1”，所有读操作都随机的发送到readHost。
• balance=“2”，所有读操作都随机的在writeHost、readhost上分发

WriteType参数设置：

• writeType=“0”, 所有写操作都发送到可⽤的writeHost上。
• writeType=“1”，所有写操作都随机的发送到readHost。
• writeType=“2”，所有写操作都随机的在writeHost、readhost分上发。

witchType参数设置：

• switchType=“1”, 主从自动切换
• switchType=“2”，从机延时超过slaveThreshold值时切换为主读

启动Mycat

mycat restart

配置文件

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
   <!-- 数据库配置 与server.xml 中的数据库对应 -->
 <schema name="TESTDB" checkSQLschema="false" dataNode="db_node" sqlMaxLimit="100">  </schema>

   <!-- 分片配置 -->
   <!-- dataNode的database对应物理数据库（实际存在） -->
   <!-- dataNode里设置的database为我们做主从同步的数据库名 -->
 <dataNode name="db_node" dataHost="db_host" database="test" />

   <!-- 物理数据库配置 -->
 <dataHost name="db_host" maxCon="1000" minCon="10" balance="1"
 writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
 	 <heartbeat>select user()</heartbeat>
     <writeHost host="hostM1" url="192.168.66.111:3350" user="root" password="123456">
        <readHost host="hostS2" url="192.168.66.111:3340" user="root" password="123456" />
     </writeHost>
 </dataHost>

</mycat:schema>

验证读写分离

在mycat中插入

insert into dog values(1,@@hostname);

注意： @@代表系统变量。

在mycat查询

select * from dog;

结果为writeHost的主机名

3 MySQL双主双从原理

一主一从

注意：

是最基础的复制结构，用来分担之前单台数据库服务器的压力，可以进行读写分离。

一主多从

问题：

一台 Slave 承受不住读请求压力时，可以添加多台，进行负载均衡，分散读压力。

如何解决

介绍：

互相将对方作为自己的 Master，自己作为对方的 Slave 来进行复制，但对外来讲，还是一个主和一个从。

4 搭建双主双从

环境准备

编号	角色	IP地址	端口	机器名
1	Master1	192.168.66.111	3350	node-1
2	Slave1	192.168.66.111	3360	node-2
3	Master2	192.168.66.111	3370	node-3
4	Slave2	192.168.66.111	3380	node-4

创建docker容器

#启动第一台
docker run -d -p 3350:3306 -e MYSQL_ROOT_PASSWORD=123456 --name=master1 mysql:5.7
#启动第二台
docker run -d -p 3360:3306 -e MYSQL_ROOT_PASSWORD=123456 --name=slave1 mysql:5.7
#启动第三台
docker run -d -p 3370:3306 -e MYSQL_ROOT_PASSWORD=123456 --name=master2 mysql:5.7
#启动第四台
docker run -d -p 3380:3306 -e MYSQL_ROOT_PASSWORD=123456 --name=slave2 mysql:5.7

修改容器内MySQL配置文件

vim /etc/mysql/mysql.conf.d/mysqld.cnf

1、修改master1配置文件

[mysqld]
#主服务器唯一ID
server-id=1
#启用二进制日志
log-bin=mysql-bin

# 设置不要复制的数据库(可设置多个)
binlog-ignore-db=mysql
binlog-ignore-db=information_schema

#设置需要复制的数据库
binlog-do-db=test

#设置logbin格式
binlog_format=STATEMENT

# 在作为从数据库的时候，有写入操作也要更新二进制日志文件
# 由于主机宕机后会变成备用主机，所以有写操作的时候也需要更新日志
log-slave-updates

#指自增字段的起始值，其默认值是1，取值范围是1 .. 65535
auto-increment-increment=2

# 指字段一次递增多少，他的取值范围是1 .. 65535
auto-increment-offset=1

2、修改master2配置文件

[mysqld]
#主服务器唯一ID
server-id=3 
#启用二进制日志
log-bin=mysql-bin

# 设置不要复制的数据库(可设置多个)
binlog-ignore-db=mysql
binlog-ignore-db=information_schema

#设置需要复制的数据库
binlog-do-db=test

#设置logbin格式
binlog_format=STATEMENT

# 在作为从数据库的时候，有写入操作也要更新二进制日志文件
# 由于主机宕机后会变成备用主机，所以有写操作的时候也需要更新日志
log-slave-updates

#指自增字段的起始值，其默认值是1，取值范围是1 .. 65535
auto-increment-increment=2

# 指字段一次递增多少，他的取值范围是1 .. 65535
auto-increment-offset=1

3、修改slave1配置文件

[mysqld]
#从服务器唯一ID
server-id=2
#启用中继日志
relay-log=mysql-relay

4、修改slave2配置文件

[mysqld]
#从服务器唯一ID
server-id=4 
#启用中继日志
relay-log=mysql-relay

双主双从重启服务

service mysql restart
docker start master

配置数据库

1、分别在两个主库中执行创建从库连接账号命令

GRANT replication SLAVE ON *.* TO 'slave' @'%' IDENTIFIED BY '123456';

2、查看两主库的master状态

show master status;

两个从库连接到主库

# 在slave1中执行
change master to
master_host='192.168.66.111',
master_user='slave',
master_port=3350,
master_password='123456',
master_log_file='mysql-bin.000001',
master_log_pos=443;

# 在slave2中执行
change master to
master_host='192.168.66.111',
master_user='slave',
master_port=3370,
master_password='123456',
master_log_file='mysql-bin.000001',
master_log_pos=443;

参数：

• master_host：这里的ip就是mysql所在服务器对应的ip
• master_user：就是在第一步配置的账号
• master_port：mysql的端口
• master_password：配置的密码
• master_log_file：file参数
• master_log_pos：Position参数

两个从库启动复制功能

start slave;

查看连接状态

show slave status \G;

两个主库再互相成为对方的从库

# 在master1上执行
change master to
master_host = '192.168.66.111',
master_user = 'slave',
master_password = '123456',
master_port = 3370,
master_log_file = 'mysql-bin.000001',
master_log_pos = 443;

# 在master2上执行
change master to
master_host = '192.168.66.111',
master_user = 'slave',
master_password = '123456',
master_port = 3350,
master_log_file = 'mysql-bin.000001',
master_log_pos = 443;

两个主库启动复制功能

start slave;

查看连接状态

show slave status \G;

双主双从配置MyCat

vim schema.xml

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

   <!-- 数据库配置 与server.xml 中的数据库对应 -->
 <schema name="TESTDB" checkSQLschema="false" dataNode="db_node" sqlMaxLimit="100">  </schema>

   <!-- 分片配置 -->
 <dataNode name="db_node" dataHost="db_host" database="test" />

   <!-- 物理数据库配置 -->
 <dataHost name="db_host" maxCon="1000" minCon="10" balance="1"  writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
     <heartbeat>select user()</heartbeat>
     <writeHost host="hostM1" url="192.168.66.111:3350" user="root" password="123456">
        <readHost host="hostS1" url="192.168.66.111:3360" user="root" password="123456" />
     </writeHost>
     <writeHost host="hostM2" url="192.168.66.111:3370" user="root" password="123456">
        <readHost host="hostS2" url="192.168.66.111:3380" user="root" password="123456" />
     </writeHost>

 </dataHost>

</mycat:schema>

注意：

• writeType=“0”：所有写操作发送到配置的第一个 writeHost，第一个挂了切到还生存的第二个writeHost，重新启动后已切换后的为准，切换记录在配置文件中:dnindex.properties.
• writeType=“1”：所有写操作都随机的发送到配置的 writeHost，1.5 以后废弃不推荐。
• switchType=“-1” ：表示不自动切换 mysql 实例
• switchType=“1” ：默认值，自动切换

四 Mycat分片技术

1 垂直拆分

前沿

目前很多互联网系统都存在单表数据量过大的问题，这就降低了查询速度，影响了客户体验。为了提高查询速度，我们可以优化sql语句，优化表结构和索引，不过对那些百万级千万级的数据库表，即便是优化过后，查询速度还是满足不了要求。

垂直分割

垂直拆分是指数据表列的拆分，把一张列比较多的表拆分为多张表。表的记录并不多，但是字段却很长，表占用空间很大，检索表的时候需要执行大量的IO，严重降低了性能。这时需要把大的字段拆分到另一个表，并且该表与原表是一对一的关系。

拆分原则：

• 把不常用的字段单独放在一张表
• 把text，blob等大字段拆分出来放在附表中
• 经常组合查询的列放在一张表中

举个例子

拆分思路：

如果我们只想查询id为8的学生的分数：select 分数 from 答题表 where id = 8;虽然知识查询分数，但是题目和回答这两个大字段也是要被扫描的，很消耗性能。但是我们只关心分数，并不想查询题目和回答。这就可以使用垂直分割。

实现分库

环境准备

编号	IP地址	端口	机器名
1	192.168.66.111	3350	node-1
2	192.168.66.111	3360	node-2

创建docker容器

#启动第一台
docker run -d -p 3350:3306 -e MYSQL_ROOT_PASSWORD=123456 --name=node-1 mysql:5.7
#启动第二台
docker run -d -p 3360:3306 -e MYSQL_ROOT_PASSWORD=123456 --name=node-2 mysql:5.7

修改schema.xml配置文件

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
       
 <schema name="TESTDB" checkSQLschema="false" dataNode="dn1" sqlMaxLimit="100">
    <table name="customer" dataNode="dn2"/>
 </schema>

 <dataNode name="dn1" dataHost="host1" database="orders" />
 <dataNode name="dn2" dataHost="host2" database="orders" />

 <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.66.111:3350" user="root" password="123456">
     </writeHost>
 </dataHost>
  <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="hostM2" url="192.168.66.111:3360" user="root" password="123456">
     </writeHost>
 </dataHost>

</mycat:schema>

新增两个空白库

分库操作不是在原来的老数据库上进行操作，需要准备两台机器分别安装新的数据库

#在数据节点 dn1、dn2 上分别创建数据库 orders
CREATE DATABASE orders;

启动Mycat

mycat start

Mycat进行分库

创建表

mysql -uroot -p123456 -h 192.168.66.111 -P8066

mysql> use TESTDB;

# 使用 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,
 order_id int,
 detail varchar(200),
  primary key(id)
);
create table dict_order_type(
 id int auto_increment,
 order_type varchar(200),
  primary key(id)
);

查看结果

在node-1中

mysql> use orders;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+------------------+
| Tables_in_orders |
+------------------+
| dict_order_type  |
| orders           |
| orders_detail    |
+------------------+
3 rows in set (0.00 sec)

在node-2中

mysql> use orders;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+------------------+
| Tables_in_orders |
+------------------+
| customer         |
+------------------+
1 row in set (0.00 sec)

2 水平拆分

前言

上面谈到垂直切分只是把表按模块划分到不同数据库，但没有解决单表大数据量的问题。

水平拆分

相对于垂直拆分，水平拆分不是将表做分类，而是按照某个字段的某种规则来分散到多个库之中，每个表中包含一部分数据。

理解：

我们可以将数据的水平切分理解为是按照数据行的切分，就是将表中的某些行切分到一个数据库，而另外的某些行又切分到其他的数据库中。

实现分表

1、 选择要拆分的表

MySQL 单表存储数据条数是有瓶颈的，单表达到 1000 万条数据就达到了瓶颈，会影响查询效率，需要进行水平拆分（分表）进行优化。

2、分表字段

以 orders 表为例，可以根据不同字段进行分表

编号	分表字段	效果
1	id	查询订单注重时效，历史订单被查询的次数少，如此分片会造成一个节点访问多，一个访问少，不平均。
2	customer_id	根据客户 id 去分，两个节点访问平均

3、 修改配置文件 schema.xml

为 orders 表设置数据节点为 dn1、dn2，并指定分片规则为 mod_rule（自定义的名字）

<table name="orders" dataNode="dn1,dn2" rule="mod_rule" ></table>

4、修改配置文件rule.xml

分片规则：

在 rule 配置文件里新增分片规则 mod_rule，并指定规则适用字段为 customer_id， 还有选择分片算法 mod-long（对字段求模运算），customer_id 对两个节点求模，根据结果分片配置算法 mod-long 参数 count 为 2，两个节点。

<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">
  <!-- 物理主机节点数量 -->
  <property name="count">2</property>
</function>

5、 在数据节点dn2上建orders表（注意：承接上文中的环境，因为dn1中已经有orders表）

create table orders(
 id int auto_increment,
 order_type int,
 customer_id int,
 amount decimal(10,2),
  primary key(id)
);

6、 重启Mycat，让配置生效

mycat restart

7、 访问Mycat实现分片

mysql -uroot -p123456 -h 192.168.66.111 -P8066

mysql> use TESTDB;

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);

查看结果

在node-1中

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 rows in set (0.00 sec)

在node-2中

mysql> select * from orders;
+----+------------+-------------+-----------+
| id | order_type | customer_id | amount    |
+----+------------+-------------+-----------+
|  3 |        101 |         101 | 120000.00 |
|  4 |        101 |         101 | 103000.00 |
|  5 |        102 |         101 | 100400.00 |
+----+------------+-------------+-----------+
3 rows in set (0.00 sec)

3 ER表

分片 join

遇到问题：

orders表分片了,那和他相关的orders_detail 表未分片, join联查的时候, node1正常查询出结果, node2上由于没有 orders_detail 表,则报错, 最后聚合结果肯定也是错误的。

ER 表

将子表的存储位置依赖于主表，并且物理上紧邻存放因此彻底解决了JION的效率和性能问题，根据这一思路，提出了基于E-R关系的数据分片策略，子表的记录与所关联的父表记录存放在同一个数据分片上。

修改配置文件schema.xml

<table name="orders" dataNode="dn1,dn2" rule="mod_rule" >
    <childTable name="orders_detail" primaryKey="id" joinKey="order_id" parentKey="id" />
</table>

参数：

• childTable：子表

node2上没有订单详情表创建一下

create table orders_detail(
id int auto_increment,
order_id int,
detail varchar(200),
primary key(id)
);

重启Mycat服务

mycat restart

Mycat服务添加数据

mysql -uroot -p123456 -h 192.168.66.111 -P8066

mysql> use TESTDB;

insert into orders_detail(detail, order_id)
values('detail1',1);
insert into orders_detail(detail, order_id)
values('detail1',2);
insert into orders_detail(detail, order_id)
values('detail1',3);
insert into orders_detail(detail, order_id)
values('detail1',4);
insert into orders_detail(detail, order_id)
values('detail1',5);

测试数据

select a.*,b.detail from orders a join orders_detail b on a.id=b.order_id;

4 全局表

全局表描述

在分片的情况下，当业务表因为规模而进行分片以后，业务表与这些附属的字典表之间的关联，就成了比较棘手的问题，考虑到字典表具有以下几个特性：

• 变动不频繁
• 数据量总体变化不大
• 数据规模不大，很少有超过数十万条记录。

全局表特征

• 全局表的插入更新操作会实时在所有节点上执行,保持各个分片的数据一致。
• 全局表的查询操作,只从一个节点获取。
• 全局表可以和任何一个表进行 JOIN 操作。

注意：

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

配置全局表

<!--全局表配置比较简单，不用写 Rule 规则，修改schema.xml，如下配置即可-->
<table name="dict_order_type" dataNode="dn1,dn2" type="global"></table>

在dn2创建 dict_order_type 表

#订单类型字典表
create table dict_order_type(
 id int auto_increment,
 order_type varchar(200),
  primary key(id)
);

重启Mycat

mycat restart

Mycat上添加数据

mysql -uroot -p123456 -h 192.168.66.111 -P8066

mysql> use TESTDB;

insert into
dict_order_type(id,order_type)values(101,'type1');
insert into
dict_order_type(id,order_type)values(102,'type2');

测试数据

select * from dict_order_type;

五 Mycat分片规则

1 取模

实现方式

取模分片就是根据数据表的某一个字段，通常是某一个整数型的字段，对其进行十进制的求模运算，将运算结果作为Mycat的路由结果。

注意：

• 优点：这种策略可以很好的分散数据库写的压力。

• 缺点：出现了范围查询，就需要MyCAT去合并结果，当数据量偏高的时候，这种跨库查询 +合并结果消耗的时间有可能会增加很多，尤其是还出现了order by的时候。

tableRule 标签

这个标签定义表规则。

<tableRule name="mod-long">
  <rule>
      <columns>user_id</columns>
      <algorithm>mod-long</algorithm>
  </rule>
</tableRule>

参数：

• name ：属性指定唯一的名字，用于标识不同的表规则。 内嵌的 rule 标签则指定对物理表中的哪一列进行拆分和使用什么路由算法。
• columns ：内指定要拆分的列名字。
• algorithm：使用 function 标签中的 name 属性。连接表规则和具体路由算法。当然，多个表规则可以连接到同一个路由算法上。

function标签

定义具体路由算法

<function name="mod-long" class="io.mycat.route.function.PartitionByMod">
 <!-- how many data nodes -->
 <property name="count">2</property>
</function>

注意：

• name 指定算法的名字。
• class 制定路由算法具体的类名字。
• property 为具体算法需要用到的一些属性。
• count：表示需要取模的最大值，将数据分成该配置的切片。

2 分片枚举

实现原理

有些业务需要按照省份或区县来做保存，这类业务使用本条规则。

实现过程

在这里，需定义三个值，规则均是在rule.xml中定义。

• tableRule
• function
• mapFile

创建示例表

#订单归属区域信息表
CREATE TABLE orders_ware_info(
  `id` INT AUTO_INCREMENT comment '编号',
  `order_id` INT comment '订单编号',
  `address` VARCHAR(200) comment '地址',
  `areacode` VARCHAR(20) comment '区域编号',
  PRIMARY KEY(id)
);

修改schema.xml配置文件

<table name="orders_ware_info" dataNode="dn1,dn2" rule="sharding-by-intfile"></table>

定义tableRule

<tableRule name="sharding-by-intfile">
    <rule>
      <columns>areacode</columns>
      <algorithm>hash-int</algorithm>
    </rule>
</tableRule>

注意：

其中，sharding-by-intfile是规则名，会在schema.xml中 用到。columns指的是对省份进行分片。algorithm是算法名， 该算法必须在function中定义。

定义funtion

<function name="hash-int" class="io.mycat.route.function.PartitionByFileMap">
    <property name="mapFile">partition-hashint.txt</property>
    <property name="type">1</property>
    <property name="defaultNode">0</property>
  </function>

注意：

• mapFile：指的是配置文件名
• type：默认值为0，0表示Integer，非零表示String。因为我接下来的测试是基于省份分片， 所以需type指定为1。
• defaultNode 默认节点：小于0表示不设置默认节点，大于等于0表示设置默认节点 默认节点的作用：枚举分片时，如果碰到不识别的枚举值，就让它路由到默认节点。

修改partition-hash-int.txt配置文件

110=0
120=1

注意：

其中，110会被分发到第一个节点中，120分发被第二个节点中。

重启Mycat

mycat restart

插入数据

mysql -uroot -p123456 -h 192.168.66.111 -P8066

mysql> use TESTDB;

INSERT INTO orders_ware_info(id,order_id,address,areacode) VALUES (1,1,'beijing','110');
INSERT INTO orders_ware_info(id,order_id,address,areacode) VALUES (2,2,'tianjing','120');

3 范围约定

实现原理

此分片适用于，提前规划好分片字段某个范围属于哪个分片。

举个例子

比如将id在0-500W的数据分片在第一个节点上面，将id在500W1000W的数据分片在第二个节点上，依次类推下去。

优缺点：

• 优点：适用于想明确知道某个分片字段的某个范围具体在哪一个节点；
• 缺点：如果短时间内有大量的批量插入操作，那么某个分片节点可能一下子会承受比较大的 数据库压力，而别的分片节点此时可能处于闲置状态，无法利用其它节点进行分担压力（热点数据问题);

实现过程

创建示例表

#支付信息表
CREATE TABLE payment_info
(
`id` INT AUTO_INCREMENT comment '编号',
`order_id` INT comment '订单编号',
`payment_status` INT comment '支付状态',
PRIMARY KEY(id)
);

修改schema.xml配置文件

<table name="payment_info" dataNode="dn1,dn2" rule="auto-sharding-long" ></table>

定义tableRule

<tableRule name="auto-sharding-long">
  <rule>
      <columns>order_id</columns>
      <algorithm>rang-long</algorithm>
  </rule>
</tableRule>

注意：

其中，auto_sharding_long是规则名，会在schema.xml中用到。columns指的是对订单id进行分片。algorithm是算法名， 该算法必须在function中定义。

定义function

<function name="rang-long" class="io.mycat.route.function.AutoPartitionByLong">
  <property name="mapFile">autopartitionlong.txt</property>
  <property name="defaultNode">0</property>
</function>

注意：

• mapFile：指的是配置文件名
• type：默认值为0，0表示Integer，非零表示String。
• defaultNode 默认节点：小于0表示不设置默认节点，大于等于0表示设置默认节点

修改autopartition-long.txt配置文件

0-102=0
103-200=1

重启Mycat

mycat restart

插入数据

mysql -uroot -p123456 -h 192.168.66.111 -P8066

mysql> use TESTDB;

INSERT INTO payment_info
(id,order_id,payment_status) VALUES (1,101,0);
INSERT INTO payment_info
(id,order_id,payment_status) VALUES (2,102,1);
INSERT INTO payment_info 
(id,order_id,payment_status) VALUES (3,103,0);
INSERT INTO payment_info
(id,order_id,payment_status) VALUES(4,104,1);

4 按日期（天）分片

实现原理

此规则为按天分片，设定时间格式、范围。

实现过程

创建示例表

#用户信息表
create table login_info(
 id int auto_increment comment '编号',
 user_id int comment '用户编号',
 login_date date comment '登录时间',
  primary key(id)
);

修改schema.xml配置文件

<table name="login_info" dataNode="dn1,dn2" rule="sharding-by-date" ></table>

修改rule.xml配置文件

<tableRule name="sharding-by-date">
    <rule>
        <columns>login_date</columns>
        <algorithm>shardingByDate</algorithm>
    </rule>
</tableRule>

参数:

• columns：分片字段，
• algorithm：分片函数

定义function

<function name="shardingByDate" class="io.mycat.route.function.PartitionByDate">
    <property name="dateFormat">yyyy-MM-dd</property>
    <property name="sBeginDate">2040-01-01</property>
    <property name="sEndDate">2040-01-04</property>
    <property name="sPartionDay">2</property>
</function>

参数：

• dateFormat ：日期格式
• sBeginDate ：开始日期
• sEndDate：结束日期,则代表数据达到了这个日期的分片后循环从开始分片插入
• sPartionDay ：分区天数，即默认从开始日期算起，分隔 2 天一个分区

重启Mycat

mycat restart

插入数据

mysql -uroot -p123456 -h 192.168.66.111 -P8066

mysql> use TESTDB;

insert into login_info(id,user_id,login_date)
values(1,101,'2040-01-01');
insert into login_info(id,user_id,login_date)
values(2,102,'2040-01-02');
insert into login_info(id,user_id,login_date)
values(3,103,'2040-01-03');
insert into login_info(id,user_id,login_date)
values(4,104,'2040-01-04');
insert into login_info(id,user_id,login_date)
values(5,105,'2040-01-05');
insert into login_info(id,user_id,login_date)
values(6,106,'2040-01-06');

5 全局序列

在实现分库分表的情况下，数据库自增主键已无法保证全局唯一。

解决方案

本地文件

此方式Mycat将sequence配置到文件中，当使用到 sequence中的配置后，Mycat会更下classpath中的 sequence_conf.properties 文件中sequence当前的值。

注意：

• 优点：本地加载，读取速度较快
• 缺点：抗风险能力差，Mycat 所在主机宕机后，无法读取本地文件。

本地时间戳方式

全局序列ID=64位二进制 (42(毫秒)+5(机器 ID)+5(业务编码)+12(重 复累加) 换算成十进制为18位数的long类型，每毫秒可以并发12位 二进制的累加。

**优缺点： **

• 优点：配置简单
• 缺点：18位ID过长

数据库方式

利用数据库一个表来进行计数累加。但是并不是每次生成序列都读写数据库，这样效率太低。Mycat 会预加载一部分号段到 Mycat 的内存中，这样大部分读写序列都是在内存中完成的。如果内存中的号段用完了 Mycat 会再向数据库要一次。

原理：

在数据库中建立一张表，存放 sequence 名称(name)， sequence 当前值(current_value)，步长(increment int 类型每次读取多少个 sequence，假设为 K)等信息；

数据库解决全局序列

修改Mycat配置文件server.xml

# 全局序列类型：0-本地文件，1-数据库方式，2-时间戳方式。此处应该修改成1。
<property name="sequnceHandlerType">1</property>

修改Mycat配置文件schema.xml

<table name="test" primaryKey="id" autoIncrement="true" dataNode="dn1,dn2" rule="mod-long"/>
<table name="mycat_sequence" primaryKey="name" dataNode="dn2"/>

修改Mycat 配置文件 sequence_db_conf.properties

最下面添加MYCAT=dn2

GLOBAL=dn1
COMPANY=dn1
CUSTOMER=dn1
ORDERS=dn1
MYCAT=dn2

在dn2节点的orders数据库中添加 MYCAT_SEQUENCE表

DROP TABLE IF EXISTS MYCAT_SEQUENCE;

CREATE TABLE MYCAT_SEQUENCE (
    name VARCHAR(50) NOT NULL,
    current_value INT NOT NULL,
    increment INT NOT NULL DEFAULT 100, 
    PRIMARY KEY(name))
ENGINE=InnoDB;

MYCAT_SEQUENCE 表插入sequence初始记录

INSERT INTO MYCAT_SEQUENCE(name,current_value,increment) VALUES ('mycat', -99, 100);

注意：

代表插入了一个名为mycat的sequence，当前值为-99，步长为 100。

创建全局序列所需存储过程

#获取当前sequence的值
DELIMITER $$
CREATE FUNCTION mycat_seq_currval(seq_name VARCHAR(50)) 
RETURNS VARCHAR(64)
DETERMINISTIC
BEGIN
DECLARE retval VARCHAR(64);
SET retval="-999999999,null";
SELECT CONCAT(CAST(current_value AS CHAR),",",CAST(increment AS CHAR)) 
INTO retval 
FROM MYCAT_SEQUENCE 
WHERE NAME = seq_name;
RETURN retval;
END $$
DELIMITER ;

#设置sequence值
DELIMITER $$
CREATE FUNCTION mycat_seq_setval(seq_name VARCHAR(50),VALUE INTEGER) 
RETURNS VARCHAR(64)
DETERMINISTIC
BEGIN
UPDATE MYCAT_SEQUENCE
SET current_value = VALUE
WHERE NAME = seq_name;
RETURN mycat_seq_currval(seq_name);
END $$
DELIMITER ;

#获取下一个sequence值
DELIMITER $$
CREATE FUNCTION mycat_seq_nextval(seq_name VARCHAR(50)) 
RETURNS VARCHAR(64)
DETERMINISTIC
BEGIN
UPDATE MYCAT_SEQUENCE
SET current_value = current_value + increment 
WHERE NAME = seq_name;
RETURN mycat_seq_currval(seq_name);
END $$
DELIMITER ;

重启Mycat

mycat restart

向dn1,dn2添加test表

create table test(id int,name varchar(10));

在Mycat中向test表中添加测试数据

mysql -uroot -p123456 -h 192.168.66.111 -P8066

mysql> use TESTDB;

insert into test(id,name) values(next value for MYCATSEQ_MYCAT,(select database()));

查询数据验证

SELECT * FROM test order by id asc;

六 Mycat高可用

1 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, 继续提供服务, 从而保证了整个集群的高可用。

2 安装配置HAProxy

安装配置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     4000					#最大连接数
    user        haproxy					#haproxy的运行用户
    group       haproxy					#haproxy的运行用户的所属组
    daemon								 #守护进程的方式在后台工作

    # turn on stats unix socket
    stats socket /var/lib/haproxy/stats

注意：

全局配置，通常是一些进程级别的配置，与操作系统相关。

default

defaults
    mode                    http				#默认使用的七层协议，也可以是tcp四层协议，如果配置为health，则表示健康检查，返回ok
    log                     global
    option                  httplog				#详细记录tcp日志
    option                  dontlognull			#不记录健康检查的日志信息
    option http-server-close
    option forwardfor       except 127.0.0.0/8
    option                  redispatch
    retries                 3					#重试次数为3次，失败3次以后则表示服务不可用
    timeout http-request    10s					#http请求超时时间，客户端建立连接5s但不请求数据的时候，关闭客户端连接
    timeout queue           1m					#等待最大时间，表示等待最大时长为1s
    timeout connect         10s					#连接超时时间，表示客户端请求转发至服务器所等待的时长为10s
    timeout client          1m					#客户端超时时间，表示客户端非活跃状态的时间为1min
    timeout server          1m					#服务器超时时间，表示客户端与服务器建立连接后，等待服务器的超时时间为1min
    timeout http-keep-alive 10s					#持久连接超时时间，表示保持连接的超时时长为10s
    timeout check           10s					#心跳检测超时时间，表示健康状态监测时的超时时间为10s
    maxconn                 3000

参数：

默认参数配置，主要是涉及的公共配置，在 defaults 中一次性添加。 frontend 、 backend 、 listen 未配置时，都可以默认 defaults 中的参数配置。若配置了，会覆盖。

frontend & backend

frontend  main *:5000
    acl url_static       path_beg       -i /static /images /javascript /stylesheets
    acl url_static       path_end       -i .jpg .gif .png .css .js

    use_backend static          if url_static
    default_backend             app

backend static
    balance     roundrobin
    server      static 127.0.0.1:4331 check

backend app
    balance     roundrobin
    server  app1 127.0.0.1:5001 check
    server  app2 127.0.0.1:5002 check
    server  app3 127.0.0.1:5003 check
    server  app4 127.0.0.1:5004 check

参数：

• frontend 可以看作是前端接收请求的部分，内部指定后端；
• backend 可以看作是后端服务接收请求的部分；

listen

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.111:8066 check inter 10s
       server mycat_2 192.168.66.112: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.cfg

查看HAProxy进程

ps -ef|grep haproxy

打开浏览器访问

http://192.168.66.111:7777/admin
在弹出框输入用户名：
admin密码：123123

验证负载均衡，通过HAProxy访问Mycat

mysql -uroot -p123456 -h 192.168.66.111 -P 48066

3 安装配置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 {
 router_id baizhan
}
vrrp_script chk_haproxy {
 script "/etc/keepalived/haproxy_check.sh"
 interval 2
 weight 2
}
vrrp_instance VI_1 {
 state MASTER
 interface ens33
 virtual_router_id 35
 priority 120
 nopreempt
 advert_int 1
 authentication {
 auth_type PASS
 auth_pass 1111
 }
 track_script {
 chk_haproxy
 }
 virtual_ipaddress {
 192.168.66.200
 }
}

带注释如下：

! 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 地址所在的网络接口相同
 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/haproxycheck.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

注意添加权限

chmod +x /etc/keepalived/haproxy_check.sh

启动Keepalived

service keepalived start

七 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>

参数：

• name：应用连接中间件逻辑库的用户名
• password：该用户对应的密码
• schemas ：应用当前连接的逻辑库。
• readOnly：应用连接中间件逻辑库所具有的权限。true 为只读，false 为读写都有，默认为 false

测试案例

使用user用户，权限为只读（readOnly：true），验证是否可以查 询出数据，验证是否可以写入数据。

1、用user用户登录，运行命令如下：

 mysql -uuser -puser -h 192.168.140.128 -P8066

2、切换到TESTDB数据库，查询orders表数据，如下：

use TESTDB
select * from orders;

3、执行插入数据sql

insert into
orders(id,order_type,customer_id,amount)
values(7,101,101,10000);

4、可看到运行结果，插入失败，只有只读权限

mysql> insert into orders(id,order_type,customer_id,amount) values(7,101,101,10000);
ERROR 1495 (HY000): User readonly

2 privileges标签权限控制

在 user 标签下的 privileges 标签可以对逻辑库（schema）、表 （table）进行精细化的 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权限	新增	更新	查询	删除
0000	禁止	禁止	禁止	禁止
0010	禁止	禁止	可以	禁止
1110	可以	禁止	禁止	禁止
1111	可以	可以	可以	可以

测试案例

使用mycat用户，privileges配置orders表权限为禁止增删改查 （dml=“0000”） 验证是否可以查询出数据，验证是否可以写入数据。

1、重启mycat，用mycat用户登录，运行命令如下：

mysql -umycat -p123456 -h 192.168.66.101 -P8066

2、切换到TESTDB数据库，查询orders表数据，如下：

use TESTDB
select * from orders;

3、禁止该用户查询数据

mysql> use TESTDB
Database changed
mysql> select* from orders;
ERROR 3012(HY000): The statement DML privilege check is not passed,reject for user
'mycat'

4、执行插入数据sql，如下

insert into orders(id,order_type,customer_id,amount) values(8,101,101,10000);

5、可看到运行结果，禁止该用户插入数据

mysql> insert into orders(id,order_type,customer_id,amount) values(8,101,101,10000);
ERROR 3012 (HY000): The statement DMLprivilege check is not passed，reject for use

3 SQL拦截白名单

firewall标签用来定义防火墙；firewall下whitehost标签用来定义IP 白名单 ，blacklist 用来定义SQL 黑名单。

白名单

可以通过设置白名单，实现某主机某用户可以访问 Mycat，而其他主机用户禁止访问。

#设置白名单
#server.xml配置文件firewall标签
#配置只有192.168.66.101主机可以通过mycat用户访问
<firewall>
 <whitehost>
  <host host="192.168.66.101" user="mycat"/>
 </whitehost>
</firewall>

4 SQL拦截黑名单

可以通过设置黑名单，实现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 拦截功能列表

八 Mycat性能监控

1 Mycat-web安装

Mycat-web是Mycat可视化运维的管理和监控平台。Mycat-web引 入了 ZooKeeper作为配置中心，可以管理多个节点。

注意：

Mycat-web 主要管理和监控 Mycat 的流量、连接、活动线程和 内存等，具备IP白名单、邮件告警等模块，还可以统计SQL并分 析慢SQL和高频 SQL等。为优化SQL提供依据。

Docker安装Zookeeper

docker run --name myzk -d -p 2181:2181 zookeeper

解压Mycat-web

tar -zxvf Mycat-web-1.0-SNAPSHOT20170102153329-linux.tar.gz -C /usr/local

设置Zookeeper的地址

在/usr/local/mycat-web/mycat-web/WEBINF/classes/mycat.properties文件中配置

zookeeper=192.168.66.101:2181

进入mycat-web的目录下运行启动命令

cd /usr/local/mycat-web/
./start.sh &

查看服务已经启动

lsof -i:8082

通过地址访问服务

 http://192.168.66.101:8082/mycat/

九 Mycat性能优化

JVM调优

Java堆内存和直接内存映射，建议堆内存大小适度，直接映射的内存尽可能大，总计占用操作系统50%～67%的内存。

示例

下面以16GB内存的服务器为例，Mycat堆内存为4GB，直接内存映 射为6GB，修改文件 /usr/local/mycat/bin/startup_nowrap.sh

JAVA_OPTS="-server -Xms4G -Xmx2G -XX:MaxPermSize=64M -XX:+AggressiveOpts -XX:MaxDirectMemorySize=6G"

Mycat调优

MyCAT所有的调优参数都可以才server.xml中找到

主要讨论两个内容:

processors数值的影响范围

<system>
   <property name="processors">1</property>
    <property name="processorExecutor">16</property>
</system>

参数：

• processors：CPU核心数越多，可以越大，当发现系统CPU压力很小的情况下，可以适当调 大此参数，如4核心的4CPU，可以设置为16，24核的可以最大设置为128
• processorExecutor：每个processor的线程池大小，建议可以是16-64，根据系统能力来测试和确定。

设置连接数

<dataHost name="localhostl" maxCon="500" mincon="10" balance="O"
dbType="MySQL"dbDriver="native" banlance="0">

注意： 最大连接池maxCon的值可以改为1000~2000，同一个MySQL 实例上的所有dataNode节点共享本dataHost上的所有物理连接。

十 Mycat实施指南

分表分库原则

• 原则一：能不分就不分；
• 原则二：分片数量尽量少；
• 原则三：分片规则需要慎重选择；
• 原则四：尽量不要在一个事务中的SQL跨越多个分片；
• 原则五：查询条件尽量优化

注意：

这里特别强调一下分片规则的选择问题，如果某个表的数据有明显的时间特征，比如订单、交易记录等，则他们通常比较合 适用时间范围分片，因为具有时效性的数据。

数据拆分原则

• 达到一定数量级才拆分（800 万）；
• 不到 800 万但跟大表（超 800 万的表）有关联查询的表也要拆分，在此称为大表关联表 ；
• 小于100万的使用全局表；
• 大于100万小于800 万跟大表使用同样的拆分策略。