Mycat 数据库中间件 1.6~2.1 从入门到放弃...

目录

MyCat1.6 的学习与使用

MyCat的基本概述

MyCat是什么？

MyCat： 是一个数据库中间件，用于帮我们管理我们的数据库，而我们只需要直接连接到MyCat进行使用即可，无需关心数据库的具体细节，实现面向MyCat操作数据库即可。

为什么要用MyCat？

为什么要用MyCat

  • 为了解决Java代码与数据库的紧耦合（我们一旦更换数据库的服务器地址或者一些其他操作，都需要直接修改Java代码）
  • 高访问量以及高并发对数据库造成的压力
  • 读写请求的数据不一致问题，也就是数据同步问题（这个需要配合MySql的主从复制）

常见的数据库中间件有哪些？

常用的数据库中间件
image

  • 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公司发布的中间件

MyCat能够做什么？

MyCat能够做什么？

  • 读写分离（需要配合MySql的主从复制）
    image

  • 数据分片

    • 垂直拆分（分库）
    • 水平拆分（分表）
    • 垂直 + 水平拆分（分库分表）

  • 多数据源整合（MyCat可以同时整合多个数据库完成多数据源整合，例如MySQL + Redis）

MyCat的原理

Mycat 的原理中最重要的一个动词是“拦截”，它拦截了用户发送过来的 SQL 语句，首先对 SQL
语句做了一些特定的分析：如分片分析、路由分析、读写分离分析、缓存分析等，然后将此 SQL 发
往后端的真实数据库，并将返回的结果做适当的处理，最终再返回给用户。
image
这种方式把数据库的分布式从代码中解耦出来，程序员察觉不出来后台使用 Mycat 还是
MySQL。

MyCat的安装，3个配置文件的大概含义

  • 下载MyCat：https://github.com/MyCATApache/Mycat-Server/releases
    image

  • 解压即可使用： 解压缩文件拷贝到 linux 下 /usr/local/
    tar -zxf Mycat-server-1.6.7.1-release-20190627191042-linux.tar.gz

  • 解压后将会得到一个目录：mycat目录

  • 3个配置文件的大概含义

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

MyCat的基本使用

修改server.xml配置文件，修改MyCat的用户信息

注意：这是MyCat的用户名和密码，配置一个账户，并为其配置能够访问的模式
注意2： 把原有的user标签删除掉。
注意3： 这个schemas需要指定schema.xml中配置的一个schema模式

…
<user name="mycat">
 <property name="password">123456</property>
 <property name="schemas">schema1</property>
</user>
…

修改schema.xml配置文件

  • 需要进行的配置
1、删除<schema>标签间的表信息
2、<dataNode>标签只留一个
3、<dataHost>标签只留一个
4、<writeHost> 只留一对
5、<readHost>只留一对，读写分离2台主机时可以使用
  • 配置示例，配置解释
    • schema： 配置一个模式，可以配置sql的最大长度等，其中配置的name，其实就是我们在连接MYCAT后，看到的数据库名称，但是实际背后还是连接着dataNode上指定的MYSQL数据库
    • dataNode：配置数据节点，其中指定需要访问到的数据库
    • dataHost：配置一台数据库的连接及心跳检测等配置信息，还有负载均衡信息
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

        <schema name="schema1" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">

        </schema>
        <dataNode name="dn1" dataHost="mysql21" database="testdb" />
        <dataHost name="mysql21" maxCon="1000" minCon="10" balance="0"
                          writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <!-- MyCat向Mysql服务器发送心跳检测的语句 -->
                <heartbeat>select user()</heartbeat>
                <writeHost host="hostM1" url="192.168.22.21:3306" user="root"  password="123456">
                        <!-- 读写分离时使用
                                <readHost host="hostS2" url="192.168.1.200:3306" user="root" password="xxx" />
                        -->
                </writeHost>
        </dataHost>
</mycat:schema>

启动MyCat，并使用navicat测试访问情况（把MyCat当成是一台MySQL来使用）

启动时有2种方式

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

使用navicat测试连接

mycat的默认监听端口： 8066
image

值得注意的是： 图形化界面的操作好像失效了一般，只能使用命令的方式来操作数据库

搭建读写分离

搭建一主一从

一个主机用于处理所有写请求，一台从机负责所有读请求，架构图如下
image

搭建MySQL主从复制(这里是MYSQL5.7.25)

修改主机MYSQL的配置文件，并重启服务
[client]
default-character-set=utf8

[mysql]
default-character-set=utf8

[mysqld]
init_connect='SET collation_connection = utf8_unicode_ci'
init_connect='SET NAMES utf8'
character-set-server=utf8
collation-server=utf8_unicode_ci
skip-character-set-client-handshake
skip-name-resolve

# 如下配置需要在mysqld标签下
#主服务器唯一ID
server-id=1
#启用二进制日志
log-bin=mysql-bin
# 设置不要复制的数据库(可设置多个)
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
#设置需要复制的数据库
binlog-do-db=testdb
#设置logbin格式，此种格式如果出现函数，将会导致主从数据不一致问题，因为是逻辑备份，保存的是sql语句
binlog_format=STATEMENT
修改从机MYSQL的配置文件，并重启服务
[client]
default-character-set=utf8

[mysql]
default-character-set=utf8

[mysqld]
init_connect='SET collation_connection = utf8_unicode_ci'
init_connect='SET NAMES utf8'
character-set-server=utf8
collation-server=utf8_unicode_ci
skip-character-set-client-handshake
skip-name-resolve

# 注意：如下配置需要配置在mysqld标签下
#从服务器唯一ID
server-id=2
#启用中继日志
relay-log=mysql-relay
在主机和从机分别执行如下命令，来完成主从复制的配置
  • 主机：执行如下命令创建一个从机用来进行主从复制的用户
# 在主机MySQL里执行授权命令
GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' IDENTIFIED BY '123456';
# 刷新权限
flush privileges;

  • 主机 ： 重建一个binlog日志flush logs;（可选）

  • 主机：执行show master status;
    image

  • 主机、从机都关闭防火墙（生产环境不要这么做）

systemctl stop firewalld;
systemctl disable firewalld;
  • 从机执行如下命令
     复制主机的命令
    CHANGE MASTER TO MASTER_HOST='主机的IP地址',
    MASTER_USER='slave',
    MASTER_PASSWORD='123456',
    MASTER_LOG_FILE='mysql-bin.具体数字',MASTER_LOG_POS=具体值;
CHANGE MASTER TO MASTER_HOST='192.168.22.21',
MASTER_USER='slave',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=590;
  • 从机： 开启主从复制，查看状态
start slave;
show slave status\G
  • 如果看到如下框框的地方标志为yes，代表主从复制成功
    image
如果配置出现问题导致从新配置，重新配置主从时

  • 主机：不变

  • 从机：先停止当前的主从复制，并重置中继日志。再重新指定正确的主机配置，最后再次开启主从复制

stop slave;
reset master;

CHANGE MASTER TO MASTER_HOST='192.168.22.21',MASTER_USER='slave',MASTER_PASSWORD='123456',MASTER_LOG_FILE='mysql-bin.000005',MASTER_LOG_POS=154;

start slave;
使用docker时的踩坑记录

前提概要：

  • 错误一： Docker容器运行时出现WARNING: IPv4 forwarding is disabled. Networking will not work.

使用了VmWare完成了一台mysql机器的克隆，修改静态ip之后，没有重启docker，导致主从复制出现错误Slave_IO_Running: Connecting,Last_IO_Errno:2003，也就是网络连接不可用。

解决： 重启docker即可

  • 错误二： 主从复制时，提示两台MYSQL的UUID不能相同
    解决： 修改任意一台服务器上docker运行的mysql的uuid即可
# 复制mysql容器中的数据
docker cp mysql:/var/lib/mysql/auto.cnf ./auto.cnf

# 修改
vim auto.cnf

# 再复制回去即可
docker cp ./auto.cnf mysql:/var/lib/mysql/auto.cnf

配置MyCat的schema.xml配置文件

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

        <schema name="schema1" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">

        </schema>
        <dataNode name="dn1" dataHost="mysql21" database="testdb" />
        <dataHost name="mysql21" maxCon="1000" minCon="10" balance="3"
                          writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <!-- MyCat向Mysql服务器发送心跳检测的语句 -->
                <heartbeat>select user()</heartbeat>
                <writeHost host="hostM1" url="192.168.22.21:3306" user="root"  password="root">
                        <readHost host="hostS1" url="192.168.22.22:3306" user="root" password="root" />
                </writeHost>
        </dataHost>
</mycat:schema>
配置文件的一些作用解释

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" ：所有读请求随机分发到 writeHost 对应的 readhost 执行，writerHost 不负担读压力。

switchType： 连接的数据库宕机时的切换规则

  • 默认值为 1，代表自动切换；
  • 设置为 2，代表基于 MySQL 主从同步的状态来决定是否切换；
  • 设置为 3，代表基于 MySQL galary cluster 的切换机制进行切换；
  • 如果你的集群基于 MMM 或 MHA 等高可用架构实现自动切换，则可以将该值设置为 -1，代表不切换。

dbDriver：数据库类型，可选的值有 native 和 JDBC，如果是 mysql，maridb，postgresql 等数据库，直接可以使用 native 即可。其他数据库则需要将对应的驱动包拷贝到 Mycat 安装目录的 lib 目录下，并写上完整的驱动类的类名。

启动MyCat

./mycat console

# 测试数据
create table student(
	id int primary key auto_increment,
	name varchar(12),
	age int
);

insert into student(name, age) values('张三', 15);
insert into student(name, age) values('李四', 18);
insert into student(name, age) values(@@hostname, 22);

