mysql主从模式原理与配置

主从模式原理

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

1.2 主要用途：

1.在开发工作中，有时候会遇见某个sql 语句需要锁表，导致暂时不能使用读的服务，这样就会影响现有业务，使用主从复制，让主库负责写，从库负责读，这样，即使主库出现了锁表的情景，通过读从库也可以保证业务的正常运作。
2.数据实时备份，当系统中某个节点发生故障时，可以方便的故障切换(主从切换)
3.高可用（HA）
4.随着系统中业务访问量的增大，如果是单机部署数据库，就会导致I/O访问频率过高。有了主从复制，增加多个数据存储节点，将负载分布在多个从节点上，降低单机磁盘I/O访问的频率，提高单个机器的I/O性能。

1.3 MySQL主从形式

1.一主多从（实现HA，读写分离，提供集群的并发能力）
2.多主一从（从库主要用于数据库备份作用）
3.双主复制

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

1.4 MySQL主从复制的原理（重点）

MySQL主从复制涉及到三个线程，一个运行在主节点（log dump thread），其余两个（I/O thread, SQL thread）运行在从节点，如下图所示：

1. 主节点log dump线程

当从节点连接主节点时，主节点会为其创建一个log dump 线程，用于发送和读取bin-log的内容。在读取bin-log中的操作时，log dump线程会对主节点上的bin-log加锁，当读取完成，在发送给从节点之前，锁会被释放。主节点会为自己的每一个从节点创建一个log dump 线程。

2.从节点I/O线程

当从节点上执行start slave命令之后，从节点会创建一个I/O线程用来连接主节点，请求主库中更新的bin-log。I/O线程接收到主节点的blog dump进程发来的更新之后，保存在本地relay-log（中继日志）中。

3.从节点SQL线程

SQL线程负责读取relay-log中的内容，解析成具体的操作并执行，最终保证主从数据的一致性。

对于每一个主从连接，都需要这三个进程来完成。当主节点有多个从节点时，主节点会为每一个当前连接的从节点建一个log dump 进程，而每个从节点都有自己的I/O进程，SQL进程。从节点用两个线程将从主库拉取更新和执行分成独立的任务，这样在执行同步数据任务的时候，不会降低读操作的性能。比如，如果从节点没有运行，此时I/O进程可以很快从主节点获取更新，尽管SQL进程还没有执行。如果在SQL进程执行之前从节点服务停止，至少I/O进程已经从主节点拉取到了最新的变更并且保存在本地relay日志中，当服务再次起来之后，就可以完成数据的同步。

要实施复制，首先必须打开Master 端的binary log（bin-log）功能，否则无法实现。

因为整个复制过程实际上就是Slave 从Master 端获取该日志然后再在自己身上完全顺序的执行日志中所记录的各种操作。如下图所示：

1.5 MySQL主从复制的模式（理解）

MySQL 主从复制默认是异步的模式。MySQL增删改操作会全部记录在bin-log（binary log）中，当slave节点连接master时，会主动从master处获取最新的bin-log文件。并把bin-log存储到本地的relay-log中，然后去执行relay-log的更新内容。
主要有三种模式：

1. 异步模式

异步模式如下图所示，这种模式下，主节点不会主动推送bin-log到从节点，主库在执行完客户端提交的事务后会立即将结果返给给客户端，并不关心从库是否已经接收并处理，这样就会有一个问题，主节点如果崩溃掉了，此时主节点上已经提交的事务可能并没有传到从节点上，如果此时，强行将从提升为主，可能导致新主节点上的数据不完整。

2.半同步模式(mysql semi-sync)

介于异步复制和全同步复制之间，主库在执行完客户端提交的事务后不是立刻返回给客户端，而是等待至少一个从库接收到并写到relay-log中才返回成功信息给客户端（只能保证主库的bin-log至少传输到了一个从节点上，但并不能保证从节点将此事务执行更新到db中），否则需要等待直到超时时间然后切换成异步模式再提交。相对于异步复制，半同步复制提高了数据的安全性，一定程度的保证了数据能成功备份到从库，同时它也造成了一定程度的延迟，但是比全同步模式延迟要低，这个延迟最少是一个TCP/IP往返的时间。所以，半同步复制最好在低延时的网络中使用。如下图所示：

3.全同步模式

