一、什么是MyCat
MyCAT是一款由阿里Cobar演变而来的用于支持数据库,读写分离、分表分库的分布式中间件。MyCAT支持Oracle、MSSQL、MYSQL、PG、DB2关系型数据库,同时也支持MongoDB等非关系型数据库。
MyCAT原理MyCAT主要是通过对SQL的拦截,然后经过一定规则的分片解析、路由分析、读写分离分析、缓存分析等,然后将SQL发给后端真实的数据块,并将返回的结果做适当处理返回给客户端。
官方网站:http://www.mycat.io/
二、环境搭建
架构图:
1、首先搭建MySQL主从复制,参考:MySQL主从复制环境搭建
2、节点准备
192.168.112.142(master)
192.168.112.143(slave)
192.168.112.144(mycat)
3、上传mycat压缩包并解压
cd /usr/local
tar -zxvf Mycat-server-1.6.5-release-20180122220033-linux.tar.gz
4、进入到/mycat/conf目录下,新增两个配置文件
cd /mycat/conf
vi schema.xml
(里面的数据库名称和服务器地址相应修改)
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<!-- TESTDB1 是mycat的逻辑库名称,链接需要用的 -->
<schema name="mycat_testdb" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1"></schema>
<!-- database 是MySQL数据库的库名 -->
<dataNode name="dn1" dataHost="localhost1" database="test" />
<!--
dataNode节点中各属性说明:
name:指定逻辑数据节点名称;
dataHost:指定逻辑数据节点物理主机节点名称;
database:指定物理主机节点上。如果一个节点上有多个库,可使用表达式db$0-99, 表示指定0-99这100个数据库;
dataHost 节点中各属性说明:
name:物理主机节点名称;
maxCon:指定物理主机服务最大支持1000个连接;
minCon:指定物理主机服务最小保持10个连接;
writeType:指定写入类型;
0,只在writeHost节点写入;
1,在所有节点都写入。慎重开启,多节点写入顺序为默认写入根据配置顺序,第一个挂掉切换另一个;
dbType:指定数据库类型;
dbDriver:指定数据库驱动;
balance:指定物理主机服务的负载模式。
0,不开启读写分离机制;
1,全部的readHost与stand by writeHost参与select语句的负载均衡,简单的说,当双主双从模式(M1->S1,M2->S2,并且M1与 M2互为主备),正常情况下,M2,S1,S2都参与select语句的负载均衡;
2,所有的readHost与writeHost都参与select语句的负载均衡,也就是说,当系统的写操作压力不大的情况下,所有主机都可以承担负载均衡;
-->
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="3" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<!-- 可以配置多个主从 -->
<writeHost host="hostM1" url="192.168.112.142:3306" user="root" password="123456">
<!-- 可以配置多个从库 -->
<readHost host="hostS2" url="192.168.112.143:3306" user="root" password="123456" />
</writeHost>
</dataHost>
</mycat:schema>
vi server.xml
(两个账号,root账号可以进行读和写,user账号只能读)
<?xml version="1.0" encoding="UTF-8"?>
<!-- - - Licensed under the Apache License, Version 2.0 (the "License");
- you may not use this file except in compliance with the License. - You
may obtain a copy of the License at - - http://www.apache.org/licenses/LICENSE-2.0
- - Unless required by applicable law or agreed to in writing, software -
distributed under the License is distributed on an "AS IS" BASIS, - WITHOUT
WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. - See the
License for the specific language governing permissions and - limitations
under the License. -->
<!DOCTYPE mycat:server SYSTEM "server.dtd">
<mycat:server xmlns:mycat="http://io.mycat/">
<!-- 读写都可用的用户 -->
<user name="root" defaultAccount="true">
<property name="password">123456</property>
<property name="schemas">mycat_testdb</property>
<!-- 表级 DML 权限设置 -->
<!--
<privileges check="false">
<schema name="TESTDB" dml="0110" >
<table name="tb01" dml="0000"></table>
<table name="tb02" dml="1111"></table>
</schema>
</privileges>
-->
</user>
<!-- 只读用户 -->
<user name="user">
<property name="password">123456</property>
<property name="schemas">mycat_testdb</property>
<property name="readOnly">true</property>
</user>
</mycat:server>
5、启动mycat
cd /usr/local/mycat/bin/
./mycat start
查看mycat是否启动成功:
cd /usr/local/mycat/logs/
cat wrapper.log
6、使用远程工具连接mycat(注意端口号默认为8066)
账号密码:root/123456(可读写)
账号密码:user/123456(可读)
user账户下,直接报错,只能进行读操作
三、SpringBoot2.0整合动态数据源
实现原理:
在Spring 2.0.1中引入了AbstractRoutingDataSource, 该类充当了DataSource的路由中介, 能有在运行时, 根据某种key值来动态切换到真正的DataSource上。
1.项目中需要集成多个数据源分别为读和写的数据源,绑定不同的key。
2.采用AOP技术进行拦截业务逻辑层方法,判断方法的前缀是否需要写或者读的操作
3.如果方法的前缀是写的操作的时候,直接切换为写的数据源,反之切换为读的数据源
也可以自己定义注解进行封装
maven依赖:
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.0.1.RELEASE</version>
<relativePath /> <!-- lookup parent from repository -->
</parent>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-aop</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>1.3.2</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.0.23</version>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
application.yml
spring:
datasource:
###可读数据源
select:
jdbc-url: jdbc:mysql://192.168.112.144:8066/mycat_testdb
driver-class-name: com.mysql.jdbc.Driver
username: user
password: user
####可写数据源
update:
jdbc-url: jdbc:mysql://192.168.112.144:8066/mycat_testdb
driver-class-name: com.mysql.jdbc.Driver
username: root
password: 123456
type: com.alibaba.druid.pool.DruidDataSource
UserEntity
public class UserEntity {
private String userName;
public String getUserName() {
return userName;
}
public void setUserName(String userName) {
this.userName = userName;
}
}
UserMapper
public interface UserMapper {
@Select("SELECT * FROM user_info ")
List<UserEntity> findUser();
@Select("insert into user_info values (#{userName}); ")
List<UserEntity> insertUser(@Param("userName") String userName);
}
UserService
@Service
public class UserService {
@Autowired
private UserMapper userMapper;
public List<UserEntity> findUser() {
return userMapper.findUser();
}
public List<UserEntity> insertUser(String userName) {
return userMapper.insertUser(userName);
}
}
UserController
@RestController
public class UserController {
@Autowired
private UserService userService;
@RequestMapping("/findUser")
public List<UserEntity> findUser() {
return userService.findUser();
}
@RequestMapping("/insertUser")
public List<UserEntity> insertUser(String userName) {
return userService.insertUser(userName);
}
}
DataSourceContextHolder
@Component
@Lazy(false)
public class DataSourceContextHolder {
// 采用ThreadLocal 保存本地多数据源
private static final ThreadLocal<String> contextHolder = new ThreadLocal<>();
// 设置数据源类型
public static void setDbType(String dbType) {
contextHolder.set(dbType);
}
public static String getDbType() {
return contextHolder.get();
}
public static void clearDbType() {
contextHolder.remove();
}
}
DataSourceConfig
@Configuration
public class DataSourceConfig {
// 创建可读数据源
@Bean(name = "selectDataSource")
@ConfigurationProperties(prefix = "spring.datasource.select") // application.properteis中对应属性的前缀
public DataSource dataSource1() {
return DataSourceBuilder.create().build();
}
// 创建可写数据源
@Bean(name = "updateDataSource")
@ConfigurationProperties(prefix = "spring.datasource.update") // application.properteis中对应属性的前缀
public DataSource dataSource2() {
return DataSourceBuilder.create().build();
}
}
DynamicDataSource
@Component
@Primary
public class DynamicDataSource extends AbstractRoutingDataSource {
@Autowired
@Qualifier("selectDataSource")
private DataSource selectDataSource;
@Autowired
@Qualifier("updateDataSource")
private DataSource updateDataSource;
/**
* 这个是主要的方法,返回的是生效的数据源名称
*/
@Override
protected Object determineCurrentLookupKey() {
System.out.println("DataSourceContextHolder:::" + DataSourceContextHolder.getDbType());
return DataSourceContextHolder.getDbType();
}
/**
* 配置数据源信息
*/
@Override
public void afterPropertiesSet() {
Map<Object, Object> map = new HashMap<>();
map.put("selectDataSource", selectDataSource);
map.put("updateDataSource", updateDataSource);
setTargetDataSources(map);
setDefaultTargetDataSource(updateDataSource);
super.afterPropertiesSet();
}
}
SwitchDataSourceAOP aop切面类进行方法拦截
@Aspect
@Component
@Lazy(false)
@Order(0) // Order设定AOP执行顺序 使之在数据库事务上先执行
public class SwitchDataSourceAOP {
// 这里切到你的方法目录
@Before("execution(* com.xwhy.service.*.*(..))")
public void process(JoinPoint joinPoint) {
String methodName = joinPoint.getSignature().getName();
if (methodName.startsWith("get") || methodName.startsWith("count") || methodName.startsWith("find")
|| methodName.startsWith("list") || methodName.startsWith("select") || methodName.startsWith("check")) {
// 读
DataSourceContextHolder.setDbType("selectDataSource");
} else {
// 切换dataSource
DataSourceContextHolder.setDbType("updateDataSource");
}
}
}
启动类
@SpringBootApplication
@MapperScan(basePackages = "com.xwhy.mapper")
public class AppMycat {
public static void main(String[] args) {
SpringApplication.run(AppMycat .class, args);
}
}
sql脚本:
DROP TABLE IF EXISTS `user_info`;
CREATE TABLE `user_info` (
`username` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
启动测试:
访问:http://localhost:8080/insertUser?userName=lisi
走的是写数据库,看后台打印日志:
访问:http://localhost:8080/findUser
走的是读数据库: