Spring Boot 如何整合 MyCat 实现读写分离

你的名字 2024-04-03 08:27 60阅读 0赞

[MyCat][] 一个彻底开源的，面向企业应用开发的大数据库集群。基于阿里开源的 Cobar 产品而研发。能满足数据库数据大量存储；提高了查询性能。文章介绍如何实现 MyCat 连接 MySQL 实现主从分离，并集成 Spring Boot 实现读写分离。

MySQL配置主从关系

说明

192.168.0.105 Linux 数据库作为主master数据库

127.0.0.1 Window 作为从slave数据库

master主数据库配置

binlog是Mysql sever层维护的一种二进制日志，主要是用来记录对Mysql数据更新或潜在发生更新的SQL语句，记录了所有的写语句，并以事务的形式保存在磁盘中，还包含语句所执行的消耗的时间，MySQL的二进制日志是事务安全型的。

master上开启log\_bin

$ vim /etc/my.cnf

log-bin = mysql-bin \#\[必须\]启用二进制日志

server-id = 4 \#\[必须\]服务器唯一ID，默认是1，最好取ip的后3位

expire-logs-days = 7 \#只保留7天的二进制日志，以防磁盘被日志占满

binlog-ignore-db = mysql \#不备份的数据库

binlog-ignore-db = information\_schema

binlog-ignore-db = performation\_schema

binlog-ignore-db = sys

binlog-do-db=itools\_simple \#需要做复制的数据库名，如果有多个，复制binlog-do-db即可

登录mysql，测试log\_bin是否成功开启

$ mysql -u root -p

Root@123

// log\_bin ON表示开启成功，OFF表示开启失败

mysql> show variables like "%log\_bin%";

\+---------------------------------+--------------------------------+

| Variable\_name | Value |

\+---------------------------------+--------------------------------+

| log\_bin | ON |

| log\_bin\_basename | /var/lib/mysql/mysql-bin |

| log\_bin\_index | /var/lib/mysql/mysql-bin.index |

| log\_bin\_trust\_function\_creators | OFF |

| log\_bin\_use\_v1\_row\_events | OFF |

| sql\_log\_bin | ON |

\+---------------------------------+--------------------------------+

6 rows in set (0.01 sec)

新增备份账户

mysql> grant replication slave on \*.\* to "backup"@"%" identified by "Root@123";

mysql> use mysql

mysql> select user,authentication\_string,host from user;

重启mysql

$ systemctl restart mysqld

登录mysql，并设置只读

$ mysql -u root -p

Root@123

mysql> flush tables with read lock;

mysql> show master status \\G

\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

File: mysql-bin.000003

Position: 154

Binlog\_Do\_DB: itools\_simple

Binlog\_Ignore\_DB: mysql,information\_schema,performation\_schema,sys

Executed\_Gtid\_Set:

1 row in set (0.01 sec)

// 导出数据库，进入到一个合适的文件夹

$ mysqldump -u root -p itools\_simple > itools\_simple.txt

// 把生成的文件导出到本机

$ sz itools\_simple.txt

// 在window本机打开cmd，登录slave本机mysql数据库，导入数据库

$ .\\mysql -u root -p -hlocalhost mycat\_master < .\\itools\_simple.txt

slave从数据库配置

配置 my.ini 文件

\[mysql\]

\#default-character-set=utf8

\[mysqld\]

max\_connections=200

character-set-server=utf8

default-storage-engine=INNODB

\# 添加一下配置

log-bin=mysql-bin

server-id=223

重启slave数据库

net stop mysql

net start mysql

登录slave数据库

$ mysql -u root -p

123456

// 停止 slave

mysql> stop slave;

// 根据master中的status，添加以下配置

msyql> change master to master\_host="192.168.0.105",master\_user="backup",master\_password="Root@123",master\_log\_file="mysql-bin.000003",master\_log\_pos=154;

// 启用slave

mysql> start slave;

// 查看slave状态

mysql> show slave status \\G

\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

Slave\_IO\_State: Waiting for master to send event

Master\_Host: 192.168.0.105

Master\_User: backup

Master\_Port: 3306

Connect\_Retry: 60

Master\_Log\_File: mysql-bin.000003

