MySQL 数据库 5 种主从复制高可用架构搭建,复制原理,常见复制故障解决方案

联动优势高级数据库运维工程师,长期从事DB2,Oracle,MySQL数据库运维工作。 欢迎大家关注我的头条号:月牙讲数据库运维

文章正文

简介

现在 MySQL 在互联网领域非常流行,甚至传统的金融,证券,银行也在将核心数据库从 DB2、Oracle 逐渐转换成 MySQL 数据库,从 DB-Engines 排行榜上,可以看到在 2020 年 10 月 MySQL 已经跃居到第二。DB-Engines 排名是按人气排名数据库管理系统,涵盖 340 多个系统。 排名标准包括搜索系统名称时搜索引擎结果的数量、Google 趋势、 Stack Overflow 网站、LinkedIn、Twitter 等社交网络中的提及的情况,综合比较、排名。该排名每月更新一次,此排行榜非常具有权威性。

在这里插入图片描述

在这里插入图片描述

对于开发人员、DBA,掌握 MySQL 数据库,都是非常必要和有价值的技能。下面就一起来开启 MySQL 数据库主从复制的学习之旅吧。

MySQL 数据库复制模式

在 MySQL 数据库没有出世之前,基本都是 Oracle、DB2 这两种数据库天下,相信这两种数据库大家都不会陌生,对这两个数据库做灾备时,Oracle 使用 DG,DB2 使用 HADR,但是其复制技术不够灵活,只能单向复制;而且早期的版本,从库还不支持读操作,主从切换也不方便。

直到后来 MySQL 诞生,其主从复制非常方便、灵活,支持异步、同步、双向复制等等,关键是开源,一些大公司可以非常方便地开发出复制工具,这些工具后来还演变成复制中心,是做同城双活、异地多活必不可少的组件。要想学好 MySQL 复制,需要先了解一下 MySQL 几种复制模式。

MySQL 官方提供三种复制模式,分别为异步复制模式、全同步模式、半同步模式,下面就分别介绍着 3 种模式。

1. 异步复制

MySQL 数据库官方提供的默认复制方式是异步模式,异步复制原理也很简单,当 master 节点接执行完应用的提交的事务之后,会给 客户端返回执行的响应,同时异步的将二进制日志通过 dump 线程发送给 salve 节点,在这种模式下,数据的一致性是无法得到保证的, 所以一般应用于可以接受一定延迟的业务,例如报表查询、历史订单查询等等。

2. 全同步

全同步复制估计很多朋友们没听说过,因为用的实在太少了,大家用的最多的是异步复制和半同步复制。在这里简单给大家介绍一下什么是全同步复制。其原理是当 master 节点执行完一个事务,master 节点必须等所有的 slave 节点都执行了该事务,才会给客户端返回事务执行成功的响应,正式因为这种复制模式,使其能使用的场景非常少,因为只要一个 slave 出现性能问题,就会导致整个复制集群的 TPS 性能下降。

3. 半同步复制

半同步复制其安全性和性能,是介于异步复制和全同步复制的,其原理是当客户端在 master 节点提交事务之后,master 节点需要将 bin log 发送 slave 节点,并写到中继日志之后,master 节点才会真正提交事务,这样能保证主从复制数据实时一致。在这里因为牺牲了数据库的性能,才换来的数据一致性,所以性能要比异步复制差。

MySQL 数据库主从复制原理

MySQL 数据复制是基于二进制日志(binlog)实现的,MySQL 提供了三种模式:

  • 基于 SQL 语句的复制(statement-based replication,SBR)
  • 基于行的复制(row-based replication,RBR)
  • 混合模式复制(mixed-based replication,MBR)

在 5.6 之后版本新增的 binlog_row_image= minimal 参数可以让 binlog 的 Update 只记录影响后的行,一定程度也优化了 binlog 文件的大小,当主库有多个 slave 从库时,会在一定程度减少 IO 和网络资源的消耗,缓解主从复制延迟。但是设置 minimal 之后,也会带来一些小问题,例如当在主库上 update 了不该更新的记录时,就无法通过 binlog2sql 工具生成回滚 SQL 语句了,因为此模式下,binlog 日志里只有变更后的值,变更前的记录值没有。

