mysql数据库一主多备部署架构 数据库一主两备

文章目录

• 一、配置mysql数据库一主两从多数据源

• 1.1 什么是读写分离？
• 1.2 数据库读写分离解决什么问题？
• 1.3 读写分离的方案
• 1.4 引入依赖
• 1.5 yml数据源配置
• 1.6 动态配置
• 1.7 dynamic-datasource-select
• 1.8 datasource-holder
• 1.9 Interceptor
• 1.10 WeightedRound - RobinScheduling
• 1.11 mybatis-config
• 1.12 测试结果
• 1.13 安利一款插件

• 二、配置数据库主从同步

• 2.1 主从同步复制的方式
• 2.2 准备3台安装了mysql 5.7的centos 7 - linux服务器
• 2.3 配置master主服务器

• 2.3.1 配置mysql配置文件my.cnf
• 2.3.2 创建用于从服务器的用户和权限
• 2.3.3 重启mysql服务
• 2.3.4 查看主服务器状态

• 2.4 配置slave从服务器（另一台类似）

• 2.4.1 配置mysql配置文件my.cnf
• 2.4.2 重启mysql服务
• 2.4.3 连接master主服务器
• 2.4.4 启动slave数据同步
• 2.4.5 查看slave信息

• 2.5 测试

• 三、总结

一、配置mysql数据库一主两从多数据源

系统环境：
mysql 5.7
oracle jdk 11
linux：centos 7

框架生态：
springboot 2.3.0 + mybatis 3.3.2

1.1 什么是读写分离？

读写分离其实就是将数据库分为了主从库，一个主库用于写数据，多个从库完成读数据的操作，主从库之间通过某种机制进行数据的同步，是一种常见的数据库架构。

一个组从同步集群，通常被称为是一个“分组”。

1.2 数据库读写分离解决什么问题？

大多数互联网业务，往往读多写少，这时候，数据库的读会首先称为数据库的瓶颈，这时，如果我们希望能够线性的提升数据库的读性能，消除读写锁冲突从而提升数据库的写性能，那么就可以使用“分组架构”（读写分离架构）。

用一句话概括，读写分离是用来解决数据库的读性能瓶颈的。

在互联网的应用场景中，常常数据量大、并发量高、高可用要求高、一致性要求高，如果使用“读写分离”，就需要注意这些问题：

• 数据库连接池要进行区分，哪些是读连接池，哪个是写连接池，研发的难度会增加；
• 为了保证高可用，读连接池要能够实现故障自动转移；
• 主从的一致性问题需要考虑。

可以看到要考究的还是有很多的，所以相比于做缓存的复杂度，读写分离很多时候是作为缓存的一种补充方案。

1.3 读写分离的方案

读写分离有2种主流实现方案：

其一是构造一个Interceptor，在SqlSessionFactory生成statement前，拦截prestatement预编译的sql请求，根据 add / update / delete / select 不同类型的sql语句动态调用不同的数据源来实现；

其二是在业务层（service）构造一个aop切面，对不同的 方法 / 类 添加不同的aop注解来拦截请求，改变sql调用时的数据源；

本文采用了第一种思路。

最后在文末安利一款非常好用的插件，同时集成了2种方案的功能，可以快速上手。

1.4 引入依赖

<parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.3.0.RELEASE</version>
    </parent>
    <dependencies>
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.3.2</version>
        </dependency>
    </dependencies>

1.5 yml数据源配置

spring:
  datasource:
    druid:
      master:
        driverClassName: com.mysql.jdbc.Driver
        jdbc-url: jdbc:mysql://192.168.122.130:3306/qa?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf-8&useSSL=false
        username: root
        password: 123456
        type: com.alibaba.druid.pool.DruidDataSource
      slave1:
        driverClassName: com.mysql.jdbc.Driver
        jdbc-url: jdbc:mysql://192.168.122.140:3306/qa?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf-8&useSSL=false
        username: root
        password: 123456
        type: com.alibaba.druid.pool.DruidDataSource
      slave2:
        driverClassName: com.mysql.jdbc.Driver
        jdbc-url: jdbc:mysql://192.168.122.141:3306/qa?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf-8&useSSL=false
        username: root
        password: 123456
        type: com.alibaba.druid.pool.DruidDataSource
    # 连接池设置
      initial-size: 5
      min-idle: 5
      max-active: 20
      # 配置获取连接等待超时的时间
      max-wait: 60000
      # 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
      time-between-eviction-runs-millis: 60000
      # 配置一个连接在池中最小生存的时间，单位是毫秒
      min-evictable-idle-time-millis: 300000
      # Oracle请使用select 1 from dual
      validation-query: SELECT 'x'
      test-while-idle: true
      test-on-borrow: false
      test-on-return: false
      # 打开PSCache，并且指定每个连接上PSCache的大小
      pool-prepared-statements: true
      max-pool-prepared-statement-per-connection-size: 20
      # 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙
      filters: stat,wall,slf4j
      # 通过connectProperties属性来打开mergeSql功能；慢SQL记录
      connection-properties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
      # 合并多个DruidDataSource的监控数据
      use-global-data-source-stat: true

