初识MyCat

初识MyCat

背景：当数据量过于庞大，则需要对数据进行分库操作或分表操作。业务数据过于重要则需要对数据进行备份。并发量大则需要对数据进行读写分离。如果程序直接连接多个数据库则代码耦合度高。在该背景下使用mycat中间件，代码只需要连接mycat即可，其他分库，分表，读写分离主备切换都交于mycat处理。
分库：又叫竖直分库，一个数据库通过业务的关联关系进行拆分多个数据库，如用户库，商品库等。
分表：又叫水平分表，一个表数据量过于庞大，则拆分到各个库中的表中.

一、mysql安装

1.配置表

IP	PORT	NAME	DESC
192.168.102.28	13306	mysql01(M1)	主写1,与M2互为主备
192.168.102.28	13307	mysql02(S1)	从读1,复制M1
192.168.102.28	13308	mysql03(M2)	主写2,与M1互为主备
192.168.102.28	13309	mysql04(S2)	从读2,复制M2
192.168.102.28	13310	mysql05(M3)	分库分表
192.168.102.28	13311	mysql06(M4)	分库分表

2.docker安装mysql

#mysql01(M1)
docker pull mysql:5.7
mkdir -p {/Users/*****/docker/mysql01/conf,/Users/*****/docker/mysql01/logs,/Users/*****/docker/mysql01/data}
docker run -p 13306:3306 --name mysql01 \
-v /Users/*****/docker/mysql/conf:/etc/mysql \
-v /Users/*****/docker/mysql/logs:/var/log/mysql \
-v /Users/*****/docker/mysql/data:/var/lib/mysql \
-e MYSQL_ROOT_PASSWORD=ok \
-d mysql:5.7

根据配置信息安装其他的mysql.

二、mycat安装

1.下载解压

http://dl.mycat.org.cn/1.6-RELEASE/

2.修改server.xml配置

<user name="root">
		<property name="password">ok</property>
		<!--mycat用于读写分离，fk用于分库，fb用于分表-->
		<property name="schemas">mycat,fk,fb</property>		
</user>

3.修改schema.xml配置

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

	
	<!--读写分离,主备切换 -->
	<schema name="mycat" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
	</schema>
	
	<!--竖直分库-->
	<schema name="fk" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn2">
	   <!--默认节点为dn2,下面三张表到dn3使用。使其达到分库的作用-->
		<table name="user" dataNode="dn3" />
		<table name="org" dataNode="dn3" />
		<table name="menu" dataNode="dn3" />
   </schema>


	<!-- 水平分表-->
	<schema name="fb" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn4">
		<!--默认节点为dn4,recor表在dn4,dn5,dn6,dn7,dn8,dn9节点都存在，通过mod_rule分片规则进行拆分-->
		<table name="record" dataNode="dn4,dn5,dn6,dn7,dn8,dn9" rule="mod_rule">
		    <!--detail与record的关系为n:1(ER表),应跨库无法进行关联，则detail的n条记录要和record的1条记录在一个库。-->
			<childTable name="detail" primaryKey="id" joinKey="record_id" parentKey="id" />
		</table>
		<!--全局表-->
		<table name="dict" dataNode="dn4,dn5,dn6,dn7,dn8,dn9" type="global" ></table>
		<table name="dict_item" dataNode="dn4,dn5,dn6,dn7,dn8,dn9" type="global" ></table>
	</schema>


	<dataNode name="dn1" dataHost="host1" database="business" />	
	<dataNode name="dn2" dataHost="host2" database="LWZC" />
        <dataNode name="dn3" dataHost="host3" database="LWZC" /> 
	<dataNode name="dn4" dataHost="host4" database="big" />	
	<dataNode name="dn5" dataHost="host5" database="big" />	
	<dataNode name="dn6" dataHost="host6" database="big" />	
	<dataNode name="dn7" dataHost="host7" database="big" />	
	<dataNode name="dn8" dataHost="host2" database="big" />	
	<dataNode name="dn9" dataHost="host3" database="big" />	


    <!--balance="1",M1用于写，其他的用于读包括(M2).如果M1宕机，则M2用于写，其他的用于读。writeType="0" 写操作在正常的写节点的第一个执行，switchType="1" 自动切换主备。-->
	<dataHost name="host1" maxCon="1000" minCon="10" balance="1"
			  writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
	    <!--心跳检测-->
		<heartbeat>select user()</heartbeat>
		 <!--写-->
		<writeHost host="M1" url="localhost:13306" user="root" password="ok">
		    <!--读-->
			<readHost host="S1" url="localhost:13307" user="root" password="ok" />
		</writeHost>
		<writeHost host="M2" url="localhost:13308" user="root" password="ok">
			<readHost host="S2" url="localhost:13309" user="root" password="ok" />
		</writeHost>
	</dataHost>


	<dataHost name="host2" maxCon="1000" minCon="10" balance="0"
                          writeType="0" dbType="mysql" dbDriver="native" switchType="-1"  slaveThreshold="100">
                <heartbeat>select 1</heartbeat>
                <writeHost host="mysql05" url="localhost:13310" user="root" password="ok">
                </writeHost>
        </dataHost>
        <dataHost name="host3" maxCon="1000" minCon="10" balance="0"
                          writeType="0" dbType="mysql" dbDriver="native" switchType="-1"  slaveThreshold="100">
                <heartbeat>select 1</heartbeat>
                <writeHost host="mysql06" url="localhost:13311" user="root" password="ok">
                </writeHost>
        </dataHost>
	<dataHost name="host4" maxCon="1000" minCon="10" balance="0"
                          writeType="0" dbType="mysql" dbDriver="native" switchType="-1"  slaveThreshold="100">
                <heartbeat>select 1</heartbeat>
                <writeHost host="mysql01" url="localhost:13306" user="root" password="ok">
                </writeHost>
        </dataHost>
        <dataHost name="host5" maxCon="1000" minCon="10" balance="0"
                          writeType="0" dbType="mysql" dbDriver="native" switchType="-1"  slaveThreshold="100">
                <heartbeat>select 1</heartbeat>
                <writeHost host="mysql02" url="localhost:13307" user="root" password="ok">
                </writeHost>
        </dataHost>
	<dataHost name="host6" maxCon="1000" minCon="10" balance="0"
                          writeType="0" dbType="mysql" dbDriver="native" switchType="-1"  slaveThreshold="100">
                <heartbeat>select 1</heartbeat>
                <writeHost host="mysql03" url="localhost:13308" user="root" password="ok">
                </writeHost>
        </dataHost>
	<dataHost name="host7" maxCon="1000" minCon="10" balance="0"
                          writeType="0" dbType="mysql" dbDriver="native" switchType="-1"  slaveThreshold="100">
                <heartbeat>select 1</heartbeat>
                <writeHost host="mysql04" url="localhost:13309" user="root" password="ok">
                </writeHost>
        </dataHost>
