MySQL主从复制和读写分离的原理与实战

主从复制

工作原理

MySQL主从复制是实现读写分离的基础。复制（replication）是MySQL提供的一种高可用、高性能的解决方案，一般用来建立大型应用。总体来说，复制的工作原理分为以下3个步骤：

1. 主服务器（master）把数据更改记录到二进制日志（binlog）中。
2. 从服务器（slave）把主服务器的二进制日志复制到自己的中继日志（relay log）中。
3. 从服务器重做中继日志，把更改应用到自己的数据库上，以达到数据的最终一致性。

从服务器有2个相关线程，一个是I/O线程，负责读取主服务器的二进制日志，并将其保存为中继日志；另一个是SQL线程，复制并执行中继日志。

复制不是完全实时地进行同步，而是异步实时。这中间存在主从服务器之间的执行延时，如果主服务器的压力很大，则可能导致主从服务器延时较大。

二进制日志

二进制日志（binary log，简称binlog）记录了对MySQL数据库执行的所有更改操作，通常有以下几种作用：

• 恢复（recovery）
• 复制（replication）
• 审计（audit）

格式

二进制日志有三种格式。

STATEMENT

二进制日志文件记录的是逻辑SQL语句。如果在主服务器运行rand、uuid等函数，又或者使用触发器等操作，会导致主从服务器数据不一致。InnoDB存储引擎的默认事务隔离级别是REPEATABLE READ，其实也是因为二进制日志文件格式的关系，如果使用READ COMMITTED事务隔离级别，会出现类似丢失更新的现象，从而出现主从数据库数据不一致。

ROW

二进制日志记录的不再是简单的SQL语句了，而是记录表的行更改情况。同时，对于上述提及的STATEMENT格式下出现的问题予以解决。ROW格式虽然可以为数据库的恢复和复制带来更好的可靠性，但是不能忽略的一点是，这会带来二进制文件大小的增加，有些语句下的ROW格式可能需要更大的容量。由于主从复制是采用传输二进制日志的方式实现的，因此主从复制的网络开销也有所增加。

MIXED

上述两种格式结合使用。默认使用STATEMENT格式，但是在一些情况下会使用ROW格式，可能的情况有：

• 表的存储引擎为NDB，这时对表的DML操作都会以ROW格式记录。
• 使用了UUID()、USER()、CURRENT_USER()、FOUND_ROWS()、ROW_COUNT()等不确定函数。
• 使用了INSERT DELAY语句。
• 使用了用户定义函数（UDF）。
• 使用了临时表（temporary table）。

实战

部署master

修改/etc/mysql/mysql.conf.d/mysqld.cnf（有可能不是这个，找到有[mysqld]节点的就行）， 增加如下配置：

[mysqld]
# 如果想做主从复制，server Id取值必须是1到2³²-1
# 默认是0，取值为0时，master会拒绝replicas(slave)的连接，replicas也会拒绝连接master
server-id=1
# 启用binlog
log-bin=mysql-bin
# 设置不需要复制的数据库
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=sys
# 设置需要复制的数据库
binlog-do-db=test
# 可选的值有STATEMENT、ROW、MIXED
binlog_format=MIXED
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

重启MySQL并进入MySQL命令行窗口，给从数据库创建一个用户并授权：

GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' IDENTIFIED BY '123456';
1

用户名：slave，密码：123456

查询master状态：

show master status;
1

如果配置成功，会出现一个表格，类似下面的：

否则啥也不显示。把这里的File和Position记下来，等会在从机上需要使用。

部署slave

修改my.cnf, 增加如下配置：

[mysqld]
server-id=2
# 启用中继日志
relay-log=mysql-relay
1
2
3
4

重启mysql使配置生效。进入MySQL命令行窗口，配置master信息：

CHANGE MASTER TO MASTER_HOST='192.168.7.97',
MASTER_USER='slave',
MASTER_PASSWORD='123456',
MASTER_PORT=3306, 
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=438;
1
2
3
4
5
6

开启slave：

start slave;
1

查看slave状态：

show slave status \G;
1

一切正常的话，会显示下面的内容：

注意IO线程和SQL线程要是运行状态。

验证效果

在master创建数据库test：

CREATE DATABASE test;
1

创建成功后去slave查看，同样也有数据库test。

在master的test数据库创建数据表：

