首页 数据库技术 正文
SpringBoot整合MyCat实现读写分离

 2022-10-27    424  

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

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-uroot-p
Root@123
//log_binON表示开启成功，OFF表示开启失败
mysql>showvariableslike'%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|
+---------------------------------+--------------------------------+
6rowsinset(0.01sec)

新增备份账户

mysql>grantreplicationslaveon*.*to'backup'@'%'identifiedby'Root@123';
mysql>usemysql
mysql>selectuser,authentication_string,hostfromuser;

重启mysql

$systemctlrestartmysqld

登录mysql，并设置只读

$mysql-uroot-p
Root@123
mysql>flushtableswithreadlock;
mysql>showmasterstatus\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:
1rowinset(0.01sec)
//导出数据库，进入到一个合适的文件夹
$mysqldump-uroot-pitools_simple>itools_simple.txt
//把生成的文件导出到本机
$szitools_simple.txt
//在window本机打开cmd，登录slave本机mysql数据库，导入数据库
$.\mysql-uroot-p-hlocalhostmycat_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数据库

netstopmysql
netstartmysql

登录slave数据库

$mysql-uroot-p
123456
//停止slave
mysql>stopslave;
//根据master中的status，添加以下配置
msyql>changemastertomaster_host='192.168.0.105',master_user='backup',master_password='Root@123',master_log_file='mysql-bin.000003',master_log_pos=154;
//启用slave
mysql>startslave;
//查看slave状态
mysql>showslavestatus\G
***************************1.row***************************
Slave_IO_State:Waitingformastertosendevent
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
..............
1rowinset(0.00sec)
//出现下面两个说明配置正确
Slave_IO_Running:Yes
Slave_SQL_Running:Yes

关闭master数据库的只读设置

mysql>unlocktables;

存储引擎

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>showvariableslike'%storage_engine%';
+----------------------------------+--------+
|Variable_name|Value|
+----------------------------------+--------+
|default_storage_engine|InnoDB|
|default_tmp_storage_engine|InnoDB|
|disabled_storage_engines||
|internal_tmp_disk_storage_engine|InnoDB|
+----------------------------------+--------+
4rowsinset(0.00sec)
//修改配置文件
$vim/etc/my.ini
[mysqld]
default-storage-engine=MyISAM
$systemctlrestartmysqld
mysql>showvariableslike'%storage_engine%';
+----------------------------------+--------+
|Variable_name|Value|
+----------------------------------+--------+
|default_storage_engine|MyISAM|
|default_tmp_storage_engine|InnoDB|
|disabled_storage_engines||
|internal_tmp_disk_storage_engine|InnoDB|
+----------------------------------+--------+
4rowsinset(0.00sec)

修改表存储引擎

如果是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>setgloballog_bin_trust_function_creators=TRUE;

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

出现的原因

  1. DB的 engine 是 MyISAM
  2. 字符集是 utf8 ，1个 utf8=3bytes
  3. (索引长度总和) * 3 > 1000。

    解决方案

    1. 修改DB engine 至 innodb
    2. 更改字符集
    3. 减小字段长度

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

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

      Mycat基于MySQL的读写分离

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

      安装Mycat

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

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

      $wgethttp://dl.mycat.io/1.6.7.5/2020-3-3/Mycat-server-1.6.7.5-test-20200303154735-linux.tar.gz
$tar-zxvfMycat-server-1.6.7.5-test-20200303154735-linux.tar.gz
$cdmycat
$useraddmycat
$chown-Rmycat:mycat/usr/local/mycat/mycat
$passwdmycat
//配置hostname，添加以下配置
[root@localhostmycat]vim/etc/sysconfig/network
HOSTNAME=localhost（主机名）
//查看是否配置主机
$vim/etc/hosts

      将Mycat配置到环境变量中

      $vim/etc/profile
//在最后添加
MYCAT_HOME=/usr/local/mycat/mycat
PATH=$MYCAT_HOME/bin:$PATH
exportPATH
//使配置生效
$source/etc/profile

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

      配置mycat的schema.xml

      <?xmlversion="1.0"?>
<!DOCTYPEmycat:schemaSYSTEM"schema.dtd">
<mycat:schemaxmlns:mycat="http://io.mycat/">
<schemaname="itools_simple"checkSQLschema="false"sqlMaxLimit="100"dataNode="dn1">
</schema>
<dataNodename="dn1"dataHost="localhost"database="itools_simple"/>
<dataHostname="localhost"maxCon="1000"minCon="10"balance="0"
writeType="0"dbType="mysql"dbDriver="native"switchType="1"slaveThreshold="100">
<heartbeat>selectuser()</heartbeat>
<writeHosthost="hostM1"url="192.168.0.105:3306"user="user1"password="Root@123">
<!--可以配置多个从库-->
<readHosthost="hostS2"url="127.0.0.1:3306"user="user2"password="Root@123"/>
</writeHost>
</dataHost>
</mycat:schema>

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

      <username="user1"defaultAccount="true">