select id,name,age from student;

搭建2主2从，并且两个Master互为主从关系（注意这里的配置文件与一主一从是有区别的）

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

配置规则如下：

192.168.22.21 Master1
192.168.22.22 Slave1
192.168.22.23 Master2
192.168.22.24 Slave2

搭建2主2从的主从复制关系

保证4台mysql服务的UUID均不一致
  • 错误二： 主从复制时，提示两台MYSQL的UUID不能相同
    解决： 修改任意一台服务器上docker运行的mysql的uuid即可
# 复制mysql容器中的数据
docker cp mysql:/var/lib/mysql/auto.cnf ./auto.cnf

# 修改
vim auto.cnf

# 再复制回去即可
docker cp ./auto.cnf mysql:/var/lib/mysql/auto.cnf
配置4台Mysql服务器的配置文件，配置后重启所有的MYSQL服务

Master1的my.cnf配置文件

[client]
default-character-set=utf8

[mysql]
default-character-set=utf8

[mysqld]
init_connect='SET collation_connection = utf8_unicode_ci'
init_connect='SET NAMES utf8'
character-set-server=utf8
collation-server=utf8_unicode_ci
skip-character-set-client-handshake
#skip-name-resolve

# 如下配置需要在mysqld标签下
#主服务器唯一ID
server-id=1
#启用二进制日志
log-bin=mysql-bin
# 设置不要复制的数据库(可设置多个)
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
#设置需要复制的数据库, 可设置多个，多个的话，就像不需要复制的数据库一样，配置多次该选项即可
binlog-do-db=testdb
#设置logbin格式，此种格式如果出现函数，将会导致主从数据不一致问题，因为是逻辑备份，保存的是sql语句
binlog_format=STATEMENT

# 额外配置
# 在作为从数据库的时候，有写入操作也要更新二进制日志文件
log-slave-updates
# 表示自增长字段每次递增的量，指自增字段的起始值，其默认值是1，取值范围是1 .. 65535
auto-increment-increment=2
# 表示自增长字段从哪个数开始，指字段一次递增多少，他的取值范围是1 .. 65535
auto-increment-offset=1

Master2的my.cnf配置文件

[client]
default-character-set=utf8

[mysql]
default-character-set=utf8

[mysqld]
init_connect='SET collation_connection = utf8_unicode_ci'
init_connect='SET NAMES utf8'
character-set-server=utf8
collation-server=utf8_unicode_ci
skip-character-set-client-handshake
skip-name-resolve

# 主从配置
#主服务器唯一ID
server-id=3
#启用二进制日志
log-bin=mysql-bin
# 设置不要复制的数据库(可设置多个)
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
#设置需要复制的数据库
binlog-do-db=testdb
#设置logbin格式
binlog_format=STATEMENT
# 在作为从数据库的时候，有写入操作也要更新二进制日志文件
log-slave-updates
#表示自增长字段每次递增的量，指自增字段的起始值，其默认值是1，取值范围是1 .. 65535
auto-increment-increment=2
# 表示自增长字段从哪个数开始，指字段一次递增多少，他的取值范围是1 .. 65535
auto-increment-offset=2

Slave1的my.cnf配置文件

[client]
default-character-set=utf8

[mysql]
default-character-set=utf8

[mysqld]
init_connect='SET collation_connection = utf8_unicode_ci'
init_connect='SET NAMES utf8'
character-set-server=utf8
collation-server=utf8_unicode_ci
skip-character-set-client-handshake
#skip-name-resolve

# 注意：如下配置需要配置在mysqld标签下
#从服务器唯一ID
server-id=2
#启用中继日志
relay-log=mysql-relay

Slave2的my.cnf配置文件

[client]
default-character-set=utf8

[mysql]
default-character-set=utf8

[mysqld]
init_connect='SET collation_connection = utf8_unicode_ci'
init_connect='SET NAMES utf8'
character-set-server=utf8
collation-server=utf8_unicode_ci
skip-character-set-client-handshake
skip-name-resolve


# 注意：如下配置需要配置在mysqld标签下
#从服务器唯一ID
server-id=4
#启用中继日志
relay-log=mysql-relay
注意4台服务器的防火墙问题，可以选择开放端口，也可以选择关闭防火墙
# 关闭防火墙
systemctl stop firewalld;
systemctl disable firewalld;
在两台主机上都创建用于主从复制的用户
# 在主机MySQL里执行授权命令
GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' IDENTIFIED BY '123456';
# 刷新权限
flush privileges;
两台Slave分别连接到自己的Master实现主从复制

1、在两台Slave对应的主机上分别执行show master status;

  • 192.168.22.21
    image
  • 192.168.22.23
    image

2、在两台从机上都连接到对应的主机

  • 192.168.22.22
CHANGE MASTER TO MASTER_HOST='192.168.22.21',MASTER_USER='slave',MASTER_PASSWORD='123456',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=600;

start slave;
show slave status\G
  • 192.168.22.24
CHANGE MASTER TO MASTER_HOST='192.168.22.23',MASTER_USER='slave',MASTER_PASSWORD='123456',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=600;

start slave;
show slave status\G
配置两台主机互为对方的Slave

在两台主机上分别执行如下命令

# 查看binlog状态
show status master;

# 192.168.22.21
CHANGE MASTER TO MASTER_HOST='192.168.22.23',MASTER_USER='slave',MASTER_PASSWORD='123456',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=964;

start slave;
show slave status\G


# 192.168.22.23
CHANGE MASTER TO MASTER_HOST='192.168.22.21',MASTER_USER='slave',MASTER_PASSWORD='123456',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=900;

start slave;
show slave status\G

配置MyCat的schema.xml配置文件 后 启动mycat

schema.xml配置文件

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

        <schema name="schema1" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">

        </schema>
        <dataNode name="dn1" dataHost="mysql21" database="testdb" />
        <dataHost name="mysql21" maxCon="1000" minCon="10" balance="1"
                          writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <!-- MyCat向Mysql服务器发送心跳检测的语句 -->
                <heartbeat>select user()</heartbeat>
                <writeHost host="hostM1" url="192.168.22.21:3306" user="root"  password="root">
                        <readHost host="hostS1" url="192.168.22.22:3306" user="root" password="root" />
                </writeHost>
                <writeHost host="hostM2" url="192.168.22.23:3306" user="root"  password="root">
                        <readHost host="hostS2" url="192.168.22.24:3306" user="root" password="root" />
                </writeHost>
        </dataHost>
</mycat:schema>

启动MyCat
./mycat console

测试读写分离

# 测试数据
create table student(
	id int primary key auto_increment,
	name varchar(12),
	age int
);

insert into student(name, age) values('张三', 15);
insert into student(name, age) values('李四', 18);
insert into student(name, age) values(@@hostname, 22);

select id,name,age from student;

抗风险能力测试

1、停止数据库Master1

2、在Mycat里插入数据依然成功，Master2自动切换为写主机

3、启动数据库Master1
注意：此时配置的负载均衡中，可以看到查询语句在Master1、Slava1、Slava2主从三个主机间切换

垂直拆分-分库（将一个数据库服务中的表，划分一部分表到另一个数据库服务中）

一个数据库由很多表的构成，每个表对应着不同的业务，垂直切分是指按照业务将表进行分类，分布到不同 的数据库上面，这样也就将数据或者说压力分担到不同的库上面，如下图。
image
系统被切分成了，用户，订单交易，支付几个模块。

如何划分表？在两台主机上的两个数据库中的表，能否关联查询？分库的原则及小实践.

疑问：在两台主机上的两个数据库中的表，能否关联查询？
答案：不可以关联查询。

分库的原则：有紧密关联关系的表应该在一个数据库当中，相互没有关联关系的表可以分到不同的数据库当中。

分库小实践
现在有如下4张表

#客户表 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)
);

从如上4张表中可以看出，只有订单详情表、订单表、订单状态字典表之间需要进行关联查询，因此我们可以把这3张表放在一个数据库当中。而客户表明显与他们没有直接的关联查询关系，因此可以放在另一个数据库当中。

MyCat实现分库（配置后，除了指定表的SQL会发送到其主机上，其他的均不会）

将之前2主2从的关系进行调整，把2个主机之间互为备机的设定关闭

  • 在2台主机上分别执行如下命令
stop slave;
reset slave;

修改MyCat的schema.xml配置文件

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

        <schema name="schema1" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
                <!-- 配置当操作某张表时，访问指定的数据节点 -->
                <table name="customer" dataNode="dn2"></table>
        </schema>
        <!-- 数据节点1，用于存储除了customer数据表以外的其他表的数据 -->
        <dataNode name="dn1" dataHost="host1" database="testdb" />
        <!-- 数据节点2，仅用于存储customer数据表的数据 -->
        <dataNode name="dn2" dataHost="host2" database="testdb" />

        <!-- 数据节点对应的数据主机,host1 -->
        <dataHost name="host1" maxCon="1000" minCon="10" balance="3"
                          writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <writeHost host="hostM1" url="192.168.22.21:3306" user="root"  password="root">
                        <readHost host="hostS1" url="192.168.22.22:3306" user="root" password="root" />
                </writeHost>
        </dataHost>

        <!-- 数据节点对应的数据主机,host2 -->
        <dataHost name="host2" maxCon="1000" minCon="10" balance="3"
                          writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <writeHost host="hostM2" url="192.168.22.23:3306" user="root"  password="root">
                        <readHost host="hostS2" url="192.168.22.24:3306" user="root" password="root" />
                </writeHost>
        </dataHost>
</mycat:schema>

启动MyCat进行测试

./mysql console

