linux配置mycat读写分离

mysql主从复制请参考：https://blog.csdn.net/qq_38270106/article/details/84780576

环境准备

192.168.128.159（master）

192.168.128.160（slave）

192.168.128.161（mycat）

1.上传，解压压缩包

cd /usr/local

tar -zxvf Mycat-server-1.6.5-release-20180122220033-linux.tar.gz

2.新增如下两个配置文件（里面的数据库名称和服务器地址相应修改）

 cd mycat/conf/

vi server.xml

<?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">root</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">user</property>
        <property name="schemas">mycat_testdb</property>
        <property name="readOnly">true</property>
    </user>

</mycat:server>

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.128.159:3306" user="root" password="root">
            <!-- 可以配置多个从库 -->
            <readHost host="hostS2" url="192.168.128.160:3306" user="root" password="root" />
        </writeHost>
    </dataHost>
</mycat:schema>

3.启动MyCAT

cd /usr/local/mycat/bin

./mycat start

4.远程工具连接mycat

mycat端口号：8066

账号密码：root/root （有读和写操作）

账号密码：user/user（登录只能读操作 ）

Java代码实现 

CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1002 DEFAULT CHARSET=utf8;

insert into test(name) values('zhangsan');
insert into test(name) values('lisi');

maven依赖

<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>2.0.4.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:
    ###mycat可读数据源
    select:
      jdbc-url: jdbc:mysql://192.168.128.161:8066/mycat_testdb
      driver-class-name: com.mysql.jdbc.Driver
      username: user
      password: user
    ####mycat可写数据源  
    update:
      jdbc-url: jdbc:mysql://192.168.128.161:8066/mycat_testdb
      driver-class-name: com.mysql.jdbc.Driver
      username: root
      password: root
    type: com.alibaba.druid.pool.DruidDataSource

DataSourceContextHolder.java 

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<>();

	// 设置数据源类型
	public static void setDbType(String dbType) {
		contextHolder.set(dbType);
	}

	public static String getDbType() {
		return contextHolder.get();
	}

	public static void clearDbType() {
		contextHolder.remove();
	}

}

DataSourceConfig.java 

import javax.sql.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;

@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.java

import javax.sql.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;

@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.java 

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 SwitchDataSourceAOP {
	
	// 这里切到你的方法目录
	@Before("execution(* com.mayikt.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");
		}
	}
}

 

Test.java 

public class Test {

	private Integer id;
	
	private String name;

	public Integer getId() {
		return id;
	}

	public void setId(Integer id) {
		this.id = id;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}
	
}

 UserMapper.java

public interface UserMapper {

	@Select("SELECT * FROM test")
	List<Test> getList();
	
	// Update
    @Update("UPDATE test SET NAME = #{name} WHERE id = #{id}")
    int update(Test test);
	

}

UserService.java 

import java.util.List;

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

import com.mayikt.entity.Test;
import com.mayikt.mapper.UserMapper;

@Service
public class UserService {

	@Autowired
	private UserMapper userMapper;
	
	public List<Test> getList() {
		return userMapper.getList();
	}
	
    public int update(Test test) {
    	return userMapper.update(test);
    }
	
}

UserController.java

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import com.mayikt.entity.Test;
import com.mayikt.service.UserService;

@RestController
public class UserController {

	
	@Autowired
	private UserService userService;
	
	/**
	 * http://localhost:8080/getList
	 * @return
	 */
	@RequestMapping("/getList")
	public List<Test> getList() {
		return userService.getList();
	}
	
	/**
	 * http://localhost:8080/update?id=1000&name=robbie
	 * @param test
	 * @return
	 */
	@RequestMapping("/update")
    public int update(Test test) {
    	return userService.update(test);
    }
}

SpringbootMycatApp.java

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@MapperScan(basePackages = "com.mayikt.mapper")
@SpringBootApplication
public class SpringbootMycatApp {

	public static void main(String[] args) {
		SpringApplication.run(SpringbootMycatApp.class, args);
	}
	
}

浏览器访问：http://localhost:8080/getList

浏览器访问：http://localhost:8080/update?id=1000&name=robbie

数据库