Mycat核心教程(mycat实战应用)
登录
标签搜索
侧边栏壁纸
博主昵称
良辰美景好时光

  • 累计撰写 67 篇文章
  • 累计收到 0 条评论
Mysql

Mycat核心教程(mycat实战应用)

lilymaxyz
lilymaxyz
2024-08-14 / 0 评论 / 6 阅读 / 正在检测是否收录...

@TOC)

一、MyCat概述

1.1.Mycat 是数据库中间件

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

1.2.为什么要用Mycat

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

在这里插入图片描述

1.3.数据库中间件对比

在这里插入图片描述
①Cobar属于阿里B2B事业群，始于2008年，在阿里服役3年多，接管3000+个MySQL数据库的schema,集群日处理在线SQL请求50亿次以上。由于Cobar发起人的离职，Cobar停止维护。

②Mycat是开源社区在阿里cobar基础上进行二次开发，解决了cobar存在的问题，并且加入了许多新的功能在其中。

1.4.Mycat的官网

1.4.1.Mycat的官网:http://www.mycat.org.cn/

在这里插入图片描述

1.4.2.右上角下载里面有个文件下载服务，点进去发现无法访问

1.5.MyCat功能

1.5.1.读写分离

在这里插入图片描述

1.5.2.数据分片：垂直拆分（分库）、水平拆分（分表）、垂直+水平拆分（分库分表）

在这里插入图片描述

1.5.3.多数据源整合

在这里插入图片描述

1.6.MyCat原理

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

二、MyCat_V1.0安装、启动、登录

2.1.安装

2.1.1.mycat安装方式-- 解压后，即可以使用

  • rpm 方式，按安装顺序
  • yum 方式，需要联网
  • 解压后，编译安装
  • 解压后，即可以使用
    1. 上传到：/opt/mycat （放安装包）
    1. 解压：tar -zxvf
    1. 解压后的，拷贝到：/usr/local/mycat（cp -r ）
    1. 三个配置文件：
      -- ①.schema.xml：定义 逻辑库、表、分片节点等内容。
      -- ②.rule.xml：定义分片规则。
      -- ③.server.xml：定义用户，以及系统相关变量，如端口等。

2.1.2.上传到：/opt/mycat （放安装包）

在这里插入图片描述

cd /opt/

tar -zxvf Mycat-server-1.6.7.1-release-20190627191042-linux.tar.gz

在这里插入图片描述

2.1.3.解压后的，拷贝到：/usr/local/mycat

cp -r mycat /usr/local

ll /usr/local/

cd /usr/local/mycat/

在这里插入图片描述

2.1.4.三个配置文件

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

2.2.启动

2.2.1.修改配置文件server.xml

修改用户信息，与MySQL区分

vi /usr/local/mycat/conf/server.xml

在这里插入图片描述

2.2.2.修改配置文件schema.xml

vi /usr/local/mycat/conf/schema.xml

删除标签间的表信息，标签只留一个，标签只留一个， 只留一对

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

        <!-- 虚拟逻辑库配置  与server.xml 中的数据库对应 ,TESTDB是逻辑数据库，指向映射的实际数据库-->
        <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
            <!-- 逻辑表配置 -->
        </schema>
        
        <!-- 数据节点配置 -->
        <!-- <dataNode>标签的database属性值必须是配置的真实数据库testdb，这个数据库可以是读主机中所有已有的数据库 -->
        <dataNode name="dn1" dataHost="host1" database="testdb" />

        <!-- 节点主机配置 --> 
        <dataHost name="host1" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <!-- heartbeat标签内指明用于和后端数据库进行心跳检查的语句 -->
                <!--heartbeat:心跳，mycat一直检测mysql的user表（心跳），当接收不到心跳时，就会认为这台mysql宕机了，就不会在对该节点做增删改查等操作-->
                <heartbeat>select user()</heartbeat>

                <!-- 可以拥有多个写主机，url是写主机的IP地址和端口号，user和password是登录写主机的mysql的用户名和密码 -->
                <writeHost host="hostM1" url="192.168.147.128:3306" user="root" password="Mysql.123456">
                        <!-- 可以拥有多个读主机，url是读主机的IP地址和端口号，user和password是登录读主机的mysql的用户名和密码 -->
                        <readHost host="hostS1" url="192.168.147.129:3306" user="root" password="Mysql.123456" />
                </writeHost>
        </dataHost>
</mycat:schema>
在运行原理上，MyCat并不直接指向实际的物理库，而是有一个逻辑库的概念。
我们通过<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1"></schema>定义了逻辑库的数据节点，
但是逻辑库dataNode="dn1"最终其实还是要指向实际的物理库，
所以也要通过<dataNode name="dn1" dataHost="host1" database="testdb" />来指向实际的物理库。
需要建库建表

在这里插入图片描述
在这里插入图片描述

2.2.3.放行3306端口

systemctl status firewalld

# --add-port=3306/tcp，表示添加指定3306端口，格式为：端口/通讯协议
# --permanent表示设置为持久
# --zone  表示作用域
firewall-cmd --zone=public --add-port=3306/tcp --permanent

# --remove-port表示移除指定端口
firewall-cmd --zone=public --remove-port=3306/tcp --permanent

# 重新加载防火墙
firewall-cmd --reload

# 再次查询开放端口
firewall-cmd --list-ports

# 查看已配置规则
firewall-cmd --list-all

在这里插入图片描述

2.2.4.验证Mysql数据库的访问情况

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

mysql -uroot -pMysql.123456 -h 192.168.147.128 -P 3306

mysql -uroot -pMysql.123456 -h 192.168.147.129 -P 3306

#如远程访问报错，请建对应用户 
grant all privileges on *.* to root@'缺少的host'  identified by 'Mysql.123456';

2.2.5.分别在读写主机上建库建表

mysql -uroot -pMysql.123456 -h 192.168.147.128 -P 3306

mysql -uroot -pMysql.123456 -h 192.168.147.129 -P 3306

show databases;

CREATE DATABASE IF NOT EXISTS testdb CHARACTER SET utf8mb4;

use testdb;

