linux配置mycat读写分离

谁借莪１个温暖的怀抱￠ 2022-04-11 01:43 246阅读 0赞

mysql主从复制请参考：[https://blog.csdn.net/qq\_38270106/article/details/84780576][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"?>
    
    <!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>
    
            
            
        </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/">
        
        <schema name="mycat_testdb" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1"></schema>
        
        <dataNode name="dn1" dataHost="localhost1" database="test" />
        
        <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 （有读和写操作）

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4MjcwMTA2_size_16_color_FFFFFF_t_70][]

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

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4MjcwMTA2_size_16_color_FFFFFF_t_70 1][]

### 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 /> 
    	</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_getList]

![20181204234202124.png][]

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

![20181204234246109.png][]

数据库

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4MjcwMTA2_size_16_color_FFFFFF_t_70 2][]

[https_blog.csdn.net_qq_38270106_article_details_84780576]: https://blog.csdn.net/qq_38270106/article/details/84780576
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4MjcwMTA2_size_16_color_FFFFFF_t_70]: /images/20220411/36ee5e11a3ee48eaa02ec1b51e714e34.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4MjcwMTA2_size_16_color_FFFFFF_t_70 1]: /images/20220411/63ed871975cb4471a4f6bdf101f2832c.png
[http_localhost_8080_getList]: http://localhost:8080/getList
[20181204234202124.png]: /images/20220411/bfae8b5fde574583ad724b8ac34f1e89.png
[http_localhost_8080_update_id_1000_name_robbie]: http://localhost:8080/update?id=1000&name=robbie
[20181204234246109.png]: /images/20220411/233ec2f6917b420f9b1939bc076f0671.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4MjcwMTA2_size_16_color_FFFFFF_t_70 2]: /images/20220411/da64f73ec4f64c089ae4dd521532c11b.png