MySQL主从配置和读写分离

2022-03-30 20:52:25

假设各位都已经安装好了 MySQL 数据库，本次操作采用的 MySQL 版本是 5.7.16。正好还有一台 Mac闲着，所以干脆就用两台物理机直接操作了，没有空闲机器的可以在虚拟机操作是一样的。

以最简单的一主一从配置。

主服务器：192.168.0.101

从服务器：192.168.0.108

MySQL主从配置和读写分离

数据库层的几种模式

在系统架构中，数据库层主要由如下几种模式，分别是单点模式、主备模式、主从模式。

MySQL主从配置和读写分离

单点模式

单点模式是最简单的模式，只有一台数据库服务器，部署最简单。但是存在单点风险，一旦这台服务器挂掉，整个系统也就挂掉了。

主备模式

为了解决单点模式的风险，主备模式产生。目前，主备模式应该是各个线上服务系统的最低配置了，比如你在各个云平台购买的数据库服务一般都会开启备份功能。一旦主节点出现问题，还可以切换到备份节点，不至于整个系统瘫痪。

主备又分为一主一备、一主多备。多个备份是为了保证更高的安全性，万一主节点出现问题的时候，碰巧备份节点也出问题呢。

当主节点出现问题的时候要切换到备份节点，切换方式又分为手动切换和自动切换。手动切换具有一定的延时，当主节点出现问题时，只能等运维人员发现或者收到系统通知。

主从模式

主从配置一般都是和读写分离相结合，主服务器负责写数据，从服务器负责读数据，并保证主服务器的数据及时同步到从服务器。

主从模式又分为一主一从、一主多从和多主多从，越往后部署越复杂，同时，系统稳定性更高。主从模式可以更好的分担数据库压力，将插入更新操作和查询操作分开，提高系统整体性能。

本文的目的就是介绍一下简单的一主一从架构的配置和原理。

主从原理

主节点

1、当主节点上进行 insert、update、delete 操作时，会按照时间先后顺序写入到 binlog 中；
2、当从节点连接到主节点时，主节点会创建一个叫做 binlog dump 的线程；

3、一个主节点有多少个从节点，就会创建多少个 binlog dump 线程；

4、当主节点的 binlog 发生变化的时候，也就是进行了更改操作，binlog dump 线程就会通知从节点 (Push模式)，并将相应的 binlog 内容发送给从节点；

从节点

当开启主从同步的时候，从节点会创建两个线程用来完成数据同步的工作。

I/O线程： 此线程连接到主节点，主节点上的 binlog dump 线程会将 binlog 的内容发送给此线程。此线程接收到 binlog 内容后，再将内容写入到本地的 relay log。

SQL线程： 该线程读取 I/O 线程写入的 relay log，并且根据 relay log 的内容对从数据库做对应的操作。

MySQL主从配置和读写分离

主从配置一般都是和读写分离相结合，主服务器负责写数据，从服务器负责读数据，并保证主服务器的数据及时同步到从服务器。

主服务器配置

开启远程连接

使用命令行或者客户端工具进入 MySQL，执行命令：

GRANT REPLICATION SLAVE ON *.* to ‘root‘@‘192.168.0.108‘ identified by ‘P@ssw0rd‘;
FLUSH PRIVILEGES;

上面语句执行完成后，在从服务器中使用客户端或者命令行测试是否生效。

mysql -h 192.168.0.101 -uroot -p

MySQL主从配置和读写分离

启用 bin-log，并设置 server-id

需要在 MySQL 配置文件中修改，MySQL 配置文件默认位置在如下位置，从上下到下优先级降低：

  1. /etc/my.cnf

  2. /etc/mysql/my.cnf

  3. /usr/local/etc/my.cnf

  4. ~/.my.cnf

配置内容如下：

[mysqld]
log-bin=mysql-bin
server-id=101

其他参数

除了上面两个必要参数外，还有其他的几个参数。

binlog_format