MySQL 的复制任务主要是由以下三个线程协同完成的。

1. master 的 Binlog dump 线程

master 节点的 dump 线程,其主要作用是负责把新增的 binlog,分发给 slave,在这里需要注意的是,如果有 2 个 slave 节点,则 master 会启动 2 个 binlog dump 线程分别处理复制任务,所以在这里特别需要注意,从 slave 节点个数不能太多,如果多了,会拖垮 master 点的,之前就听一个朋友说,一家游戏公司,因为读取需求比较大,创建了 10 个 slave 节点,导致 master 节点的性能出现瓶颈,后来改造成级联复制才解决这个问题。

2. slave 的 I/O 线程

在 slave 上运行的 I/O 线程,其主要作用是连接 master,并且告诉 master 数据库将新增的 binlog 记录发送过来,然后读取 master 的 binlog dump 线程发送来的数据,写入到本地的中继日志(Relay Log),持久化到磁盘。

3. slave 的 SQL 线程

slave 创建的 SQL 线程用于读取 relay log 中的记录,并将其重放。

在这里插入图片描述

主从半同步复制原理

半同步特性是插件式的,由 Google 开发并开源的。MySQL 数据库从 5.5 版本开始支持半同步的,在这里重点介绍一下 MySQL 5.7 数据库的半同步原理,在 MySQL 5.7 版本下的半同步是无损模式的,并且半同步增加了 rpl_semi_sync_master_wait_point 参数,来控制半同步模式下主库在返回给会话事务成功之前提交事务的方式。rpl_semi_sync_master_wait_point 该参数有两个值——AFTER_COMMIT 和 AFTER_SYNC,下面来看看 2 种模式。

AFTER_COMMIT

AFTER_COMMIT 是 MySQL 5.6 的默认半同步复制模式,其原理如下所示,当客户端提交事务到 master 节点之后,master 节点把事务写入到二进制之后,dump 线程会将新增的二进制内容发送给 slave 节点的 I/O 线程,salve 的 I/O 线程并将接收到的内容写入中继日志,在 dump 线程会将新增的二进制内容发送给 slave 节点的 I/O 线程的同时,主库的 InnoDB 存储引擎提交事务,并等待 salve 节点返回收到 relay log 的 ACK,当 master 节点收到 ACK 之后,就给客户端返回事务执行成功。

在这里插入图片描述

