spring boot主从库配置 springboot数据库主从

一、MySQL主从复制（CentOS7+MySQL5.7）

1.1、主从复制的原理

 

1.2、数据库信息

 10.211.55.4：主库master

10.211.55.5：从库slave

 10.211.55.6：从库slave

1.3、启动/停止

service  mysqld  start|stop|restart
systemctl  start|stop|restart  mysqld

1.4、主库设置

1.4.1、停止MySQL服务

1.4.2、编辑/etc/my.cnf文件，在[mysqld]下增加如下两行设置

log-bin=mysql-bin # 非必需

server-id=1　　　　# 必需

1.4.3、创建用于数据同步的账户

1.4.3.1、启动MySQL

1.4.3.2、登录MySQL客户端

 

 1.4.3.3、依次执行命令

CREATE USER 'repl’@’10.211.55.%’ IDENTIFIED BY ‘Aa123123.’;
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'10.211.55.%';
FLUSH PRIVILEGES;

1.4.4、查看主库master状态 

show variables like ‘log_bin%’;

show master status;

Ps：这里需要记住File和Position字段的值，在从库的设置中会用到 

1.5、从库设置

1.5.1、停止MySQL服务

 1.5.2、编辑/etc/my.cnf文件，在[mysqld]下增加如下设置server-id

server-id=2

1.5.3、执行同步语句，并启动slave 

1.5.3.1、启动MySQL、登录MySQL

 

 1.5.3.2、执行命令

change master to master_host='10.211.55.4', master_user='repl', master_password=‘Aa123123.’, master_log_file='mysql-bin.000001', master_log_pos=767;

1.5.4、修改从库UUID

Ps：由于两个从库的Linux服务器是从主库Linux服务器克隆来的，所以会导致主库和2个从库的MySQL数据库的UUID相同，这时我们需要修改2个从库的UUID否则从库slave_io_runnint值一致为no；只要保证3个数据库UUID值不一样就行

vi /var/lib/mysql/auto.cnf 

1.5.5、查看从库slave状态

1.5.5.1、重启mysql，再次登录MySQL

 

1.5.5.2、查看从库状态

show slave status\G

1.5.6、设置从库为只读账户

登录MySQL客户端 

create user ‘pig’@‘%’ identified by ‘Aa123123.’;
grant select on test.* to ‘pig’@‘%’
flush privileges;

二、MySQL主从复制容易出现的问题

当我们配置好主从同步后，如果重启服务器或者其他原因导致服务器宕机，则数据将无法同步，以下两种情况最常见

 2.1、Slave_SQL_Running:No

负责自己的slave mysql进程，用于update数据

解决办法如下，进入mysql客户端，依次执行命令：

stop slave;                                                      
SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE;            
start slave;                                                      
show slave status\G

至此，问题解决

2.2、Slave_IO_Running:No 

负责与主机（master）的io通信

2.2.1、问题1：UUIDs相同

由于两台从服务器（slave）是通过主服务器（master）克隆来的，所以三台机器安装MySQL的UUIDs是相同的，我们需要修改两台从服务器（slave）的UUIDs的值，让3台服务器的UUID值都不相同

解决办法：修改从库UUIDs

vi /var/lib/mysql/auto.cnf

2.2.2、问题2：主从日志文件mysql-bin不对应

 

解决办法如下，进入mysql客户端：

2.2.2.1、查看主服务器（master） 

show master status\G    

2.2.2.2、查看从服务器（slave）

 show slave status\G    

2.2.2.3、找到问题：发现Master_Log_File没有对应 

2.2.2.4、解决问题：在出现Slave_IO_Running:No的从服务器上操作

slave stop;                 
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000026', MASTER_LOG_POS=0;  
slave start;                               
show slave status\G

 

 至此，问题解决

三、SpringBoot+MySQL读写分离

3.1、创建Maven项目