-- 测试数据
#客户表 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)
);
  • 连接上对应的MYSQL，发现确实已经分表了
    数据节点1
    image
    数据节点2
    image

水平拆分-分表

相对于垂直拆分，水平拆分不是将表做分类，而是按照某个字段的某种规则来分散到多个库之中，每个表中 包含一部分数据。简单来说，我们可以将数据的水平切分理解为是按照数据行的切分，就是将表中的某些行切分 到一个数据库，而另外的某些行又切分到其他的数据库中，如图：
image

MyCat实现分表

分析并选择需要进行拆分的表

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

例如：例子中的 orders、orders_detail 都已经达到 600 万行数据，需要进行分表优化。

分表字段（根据哪个字段进行分表）

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

编号 分表字段 效果
1 order_id（订单id） 由于我们的id都是单调递增，历史订单一般被访问的频率降低，很容易导致并发量都打在最新id存放的服务器上，导致访问不平均
2 customer_id（客户id） 根据客户id划分，两个节点的访问较为平均，一个客户的所有订单都在同一个节点

修改MyCat的配置文件schema.xml

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

        <schema name="schema1" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
                <!-- 配置当操作某张表时，访问指定的数据节点 -->
                <table name="customer" dataNode="dn2"></table>
                <!-- 当操作订单表时，要求根据规则访问指定的数据节点，该规则是自定义的名称 -->
                <table name="orders" dataNode="dn1,dn2" rule="mod_rule"></table>
        </schema>
        <!-- 数据节点1，用于存储除了customer数据表以外的其他表的数据 -->
        <dataNode name="dn1" dataHost="host1" database="testdb" />
        <!-- 数据节点2，仅用于存储customer数据表的数据 -->
        <dataNode name="dn2" dataHost="host2" database="testdb" />

        <!-- 数据节点对应的数据主机,host1 -->
        <dataHost name="host1" maxCon="1000" minCon="10" balance="3"
                          writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <writeHost host="hostM1" url="192.168.22.21:3306" user="root"  password="root">
                        <readHost host="hostS1" url="192.168.22.22:3306" user="root" password="root" />
                </writeHost>
        </dataHost>

        <!-- 数据节点对应的数据主机,host2 -->
        <dataHost name="host2" maxCon="1000" minCon="10" balance="3"
                          writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <writeHost host="hostM2" url="192.168.22.23:3306" user="root"  password="root">
                        <readHost host="hostS2" url="192.168.22.24:3306" user="root" password="root" />
                </writeHost>
        </dataHost>
</mycat:schema>

修改Mycat的配置文件rule.xml

新增分片规则 + 配置算法的参数（告知所使用的节点数量）

# 在 rule 配置文件里新增分片规则 mod_rule，并指定规则适用字段为 customer_id，
        <tableRule name="mod_rule">
                <rule>
                        <columns>customer_id</columns>
                        <algorithm>mod-long</algorithm>
                </rule>
        </tableRule>

#还有选择分片算法 mod-long（对字段求模运算），customer_id 对两个节点求模，根据结果分片
#配置算法 mod-long 参数 count 为 2，两个节点
        <function name="mod-long" class="io.mycat.route.function.PartitionByMod">
                <!-- how many data nodes -->
                <property name="count">2</property>
        </function>

重启MyCat后进行测试（注意进行分表的那一张表，在其对应的节点上，都需要创建好这张表）

# 重启mycat
./mycat console

# 测试表（注意需要每个节点上都有这个表）
CREATE TABLE orders(
 id INT AUTO_INCREMENT,
 order_type INT,
 customer_id INT,
 amount DECIMAL(10,2),
 PRIMARY KEY(id)
);

# 测试数据
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的分片json

Orders 订单表已经进行分表操作了，和它关联的 orders_detail 订单详情表如何进行 join 查询。我们要对 orders_detail 也要进行分片操作。Join 的原理如下图：

image

ER表（其实就是使子表的存储位置依赖于主表）

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

  • 修改schema.xml配置文件
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

        <schema name="schema1" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
                <!-- 配置当操作某张表时，访问指定的数据节点 -->
                <table name="customer" dataNode="dn2"></table>
                <!-- 当操作订单表时，要求根据规则访问指定的数据节点 -->
                <table name="orders" dataNode="dn1,dn2" rule="mod_rule">
                        <!--
                             使用ER表的方式，orders_details订单详细表存储到与订单表的id相对应的一个节点当中
                             name: 数据表的名称
                             primaryKey：orders_detail表中的哪个字段是主键
                             joinKey: 用于关联订单id的字段名称
                             parentKey: orders表中哪个字段作为id
                         -->
                        <childTable name="orders_detail" primaryKey="id" joinKey="order_id" parentKey="id" />
                </table>
        </schema>
        <!-- 数据节点1，用于存储除了customer数据表以外的其他表的数据 -->
        <dataNode name="dn1" dataHost="host1" database="testdb" />
        <!-- 数据节点2，仅用于存储customer数据表的数据 -->
        <dataNode name="dn2" dataHost="host2" database="testdb" />

        <!-- 数据节点对应的数据主机,host1 -->
        <dataHost name="host1" maxCon="1000" minCon="10" balance="3"
                          writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <writeHost host="hostM1" url="192.168.22.21:3306" user="root"  password="root">
                        <readHost host="hostS1" url="192.168.22.22:3306" user="root" password="root" />
                </writeHost>
        </dataHost>

        <!-- 数据节点对应的数据主机,host2 -->
        <dataHost name="host2" maxCon="1000" minCon="10" balance="3"
                          writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <writeHost host="hostM2" url="192.168.22.23:3306" user="root"  password="root">
                        <readHost host="hostS2" url="192.168.22.24:3306" user="root" password="root" />
                </writeHost>
        </dataHost>
</mycat:schema>

  • 重启MyCat
    ./mycat console

  • 保证配置了的节点上都有对应的数据表, 这里的话还需要在dn2数据节点上添加orders_detail表

CREATE TABLE orders_detail(
 id INT AUTO_INCREMENT,
 detail VARCHAR(2000),
 order_id INT,
 PRIMARY KEY(id)
);
  • 添加数据进行测试
insert into orders_detail(detail, order_id) values('订单详情1，我应该在数据节点1上', 1);
insert into orders_detail(detail, order_id) values('订单详情3，我应该在数据节点2上', 3);
insert into orders_detail(detail, order_id) values('订单详情4，我应该在数据节点2上', 4);

全局表（所有数据节点中都存在该表）

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

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

鉴于此，Mycat 定义了一种特殊的表，称之为“全局表”，全局表具有以下特性：

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

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

修改schema.xml配置文件

<table name="dict_order_type" dataNode="dn1,dn2" type="global" ></table>

重启Mycat并连接，进行如下操作进行验证

# 创建表，如果已经存在则会忽略，这样会使没有该表的数据节点也创建好该表
CREATE TABLE dict_order_type(
 id INT AUTO_INCREMENT,
 order_type VARCHAR(200),
 PRIMARY KEY(id)
);

# 添加数据
INSERT INTO dict_order_type(id,order_type) VALUES(101,'type1');
INSERT INTO dict_order_type(id,order_type) VALUES(102,'type2');

常用分片规则

取模

取模：此规则为对分片字段求摸运算。也是水平分表最常用规则。配置分表中的示例中，orders 表采用了此规则。

分片枚举

分片枚举：通过在配置文件中配置可能的枚举 id，自己配置分片，本规则适用于特定的场景，比如有些业务需要按照省份或区县来做保存，而全国省份区县固定的，这类业务使用本条规则。

1、修改schema.xml配置文件

<table name="orders_ware_info" dataNode="dn1,dn2" rule="sharding_by_intfile" ></table>

2、修改rule.xml配置文件

<!-- columns：分片字段，algorithm：分片函数 -->
<tableRule name="sharding_by_intfile">
         <rule>
                 <columns>areacode</columns>
                 <algorithm>hash-int</algorithm>
        </rule>
</tableRule>


<!--
        mapFile：标识配置文件名称
        type：0为int型、非0为String
        defaultNode：默认节点:小于 0 表示不设置默认节点，大于等于 0 表示设置默认节点，
                     设置默认节点如果碰到不识别的枚举值，就让它路由到默认节点，如不设置不识别就报错
-->
<function name="hash-int" class="io.mycat.route.function.PartitionByFileMap">
        <property name="mapFile">partition-hash-int.txt</property>
        <property name="type">1</property>
        <property name="defaultNode">0</property>
</function>

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

# 其中0代表第一个数据节点，1代表第二个数据节点
110=0
120=1

4、重启MyCat

5、访问MyCat创建表，订单归属区域信息表

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

INSERT INTO orders_ware_info(id, order_id,address,areacode) VALUES (1,1,'北京','110');
INSERT INTO orders_ware_info(id, order_id,address,areacode) VALUES (2,2,'天津','120');

6、查询Mycat、dn1、dn2可以看到数据分片效果
image

范围约定

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

1、修改schema.xml配置文件

<table name="payment_info" dataNode="dn1,dn2" rule="auto_sharding_long" ></table>

2、修改rule.xml配置文件

<!-- columns：分片字段，algorithm：分片函数 -->
<tableRule name="auto_sharding_long">
    <rule>
        <columns>order_id</columns>
        <algorithm>rang-long</algorithm>
    </rule>
</tableRule>


<!--
        mapFile：标识配置文件名称
        type：0为int型、非0为String
        defaultNode：默认节点:小于 0 表示不设置默认节点，大于等于 0 表示设置默认节点，
                     设置默认节点如果碰到不识别的枚举值，就让它路由到默认节点，如不设置不识别就报错