bin-log 日志的格式，支持下面三种，推荐使用 mixed 。
statement：会将对数据库操作的sql语句写入到binlog中。
row：会将每一条数据的变化写入到binlog中。
mixed：statement 与 row 的混合。MySQL 决定什么时候写 statement 格式，什么时候写 row 格式。

binlog-do-db

配置要同步的数据库，如果不配置默认为全部数据库。

binlog-do-db=db1
binlog-do-db=db2

binlog-ignore-db

配置不需要同步的数据库。

binlog-ignore-db=db3

expire-logs-days

bin-log 日志保存天数，保存天数越久占用空间越大。

然后重启 MySQL 服务

mysql.server restart

使用下面的命令可以查看配置是否生效：

show variables like ‘log_bin‘;
show variables like ‘server_id‘;

使用下面的语句查看 master 状态。

show master status;

MySQL主从配置和读写分离

从服务器配置

1、打开从服务器的配置文件，在其中加上如下配置：

server-id=108

2、重启 MySQL 服务。

mysql.server restart

3、配置主从同步

change master to master_host=‘192.168.0.101‘,master_user=‘root‘,master_password=‘P@ssw0rd‘,master_log_file=‘mysql-bin.000001‘ ,master_log_pos=154;

其中 master_host表示主服务器 IP，master_user和master_password分别是主服务器的用户名和密码，master_log_file和master_log_pos在主服务器中通过show master status语句可以查到。

4、开启同步进程。

start slave

5、查看同步状态。

show slave status;

可能碰到的问题

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.

mysql 5.6 之后引入了uuid的概念，各个复制主从结构中的 server_uuid 要保证不一样。由于我这台新 Mac 是用系统自带的「迁移助理」从老机器迁移过来的，所以说，所有的软件和配置都是一模一样的。

通过下面的语句可查看 server_uuid 的值 和 auto.cnf 的路径，auto.cnf 中保存了 server_uuid 的值。

show variables like ‘%server_uuid%‘;
show variables like ‘%datadir%‘;

我查到的从服务器上的 datadir 目录在 /usr/local/var/mysql，修改目录下的 auto.cnf 文件中的 server-uuid 的值

修改 datadir 目录下的auto.cnf 文件中的 server-uuid 的值。

然后再重启服务，查看同步状态就不会有这个问题了。

测试一下

同步配置完成后，我在主服务器上创建一个数据库，创建一张表，然后新增、修改、删除数据，查看从服务器上是否相应的同步修改。

正常情况下，在主服务器上操作完成，从服务器也马上会看到对应的数据。

主从状态查询

主服务状态

使用如下语句可查看主服务状态：

show master status\G;

MySQL主从配置和读写分离

使用 show processlist语句可查看线程状态，可以看到第一个线程的 State 是 「Master has sent all binlog to slave; waiting for more updates」，说明同步线程一直在运行中。

mysql> show processlist\G;
*************************** 1. row ***************************
     Id: 20
   User: root
   Host: 192.168.0.108:57815
     db: NULL
Command: Binlog Dump
   Time: 11702
  State: Master has sent all binlog to slave; waiting for more updates
   Info: NULL
*************************** 2. row ***************************
     Id: 21
   User: root
   Host: localhost
     db: NULL
Command: Query
   Time: 0
  State: starting
   Info: show processlist
2 rows in set (0.03 sec)

ERROR:
No query specified

从服务状态

通过 show slave status;可查看从服务状态，从中可以看到主服务的信息以及 Slave_IO 和 Slave_SQL 线程的状态等。

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.0.101
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 1393
               Relay_Log_File: 192-relay-bin.000005
                Relay_Log_Pos: 1284
        Relay_Master_Log_File: mysql-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: 1393
              Relay_Log_Space: 1930
              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: 101
                  Master_UUID: 220919a2-9690-11e6-9c9b-9d406b577440
             Master_Info_File: /usr/local/var/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

ERROR: 
No query specified

也可以通过 show processlist 查看从服务的线程情况。

mysql> show processlist\G;

*************************** 1. row ***************************
     Id: 1
   User: system user
   Host: 
     db: NULL