3.2、修改pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>

	<groupId>org.personal.qin.demos</groupId>
	<artifactId>mysql_master_slave</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<packaging>jar</packaging>

	<name>mysql_master_slave</name>
	<description></description>

	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>2.0.5.RELEASE</version>
		<relativePath /> <!-- lookup parent from repository -->
	</parent>

	<properties>
		<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
		<project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
		<java.version>1.8</java.version>
	</properties>

	<dependencies>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-aop</artifactId>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-jdbc</artifactId>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>
		</dependency>
		<dependency>
			<groupId>org.apache.commons</groupId>
			<artifactId>commons-lang3</artifactId>
		</dependency>

		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-test</artifactId>
			<scope>test</scope>
		</dependency>

		<!-- MySQL:Begin -->
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
		</dependency>
		<!-- MySQL:End -->

		<!-- mybatis:Begin -->
		<dependency>
			<groupId>org.mybatis.spring.boot</groupId>
			<artifactId>mybatis-spring-boot-starter</artifactId>
			<version>1.3.2</version>
		</dependency>
		<dependency>
			<groupId>org.mybatis</groupId>
			<artifactId>mybatis-spring</artifactId>
			<version>1.3.1</version>
		</dependency>
		<dependency>
			<groupId>org.mybatis</groupId>
			<artifactId>mybatis</artifactId>
			<version>3.5.0</version>
		</dependency>
		<!-- mybatis:End -->
		<!-- BoneCP连接池(JDBC):Begin -->
		<dependency>
			<groupId>com.jolbox</groupId>
			<artifactId>bonecp-spring</artifactId>
			<version>0.8.0.RELEASE</version>
		</dependency>
		<!-- BoneCP连接池(JDBC):End -->
	</dependencies>

	<build>
		<plugins>
			<plugin>
				<groupId>org.springframework.boot</groupId>
				<artifactId>spring-boot-maven-plugin</artifactId>
			</plugin>
		</plugins>
	</build>
</project>

3.3、配置数据源

这里，我们配置了4个数据源，1个master，2两个slave，1个路由数据源。前3个数据源都是为了生成第4个数据源，而且后续我们只用这最后一个路由数据源。

application.yum

spring:
  datasource:
    master:
      jdbc-url: jdbc:mysql://10.211.55.4:3306/test_datasource
      username: root
      password: Aa123123.
      driver-class-name: com.mysql.jdbc.Driver
    slave1:
      jdbc-url: jdbc:mysql://10.211.55.5:3306/test_datasource
      username: pig   # 只读账户
      password: Aa123123.
      driver-class-name: com.mysql.jdbc.Driver
    slave2:
      jdbc-url: jdbc:mysql://10.211.55.6:3306/test_datasource
      username: pig   # 只读账户
      password: Aa123123.
      driver-class-name: com.mysql.jdbc.Driver

package demo.datasource.config;

import java.util.HashMap;
import java.util.Map;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Qualifier;
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;

import demo.datasource.config.bean.MyRoutingDataSource;
import demo.datasource.config.enums.DBTypeEnum;

/**
 * 多数据源配置
 */

@Configuration
public class DataSourceConfig {

    @Bean
    @ConfigurationProperties("spring.datasource.master")
    public DataSource masterDataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean
    @ConfigurationProperties("spring.datasource.slave1")
    public DataSource slave1DataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean
    @ConfigurationProperties("spring.datasource.slave2")
    public DataSource slave2DataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean
    public DataSource myRoutingDataSource(@Qualifier("masterDataSource") DataSource masterDataSource,
                                          @Qualifier("slave1DataSource") DataSource slave1DataSource,
                                          @Qualifier("slave2DataSource") DataSource slave2DataSource) {
        Map<Object, Object> targetDataSources = new HashMap<>();
        targetDataSources.put(DBTypeEnum.MASTER, masterDataSource);
        targetDataSources.put(DBTypeEnum.SLAVE1, slave1DataSource);
        targetDataSources.put(DBTypeEnum.SLAVE2, slave2DataSource);
        MyRoutingDataSource myRoutingDataSource = new MyRoutingDataSource();
        myRoutingDataSource.setDefaultTargetDataSource(masterDataSource);
        myRoutingDataSource.setTargetDataSources(targetDataSources);
        return myRoutingDataSource;
    }

}

