MySQL主从同步实现读写分离

1.首先配置SpringBoot+Mybatis-plus服务端

配置信息如下，先连接第一台mysql

#db
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://59.110.171.158 :3306/test?serverTimezone=Asia/Shanghai
spring.datasource.username=root
spring.datasource.password=root

#web
server.port=8080
server.servlet.context-path=/demo

2.测试单台mysql时服务是否正常

@RestController
public class DemoController {

    @Autowired
    private UserMapper userMapper;



    @RequestMapping(value = "/user/get",method = RequestMethod.GET)
    public Map<String,Object> getUserInfo(@RequestParam(value = "id",required = true)Long id){

        User user = userMapper.selectById(id);
        Map<String, Object> res = new HashMap<String,Object>();
        res.put("success",true);
        res.put("data",user);
        return res;
    }

}

此时服务正常运行

3.开始配置mysql主从复制

• 在主库上打开配置文件

vim /etc/my.cnf

• 添加如下配置，打开binlog

server-id = 1
log-bin = master-bin
log-bin-index = master-bin.index

• 重启mysql服务

systemctl restart mariadb.service

• 验证主库是否开启正常

MariaDB [(none)]> show master status;
+-------------------+----------+--------------+------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-bin.000001 |      245 |              |                  |
+-------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

file生成的binlog文件，position开始位置

• 主库创建一个用户并授权，用于让从库连接主库

MariaDB [(none)]> create user demo;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> grant replication slave on *.* to 'demo'@'%' identified by '123';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

• 在从库上打开配置文件

vim /etc/my.cnf

• 添加如下配置，打开relaylog

server-id = 2
relay-log = slave-relay-bin
relay-log-index = slave-relay-bin.index

• 重启mysql服务

systemctl restart mariadb.service

• 将主从库关联

登录从库，设置主库IP和Port，用于登录的用户名和密码，binlog文件位置等信息

MariaDB [(none)]> change master to master_host='59.110.171.154',master_port=3306,master_user='demo',master_password='123',master_log_file='master-bin.000001',master_log_pos=0;
Query OK, 0 rows affected (0.04 sec)

MariaDB [(none)]> start slave;

• 查看从库状态

MariaDB [test]> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 59.110.171.154
                  Master_User: demo
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-bin.000001
          Read_Master_Log_Pos: 534
               Relay_Log_File: slave-relay-bin.000002
                Relay_Log_Pos: 819
        Relay_Master_Log_File: master-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 534
              Relay_Log_Space: 1113
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
1 row in set (0.00 sec)

至此主从同步配置完成

4.SpringBoot配置多数据源，实现读写分离

• 首先配置文件配置多个数据源信息

#db
spring.datasource.hikari.master.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.hikari.master.jdbc-url=jdbc:mysql://59.110.171.157 :3306/demo?serverTimezone=Asia/Shanghai
spring.datasource.hikari.master.username=root
spring.datasource.hikari.master.password=123

spring.datasource.hikari.slave.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.hikari.slave.jdbc-url=jdbc:mysql://94.191.112.258 :3306/demo?serverTimezone=Asia/Shanghai
spring.datasource.hikari.slave.username=root
spring.datasource.hikari.slave.password=123

• 通过实现AbstractRoutingDataSource接口实现数据源的选择

/**
 * 多数据源读写分离
 */
public class DynamicDataSource extends AbstractRoutingDataSource {

    @Override
    protected Object determineCurrentLookupKey() {
        return DynamicDataSourceHolder.getDBType();
    }
}

/**
 * 数据源选择
 */
@Slf4j
public class DynamicDataSourceHolder {

    private static Logger logger = LoggerFactory.getLogger(DynamicDataSourceHolder.class);

    private static ThreadLocal<String> contextHolder = new ThreadLocal<String>();

    public static final String DB_MASTER = "master";

    public static final String DB_SLAVE = "slave";


    public static String getDBType() {
        String db = contextHolder.get();
        if (db == null){
            db = DB_MASTER;
        }
        return db;
    }

    public static void setDBType(String str) {
        logger.info("所使用的数据源为：" + str);
        contextHolder.set(str);
    }

    public static void clear() {
        logger.info("清空线程绑定的数据源!");
        contextHolder.remove();
    }


}

• 通过AOP拦截Service方法或者Interceptor拦截SQL的执行器Executor，选择对应的数据源

/**
 * 利用mybatis的拦截器 拦截SQL语句 注入数据源
 */
@Slf4j
@Intercepts({@Signature(type = Executor.class, method = "update", args = {MappedStatement.class, Object.class}),
        @Signature(type = Executor.class, method = "query", args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class})})
public class DynamicDataSourceInterceptor implements Interceptor {

    private Logger logger = LoggerFactory.getLogger(DynamicDataSourceInterceptor.class);

    //匹配以insert delete update+空格开头
    private static final String REGEX = ".*insert\\u0020.*|.*delete\\u0020.*|.*update\\u0020.*";