指当主库执行完一个事务，然后所有的从库都复制了该事务并成功执行完才返回成功信息给客户端。因为需要等待所有从库执行完该事务才能返回成功信息，所以全同步复制的性能必然会收到严重的影响。

1.6 GTID复制模式

在传统的复制里面，当发生故障，需要主从切换，需要找到bin-log和pos点（指从库更新到了主库bin-log的哪个位置，这个位置之前都已经更显完毕，这个位置之后未更新），然后将主节点指向新的主节点，相对来说比较麻烦，也容易出错。在MySQL 5.6里面，不用再找bin-log和pos点，我们只需要知道主节点的ip，端口，以及账号密码就行，因为复制是自动的，MySQL会通过内部机制GTID自动找点同步。
基于GTID的复制是MySQL 5.6后新增的复制方式.
GTID (global transaction identifier) 即全局事务ID, 保证了在每个在主库上提交的事务在集群中有一个唯一的ID.

1.7 MySQL主从复制的方式(重点)

MySQL主从复制有三种方式：基于SQL语句的复制(statement-based replication, SBR) , 基于行的复制(row-based replication, RBR) , 混合模式复制(mixed-based replication, MBR)。对应的bin-log文件的格式也有三种：STATEMENT, ROW , MIXED

Statement-base Replication (SBR)

就是记录sql语句在bin-log中，MySQL 5.1.4及之前的版本都是使用这种复制格式。优点就是只需要记录会修改数据的sql语句到bin-log中，减少了bin-log日质量，节约I/O，提高性能。缺点是在某些情况下，会导致主从节点中数据不一致(比如sleep(),now()或自定义函数存储过程没同步，导致执行失败等)

Row-based Relication(RBR)

mysql master将SQL语句分解为基于Row更改的语句并记录在bin-log中，也就是只记录哪条数据被修改了，修改成什么样。优点是不会出现某些特定情况下的存储过程、或者函数、或者trigger的调用或者触发无法被正确复制的问题。缺点是会产生大量的日志，尤其是修改table的时候会让日志暴增,同时增加bin-log同步时间。也不能通过bin-log解析获取执行过的sql语句，只能看到发生的data变更。

Mixed-format Replication(MBR)

MySQL NDB cluster 7.3和7.4使用的MBR。是以上两种模式的混合，对于一般的复制使用STATEMENT模式保存到bin-log，对于STATEMENT模式无法复制的操作则使用ROW模式来保存，MySQL会根据执行的SQL语句选择日志保存方式。

配置主从模式

2.1 docker下mysql创建

docker run -d name mysql_master -p  3306:3306 -e MYSQL_ROOT_PASSWORD=123456 --network bridge --ip 172.17.0.2 mysql:5.7
docker run -d name mysql_slave -p  3307:3306 -e MYSQL_ROOT_PASSWORD=123456 --network bridge --ip 172.17.0.3 mysql:5.7
这里如果第一步ip没有指定，docker容器每次启动ip会自动变化，可以另外添加另一个网络环境
docker network create --subnet 172.18.0.0/16 my-net
docker network connect --ip 172.18.0.2 my-net mysql_master
docker network connect --ip 172.18.0.3 my-net mysql_slave

如果两个mysql不是新创建的或者数据不一致，注意先用数据库同步工具先同步好两者数据结构、数据一致，再执行主从配置；

2.2 创建账号和开启binlog

// 进入master容器
docker exec -it mysql_master /bin/bash
// 登录mysql
mysl -u root -p
// 创建账号
GRANT REPLICATION SLAVE ON *.* to 'slave'@'172.18.0.%' identified by '123456';
flush privileges;

修改mysqld配置，注意docker 5.7下配置路径为：/etc/mysql/mysql.conf.d/mysqld.cnf

[mysqld]# 开启binlog
log-bin=mysql-bin
server-id=1
binlog_format=mixed
# 需要同步的数据库，如果不配置则同步全部数据库
binlog-do-db=db_test
# binlog日志保留的天数，清除超过10天的日志
# 防止日志文件过大，导致磁盘空间不足
expire-logs-days=10
# 事务提交过程中的数据同步模式控制
innodb_flush_log_at_trx_commit=1

配置完成后，重启mysql

docker restart mysql_master

重新进入mysql中，查看当前binlog文件状态(后面slave配置会用到File、Position参数)

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

2.3 Slave配置binlog