-->
<function name="rang-long" class="io.mycat.route.function.AutoPartitionByLong">
    <property name="mapFile">autopartition-long.txt</property>
    <property name="defaultNode">0</property>
</function>

3、修改autopartition-long.txt配置文件

# 其中0代表第一个数据节点，1代表第二个数据节点
0-102=0
103-200=1

4、重启MyCat

5、访问MyCat创建表，订单归属区域信息表

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


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

6、查询Mycat、dn1、dn2可以看到数据分片效果
image

按日期（天）分片

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

1、修改schema.xml配置文件

<table name="login_info" dataNode="dn1,dn2" rule="sharding_by_date" ></table>

2、修改rule.xml配置文件

<!-- columns：分片字段，algorithm：分片函数 -->
<tableRule name="sharding_by_date">
    <rule>
        <columns>login_date</columns>
        <algorithm>shardingByDate</algorithm>
    </rule>
</tableRule>


<!--
        # columns：分片字段，algorithm：分片函数
        # dateFormat ：日期格式
        # sBeginDate ：开始日期
        # sEndDate：结束日期,则代表数据达到了这个日期的分片后循环从开始分片插入
        # sPartionDay ：分区天数，即默认从开始日期算起，分隔 2 天一个分区
-->
<function name="shardingByDate" class="io.mycat.route.function.PartitionByDate">
    <property name="dateFormat">yyyy-MM-dd</property>
    <property name="sBeginDate">2019-01-01</property>
    <property name="sEndDate">2019-01-04</property>
    <property name="sPartionDay">2</property>
</function>

3、重启MyCat

4、访问MyCat创建表，订单归属区域信息表

CREATE TABLE login_info
(
 `id` INT AUTO_INCREMENT comment '编号',
 `user_id` INT comment '用户编号',
 `login_date` date comment '登录日期',
 PRIMARY KEY(id)
);


INSERT INTO login_info(id,user_id,login_date) VALUES (1,101,'2019-01-01');
INSERT INTO login_info(id,user_id,login_date) VALUES (2,102,'2019-01-02');
INSERT INTO login_info(id,user_id,login_date) VALUES (3,103,'2019-01-03');
INSERT INTO login_info(id,user_id,login_date) VALUES (4,104,'2019-01-04');
INSERT INTO login_info(id,user_id,login_date) VALUES (5,103,'2019-01-05');
INSERT INTO login_info(id,user_id,login_date) VALUES (6,104,'2019-01-06');

6、查询Mycat、dn1、dn2可以看到数据分片效果
image

全局ID序列

在实现分库分表的情况下，数据库自增主键已无法保证自增主键的全局唯一。为此，Mycat 提供了全局 sequence，并且提供了包含本地配置和数据库配置等多种实现方式

本地文件方式

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

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

数据库方式（可能出现的问题，以及MyCat是如何解决的）

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

问：那如果 Mycat 崩溃了 ，那内存中的序列岂不是都没了？
是的。如果是这样，那么 Mycat 启动后会向数据库申请新的号段，原有号段会弃用。
也就是说如果 Mycat 重启，那么损失是当前的号段没用完的号码，但是不会因此出现主键重复

1、建立序列脚本，在任意一个数据节点上，例如dn1

#在 dn1 上创建全局序列表
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;

#创建全局序列所需函数
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 ;

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 ;


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 ;

#初始化序列表记录
INSERT INTO MYCAT_SEQUENCE(NAME,current_value,increment) VALUES ('ORDERS', 400000,
100);

2、修改Mycat的配置

  • 修改sequence_db_conf.properties
vim sequence_db_conf.properties

#意思是 ORDERS这个序列在dn1这个节点上，具体dn1节点是哪台机子，请参考schema.xml
# 添加如下配置，指定表的全局序列去哪个数据节点找：
GLOBAL=dn1
COMPANY=dn1
CUSTOMER=dn1
ORDERS=dn1
  • 修改server.xml
vim server.xml
# 全局序列类型：0-本地文件，1-数据库方式，2-时间戳方式。此处应该修改成1。

image

3、重启MyCat

4、验证全局序列

#登录 Mycat，插入数据
insert into orders(id,amount,customer_id,order_type) values(next value for
MYCATSEQ_ORDERS,1000,101,102);

image

重启Mycat后，再次插入数据，再查询

image

时间戳方式

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

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

自主生成全局序列

可在 java 项目里自己生成全局序列，如下：

  • 根据业务逻辑组合
  • 可以利用 redis 的单线程原子性 incr 来生成序列但，自主生成需要单独在工程中用 java 代码实现，还是推荐使用 Mycat 自带全局序列。
  • 还可以使用雪花算法生成自带全局序列

基于HAProxy机制的MyCat高可用

在实际项目中，Mycat 服务也需要考虑高可用性，如果 Mycat 所在服务器出现宕机，或 Mycat 服
务故障，需要有备机提供服务，需要考虑 Mycat 集群。

高可用方案（其实好像用nginx的stream也可以...）

我们可以使用 HAProxy + Keepalived 配合两台 Mycat 搭起 Mycat 集群，实现高可用性。HAProxy实现了 MyCat 多节点的集群高可用和负载均衡，而 HAProxy 自身的高可用则可以通过 Keepalived来实现。

image

编号 角色 IP地址
1 MyCat1 192.168.22.19
2 MyCat2 192.168.22.20
3 HAProxy（master） 192.168.22.31
4 Keepalived（master） 192.168.22.31
5 HAProxy（backup） 192.168.22.32
6 Keepalived（backup） 192.168.22.32

安装配置HAProxy（2台服务器均采用一样的配置即可）

安装HAProxy

tar -zxf haproxy-2.7.0.tar.gz -C /usr/local/src/
  • 进入解压后的目录，查看内核版本，进行编译
cd /usr/local/src/haproxy-2.7.0/
uname -r

# ARGET=linux310，内核版本，使用uname -r查看内核，如：3.10.0-514.el7，此时该参数就为linux310；
#ARCH=x86_64，系统位数；
#PREFIX=/usr/local/haprpxy #/usr/local/haprpxy，为haprpxy安装路径。
make TARGET=linux310 PREFIX=/usr/local/haproxy ARCH=x86_64
  • 编译完成后，进行安装
make install PREFIX=/usr/local/haproxy
  • 安装完成后，创建目录、创建HAProxy配置文件
mkdir -p /usr/local/haproxy/data
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/data/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.22.19:8066 check inter 10s
    server mycat_2 192.168.22.20: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:123456
    stats hide-version
    stats admin if TRUE

启动验证

  • 启动HAProxy
/usr/local/haproxy/sbin/haproxy -f /usr/local/haproxy/haproxy.conf
  • 查看HAProxy进程
ps -ef | grep haproxy

  • 打开浏览器访问管理台页面
    http://192.168.22.31:7777/admin

  • 在弹出框输入用户名：admin密码：123456

  • 如果Mycat主备机均已启动，则可以看到如下图
    image

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

配置Keepalived（注意2台服务器的主从机关系，还有唯一标志）

安装Keepalived

tar -zxf keepalived-2.2.7.tar.gz -C /usr/local/src
  • 安装依赖插件
yum install -y gcc openssl-devel popt-devel
  • 进入解压后的目录，进行配置和编译
cd /usr/local/src/keepalived-2.2.7
./configure --prefix=/usr/local/keepalived
  • 进行编译，完全后进行安装
make && make install
  • 运行时配置
cp /usr/local/src/keepalived-2.2.7/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-2.2.7/keepalived/etc/sysconfig/keepalived /etc/sysconfig/

cp /usr/local/keepalived/sbin/keepalived /usr/sbin/
  • 修改配置文件vim /etc/keepalived/keepalived.conf，不同的机器只需要修改router_id
! Configuration File for keepalived
global_defs {
  # 每台机器需要唯一的路由id
  router_id lb111
  vrrp_skip_check_adv_addr
  vrrp_garp_interval 0
  vrrp_gna_interval 0
}

vrrp_instance mycatInstance {
  # 配置为主机，另外一台配置为SLAVE
  state MASTER
  # 使用的网卡
  interface ens33
  # 虚拟的路由id，多台机器需要一致
  virtual_router_id 51
  # 优先级
  priority 100
  advert_int 1

  # 配置同一个keepalived分组的认证
  authentication {
    auth_type PASS
    auth_pass 1111
  }
  
  # 配置虚拟ip
  virtual_ipaddress {
    192.168.22.200
  }
}

virtual_server 192.168.22.200 48066 {
  delay_loop 6
  lb_algo rr
  lb_kind NAT
  persistence_timeout 50
  protocol TCP
  real_server 192.168.22.31 48066 {
    weight 1
    TCP_CHECK {
      connect_timeout 3
      retry 3
      delay_before_retry 3
    }
  }
  real_server 192.168.22.32 48600 {
    weight 1
    TCP_CHECK {
      connect_timeout 3
      nb_get_retry 3
      delay_before_retry 3
    }
  }
}

启动验证测试高可用

#1启动Keepalived
systemctl start keepalived

#2登录验证
mysql -umycat -p123456 -h 192.168.22.200 -P 48066

image

使用keepalived映射的虚拟IP来访问HAProxy的管理页面，发现也是没问题的
image

Mycat安全设置

权限配置（server.xml配置文件）

user标签权限控制

