灵境 / 博客 / Mysql 常用的一些主从热备方案

Mysql 常用的一些主从热备方案

2023 年 4 月 25 日 22:38

本篇文章主要介绍 Mysql 常用的一些主从热备方案，内容主要包括方案实现原理与方案实施操作。

基础知识

在介绍各方案前有必要将个关键知识点做一个简单的阐述，包括什么是主从复制，基于什么原理实现的以及为什么我们需要主从复制。

mysql 主从复制

MySQL 主从复制是指数据可以从一个 MySQL 数据库服务器主节点复制到一个或多个从节点。MySQL 默认采用异步复制方式，这样从节点不用一直访问主服务器来更新自己的数据，数据的更新可以在远程连接上进行，从节点可以复制主数据库中的所有数据库或者特定的数据库，或者特定的表

原理

  1. master 节点将数据的改变记录到 binlog 日志，当 master 上的数据发生改变时，则将其改变写入 binlog 日志中；
  2. slave 节点会在一定时间间隔内对 master 的 binlog 日志进行探测，判断其是否发生改变，如果发生改变，则开始一个 I/O Thread 请求 master 二进制事件；
  3. 同时 master 节点为每个 I/O 线程启动一个 dump 线程，用于向其发送二进制事件，并保存至从节点本地的 relaylog 中继日志中，从节点将启动 SQL 线程从 relaylog 中继日志中读取二进制日志，在本地重放，使得其数据和主节点的保持一致，最后 I/O 线程和 SQL 线程将进入睡眠状态，等待下一次被唤醒。

说明：

  • 从库会生成两个线程，一个 I/O 线程，一个 SQL 线程；
  • I/O 线程会去请求主库的 binlog, 并将得到的 binlog 写到本地的 relay-log（中继日志） 文件中；
  • 主库会生成一个 log dump 线程，用来给从库 I/O 线程传 binlog;
  • SQL 线程，会读取 relay log 文件中的日志，并解析成 sql 语句逐一执行；

执行的具体步骤

  1. 从库通过手工执行 change master to 语句连接主库，提供了连接的用户一切条件（user 、password、port、ip），并且让从库知道，二进制日志的起点位置（file 名 position 号）； start slave
  2. 从库的 IO 线程和主库的 dump 线程建立连接。
  3. 从库根据 change master to 语句提供的 file 名和 position 号，IO 线程向主库发起 binlog 的请求。
  4. 主库 dump 线程根据从库的请求，将本地 binlog 以 events 的方式发给从库 IO 线程。
  5. 从库 IO 线程接收 binlog events，并存放到本地 relay-log 中，传送过来的信息，会记录到 master.info 中
  6. 从库 SQL 线程应用 relay-log，并且把应用过的记录到 relay-log.info 中，默认情况下，已经应用过的 relay 会自动被清理 purge

为什么需要主从复制

  1. 实现读写分离：在业务复杂的系统中，有这么一个情景，有一句 sql 语句需要锁表，导致暂时不能使用读的服务，那么就很影响运行中的业务，使用主从复制，让主库负责写，从库负责读，这样，即使主库出现了锁表的情景，通过读从库也可以保证业务的正常运作。
  2. 实现数据热备，满足数据可靠性需求
  3. 完成架构扩展，提高性能：业务量越来越大，I/O 访问频率过高，单机无法满足，此时做多库的存储，降低磁盘 I/O 访问的频率，提高单个机器的 I/O 性能。

常用的部署方案

下面介绍一些使用频率较高的部署形式及其部署实施操作：

一主一从部署

使用场景

  1. 需要数据热备的场景
  2. 需要数据读写分离的场景

架构图

配置方法

假设 master 节点的 IP 为 166.11.166.2，slave 节点的 IP 为 166.11.166.3，Mysql 服务端口均为 3306。实现一主一从的配置流程如下：

按如下说明编辑各实例的配置文件/etc/my.cnf，开启两个节点上 Mysql 实例的 binlog 功能，并配置 server id，每个 Mysql 实例的 server id 必须不同；

关闭服务器防火墙或开放 Mysql 对应的端口，使主从节点 Mysql 实例可以联通

在 master 节点上的 Mysql 实例创建用于同步的用户，注意修改对应 IP 和数据库密码；

