MySQL主从读写分离及多数据源配置

        最近在做分布式相关的项目，在微服务架构下，大部分性能瓶颈集中在数据库IO上，因此准备在数据库上做点优化。最基础最简单提高数据库性能的方式就是做多主从/主备，再配置多数据源的读写分离，下面就挨个介绍。

1 主从MySQL配置

主机：Linux虚拟机 Cent OS 7

从机：物理机 Windows 10

为避免不必要的麻烦，主从机均使用MySQL 5.7.40，也推荐使用同一版本。

1.1 主库配置

        首先配置主机MySQL，需要在配置文件my.cnf中加入如下配置。

[mysqld]

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

# 开启binlog
log-bin = mysql-bin

# 开启慢查询日志
slow_query_log = 1
# 慢查询时间阈值 3s
long_query_time = 3

# 主从配置
server-id = 1
# 无需同步的库
# binlog-ignore-db = mysql
# 需要同步的库
binlog_do_db = sg_blog
# 主库可读可写
read-only = 0

        有几个需要注意的点，逐一强调一下：

1. 主从复制是基于binlog实现的，因此必须要打开主服务器的binlog，即设置“bin-log = name”，名字可以自己取。且启动binlog就必须设置server-id，否则会导致MySQL无法正常启动，server-id不能与其他服务器重复。
2. binlog有3种记录模式，STATEMENT/ROW/MIXED，简单说STATEMENT是记录实际执行的SQL语句，ROW记录变更的记录（如一次性变更10行则记录10行），MIXED是取两者优点的折中方式。STATEMENT在使用不同索引时会导致不同结果，所以尽管ROW模式占用更大的空间，还是推荐使用ROW。
3. 需要指定从库同步的数据库时，可以使用“binlog_do_db”并配置数据库名称。如要同步多个库，需要配置多次该属性（不能写在一行，如“a, b, c”这种形式为错误的）。
4. 需要指定从库不需要同步的数据库时，可以使用“binlog_ignore_db”，使用方法与“binlog_do_db”相同。要注意的是，这两对属性是互斥的，配置其中一个即可。

        配置文件填写完毕后重启服务，还需要给从库创建一个专用权限账户用于同步数据，可设置成仅从库IP可登录。

#创建用户
CREATE USER 'slaveuser'@'192.168.190.129' IDENTIFIED BY '123456';

#赋予权限
GRANT REPLICATION SLAVE ON *.* TO 'slaveuser'@'192.168.190.129';

#刷新权限
flush privileges;

        这步成功执行后，主库这边就只差一步了，需要定位一下binlog当前写入的位置，来确定从库从哪里开始同步。

SHOW MASTER STATUS;

+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 88       | sg_blog      |                  |
+------------------+----------+--------------+------------------+

        记录一下当前位置Position就可以去配置从库啦。

1.2 从库配置

        从库需要修改的配置较少，注意id不重复，log-bin名称保持和主库相同即可。

# 从库配置
server_id = 2
log-bin = mysql-bin

# 需要同步的库
replicate-do-db = sg_blog

       值得注意的是“replicate-do-db”属性，也是指定需要同步的数据库，效力等同于主库的“binlog_do_db”。重启服务后进入MySQL命令行，配置连接主数据库信息并启动。

# 配置主数据库信息
change master to
master_host='192.168.190.129',
master_user='slaveuser',
master_password='******',
master_log_file='mysql-bin.000001',
master_log_pos=88;

# 开始同步
start slave;

         如果不出意外，应该是已经开始同步了，查看一下同步状态。

# 查看同步状态
mysql> show slave status\G

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.190.129
                  Master_User: slaveuser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000004
          Read_Master_Log_Pos: 154
               Relay_Log_File: Shelter-relay-bin.000008
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000004
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

         内容很多，主要需要关注的是“Slave_IO_Running”和“Slave_SQL_Running”，分别表示binlog拉取状态和同步SQL语句运行状态，两个都是Yes才代表正常运行。

1.3 出现的问题

        问题主要集中在完成主从配置后、建立连接时，下面根据我遇到的情况记录一下问题和解决方案。

Q1：Slave_IO_Running为No