Read\_Master\_Log\_Pos: 154

Relay\_Log\_File: DESKTOP-OB9O5N7-relay-bin.000002

Relay\_Log\_Pos: 320

Relay\_Master\_Log\_File: mysql-bin.000003

Slave\_IO\_Running: Yes

Slave\_SQL\_Running: Yes

..............

1 row in set (0.00 sec)

// 出现下面两个说明配置正确

Slave\_IO\_Running: Yes

Slave\_SQL\_Running: Yes

关闭master数据库的只读设置

mysql> unlock tables;

存储引擎

InnoDB特性

MySQL5.5版本后，MySQL的默认内置存储引擎已经从MyISAM变成InnoDB

支持事务;

行级锁定(更新数据时一般指锁定当前行)：通过索引实现、全表扫描忍让时表锁、注意间隙所的影响;

读写阻塞与事务的隔离级别相关；

具有非常高的缓存特性(既能缓存索引、也能缓存数据)；

这个表和主键以组(Cluster)的方式存储、组成一颗平衡树；

所有的辅助索引(secondary indexes)都会保存主键信息；

支持分区、表空间类似与oracle 数据库；

支持外键约束、不支持全文检索(5.5.5之前的MyISAM支持全文检索、5.5.5之后就不在支持);

相对MyISAM而言、对硬件的要求比较高

MyISAM特性

不支持事务

表级锁定，数据更新时锁定整个表：其锁定机制是表级锁定，这虽然可以让锁定的实现成本很小但是也同时大大降低了其并发性能。

读写互相阻塞：不仅会在写入的时候阻塞读取，myisam还会在读取的时候阻塞写入，但读本身并不会阻塞另外的读。

只会缓存索引：MyISAM可以通过key\_buffer\_size缓存索引，以大大提高访问性能，减少产品IO，但是这个缓存区只会缓存索引，而不会缓存数据。

读取速度较快，占用资源相对少。

不支持外键约束，但支持全文索引。

修改slave数据库存储引擎

mysql> show variables like "%storage\_engine%";

\+----------------------------------+--------+

| Variable\_name | Value |

\+----------------------------------+--------+

| default\_storage\_engine | InnoDB |

| default\_tmp\_storage\_engine | InnoDB |

| disabled\_storage\_engines | |

| internal\_tmp\_disk\_storage\_engine | InnoDB |

\+----------------------------------+--------+

4 rows in set (0.00 sec)

// 修改配置文件

$ vim /etc/my.ini

\[mysqld\]

default-storage-engine=MyISAM

$ systemctl restart mysqld

mysql> show variables like "%storage\_engine%";

\+----------------------------------+--------+

| Variable\_name | Value |

\+----------------------------------+--------+

| default\_storage\_engine | MyISAM |

| default\_tmp\_storage\_engine | InnoDB |

| disabled\_storage\_engines | |

| internal\_tmp\_disk\_storage\_engine | InnoDB |

\+----------------------------------+--------+

4 rows in set (0.00 sec)

修改表存储引擎

如果是slave数据库的表是MyISAM，master数据库的表是InnoDB。直接覆盖master数据库来同步数据的话，slave数据库表的存储引擎也将会同步变成InnoDB。扩展：InnoDB一棵B+树可以存放多少行数据？

更换存储引擎遇到的问题

This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you might want to use the less safe log\_bin\_trust\_function\_creators variable)

出现的原因

在function里面，只有 DETERMINISTIC, NO SQL 和 READS SQL DATA 被支持。

如果我们开启了 bin-log, 我们就必须为我们的function指定一个参数。

解决方案

mysql> set global log\_bin\_trust\_function\_creators=TRUE;

Specified key was too long; max key length is 1000 bytes

出现的原因

DB的 engine 是 MyISAM

字符集是 utf8 ，1个 utf8=3bytes

(索引长度总和) \* 3 > 1000。

解决方案

修改DB engine 至 innodb

更改字符集

减小字段长度

注意：一定不要手动去修改slave数据库中的数据，需要给slave的用户设置只读。

至此，mysql的数据库主从设置已经配置成功。在master中修改数据库，会同步到slave中。