<propertyname="password">Root@123</property>
<propertyname="schemas">itools_simple</property>
<propertyname="defaultSchema">itools_simple</property>
</user>
<username="user2">
<propertyname="password">Root@123</property>
<propertyname="schemas">itools_simple</property>
<propertyname="readOnly">true</property>
<propertyname="defaultSchema">itools_simple</property>
</user>

      启动Mycat

      启动mycat

      $mycatstart
StartingMycat-server...

      查看启动日志

      $catwrapper.log
MyCATServerstartupsuccessfully.seelogsinlogs/mycat.log

      使用客户端连接mycat

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

      使用Navicat连接

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

      SpringBoot 整合 MyCat 实现读写分离

      • 首先需要配置好数据库的主从关系。
      • 配置好MyCat服务。
      • 实现MyCat与MySQL读写分离。

      添加依赖

      <dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-aop</artifactId>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.0.23</version>
</dependency>

      创建数据源

      packagecom.muycode.itoolsimple.datasource;
importorg.springframework.boot.context.properties.ConfigurationProperties;
importorg.springframework.boot.jdbc.DataSourceBuilder;
importorg.springframework.context.annotation.Bean;
importorg.springframework.context.annotation.Configuration;
importjavax.sql.DataSource;
@Configuration
publicclassDataSourceConfig{
/**
*创建可读数据源
*
*@return
*/
@Bean(name="selectDataSource")
@ConfigurationProperties(prefix="spring.datasource.select")
publicDataSourcedataSource1(){
returnDataSourceBuilder.create().build();
}
/**
*创建可写数据源
*
*@return
*/
@Bean(name="updateDataSource")
@ConfigurationProperties(prefix="spring.datasource.update")
publicDataSourcedataSource2(){
returnDataSourceBuilder.create().build();
}
}

      设置数据源

      packagecom.muycode.itoolsimple.datasource;
importorg.springframework.context.annotation.Lazy;
importorg.springframework.stereotype.Component;
@Component
@Lazy(false)
publicclassDataSourceContextHolder{
/**
*采用ThreadLocal保存本地多数据源
*/
privatestaticfinalThreadLocal<String>contextHolder=newThreadLocal<>();
/**
*设置数据源类型
*
*@paramdbType
*/
publicstaticvoidsetDbType(StringdbType){
contextHolder.set(dbType);
}
/**
*获取数据源类型
*/
publicstaticStringgetDbType(){
returncontextHolder.get();
}
publicstaticvoidclearDbType(){
contextHolder.remove();
}
}

      返回数据源

      packagecom.muycode.itoolsimple.datasource;
importorg.springframework.beans.factory.annotation.Autowired;
importorg.springframework.beans.factory.annotation.Qualifier;
importorg.springframework.context.annotation.Primary;
importorg.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
importorg.springframework.stereotype.Component;
importjavax.sql.DataSource;
importjava.util.HashMap;
importjava.util.Map;
@Component
@Primary
publicclassDynamicDataSourceextendsAbstractRoutingDataSource{
@Autowired
@Qualifier("selectDataSource")
privateDataSourceselectDataSource;
@Autowired
@Qualifier("updateDataSource")
privateDataSourceupdateDataSource;
/**
*返回生效的数据源名称
*/
@Override
protectedObjectdetermineCurrentLookupKey(){
returnDataSourceContextHolder.getDbType();
}
/**
*配置数据源信息
*/
@Override
publicvoidafterPropertiesSet(){
Map<Object,Object>map=newHashMap<>(16);
map.put("selectDataSource",selectDataSource);
map.put("updateDataSource",updateDataSource);
setTargetDataSources(map);
setDefaultTargetDataSource(updateDataSource);
super.afterPropertiesSet();
}
}

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

      packagecom.muycode.itoolsimple.datasource;
importorg.aspectj.lang.JoinPoint;
importorg.aspectj.lang.annotation.Aspect;
importorg.aspectj.lang.annotation.Before;
importorg.springframework.context.annotation.Lazy;
importorg.springframework.core.annotation.Order;
importorg.springframework.stereotype.Component;
@Aspect
@Component
@Lazy(false)
@Order(0)//Order设定AOP执行顺序使之在数据库事务上先执行
publicclassDataSourceOptionAop{
/**
*可读数据源
*/
privatefinalstaticStringDATASOURCE_TYPE_SELECT="selectDataSource";
/**
*可写数据源
*/
privatefinalstaticStringDATASOURCE_TYPE_UPDATE="updateDataSource";
/**
*创建切面，根据方法类型选择不同的数据源
*
*@paramjoinPoint
*/
@Before("execution(*com.muycode.itoolsimple.service.*.*(..))")
publicvoidprocess(JoinPointjoinPoint){
StringmethodName=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数据源-------------------

  •  标签：  
  • MySQL
    •  

发表评论：

原文链接：https://77isp.com/post/10661.html

=========================================

https://77isp.com/ 为 “云服务器技术网” 唯一官方服务平台，请勿相信其他任何渠道。

热门文章
热门标签
最新留言
友情链接

首页 IT运维空间 网云互联 1818IP网

本站资源均来自原创及互联网，仅供学习与参考，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系，我们将在24小时内删除。

苏ICP备2022008373号-1