1.6 动态配置

注意事项：

• 数据库连接地址需要重用jdbc-url （这个属性才能重写，url不行）
• 数据库驱动用驼峰命名法：driverClassName（有些文档用driver-class-name，本文环境下亲测不行）

1.7 dynamic-datasource-select

该类继承了springframework的AbstractRoutingDataSource类，用来从ThreadLocal中动态选择数据源。

public class DynamicDataSource extends AbstractRoutingDataSource {

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

1.8 datasource-holder

这个类用来存放相应的datasource

@Slf4j
public class DynamicDataSourceHolder {

    /**
     * 创建一个本地线程
     */
    private static ThreadLocal<String> contextHolder = new ThreadLocal<>();
    public static final String DB_MASTER = "master";
    public static final String DB_SLAVE_1 = "slave_1";
    public static final String DB_SLAVE_2 = "slave_2";

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

    public static void setDBType(String str) {
        log.info("数据源为" + str);
        contextHolder.set(str);
    }

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

}

1.9 Interceptor

拦截器：指定拦截哪些方法，拦截策略中update包括增删改，query等于查询

@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 static final String REGEX = ".*insert\\u0020.*|.*delete\\u0020.*|.*update\\u0020.*";

    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        boolean synchronizationActive = TransactionSynchronizationManager.isActualTransactionActive();
        String lookupKey = DynamicDataSourceHolder.DB_MASTER;
        if (!synchronizationActive) {
            Object[] objects = invocation.getArgs();
            MappedStatement ms = (MappedStatement) objects[0];
            if (ms.getSqlCommandType().equals(SqlCommandType.SELECT)) {
                //如果selectKey为自增id查询主键,使用主库
                if (ms.getId().contains(SelectKeyGenerator.SELECT_KEY_SUFFIX)) {
                    lookupKey = DynamicDataSourceHolder.DB_MASTER;
                } else {
                    BoundSql boundSql = ms.getSqlSource().getBoundSql(objects[1]);
                    String sql = boundSql.getSql().toLowerCase(Locale.CHINA).replaceAll("[\\t\\n\\r]", " ");
                    if (sql.matches(REGEX)) {
                        lookupKey = DynamicDataSourceHolder.DB_MASTER;
                    } else {
                        //添加轮询算法
                        WeightedRoundRobinScheduling obj = new WeightedRoundRobinScheduling();
                        obj.init();
                        lookupKey = obj.getServer().getSlaveId();
                    }
                }
            }
        } else {
            lookupKey = DynamicDataSourceHolder.DB_MASTER;
        }
        DynamicDataSourceHolder.setDBType(lookupKey);
        return invocation.proceed();
    }

    @Override
    public Object plugin(Object target) {
        //增删改查的拦截, 然后交由intercept处理
        if (target instanceof Executor) {
            return Plugin.wrap(target, this);
        } else {
            return target;
        }
    }

    @Override
    public void setProperties(Properties properties) {

    }
}

1.10 WeightedRound - RobinScheduling

权重轮询调度算法

public class WeightedRoundRobinScheduling {

    /**
     * 上一次选择的服务器
     */
    private int currentIndex = -1;

    /**
     * 当前调度的权值
     */
    private int currentWeight = 0;

    /**
     * 最大权重
     */
    private int maxWeight = 0;

    /**
     * 所有服务器权重的最大公约数
     */
    private int gcdWeight = 0;

    /**
     * 服务器数量
     */
    private int serverCount = 2;

    /**
     * 服务器集合
     */
    private List<Server> serverList;

    /**
     * 返回最大公约数
     *
     * @param a
     * @param b
     * @return
     */
    private static int gcd(int a, int b) {
        BigInteger b1 = new BigInteger(String.valueOf(a));
        BigInteger b2 = new BigInteger(String.valueOf(b));
        BigInteger gcd = b1.gcd(b2);
        return gcd.intValue();
    }