查看 master 节点上 Mysql 实例的 binlog 的 file 名、position 号，注意修改对应 IP 和数据库密码；

按照上一步获取的信息，配置 slave 节点上的 Mysql 实例为 master 节点上 Mysql 实例的从节点，在 slave 节点上的 Mysql 实例执行如下命令，注意修改 IP、用户、文件名和 position 号信息；

启动主从复制，在 slave 节点上的 Mysql 实例执行如下命令；

在 slave 节点上的 Mysql 实例执行如下命令，检查配置是否成功，主从复制是否正常。

注意：Slave_IO_Running 和 Slave_SQL_Running 的状态都为 Yes 时，说明从库配置成功

一主多从部署

使用场景

  1. 需要数据热备的场景
  2. 分库架构的读写分离场景，一个库一个从，提高系统的读性能

架构图

配置方法

实现一主多从的配置流程同一主一从，只是将主从关系配置重复在多个从实例上执行，注意配置的server-id需要不同

双主互备（主主复制）部署

使用场景

  1. 需要数据热备的场景
  2. 需要实现数据库实例冗余加强可靠性的场景（比如借助 keepalived 实现数据库宕机切换）
  3. 分库架构的读写分离场景，一个库一个从

架构图

配置方法

假设 master1 节点的 IP 为 166.11.166.2，master2 节点的 IP 为 166.11.166.3，Mysql 服务端口均为 3306。实现一主一从的配置流程如下：

【各节点均执行】按如下说明编辑各实例的配置文件/etc/my.cnf，开启两个节点上 Mysql 实例的 binlog 功能，并配置 server id，每个 Mysql 实例的 server id 必须不同；

【各节点均执行】分别在两个节点执行如下命令，关闭服务器防火墙或开放 Mysql 对应的端口，使两个节点 Mysql 实例可以联通

【master1节点均执行】在 master1 节点上的 Mysql 实例创建用于同步的用户，注意修改对应 IP 和数据库密码；

【master1节点均执行】查看 master1 节点上 Mysql 实例的 binlog 的 file 名、position 号，注意修改对应 IP 和数据库密码；

【master2节点均执行】按照上一步获取的信息，配置 master2 节点上的 Mysql 实例为 master1 节点上 Mysql 实例的从节点，在 master2 节点上的 Mysql 实例执行如下命令，注意修改 IP、用户、文件名和 position 号信息；

【master2节点均执行】启动主从复制，在 master2 节点上的 Mysql 实例执行如下命令；

【master2节点均执行】在 master2 节点上的 Mysql 实例创建用于同步的用户，注意修改对应 IP 和数据库密码；

【master2节点均执行】查看 master2 节点上 Mysql 实例的 binlog 的 file 名、position 号，注意修改对应 IP 和数据库密码；

【master1节点均执行】按照上一步获取的信息，配置 master1 节点上的 Mysql 实例为 master2 节点上 Mysql 实例的从节点，在 master1 节点上的 Mysql 实例执行如下命令，注意修改 IP、用户、文件名和 position 号信息；

【各节点均执行】分别在两个节点上的 Mysql 实例执行如下命令，检查配置是否成功，主从复制是否正常。

注意：Slave_IO_Running 和 Slave_SQL_Running 的状态都为 Yes 时，说明从库配置成功

多主一从部署

使用场景

  1. 数据汇总，可将多个主数据库同步汇总到一个从数据库中，方便数据统计分析
  2. 读写分离，从库只用于查询，提高数据库整体性能。

架构图

联级复制部署

级联复制模式下，部分 slave 的数据同步不连接主节点，而是连接从节点。因为如果主节点有太多的从节点，就会损耗一部分性能用于 replication，那么我们可以让 3~5 个从节点连接主节点，其它从节点作为二级或者三级与从节点连接，这样不仅可以缓解主节点的压力，并且对数据一致性没有负面影响。

架构图

参考文章

mysql -hlocalhost -uroot -p${MYSQL_PASSWORD} -e "show slave status\G;"

mysql -hlocalhost -uroot -p${MYSQL_PASSWORD} -e "change master to master_host='166.11.166.3',master_user='repl',master_password='repl123!',master_log_file='mysql-bin.000001',master_log_pos=154;"