目前 Mycat 对于中间件的连接控制并没有做太复杂的控制，目前只做了中间件逻辑库级别的读
写权限控制。是通过 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 该用户对应的密码
TESTDB 应用当前连接的逻辑库中所对应的逻辑表。schemas 中可以配置一个或多个
readOnly 应用连接中间件逻辑库所具有的权限。true 为只读，false 为读写都有，默认为 false

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">
         <!-- 额外注意，这个dml泛指全部的表，如果没有单独设置过的表，则使用该规则 -->
        <schema name="TESTDB" dml="1111" >
            <table name="orders" dml="0000"></table>
            <!--<table name="tb02" dml="1111"></table>-->
        </schema>
    </privileges>
</user>

配置说明
image

SQL拦截（server.xml）

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

白名单

  • 配置只有192.168.22.50主机可以通过mycat用户访问
<firewall>
    <whitehost>
        <host host="192.168.140.128" user="mycat"/>
    </whitehost>
</firewall>

黑名单

可以通过设置黑名单，实现 Mycat 对具体 SQL 操作的拦截，如增删改查等操作的拦截。

  • 禁止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 操作

MyCat监控工具 MyCat-web

Mycat-web 是 Mycat 可视化运维的管理和监控平台，弥补了 Mycat 在监控上的空白。帮 Mycat 分
担统计任务和配置管理任务。Mycat-web 引入了 ZooKeeper 作为配置中心，可以管理多个节点。
Mycat-web 主要管理和监控 Mycat 的流量、连接、活动线程和内存等，具备 IP 白名单、邮件告警等模块，还可以统计 SQL 并分析慢 SQL 和高频 SQL 等。为优化 SQL 提供依据。

image

Mycat-web配置使用

ZooKeeper安装

tar -zxf zookeeper-3.4.11.tar.gz
  • 进入ZooKeeper解压后的配置目录（conf），复制配置文件并改名
cp zoo_sample.cfg zoo.cfg
  • 进入ZooKeeper的命令目录（bin），运行启动命令
./zkServer.sh start
  • ZooKeeper服务端口为2181，查看服务已经启动
netstat -ant | grep 2181

image

MyCat-web安装

tar -zxvf Mycat-web-1.0-SNAPSHOT-20170102153329-linux.tar.gz
  • 拷贝mycat-web文件夹到/usr/local目录下
cp -r mycat-web /usr/local
  • 进入mycat-web的目录下运行启动命令
cd /usr/local/mycat-web/


# 后台启动
/start.sh &
  • Mycat-web服务端口为8082，查看服务已经启动
netstat -ant | grep 8082
  • 通过地址访问服务
http://192.168.22.50:8082/mycat/

image

MyCat配置

  • 先在注册中心配置ZooKeeper地址，配置后刷新页面，可见
    image

MyCat-web基本使用

新增Mycat监控实例

image
image

性能监控指标

在 Mycat-web 上可以进行 Mycat 性能监控，例如：内存分享、流量分析、连接分析、活动线程分
析等等。
image

Mycat 2.0数据库中间件的学习

相关概念

MyCat1.6 与 MyCat2.0的功能对比

功能 1.6 2.x
多语句 不支持 支持
blob值 支持一部分 支持
全局二级索引 不支持 支持
任意跨库join(包含复杂查询) catlet支持 支持
关联子查询 不支持 支持一部分
分库同时分表 不支持 支持
存储过程 支持固定形式的 支持更多
支持逻辑视图 不支持 支持
支持物理视图 支持 支持
批量插入 不支持 支持
执行计划管理 不支持 支持
路由注释 支持 支持
集群功能 支持 支持更多集群类型
自动hash分片算法 不支持 支持
支持第三方监控 支持mycat-web 支持普罗米斯,kafka日志等监控
流式合拼结果集 支持 支持
范围查询 支持 支持
单表映射物理表 不支持 支持
XA事务 弱XA 支持,事务自动恢复
支持MySQL8 需要更改mysql8的服务器配置支持 支持
虚拟表 不支持 支持
joinClustering 不支持 支持
unionAll语法 不支持 支持
BKAJoin 不支持 支持
优化器注释 不支持 支持
ER表 支持 支持
全局序列号 支持 支持
保存点 不支持 支持

不同版本的MYCAT的映射模型区别

MyCat1.6

  • 分库
    多个DataNode数据节点，并在schema标签中定义table，指定对应的数据节点。

  • 分表
    多个DataNode数据节点，在sachema标签中定义table，指定对应的数据节点，还需要指定分片规则。以此分散到不同机器上

  • 单表
    单个DataNode，不需要在schema中配置table标签。

MyCat2.x

  • 分库、分表、单表
    首先、配置多个数据源，一个数据源对应着一台MYSQL服务器，但是此时还不知道这些MYSQL服务器之间的关系。因此需要配置集群，在集群中指定一个个的数据源，并指定他们之间的关系。分库分表时就单独指定对应的表所对应的集群即可。

MyCat2的相关概念

分库分表

含义： 按照一定规则把数据库中的表拆分为多个带有数据库实例，物理库，物理库访问路径的分表。
解读：
  分库： 一个项目中，根据业务功能的不同，将不同的数据表放在不同的数据库当中，属于垂直拆分(根据业务功能拆分)
  分表：当一张表中的数据超过1000万条或者500万条左右，基本已经到达瓶颈了，因此我们需要将表中的数据按照一定的规则，存放在不同的数据表当中。两个不同的数据库中都有同一张表，但是表中的数据并不一致。属于水平拆分，即解决项目中单个节点无法满足需求，需要分配到多个节点来。

逻辑库

含义：数据库代理中的数据库，它可以包含多个逻辑表。

解读：MyCat里定义的库，在逻辑上存在，物理上在MYSQL中并不存在。有可能是多个MYSQL数据库共同组成了一个逻辑库，也可能是单个MYSQL数据库作为一个逻辑库。

可以理解为外面套了一层，逻辑库用来组合真实MYSQL的物理库，组成一个逻辑库

逻辑表

含义： 数据库代理中的表，他可以映射代理连接的数据库中的表（物理表）

解读：MYCAT里定义的表，在逻辑上存在，可以映射真实MYSQL物理数据库的表。可以一对多，也可以一对多

物理库

数据库代理连接的数据库中的库
解读： MYSQL真实的数据库

物理表

数据表代理连接点数据库中的表

解读： MYSQL真实数据库中的表

拆分键（分片键）

描述拆分逻辑表的数据规则的字段

解读： 比如根据不同的规则将表中的数据分散到不同的MYSQL物理表中，而用来指定规则的那个字段，就称为拆分键

物理分表

含义： 指已经进行数据拆分的，在数据库上面的物理表，是分片表的一个分区

解读：多个物理表里的数据汇总就是逻辑表的全部数据。物理分表其实就是指真实的MYSQL数据库中的表。 这一个个真实数据库中的表，数据汇总起来就是逻辑表。

物理分库

含义：一般指包含多个物理分表的库

解读： 参与数据分片的实际一个个的MYSQL真实数据库

分片表

按照一定规则把数据拆分成多个分区的表,在分库分表语境下,它属于逻辑表的一种

解读： 实际上就是对我们的真是数据库进行了物理分表，将数据根据不同的规则进行了拆分，拆分后的一个个物理分表组合起来，就是一个分片表。包含了物理分表的表中全部数据汇总。

单表

没有分片，没有数据冗余的表，仅在一个物理库中存在

全局表

每个数据库实例都冗余全量数据的逻辑表.

它通过表数据冗余,使分片表的分区与该表的数据在同一个数据库实例里,达到 join 运算能够直接在该数据库实例里执行.它的数据一致一般是通过数据库代理分发 SQL 实现. 也有基于集群日志的实现

解读：例如系统中翻译字段的字典表，每个分片表都需要完整的字典数据翻译字段

ER表

含义： 狭义指父子表中的子表,它的分片键指向父表的分片键,而且两表的分片算法相同广义指具有相同数据分布的一组表

解读： 关联别的表的子表，例如：订单详情表就是订单表的 ER 表

集群(多个数据源组成集群)

多个数据节点组成的逻辑节点.在 mycat2 里,它是把对多个数据源地址视为一个数据源
地址(名称),并提供自动故障恢复,转移,即实现高可用,负载均衡的组件。

解读：集群就是高可用、负载均衡的代名词

数据源

连接后端数据库的组件,它是数据库代理中连接后端数据库的客户端
解读：Mycat 通过数据源连接 MySQL 数据库

原型库(prototype)

原型库是 Mycat2 后面的数据库，比如 mysql 库

解读：原型库就是存储数据的真实数据库，配置数据源时必须指定原型库

配置文件

server.json

服务相关配置，在mycat2安装目录中的conf目录下
image

一般默认配置即可，不进行修改

users目录(注意XA与Proxy的区别)

配置用户相关信息

  • 所在目录
    mycat/conf/users

  • 命名方式
    用户名.user.json

  • 配置内容 vim mycat/conf/users/root.user.json，注意这个是MyCat的用户名和密码

{
    "ip": null, 
    "password": "123456", 
    "transactionType": "xa", 
    "username": "root", 
    "isolation": 3
}

配置详解

  • ip：客户端访问ip，建议为空,填写后会对客户端的ip进行限制
  • username：用户名
  • password：密码
  • isolation：设置初始化的事务隔离级别
    • READ_UNCOMMITTED:1
    • READ_COMMITTED:2
    • REPEATED_READ:3,默认
    • SERIALIZABLE:4
  • transactionType：事务类型
    • proxy 本地事务,在涉及大于 1 个数据库的事务,commit 阶段失败会导致不一致,但是兼容性最好
    • xa 需要确认存储节点集群类型是否支持 XA，就是你想的那个XA

可以在连接上MYCAT后通过命令修改事务类型

# 设置
set transaction_policy = 'xa'
set transaction_policy = 'proxy' 可以通过语句查询

# 查询
SELECT @@transaction_policy

数据源(datasource)