A1：说明从服务器对主服务器的连接未成功，首先可以先从网络层面排查，主从互相ping一下看能不能ping通；如无法完成通信，就说明是网络问题。如果问题并不在于网络，就需要分情况分析了，问题一般都存在于连接主库配置的信息上，可见上方“change master to”那一段；要不是用户名密码写的有问题，要不是主从同步账户权限有问题，可以去主库排查一下，我的问题是同步账户没有给“REPLICATION SLAVE”权限。

Q2：Slave_SQL_Running为No

A2：说明同步SQL语句执行失败，一般是由于从库要同步的数据库发生了数据变更，在同步期间最好是不要修改数据，待同步成功后再进行修改。我的问题出在边建表边同步数据，等建表和插入语句执行完后，再重新启动同步即可。

       至此，两台主从复制的数据库就准备好了，下面去SpringBoot中配置双数据源和读写分离。

2 SpringBoot多数据源读写分离

2.1 配置多数据源和连接池

        多数据源配置依赖于苞米豆的dynamic-datasource-spring-boot-starter，为了进一步优化，本次也引入了Druid连接池。

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid-spring-boot-starter</artifactId>
</dependency>

<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>dynamic-datasource-spring-boot-starter</artifactId>
</dependency>

        由于都使用了自动配置依赖，他们的使用也十分简单，在配置文件中配置好数据源信息和连接池信息即可。

spring:
  datasource:
    #多数据源配置
    dynamic:
      primary: master #默认数据源
      strict: false #严格匹配数据源,默认false. true未匹配到指定数据源时抛异常,false使用默认数据源
      datasource:
        master:
          url: jdbc:mysql://192.168.190.129:3306/sg_blog?characterEncoding=utf-8&serverTimezone=Asia/Shanghai
          username: root
          password: '******'
          driver-class-name: com.mysql.cj.jdbc.Driver

        slave:
          url: jdbc:mysql://127.0.0.1:3306/sg_blog?characterEncoding=utf-8&serverTimezone=Asia/Shanghai
          username: root
          password: '******'
          driver-class-name: com.mysql.cj.jdbc.Driver

      # 连接池配置：
      druid:
        initial-size: 2 # 初始化时建立物理连接的个数。默认0
        max-active: 10 # 最大连接池数量，默认8
        min-idle: 2 # 最小连接池数量
        max-wait: 2000 # 获取连接时最大等待时间，单位毫秒。
        pool-prepared-statements: false # 是否缓存preparedStatement，也就是PSCache。PSCache对支持游标的数据库性能提升巨大，比如说oracle。在mysql下建议关闭。
        max-pool-prepared-statement-per-connection-size: -1 # 要启用PSCache，必须配置大于0，当大于0时，poolPreparedStatements自动触发修改为true。在Druid中，不会存在Oracle下PSCache占用内存过多的问题，可以把这个数值配置大一些，比如说100

        设置master/slave 只写/只读后，就可以用@DS注解在代码中指定数据源了。在这之后的试验时间中遇到了几个问题，下面总结一下。

2.2 出现的问题

Q1：引入Druid后项目无法正常启动，提示找不到数据源

A1：这个问题是由于Druid的DruidDataSourceWrapper，源码可以看到他继承了InitializingBean，因此在初始化Bean时会调用afterPropertiesSet()方法。

public void afterPropertiesSet() throws Exception {
    if (super.getUsername() == null) {
        super.setUsername(this.basicProperties.determineUsername());
    }

    ......
}

        代码意为获取DataSourceProperties实体类中的属性，该实体类上加了“@ConfigurationProperties(prefix = "spring.datasource")”。我们的数据源信息现在是放在dynamic下的，没找到属性就报错了。

        要解决这个问题，需要排除Druid的自动配置，在启动类上exclude掉“DruidDataSourceAutoConfigure”即可。

Q2：Slave库仍可写入数据

A2：由于数据是单向同步，仅能从主同步到从，所以一般从库都不支持写入操作；因此要在从库配置中加入“read_only = 1”，设置为只读。但我在设置后仍然可对从库写入数据，后来排查了一下发现我连接从库用的是root账户，只读属性对他是无效的。解决方案有俩，建个普通权限账户供服务连接；或者使用“super_read_only = 1”，顾名思义，超级用户也只能读不让写，这是种更极端的方式。我选第二种，方便，也能学个新玩意。

Q3：跨数据库事务失效

A3：单个方法中涉及跨库操作时，@Transactional注解会失效，需要使用框架提供的@DSTransactional支持跨库事务。