    /**
     * 返回所有服务器权重的最大公约数
     *
     * @param serverList
     * @return
     */
    private static int getGCDForServers(List<Server> serverList) {
        int w = 0;
        for (int i = 0, len = serverList.size(); i < len - 1; i++) {
            if (w == 0) {
                w = gcd(serverList.get(i).weight, serverList.get(i + 1).weight);
            } else {
                w = gcd(w, serverList.get(i + 1).weight);
            }
        }
        return w;
    }

    /**
     * 返回所有服务器中的最大权重
     *
     * @param serverList
     * @return
     */
    public static int getMaxWeightForServers(List<Server> serverList) {
        int w = 0;
        for (int i = 0, len = serverList.size(); i < len - 1; i++) {
            if (w == 0) {
                w = Math.max(serverList.get(i).weight, serverList.get(i + 1).weight);
            } else {
                w = Math.max(w, serverList.get(i + 1).weight);
            }
        }
        return w;
    }

    /**
     * 算法流程：
     * 假设有一组服务器 S = {S0, S1, …, Sn-1}
     * 有相应的权重，变量currentIndex表示上次选择的服务器
     * 权值currentWeight初始化为0，currentIndex初始化为-1 ，当第一次的时候返回 权值取最大的那个服务器，
     * 通过权重的不断递减 寻找 适合的服务器返回，直到轮询结束，权值返回为0
     * @return
     */
    public Server getServer() {
        while (true) {
            currentIndex = (currentIndex + 1) % serverCount;
            if (currentIndex == 0) {
                currentWeight = currentWeight - gcdWeight;
                if (currentWeight <= 0) {
                    currentWeight = maxWeight;
                    if (currentWeight == 0) {
                        return null;
                    }
                }
            }
            if (serverList.get(currentIndex).weight >= currentWeight) {
                return serverList.get(currentIndex);
            }
        }
    }

    /**
     * 服务器对象
     */
    class Server {
        public String slaveId;
        /**
         * 轮询权重
         */
        public int weight;

        public Server(String slaveId, int weight) {
            this.slaveId = slaveId;
            this.weight = weight;
        }

        public String getSlaveId() {
            return slaveId;
        }

        public void setSlaveId(String ip) {
            this.slaveId = slaveId;
        }

        public int getWeight() {
            return weight;
        }

        public void setWeight(int weight) {
            this.weight = weight;
        }
    }

    public void init() {
        Server s1 = new Server(DynamicDataSourceHolder.DB_SLAVE_1, 1);
        Server s2 = new Server(DynamicDataSourceHolder.DB_SLAVE_1, 2);
        serverList = new ArrayList<>();
        serverList.add(s1);
        serverList.add(s2);

        currentIndex = -1;
        currentWeight = 0;
        serverCount = serverList.size();
        maxWeight = getMaxWeightForServers(serverList);
        gcdWeight = getGCDForServers(serverList);
    }
}

1.11 mybatis-config

通过自定义的master和slave1,2数据源构建动态数据源，并用这个动态数据源去创建SqlSessionFactory，并加载拦截器到SqlSessionFactory。

@Configuration
@EnableTransactionManagement
@MapperScan("com.cry.qa.dao")
public class MybatisPlusConfig {

    /**
     * 分页插件
     */
    @Bean
    public PaginationInterceptor paginationInterceptor() {
        // 开启count的join优化,只针对left join
        return new PaginationInterceptor().setCountSqlParser(new JsqlParserCountOptimize(true));
    }

    /**
     * 两个分页插件都配置,不会冲突
     * pageHelper的分页插件
     */
    @Bean
    public PageInterceptor pageInterceptor() {
        return new PageInterceptor();
    }

    /**
     * 配置数据源
     *
     * @return
     */
    @Bean(name = "master")
    @ConfigurationProperties(prefix = "spring.datasource.druid.master")
    public DataSource master() {
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "slave1")
    @ConfigurationProperties(prefix = "spring.datasource.druid.slave1")
    public DataSource slave1() {
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "slave2")
    @ConfigurationProperties(prefix = "spring.datasource.druid.slave2")
    public DataSource slave2() {
        return DataSourceBuilder.create().build();
    }


