mysql主从同步及Spring Boot读写分离实践应用

mysql主从同步及Spring Boot读写分离实践应用

一、数据库环境搭建

1、搭建数据库

a、主数据库服务master-mysql，用于写数据

docker run -p 3339:3306 --name master-mysql -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7

b、从数据库服务slave-mysql，用于读数据

docker run -p 3340:3306 --name slave-mysql -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7

2、配置Master

#1、进入容器内部
docker exec -it master-mysql /bin/bash
#2、切换目录到/etc/mysql下
#3、编辑my.cnf添加以下内容：
	[mysqld]
    ## 同一局域网内注意要唯一
    server-id=100  
    ## 开启二进制日志功能，可以随便取（关键）
    log-bin=mysql-bin
#4、重启 master-mysql容器使配置生效
#5、创建数据同步用户并授权
	CREATE USER 'slave'@'%' IDENTIFIED BY '123456';
	GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'%';

3、配置Slave

#1、进入容器内部
docker exec -it slave-mysql /bin/bash
#2、切换目录到/etc/mysql下
#3、编辑my.cnf添加以下内容：
	[mysqld]
    ## 设置server_id,注意要唯一
    server-id=101  
    ## 开启二进制日志功能，以备Slave作为其它Slave的Master时使用
    log-bin=mysql-slave-bin   
    ## relay_log配置中继日志
    relay_log=edu-mysql-relay-bin  
#4、重启 slave-mysql容器使配置生效

4、链接主从库

-- 1、在主库服务master-mysql下进入mysql
-- 2、查看master状态
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
-- 3、在从库服务slave-mysql下进入mysql
-- 4、配置master
mysql> change master to master_host='172.17.0.3', master_user='slave', master_password='123456', master_port=3306, master_log_file='mysql-bin.000002', master_log_pos= 154, master_connect_retry=30;
-- *参数说明：
    -- master_host ：Master的地址，指的是容器的独立ip,可以通过docker inspect --format='{{.NetworkSettings.IPAddress}}' 容器名称|容器id查询容器的ip
    -- master_port：Master的端口号，指的是容器的端口号
    -- master_user：用于数据同步的用户
    -- master_password：用于同步的用户的密码
    -- master_log_file：指定 Slave 从哪个日志文件开始复制数据，即上文中提到的 File 字段的值
    -- master_log_pos：从哪个 Position 开始读，即上文中提到的 Position 字段的值
    -- master_connect_retry：如果连接失败，重试的时间间隔，单位是秒，默认是60秒
-- 5、开启主从同步
mysql> start slave;
-- 6、查看主从同步状态
mysql> show slave status;
-- SlaveIORunning 和 SlaveSQLRunning 都是Yes，说明主从同步已经开启。

5、测试

在Master创建一个新的数据库，然后刷新Slave，检查是否已同步新的数据库。

二、Spring Boot实践应用

1、添加依赖

<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.springframework.boot</groupId>
    <artifactId>spring-boot-starter-aop</artifactId>
</dependency>
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <scope>runtime</scope>
</dependency>
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid-spring-boot-starter</artifactId>
    <version>1.1.23</version>
</dependency>
<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus-boot-starter</artifactId>
    <version>3.4.3</version>
</dependency>
<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus-generator</artifactId>
    <version>3.4.1</version>
</dependency>
<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <optional>true</optional>
</dependency>

2、配置文件

spring:
  datasource:
    druid:
      master: #主库，用来写入数据
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://192.168.191.105:3339/readorwrite?useUnicode=true&characterEncoding=utf-8&useSSL=true&serverTimezone=UTC
        username: root
        password: 123456
      slave: #从库，用来读取数据
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://192.168.191.105:3340/readorwrite?useUnicode=true&characterEncoding=utf-8&useSSL=true&serverTimezone=UTC
        username: root
        password: 123456

3、实现

1、定义DataSourceTypeEnum类管理多个数据源名称

public enum DataSourceTypeEnum {
    MASTER, SLAVE;
}

2、定义DataSourceContextHolder类管理数据源

@Slf4j
public class DataSourceContextHolder {
    private static final ThreadLocal<DataSourceTypeEnum> DB_CONTEXT_LOCAL =new ThreadLocal<>();

    /**
     * 设置指定数据库到上下文
     * @param dbType 数据库类型
     */
    public static void setDbContextLocal(DataSourceTypeEnum dbType) {
        DB_CONTEXT_LOCAL.set(dbType);
        log.info("数据库切换至【{}】",dbType);
    }
    /**
     * 从上下文中获取当前数据库
     */
    public static DataSourceTypeEnum getDbContextLocal() {
        return DB_CONTEXT_LOCAL.get();
    }
    /**
     * 移除上下文
     */
    public static void toRemove() {
        DB_CONTEXT_LOCAL.remove();
    }
}

3、定义RoutingDataSource类作为数据源路由

@Slf4j
public class RoutingDataSource extends AbstractRoutingDataSource {
    /**
     * 检索当前目标数据源
     * @return
     */
    @Override
    protected Object determineCurrentLookupKey() {
        log.info(">>>线程：{},数据源：{}<<<", Thread.currentThread().getName(),DataSourceContextHolder.getDbContextLocal());
        return DataSourceContextHolder.getDbContextLocal();
    }
}