此图引用(https://www.cnblogs.com/zero-gg/p/9057092.html)

AFTER_SYNC(5.7 默认值)

AFTER_SYNC 半同步复制模式,MySQL 5.6 是不支持的,只有在 MySQL 5.7 以后的版本才正式支持,为什么提供 AFTER_SYNC 的复制模式呢,这是因为 AFTER_COMMIT 模式下,是无法保证数据的一致性,即不是无损复制,至于原因,下面会有一个章节详细介绍,在这里就不多介绍了。

其原理如下所示,当客户端提交事务到 master 节点之后,master 节点把事务写入到二进制之后,dump 线程会将新增的二进制内容发送给 slave 节点的 I/O 线程,salve 的 I/O 线程并将接收到的内容写入中继日志,并给 master 节点返回一个 ACK,当 master 节点收到 ACK 之后,InnoDB 存储引擎就会真正的提交事务,并给客户端返回事务执行成功结果。

在这里插入图片描述

(此图引用自 https://www.cnblogs.com/zero-gg/p/9057092.html

AFTER_COMMIT 半同步模式缺陷

在 MySQL 5.6 半同步模式和 MySQL 5.7 的 AFTER_COMMIT 半同步模式下,是存在缺陷的,在极端情况下,会造成主从复制数据不一致,即不是无损复制的。

在这里举个例子订单状态是未支付状态,应用执行 update 语句,将订单状态改为支付状态,并去 MySQL 数据库提交事务 commit 开始 ---> 写 binlog---> 将 binlog 同步到 slave ---> master 存储引擎提交事务,如果这个时候 master 宕机,由于 master 已经将事务提交,但是 slave 还没有收到 binlog,那么就会造成 master 主库上,订单状态已经变成已支付状态,而 slave 订单还是未支付状态,发生主从切换之后,在 slave 上查询订单会发现还是未支付状态,可能造成重复支付的情况。

写到这里,有的朋友会问:AFTER_SYNC 模式,不也会造成主从数据不一致吗?

因为在无损半同步模式下,master 节点的 dump 线程将新增的二进制内容发送给 slave 节点的 I/O 线程之后,InnoDB engine 层 commit 之前,是需要等待 slave 收到新增的二进制内容,并写入中继日志的 ACK 响应。master 节点只有在收到 ACK 之后,才会在 InnoDB engine 层提交事务,然后把事务执行成功结果返回给客户端。

那如果 master 节点在等待 slave 节点的 ACK 的过程中,突然宕机了呢?这个时候 slave 节点已经收到了 master 节点的 Binlog,并且执行成功,而主库上事务还没有真正提交,就相当于在从库上多了数据,造成主从数据不一致。

如果你能这么思考,说明你是经过深入思考的,可是聪明的 MySQL 数据库架构师也是考虑过这个问题,所以在 MySQL 实例启动恢复做了优化,下面来看看 MySQL 数据库如何解决这个主从数据不一致的问题。

MSQL 崩溃恢复过程

在使用 redo log 恢复完成以后,InnoDB 还是存在可能丢失部分数据的情况(或者造成主从数据不一致),主要是由于,InnoDB 事务在提交的时候,先写 binlog 日志后,才在引擎层提交,如果 MySQL 数据库在写完 binlog 日志,但是 InnoDB 还未提交前 crash,这样,已经写入到 binlog 部分的数据需要恢复的,否则,就会造成主从数据不一致。

其整个恢复分为三个步骤:

  1. 通过扫描 binlog 的最后一个文件,获取所有的 binlog 的 xid
  2. 通过 undo log 来构建未提交的事务列表(存在 undo 信息的事务都是未提交的)
  3. 比较从 binlog 获取的 xid 列表和 innodb 通过 undo 恢复事务的 xid,如果两边的 xid 相同,则可以提交事务,恢复数据

当事务在 binlog 里已经提交,而由于实例崩溃,在 innodb 引擎层没有提交,即 redo log 没有 commit,这个时候,MySQL 就会比较从 binlog 获取的 xid 列表和 innodb 通过 undo 恢复事务的 xid,如果两边的 xid 相同,MySQL 就会提交 redo log 中没有提交的事务,实现半同步的无损复制。

Docker 搭建主从复制

在这里给大家介绍一下,如何在 Docker 里快速搭建主从复制,下面是主从复制的架构图:

在这里插入图片描述

在宿主机上创建配置目录和数据目录:

mkdir -p /data/mysql/mysql8/conf/{3306,3307,3308}
mkdir -p /data/mysql/mysql8/data/{3306,3307,3308}

设置 MySQL 8 的配置文件,不同的实例,将配置文件的端口替换一下即可

[mysqld]
##basic settings###
server-id= 3306
port = 3306
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
skip-name-resolve
max_connections=1000
max_user_connections=800
max_allowed_packet=512M
max_connect_errors=100000
transaction_isolation = READ-COMMITTED
lower_case_table_names=1
default_time_zone =+8:00
open_files_limit=65535
log_timestamps=system
wait_timeout=900
interactive_timeout=900

##innodb setting##
innodb_buffer_pool_size = 256M
innodb_flush_method=O_DIRECT

innodb_log_file_size = 128M
innodb_log_files_in_group=4
innodb_log_buffer_size = 32M

innodb_undo_log_truncate=1
innodb_max_undo_log_size=128M

innodb_flush_neighbors=0
innodb_flush_log_at_trx_commit = 1
innodb_print_all_deadlocks = 1
innodb_online_alter_log_max_size=128M

innodb_lock_wait_timeout=10
innodb_file_per_table=ON
innodb_doublewrite=ON

##log settings##
log-error = error.log
slow_query_log = 1
slow_query_log_file = mysql_slow_query.log
long_query_time = 10

##replication settings##
gtid-mode=on
enforce-gtid-consistency=true
master-info-repository=TABLE
relay-log-info-repository=TABLE
sync-master-info=1000
relay_log_recovery = 1
relay-log= mysql-relay-bin

#binlog
log_bin= binlog
binlog_cache_size=32768
binlog_format=row
expire_logs_days=10
max_binlog_cache_size=128M
max_binlog_size=50M
sync_binlog=1

##session settings##
read_buffer_size = 8M
read_rnd_buffer_size = 1M

sort_buffer_size = 16M
tmp_table_size = 16M
max_heap_table_size=16M
join_buffer_size=8M

[client]
port = 3306

使用 Docker 搭建 3306、3307、3308 数据库服务:

[root@mysql data]# docker run --name mysql-3306 -v /data/mysql/mysql8/conf/3306:/etc/mysql/conf.d -v /data/mysql/mysql8/data/3306:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=root -p 3306:3306 -it -d mysql:8.0.21
18374a493e568d05b540e89043e8e1e539737c6f19c6363c724de18c3fe0d1fb
[root@mysql data]# docker run --name mysql-3307 -v /data/mysql/mysql8/conf/3307:/etc/mysql/conf.d -v /data/mysql/mysql8/data/3307:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=root -p 3307:3307 -it -d mysql:8.0.21
1db93798acc19728e7ba26cd52495005d0741260600c7ee6d5cc33beb734452f
[root@mysql data]# docker run --name mysql-3308 -v /data/mysql/mysql8/conf/3308:/etc/mysql/conf.d -v /data/mysql/mysql8/data/3308:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=root -p 3308:3308 -it -d mysql:8.0.21
215ceab6d99b9eb7f3ea3d137fd305d8a0b32f4120145def9273c57c30da7804
[root@mysql data]# 
[root@mysql data]# docker ps
CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS              PORTS                                         NAMES
215ceab6d99b        mysql:8.0.21        "docker-entrypoint.s 鈥 15 seconds ago      Up 12 seconds       3306/tcp, 33060/tcp, 0.0.0.0:3308->3308/tcp   mysql-3308
1db93798acc1        mysql:8.0.21        "docker-entrypoint.s 鈥 20 seconds ago      Up 15 seconds       3306/tcp, 33060/tcp, 0.0.0.0:3307->3307/tcp   mysql-3307
18374a493e56        mysql:8.0.21        "docker-entrypoint.s 鈥 18 minutes ago      Up 18 minutes       0.0.0.0:3306->3306/tcp, 33060/tcp             mysql-3306

测试创建好的 3306、3307、3308 三个数据库服务:

[root@mysql ~]# /data/mysql-8.0.21/bin/mysql -h 192.168.112.136 -uroot -proot -P 3306
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 8.0.21 MySQL Community Server - GPL

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 

[root@mysql ~]# /data/mysql-8.0.21/bin/mysql -h 192.168.112.136 -uroot -proot -P 3307
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.21 MySQL Community Server - GPL

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

[root@mysql ~]# /data/mysql-8.0.21/bin/mysql -h 192.168.112.136 -uroot -proot -P 3308
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.21 MySQL Community Server - GPL

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 

在 master 节点创建复制用户:

mysql> create user 'repl'@'%' identified with mysql_native_password by 'repl';
Query OK, 0 rows affected (0.00 sec)

mysql> grant replication slave,replication client on *.* to 'repl'@'%'; 
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

配置从服务器 slave,并启动 slave:

mysql> change master to master_host='192.168.112.136',master_port =3306,master_user='repl',master_password='repl', master_auto_position = 1;
Query OK, 0 rows affected, 1 warning (0.10 sec)

mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.112.136
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000003
          Read_Master_Log_Pos: 878
               Relay_Log_File: mysql-relay-bin.000003
                Relay_Log_Pos: 1087
        Relay_Master_Log_File: binlog.000003
             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: 878
              Relay_Log_Space: 3105549
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
   
                        
作者正在撰写中...
隐藏内容 支付可见
内容互动
写评论
加载更多
评论文章
¥2.99 购买
× 订阅 Java 精选频道
¥ 元/月
订阅即可免费阅读所有精选内容