编程语言SpringBoot整合MyCat实现读写分离

    作者:颜不喜更新于: 2020-05-10 14:27:12

    大神带你学编程,欢迎选课

    SpringBoot整合MyCat实现读写分离。编程语言原本是被设计成专门使用在计算机上的,但它们也可以用来定义算法或者数据结构。正是因为如此,程序员才会试图使程序代码更容易阅读。

    文章介绍如何实现MyCat连接MySQL实现主从分离,并集成SpringBoot实现读写分离。

    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

    1. $ vim /etc/my.cnf  
    2. log-bin           = mysql-bin  #[必须]启用二进制日志  
    3. server-id         = 4          #[必须]服务器唯一ID,默认是1,最好取ip的后3位  
    4. expire-logs-days  = 7           #只保留7天的二进制日志,以防磁盘被日志占满  
    5. binlog-ignore-db  = mysql       #不备份的数据库  
    6. binlog-ignore-db  = information_schema  
    7. binlog-ignore-db  = performation_schema  
    8. binlog-ignore-db  = sys  
    9. binlog-do-db=itools_simple #需要做复制的数据库名,如果有多个,复制binlog-do-db即可 

    登录mysql,测试log_bin是否成功开启

    1. $ mysql -u root -p  
    2.   Root@123  
    3. // log_bin ON表示开启成功,OFF表示开启失败  
    4. mysql> show variables like '%log_bin%';  
    5. +---------------------------------+--------------------------------+  
    6. | Variable_name                   | Value                          |  
    7. +---------------------------------+--------------------------------+  
    8. | log_bin                         | ON                             |  
    9. | log_bin_basename                | /var/lib/mysql/mysql-bin       |  
    10. | log_bin_index                   | /var/lib/mysql/mysql-bin.index |  
    11. | log_bin_trust_function_creators | OFF                            |  
    12. | log_bin_use_v1_row_events       | OFF                            |  
    13. | sql_log_bin                     | ON                             |  
    14. +---------------------------------+--------------------------------+  
    15. 6 rows in set (0.01 sec) 

    新增备份账户

    1. mysql> grant replication slave on *.* to 'backup'@'%' identified by 'Root@123';  
    2. mysql> use mysql  
    3. mysql> select user,authentication_string,host from user; 

    重启mysql

    1. $ systemctl restart mysqld 

    登录mysql,并设置只读

    1. $ mysql -u root -p  
    2.   Root@123  
    3. mysql> flush tables with read lock;  
    4. mysql> show master status \\G  
    5. *************************** 1. row ***************************  
    6.              File: mysql-bin.000003  
    7.          Position: 154  
    8.      Binlog_Do_DB: itools_simple  
    9.  Binlog_Ignore_DB: mysql,information_schema,performation_schema,sys  
    10. Executed_Gtid_Set:  
    11.  1 row in set (0.01 sec)  
    12. // 导出数据库,进入到一个合适的文件夹  
    13. $ mysqldump -u root -p itools_simple > itools_simple.txt  
    14. // 把生成的文件导出到本机  
    15. $ sz itools_simple.txt  
    16. // 在window本机打开cmd,登录slave本机mysql数据库,导入数据库  
    17. $ .\\mysql -u root -p -hlocalhost mycat_master < .\\itools_simple.txt 

    slave从数据库配置

    配置 my.ini 文件

    1. [mysql]  
    2. #default-character-set=utf8  
    3. [mysqld]   
    4. max_connections=200  
    5. character-set-server=utf8  
    6. default-storage-engine=INNODB  
    7. # 添加一下配置  
    8. log-bin=mysql-bin  
    9. server-id=223 

    重启slave数据库

    1. net stop mysql  
    2. net start mysql 

    登录slave数据库

    1. $ mysql -u root -p  
    2.   123456  
    3. // 停止 slave  
    4. mysql> stop slave;  
    5. // 根据master中的status,添加以下配置  
    6. 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;  
    7. // 启用slave  
    8. mysql> start slave;  
    9. // 查看slave状态  
    10. mysql> show slave status \\G  
    11. *************************** 1. row ***************************  
    12.                Slave_IO_State: Waiting for master to send event  
    13.                   Master_Host: 192.168.0.105  
    14.                   Master_User: backup  
    15.                   Master_Port: 3306  
    16.                 Connect_Retry: 60  
    17.               Master_Log_File: mysql-bin.000003  
    18.           Read_Master_Log_Pos: 154  
    19.                Relay_Log_File: DESKTOP-OB9O5N7-relay-bin.000002  
    20.                 Relay_Log_Pos: 320  
    21.         Relay_Master_Log_File: mysql-bin.000003  
    22.              Slave_IO_Running: Yes  
    23.             Slave_SQL_Running: Yes  
    24.              ..............  
    25. 1 row in set (0.00 sec)  
    26. // 出现下面两个说明配置正确  
    27.  Slave_IO_Running: Yes  
    28. Slave_SQL_Running: Yes 

    关闭master数据库的只读设置

    1. 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数据库存储引擎

    1. mysql> show variables like '%storage_engine%';  
    2. +----------------------------------+--------+  
    3. | Variable_name                    | Value  |  
    4. +----------------------------------+--------+  
    5. | default_storage_engine           | InnoDB |  
    6. | default_tmp_storage_engine       | InnoDB |  
    7. | disabled_storage_engines         |        |  
    8. | internal_tmp_disk_storage_engine | InnoDB |  
    9. +----------------------------------+--------+  
    10. 4 rows in set (0.00 sec)  
    11. // 修改配置文件  
    12. $ vim /etc/my.ini  
    13.     [mysqld]  
    14.     default-storage-engine=MyISAM  
    15. $ systemctl restart mysqld 
    16. mysql> show variables like '%storage_engine%';  
    17. +----------------------------------+--------+  
    18. | Variable_name                    | Value  |  
    19. +----------------------------------+--------+  
    20. | default_storage_engine           | MyISAM |  
    21. | default_tmp_storage_engine       | InnoDB |  
    22. | disabled_storage_engines         |        |  
    23. | internal_tmp_disk_storage_engine | InnoDB |  
    24. +----------------------------------+--------+  
    25. 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指定一个参数。

    解决方案

    1. mysql> set global log_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,进入文件夹,下载安装包

    1. $ wget http://dl.mycat.io/1.6.7.5/2020-3-3/Mycat-server-1.6.7.5-test-20200303154735-linux.tar.gz  
    2. $ tar -zxvf Mycat-server-1.6.7.5-test-20200303154735-linux.tar.gz  
    3. $ cd mycat  
    4. $ useradd mycat  
    5. $ chown -R mycat:mycat /usr/local/mycat/mycat  
    6. $ passwd mycat  
    7. // 配置hostname,添加以下配置  
    8. [root@localhost mycat] vim /etc/sysconfig/network  
    9.     HOSTNAME=localhost(主机名)  
    10. // 查看是否配置主机  
    11. $ vim /etc/hosts 

    将Mycat配置到环境变量中

    1. $ vim /etc/profile  
    2. // 在最后添加  
    3. MYCAT_HOME=/usr/local/mycat/mycat  
    4. PATH=$MYCAT_HOME/bin:$PATH  
    5. export PATH  
    6. // 使配置生效  
    7. $ source /etc/profile 

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

    配置mycat的schema.xml

    1. xml version="1.0"?>  
    2. >  
    3. <mycat:schema xmlns:mycat="http://io.mycat/">  
    4.     <schema name="itools_simple" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">  
    5.     schema>  
    6.     <dataNode name="dn1" dataHost="localhost" database="itools_simple" />  
    7.     <dataHost name="localhost" maxCon="1000" minCon="10" balance="0"  
    8.               writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">  
    9.         <heartbeat>select user()heartbeat>  
    10.         <writeHost host="hostM1" url="192.168.0.105:3306" user="user1" password="Root@123">  
    11.               
    12.             <readHost host="hostS2" url="127.0.0.1:3306" user="user2" password="Root@123" />  
    13.         writeHost>  
    14.     dataHost>  
    15. mycat:schema> 

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

    1. <user name="user1" defaultAccount="true">  
    2.     <property name="password">Root@123property>  
    3.     <property name="schemas">itools_simpleproperty>  
    4.     <property name="defaultSchema">itools_simpleproperty>  
    5. user>  
    6. <user name="user2">  
    7.     <property name="password">Root@123property>  
    8.     <property name="schemas">itools_simpleproperty>  
    9.     <property name="readOnly">trueproperty>  
    10.     <property name="defaultSchema">itools_simpleproperty>  
    11. user> 

    启动Mycat

    启动mycat

    1. $ mycat start  
    2. Starting Mycat-server... 

    查看启动日志

    1. $ cat wrapper.log  
    2. MyCAT Server startup successfully. see logs in logs/mycat.log 

    使用客户端连接mycat

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

    编程语言SpringBoot整合MyCat实现读写分离

    使用Navicat连接

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

    SpringBoot 整合 MyCat 实现读写分离

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

    添加依赖

    1. <dependency>  
    2.     <groupId>org.springframework.bootgroupId>  
    3.     <artifactId>spring-boot-starter-aopartifactId>  
    4. dependency>  
    5. <dependency>  
    6.     <groupId>com.alibabagroupId>  
    7.     <artifactId>druidartifactId>  
    8.     <version>1.0.23version>  
    9. dependency> 

    创建数据源

    1. package com.muycode.itoolsimple.datasource;  
    2. import org.springframework.boot.context.properties.ConfigurationProperties;  
    3. import org.springframework.boot.jdbc.DataSourceBuilder;  
    4. import org.springframework.context.annotation.Bean;  
    5. import org.springframework.context.annotation.Configuration;  
    6. import javax.sql.DataSource;  
    7. @Configuration  
    8. public class DataSourceConfig {  
    9.     /**  
    10.      * 创建可读数据源  
    11.      *  
    12.      * @return  
    13.      */  
    14.     @Bean(name = "selectDataSource")  
    15.     @ConfigurationProperties(prefix = "spring.datasource.select")  
    16.     public DataSource dataSource1() {  
    17.         return DataSourceBuilder.create().build();  
    18.     }  
    19.     /**  
    20.      * 创建可写数据源  
    21.      *  
    22.      * @return  
    23.      */  
    24.     @Bean(name = "updateDataSource")  
    25.     @ConfigurationProperties(prefix = "spring.datasource.update")  
    26.     public DataSource dataSource2() {  
    27.         return DataSourceBuilder.create().build();  
    28.     }  

    设置数据源

    1. package com.muycode.itoolsimple.datasource;  
    2. import org.springframework.context.annotation.Lazy;  
    3. import org.springframework.stereotype.Component;  
    4. @Component  
    5. @Lazy(false)  
    6. public class DataSourceContextHolder {  
    7.     /**  
    8.      * 采用ThreadLocal 保存本地多数据源  
    9.      */  
    10.     private static final ThreadLocal<String> contextHolder = new ThreadLocal<>();  
    11.     /**  
    12.      * 设置数据源类型  
    13.      *  
    14.      * @param dbType  
    15.      */  
    16.     public static void setDbType(String dbType) {  
    17.         contextHolder.set(dbType);  
    18.     }  
    19.     /**  
    20.      * 获取数据源类型  
    21.      */  
    22.     public static String getDbType() {  
    23.         return contextHolder.get();  
    24.     }  
    25.     public static void clearDbType() {  
    26.         contextHolder.remove();  
    27.     }  

    返回数据源

    1. package com.muycode.itoolsimple.datasource; 
    2. import org.springframework.beans.factory.annotation.Autowired;  
    3. import org.springframework.beans.factory.annotation.Qualifier;  
    4. import org.springframework.context.annotation.Primary;  
    5. import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;  
    6. import org.springframework.stereotype.Component;  
    7. import javax.sql.DataSource;  
    8. import java.util.HashMap;  
    9. import java.util.Map;  
    10. @Component  
    11. @Primary 
    12.  public class DynamicDataSource extends AbstractRoutingDataSource {  
    13.     @Autowired  
    14.     @Qualifier("selectDataSource")  
    15.     private DataSource selectDataSource; 
    16.     @Autowired  
    17.     @Qualifier("updateDataSource")  
    18.     private DataSource updateDataSource;  
    19.     /**  
    20.      * 返回生效的数据源名称  
    21.      */  
    22.     @Override  
    23.     protected Object determineCurrentLookupKey() {  
    24.         return DataSourceContextHolder.getDbType();  
    25.     }  
    26.     /**  
    27.      * 配置数据源信息  
    28.      */  
    29.     @Override  
    30.     public void afterPropertiesSet() {  
    31.         Map<Object, Object> map = new HashMap<>(16);  
    32.         map.put("selectDataSource", selectDataSource);  
    33.         map.put("updateDataSource", updateDataSource);  
    34.         setTargetDataSources(map);  
    35.         setDefaultTargetDataSource(updateDataSource);  
    36.         super.afterPropertiesSet();  
    37.     }  

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

    1. package com.muycode.itoolsimple.datasource;  
    2. import org.aspectj.lang.JoinPoint;  
    3. import org.aspectj.lang.annotation.Aspect;  
    4. import org.aspectj.lang.annotation.Before;  
    5. import org.springframework.context.annotation.Lazy;  
    6. import org.springframework.core.annotation.Order;  
    7. import org.springframework.stereotype.Component;  
    8. @Aspect  
    9. @Component  
    10. @Lazy(false)  
    11. @Order(0) // Order设定AOP执行顺序 使之在数据库事务上先执行  
    12. public class DataSourceOptionAop {  
    13.     /**  
    14.      * 可读数据源  
    15.      */  
    16.     private final static String DATASOURCE_TYPE_SELECT = "selectDataSource";  
    17.     /**  
    18.      * 可写数据源  
    19.      */  
    20.     private final static String DATASOURCE_TYPE_UPDATE = "updateDataSource";  
    21.     /**  
    22.      * 创建切面,根据方法类型选择不同的数据源  
    23.      *  
    24.      * @param joinPoint  
    25.      */  
    26.     @Before("execution(* com.muycode.itoolsimple.service.*.*(..))")  
    27.     public void process(JoinPoint joinPoint) { 
    28.          String methodName = joinPoint.getSignature().getName();  
    29.         System.out.print("=========== " + methodName);  
    30.         if (methodName.startsWith("get") || methodName.startsWith("count") || methodName.startsWith("find")  
    31.                 || methodName.startsWith("list") || methodName.startsWith("select") || methodName.startsWith("check")  
    32.                 || methodName.startsWith("query")) {  
    33.             DataSourceContextHolder.setDbType(DATASOURCE_TYPE_SELECT);  
    34.             System.out.println("-----------------使用selectDataSource数据源-------------------");  
    35.         } else {  
    36.             DataSourceContextHolder.setDbType(DATASOURCE_TYPE_UPDATE);  
    37.             System.out.println("-----------------使用updateDataSource数据源-------------------");  
    38.         }  
    39.     }  

    输出结果

    1. =========== getByUsername-----------------使用selectDataSource数据源-------------------  
    2. =========== getPermissionStringByUserId-----------------使用selectDataSource数据源-------------------  
    3. =========== getPermissionByUserId-----------------使用selectDataSource数据源-------------------  
    4. =========== getRolePermissionLinkByUserId-----------------使用selectDataSource数据源-------------------  
    5. =========== save-----------------使用updateDataSource数据源-------------------  
    6. =========== queryByPage-----------------使用selectDataSource数据源-------------------  
    7. =========== save-----------------使用updateDataSource数据源-------------------  
    8. =========== getPermissionAll-----------------使用selectDataSource数据源-------------------  
    9. =========== save-----------------使用updateDataSource数据源-------------------  
    10. =========== getSysCodeAll-----------------使用selectDataSource数据源-------------------  
    11. =========== save-----------------使用updateDataSource数据源-------------------  
    12. =========== getByRid-----------------使用selectDataSource数据源-------------------  
      编程语言(programming language)是一种被标准化的交流技巧,用来向计算机发出指令,定义计算机程序,让程序员能够准确地定义计算机所需要使用的数据,并精确地定义在不同情况下所应当采取的行动的一种计算机语言。 编程语言可以分成机器语言、汇编语言、高级语言三大类。计算机领域已发明了上千不同的编程语言,而且每年仍有新的编程语言诞生。

课课家教育

未登录