Mycat基于MySQL的读写分离

Mycat不负责数据的同步，所以要还是要基于 MySQL 的主从配置来实现读写分离。

安装Mycat

由于github限制，所以以后新版本从以下地址下载 http://dl.mycat.io

Linux创建文件夹/usr/local/mycat，进入文件夹，下载安装包

$ wget http://dl.mycat.io/1.6.7.5/2020-3-3/Mycat-server-1.6.7.5-test-20200303154735-linux.tar.gz

$ tar -zxvf Mycat-server-1.6.7.5-test-20200303154735-linux.tar.gz

$ cd mycat

$ useradd mycat

$ chown -R mycat:mycat /usr/local/mycat/mycat

$ passwd mycat

// 配置hostname，添加以下配置

\[root@localhost mycat\] vim /etc/sysconfig/network

HOSTNAME=localhost（主机名）

// 查看是否配置主机

$ vim /etc/hosts

将Mycat配置到环境变量中

$ vim /etc/profile

// 在最后添加

MYCAT\_HOME=/usr/local/mycat/mycat

PATH=$MYCAT\_HOME/bin:$PATH

export PATH

// 使配置生效

$ source /etc/profile

在master数据库中添加user1（写）、user2（只读）两个账户，并配置权限。

配置mycat的schema.xml

<?xml version="1.0"?>

<!DOCTYPE mycat:schema SYSTEM "schema.dtd">

<mycat:schema xmlns:mycat="http://io.mycat/">

</schema>

<dataHost name="localhost" maxCon="1000" minCon="10" balance="0"

writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">

<heartbeat>select user()</heartbeat>

</writeHost>

</dataHost>

</mycat:schema>

配置mycat的server.xml，增加两个用户

<property name="schemas">itools\_simple</property>

<property name="defaultSchema">itools\_simple</property>

</user>

<property name="schemas">itools\_simple</property>

<property name="defaultSchema">itools\_simple</property>

</user>

启动Mycat

启动mycat

$ mycat start

Starting Mycat-server...

查看启动日志

$ cat wrapper.log

MyCAT Server startup successfully. see logs in logs/mycat.log

使用客户端连接mycat

使用SQLyog连接(使用此方式连接，不能直接通过点击表查看数据)

使用Navicat连接

可通过客户端直接查看master数据，也可通过修改mycat数据，查看master和slave的数据是否会同步。

Spring Boot 整合 MyCat 实现读写分离

首先需要配置好数据库的主从关系。

配置好MyCat服务。

实现MyCat与MySQL读写分离。

添加依赖

<groupId>org.springframework.boot</groupId>

<artifactId>spring-boot-starter-aop</artifactId>

</dependency>

<groupId>com.alibaba</groupId>

<artifactId>druid</artifactId>

</dependency>

创建数据源

package com.muycode.itoolsimple.datasource;

import org.springframework.boot.context.properties.ConfigurationProperties;

import org.springframework.boot.jdbc.DataSourceBuilder;

import org.springframework.context.annotation.Bean;

import org.springframework.context.annotation.[Configuration][];

import javax.sql.DataSource;

@Configuration