4、定义DataSourceConfig类配置多数据源

@Configuration
@MapperScan(basePackages = "cn.ming.springbootreadwriteseparationdemo.mapper")
public class DataSourceConfig {
    /**
     * 创建主数据源实例
     * @return DataSource
     */
    @Bean(name = "masterDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.druid.master")
    public DataSource masterDataSource() {
        return new DruidDataSource();
    }
    /**
     * 创建从数据源实例
     * @return DataSource
     */
    @Bean(name = "slaveDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.druid.slave")
    public DataSource slaveDataSource() {
        return new DruidDataSource();
    }

    /**
     * 路由数据源
     * @param masterDataSource
     * @param slaveDataSource
     * @return
     */
    @Bean(name = "routingDataSource")
    public DataSource routingDataSource(@Qualifier("masterDataSource") DataSource masterDataSource, @Qualifier("slaveDataSource") DataSource slaveDataSource) {
        Map<Object,Object> targetDataSources = new HashMap<>(2);
        targetDataSources.put(DataSourceTypeEnum.MASTER,masterDataSource);
        targetDataSources.put(DataSourceTypeEnum.SLAVE,slaveDataSource);
        RoutingDataSource routingDataSource=new RoutingDataSource();
        //设置默认数据源为Master
        routingDataSource.setDefaultTargetDataSource(masterDataSource);
        //设置路由表
        routingDataSource.setTargetDataSources(targetDataSources);
        return routingDataSource;
    }
}

5、定义@MasterDB，@SlaveDB用来指定方法所使用的数据源

@Target({ElementType.TYPE, ElementType.METHOD})
@Retention(RetentionPolicy.RUNTIME)
public @interface MasterDB {
}

@Target({ElementType.TYPE, ElementType.METHOD})
@Retention(RetentionPolicy.RUNTIME)
public @interface SlaveDB {
}

6、定义DataSourceAop类，在注解方法执行前切换数据源

@Aspect
@Order(1) //值越小，越优先执行 要优于事务的执行,在启动类中加上了@EnableTransactionManagement
@Component
public class DataSourceAop {
    /**
     * 写数据方法切面，用于切换到主数据库
     */
    @Before("@annotation(cn.ming.springbootreadwriteseparationdemo.commons.annatation.MasterDB)")
    public void masterData() {
        DataSourceContextHolder.setDbContextLocal(DataSourceTypeEnum.MASTER);
    }
    /**
     * 读数据方法切面，用于切换到从数据库
     */
    @Before("@annotation(cn.ming.springbootreadwriteseparationdemo.commons.annatation.SlaveDB)")
    public void slaveData() {
        DataSourceContextHolder.setDbContextLocal(DataSourceTypeEnum.SLAVE);
    }
}

7、定义MybatisPlusConfig类配置mybatis-plus

@Configuration
@EnableTransactionManagement
@MapperScan(basePackages = "cn.ming.springbootreadwriteseparationdemo.mapper")
public class MybatisPlusConfig {
    @Resource(name = "routingDataSource")
    private DataSource routingDataSource;

    /**
     * mybatis-plus SqlSessionFactory配置初始化
     * @return SqlSessionFactory
     * @throws Exception
     */
    @Bean
    public SqlSessionFactory sqlSessionFactory() throws Exception {
        MybatisSqlSessionFactoryBean sqlSessionFactoryBean = new MybatisSqlSessionFactoryBean();
        sqlSessionFactoryBean.setDataSource(routingDataSource);
        sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/*Mapper.xml"));
        MybatisConfiguration mybatisConfiguration = new MybatisConfiguration();
        //开启日志输出
        mybatisConfiguration.setLogImpl(StdOutImpl.class);
        //开启Mybatis二级缓存 默认true
        mybatisConfiguration.setCacheEnabled(false);
        //是否开启自动驼峰命名规则（camel case）映射，默认true
        mybatisConfiguration.setMapUnderscoreToCamelCase(true);
        sqlSessionFactoryBean.setConfiguration(mybatisConfiguration);
        return sqlSessionFactoryBean.getObject();
    }

    /**
     * 事务配置初始化
     * @return DataSourceTransactionManager
     */
    @Bean
    public DataSourceTransactionManager transactionManager() {
        DataSourceTransactionManager transactionManager = new DataSourceTransactionManager();
        transactionManager.setDataSource(routingDataSource);
        return transactionManager;
    }
}

4、测试

@Service
public class UserServiceImpl implements UserService {
    @Autowired(required = false)
    private UserMapper userMapper;
    /**
     * 获取用户列表
     *
     * @return
     */
    @SlaveDB
    @Override
    public List<SysUser> getList() {
        return userMapper.selectList(new QueryWrapper<>());
    }

    /**
     * 添加新用户
     */
    @MasterDB
    @Override
    public void addUser() {
        SysUser sysUser = new SysUser();
        sysUser.setName("rwst");
        sysUser.setPassword("3333");
        userMapper.insert(sysUser);
    }
}

控制台输出：

5、源码分享