</mycat:schema>

4.启动

需要JDK1.8环境

cd mycat/bin
./mycat console

5.使用mysql进入mycat

mysql -uroot -pok -P8066 -h 192.168.102.26

6.schema参数详解

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 不负担读压力
5. writeType=“0” 写主机(M1),主机宕机则主备切换(M1->M2)。
6. writeType=“1” M1 or M2,随机分配给写主机，写备机。
7. writeType=“2”
8. switchType=“1” 自动切换
9. switchType="-1" 不自动切换
10. switchType=“2” 基于mysql主从同步状态决定是否切换

三、读写分离，主备切换

1.mysql一主多从（读写分离）

A.M1配置

修改my.cnf，在/Users/*/docker/mysql01/conf下，没有创建即可，修改完成后重启mysql服务

[mysqld]
#服务id，不要重复。
server-id=1
#开启2进制日志
log-bin=master-bin
log_bin_index=master-bin.index
#需要复制的数据库
binlog_do_db=business
#不需要复制的数据库
binlog_ignore_db=mysql
user=mysql
#bing-log_format=STATEMENT,ROW,MIXED;默认MIXED

B.S1配置

修改my.cnf

[mysqld]
server-id=2
log-bin=slave-bin
#开启中继日志
relay-log=slave-relay-bin
relay-log-index=slave-relay-bin.index

C.主机权限配置

执行sql

-- 创建用于主从复制的用户
CREATE USER 'rc'@'%' IDENTIFIED BY '123456'；
-- 刷新MySQL的系统权限相关表
FLUSH PRIVILEGES;
-- 授予主从复制权限
GRANT REPLICATION SLAVE ON *.* TO 'rc'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;
-- 查看主从复制的主机状态，主要需要查看master_log_file(File)和master_log_pos(Position)
show master status;

D.从机远程配置

执行sql

-- 停止主从复制
stop slave;
-- 修改主从复制的主机信息
change master to master_host='192.168.102.28', master_port=13306, master_user='rc', master_password='123456', master_log_file='master-bin.000001', master_log_pos=154;
-- 开启主从复制
start slave;
-- 查看从机状态Slave_IO_Running: Yes，Slave_SQL_Running: Yes 即可。
show slave status;

E.Mycat配置

<!--读写分离,主备切换 -->
	<schema name="mycat" checkSQLschema="false" sqlMaxLimit="100" 	dataNode="dn1">
	</schema>
<dataNode name="dn1" dataHost="host1" database="business" />

<dataHost name="host1" maxCon="1000" minCon="10" balance="1"
			  writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
		<heartbeat>select user()</heartbeat>
		<writeHost host="M1" url="localhost:13306" user="root" password="ok">
			<readHost host="S1" url="localhost:13307" user="root" password="ok" />
		</writeHost>
	</dataHost>

2.Mysql双主（主备切换）

A.M1配置

修改my.cnf

[mysqld]
server-id=1
log-bin=master-bin
log_bin_index=master-bin.index
binlog_do_db=business
binlog_ignore_db=mysql
user=mysql
#bing-log_format=STATEMENT,ROW,MIXED;默认MIXED
#开启主主复制
log-slave-updates
#主主复制步长与另一台主机一致
auto-increment-increment=1
#主主复制开启位置。与另一台主机不能重复
auto-increment-offset=1

B.S1配置

修改my.cnf

[mysqld]
server-id=2
log-bin=slave-bin
relay-log=slave-relay-bin
relay-log-index=slave-relay-bin.index

C.M2配置

修改my.cnf

server-id=3
log-bin=master-bin
log_bin_index=master-bin.index
binlog_do_db=business
binlog_ignore_db=mysql
user=mysql
#bing-log_format=STATEMENT,ROW,MIXED;默认MIXED
log-slave-updates
auto-increment-increment=1
auto-increment-offset=2

D.S2配置

修改my.cnf

[mysqld]
server-id=4
log-bin=slave-bin
relay-log=slave-relay-bin
relay-log-index=slave-relay-bin.index

E.主机权限配置

执行sql

CREATE USER 'rc'@'%' IDENTIFIED BY '123456';
FLUSH PRIVILEGES;
GRANT REPLICATION SLAVE ON *.* TO 'rc'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;
show master status;

F.远程配置

执行sql,主机也是另一台的从机，所以也要主机运行开启主从复制

--s1,m2
stop slave;
change master to master_host='192.168.102.26', master_port=13306, master_user='rc', master_password='123456', master_log_file='master-bin.000002', master_log_pos=154;
start slave;
show slave status;
--s2,m1
stop slave;
change master to master_host='192.168.102.26', master_port=13308, master_user='rc', master_password='123456', master_log_file='master-bin.000001', master_log_pos=855;
start slave;
show slave status;

F.Mycat配置

<!--读写分离,主备切换 -->
	<schema name="mycat" checkSQLschema="false" sqlMaxLimit="100" 	dataNode="dn1">
	</schema>
<dataNode name="dn1" dataHost="host1" database="business" />

<dataHost name="host1" maxCon="1000" minCon="10" balance="1"
			  writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
		<heartbeat>select user()</heartbeat>
		<writeHost host="M1" url="localhost:13306" user="root" password="ok">
			<readHost host="S1" url="localhost:13307" user="root" password="ok" />
		</writeHost>
		<writeHost host="M2" url="localhost:13308" user="root" password="ok">
			<readHost host="S2" url="localhost:13309" user="root" password="ok" />
		</writeHost>
	</dataHost>

四、分库

<!--schema.xml-->
<schema name="fk" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn2">
                <table name="user" dataNode="dn3" />
                <table name="org" dataNode="dn3" />
                <table name="menu" dataNode="dn3" />
</schema>

定义一个库那些表来自另一个数据节点，其他的走默认节点.如：除了user，org，menu等表在数据节点3中其他的表在数据节点2中

五、分表

<!--schema.xml-->
	<schema name="fb" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn4">
	   <!--record在多个节点都有，使用mod_rule分片规则-->
		<table name="record" dataNode="dn4,dn5,dn6,dn7,dn8,dn9" rule="mod_rule"/>
	</schema>

<!--rule.xml-->
<tableRule name="mod_rule">
     <rule>
     		 <!--对表中的id字段进行分片-->
             <columns>id</columns>
             <!--分片方法为mod-long。该方法为mycat自带的。除余运算。
			如有3个节点，则id%3==0为第一节点，id%3==1为第二节点，id%3==2为第三节点
			-->
             <algorithm>mod-long</algorithm>
     </rule>
</tableRule>

定义一个表的数据在那些库中分布，定义分片规则。

六、其他

1.全局序列

有多种序列方案，0:本地,1:数据库2:时间戳方式3:自主，数据库为最常用的序列方案。

A.SEQ建立

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 ;

-- 插入record的序列
INSERT INTO MYCAT_SEQUENCE(name,current_value,increment) VALUES ('record', 1, 100);

B.修改配置

<!--server.xml-->
<!-- 1为数据库，默认为0，本地-->
<system><property name="sequnceHandlerType">1</property></system>

<!--sequence_db_conf.properties-->
<!--使用record的序列的数据节点是哪个。且RECORD必须大写，数据库中无所谓-->
RECORD=dn4

C.使用

-- next value for MYCATSEQ_*，*为建立的序列的名称，如RECORD。必须大写。
insert into record(id,time) values(next value for MYCATSEQ_RECORD,now());

2.ER表分片

分片表的关联关系表数据必须存放在同一个数据节点当中，跨库无法使用join。如：一条记录关联多条详情.记录在数据节点A中，则详情也在数据节点A中。

<!--schema.xml-->
	<schema name="fb" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn4">
		<table name="record" dataNode="dn4,dn5,dn6,dn7,dn8,dn9" rule="mod_rule">
				<childTable name="detail" primaryKey="id" joinKey="record_id" parentKey="id" />
		</table>
	</schema>

根据分片表的关联关系进行分表。如：一条记录关联多条详情.记录在数据节点A中，则详情也在数据节点A中。

3.全局表

该表在每个库都要存在。如字典表对表中的进行字段进行翻译(记录表中的状态字段1:有效，0:无效)，也是跨库无法使用join的原因所以每个节点都要有。

<!--schema.xml-->
<table name="dict" dataNode="dn4,dn5,dn6,dn7,dn8,dn9" type="global" ></table>
<table name="dict_item" dataNode="dn4,dn5,dn6,dn7,dn8,dn9" type="global" ></table>

4.后端代码编写

springboot + mycat + myBatis-plus

A.POM

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.6.2</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.scy.study</groupId>
    <artifactId>mycat</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>mycat</name>
    <description>Demo project for Spring Boot</description>
    <properties>
        <java.version>1.8</java.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.37</version>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.3.2</version>
        </dependency>
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-extension</artifactId>
            <version>3.3.2</version>
        </dependency>
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-generator</artifactId>
            <version>3.3.2</version>
        </dependency>
        <!--代码生成模板-->
        <dependency>
            <groupId>org.freemarker</groupId>
            <artifactId>freemarker</artifactId>
            <version>2.3.30</version>
        </dependency>
        <dependency>
            <groupId>cn.hutool</groupId>
            <artifactId>hutool-all</artifactId>
            <version>5.5.9</version>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

</project>

B.application.yaml

server:
  port: 8088

spring:
  datasource:
    driver-class-name: com.mysql.jdbc.Driver
    url: jdbc:mysql://localhost:8066/fb?serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=utf8&autoReconnect=true&failOverReadOnly=false
    username: root
    password: ok
    type: com.zaxxer.hikari.HikariDataSource

mybatisplus:
  mapper-locations: classpath:mapper/*xml
  type-aliases-package: com.scy.study.mycat.model
  global-config:
    db-config:
      logic-delete-field: status
      logic-delete-value: 0
      logic-not-delete-value: 1
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl

C.代码

/**
* model
*/
@Data
@EqualsAndHashCode(callSuper = false)
@TableName("record")
public class Record implements Serializable {
    private static final long serialVersionUID = 1L;
    @TableId("id")
    private Integer id;
    @TableField("time")
    private LocalDateTime time;
}
/*
* mapper.xml
*/
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.scy.study.mycat.dao.RecordMapper">
    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.scy.study.mycat.model.Record">
        <id column="id" property="id" />
        <result column="time" property="time" />
    </resultMap>
    <!-- 通用查询结果列 -->
    <sql id="Base_Column_List">
        id, time
    </sql>
    <insert id="insertRe">
        <!-- insert into record values() 会报错，切记不能使用，必须把字段名称带上。-->
        insert into record(id,time) values(next value for MYCATSEQ_RECORD,now());
    </insert>
</mapper>
/*
* dao
*/
public interface RecordMapper extends BaseMapper<Record> {
    int insertRe();
}
/**
* service
*/
public interface IRecordService extends IService<Record> {
    int insertRe();
}
/**
* impl
*/
@Service
public class RecordServiceImpl extends ServiceImpl<RecordMapper, Record> implements IRecordService {
    @Override
    public int insertRe() {
        return super.baseMapper.insertRe();
    }
}
/**
* controller
*/
@RestController
@RequestMapping("/record")
public class RecordController {

    @Autowired
    private IRecordService iRecordService;

    @GetMapping("/getAll")
    public List<Record> getAll() {
        LocalDateTime localDateTime = LocalDateTime.of(2022,1,13,0,0);
        return iRecordService.list(new LambdaQueryWrapper<Record>().ge(Record::getTime,localDateTime)
                .orderByDesc(Record::getId));
    }
}

代码与Mysql/Oracle一致