    @Primary
    @Bean(name = "dynamicDataSource")
    public DynamicDataSource dataSource(@Qualifier("master") DataSource master,
                                        @Qualifier("slave1") DataSource slave1,
                                        @Qualifier("slave2") DataSource slave2) {
        Map<Object, Object> targetDataSource = new HashMap<>();
        targetDataSource.put(DynamicDataSourceHolder.DB_MASTER, master);
        targetDataSource.put(DynamicDataSourceHolder.DB_SLAVE_1, slave1);
        targetDataSource.put(DynamicDataSourceHolder.DB_SLAVE_2, slave2);
        DynamicDataSource dataSource = new DynamicDataSource();
        dataSource.setTargetDataSources(targetDataSource);
        return dataSource;
    }

    @Bean
    public DynamicDataSourceInterceptor dynamicDataSourceInterceptor() {
        return new DynamicDataSourceInterceptor();
    }

    /**
     * 根据数据源创建SqlSessionFactory
     */
    @Bean(name = "SqlSessionFactory")
    public SqlSessionFactory dataSourceSqlSessionFactory() throws Exception {

        //配置mybatis,对应mybatis-config.xml
        MybatisSqlSessionFactoryBean sqlSessionFactory = new MybatisSqlSessionFactoryBean();
        //懒加载
        LazyConnectionDataSourceProxy p = new LazyConnectionDataSourceProxy();
        p.setTargetDataSource(dataSource(master(), slave1(), slave2()));
        sqlSessionFactory.setDataSource(p);
        //需要mapper文件时加入扫描，sqlSessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:/mapper/*/*Mapper.xml"));
        sqlSessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:/mybatis/*.xml"));
        MybatisConfiguration configuration = new MybatisConfiguration();
        configuration.setJdbcTypeForNull(JdbcType.NULL);
        configuration.setMapUnderscoreToCamelCase(true);
        configuration.setUseGeneratedKeys(true);
        configuration.setCacheEnabled(false);
        sqlSessionFactory.setConfiguration(configuration);
        //加入上面的两个拦截器
        Interceptor interceptor[] = {paginationInterceptor(), dynamicDataSourceInterceptor()};
        sqlSessionFactory.setPlugins(interceptor);
        return sqlSessionFactory.getObject();
    }

    /**
     * 配置事务管理器
     */
    @Bean
    public DataSourceTransactionManager transactionManager(DynamicDataSource dataSource) throws Exception {
        return new DataSourceTransactionManager(dataSource);
    }
}

1.12 测试结果

postman发送请求：

主库结果：

从库结果：

无新增数据

1.13 安利一款插件

dynamic-datasource-spring-boot-starter

和mybatis非常契合，可以透明地实现多数据源 / 主从分离

二、配置数据库主从同步

模型解析：

• 在mysql-master的数据库事件（例如修改数据库的sql操作语句），都会存储到日志系统A中，在相应的端口（默认3306）通过网络发送给Mysql-B。Mysql-B收到后，写入本地日志系统B，然后一条条的将数据库事件在数据库Mysql-B中完成。
• bin log，是MYSQL的日志类型中的二进制日志，也就是专门用来保存修改数据库表的所有动作，MYSQL会在执行语句之后，释放锁之前，写入二进制日志，确保事务安全。
• relay log，称为中继日志，不是二进制日志，由于它是从mysql-master的二进制日志复制过来的，并不是自己的数据库变化产生的，类似接力。

2.1 主从同步复制的方式

通过上面的机制，可以保证mysql-master和mysql-slave的数据库数据一致，但是时间上肯定有延迟，即mysql-slave的数据是滞后的。因此，会出现这样的问题，mysql-master的数据库操作是可以并发的执行的，但是mysql-slave只能从relay log中一条一条的读取执行。若mysql-master写操作很频繁，mysql-slave很可能就跟不上了。

由此诞生了几种主从同步的方式：

• 1.同步复制，master的变化，必须等待slave-1,slave-2,…,slave-n完成后才能返回。
• 2.异步复制，master只需要完成自己的数据库操作即可，至于slaves是否收到二进制日志，是否完成操作，不用关心。MYSQL的默认设置。
• 3.半同步复制，master只保证slaves中的一个操作成功，就返回，其他slave不管。这个功能，是由google为MYSQL引入的。

本文采取的是第二种方式。

2.2 准备3台安装了mysql 5.7的centos 7 - linux服务器

本文中的三服务器的IP地址分别是：

• 主服务器（192.168.122.130）
• 从服务器1（192.168.122.140）
• 从服务器2（192.168.122.141）

2.3 配置master主服务器

2.3.1 配置mysql配置文件my.cnf

编辑my.cnf文件
[root@localhost mysql]# vim /etc/my.cnf
 
#在[mysqld]下添加：
server-id=1
log_bin=master-bin
log_bin_index=master-bin.index
binlog_do_db=test
#备注：
#server-id 服务器唯一标识。
#log_bin 启动MySQL二进制日志，即数据同步语句，从数据库会一条一条的执行这些语句。
#binlog_do_db 指定记录二进制日志的数据库，即需要复制的数据库名，如果复制多个数据库，重复设置这个选项即可。
#binlog_ignore_db 指定不记录二进制日志的数据库，即不需要复制的数据库名，如果有多个数据库，重复设置这个选项即可。
#其中需要注意的是，binlog_do_db和binlog_ignore_db为互斥选项，一般只需要一个即可。

2.3.2 创建用于从服务器的用户和权限

#进入mysql数据库
[root@localhost mysql]# mysql -uroot -p
Enter password: 123456
 
#创建从数据库的masterbackup用户和权限
mysql> grant replication slave on *.* to masterbackup@'192.168.122.%' identified by '123456';
#备注
#192.168.122.%通配符，表示0-255的IP都可访问主服务器，正式环境请配置指定从服务器IP
#若将 192.168.122.% 改为 %，则任何ip均可作为其从数据库来访问主服务器
#masterbackup为用于登录主服务器的用户账号
 
#退出mysql
mysql> exit;

2.3.3 重启mysql服务

[root@localhost mysql]# systemctl restart mysqld.service

2.3.4 查看主服务器状态

#进入mysql数据库
[root@localhost mysql]# mysql -uroot -p
Enter password: 123456
 
#查看主服务器状态
mysql> show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000004 |     20288| qa           |                  |                   |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

显示如上一行状态就代表配置成功了。

2.4 配置slave从服务器（另一台类似）

2.4.1 配置mysql配置文件my.cnf

#编辑my.cnf文件
[root@localhost mysql]# vim /etc/my.cnf
 
#在[mysqld]中添加：
server-id=2
relay-log=slave-relay-bin
relay-log-index=slave-relay-bin.index
#replicate-do-db=test
#备注：
#server-id 服务器唯一标识，如果有多个从服务器，每个服务器的server-id不能重复，跟IP一样是唯一标识，如果你没设置server-id或者设置为0，则从服务器不会连接到主服务器。
#relay-log 启动MySQL二进制日志，可以用来做数据备份和崩溃恢复，或主服务器挂掉了，将此从服务器作为其他从服务器的主服务器。
#replicate-do-db 指定同步的数据库，如果复制多个数据库，重复设置这个选项即可。若在master端不指定binlog-do-db，则在slave端可用replication-do-db来过滤。
#replicate-ignore-db 不需要同步的数据库，如果有多个数据库，重复设置这个选项即可。
#其中需要注意的是，replicate-do-db和replicate-ignore-db为互斥选项，一般只需要一个即可。

2.4.2 重启mysql服务

[root@localhost mysql]# systemctl restart mysqld.service

2.4.3 连接master主服务器

#进入mysql数据库
[root@localhost mysql]# mysql -uroot -p
Enter password: 123456 
 
#连接master主服务器
mysql> change master to master_host='192.168.122.130',master_port=3306,master_user='masterbackup',master_password='123456',master_log_file='master-bin.000004',master_log_pos=20288;
#备注：
#master_host对应主服务器的IP地址。
#master_port对应主服务器的端口。
#master_log_file对应show master status显示的File列：master-bin.000001。
#master_log_pos对应show master status显示的Position列：20288。
#master_user对应登录主服务器的用户账号

2.4.4 启动slave数据同步

#启动slave数据同步
mysql> start slave;
#停止slave数据同步（若有需要）
mysql> stop slave;

2.4.5 查看slave信息

mysql> show slave status\G;

Slave_IO_Running和Slave_SQL_Running都为yes，则表示同步成功。

注意：

• 这里由于我的从服务器是从主服务器里复制而来的，Slave_IO_Running显示为NO，查看异常日志：[ERROR] Slave I/O for channel ‘’: Fatal error: The slave I /O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work. Error_code: 1593
• 定位问题是由mysql的uuid相同，而导致触发此异常，修改从数据库的uuid
• find / -name “auto.cnf” 查出mysql的自动配置文件，修改其中的uuid

2.5 测试

重新发送add请求

master：

slave1：

slave2：

同步成功。

三、总结

至此，mysql数据库的主从同步就完成了和读写分离就完成了，结合1和2两部分就可以实现一个简单的主从读写分离并保证其主从一致性的方案。