3.4、MyBatista配置MyBatisConfig

由于Spring容器中现在有4个数据源，所以我们需要为事务管理器和MyBatis手动指定一个明确的数据源。

package demo.datasource.config;

import javax.annotation.Resource;
import javax.sql.DataSource;

import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.boot.autoconfigure.SpringBootVFS;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.core.io.support.ResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;

/**
 * MyBatis配置
 */
@EnableTransactionManagement
@Configuration
public class MyBatisConfig {

	@Resource(name = "myRoutingDataSource")
	private DataSource myRoutingDataSource;

	@Bean
	public SqlSessionFactory sqlSessionFactory() throws Exception {
		SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
		// 设置数据源
		sqlSessionFactoryBean.setDataSource(myRoutingDataSource);
		// 添加一行如下代码,解决setTypeAliasesPackage别名设置不起作用问题
		sqlSessionFactoryBean.setVfs(SpringBootVFS.class);
		
		//设置mybatis的主配置文件
        ResourcePatternResolver resourceResolver = new PathMatchingResourcePatternResolver();
        org.springframework.core.io.Resource mybatisConfigXml = resourceResolver.getResource(Cast.MYBATIS_CONFIG);
		sqlSessionFactoryBean.setConfigLocation(mybatisConfigXml);
		// 设置别名包
        sqlSessionFactoryBean.setTypeAliasesPackage(Cast.PO_PACKAGE);
        PathMatchingResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
        sqlSessionFactoryBean.setMapperLocations(resolver.getResources(Cast.MAPPER_SCANNER));
		
		return sqlSessionFactoryBean.getObject();
	}

	@Bean
	public PlatformTransactionManager platformTransactionManager() {
		return new DataSourceTransactionManager(myRoutingDataSource);
	}
}

3.5、设置路由key/查找数据源 

目标数据源就是那前3个这个我们是知道的，但是使用的时候是如果查找数据源的呢？

首先，我们定义一个枚举来代表这三个数据源

package com.cjs.example.enums;

public enum DBTypeEnum {

    MASTER, SLAVE1, SLAVE2;

}

接下来，通过ThreadLocal将数据源设置到每个线程的上下文中

package demo.datasource.config.bean;

import java.util.concurrent.atomic.AtomicInteger;

import demo.datasource.config.enums.DBTypeEnum;
import demo.datasource.utils.Log;

/**
 * 通过ThreadLocal将数据源设置到每个线程上下文中
 */
public class DBContextHolder {

    private static final ThreadLocal<DBTypeEnum> contextHolder = new ThreadLocal<>();

    private static final AtomicInteger counter = new AtomicInteger(-1);

    public static void set(DBTypeEnum dbType) {
        contextHolder.set(dbType);
    }

    public static DBTypeEnum get() {
        return contextHolder.get();
    }

    public static void master() {
        set(DBTypeEnum.MASTER);
        Log.i(DBContextHolder.class, "切换到master");
    }

    public static void slave() {
        //  轮询
        int index = counter.getAndIncrement() % 2;
        if (counter.get() > 9999) {
            counter.set(-1);
        }
        Log.i(DBContextHolder.class, "轮询index："+index);
        if (index == 0) {
            set(DBTypeEnum.SLAVE1);
            Log.i(DBContextHolder.class, "切换到slave1");
        } else {
            set(DBTypeEnum.SLAVE2);
            Log.i(DBContextHolder.class, "切换到slave2");
        }
    }

}

设置路由key

package com.cjs.example.bean;

import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
import org.springframework.lang.Nullable;

public class MyRoutingDataSource extends AbstractRoutingDataSource {
    @Nullable
    @Override
    protected Object determineCurrentLookupKey() {
        return DBContextHolder.get();
    }

}

3.6、定义切面

package demo.datasource.aop;

