主从复制

摘要：本文学习了数据库的主从复制。

1 简介

1.1 背景

一般应用对数据库而言都是读多写少，想要提升数据库的并发能力：

• 首先考虑的是如何优化SQL和索引，这种方式简单有效。
• 其次采用缓存的策略，比如将热点数据保存在类似Redis的内存数据库中，提升读取的效率。
• 最后采用数据库集群的方案，配置主从架构，进行读写分离，这样同样可以提升数据库的并发处理能力。

并不是所有的应用都需要对数据库配置主从架构，毕竟设置架构本身是有成本的。

1.2 作用

主从复制主要有三种作用：

• 读写分离：将数据库的读写操作分配到不同的服务器上，提高数据库的整体性能和可用性。
• 数据备份：创建数据库数据的副本，以便在某些情况下能够恢复数据。
• 高可用性：确保数据库系统能够持续运行，当主服务器故障时切换到从服务器提供服务，尽可能减少停机时间。

2 原理

主库称为Master服务器，从库称为Slave服务器。每个Slave只有一个Master，每个Master可以有多个Slave。

主从复制是基于二进制日志和中继日志实现的，涉及三个线程操作，一个主库线程，两个从库线程。

过程：

• 当主库上的数据发生改变时，会将改变写入二进制日志中，这个过程称为二进制日志事件。
• 当从库建立和主库的连接时，从库会请求与主库进行复制。
• 主库会创建二进制日志转储线程，读取二进制日志并对日志加锁，当读取结束后释放锁，然后将二进制日志内容发送给从库。
• 从库会创建IO线程，用于接收主库发送的二进制日志内容，然后存储到中继日志。
• 从库会创建SQL线程，读取中继日志的内容，在数据库中执行操作，完成数据复制。
• 在此后二进制日志转储线程监听二进制日志，当发生改变时会通知从库开启新一轮主从复制。

如图：

3 步骤

3.1 修改主库配置文件

解压安装包到主库目录，创建配置文件。

使用管理员身份执行cmd命令，创建主库服务并指定配置文件：

1

mysqld --install MySQL-Master --defaults-file="D:\Work\MySQL\mysql-master\my.ini"

初始化主库：

1

mysqld --initialize-insecure

启动主库，登录主库修改密码。

配置主库二进制日志：

1
2
3
4
5
6
7
8
9

[mysqld]
# 集群唯一ID
server-id=1
# 是否只读，0表示读写，1表示只读
read-only=0
# 设置二进制日志的位置，会在名称后附加数字扩展名
log_bin=mysql-bin
# 设置二进制日志索引文件的位置，会在名称后附加.index扩展名
log_bin_index=mysql-bin

重启主库服务。

3.2 修改从库配置文件

解压安装包到从库目录，创建配置文件。

使用管理员身份执行cmd命令，创建从库服务并指定配置文件：

1

mysqld --install MySQL-Slave --defaults-file="D:\Work\MySQL\mysql-slave\my.ini"

初始化从库：

1

mysqld --initialize-insecure

启动从库，登录从库修改密码。

配置从库二进制日志：

1
2
3
4
5
6
7
8

# 集群唯一ID
server-id=2
# 是否只读，0表示读写，1表示只读
read-only=1
# 设置二进制日志的位置，会在名称后附加数字扩展名
log_bin=mysql-bin
# 设置二进制日志索引文件的位置，会在名称后附加.index扩展名
log_bin_index=mysql-bin

配置从库中继日志：

1
2
3
4
5
6
7
8

# 设置中继日志的位置
relay_log=relay-bin
# 设置中继日志索引文件的位置，会在名称后附加.index扩展名
relay_log_index=relay-bin
# 设置主库二进制日志进度信息，file表示存储在文件，table表示存储在mysql.slave_master_info表，支持同时设置两种
master_info_repository=table
# 设置从库中继日志进度信息，file表示存储在文件，table表示存储在mysql.slave_relay_log_info表，支持同时设置两种
relay_log_info_repository=table

重启从库服务。

3.3 配置主库

从库需要访问主库，所以还要在主库创建用于主从复制的用户，并对用户进行授权。

创建用户：

1

create user 'doSlave' identified by '123456';

访问授权：

1

grant replication slave on *.* to 'doSlave' identified by '123456';

刷新授权：

1

flush privileges;

查看主库状态：

1
2
3
4
5
6
7
8
9

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 |      840 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql>

记录下File和Position的值。

3.4 配置从库

配置主库：

1
2
3
4
5
6
7

change master to
master_host=主库地址,
master_port=主库端口号,
master_user=主库授权用户,
master_password=主库授权用户密码,
master_log_file=主库二进制日志,
master_log_pos=主库二进制日志的Pos点;

示例：

1
2
3
4
5
6
7
8
9
10

mysql> change master to
    -> master_host='localhost',
    -> master_port=3306,
    -> master_user='doSlave',
    -> master_password='123456',
    -> master_log_file='mysql-bin.000002',
    -> master_log_pos=840;
Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql>

3.5 执行复制

登录从库。

停止复制：

1

stop slave;

相当于：

1
2

stop slave io_thread;
stop slave sql_thread;

执行复制：

1

start slave;

相当于：

1
2

start slave io_thread;
start slave sql_thread;

查看状态：

1

show slave status\G

说明：

• Slave_IO_Running：从库IO线程是否正常运行，Yes表示正常运行。
• Slave_SQL_Running：从库SQL线程是否正常运行，Yes表示正常运行。
• Seconds_Behind_Master：从库同步的延迟时间，从库当前的时间戳与从库正在执行的事件时间戳的差值，单位是秒。
• SQL_Delay：从库延迟复制的时间，单位是秒。

3.6 测试

在主库执行SQL语句，在从库查看是否进行了复制。

4 复制方式

主从复制是通过日志实现的，因为从库执行中继日志是在主库执行SQL命令之后，所以即使在网络正常的情况下，从库执行中继日志的时间也会导致延迟，进而产生数据不一致的问题。

数据不一致问题可以通过更换复制方式解决。

4.1 异步复制

主库执行SQL命令，记录二进制日志，向客户端返回执行结果，从库在间隔一段时间探测到二进制日志有改动后进行复制。

异步复制是默认的复制方式，提供了最佳性能，但没有考虑数据不一致问题。

4.2 同步复制

主库执行SQL命令，记录二进制日志，通知从库复制数据，在收到所有从库复制成功的消息后，向客户端返回执行结果。

同步复制解决了数据不一致问题，但牺牲了很大的性能。

4.3 半同步复制

主库执行SQL命令，记录二进制日志，通知从库复制数据，在收到任意一个从库写入中继日志成功的消息后，向客户端返回执行结果。

半同步复制是在异步复制和同步复制折中的一个方案，也是最佳性能和最佳安全的折中。

在5.5版本之后才支持半同步复制，需要安装插件才能开启。

4.4 增强半同步复制（无损复制）

主库执行SQL命令但并不提交事务，记录二进制日志，通知从库复制数据，在收到任意一个从库写入中继日志成功的消息后，提交事务并向客户端返回执行结果。

增强半同步复制是对半同步复制的优化，在主库执行SQL命令后并不提交事务，等到任意一个从库写入中继日志后在提交事务，进一步提高了安全性，但因为没有及时提交事务，也会影响一些性能。

在5.7版本之后取代了半同步复制。