CREATE TABLE `t_user` (
	`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
	`name` varchar(255) NOT NULL,
	`age` int(11) unsigned NOT NULL,
	`hobby` varchar(255) DEFAULT NULL,
	 PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
1
2
3
4
5
6
7

创建成功后去slave查看，同样也有数据表t_user。

在master上往表t_user中插入一条数据：

INSERT INTO t_user(`name`, `age`, `hobby`) VALUES ('Jerry', 30, 'photography');
1

执行成功后去slave查询，同样有这条数据。

读写分离

读写分离适用于读多写少的场景。master可读可写，slave只读。读请求走slave可以缓解master的压力。

Mycat2

Mycat是一款用Java语言编写的MySQL数据库网络协议的开源中间件，利用它可以实现MySQL的读写分离。下面是它的部署和使用步骤。

安装部署

在主从节点均创建给 Mycat 程序使用的用户并授权（user表位于系统库mysql，虽然上文配置了不复制系统库mysql的数据，但实际上我在master创建完mycat用户后，slave同步到了这条数据，不知道为什么）：

CREATE USER 'mycat'@'192.168.7.97' IDENTIFIED BY '123456';
-- MySQL 8 必须赋予的权限
GRANT XA_RECOVER_ADMIN ON *.* to 'mycat'@'192.168.7.97';
-- 视情况赋予权限
GRANT ALL PRIVILEGES ON *.* to 'mycat'@'192.168.7.97';
FLUSH PRIVILEGES;
1
2
3
4
5
6

这里我的mycat打算部署在192.168.7.97这台主机上，ip地址视自己的情况填写。

在主节点创建Mycat使用的数据库：

CREATE DATABASE IF NOT EXISTS `mycat`;
1

这个库称为 Mycat 的原型库（prototype），Mycat 在启动时，会自动在原型库下创建其运行时所需的数据表。

下载并安装Mycat：

mkdir /opt/mycat
cd /opt/mycat
# 下载安装包
wget http://dl.mycat.org.cn/2.0/install-template/mycat2-install-template-1.21.zip
# 下载 Mycat 2 所需依赖 jar
wget http://dl.mycat.org.cn/2.0/1.21-release/mycat2-1.21-release-jar-with-dependencies.jar
unzip mycat2-install-template-1.21.zip
cd mycat
# 复制 Mycat 2 所需依赖 jar 到 mycat 的 lib 文件夹
cp ../mycat2-1.21-release-jar-with-dependencies.jar lib/
# 授予 bin 目录下所有命令可执行权限
chmod +x bin/*
1
2
3
4
5
6
7
8
9
10
11
12

配置 Mycat 原型库的数据源（datasource）信息：

cd /opt/mycat/mycat/conf/datasources
vi prototypeDs.datasource.json
1
2

{
        "dbType":"mysql",
        "idleTimeout":60000,
        "initSqls":[],
        "initSqlsGetConnection":true,
        "instanceType":"READ_WRITE",
        "maxCon":1000,
        "maxConnectTimeout":3000,
        "maxRetryCount":5,
        "minCon":1,
        "name":"prototypeDs",
        // 数据库密码
        "password":"123456",
        "type":"JDBC",
        // 用自己创建的数据库
        "url":"jdbc:mysql://192.168.7.97:3306/mycat?useUnicode=true&serverTimezone=Asia/Shanghai&characterEncoding=UTF-8",
        // 用自己创建的用户
        "user":"mycat",
        "weight":0
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

添加test数据库的数据源信息：

cp prototypeDs.datasource.json master.datasource.json
cp prototypeDs.datasource.json slave.datasource.json
vi master.datasource.json
1
2
3

{
        "dbType":"mysql",
        "idleTimeout":60000,
        "initSqls":[],
        "initSqlsGetConnection":true,
        // 主节点可读可写
        "instanceType":"READ_WRITE",
        "maxCon":1000,
        "maxConnectTimeout":3000,
        "maxRetryCount":5,
        "minCon":1,
        // 数据源名称
        "name":"master",
        "password":"123456",
        "type":"JDBC",
        // 数据库连接，使用我们上文创建的test数据库
        "url":"jdbc:mysql://192.168.7.97:3306/test?useUnicode=true&serverTimezone=Asia/Shanghai&characterEncoding=UTF-8",
        "user":"mycat",
        "weight":0
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

vi slave.datasource.json
1

{
        "dbType":"mysql",
        "idleTimeout":60000,
        "initSqls":[],
        "initSqlsGetConnection":true,
        // 从节点只读
        "instanceType":"READ",
        "maxCon":1000,
        "maxConnectTimeout":3000,
        "maxRetryCount":5,
        "minCon":1,
        "name":"slave",
        "password":"123456",
        "type":"JDBC",
        "url":"jdbc:mysql://192.168.7.81:3306/test?useUnicode=true&serverTimezone=Asia/Shanghai&characterEncoding=UTF-8",
        "user":"mycat",
        "weight":0
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

配置test数据源的集群信息：

cd /opt/mycat/mycat/conf/clusters
# 注意：这里不要删除 prototype.cluster.json，否则启动 Mycat 时会报错
cp prototype.cluster.json test.cluster.json
vi test.cluster.json
1
2
3
4

{
        // 集群类型：SINGLE_NODE（单节点）、MASTER_SLAVE（普通主从）、GARELA_CLUSTER（garela cluster/PXC集群）等
        "clusterType":"MASTER_SLAVE",
        "heartbeat":{
                "heartbeatTimeout":1000,
                "maxRetry":3,
                "minSwitchTimeInterval":300,
                "slaveThreshold":0
        },
        "masters":[
                // 主节点数据源名称
                "master"
        ],
        "replicas": [
                // 从节点数据源名称
                "slave"
        ],
        "maxCon":200,
        // 集群名称。在后面配置物理库（schema）时会用到
        "name":"test",
        "readBalanceType":"BALANCE_ALL",
        // NOT_SWITCH（不进行主从切换）、SWITCH（进行主从切换）
        "switchType":"SWITCH"
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

配置物理库（schema）和 Mycat 中数据源/数据源集群的关系：

cd /opt/mycat/mycat/conf/schemas
# 创建一个新文件
vi test.schema.json
1
2
3

{
  // 物理库
  "schemaName": "test",
  // 指向集群，或者数据源
  "targetName": "test",
  // 这里可以配置数据表相关的信息，在物理表已存在或需要启动时自动创建物理表时配置此项
  "normalTables": {}
}
1
2
3
4
5
6
7
8

修改 Mycat 登录用户信息：

cd /opt/mycat/mycat/conf/users
vi root.user.json
1
2

{
        "dialect":"mysql",
        // ip 为 null，允许任意 ip 登录
        "ip":null,
        "password":"123456",
        "transactionType":"proxy",
        "username":"root"
}
1
2
3
4
5
6
7
8

修改 Mycat 服务端口等信息：

cd /opt/mycat/mycat/conf
vi server.json
1
2

{
  "loadBalance":{
    "defaultLoadBalance":"BalanceRandom",
    "loadBalances":[]
  },
  "mode":"local",
  "properties":{},
  "server":{
    "bufferPool":{

    },
    "idleTimer":{
      "initialDelay":3,
      "period":60000,
      "timeUnit":"SECONDS"
    },
    "ip":"0.0.0.0",
    "mycatId":1,
    "port":8066,
    "reactorNumber":8,
    "tempDirectory":null,
    "timeWorkerPool":{
      "corePoolSize":0,
      "keepAliveTime":1,
      "maxPendingLimit":65535,
      "maxPoolSize":2,
      "taskTimeout":5,
      "timeUnit":"MINUTES"
    },
    "workerPool":{
      "corePoolSize":1,
      "keepAliveTime":1,
      "maxPendingLimit":65535,
      "maxPoolSize":1024,
      "taskTimeout":5,
      "timeUnit":"MINUTES"
    }
  }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39

常用命令：

# 启动
./bin/mycat start
# 查看状态
./bin/mycat status
# 停止
./bin/mycat stop
# 暂停
./bin/mycat pause
# 重启
./bin/mycat restart
# 前台运行
./bin/mycat console
1
2
3
4
5
6
7
8
9
10
11
12

查看日志：

tail -f /opt/mycat/mycat/logs/wrapper.log
1

使用MySQL命令连接Mycat：

mysql -uroot -p123456 -P8066 -h192.168.7.97
1

在代码里也可以像连接MySQL一样连接Mycat。

验证读写分离

在主从节点均开启日志记录：

# 把日志输出到表；开启日志记录
SET GLOBAL log_output = 'TABLE'; SET GLOBAL general_log = 'ON';
# 清空 mysql.general_log 日志表中的记录
TRUNCATE TABLE mysql.general_log;
1
2
3
4

在 Mycat 中分别执行插入和查询语句：

INSERT INTO test.t_user(`name`, `age`, `hobby`) VALUES ('John', 25, 'soccer');
SELECT * FROM test.t_user;
SELECT * FROM test.t_user;
1
2
3

分别在主从节点执行如下语句，查询 SQL 执行历史：

SELECT event_time,
       user_host,
       thread_id,
       server_id,
       command_type,
       CAST(argument AS CHAR(500) CHARACTER SET utf8mb4) argument
FROM mysql.general_log
ORDER BY event_time DESC;
1
2
3
4
5
6
7
8

可以看到主节点上有一条 INSERT 和一条 SELECT；从节点上可能只有一条 SELECT，也有可能有一条 INSERT 和一条 SELECT。这里就有两个问题：

1. 为什么主节点会看到SELECT历史记录？
2. 为什么从节点会看到INSERT历史记录？

第1个问题很好解释，因为主节点我们配置的是可读可写。对于第2个问题，上文我们提到binlog_format，当它的值为STATEMENT或MIXED时，从节点会对逻辑SQL语句进行回放，其实就是执行一遍，然后就被记录到general_log了。

验证完毕后，在主从节点关闭日志记录：

# 把日志输出到文件（默认设置）；关闭日志记录
SET GLOBAL log_output = 'FILE'; SET GLOBAL general_log = 'OFF';
1
2