配置Mycat连接的数据源信息

  • 所在目录
    mycat/conf/datasources

  • 命名方式
    {数据源名字}.datasource.json

  • 配置内容vim mycat/conf/datasources/ prototype. datasources.json

{
    "dbType": "mysql", # 数据库类型
    "idleTimeout": 60000,  # 空闲超时时间
    "initSqls": [ ],  # 初始化SQL
    "initSqlsGetConnection": true, # 是否每次建立连接都执行初始化SQL 
    "instanceType": "READ_WRITE", # 配置实例的读写类型，默认是读写，也可以是READ 或 WRITE
    "maxCon": 1000, # 最大连接数量
    "maxConnectTimeout": 3000,  # 最大连接超时时间
    "maxRetryCount": 5,  # 最大对于该数据源的连接重试次数
    "minCon": 1,  # 最小连接数
    "name": "prototype",  # 当前数据源的名称
    "password": "123456",  # 连接真实MYSQL的密码
    "type": "JDBC",  # 数据源的类型
    "url": "jdbc:mysql://127.0.0.1:3306/mysql?useUnicode=true&serverTimezone=UTC", 
    "user": "root", # 连接真实MYSQL的用户名
    "weight": 0,  # 权重
    "queryTimeout": 30 # 查询超时时间，单位是秒
}

集群（Cluster）

配置集群信息，分库分表操作的就是集群

  • 所在目录
    mycat/conf/clusters

  • 命名方式
    {集群名字}.cluster.json

  • 配置内容vim mycat/conf/clusters/prototype.cluster.json

{
    "clusterType": "MASTER_SLAVE", // 集群类型
    "heartbeat": {
        "heartbeatTimeout": 1000, 
        "maxRetryCount": 3, 
        "minSwitchTimeInterval": 300, 
        "slaveThreshold": 0
    }, 
    "masters": [
        "prototypeDs"
    ], 
    "replicas": [
        "xxxx"
    ], 
    "maxCon": 200, 
    "name": "prototype", 
    "readBalanceType": "BALANCE_ALL", 
    "switchType": "SWITCH", 
// MySQL集群心跳周期,配置则开启集群心跳,Mycat主动检测主从延迟以及高可用主从切换
    "timer": {
        "initialDelay": 30, 
        "period": 5, 
        "timeUnit": "SECONDS"
    }
}

配置详情

  • clusterType ：集群类型，可选值有

    • SINGLE_NODE:单一节点
    • MASTER_SLAVE:普通主从
    • GARELA_CLUSTER: garela cluster/PXC 集群
    • MHA：MHA 集群
    • MGR：MGR 集群

  • readBalanceType：查询负载均衡策略

    • BALANCE_ALL(默认值)：获取集群中所有数据源
    • BALANCE_ALL_READ ： 获取集群中允许读的数据源
    • BALANCE_READ_WRITE ： 获取集群中允许读写的数据源,但允许读的数据源优先
    • BALANCE_NONE : 获取集群中允许写数据源,即主节点中选择

  • switchType ： 切换类型

    • NOT_SWITCH:不进行主从切换
    • SWITCH:进行主从切换

逻辑库表（schema）

配置逻辑库表，实现分库分表

  • 所在目录
    mycat/conf/schemas

  • 命名方式
    {库名}.schema.json

  • 配置内容vim mycat/conf/schemas/mydb1.schema.json

#库配置
{
  "schemaName": "mydb",
  "targetName": "prototype"
}
# schemaName：逻辑库名
# targetName：目的数据源或集群
targetName自动从prototype目标加载test库下的物理表或者视图作为单表,prototype
必须是mysql服务器