public class DataSourceConfig \{

/\*\*

\* 创建可读数据源

\* @return

\*/

@Bean(name = "selectDataSource")

@ConfigurationProperties(prefix = "spring.datasource.select")

public DataSource dataSource1() \{

return DataSourceBuilder.create().build();

/\*\*

\* 创建可写数据源

\* @return

\*/

@Bean(name = "updateDataSource")

@ConfigurationProperties(prefix = "spring.datasource.update")

public DataSource dataSource2() \{

return DataSourceBuilder.create().build();

设置数据源

package com.muycode.itoolsimple.datasource;

import org.springframework.context.annotation.Lazy;

import org.springframework.stereotype.Component;

@Component

@Lazy(false)

public class DataSourceContextHolder \{

/\*\*

\* 采用ThreadLocal 保存本地多数据源

\*/

private static final ThreadLocal<String> contextHolder = new ThreadLocal<>();

/\*\*

\* 设置数据源类型

\* @param dbType

\*/

public static void setDbType(String dbType) \{

contextHolder.set(dbType);

/\*\*

\* 获取数据源类型

\*/

public static String getDbType() \{

return contextHolder.get();

public static void clearDbType() \{

contextHolder.remove();

返回数据源

package com.muycode.itoolsimple.datasource;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.beans.factory.annotation.Qualifier;

import org.springframework.context.annotation.Primary;

import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;

import org.springframework.stereotype.Component;

import javax.sql.DataSource;

import java.util.HashMap;

import java.util.Map;

@Component

@Primary

public class DynamicDataSource extends AbstractRoutingDataSource \{

@Autowired

@Qualifier("selectDataSource")

private DataSource selectDataSource;

@Autowired

@Qualifier("updateDataSource")

private DataSource updateDataSource;

/\*\*

\* 返回生效的数据源名称

\*/

@Override

protected Object determineCurrentLookupKey() \{

return DataSourceContextHolder.getDbType();

/\*\*

\* 配置数据源信息

\*/

@Override

public void afterPropertiesSet() \{

Map<Object, Object> map = new HashMap<>(16);

map.put("selectDataSource", selectDataSource);

map.put("updateDataSource", updateDataSource);

setTargetDataSources(map);

setDefaultTargetDataSource(updateDataSource);

super.afterPropertiesSet();

创建切面，动态设置数据源

package com.muycode.itoolsimple.datasource;

import org.aspectj.lang.JoinPoint;

import org.aspectj.lang.annotation.Aspect;

import org.aspectj.lang.annotation.Before;

import org.springframework.context.annotation.Lazy;

import org.springframework.core.annotation.Order;

import org.springframework.stereotype.Component;

@Aspect

@Component

@Lazy(false)

@Order(0) // Order设定AOP执行顺序 使之在数据库事务上先执行

public class DataSourceOptionAop \{

/\*\*

\* 可读数据源

\*/

private final static String DATASOURCE\_TYPE\_SELECT = "selectDataSource";

/\*\*

\* 可写数据源

\*/

private final static String DATASOURCE\_TYPE\_UPDATE = "updateDataSource";

/\*\*

\* 创建切面，根据方法类型选择不同的数据源

\* @param joinPoint

\*/

@Before("execution(\* com.muycode.itoolsimple.service.\*.\*(..))")

public void process(JoinPoint joinPoint) \{

String methodName = joinPoint.getSignature().getName();

System.out.print("=========== " + methodName);

if (methodName.startsWith("get") || methodName.startsWith("count") || methodName.startsWith("find")

|| methodName.startsWith("list") || methodName.startsWith("select") || methodName.startsWith("check")

|| methodName.startsWith("query")) \{

DataSourceContextHolder.setDbType(DATASOURCE\_TYPE\_SELECT);

System.out.println("-----------------使用selectDataSource数据源-------------------");

\} else \{

DataSourceContextHolder.setDbType(DATASOURCE\_TYPE\_UPDATE);

System.out.println("-----------------使用updateDataSource数据源-------------------");

输出结果

=========== getByUsername-----------------使用selectDataSource数据源-------------------

=========== getPermissionStringByUserId-----------------使用selectDataSource数据源-------------------

=========== getPermissionByUserId-----------------使用selectDataSource数据源-------------------

=========== getRolePermissionLinkByUserId-----------------使用selectDataSource数据源-------------------

=========== save-----------------使用updateDataSource数据源-------------------

=========== queryByPage-----------------使用selectDataSource数据源-------------------

=========== save-----------------使用updateDataSource数据源-------------------

=========== getPermissionAll-----------------使用selectDataSource数据源-------------------

=========== save-----------------使用updateDataSource数据源-------------------

=========== getSysCodeAll-----------------使用selectDataSource数据源-------------------

=========== save-----------------使用updateDataSource数据源-------------------

=========== getByRid-----------------使用selectDataSource数据源-------------------

来源：[微点阅读][Link 1] [https://www.weidianyuedu.com][Link 1]

[MyCat]: https://so.csdn.net/so/search?q=MyCat&spm=1001.2101.3001.7020
[Configuration]: https://so.csdn.net/so/search?q=Configuration&spm=1001.2101.3001.7020
[Link 1]: https://www.weidianyuedu.com