import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Before;
import org.aspectj.lang.annotation.Pointcut;
import org.springframework.stereotype.Component;

import demo.datasource.config.bean.DBContextHolder;

/**
 * 设置路由key
 * 默认情况下，所有的查询都走从库，插入/修改/删除走主库。我们通过方法名来区分操作类型（CRUD）
 */
@Aspect
@Component
public class DataSourceAop {

    @Pointcut("!@annotation(demo.datasource.annotation.Master) " +
            "&& (execution(* demo.datasource.service..*.select*(..)) " +
            "|| execution(* demo.datasource.service..*.get*(..)))")
    public void readPointcut() {

    }

    @Pointcut("@annotation(demo.datasource.annotation.Master) " +
            "|| execution(* demo.datasource.service..*.insert*(..)) " +
            "|| execution(* demo.datasource.service..*.add*(..)) " +
            "|| execution(* demo.datasource.service..*.update*(..)) " +
            "|| execution(* demo.datasource.service..*.edit*(..)) " +
            "|| execution(* demo.datasource.service..*.delete*(..)) " +
            "|| execution(* demo.datasource.service..*.remove*(..))")
    public void writePointcut() {

    }

    @Before("readPointcut()")
    public void read() {
        DBContextHolder.slave();
    }

    @Before("writePointcut()")
    public void write() {
        DBContextHolder.master();
    }


    /**
     * 另一种写法：if...else...  判断哪些需要读从数据库，其余的走主数据库
     */
//    @Before("execution(* demo.datasource.service.impl.*.*(..))")
//    public void before(JoinPoint jp) {
//        String methodName = jp.getSignature().getName();
//
//        if (StringUtils.startsWithAny(methodName, "get", "select", "find")) {
//            DBContextHolder.slave();
//        }else {
//            DBContextHolder.master();
//        }
//    }
}

3.7、添加例外

有一般情况就有特殊情况，特殊情况是某些情况下我们需要强制读主库，针对这种情况，我们定义一个注解，用该主句标注的Service方法就读主库

package com.cjs.example.annotation;

public @interface Master {
}

3.8、定义Service和实现类

例如：我们有一张表member

package demo.datasource.service.impl;

import java.util.List;

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

import demo.datasource.annotation.Master;
import demo.datasource.entity.Member;
import demo.datasource.mapper.MemberMapper;
import demo.datasource.service.MemberService;

@Service
public class MemberServiceImpl implements MemberService {

    @Autowired
    private MemberMapper memberMapper;

//    @Transactional
    @Override
    public int insert(Member member) {
        return memberMapper.insert(member);
    }

    @Master
    @Override
    public int save(Member member) {
        return memberMapper.insert(member);
    }

    @Override
    public List<Member> selectAll() {
        return memberMapper.selectByExample();
    }

    @Master
    @Override
    public String getToken(String appId) {
        //  有些读操作必须读主数据库
        //  比如，获取微信access_token，因为高峰时期主从同步可能延迟
        //  这种情况下就必须强制从主数据读
        return appId;
    }
}

3.9、使用单元测试进行测试

package demo.datasource.test;

import java.util.List;

import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

import demo.datasource.entity.Member;
import demo.datasource.service.MemberService;

@RunWith(SpringRunner.class)
@SpringBootTest
public class DatasourceDemoApplicationTests {

	@Autowired
    private MemberService memberService;

    @Test
    public void testWrite() {
        Member member = new Member();
        member.setName("mike");
        memberService.insert(member);
    }

    @Test
    public void testRead() {
        for (int i = 0; i < 4; i++) {
            List<Member> selectAll = memberService.selectAll();
            for(Member m : selectAll) {
            	System.out.println(m);
            }
        }
    }

    @Test
    public void testSave() {
        Member member = new Member();
        member.setName("wangwu");
        memberService.save(member);
    }

    @Test
    public void testReadFromMaster() {
        memberService.getToken("1234");
    }
}

数据库效果：

 执行查询时控制台效果，会在两个从库中轮询：