    //拦截方法
    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        //判断是否是事务的
        boolean synchronizationActive = TransactionSynchronizationManager.isActualTransactionActive();
        //获取参数
        String DB = DynamicDataSourceHolder.DB_MASTER;
        Object[] args = invocation.getArgs();
        MappedStatement ms = (MappedStatement) args[0];
        if (!synchronizationActive) {
            //非事务
            if (ms.getSqlCommandType().equals(SqlCommandType.SELECT)) {
                //selectKey 为自增id查询主键（Select LAST_INSERT_ID）方法，使用主库
                if (ms.getId().contains(SelectKeyGenerator.SELECT_KEY_SUFFIX)) {
                    DB = DynamicDataSourceHolder.DB_MASTER;
                } else {
                    BoundSql boundSql = ms.getSqlSource().getBoundSql(args[1]);
                    //将换行 table 替换为空格
                    String sql = boundSql.getSql().toLowerCase(Locale.CHINA).replaceAll("[\\t\\n\\r]", " ");
                    if (sql.matches(REGEX)) {
                        //正则匹配增删改
                        DB = DynamicDataSourceHolder.DB_MASTER;
                    } else {
                        DB = DynamicDataSourceHolder.DB_SLAVE;
                    }
                }
            }
        } else {
            //事务
            DB = DynamicDataSourceHolder.DB_MASTER;
        }
        DynamicDataSourceHolder.setDBType(DB);
        logger.info("设置方法[{}] use [{}] Strategy,SqlCommanType[{}]..", ms.getId(),
                DB, ms.getSqlCommandType().name());
        return invocation.proceed();
    }

    //返回拦截对象/代理对象
    @Override
    public Object plugin(Object target) {
        //如果target是Exector  mybatis的SQL执行器
        if (target instanceof Executor) {
            //将其交给intercept处理
            return Plugin.wrap(target, this);
        } else {
            //否则直接返回
            return target;
        }
    }

    //可以配置一些属性
    @Override
    public void setProperties(Properties properties) {

    }
}

• 设置MyBatis-Plus配置，注入多数据源，将多数据源注入SqlSessionFactory和事务管理器，并加入拦截器

/**
 * MyBatis配置
 */
@Configuration
@MapperScan("com.example.demo.mapper")
public class MyBatisConfig {


    //主库
    @Bean(name = "masterDataSource")
    @ConfigurationProperties("spring.datasource.hikari.master")
    public DataSource masterDataSource() {
        return DataSourceBuilder.create().build();
    }


    //从库
    @Bean(name = "slaveDataSource")
    @ConfigurationProperties("spring.datasource.hikari.slave")
    public DataSource slaveDataSource() {
        return DataSourceBuilder.create().build();
    }

    //动态数据源
    @Primary
    @Bean(name = "dynamicDataSource")
    public DynamicDataSource dynamicDataSource(@Qualifier("masterDataSource") DataSource master,
                                               @Qualifier("slaveDataSource") DataSource slave) {
        Map<Object, Object> targetDataSource = new HashMap<>();
        targetDataSource.put(DynamicDataSourceHolder.DB_MASTER, master);
        targetDataSource.put(DynamicDataSourceHolder.DB_SLAVE, slave);
        DynamicDataSource dataSource = new DynamicDataSource();
        dataSource.setTargetDataSources(targetDataSource);
        dataSource.setDefaultTargetDataSource(master);
        return dataSource;
    }

    //拦截器
    @Bean
    public DynamicDataSourceInterceptor dynamicDataSourceInterceptor(){
        return new DynamicDataSourceInterceptor();
    }

    //分页插件
    @Bean
    public PaginationInterceptor paginationInterceptor() {
        PaginationInterceptor paginationInterceptor = new PaginationInterceptor();
        // paginationInterceptor.setLimit(你的最大单页限制数量，默认 500 条，小于 0 如 -1 不受限制);
        return paginationInterceptor;
    }

    //创建SqlSessionFactory
    @Bean
    public SqlSessionFactory sqlSessionFactory() throws Exception {
        //mybatis-plus的SqlSessionFactoryBean
        MybatisSqlSessionFactoryBean sqlSessionFactoryBean = new MybatisSqlSessionFactoryBean();

        //懒加载
        LazyConnectionDataSourceProxy lazyConnectionDataSourceProxy=new LazyConnectionDataSourceProxy();
        lazyConnectionDataSourceProxy.setTargetDataSource(dynamicDataSource(masterDataSource(),slaveDataSource()));
        sqlSessionFactoryBean.setDataSource(lazyConnectionDataSourceProxy);

        //需要mapper文件时加入扫描，sqlSessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:/mapper/*/*Mapper.xml"));
        MybatisConfiguration configuration = new MybatisConfiguration();
        configuration.setJdbcTypeForNull(JdbcType.NULL);
        configuration.setMapUnderscoreToCamelCase(true);
        configuration.setUseGeneratedKeys(true);
        configuration.setCacheEnabled(false);
        sqlSessionFactoryBean.setConfiguration(configuration);

        //加入上面的两个拦截器
        Interceptor interceptor[]={paginationInterceptor(),dynamicDataSourceInterceptor()};
        sqlSessionFactoryBean.setPlugins(interceptor);

        return sqlSessionFactoryBean.getObject();
    }

    //配置事务管理器
    @Bean
    public PlatformTransactionManager platformTransactionManager(){
        return new DataSourceTransactionManager(dynamicDataSource(masterDataSource(),slaveDataSource()));
    }


}

5.测试