#单表配置
{
"schemaName": "mysql-test",
  "normalTables": {
  "role_edges": {
    "createTableSQL":null,//可选
    "locality": {
      "schemaName": "mysql",//物理库,可选
      "tableName": "role_edges",//物理表,可选
      "targetName": "prototype"//指向集群,或者数据源
    }
}
......
#详细配置见分库分表

MyCat的简单使用

安装MYCAT

  • 下载安装包

下载对应的 tar 安装包,以及对应的 jar包，其中的jar包需要放到tar包解压后的lib目录当中

tar（zip）包 ：http://dl.mycat.org.cn/2.0/install-template/mycat2-install-template-1.21.zip

jar包： http://dl.mycat.org.cn/2.0/1.21-release/mycat2-1.21-release-jar-with-dependencies.jar
下载最新的jar包

如果打不开如上的网址，需要配置以下hosts

210.51.26.184 mycat.org.cn www.mycat.org.cn dl.mycat.org.cn

github网址：https://github.com/MyCATApache/Mycat2

image

  • 将解压后，并且添加了jar的解压后的包，上传到linux服务器上，可以放在/usr/local目录下

  • 修改文件夹及以下文件的权限,bin 目录下
    修改成最高权限，否则运行启动命令时，会因权限不足而报错(只加可执行权限也可以)
    image

完成MyCat基本配置并启动（注意这里的数据源连接的数据库暂时根本无所谓）

  1. 在mycat连接的mysql数据库里添加用户

创建用户 ,用户名为mycat,密码为123456,赋权限，如下：

CREATE USER 'mycat'@'%' IDENTIFIED BY '123456';
--必须要赋的权限mysql8才有的
GRANT XA_RECOVER_ADMIN ON *.* TO 'root'@'%';
---视情况赋权限
GRANT ALL PRIVILEGES ON *.* TO 'mycat'@'%' ;
flush privileges;
  1. 修改mycat的prototype的配置vim conf/datasources/prototypeDs.datasource.json

启动mycat之前需要确认prototype数据源所对应的mysql数据库配置,修改对应的user(用户),password(密码),url中的ip

{
        "dbType":"mysql",
        "idleTimeout":60000,
        "initSqls":[],
        "initSqlsGetConnection":true,
        "instanceType":"READ_WRITE",
        "maxCon":1000,
        "maxConnectTimeout":3000,
        "maxRetryCount":5,
        "minCon":1,
        "name":"prototypeDs",
        "password":"root",
        "type":"JDBC",
        "url":"jdbc:mysql://192.168.22.21:3306/mysql?useUnicode=true&serverTimezone=Asia/Shanghai&characterEncoding=UTF-8",
        "user":"root",
        "weight":0
}
  1. 验证数据库访问情况
    Mycat 作为数据库中间件要和数据库部署在不同机器上，所以要验证远程访问情况。
mysql -uroot -proot -h 192.168.22.21 -P 3306
mysql -uroot -proot -h 192.168.22.22 -P 3306
#如远程访问报错，请建对应用户
grant all privileges on *.* to root@'%' identified by 'root';
  1. 启动mycat
cd mycat/bin
./mycat start
./mycat status
./mycat start 启动
./mycat stop 停止
./mycat console 前台运行
./mycat install 添加到系统自动启动（暂未实现）
./mycat remove 取消随系统自动启动（暂未实现）
./mycat restart 重启服务
./mycat pause 暂停
./mycat status 查看启动状态
  1. 登录
# 此登录方式用于管理维护 Mycat，注意，这个用户名密码目前是users配置文件下的root.user.json中的用户密码
mysql -uroot -p123456 -P 9066

# 数据接口
mysql -uroot -p123456 -P 8066

image

搭建读写分离

一主一从

前提概要： 先自行搭建好2台MYSQL实现主从复制

配置MyCat，创建逻辑库，配置数据源

1、连接MyCat，创建一个数据库（这里其实是逻辑库）
create database testdb

2、修改test_db_schema.schema.json 指定数据源 "targetName": "prototype"，配置主机数据源vim conf/schemas/testdb.schema.json

{
        "customTables":{},
        "globalTables":{},
        "normalProcedures":{},
        "normalTables":{},
        "schemaName":"testdb",
        "targetName": "prototype",
        "shardingTables":{},
        "views":{}
}

3、使用注解方式添加数据源

# 配置主机数据源
/*+ mycat:createDataSource{ "name":"master1", "url":"jdbc:mysql://192.168.22.21:3306/testdb?useSSL=false&characterEncoding=UTF-8&useJDBCCompliantTimezoneShift=true", "user":"root", "password":"root" } */;

# 配置从机数据源
/*+ mycat:createDataSource{ "name":"slave1", "url":"jdbc:mysql://192.168.22.22:3306/testdb?useSSL=false&characterEncoding=UTF-8&useJDBCCompliantTimezoneShift=true", "user":"root", "password":"root" } */

#查询配置数据源结果
/*+ mycat:showDataSources{} */;

4、更新集群信息，实现读写分离

# 添加集群信息
/*! mycat:createCluster{"name":"prototype","masters":["master1"],"replicas":["slave1"]} */;

#查看配置集群信息
/*+ mycat:showClusters{} */;

5、查看集群配置文件vim /usr/local/mycat/conf/clusters/prototype.cluster.json

{
        "clusterType":"MASTER_SLAVE",
        "heartbeat":{
                "heartbeatTimeout":1000,
                "maxRetryCount":3,
                "minSwitchTimeInterval":300,
                "showLog":false,
                "slaveThreshold":0.0
        },
        "masters":[
                "master1"
        ],
        "maxCon":2000,
        "name":"prototype",
        "readBalanceType":"BALANCE_ALL",
        "replicas":[
                "slave1"
        ],
        "switchType":"SWITCH"
}

配置详情

  • readBalanceType : 查询负载均衡策略
    • BALANCE_ALL(默认值)：获取集群中所有数据源
    • BALANCE_ALL_READ ： 获取集群中允许读的数据源
    • BALANCE_READ_WRITE ： 获取集群中允许读写的数据源,但允许读的数据源优先
    • BALANCE_NONE : 获取集群中允许写数据源,即主节点中选择

配置读写分离

1、修改master1的数据源，将"instanceType"设置为WRITE

2、修改slave数据源，将"instanceType"设置为READ

3、修改集群中的负载均衡策略，设置为BALANCE_ALL_READ即可

搭建2主2从（不知道原因，无法完成自动故障转移）

前提概要：根据上篇MyCat1.6来进行2主2从的主从复制MySQL的搭建

修改MyCat的配置实现2主2从

1、双主双从集群角色划分
*m1：主机
*m2：备机，也负责读
*s1，s2：从机

2、增加两个数据源(只加2个是因为配置1主1从的时候配置了2个了)

# 配置主机数据源
/*+ mycat:createDataSource{ "name":"master2", "url":"jdbc:mysql://192.168.22.23:3306/testdb?useSSL=false&characterEncoding=UTF-8&useJDBCCompliantTimezoneShift=true", "user":"root", "password":"root" } */;

# 配置从机数据源
/*+ mycat:createDataSource{ "name":"slave2", "url":"jdbc:mysql://192.168.22.24:3306/testdb?useSSL=false&characterEncoding=UTF-8&useJDBCCompliantTimezoneShift=true", "user":"root", "password":"root" } */

#查询配置数据源结果
/*+ mycat:showDataSources{} */;

3、修改集群配置文件vim /usr/local/mycat/conf/clusters/prototype.cluster.json

{
	"clusterType": "MASTER_SLAVE",
	"heartbeat": {
		"heartbeatTimeout": 1000,
		"maxRetryCount": 3,
		"minSwitchTimeInterval": 300,
		"slaveThreshold": 0
	},
	"masters": [
		"master1", "master2"
	],
	"replicas": [
		"master2", "slave1", "slave2"
	],
	"maxCon": 200,
	"name": "prototype",
	"readBalanceType": "BALANCE_ALL",
	"switchType": "SWITCH",
	"timer": {
		"initialDelay": 30,
		"period": 5,
		"timeUnit": "SECONDS"
	}
}

4、重启MyCat生效

读写分离配置扩展学习

通过对集群配置的修改，可以根据需求实现更多种情况的读写分离配置，总结如下

读写分离(一主一从,无备)(m是主,s是从)
{
	"clusterType": "MASTER_SLAVE",
	"heartbeat": {
		"heartbeatTimeout": 1000,
		"maxRetryCount": 3,
		"minSwitchTimeInterval": 300,
		"slaveThreshold": 0
	},
	"masters": [
		"m"
	],
	"replicas": [
		"s"
	],
	"maxCon": 200,
	"name": "prototype",
	"readBalanceType": "BALANCE_ALL",
	"switchType": "SWITCH",
	"timer": {
		"initialDelay": 30,
		"period": 5,
		"timeUnit": "SECONDS"
	}
}
读写分离(一主一从,一备)(m是主,s是从备)
{
	"clusterType": "MASTER_SLAVE",
	"heartbeat": {
		"heartbeatTimeout": 1000,
		"maxRetryCount": 3,
		"minSwitchTimeInterval": 300,
		"slaveThreshold": 0
	},
	"masters": [
		"m", "s"
	],
	"replicas": [
		"s"
	],
	"maxCon": 200,
	"name": "prototype",
	"readBalanceType": "BALANCE_ALL",
	"switchType": "SWITCH",
	"timer": {
		"initialDelay": 30,
		"period": 5,
		"timeUnit": "SECONDS"
	}
}
读写分离(一主一从,一备)(m是主,s是从,b是备)
{
	"clusterType": "MASTER_SLAVE",
	"heartbeat": {
		"heartbeatTimeout": 1000,
		"maxRetryCount": 3,
		"minSwitchTimeInterval": 300,
		"slaveThreshold": 0
	},
	"masters": [
		"m", "b"
	],
	"replicas": [
		"s"
	],
	"maxCon": 200,
	"name": "prototype",
	"readBalanceType": "BALANCE_ALL",
	"switchType": "SWITCH",
	"timer": {
		"initialDelay": 30,
		"period": 5,
		"timeUnit": "SECONDS"
	}
}
MHA(一主一从,一备)(m是主,s是从,b是备,READ_ONLY判断主)
{
	"clusterType": "MHA",
	"heartbeat": {
		"heartbeatTimeout": 1000,
		"maxRetryCount": 3,
		"minSwitchTimeInterval": 300,
		"slaveThreshold": 0
	},
	"masters": [
		"m", "b"
	],
	"replicas": [
		"s"
	],
	"maxCon": 200,
	"name": "prototype",
	"readBalanceType": "BALANCE_ALL",
	"switchType": "SWITCH",
	"timer": {
		"initialDelay": 30,
		"period": 5,
		"timeUnit": "SECONDS"
	}
}
MGR(一主一从,一备)(m是主,s是从,b是备,READ_ONLY判断主)
{
	"clusterType": "MGR",
	"heartbeat": {
		"heartbeatTimeout": 1000,
		"maxRetryCount": 3,
		"minSwitchTimeInterval": 300,
		"slaveThreshold": 0
	},
	"masters": [
		"m", "b"
	],
	"replicas": [
		"s"
	],
	"maxCon": 200,
	"name": "prototype",
	"readBalanceType": "BALANCE_ALL",
	"switchType": "SWITCH",
	"timer": {
		"initialDelay": 30,
		"period": 5,
		"timeUnit": "SECONDS"
	}
}
GARELA_CLUSTER(一主一从,一备)(m是主,s是从,b多主)
{
	"clusterType": "GARELA_CLUSTER",
	"heartbeat": {
		"heartbeatTimeout": 1000,
		"maxRetryCount": 3,
		"minSwitchTimeInterval": 300,
		"slaveThreshold": 0
	},
	"masters": [
		"m", "b"
	],
	"replicas": [
		"s"
	],
	"maxCon": 200,
	"name": "prototype",
	"readBalanceType": "BALANCE_ALL",
	"switchType": "SWITCH",
	"timer": {
		"initialDelay": 30,
		"period": 5,
		"timeUnit": "SECONDS"
	}
}

搭建读写分离踩坑记录（逻辑库名称，mycat这个数据库）

问题1： 创建逻辑库时，如果逻辑库与MYSQL的物理库名称不一致时，将会导致MYCAT创建的数据库与实际数据库没有任何关联。。。 (大坑)

问题2： 搭建主从复制的时候，mycat会自动为我们的数据源创建一个mycat数据库，而由于其他从库没有该库，会导致主从关系直接报错 （大坑） 后失效。我们可以把mycat也设置为需要主从同步的数据库。

分库同时分表（只要配置好了数据源，配置好了集群，直接创建表即可..会自动绑定）

Mycat2 一大优势就是可以在终端直接创建数据源、集群、库表，并在创建时指定分库、分表。与 1.6 版本比大大简化了分库分表的操作

添加数据源

/*+ mycat:createDataSource{ "name":"master1", "url":"jdbc:mysql://192.168.22.21:3306/testdb?useSSL=false&characterEncoding=UTF-8&useJDBCCompliantTimezoneShift=true", "user":"root", "password":"root" } */;

/*+ mycat:createDataSource{ "name":"master2", "url":"jdbc:mysql://192.168.22.22:3306/testdb?useSSL=false&characterEncoding=UTF-8&useJDBCCompliantTimezoneShift=true", "user":"root", "password":"root" } */;

/*+ mycat:createDataSource{ "name":"slave1", "url":"jdbc:mysql://192.168.22.23:3306/testdb?useSSL=false&characterEncoding=UTF-8&useJDBCCompliantTimezoneShift=true", "user":"root", "password":"root" } */;

/*+ mycat:createDataSource{ "name":"slave2", "url":"jdbc:mysql://192.168.22.24:3306/testdb?useSSL=false&characterEncoding=UTF-8&useJDBCCompliantTimezoneShift=true", "user":"root", "password":"root" } */;
  • 通过注释命名添加数据源后，在对应目录会生成相关配置文件
    cd /usr/local/mycat/conf/datasources
    如下图
    image

添加集群配置（集群名称必须以c开头数字结尾）

/*!mycat:createCluster{"name":"c0","masters":["master1"],"replicas":["slave1"]}*/;

/*!mycat:createCluster{"name":"c1","masters":["master2"],"replicas":["slave2"]}*/;

可以查看集群配置信息
cd /usr/local/mycat/conf/clusters
image

创建全局表

如此操作后，所有被分配到的集群中的数据节点上都会有这张全局表，也就是刚刚集群中配置的4个节点全部都会有

use testdb;

CREATE TABLE `travelrecord2` (
	`id` BIGINT NOT NULL AUTO_INCREMENT,
	`user_id` VARCHAR(100) DEFAULT NULL,
	`traveldate` DATE DEFAULT NULL,
	`fee` DECIMAL(10,0) DEFAULT NULL,
	`days` INT DEFAULT NULL,
	`blob` LONGBLOB,
	PRIMARY KEY (`id`),
	KEY `id` (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 BROADCAST;

进入相关目录查看 schema 配置
image

创建分片表（会自动分库分表，需要注意主从复制所选中的数据库，可能会出现无法同步的情况，因为会自动创建数据库）

CREATE TABLE orders(
  id BIGINT NOT NULL AUTO_INCREMENT,
  order_type INT,
  customer_id INT,
  amount DECIMAL(10,2),
  PRIMARY KEY(id),
  KEY `id` (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
dbpartition BY mod_hash(customer_id) tbpartition BY mod_hash(customer_id) tbpartitions 1 dbpartitions 2;

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);
SELECT * FROM orders;

image

创建ER表

#在 Mycat 终端直接运行建表语句进行数据分片
CREATE TABLE orders_detail(
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  detail VARCHAR(2000),
  order_id INT,
  PRIMARY KEY(id)
)ENGINE=INNODB DEFAULT CHARSET=utf8;
dbpartition BY mod_hash(order_id) tbpartition BY mod_hash(order_id) tbpartitions 1

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);
SELECT * FROM orders o INNER JOIN orders_detail od ON od.order_id=o.id;

image
image

  • 查看配置的表是否具有ER关系
/*+ mycat:showErGroup{}*/

常用分片规则（hash自动，并且要求集群名）

1、分片算法简介
Mycat2 支持常用的(自动)HASH 型分片算法也兼容 1.6 的内置的(cobar)分片算法.HASH 型分片算法默认要求集群名字以 c 为前缀,数字为后缀,c0 就是分片表第一个节点,c1 就是第二个节点.该命名规则允许用户手动改变

2、Mycat2 与 1.x 版本区别

  • Mycat2 Hash 型分片算法多数基于 MOD_HASH(MOD 对应 JAVA 的%运算）,实际上是取余运算。
  • Mycat2 Hash 型分片算法对于值的处理,总是把分片值转换到列属性的数据类型再运算。
  • 而 1.x 系列的分片算法统一转换到字符串类型再运算且只能根据一个分片字段计算出存储节点下标。
  • Mycat2 Hash 型分片算法适用于等价条件查询。而 1.x 系列由于含有用户经验的路由规则。1.x 系列的分片规则总是先转换成字符串再运算。

3、分片规则与适用性
image
image

4、常用分片规则简介

（1）MOD_HASH
[数据分片]HASH 型分片算法-MOD_HASH
如果分片值是字符串则先对字符串进行 hash 转换为数值类型
分库键和分表键是同键：
分表下标=分片值%(分库数量*分表数量)
分库下标=分表下标/分表数量
分库键和分表键是不同键：
分表下标= 分片值%分表数量
分库下标= 分片值%分库数

（2）RIGHT_SHIFT
[数据分片]HASH 型分片算法-RIGHT_SHIFT
RIGHT_SHIFT(字段名,位移数)
仅支持数值类型
分片值右移二进制位数,然后按分片数量取余

（3）YYYYMM
[数据分片]HASH 型分片算法-YYYYMM
仅用于分库
(YYYY*12+MM)%分库数.MM 是 1-1

（4）MMDD
仅用于分表
仅 DATE/DATETIME 一年之中第几天%分表数
tbpartitions 不超过 36

全局序列

Mycat2 在 1.x 版本上简化全局序列，自动默认使用雪花算法生成全局序列号，如不需要 Mycat 默认的全局序列，可以通过配置关闭自动全局序列

建表语句方式关闭全局序列

如果不需要使用 mycat 的自增序列,而使用 mysql 本身的自增主键的功能,需要在配置中更改对应的建表 sql,不设置 AUTO_INCREMENT 关键字,这样,mycat 就不认为这个表有自增主键的功能,就不会使用 mycat 的全局序列号.这样,对应的插入 sql 在 mysql处理,由 mysql 的自增主键功能补全自增值.

例如：

#带 AUTO_INCREMENT 关键字使用默认全局序列
CREATE TABLE db1.`travelrecord` (
`id` bigint NOT NULL AUTO_INCREMENT,
`user_id` varchar(100) DEFAULT NULL,
`traveldate` date DEFAULT NULL,
`fee` decimal(10,0) DEFAULT NULL,
`days` int DEFAULT NULL,
`blob` longblob,
PRIMARY KEY (`id`),
KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 BROADCAST;


#去掉关键字，不使用
CREATE TABLE db1.`travelrecord` (
`id` bigint NOT NULL,
`user_id` varchar(100) DEFAULT NULL,
`traveldate` date DEFAULT NULL,
`fee` decimal(10,0) DEFAULT NULL,
`days` int DEFAULT NULL,
`blob` longblob,
PRIMARY KEY (`id`),
KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 BROADCAST;

设置Mycat数据库方式获取全局序列

1、在prototype服务器的db1库导入dbseq.sql文件

Mycat2已经为用户提供了相关sql脚本，需要在对应数据库下运行脚本，不能通过Mycat客户端执行。脚本所在目录mycat/conf
image

2、添加全局序列配置文件
进入/mycat/conf/sequences目录，添加配置文件

{数据库名字}_{表名字}.sequence.json

配置内容：
{
 "clazz":"io.mycat.plug.sequence.SequenceMySQLGenerator",
 "name":"db1_travelrecord",
 "targetName": "prototype",
 "schemaName":"db1"//指定物理库名
}

可选参数targetName 更改序列号服务器

"targetName": "prototype" 是执行自增序列的节点,也是dbseq.sql导入的节点
dbseq.sql导入的当前库的库名与逻辑表的逻辑库名一致

导入后检查库下有没有mycat_sequence表。
image

其中increment是序列号自增的步伐,为1的时候严格按1递增,当1000的时候,mycat会每次批量递增1000取序列号.此时在多个mycat访问此序列号表的情况下,不能严格自增NAME列中的值是对应的 库名_表名 该值需要用户设置,即插入一条逻辑表相关的记录, 用于记录序列号

3、切换为数据库方式全局序列号
使用注释前要导入dbseq.sql以及设置mycat_sequence表内的逻辑表记录

通过注释设置为数据库方式全局序列号

/*+ mycat:setSequence{
"name":"db1_travelrecord",
"clazz":"io.mycat.plug.sequence.SequenceMySQLGenerator",
"name":"db1_travelrecord",
"targetName": "prototype",
"schemaName":"db2"
} */

4、切换为雪花算法方式全局序列号

/*+ mycat:setSequence{"name":"db1_travelrecord","time":true} */;

Mycat2安全设置

1、user 标签权限控制
目前 Mycat 对于中间件的连接控制并没有做太复杂的控制，目前只做了中间件逻辑库级别的读写权限控制。是通过 mycat/conf/users 目录下的{用户名}.user.json进行配置

{
"dialect":"mysql",
"ip":null,
"password":"123456",
"transactionType":"xa",
"username":"root"
}

image

2、权限说明
Mycat2 权限分为两块：登录权限、sql 权限
（1）登录权限：
Mycat2 在 MySQL 网络协议的时候检查客户端的 IP,用户名,密码
其中 IP 使用正则表达式匹配,一旦匹配成功,就放行
（2）sql 权限
使用自定义拦截器实现

Mycat2 UI

Mycat2 UI 是官方推出的 Mycat2 监控工具

1、下载
http://dl.mycat.org.cn/2.0/ui/
2、运行环境
在安装 JDK8 的环境,双击 jar 包就可以

3、使用
（1）连接
连接 Mycat2(需要 Mycat2 服务器正常启动
image

编辑分片表
image
导入文件是 csv 格式,无表头
(6 项)
prototype,s0,t0,0,0,0
prototype,s0,t1,0,1,1
(3 项)
prototype,s0,t0
prototype,s0,t1

暂时不支持自动 HASH 型算法的分区导入
物理分库下标,物理分表下标是根据分片算法要求填入,没有明确要求不需要填写
对于 1.6 的分片算法,物理分库下标,物理分表下标是没有意义的,只有总物理分表
下标有意义(总分表的下标)

编辑索引表
image

编辑全局表
image

使用MyCat1.6期间出现的错误

由于xml文件配置顺序导致出错

必须 将 schema dataNode dataHost 分别集中存放

不能基于实例节点放置比如：schema1 -》 dataNode1 -》 dataHost1 -》schema2 -》 dataNode2 -》dataHost2 这样是不行的

在schema.xml数据文件中进行配置table时，如果指定了多个数据节点，但是数据节点中又没有对应的表时

将会直接报错，找不到数据表：1105 - Table 'testdb.orders_detail' doesn't exist

小提示： 实现高可用时，如果有一台mysql宕机，可能导致短时间的数据不一致问题

插入数据报错partition table, insert must provide ColumnList

原因：进行了分区的表，在插入时必须指定插入的列

解决： 需要在插入数据时，指定需要插入的列

分库分表的概念（非常重要）

分库

  • 分为垂直分库、水平分库
  • 分库解决的问题： 单台服务器磁盘空间不足
  • 需要关心的问题： 数据路由和关联查询问题

分表

  • 分为垂直分表、水平分表(作为水平分库的一个补充)
  • 分表解决的问题：单表数据量过大导致查询性能下降，例如单表数据超过500w条或超过2G
  • 需要关心的问题：数据路由和关联查询问题

image

垂直分表
由于单表数据量过大，为了区分热点数据与非热点数据，我们将一张表中的数据字段按照冷热进行拆分成多张表，一般拆分完后，仍在一个数据库中。解决的是单表数据量过大问题
image

垂直分库
由于单台服务器磁盘空间有限，单台服务器磁盘不够可以进行垂直分库，根据不同的业务对表进行划分，划分后分别放在不同的数据库当中。
image

水平分库
如果已经进行了垂直分库，该数据库中磁盘空间不足了，但是由于已经对表进行了业务的划分，无法再进行垂直分库了，此时就可以进行水平分库。将单个数据库拆分为多个数据库，多个数据库当中拥有的表都一致，但是存储的表数据不同。
image

水平分表(对水平分库的补充)
当进行水平分库后，库中的单表数据量仍然过大，可以对该表再次进行水平拆分。把单表拆分为多张表，每张表的结构一致，但是拥有的数据不一致.
image

posted @ 2022-12-05 20:35  CodeStars  阅读(596)  评论(0)    收藏  举报