Mycat核心教程--mycat实战应用【一】

已于 2024-02-24 14:41:59 修改
阅读量1k 收藏 17
点赞数 10
分类专栏： mysql 文章标签： mycat
于 2024-02-24 13:53:01 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_45740503/article/details/136188685
版权

Mycat核心教程--mycat实战应用

一、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 自带全局序列。

endl

良辰美景好时光
关注
  • 10
    点赞
  • 踩
  • 17
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
专栏目录
通俗易懂的数据库中间件Mycat教程（含资料）
01-15
Mycat是一个开源的分布式数据库系统，其核心功能是分表分库，即将一个大表水平分割为多个小表，存储在后端MySQL或者其他数据库里。取名Mycat原因一是简单好记，另一个则是希望未来能够入驻 Apache，Apache的开源产品Tomcat也是一只猫。本视频涵盖Mycat核心技术主要知识点，常用功能均有实战练习。主要包含Mycat介绍原理、安装、一主一从读写分离、双主双从读写分离、分库分表、全局序列、高可用架构、安全设置、监控平台等章节。本课程适合有一定MySQL基础的学员，让学员能掌握数据库优化的解决方案，应用在真实项目系统搭建中。
最全的mycat配置教程
热门推荐
qq_45563131的博客
04-09 1万+
1.mycat 配置 首先说一下什么是mycat？ 直白话的意思就是说： 1.他是Java语言编写的MySQL数据库网络协议的、活跃的、性能好的数据库中间件，是mysql集群数据库中间件。基于心跳的自动故障切换，支持读写分离。支持分表分库。支持全局序列号，解决分布式下的主键生成问题。mycat 是阿里的开源数据库中间件cobar为基础，所以站在巨人肩膀。 2.原理： Mycat的原理并不复杂，复杂的是代码，如果代码也不复杂，那么早就成为一个传说了。 Mycat 的原理中最重要的一个动词是“拦截”
MyCat
RongYu__的博客
09-07 887
1.mycat的简单介绍；2.mycat的安装和测试
MySQL 案例实战--MySQL 基于Mycat实现读写分离
XY0918ZWQ的博客
02-07 466
MySQL 基于Mycat实现读写分离前言一、什么是读写分离？二、MySQL 读写分离解决方案三、MySQL 基于Mycat实现读写分离 前言 本环境是基于 Centos 7.8 系统构建MySQL-5.7.14 具体构建，请参考 MySQL-5.7.14 环境构建 在实际的生产环境中，对数据库的读和写都在同一个数据库服务器中，是不能满足实际需求的。无论是在安全性、高可用性还是高并发等各个方面都是完全不能满足实际需求的。因此，通过主从复制的方式来同步数据，再通过读写分离来提升数据库的并发负载能力。有点类
数据库中间MyCat最新硬核教程，主从复制，分库分表
10-20 558
如今随着互联网的发展，数据的量级也是成指数的增长，从 GB 到 TB 到 PB。对数据的各种操作也是愈加的困难，传统的关系性数据库已经无法满足快速查询与插入数据的需求。这个时候 NoSQL 的出现暂时解决了这一危机。它通过降低数据的安全性，减少对事务的支持，减少对复杂查询的支持，来获取性能上的提升。但是，在有些场合 NoSQL 一些折衷是无法满足使用场景的，就比如有些使用场景是绝对要有事务与安全指标的。这个时候 NoSQL 肯定是无法满足的，所以还是需要使用关系性数据库。
mycat2 install-template（mycat2-install-template-1.20.zip）
06-10
mycat2 install-template（mycat2-install-template-1.20.zip）
mycat2 install-template（mycat2-install-template-1.21.zip）
06-10
mycat2 install-template（mycat2-install-template-1.21.zip）
Mycat-server-1.6.6.1 linux版本
07-20
Mycat-server-1.6.6.1-release-20180908155252-linux.tar.gz
Mycat-Server-1.6（源码）
11-30
Mycat-Server-1.6 源码，可调试
Mycat-server-1.7.0-DEV-20170416134921-linux.tar.gz
11-12
 MyCat 是目前最流行的基于 java 语言编写的数据库中间件，是一个实现了 MySQL 协议的服务器，前端用户可以把它看作是一个数据库代理，用 MySQL 客户端工具和命令行访问，而其后端可以用 MySQL 原生协议与多个 ...