Command: Connect
   Time: 84003
  State: Slave has read all relay log; waiting for more updates
   Info: NULL
*************************** 2. row ***************************
     Id: 2
   User: system user
   Host: 
     db: NULL
Command: Connect
   Time: 123734
  State: Waiting for master to send event
   Info: NULL
*************************** 3. row ***************************
     Id: 9
   User: root
   Host: localhost
     db: NULL
Command: Query
   Time: 0
  State: starting
   Info: show processlist
3 rows in set (0.00 sec)

ERROR: 
No query specified

从库挂机了怎么恢复同步

哪儿有什么岁月静好，只不过是有人帮你顶住了锅而已。服务宕掉通常都在不经意间，一旦从库服务挂掉了怎么办。

在主服务的 binlog dump 线程将指定的 binlog 信息发给从服务时，除了日志内容，还包括本次发送内容在主服务端的 bin-log 日志文件名称以及位置信息。

从服务的 I/O 线程接收到信息后将日志内容写入realy-log 文件(mysql-relay-bin.xxxxxx)的末端，并将读取到的主服务端的 bin-log 的文件名和位置记录到 master-info 中（通过 show slave status 中的 Master_Info_File 字段可以看到 master.info 保存的位置），以便下一次读取时能告诉主服务从哪里开始同步。

从服务的 SQL 线程检测到 realy-log 新增了内容后，解析日志文件生成对应的 sql 语句，并应用这些 sql 到数据库，保证主从数据一致性。

所以，及时从库挂掉了，因为有 master.info 记录了上一次同步的位置，只要同步服务再次启动，那就可以从上次同步的位置继续增量同步了。

那话说主库宕了怎么办，这就是另一个悲伤的故事了，就没有从库挂掉这么简单了，如果马上启动那就是最好的解决办法。如果由于硬件或者比较棘手的问题导致没办法立即重启，那就要选一个从库升级为主库，选择的标准是数据最接近主库的，也就是最后一次同步时间最晚的。如果有可能（比如主服务只是数据库无法启动，但机器还在）还要到主服务上拉取最新的 bin-log 进行同步。最后进行一系列设置将选中的从库变更为主库配置。（就不展开说了，因为再展开就露馅儿了，我也不会）

ShardingSphere-JDBC 实现读写分离

主从分离之后，就要做系统的读写分离了，写操作走主节点，读操作走从节点。这就需要数据库中间件来帮忙了，现在比较流行的中间件有Atlas、Cobar、Mycat、Sharding-Sphere，具体选择哪个或者自研，要看各个公司的标准了。

Sharding-Sphere 包括 ShardingSphere-JDBC 和 ShardingSphere-Proxy。

ShardingSphere-JDBC 定位为轻量级 Java 框架，在 Java 的 JDBC 层提供的额外服务。 它使用客户端直连数据库，以 jar 包形式提供服务，无需额外部署和依赖，可理解为增强版的 JDBC 驱动，完全兼容 JDBC 和各种 ORM 框架。

  • 适用于任何基于 JDBC 的 ORM 框架，如：JPA, Hibernate, Mybatis, Spring JDBC Template 或直接使用 JDBC。
  • 支持任何第三方的数据库连接池，如：DBCP, C3P0, BoneCP, Druid, HikariCP 等。
  • 支持任意实现 JDBC 规范的数据库，目前支持 MySQL，Oracle，SQLServer，PostgreSQL 以及任何遵循 SQL92 标准的数据库。

MySQL主从配置和读写分离

ShardingSphere 当前版本是 4.x，官网地址：https://shardingsphere.apache.org/index_zh.html

接下来简单演示一下 ShardingSphere-JDBC + Spring Boot + MyBatis 实现简单的读写分离。

版本说明

MySQL主从配置和读写分离

MySQL主从配置和读写分离

MySQL主从配置和读写分离

MySQL主从配置和读写分离

创建 Spring Boot 项目

1、到 Spring Initializr（https://start.spring.io/） 上创建项目结构，选择 JDK 版本为 8，Spring Boot 为2.3.2（目前最新版本），并输入项目相关的信息。