DROP TABLE IF EXISTS user; 
CREATE TABLE `user` (
  `id` int(11) PRIMARY KEY NOT NULL AUTO_INCREMENT COMMENT '主键',
  `name` varchar(50) NOT NULL COMMENT '姓名'
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='用户表';

# ALTER TABLE user AUTO_INCREMENT = 1;

INSERT INTO user (name) VALUES('zhangsan'),('lisi'),('lily');

select * from user;

在这里插入图片描述
在这里插入图片描述
在这里插入图片描述

2.2.6.启动程序

cd /usr/local/mycat/bin

./mycat console

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

在这里插入图片描述

2.2.7. 将MyCat配置到环境变量中

vim /etc/profile
# MyCat环境变量配置
export MYCAT_HOME=/usr/local/mycat
export PATH=$PATH:$MYCAT_HOME/bin
# 输入下面命令让设置的环境变量生效
source /etc/profile

2.2.8.MyCat常用操作命令

mycat start

mycat stop

#前台运行MyCat带控制台输出
mycat console

mycat restart

#暂停MyCat
mycat pause

#查看启动状态
mycat status

在这里插入图片描述

2.3.登录

2.3.1.登录后台管理窗口【9066】-- 用于管理维护Mycat

# -u Mycat server.xml中配置逻辑库的用户mycat
# -p Mycat server.xml中配置逻辑库的密码123456
# -P 后面是管理端口号。注意P是大写
# 9066用于查看MyCat的运行状态
# -h 后面是主机。 即当前Mycat安装的主机IP地址
# -D Mycat server.xml中配置逻辑库
# mysql -umycat -p -P 9066 -h 192.168.147.128 -DTESTDB
mysql -umycat -p -P 9066 -h 192.168.147.128
123456

#常用命令如下：
show database;
show @@help;

在这里插入图片描述

在这里插入图片描述

在这里插入图片描述
在这里插入图片描述

2.3.2.登录数据窗口【8066用于进行数据的CRUD操作】

此登录方式用于通过 Mycat 查询数据，我们选择这种方式访问 Mycat

# mysql -umycat -p -P 8066 -h 192.168.147.128 -DTESTDB
mysql -umycat -p -P 8066 -h 192.168.147.128
123456

2.3.3.报错处理：使用show tables找不到表

在这里插入图片描述

mysql> show databases;
+----------+
| DATABASE |
+----------+
| TESTDB   |
+----------+
1 row in set (0.00 sec)

mysql> use TESTDB;
Database changed
mysql> show tables;
ERROR 1184 (HY000): Invalid DataSource:0

在这里插入图片描述

三、搭建读写分离

通过 Mycat 和 MySQL 的主从复制配合搭建数据库的读写分离，实现 MySQL 的高可用性。

3.1.搭建一主一从

一个主机用于处理所有写请求，一台从机负责所有读请求，架构图如下
在这里插入图片描述

3.1.1.搭建MySQL 数据库主从复制

3.1.1.1.MySQL 主从复制原理--I/O具有延时性

master主节点的bin log传到slave从节点后，被写道relay-log里，从节点的slave sql线程从relay-log里读取日志然后应用到slave从节点本地。从服务器I/O线程将主服务器的二进制日志读取过来记录到从服务器本地文件，然后SQL线程会读取relay-log日志的内容并应用到从服务器，从而使从服务器和主服务器的数据保持一致。

中继日志是连接mastert(主服务器)和slave(从服务器)的信息，它是复制的核心，I/O线程将来自master的bin-log存储到中继日志中，中继日志充当缓冲，这样master不必等待slave执行完成就可以发送下一个bin-log。

在这里插入图片描述

  1. Master收到客户端请求语句，在语句结束之前向二进制日志写入一条记录，可能包含多个事件
  2. 一个Slave连接到Master，Master的dump线程从bin-log读取日志并发送到Slave的IO线程。
  3. IO线程从master.info读取到上一次写入的最后的位置。
  4. IO线程写入日志到relay-log中继日志，如果超过指定的relay-log大小，写入轮换事件，创建一个新的relay-log。
  5. 更新master.info的最后位置
  6. SQL线程从relay-log.info读取进上一次读取的位置
  7. SQL线程读取日志事件
  8. 在数据库中执行sql
  9. 更新relay-log.info的最后位置
  10. Slave记录自己的bin-log日志

3.1.1.2.主机配置【192.168.147.128】

修改配置文件

vim /etc/my.cnf 
# 主服务器唯一ID 
server-id=1 
# 启用二进制日志 
log-bin=mysql-bin 
# 设置不要复制的数据库(可设置多个) 
binlog-ignore-db=mysql 
binlog-ignore-db=information_schema 
#设置需要复制的数据库 
binlog-do-db=需要复制的主数据库名字 
#设置logbin格式 
binlog_format=STATEMENT

在这里插入图片描述

在这里插入图片描述

3.1.1.3.从机配置【192.168.147.129】

修改配置文件
vim /etc/my.cnf 

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

3.1.1.4.主机、从机重启MySQL 服务

systemctl restart mysqld

systemctl status mysqld

3.1.1.5.主机从机都放行3306端口

systemctl status firewalld

# --add-port=3306/tcp，表示添加指定3306端口，格式为：端口/通讯协议
# --permanent表示设置为持久
# --zone  表示作用域
firewall-cmd --zone=public --add-port=3306/tcp --permanent

# --remove-port表示移除指定端口
firewall-cmd --zone=public --remove-port=3306/tcp --permanent

# 重新加载防火墙
firewall-cmd --reload

# 再次查询开放端口
firewall-cmd --list-ports

# 查看已配置规则
firewall-cmd --list-all

3.1.1.6.在主机上建立帐户并授权 slave

mysql -uroot -p
# 在主机MySQL里执行授权命令 
GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' IDENTIFIED BY 'Mysql.123123';

#查询master的状态 
show master status;

#记录下File和Position的值 
#执行完此步骤后不要再操作主服务器MySQL，防止主服务器状态值变化

在这里插入图片描述

3.1.1.7.在从机上配置需要复制的主机

mysql -uroot -p
#复制主机的命令 
CHANGE MASTER TO MASTER_HOST='主机的IP地址',
MASTER_USER='slave',
MASTER_PASSWORD='Mysql.123123',
MASTER_LOG_FILE='mysql-bin.具体数字',MASTER_LOG_POS=具体值;

#启动从服务器复制功能 
start slave;
#查看从服务器状态 
show slave status\G;

#下面两个参数都是Yes，则说明主从配置成功！ 
# Slave_IO_Running: Yes 
# Slave_SQL_Running: Yes

在这里插入图片描述
在这里插入图片描述

3.1.1.8.解决问题：Slave_IO_Running: No

Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.

# 报错信息发现是mysql的server_uuid的原因
find / -name auto.cnf

cat /var/lib/mysql/auto.cnf
# 检查两个mysql实例的uuid发现主从的uuid一模一样

# 删除从库的auto.cnf文件并且重启从库实例问题解决
rm -rf /var/lib/mysql/auto.cnf
systemctl restart mysqld

# 检查从库复制状态
show slave status\G;

主从正常

在这里插入图片描述

在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述

3.1.1.9.主机新建库、新建表、insert 记录，从机复制

CREATE DATABASE IF NOT EXISTS testdb CHARACTER SET utf8mb4;

use testdb;

DROP TABLE IF EXISTS user; 
CREATE TABLE `user` (
  `id` int(11) PRIMARY KEY NOT NULL AUTO_INCREMENT COMMENT '主键',
  `name` varchar(50) NOT NULL COMMENT '姓名'
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='用户表';

INSERT INTO user (name) VALUES('zhangsan'),('lisi'),('lily');

select * from user;

在这里插入图片描述

3.1.1.10.如何停止从服务复制功能--stop slave

stop slave;

3.1.1.11.之前搭建过主从复制，如何重新配置主从

stop slave;

reset master;

3.1.1.2.修改Mycat 的配置文件schema.xml

之前的配置已分配了读写主机，是否已实现读写分离？

3.1.1.2.1.验证读写分离

-- 在写主机插入
insert into user(name) values (@@hostname);
-- 在写主机数据库表user中插入带系统变量数据，造成主从数据不一致
-- 在Mycat里查询
select * from user;

在这里插入图片描述

3.1.1.2.2.修改的balance属性，通过此属性配置读写分离的类型

负载均衡类型，目前的取值有4 种：

（1）balance="0", 不开启读写分离机制，所有读操作都发送到当前可用的 writeHost 上。

（2）balance="1"，全部的 readHost 与 stand by writeHost 参与 select 语句的负载均衡，简单的说，当双主双从模式(M1->S1，M2->S2，并且 M1 与 M2 互为主备)，正常情况下，M2,S1,S2 都参与 select 语句的负载均衡。 【双主双从】

（3）balance="2"，所有读操作都随机的在 writeHost、readhost 上分发。

（4）balance="3"，所有读请求随机的分发到 readhost 执行，writerHost 不负担读压力。【单主单从】

3.1.1.2.3.为了能看到读写分离的效果，把balance设置成2，会在两个主机间随机切换查询

在这里插入图片描述

3.1.1.3.重新启动Mycat

mycat console
mysql -umycat -p -P 8066 -h 192.168.147.128
123456

3.1.1.4.验证读写分离：在Mycat里查询user表,可以看到查询语句在主从两个主机间切换

在这里插入图片描述
在这里插入图片描述

3.2.搭建双主双从

一个主机m1 用于处理所有写请求，它的从机s1 和另一台主机m2 还有它的从机 s2 负责所有读请求。
当m1 主机宕机后，m2 主机负责写请求，m1、m2 互为备机。
架构图如下
在这里插入图片描述

在这里插入图片描述

编号角色IP地址机器名
1Master1192.168.147.128host128
2Slave1192.168.147.129host129
3Master2192.168.147.130host130
4Slave2192.168.147.131host131

3.2.1.清除原来配置

  • 删除数据库testdb
  • 重新配置主从
drop database testdb;

stop slave;

reset master;

3.2.2.搭建MySQL 数据库主从复制【双主双从】

3.2.2.1.双主机配置--Master1配置【host128】

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

3.2.2.2.双主机配置--Master2配置【host130】

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

3.2.2.3.双从机配置--Slave1配置【host129】

# 修改配置文件
vim /etc/my.cnf
# 从服务器唯一ID
server-id=2
# 启用中继日志
relay-log=mysql-relay

3.2.2.4.双从机配置--Slave2配置【host130】

# 修改配置文件
vim /etc/my.cnf
# 从服务器唯一ID
server-id=4
# 启用中继日志
relay-log=mysql-relay

3.2.2.5.双主机、双从机重启 mysql 服务

systemctl restart mysqld

systemctl status mysqld

在这里插入图片描述

3.2.2.6.主机从机都放行3306端口

systemctl status firewalld

# --add-port=3306/tcp，表示添加指定3306端口，格式为：端口/通讯协议
# --permanent表示设置为持久
# --zone  表示作用域
firewall-cmd --zone=public --add-port=3306/tcp --permanent

# --remove-port表示移除指定端口
firewall-cmd --zone=public --remove-port=3306/tcp --permanent

# 重新加载防火墙
firewall-cmd --reload

# 再次查询开放端口
firewall-cmd --list-ports

# 查看已配置规则
firewall-cmd --list-all

在这里插入图片描述

3.2.2.7.在两台主机上建立帐户并授权 slave

mysql -uroot -p
# 在主机MySQL里执行授权命令 
GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' IDENTIFIED BY 'Mysql.123123';

use mysql;

select host,user from user;

在这里插入图片描述

在这里插入图片描述

3.2.2.8.查询master1的状态

show master status;

#记录下File和Position的值 
#执行完此步骤后不要再操作主服务器MySQL，防止主服务器状态值变化

在这里插入图片描述

3.2.2.9.查询master2的状态

show master status;

#记录下File和Position的值 
#执行完此步骤后不要再操作主服务器MySQL，防止主服务器状态值变化

在这里插入图片描述

3.2.2.10.在从机上配置需要复制的主机

mysql -uroot -p
#复制主机的命令 
CHANGE MASTER TO MASTER_HOST='主机的IP地址',
MASTER_USER='slave',
MASTER_PASSWORD='Mysql.123123',
MASTER_LOG_FILE='mysql-bin.具体数字',MASTER_LOG_POS=具体值;

#启动两台从服务器复制功能 
start slave;
#查看从服务器状态 
show slave status\G;

#下面两个参数都是Yes，则说明主从配置成功！ 
# Slave_IO_Running: Yes 
# Slave_SQL_Running: Yes

3.2.2.11.slave1【host129】

#slave1复制主机的命令
stop slave;
reset master;
CHANGE MASTER TO MASTER_HOST='192.168.147.128',
MASTER_USER='slave',
MASTER_PASSWORD='Mysql.123123',
MASTER_LOG_FILE='mysql-bin.000005',MASTER_LOG_POS=154;

#启动两台从服务器复制功能 
start slave;
#查看从服务器状态 
show slave status\G;

#下面两个参数都是Yes，则说明主从配置成功！ 
# Slave_IO_Running: Yes 
# Slave_SQL_Running: Yes 
ERROR 3021 (HY000): This operation cannot be performed with a running slave io thread; 
run STOP SLAVE IO_THREAD FOR CHANNEL '' first.

在这里插入图片描述
在这里插入图片描述

3.2.2.12.slave2【host131】

#slave2复制主机的命令
stop slave;
CHANGE MASTER TO MASTER_HOST='192.168.147.130',
MASTER_USER='slave',
MASTER_PASSWORD='Mysql.123123',
MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=435;

#启动两台从服务器复制功能 
start slave;
#查看从服务器状态 
show slave status\G;

#下面两个参数都是Yes，则说明主从配置成功！ 
# Slave_IO_Running: Yes 
# Slave_SQL_Running: Yes

在这里插入图片描述
在这里插入图片描述

3.2.2.13.两个主机互相复制：Master2 复制Master1

#Master2复制主机的命令
CHANGE MASTER TO MASTER_HOST='192.168.147.128',
MASTER_USER='slave',
MASTER_PASSWORD='Mysql.123123',
MASTER_LOG_FILE='mysql-bin.000005',MASTER_LOG_POS=154;

#启动两台主服务器复制功能 
start slave;
#查看从服务器状态 
show slave status\G;

在这里插入图片描述

3.2.2.14.两个主机互相复制：Master1 复制Master2

#Master2复制主机的命令
CHANGE MASTER TO MASTER_HOST='192.168.147.130',
MASTER_USER='slave',
MASTER_PASSWORD='Mysql.123123',
MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=435;

#启动两台主服务器复制功能 
start slave;
#查看从服务器状态 
show slave status\G;

在这里插入图片描述

3.2.2.15.Master1 主机新建库、新建表、insert 记录，Master2 和从机复制

CREATE DATABASE IF NOT EXISTS testdb CHARACTER SET utf8mb4;

use testdb;

DROP TABLE IF EXISTS user; 
CREATE TABLE `user` (
  `id` int(11) PRIMARY KEY NOT NULL AUTO_INCREMENT COMMENT '主键',
  `name` varchar(50) NOT NULL COMMENT '姓名'
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='用户表';

INSERT INTO user (name) VALUES('zhangsan'),('lisi'),('lily');

select * from user;

select @@hostname;
use testdb;
select * from user;
select @@hostname;

在这里插入图片描述

3.2.2.16.如何停止从服务复制功能--stop slave

stop slave;

3.2.2.17.之前搭建过主从复制，如何重新配置主从

stop slave;

reset master;

3.2.3.修改Mycat 的配置文件schema.xml

修改的balance属性，通过此属性配置读写分离的类型

负载均衡类型，目前的取值有4 种：

（1）balance="0", 不开启读写分离机制，所有读操作都发送到当前可用的 writeHost 上。

（2）balance="1"，全部的 readHost 与 stand by writeHost 参与 select 语句的负载均衡，简单的说，当双主双从模式(M1->S1，M2->S2，并且 M1 与 M2 互为主备)，正常情况下，M2,S1,S2 都参与 select 语句的负载均衡。 【双主双从】

（3）balance="2"，所有读操作都随机的在 writeHost、readhost 上分发。

（4）balance="3"，所有读请求随机的分发到 readhost 执行，writerHost 不负担读压力。【单主单从】

3.2.3.1.双主双从读写分离balance设置为1

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

        <!-- 虚拟逻辑库配置  与server.xml 中的数据库对应 ,TESTDB是逻辑数据库，指向映射的实际数据库-->
        <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
                <!-- 逻辑表配置 -->
        </schema>
        <!-- 数据节点配置 -->
        <!-- <dataNode>标签的database属性值必须是配置的真实数据库testdb，这个数据库可以是读主机中所有已有的数据库 -->
        <dataNode name="dn1" dataHost="host1" database="testdb" />

        <!-- 节点主机配置 -->
        <dataHost name="host1" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">

                <!-- heartbeat标签内指明用于和后端数据库进行心跳检查的语句 -->
                <heartbeat>select user()</heartbeat>

                <!-- 可以拥有多个写主机，url是写主机的IP地址和端口号，user和password是登录写主机的mysql的用户名和密码 -->
                <writeHost host="hostM1" url="192.168.147.128:3306" user="root" password="Mysql.123456">
                        <!-- 可以拥有多个读主机，url是读主机的IP地址和端口号，user和password是登录读主机的mysql的用户名和密码 -->
                        <readHost host="hostS1" url="192.168.147.129:3306" user="root" password="Mysql.123456" />
                </writeHost>

                <!-- 可以拥有多个写主机，url是写主机的IP地址和端口号，user和password是登录写主机的mysql的用户名和密码 -->
                <writeHost host="hostM2" url="192.168.147.130:3306" user="root" password="Mysql.123456">
                        <!-- 可以拥有多个读主机，url是读主机的IP地址和端口号，user和password是登录读主机的mysql的用户名和密码 -->
                        <readHost host="hostS2" url="192.168.147.131:3306" user="root" password="Mysql.123456" />
                </writeHost>

        </dataHost>
</mycat:schema>

# balance="1": 全部的readHost与stand by writeHost参与select语句的负载均衡。 
# writeType="0": 所有写操作发送到配置的第一个writeHost，第一个挂了切到还生存的第二个 。
# writeType="1": 所有写操作都随机的发送到配置的 writeHost，1.5 以后废弃不推荐 。
# writeHost，重新启动后以切换后的为准，切换记录在配置文件中:dnindex.properties。
# switchType="1": 1 默认值，自动切换。 
#                -1 表示不自动切换 。
#                 2 基于 MySQL 主从同步的状态决定是否切换。

在这里插入图片描述

3.2.3.2.验证读写分离

-- 在写主机Master1插入
insert into user(name) values (@@hostname);
-- 在写主机Master1数据库表user中插入带系统变量数据，造成主从数据不一致
select @@hostname;
select * from user;

在这里插入图片描述

3.2.4.重新启动Mycat

mycat console

mysql -umycat -p -P 8066 -h 192.168.147.128
123456

3.2.5.验证读写分离：在Mycat里查询user表

-- 在Mycat里查询
select @@hostname;
use TESTDB;
select * from user;

在Mycat里查询user表,可以看到查询语句在Slava1（host129）、Master2（host130）、Slava2（host131）主从三个主机间切换

在这里插入图片描述

3.2.6.抗风险能力--停止数据库Master1【host128】

select @@hostname;

systemctl stop mysqld
systemctl status mysqld

cat /etc/hostname

在这里插入图片描述

3.2.7.在Mycat里插入数据依然成功，Master2【host130】自动切换为写主机

-- 在Mycat里插入数据
insert into user(name) values (@@hostname);

select @@hostname;

select * from user;

在这里插入图片描述

3.2.8.启动数据库Master1【host128】

systemctl start mysqld
systemctl status mysqld

在这里插入图片描述

3.2.9.在Mycat里查询user表

看到查询语句在Master1（host128）、Slava1（host129）、Slava2（host131）主从三个主机间切换

select @@hostname;

select * from user;

在这里插入图片描述

3.2.10.Master1、Master2 互做备机，负责写的主机宕机，备机切换负责写操作，保证数据库读写分离高可用性

四、垂直拆分--分库

一个数据库由很多表的构成，每个表对应着不同的业务，垂直切分是指按照业务将表进行分类，分布到不同的数据库上面，这样也就将数据或者说压力分担到不同的库上面，如下图：

在这里插入图片描述

4.1.划分表

注意：在两台主机上的两个数据库中的表，不可以关联查询。

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

#客户表 rows:20万条
CREATE TABLE customer(
    id INT AUTO_INCREMENT,
    NAME VARCHAR(200),
    PRIMARY KEY(id)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

#订单表 rows:600万条
CREATE TABLE orders(
    id INT AUTO_INCREMENT,
    order_type INT,
    customer_id INT,
    amount DECIMAL(10,2),
    PRIMARY KEY(id)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

#订单详细表 rows:600万条
CREATE TABLE orders_detail(
    id INT AUTO_INCREMENT,
    detail VARCHAR(2000),
    order_id INT,
    PRIMARY KEY(id)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

#订单状态字典表 rows:20条
CREATE TABLE dict_order_type(
    id INT AUTO_INCREMENT,
    order_type VARCHAR(200),
    PRIMARY KEY(id)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

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

垂直分库，不能在老的数据库上进行，需要在新的空数据库上进行。

先在【host128，host129】两台机器上创建好2个数据库，然后通过Mycat创建好需要的空表（分布在两台机器上的数据库中）。

最后将老的数据库上的数据插入到新的2个数据库上就可以了。

4.2.实现分库

4.2.1.修改schema配置文件

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

        <!-- 虚拟逻辑库配置  与server.xml 中的数据库对应 ,TESTDB是逻辑数据库，指向映射的实际数据库-->
        <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
                <!-- 逻辑表配置 指定当前表所在的指定的节点位置（即所在的数据库）-->
                <table name="customer" dataNode="dn2"></table>
        </schema>
        <!-- 数据节点配置 -->
        <!-- <dataNode>标签的database属性值必须是配置的真实数据库testdb，这个数据库可以是读主机中所有已有的数据库 -->
        <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标签内指明用于和后端数据库进行心跳检查的语句 -->
                <heartbeat>select user()</heartbeat>

                <!-- 可以拥有多个写主机，url是写主机的IP地址和端口号，user和password是登录写主机的mysql的用户名和密码 -->
                <writeHost host="hostM1" url="192.168.147.128:3306" user="root" password="Mysql.123456">
                </writeHost>
        </dataHost>
        <!-- 节点主机配置 -->
        <dataHost name="host2" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <!-- heartbeat标签内指明用于和后端数据库进行心跳检查的语句 -->
                <heartbeat>select user()</heartbeat>

                <!-- 可以拥有多个写主机，url是写主机的IP地址和端口号，user和password是登录写主机的mysql的用户名和密码 -->
                <writeHost host="hostM2" url="192.168.147.129:3306" user="root" password="Mysql.123456">
                </writeHost>
        </dataHost>
</mycat:schema>

在这里插入图片描述

4.2.2.新增两个数据库【orders】

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

#在数据节点 dn1【host128】、dn2【host129】 上分别创建数据库 orders
mysql -uroot -p
Mysql.123456

select @@hostname;
CREATE DATABASE IF NOT EXISTS orders CHARACTER SET utf8mb4;
use orders;

在这里插入图片描述

4.2.3.重新启动Mycat

mycat console

4.2.4.访问Mycat 进行分库

mysql -umycat -p -P 8066 -h 192.168.147.128
123456

#切换到TESTDB
use TESTDB;

#创建4 张表 
#查看表信息，可以看到成功分库 

use orders;
show tables;
select @@hostname;

在这里插入图片描述
在这里插入图片描述

五、水平拆分--分表

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

5.1.实现分表

5.1.1.选择要拆分的表

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

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

5.1.2.分表字段

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

编号分表字段效果
1id（主键、或创建时间）查询订单注重时效，历史订单被查询的次数少，
如此分片会造成一个节点访问多，一个访问少，不平均。
2customer_id（客户id）根据客户 id 去分，两个节点访问平均，一个客户的所有订单都在同一个节点

5.1.3.修改配置文件schema.xml

vi /usr/local/mycat/conf/schema.xml

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

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

在这里插入图片描述

5.1.4.修改配置文件rule.xml

vi /usr/local/mycat/conf/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">
                <!-- how many data nodes -->
                <property name="count">2</property>
        </function>

在这里插入图片描述

5.1.5.在数据节点dn2 【host129】上建orders 表【订单表】

#订单表 rows:600万条
CREATE TABLE orders(
    id INT AUTO_INCREMENT,
    order_type INT,
    customer_id INT,
    amount DECIMAL(10,2),
    PRIMARY KEY(id)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

在这里插入图片描述

5.1.6.重启Mycat，让配置生效

mycat console

mysql -umycat -p -P 8066 -h 192.168.147.128
123456

#切换到TESTDB
use TESTDB;
show tables;

5.1.7.访问Mycat 实现分片

INSERT INTO orders VALUES(1,101,100,100100);

#在mycat 里向orders 表插入数据，INSERT 字段不能省略
INSERT INTO orders(id,order_type,customer_id,amount) VALUES
(1,101,100,100100),(2,101,100,100300),(3,101,101,120000),
(4,101,101,103000),(5,102,101,100400),(6,102,100,100020);

#在mycat、dn1【host128】、dn2【host129】中查看orders表数据，分表成功

在这里插入图片描述

5.2.Mycat 的分片 “join”

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

5.2.1.水平分表--ER表

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

5.2.1.1.修改schema.xml 配置文件

        <!-- 虚拟逻辑库配置  与server.xml 中的数据库对应 ,TESTDB是逻辑数据库，指向映射的实际数据库-->
        <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
                <!-- 逻辑表配置 -->
                <table name="customer" dataNode="dn2"></table>
                <!-- 为orders 表设置数据节点为 dn1、dn2，并指定分片规则为 mod_rule（自定义的名字） -->
                <table name="orders" dataNode="dn1,dn2"  rule="mod_rule" >
                        <childTable name="orders_detail" primaryKey="id" joinKey="order_id" parentKey="id" />
                </table> 
        </schema>

在这里插入图片描述

5.2.1.2.在dn2【host129】创建orders_detail 表【订单详细表】

#订单详细表 rows:600万条
CREATE TABLE orders_detail(
    id INT AUTO_INCREMENT,
    detail VARCHAR(2000),
    order_id INT,
    PRIMARY KEY(id)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

5.2.1.3.重启Mycat，让配置生效

mycat console

5.2.1.4.访问Mycat 向orders_detail 表插入数据

mysql -umycat -p -P 8066 -h 192.168.147.128
123456

#切换到TESTDB
use TESTDB;
# mycat中不支持一次对子表插入多条记录
# 拆成单条SQL语句插入即可
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_detail;
mysql> INSERT INTO orders_detail(id,detail,order_id) values 
    -> (1,'detail1',1),(2,'detail1',2),(3,'detail1',3), 
    -> (4,'detail1',4),(5,'detail1',5),(6,'detail1',6);
ERROR 1064 (HY000): ChildTable multi insert not provided
mysql>

在这里插入图片描述
在这里插入图片描述

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

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

在这里插入图片描述

5.2.2.水平分表--全局表

在分片的情况下，当业务表因为规模而进行分片以后，业务表与这些附属的字典表之间的关联，就成了比较棘手的问题，考虑到字典表具有以下几个特性：
  • 变动不频繁
  • 数据量总体变化不大
  • 数据规模不大，很少有超过数十万条记录
鉴于此，Mycat 定义了一种特殊的表，称之为“全局表”，全局表具有以下特性：
  • 全局表的插入、更新操作会实时在所有节点上执行，保持各个分片的数据一致性
  • 全局表的查询操作，只从一个节点获取
  • 全局表可以跟任何一个表进行 JOIN 操作

将字典表或者符合字典表特性的一些表定义为全局表，则从另外一个方面，很好的解决了数据JOIN 的难题。

通过全局表+基于 E-R 关系的分片策略，Mycat 可以满足 80%以上的企业应用开发

5.2.2.1.修改schema.xml 配置文件

通过数据冗余实现拆分效果

        <!-- 虚拟逻辑库配置  与server.xml 中的数据库对应 ,TESTDB是逻辑数据库，指向映射的实际数据库-->
        <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
                <!-- 逻辑表配置 -->
                <table name="customer" dataNode="dn2"></table>
                <!-- 为orders 表设置数据节点为 dn1、dn2，并指定分片规则为 mod_rule（自定义的名字） -->
                <table name="orders" dataNode="dn1,dn2"  rule="mod_rule" >
                        <childTable name="orders_detail" primaryKey="id" joinKey="order_id" parentKey="id" />
                </table>
                <!-- 全局表 -->
                <table name="dict_order_type" dataNode="dn1,dn2" type="global" ></table> 
        </schema>

在这里插入图片描述

5.2.2.2.在dn2【host129】创建dict_order_type 表【订单状态字典表】

#订单状态字典表 rows:20条
CREATE TABLE dict_order_type(
    id INT AUTO_INCREMENT,
    order_type VARCHAR(200),
    PRIMARY KEY(id)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

5.2.2.3.重启Mycat，让配置生效

mycat console

5.2.2.4.访问Mycat 向dict_order_type 表插入数据

mysql -umycat -p -P 8066 -h 192.168.147.128
123456

#切换到TESTDB
use TESTDB;
# mycat中不支持一次对子表插入多条记录
# 拆成单条SQL语句插入即可
INSERT INTO dict_order_type(id,order_type) VALUES(101,'type1');
INSERT INTO dict_order_type(id,order_type) VALUES(102,'type2');

5.2.2.5.在Mycat、dn1、dn2中查询表数据

select * from dict_order_type;

在这里插入图片描述

5.3.常用分片规则

5.3.1.取模

此规则为对分片字段求模运算。也是水平分表最常用规则。5.1.4.配置分表中，orders 表采用了此规则。

5.3.2.分片枚举

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

5.3.2.1.修改schema.xml配置文件

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

在这里插入图片描述

5.3.2.2.修改rule.xml配置文件

        <tableRule name="sharding_by_intfile">
                <rule>
                        <columns>areacode</columns>
                        <algorithm>hash-int</algorithm>
                </rule>
        </tableRule>

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

在这里插入图片描述

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

# mapFile：标识配置文件名称
# type：0为int型、非0为String
# `areacode` VARCHAR(20) comment '区域编号'  type为1

# defaultNode：默认节点:小于 0 表示不设置默认节点，大于等于 0 表示设置默认节点
# 设置默认节点如果碰到不识别的枚举值，就让它路由到默认节点，如不设置默认节点遇到不识别就报错

在这里插入图片描述

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

vi /usr/local/mycat/conf/partition-hash-int.txt
110=0
120=1
# 不能有空格，0代表dn1,1代表dn2

5.3.2.4.重启Mycat，让配置生效

mycat console

5.3.2.5.访问Mycat创建表【订单归属区域信息表】

mysql -umycat -p -P 8066 -h 192.168.147.128
123456

#切换到TESTDB
use TESTDB;
-- DROP TABLE IF EXISTS orders_ware_info;
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)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

select * from orders_ware_info;

在这里插入图片描述

5.3.2.6.访问Mycat插入数据

# mycat中不支持一次对子表插入多条记录
# 拆成单条SQL语句插入即可
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,'上海','110');
INSERT INTO orders_ware_info(id,order_id,address,areacode) VALUES (3,3,'杭州','110');
INSERT INTO orders_ware_info(id,order_id,address,areacode) VALUES (4,4,'深圳','110');
INSERT INTO orders_ware_info(id,order_id,address,areacode) VALUES (5,5,'苏州','120');
INSERT INTO orders_ware_info(id,order_id,address,areacode) VALUES (6,6,'天津','120');

在这里插入图片描述

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

select * from orders_ware_info;
select @@hostname;

在这里插入图片描述

5.3.3.范围约定

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

5.3.3.1.修改schema.xml配置文件

<!-- 范围约定算法 -->
<table name="payment_info" dataNode="dn1,dn2" rule="auto_sharding_long" ></table>

在这里插入图片描述

5.3.3.2.修改rule.xml配置文件

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

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

# columns：分片字段，algorithm：分片函数
# mapFile：标识配置文件名称

# defaultNode：默认节点:小于 0 表示不设置默认节点，大于等于 0 表示设置默认节点，
# 设置默认节点如果碰到不识别的枚举值，就让它路由到默认节点，如不设置不识别就报错

在这里插入图片描述

在这里插入图片描述

5.3.3.3.修改autopartition-long.txt配置文件

0-102=0
103-200=1

5.3.3.4.重启Mycat，让配置生效

mycat console

5.3.3.5.访问Mycat创建表【支付信息表】

mysql -umycat -p -P 8066 -h 192.168.147.128
123456

#切换到TESTDB
use TESTDB;
#支付信息表   
CREATE TABLE  payment_info( 
    `id` INT AUTO_INCREMENT comment '编号',
    `order_id` INT comment '订单编号',
    `payment_status` INT comment '支付状态',
    PRIMARY KEY(id)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

5.3.3.6.访问Mycat插入数据

# mycat中不支持一次对子表插入多条记录
# 拆成单条SQL语句插入即可
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);

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

select * from payment_info;
select @@hostname;

在这里插入图片描述

5.3.4.按日期（天）分片

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

5.3.4.1.修改schema.xml配置文件

<!-- 按日期（天）分片 -->
<table name="login_info" dataNode="dn1,dn2" rule="sharding_by_date" ></table>

在这里插入图片描述

5.3.4.2.修改rule.xml配置文件

        <tableRule name="sharding_by_date">
                <rule>
                        <columns>login_date</columns>
                        <algorithm>shardingByDate</algorithm>
                </rule>
        </tableRule>

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

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

5.3.4.3.重启Mycat，让配置生效

mycat console

5.3.4.4.访问Mycat创建表【用户信息表】

mysql -umycat -p -P 8066 -h 192.168.147.128
123456

#切换到TESTDB
use TESTDB;
#用户信息表   
CREATE TABLE  login_info (
    `id` INT AUTO_INCREMENT comment '编号',
    `user_id` INT comment '用户编号',
    `login_date` date comment '登录日期',
    PRIMARY KEY(id)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

5.3.4.5.访问Mycat插入数据

# mycat中不支持一次对子表插入多条记录
# 拆成单条SQL语句插入即可
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');

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

select * from login_info;
select @@hostname;

在这里插入图片描述

5.4.全局序列

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

为此，Mycat 提供了全局 sequence，并且提供了包含本地配置和数据库配置等多种实现方式

5.4.1.本地文件

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

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

5.4.2.数据库方式

利用数据库一个表来进行计数累加。

但是并不是每次生成序列都读写数据库，这样效率太低。

Mycat 会预加载一部分号段到 Mycat 的内存中，这样大部分读写序列都是在内存中完成的。

如果内存中的号段用完了 Mycat 会再向数据库要一次。

问题：那如果Mycat 崩溃了 ，那内存中的序列岂不是都没了？

是的。如果是这样，那么Mycat 启动后会向数据库申请新的号段，原有号段会弃用。

也就是说如果Mycat 重启，那么损失是当前的号段没用完的号码，但是不会因此出现主键重复

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

select * from MYCAT_SEQUENCE;

在这里插入图片描述

5.4.2.2.修改Mycat 配置

5.4.2.2.1.修改sequence_db_conf.properties
vi /usr/local/mycat/conf/sequence_db_conf.properties
#sequence stored in datanode
GLOBAL=dn1
COMPANY=dn1
CUSTOMER=dn1
ORDERS=dn1
# ORDERS这个序列在dn1这个节点上，具体dn1节点是哪台机子，请参考schema.xml
5.4.2.2.2.修改server.xml
vi /usr/local/mycat/conf/server.xml

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

在这里插入图片描述

5.4.2.2.3.重启Mycat，让配置生效
mycat console

5.4.2.3.验证全局序列

5.4.2.3.1.登录Mycat，插入数据
mysql -umycat -p -P 8066 -h 192.168.147.128
123456

#切换到TESTDB
use TESTDB;

insert into orders(id,amount,customer_id,order_type) values(next value for MYCATSEQ_ORDERS,1000,101,102);
insert into orders(id,amount,customer_id,order_type) values(next value for MYCATSEQ_ORDERS,1001,101,102);
5.4.2.3.2.查询数据
select * from orders;

在这里插入图片描述

5.4.2.3.3.重启Mycat后，再次插入数据，再查询
use TESTDB;
insert into orders(id,amount,customer_id,order_type) values(next value for MYCATSEQ_ORDERS,1003,103,103);
insert into orders(id,amount,customer_id,order_type) values(next value for MYCATSEQ_ORDERS,1004,104,104);
select * from orders;

在这里插入图片描述

5.4.3.时间戳方式

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

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

5.4.4.自主生成全局序列

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

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

六、基于HA 机制的Mycat 高可用

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

6.1.高可用方案

我们可以使用HAProxy + Keepalived 配合两台 Mycat 搭起 Mycat 集群，实现高可用性。

HAProxy 实现了 MyCat 多节点的集群高可用和负载均衡， 而 HAProxy 自身的高可用则可以通过Keepalived 来实现。

在这里插入图片描述

编号角色IP地址机器名
1Mycat1192.168.147.128host128
2Mycat2192.168.147.129host129
3HAProxy(master)192.168.147.130host130
4Keepalived(master)192.168.147.130host130
5HAProxy(backup)192.168.147.131host131
6Keepalived(backup)192.168.147.131host131

6.2.安装配置HAProxy

6.2.1.准备好HAProxy安装包，传到/opt目录下

官网：https://www.haproxy.org/

cd /opt/

wget https://www.haproxy.org/download/1.5/src/haproxy-1.5.19.tar.gz

在这里插入图片描述

在这里插入图片描述

6.2.2.解压到/usr/local/src

tar -xzvf haproxy-1.5.19.tar.gz -C /usr/local/src/

6.2.3.进入解压后的目录，查看内核版本，进行编译

cd /usr/local/src/haproxy-1.5.19

# 查看内核版本
uname -r
# 3.10.0-1160.71.1.el7.x86_64

# 执行Linux的make命令进行编译
make TARGET=linux310 PREFIX=/usr/local/haproxy ARCH=x86_64

# ARGET=linux310，内核版本，使用uname -r查看内核，如：3.10.0-1160.71.1.el7，此时该参数就为linux310
# ARCH=x86_64，系统位数
# PREFIX=/usr/local/haprpxy 
# /usr/local/haprpxy，为haprpxy安装路径。

在这里插入图片描述

6.2.4.编译完成后，进行安装

make install PREFIX=/usr/local/haproxy

在这里插入图片描述

6.2.5.安装完成后，创建目录、创建HAProxy配置文件

# 将解压后目录下的配置模版文件拷贝到程序运行目录的配置文件目录下
cp /usr/local/src/haproxy-1.5.19/examples/haproxy.cfg /usr/local/haproxy/haproxy.cfg

# 创建目录
mkdir -p /usr/data/haproxy/

vim /usr/local/haproxy/haproxy.conf

6.2.6.向配置文件中插入以下配置信息,并保存

# 全局配置，定义haproxy进程的工作特性和全局配置
global
    # 定义全局的 syslog 服务器，最多可以定义两个
    log 127.0.0.1    local0
    #log 127.0.0.1    local1 notice
    #log loghost    local0 info
    maxconn 4096
    # chroot运行的路径，，当前目录为指定目录，设置超级用户权限启动进程，提高安全性
    chroot /usr/local/haproxy 
    # haproxy pid的存放位置
    pidfile /usr/data/haproxy/haproxy.pid 
    uid 99
    gid 99
    # 守护进程的方式在后台工作
    daemon
    #debug
    #quiet

# 默认配置
defaults
    # 日志继承全局配置段的设置
    log     global
    mode    tcp 
    option  abortonclose
      option  redispatch
      # 重试次数为3次，失败3次以后则表示服务不可用
      retries 3 
    maxconn 2000 
    # 连接超时时间，表示客户端请求转发至服务器所等待的时长
    timeout connect 5000 
    # 客户端超时时间，表示客户端非活跃状态的时间
    timeout client  50000 
    # 服务器超时时间，表示客户端与服务器建立连接后，等待服务器的超时时间
    timeout server  50000
    
listen proxy_status
    # bind后面必须有空格然后加冒号，否则会报错
    bind :48066
        mode tcp 
        balance roundrobin 
        server mycat_1 192.168.147.128:8066 check inter 10s 
        server mycat_2 192.168.147.129: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
# 控制台的用户名admin 密码123123

6.2.7.放行8066端口

systemctl status firewalld

# --add-port=8066/tcp，表示添加指定8066端口，格式为：端口/通讯协议
# --permanent表示设置为持久
# --zone  表示作用域
firewall-cmd --zone=public --add-port=8066/tcp --permanent

# --remove-port表示移除指定端口
firewall-cmd --zone=public --remove-port=8066/tcp --permanent

# 重新加载防火墙
firewall-cmd --reload

# 再次查询开放端口
firewall-cmd --list-ports

# 查看已配置规则
firewall-cmd --list-all

6.2.8.启动验证--启动HAProxy

/usr/local/haproxy/sbin/haproxy -f /usr/local/haproxy/haproxy.conf

6.2.9.查看HAProxy进程

ps -ef | grep haproxy

在这里插入图片描述

6.2.10.haproxy安装成系统服务

# 创建脚本
vi /usr/lib/systemd/system/haproxy.service

# 重新加载系统服务
systemctl daemon-reload

systemctl start haproxy
systemctl stop haproxy
systemctl status haproxy
[Unit]
Description=HAProxy Load Balancer
Documentation=https://www.haproxy.org/
After=network.target

[Service]
Type=forking
PIDFile=/usr/data/haproxy/haproxy.pid
ExecStart=/usr/local/haproxy/sbin/haproxy -f /usr/local/haproxy/haproxy.conf
ExecReload=/bin/kill -s HUP $MAINPID
ExecStop=/bin/kill -s TERM $MAINPID
PrivateTmp=true

[Install]
WantedBy=default.target

在这里插入图片描述

6.2.11.Mycat主备机均启动

mycat console

在这里插入图片描述

在这里插入图片描述

6.2.12.打开浏览器访问

http://192.168.147.130:7777/admin

#在弹出框输入用户名：admin密码：123123

在这里插入图片描述
在这里插入图片描述
在这里插入图片描述

在这里插入图片描述
在这里插入图片描述

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

mysql -umycat -p -P 48066 -h 192.168.147.130
123456

在这里插入图片描述
在这里插入图片描述

6.3.配置Keepalived

keepalived官网地址：https://keepalived.org/

6.3.1.准备好Keepalived安装包，传到/opt目录下

cd /opt/

wget https://keepalived.org/software/keepalived-2.2.8.tar.gz

在这里插入图片描述

6.3.2.解压到/usr/local/src

tar -zxvf keepalived-2.2.8.tar.gz -C /usr/local/src

6.3.3.安装依赖插件

yum install -y gcc openssl-devel popt-devel libnl libnl-devel

6.3.4.进入解压后的目录，进行配置，进行编译

cd /usr/local/src/keepalived-2.2.8

./configure --prefix=/usr/local/keepalived

6.3.5.进行编译，完成后进行安装

make && make install

6.3.6.运行前配置

cp /usr/local/src/keepalived-2.2.8/keepalived/etc/init.d/keepalived /etc/init.d/ 

mkdir /etc/keepalived 

cp /usr/local/keepalived/etc/keepalived/keepalived.conf.sample /etc/keepalived/keepalived.conf

cp /usr/local/src/keepalived-2.2.8/keepalived/etc/sysconfig/keepalived /etc/sysconfig/ 

cp /usr/local/keepalived/sbin/keepalived /usr/sbin/

6.3.7.修改配置文件

vim /etc/keepalived/keepalived.conf
#修改内容如下
! Configuration File for keepalived

global_defs { 
    notification_email { 
        coon@foxmail.com 
       }
       notification_email_from keepalived@joy.com    
       smtp_server 127.0.0.1 
       smtp_connect_timeout 30 
    router_id LVS_DEVEL 
       vrrp_skip_check_adv_addr 
       vrrp_garp_interval 0 
       vrrp_gna_interval 0 
}
vrrp_instance VI_1 {
    #主机配MASTER，备机配BACKUP 
    state MASTER 
    #所在机器网卡 
    interface ens33 
    virtual_router_id 51 
    #数值越大优先级越高 
    priority 100 
    advert_int 1 
    authentication{
        auth_type PASS 
        auth_pass 1111 
    } 
    virtual_ipaddress { 
        #虚拟IP 
        192.168.147.200 
    } 
} 
virtual_server 192.168.147.200 48066 { 
    delay_loop 6 
    lb_algo rr 
    lb_kind NAT     
    persistence_timeout 50     
    protocol TCP 
    # HAProxy主机
    real_server 192.168.147.130 48066 {
        weight 1 
        TCP_CHECK { 
            connect_timeout 3 
            retry 3 
            delay_before_retry 3
        } 
    } 
    # HAProxy从机
    real_server 192.168.147.131 48600 {  
        weight 1 
        TCP_CHECK { 
            connect_timeout 3 
            retry 3 
            delay_before_retry 3
        } 
    } 
    
}

6.3.8.启动验证--启动Keepalived

systemctl start keepalived
systemctl stop keepalived
systemctl status keepalived

在这里插入图片描述
在这里插入图片描述

6.3.9.登录验证

mysql -umycat -p -P 48066 -h 192.168.147.200
123456

在这里插入图片描述
在这里插入图片描述

6.4.测试高可用

6.4.1.关闭mycat

在这里插入图片描述

6.4.2.通过虚拟ip查询数据

mysql -umycat -p123456 -h 192.168.147.200 -P 48066

在这里插入图片描述
在这里插入图片描述

七、Mycat 安全设置

7.1.权限配置

7.1.1.user 标签权限控制

目前Mycat 对于中间件的连接控制并没有做太复杂的控制，目前只做了中间件逻辑库级别的读写权限控制。
是通过server.xml 的user 标签进行配置。

7.1.1.1.修改配置文件server.xml

vi /usr/local/mycat/conf/server.xml
#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>

在这里插入图片描述

7.1.1.2.配置说明

标签属性说明
name应用连接中间件逻辑库的用户名
password该用户对应的密码
TESTDB应用当前连接的逻辑库中所对应的逻辑表。schemas 中可以配置一个或多个
readOnly应用连接中间件逻辑库所具有的权限。true 为只读，false 为读写都有，默认为 false

7.1.2.测试案例一

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

7.1.2.1.用user用户登录，运行命令如下

mysql -uuser -puser -h 192.168.147.128 -P8066

在这里插入图片描述

7.1.2.2.切换到TESTDB数据库，查询orders表数据

use TESTDB;

select * from orders;

7.1.2.3.查询到数据，如图

在这里插入图片描述

7.1.2.4.执行插入数据sql

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

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

在这里插入图片描述

7.1.3.测试案例二

  • 使用mycat用户，权限为可读写（readOnly：false）
  • 验证是否可以查询出数据，验证是否可以写入数据

7.1.3.1.用mycat用户登录，运行命令如下

mysql -umycat -p123456 -h 192.168.147.128 -P8066

在这里插入图片描述

7.1.3.2.切换到TESTDB数据库，查询orders表数据

use TESTDB;

select * from orders;

7.1.3.3.查询到数据

在这里插入图片描述

7.1.3.4.执行插入数据sql

insert into orders(id,order_type,customer_id,amount) values(7,101,101,10000);
insert into orders(id,order_type,customer_id,amount) values(next value for MYCATSEQ_ORDERS,101,101,10000);

7.1.3.5.看到运行结果，插入成功

在这里插入图片描述

7.1.4.privileges 标签权限控制

在user 标签下的privileges 标签可以对逻辑库（schema）、表（table）进行精细化的DML 权限控制。

privileges 标签下的check 属性，如为true 开启权限检查，为 false 不开启，默认为 false。

由于 Mycat 一个用户的 schemas 属性可配置多个逻辑库（schema） ，所以 privileges 的下级节点 schema 节点同样可配置多个，对多库多表进行细粒度的 DML 权限控制。

7.1.4.1.修改server.xml配置文件privileges部分

vi /usr/local/mycat/conf/server.xml
#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>

在这里插入图片描述

7.1.4.2.配置说明

DML 权限增加（insert）更新（update）查询（select）删除（select）
0000禁止禁止禁止禁止
0010禁止禁止可以禁止
1110可以禁止禁止禁止
1111可以可以可以可以

7.1.5.测试案例一

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

7.1.5.1.重启mycat，用mycat用户登录，运行命令

mysql -umycat -p123456 -h 192.168.147.128 -P8066

7.1.5.2.切换到TESTDB数据库，查询orders表数据

use TESTDB;

select * from orders;

7.1.5.3.禁止该用户查询数据

在这里插入图片描述

7.1.5.4.执行插入数据sql

insert into orders(id,order_type,customer_id,amount) values(8,101,101,10000);
insert into orders(id,order_type,customer_id,amount) values(next value for MYCATSEQ_ORDERS,105,102,10000);

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

在这里插入图片描述

7.1.6.测试案例二

  • 使用mycat用户，privileges配置orders表权限为禁止增删改查（dml="1111"）
  • 验证是否可以查询出数据，验证是否可以写入数据
vi /usr/local/mycat/conf/server.xml

在这里插入图片描述

7.1.6.1.重启mycat，用mycat用户登录，运行命令

mysql -umycat -p123456 -h 192.168.147.128 -P8066

7.1.6.2.切换到TESTDB数据库，查询orders表数据

use TESTDB;

select * from orders;

7.1.6.3.可以查询到数据，如图

在这里插入图片描述

7.1.6.4.执行插入数据sql

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

7.1.6.5.运行结果，插入成功

在这里插入图片描述

7.1.6.6.执行删除数据sql

delete from orders where id in (7,8);

7.1.6.7.运行结果，删除成功

在这里插入图片描述

7.2.SQL 拦截

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

7.2.1.白名单

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

7.2.1.1.设置白名单

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

在这里插入图片描述

7.2.1.2.重启Mycat后，192.168.147.129主机使用mycat用户访问

mysql -umycat -p123456 -h 192.168.140.128 -P 8066

7.2.1.3.可以正常访问

在这里插入图片描述

7.2.1.4.在此主机换user用户访问，禁止访问

mysql -uuser -puser -h 192.168.147.128 -P8066

在这里插入图片描述

7.2.1.5.在192.168.147.130主机用mycat用户访问，禁止访问

mysql -umycat -p -h 192.168.147.128 -P 8066
123456

在这里插入图片描述

7.2.2.黑名单

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

7.2.2.1.设置黑名单

vi /usr/local/mycat/conf/server.xml
#设置黑名单 
#server.xml配置文件firewall标签 
#配置禁止mycat用户进行删除操作
<firewall> 
    <whitehost> 
          <host host="192.168.147.129" user="mycat"/> 
      </whitehost> 
      <blacklist check="true"> 
          <property name="deleteAllow">false</property> 
      </blacklist> 
</firewall>

在这里插入图片描述

7.2.2.2.重启Mycat后，192.168.147.129主机使用mycat用户访问

mysql -umycat -p -h 192.168.147.128 -P 8066
123456

7.2.2.3.可以正常访问

在这里插入图片描述

7.2.2.4.切换TESTDB数据库后，执行删除数据语句

use TESTDB;

select * from orders;

delete from orders where id = 6;

7.2.2.5.运行后发现已禁止删除数据

在这里插入图片描述

7.2.3.可以设置的黑名单SQL 拦截功能列表

配置项缺省值描述
selelctAllowtrue是否允许执行 SELECT 语句
deleteAllowtrue是否允许执行 DELETE 语句
updateAllowtrue是否允许执行 UPDATE 语句
insertAllowtrue是否允许执行 INSERT 语句
createTableAllowtrue是否允许创建表
setAllowtrue是否允许使用 SET 语法
alterTableAllowtrue是否允许执行 Alter Table 语句
dropTableAllowtrue是否允许修改表
commitAllowtrue是否允许执行 commit 操作
rollbackAllowtrue是否允许执行 roll back 操作

八、 ZooKeeper集群搭建

ZooKeeper官网下载地址：https://zookeeper.apache.org/releases.html

apache下载：https://downloads.apache.org/

在这里插入图片描述

8.1.ZooKeeper简介

ZooKeeper是一个分布式的，开放源码的分布式应用程序协调服务，是Google的Chubby一个开源的实现，是Hadoop和Hbase的重要组件。

ZooKeeper是一个分布式数据一致性的解决方案，分布式应用可以基于它实现发布订阅、负载均衡、命名服务、协调通知、集群管理、master选举、分布式锁和分布式队列的特性。

8.2.数据复制的好处

  • 容错：一个节点出错，不至于让整个集群无法提供服务
  • 扩展性：通过增加服务器节点能提高 ZooKeeper 系统的负载能力，把负载分布到多个节点上
  • 高性能：客户端可访问本地 ZooKeeper 节点或者访问就近的节点，依次提高用户的访问速度

8.3.Zookeeper设计目的

Zookeeper的设计目的是提供高性能（简单的数据模型），高可用（构建集群），顺序一致性（严格顺序访问）的分布式协调服务，保证数据一致性。

    1. 最终一致性：client不论连接到哪个Server，展示给它都是同一个视图，这是zookeeper最重要的性能。
    1. 可靠性：具有简单、健壮、良好的性能，如果消息被到一台服务器接受，那么它将被所有的服务器接受。
    1. 实时性：Zookeeper保证客户端将在一个时间间隔范围内获得服务器的更新信息，或者服务器失效的信息。但由于网络延时等原因，Zookeeper不能保证两个客户端能同时得到刚更新的数据，如果需要最新数据，应该在读数据之前调用sync()接口。
    1. 等待无关（wait-free）：慢的或者失效的client不得干预快速的client的请求，使得每个client都能有效的等待。
    1. 原子性：更新只能成功或者失败，没有中间状态。
    1. 顺序性：包括全局有序和偏序两种：全局有序是指如果在一台服务器上消息a在消息b前发布，则在所有Server上消息a都将在消息b前被发布；偏序是指如果一个消息b在消息a后被同一个发送者发布，a必将排在b前面。

8.4.zookeeper集群包括3种角色

zookeeper集群包括3种角色：leader（核心）、follower、observer

8.4.1.Leader角色

Leader是整个 zookeeper 集群的核心，Leader 主要有三个功能：

  • 集群异常时从快照和日志中恢复数据
  • 维持与 Learner 的心跳，接收 Learner 请求并判断 Learner 的请求消息类型
  • 根据不同的消息类型，进行不同的处理。
    Learner 的消息类型主要有：
  • PING 消息：指Learner 的心跳信息
  • REQUEST 消息：Follower 发送的提议信息，包括读写请求和同步请求
  • ACK 消息：Follower 对提议的回复，超过半数的 Follower 通过，则 commit 该提议

  • REVALIDATE 消息：用来延长 Session 有效时间

    在这里插入图片描述

8.4.2.Follower 角色

在Zookeeper集群内部，Follower 主要有四个功能：

  • 向 Leader 发送请求（PING 消息、REQUEST 消息、ACK 消息、REVALIDATE 消息）；
  • 接收 Leader 消息并进行处理；
  • 接收 Client 的请求，如果为写请求，则转发给 Leader进行投票；

  • 返回 Client 结果。

    Leader 的消息有：

  • PING 消息： 心跳消息；
  • PROPOSAL 消息：Leader 发起的提案，要求 Follower 投票；
  • COMMIT 消息：服务器端最新一次提案的信息；
  • UPTODATE 消息：表明同步完成；
  • REVALIDATE 消息：根据 Leader 的 REVALIDATE 结果，关闭待 revalidate 的 session 还是允许其接受消息；
  • SYNC 消息：返回 SYNC 结果到客户端，这个消息最初由客户端发起，用来强制得到最新的更新。

在这里插入图片描述

8.4.3.Observer 角色

  • Observer 是 zookeeper3.3 开始引入的一个全新的服务器角色，观察者角色。
  • 观察 zookeeper 集群中的最新状态变化并将这些状态变化同步到 observer 服务器上。
  • Observer 流程和 Follower 的唯一不同的地方就是 Observer 不会参加 Leader 发起的投票，也不会被选举为 Leader。

8.5.zookeeper集群工作流程

zookeeper集群只能有一个leader节点，由所有follower选举产生，只有leader节点才有权利发起修改数据的操作，而follower节点即使接收到了客户端发起的修改操作，也要将其转交给leader来处理，leader接收到修改数据的请求后，会向所有follower广播一条消息，让他们执行某项操作，follower 执行完后，便会向 leader 回复执行完毕。当 leader 收到半数以上的 follower 的确认消息，便会判定该操作执行完毕，然后向所有 follower 广播该操作已经生效。

8.6.zookeeper集群节点数量为奇数，建议至少3个节点

如果要搭建一个能够允许N台机器down掉的集群，那么就要部署2*N+1台服务器构成的 zookeeper 集群

8.7.ZooKeeper 集群搭建

确保关闭防火墙
机器名称IP服务器角色环境要求
host128192.168.147.128zk节点1CentOS7.9 + zk 3.8.3+JDK1.8
host129192.168.147.129zk节点2CentOS7.9 + zk 3.8.3+JDK1.8
host130192.168.147.130zk节点3CentOS7.9 + zk 3.8.3+JDK1.8

8.7.1.下载ZooKeeper安装包并解压

apache下载：https://downloads.apache.org/

ZooKeeper官网下载地址：https://zookeeper.apache.org/releases.html

cd /opt/

wget https://downloads.apache.org/zookeeper/zookeeper-3.8.3/apache-zookeeper-3.8.3-bin.tar.gz

tar -xzvf apache-zookeeper-3.8.3-bin.tar.gz -C /usr/local/src

在这里插入图片描述
在这里插入图片描述

在这里插入图片描述

8.7.2.进入ZooKeeper解压后的配置目录（conf），复制配置文件并改名为zoo.cfg

# 更名
cd /usr/local/src
mv apache-zookeeper-3.8.3-bin zookeeper

cd /usr/local/src/zookeeper/conf

cp /usr/local/src/zookeeper/conf/zoo_sample.cfg /usr/local/src/zookeeper/conf/zoo.cfg

8.7.3.修改zookeeper数据文件存放目录

dataDir=/data/zookeeper

8.7.4.修改zoo.cfg 配置文件内容为

vi /usr/local/src/zookeeper/conf/zoo.cfg
# tickTime这个时间是作为zookeeper服务器之间或客户端与服务器之间维持心跳的时间间隔为2ms,也就是说每隔tickTime时间就会发送一个心跳。
tickTime=2000

# initLimit这个配置项是用来配置zookeeper接受客户端
# （这里所说的客户端不是用户连接zookeeper服务器的客户端,而是zookeeper服务器集群中连接到leader的follower 服务器）
# 初始化连接时最长能忍受多少个心跳时间间隔数。当初始化连接时间超过该值，则表示连接失败。
# 当已经超过10个心跳的时间（也就是tickTime）长度后 zookeeper 服务器还没有收到客户端的返回信息,那么表明这个客户端连接失败。
# 对于从节点最初连接到主节点时的超时时间，单位为tick值的倍数。总的时间长度就是 10*2000。即20ms
initLimit=10

# syncLimit这个配置项标识leader与follower之间发送消息,请求和应答时间长度,最长不能超过多少个tickTime的时间长度
# 如果follower在设置时间内不能与leader通信，那么此follower将会被丢弃。
# 对于主节点与从节点进行同步操作时的超时时间，单位为tick值的倍数。总的时间长度就是5*2000。即10ms
syncLimit=5

# dataDir就是zookeeper保存数据库数据快照的位置,默认情况下zookeeper将写数据的日志文件也保存在这个目录里
# 注意：不能使用 /tmp 路径，会被定期清除。使用专用的存储设备能够大大提高系统的性能
# dataDir=/tmp/zookeeper
dataLogDir=/opt/zookeeper/logs
# 数据文件存放目录
dataDir=/opt/zookeeper/data

# clientPort这个端口就是客户端连接Zookeeper服务器的端口,Zookeeper会监听这个端口接受客户端的访问请求
clientPort=2181

# 客户端最大链接数
maxClientCnxns=60

# zookeeper在运行过程中会生成快照数据，默认不会自动清理，会持续占用硬盘空间
# 保存3个快照，即3个日志文件
autopurge.snapRetainCount=3
# 间隔1个小时执行一次清理
autopurge.purgeInterval=1

# server.A=B:C:D
# 其中A是一个数字,表示这个是第几号服务器
# B是这个服务器的IP地址
# C第一个端口用来集群成员的信息交换,表示这个服务器与集群中的leader服务器交换信息的端口
# D是在leader挂掉时专门用来进行选举leader选举所用的端口
# 服务器=运行主机：心跳端口：选举端口
# zk集群
# 3888后面位置不能有空格，否则Address unresolved: 192.168.147.128:3888
server.1=192.168.147.128:2888:3888
server.2=192.168.147.129:2888:3888
server.3=192.168.147.130:2888:3888
#创建相关目录，三台节点都需要
mkdir -p /opt/zookeeper/{logs,data}

# 赋予权限
chmod 777 /opt/zookeeper/{logs,data}

8.7.5.创建ServerID标识

# 在三台服务器的主配置文件 zoo.cfg 中指定的 dataDir 目录下 新建myid文件，并写入对应的server.num 中的num数字
# 如：在192.168.147.128上将server.1中1写入myid
# 如：在192.168.147.129上将server.2中2写入myid
# 数据文件存放目录 dataDir=/opt/zookeeper/data

echo 1 >/opt/zookeeper/data/myid

cat /opt/zookeeper/data/myid

在这里插入图片描述
在这里插入图片描述

在这里插入图片描述

8.7.6.添加到环境变量

vim /etc/profile
# zookeeper
export ZK_HOME=/usr/local/src/zookeeper
export PATH=$PATH:$ZK_HOME/bin
# 输入下面命令让设置的环境变量生效
source /etc/profile

8.7.7.ZooKeeper启动命令

zkServer.sh start
zkServer.sh status
zkServer.sh stop
zkServer.sh restart
#以打印日志方式启动
zkServer.sh start-foreground

# jps 是 Java Process Status Tool 的简称,它的作用是为了列出所有正在运行中的 Java 虚拟机进程
# 每一个 Java 程序在启动的时候都会为之创建一个Jvm 实例,通过jps可以查看这些进程的相关信息
# jps是Jdk提供的一个工具,它安装在 JAVA_HOME/bin下

jps

在这里插入图片描述

8.7.8.ZooKeeper服务端口为2181，查看服务已经启动

ps -aux | grep zookeeper

netstat -ant | grep 2181

在这里插入图片描述

8.7.9.注意：Starting zookeeper … FAILED TO START

zookeeper版本只要 >= 3.5.5 版本启动时启动错误提示“Starting zookeeper … FAILED TO START”

问题原因：下载了错误的版本文件，Zookeeper 从3.5.5后开始拆分为两个版本，而且他们的结构还很类似。

标准版本（Apache ZooKeeper x.y.z ）下载的文件名为：apache-zookeeper-x.y.z-bin.tar.gz`
源码版本（Apache ZooKeeper x.y.z Source Release）下载的文件名为：apache-zookeeper-x.y.z.tar.gz
在安装时下载标准版本安装使用即可正常使用

8.7.10.Address unresolved

# 查看日志
cat /usr/local/src/zookeeper/logs/zookeeper-root-server-host128.out
[root@host128 conf]# zkServer.sh start
ZooKeeper JMX enabled by default
Using config: /usr/local/src/zookeeper/bin/../conf/zoo.cfg
Starting zookeeper ... FAILED TO START

[root@host128 logs]# cat zookeeper-root-server-host128.out 
2024-02-26 21:01:09,066 [myid:] - INFO  [main:o.a.z.s.q.QuorumPeerConfig@177] - Reading configuration from: /usr/local/src/zookeeper/bin/../conf/zoo.cfg
2024-02-26 21:01:09,075 [myid:] - INFO  [main:o.a.z.s.q.QuorumPeerConfig@440] - clientPortAddress is 0.0.0.0:2181
2024-02-26 21:01:09,076 [myid:] - INFO  [main:o.a.z.s.q.QuorumPeerConfig@444] - secureClientPort is not set
2024-02-26 21:01:09,076 [myid:] - INFO  [main:o.a.z.s.q.QuorumPeerConfig@460] - observerMasterPort is not set
2024-02-26 21:01:09,076 [myid:] - INFO  [main:o.a.z.s.q.QuorumPeerConfig@477] - metricsProvider.className is org.apache.zookeeper.metrics.impl.DefaultMetricsProvider
2024-02-26 21:01:09,086 [myid:] - ERROR [main:o.a.z.s.q.QuorumPeerMain@99] - Invalid config, exiting abnormally
org.apache.zookeeper.server.quorum.QuorumPeerConfig$ConfigException: Address unresolved: 192.168.147.128:3888  
        at org.apache.zookeeper.server.quorum.QuorumPeer$QuorumServer.initializeWithAddressString(QuorumPeer.java:359)
        at org.apache.zookeeper.server.quorum.QuorumPeer$QuorumServer.<init>(QuorumPeer.java:280)
        at org.apache.zookeeper.server.quorum.QuorumPeer$QuorumServer.<init>(QuorumPeer.java:275)
        at org.apache.zookeeper.server.quorum.flexible.QuorumMaj.<init>(QuorumMaj.java:96)
        at org.apache.zookeeper.server.quorum.QuorumPeerConfig.createQuorumVerifier(QuorumPeerConfig.java:654)
        at org.apache.zookeeper.server.quorum.QuorumPeerConfig.createQuorumVerifier(QuorumPeerConfig.java:640)
        at org.apache.zookeeper.server.quorum.QuorumPeerConfig.parseDynamicConfig(QuorumPeerConfig.java:685)
        at org.apache.zookeeper.server.quorum.QuorumPeerConfig.setupQuorumPeerConfig(QuorumPeerConfig.java:659)
        at org.apache.zookeeper.server.quorum.QuorumPeerConfig.parseProperties(QuorumPeerConfig.java:487)
        at org.apache.zookeeper.server.quorum.QuorumPeerConfig.parse(QuorumPeerConfig.java:194)
        at org.apache.zookeeper.server.quorum.QuorumPeerMain.initializeAndRun(QuorumPeerMain.java:125)
        at org.apache.zookeeper.server.quorum.QuorumPeerMain.main(QuorumPeerMain.java:91)
Invalid config, exiting abnormally
2024-02-26 21:01:09,086 [myid:] - INFO  [main:o.a.z.a.ZKAuditProvider@42] - ZooKeeper audit is disabled.
2024-02-26 21:01:09,087 [myid:] - ERROR [main:o.a.z.u.ServiceUtils@48] - Exiting JVM with code 2

在这里插入图片描述

8.8.Zookeeper集群查看【host129为leader】

zkServer.sh start
zkServer.sh status
ifconfig ens33
机器名称IP服务器角色
host128192.168.147.128follower
host129192.168.147.129leader
host130192.168.147.130follower

在这里插入图片描述
在这里插入图片描述
在这里插入图片描述

8.9.模拟集群故障--【host130为leader】

zkServer.sh stop

zkServer.sh status

在这里插入图片描述
在这里插入图片描述

8.10.Zookeeper客户端验证

Zookeeper自带客户端工具 zkCli.sh，可以实现连接服务、数据增删改查等功能

zkCli.sh -server 192.168.147.128:2181

zkCli.sh -server 192.168.147.130:2181

在这里插入图片描述

在这里插入图片描述

在这里插入图片描述

8.11.zkCli客户端支持操作命令

[zk: 192.168.147.128:2181(CONNECTED) 0] help
ZooKeeper -server host:port -client-configuration properties-file cmd args
        addWatch [-m mode] path # optional mode is one of [PERSISTENT, PERSISTENT_RECURSIVE] - default is PERSISTENT_RECURSIVE
        addauth scheme auth
        close 
        config [-c] [-w] [-s]
        connect host:port
        create [-s] [-e] [-c] [-t ttl] path [data] [acl]
        delete [-v version] path
        deleteall path [-b batch size]
        delquota [-n|-b|-N|-B] path
        get [-s] [-w] path
        getAcl [-s] path
        getAllChildrenNumber path
        getEphemerals path
        history 
        listquota path
        ls [-s] [-w] [-R] path
        printwatches on|off
        quit 
        reconfig [-s] [-v version] [[-file path] | [-members serverID=host:port1:port2;port3[,...]*]] | [-add serverId=host:port1:port2;port3[,...]]* [-remove serverId[,...]*]
        redo cmdno
        removewatches path [-c|-d|-a] [-l]
        set [-s] [-v version] path data
        setAcl [-s] [-v version] [-R] path acl
        setquota -n|-b|-N|-B val path
        stat [-w] path
        sync path
        version 
        whoami 
Command not found: Command not found help

九、Mycat 监控工具

9.1.Mycat-web 简介

Mycat-web 是Mycat 可视化运维的管理和监控平台，弥补了Mycat 在监控上的空白。
帮助 Mycat 分担统计任务和配置管理任务。

Mycat-web 引入了 ZooKeeper 作为配置中心，可以管理多个节点。

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

9.2.Mycat-web 配置使用

9.2.1.ZooKeeper 安装【上面有】

9.2.2.Mycat-web 安装

Mycat的官网：http://www.mycat.org.cn/

9.2.2.1.下载安装包

9.2.2.2.安装包拷贝到Linux系统/opt目录下，并解压

tar -xzvf Mycat-web-1.0-SNAPSHOT-20170102153329-linux.tar.gz

9.2.2.3.拷贝mycat-web文件夹到/usr/local目录下

cp -r mycat-web /usr/local

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

cd /usr/local/mycat-web/

./start.sh &

9.2.2.5.Mycat-web服务端口为8082，查看服务已经启动

netstat -ant | grep 8082

在这里插入图片描述

9.2.2.6.保证其它服务上线

# 关闭防火墙
systemctl stop firewalld

# 启动mysqld【host128、host129】
systemctl start mysqld
systemctl status mysqld

# 启动mycat【host128、host129】
mycat console

# 启动haproxy【host130、host131】
systemctl start haproxy
systemctl status haproxy

# 启动Keepalived【host130、host131】
systemctl start keepalived
systemctl status keepalived

# 启动ZooKeeper【host128、host129、host130】
zkServer.sh start
zkServer.sh status

在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述

9.2.2.7.通过地址访问服务

http://192.168.147.128:8082/mycat/

9.2.3.Mycat-web 配置

在这里插入图片描述

9.2.3.1.先在注册中心配置ZooKeeper地址，配置后刷新页面

在这里插入图片描述

9.2.3.2.新增Mycat监控实例

在这里插入图片描述
在这里插入图片描述
在这里插入图片描述

在这里插入图片描述

9.3.Mycat 性能监控指标

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

在这里插入图片描述

endl

0
暂无标签
版权属于： lilymaxyz
本文链接： https://www.hwpdyl.com/index.php/archives/27/
作品采用： 《 署名-非商业性使用-相同方式共享 4.0 国际 (CC BY-NC-SA 4.0) 》许可协议授权

评论

博主关闭了所有页面的评论