MySQL MyCAT安装应用经典案例实战
02-14
MySQL MyCAT安装应用经典案例实战
mycat入门到精通教程
07-14
mycat入门到精通教程
mycat 1.5 中文 教程
10-13
mycat1.5中文教程，大家也可以去官网免费下载。 https://github.com/MyCATApache/Mycat-Server
mysql+mycat视频教程
02-24
自己从淘宝上花5元购买的MySql集群搭建，读写分离，使用MyCat工具。如果能够帮助到你，麻烦给个好评谢谢！
MyCAT使用手册详细教程
08-14
2015年5月，由核心参与者们一起编写的第一本官方权威指南《Mycat权威指南》电子版发布，累计超过500本，成为开源项目中的首创。 截至2015年7月，超过100个项目采用Mycat，涵盖银行、电信、电子商务、物流、移动应用、O2O的众多领域和公司。
【mycat教程】 入门 mycat安装启动 搭建读写分离 mycat 搭建读写分离 mycat 水平拆分——分表 基于 HA 机制的 Mycat 高可用 Mycat 安全设置 Mycat 监控工具
世上哪有什么岁月静好,不过是有人替你负重前行
05-10 946
mycat是什么 Mycat 是数据库中间件。 1、数据库中间件 中间件：是一类连接软件组件和应用的计算机软件，以便于软件各部件之间的沟通。 例子：Tomcat，web中间件。 数据库中间件：连接java应用程序和数据库 2、为什么要用Mycat？ ① Java与数据库紧耦合。 ② 高访问量高并发对数据库的压力。 ③ 读写请求数据不一致 3、数据库中间件对比
MyCat教程【简单介绍】
波波烤鸭的博客
10-15 9229
一、MyCat简介 1. 什么是MyCat   MyCat 是目前最流行的基于 java 语言编写的数据库中间件，是一个实现了 MySQL 协议的服务器，前端用户可以把它看作是一个数据库代理，用 MySQL 客户端工具和命令行访问，而其后端可以用 MySQL 原生协议与多个 MySQL 服务器通信，也可以用 JDBC 协议与大多数主流数据库服务器通信，其核心功能是分库分表。配合数据库的主从模式还可...
MyCat实战讲解（MySQL集群）
01-01
Mycat是阿里推广的一个开源组件，其核心的概念为库表分离实现操作，利用合理的库表分离可以用更高效的方式来进行SQL数据库的开发与处理，适合高并发的数据访问处理。 在本课程中将为读者讲解库表分离的设计思想，以及数据库的垂直水平分割概念，同时后将为读者讲解高可用的MYSQL集群机构的设计实现。
mycat-server-1.6-relesase下载
最新发布
11-26
mycat-server-1.6-release是一个开源的、基于MySQL的分布式数据库系统，主要用于处理大规模数据的存储和查询。要下载mycat-server-1.6-release，首先需要打开官方网站或者github等开源软件托管网站。在网站主页或者项目页面中，可以找到mycat-server-1.6-release的下载链接。点击链接进入下载页面，可以找到不同版本和不同系统平台的下载选项。根据自己的需求选择合适的版本和平台，一般会有zip压缩包或者tar.gz压缩包等格式。 点击下载按钮后，会开始下载mycat-server-1.6-release的安装包。下载完成后，解压安装包到本地目录，然后按照官方提供的安装指南进行安装和配置。安装完成后，就可以开始使用mycat-server-1.6-release来处理分布式数据库的操作了。 需要注意的是，下载和使用mycat-server-1.6-release需要遵守相关的开源协议和使用条款，尊重作者的知识产权和劳动成果。此外，在使用过程中如果遇到问题，可以参考官方文档或者在开源社区中寻求帮助。希望这篇回答能够对你有所帮助。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

良辰美景好时光

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值