MySQL主从配置和读写分离

2、引入 Spring Boot 相关依赖包，包括 spring-boot-starter-web、mybatis-spring-boot-starter、mysql-connector-java、lombok。

MySQL主从配置和读写分离

3、最后将创建完成后的目录结构直接下载下来导入到 IDEA，然后 maven 更新依赖包即可。

4、引入 ShardingSphere-JDBC 专门针对 Spring Boot 的依赖包。

<dependency>
  <groupId>org.apache.shardingsphere</groupId>
  <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
  <version>4.1.1</version>
</dependency>

5、配置主从数据库，保证读请求到从节点，写请求到主节点，使用的连接池是 Spirng Boot 2.x 默认的 hikari。注意，如果用的是其他连接池，比如 Druid 的话，jdbcUrl 属性要改成 url 。

spring:
  shardingsphere:
    datasource:
      master:
        type: com.zaxxer.hikari.HikariDataSource
        driverClassName: com.mysql.cj.jdbc.Driver
        jdbcUrl: jdbc:mysql://localhost:3306/master-slave?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMT
        username: root
        password: password
      slave0:
        type: com.zaxxer.hikari.HikariDataSource
        driverClassName: com.mysql.cj.jdbc.Driver
        jdbcUrl: jdbc:mysql://192.168.0.108:3306/master-slave?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMT
        username: root
        password: password
      names: master,slave0


    props:
      sql.show: true
    masterslave:
      load-balance-algorithm-type: round_robin
    sharding:
      master-slave-rules:
        master:
          master-data-source-name: master
          slave-data-source-names: slave0

如果有多个从节点，可以继续在 spring.shardingsphere.datasource 下添加 slave1、slave2 等。

最后要配置上 master-slave-rules（主从规则），才能保证实现读写分离，如果不加这个配置，就会采用 round_robin 路由算法，将主从节点无差别对待。写请求可能会落到从节点，导致无法像主节点同步。

6、配置 MyBatis 相关的 mapper.xml 、dao 接口、实体等，不做过多介绍。

7、添加 Service、Controller，添加一个查询方法，对应数据库 select。添加一个新增方法，对应数据库 insert 。

@RestController
@RequestMapping("user")
public class UserController {

    @Autowired
    private IUserService userService;

    @GetMapping(value = "list")
    public Object list() {
        List<User> users = userService.list();
        return users;
    }

    @PostMapping(value = "add")
    public Object add(@RequestBody UserDto userDto) {
        User user = new User();
        BeanUtils.copyProperties(userDto, user);
        return userService.addUser(user);
    }
}

8、最后通过分别请求 list 接口和 add 接口，然后通过日志可以看到，select 会路由到从节点，insert 会路由到主节点。

MySQL主从配置和读写分离

MySQL主从配置和读写分离

源码在 github 上，有需要的同学可以到 github 上获取。

仓库地址：https://github.com/huzhicheng/play ，其中的 sharding-jdbc-write-read-split 项目是本文的示例源码。

总结

主从配置+读写分离可以很大程度上保证系统高可用性和整体性能，而且也是互联网应用的基础入门配置。MySQL 可以利用 bin-log 实现主从同步，master 节点采用推方式向 slave 节点推送写入操作形成的日志，slave 节点会将日志先写到本地的 relay-log 中，然后再写入数据库中，此过程中还会更新 master.info 文件，记录本地同步的位置，以便下次增量同步。

数据库层配置完成，最后要在应用中进行读写配置，具体的配置可以公司内部自行开发或者采用比较稳定的开源框架，本文采用的是 ShardingSphere-JDBC，只是个代理客户端，其原理就是分析 SQL 语句判断是读还是写，从而分发到不同的节点。

如果需要更复杂的操作，比如跨库 join 等，那就需要代理中间件了，比如 ShardingSphere-Proxy 等。


 

MySQL主从配置和读写分离

上一篇：svn提交报database is locked


下一篇：JDBC------之执行查询操作和使用结果集获取数据的简单方式