mysql -hlocalhost -uroot -p${MYSQL_PASSWORD} -e "show master status;"
#+------------------+----------+--------------+------------------+-------------------+
#| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
#+------------------+----------+--------------+------------------+-------------------+
#| mysql-bin.000001 |      154 |              |                  |                   |
#+------------------+----------+--------------+------------------+-------------------+
#1 row in set (0.04 sec)
# 上述输出中 Position 对应的 154 为 position 号，File 对应的 mysql-bin.000001 为 binlog 文件名，将用于下一步的配置

mysql -hlocalhost -uroot -p${MYSQL_PASSWORD} -e "create user 'repl'@'166.11.166.2' identified by 'repl123!';"
mysql -hlocalhost -uroot -p${MYSQL_PASSWORD} -e "grant replication slave on *.* to 'repl'@'166.11.166.2';"
mysql -hlocalhost -uroot -p${MYSQL_PASSWORD} -e "flush privileges;"

mysql -hlocalhost -uroot -p${MYSQL_PASSWORD} -e "start slave;"

mysql -hlocalhost -uroot -p${MYSQL_PASSWORD} -e "change master to master_host='166.11.166.2',master_user='repl',master_password='repl123!',master_log_file='mysql-bin.000001',master_log_pos=154;"

mysql -hlocalhost -uroot -p${MYSQL_PASSWORD} -e "show master status;"
#+------------------+----------+--------------+------------------+-------------------+
#| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
#+------------------+----------+--------------+------------------+-------------------+
#| mysql-bin.000001 |      154 |              |                  |                   |
#+------------------+----------+--------------+------------------+-------------------+
#1 row in set (0.04 sec)
# 上述输出中 Position 对应的 154 为 position 号，File 对应的 mysql-bin.000001 为 binlog 文件名，将用于下一步的配置

mysql -hlocalhost -uroot -p${MYSQL_PASSWORD} -e "create user 'repl'@'166.11.166.3' identified by 'repl123!';"
mysql -hlocalhost -uroot -p${MYSQL_PASSWORD} -e "grant replication slave on *.* to 'repl'@'166.11.166.3';"
mysql -hlocalhost -uroot -p${MYSQL_PASSWORD} -e "flush privileges;"

# 以 CentOS7 为例的开放 3306 端口的命令
firewall-cmd --zone=public --add-port=3306/tcp --permanent
firewall-cmd --reload

# 添加如下配置，其中 server-id 各实例必须不同
log-bin=mysql-bin   
server-id=1

mysql -hlocalhost -uroot -p${MYSQL_PASSWORD} -e "show slave status\G;"

mysql -hlocalhost -uroot -p${MYSQL_PASSWORD} -e "start slave;"

mysql -hlocalhost -uroot -p${MYSQL_PASSWORD} -e "change master to master_host='166.11.166.2',master_user='repl',master_password='repl123!',master_log_file='mysql-bin.000001',master_log_pos=154;"

mysql -hlocalhost -uroot -p${MYSQL_PASSWORD} -e "show master status;"
#+------------------+----------+--------------+------------------+-------------------+
#| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
#+------------------+----------+--------------+------------------+-------------------+
#| mysql-bin.000001 |      154 |              |                  |                   |
#+------------------+----------+--------------+------------------+-------------------+
#1 row in set (0.04 sec)
# 上述输出中 Position 对应的 154 为 position 号，File 对应的 mysql-bin.000001 为 binlog 文件名，将用于下一步的配置

mysql -hlocalhost -uroot -p${MYSQL_PASSWORD} -e "create user 'repl'@'166.11.166.3' identified by 'repl123!';"
mysql -hlocalhost -uroot -p${MYSQL_PASSWORD} -e "grant replication slave on *.* to 'repl'@'166.11.166.3';"
mysql -hlocalhost -uroot -p${MYSQL_PASSWORD} -e "flush privileges;"

# 以 CentOS7 为例的开放 3306 端口的命令
firewall-cmd --zone=public --add-port=3306/tcp --permanent
firewall-cmd --reload

# 添加如下配置，其中 server-id 各实例必须不同
log-bin=mysql-bin   
server-id=1