Mysql 主从复制相关原理简述

发表于 更新于 上层目录 阅读次数：

Mysql 主从同步基本原理

复制的基本过程如下：

  1. Slave 上面的 IO 进程连接上 Master，并请求从指定日志文件的指定位置（或者从最开始的日志）之后的日志内容；

  2. Master 接收到来自 Slave 的 IO 进程的请求后，通过负责复制的 IO 进程,根据请求信息,读取指定日志指定位置之后的日志信息，返回给 Slave 的 IO 进程。返回信息中除了日志所包含的信息之外，还包括本次返回的信息已经到 Master 端的 bin-log 文件的名称以及 bin-log 的位置；

  3. Slave 的 IO 进程接收到信息后，将接收到的日志内容依次添加到 Slave 端的 relay-log 文件的最末端，并将读取到的 Master 端的 bin-log 的文件名和位置记录到 master-info 文件中，以便在下一次读取的时候能够清楚的告诉 Master “我需要从某个 bin-log 的哪个位置开始往后的日志内容，请发给我”；

  4. Slave 的 Sql 进程检测到 relay-log 中新增加了内容后，会马上解析 relay-log 的内容，获得在 Master 端真实执行的那些可执行的内容，并在自身执行。

双主情况下，禁止同时写入，建议还是按照主从的方式工作，防止数据冲突。双主场景下，主要是切换主备方便。

Mysql 复制方式

异步复制（Asynchronous replication）

MySQL 默认的复制即是异步的，主库在执行完客户端提交的事务后会立即将结果返给给客户端，并不关心从库是否已经接收并处理，这样就会有一个问题，主如果 crash 掉了，此时主上已经提交的事务可能并没有传到从上，如果此时，强行将从提升为主，可能导致新主上的数据不完整。

全同步复制（Fully synchronous replication）

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

半同步复制（Semisynchronous replication）

介于异步复制和全同步复制之间，主库在执行完客户端提交的事务后不是立刻返回给客户端，而是等待至少一个从库接收到并写到 relay log 中才返回给客户端。相对于异步复制，半同步复制提高了数据的安全性，同时它也造成了一定程度的延迟，这个延迟最少是一个 TCP/IP 往返的时间。所以，半同步复制最好在低延时的网络中使用。半同步复制失败(配置超时时间)，自动转为异步复制

半同步复制配置步骤

  1. 加载使用的插件

主库执行以下命令

master
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';

从库执行以下命令

slave
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';

通过 show plugins; 可查看已加载的插件

  1. 启动半同步复制

主库执行以下命令

master
SET GLOBAL rpl_semi_sync_master_enabled = 1;

从库执行以下命令

slave
SET GLOBAL rpl_semi_sync_slave_enabled = 1;

执行以下命令重启从库上的 IO 线程

slave
STOP SLAVE IO_THREAD;
START SLAVE IO_THREAD;
  1. 检查半同步复制插件是否在运行

主库执行以下命令

master
show status like 'Rpl_semi_sync_master_status';

从库执行以下命令

slave
show status like 'Rpl_semi_sync_slave_status';

Mysql 复制级别说明

不同复制级别的设置会影响到 Master 端的 bin-log 记录成不同的形式。
配置方式:

/etc/my.cnf
binlog_format='row'

基于 sql 语句（Statement level）

每一条会修改数据的 sql 都会记录到 master 的 bin-log 中。slave 在复制的时候，sql 进程会解析成和原来 master 端执行过的相同的 sql 来再次执行。

优点 ：statement level 下的优点首先就是解决了row level下的缺点，不需要记录每一行数据的变化，减少 bin-log 日志量，节约 IO，提高性能。因为他只需要记录在 Master 上所执行的语句的细节，以及执行语句时候的上下文的信息。

缺点 ：由于他是记录的执行语句，所以，为了让这些语句在 slave 端也能正确执行，那么他还必须记录每条语句在执行的时候的一些相关信息，也就是上下文信息，以保证所有语句在 slave 端被执行的时候能够得到和在 master 端执行时候相同的结果。
另外就是，由于 Mysql 现在发展比较快，很多的新功能不断的加入，使 mysql 的复制遇到了不小的挑战，复制的时候涉及到越复杂的内容，bug 也就越容易出现。在 statement level 下，目前已经发现的就有不少情况会造成 mysql 的复制出现问题，主要是修改数据的时候使用了某些特定的函数或者功能的时候会出现，比如：sleep()函数在有些版本中就不能真确复制，在存储过程中使用了 last_insert_id()函数，可能会使 slave 和 master 上得到不一致的 id 等等。
由于 row level 是基于每一行来记录的变化，所以不会出现类似的问题。

基于一条记录（Row level）

日志中会记录成每一行数据被修改的形式，然后在 slave 端再对相同的数据进行修改

优点 ： 在 row level 模式下，bin-log 中可以不记录执行的 sql 语句的上下文相关的信息，仅仅只需要记录那一条记录被修改了，修改成什么样了。所以 row level 的日志内容会非常清楚的记录下每一行数据修改的细节，非常容易理解。而且不会出现某些特定情况下的存储过程，或 function，以及 trigger 的调用和触发无法被正确复制的问题。
任何情况都可以被复制，这对复制来说是最安全可靠的；和其他大多数数据库系统的复制技术一样；多数情况下，从服务器上的表如果有主键的话，复制就会快了很多,更少的锁

缺点 ： row level 下，所有的执行的语句当记录到日志中的时候，都将以每行记录的修改来记录，这样可能会产生大量的日志内容，比如有这样一条 update 语句：update product set owner_member_id = ‘b’ where owner_member_id = ‘a’，执行之后，日志中记录的不是这条 update 语句所对应的事件（mysql 以事件的形式来记录 bin-log 日志），而是这条语句所更新的每一条记录的变化情况，这样就记录成很多条记录被更新的很多个事件。自然，bin-log 日志的量就会很大。尤其是当执行 alter table 之类的语句的时候，产生的日志量是惊人的。因为 Mysql 对于 alter table 之类的表结构变更语句的处理方式是整个表的每一条记录都需要变动，实际上就是重建了整个表。那么该表的每一条记录都会被记录到日志中。

Mixed

在 Mixed 模式下，Mysql 会根据执行的每一条具体的 sql 语句,来区分对待记录的日志形式，也就是在 Statement 和 Row 之间选择一种。新版本中的 Statment level 还是和以前一样，仅仅记录执行的语句。而新版本的 Mysql 中对 row level 模式也被做了优化，并不是所有的修改都会以 row level 来记录，像遇到表结构变更的时候就会以 statement 模式来记录，如果 sql 语句确实就是 update 或者 delete 等修改数据的语句，那么还是会记录所有行的变更。

GTID 模式

需要基于 row 模式，mysql-5.6.2 支持,mysql5.6.10 后完善

/etc/my.cnf
log_bin=on
binlog_format='row'
gtid_mode=on
enforce-gtid-consistency=on
log-slave-updates=1

限制：

  1. 不支持非事务引擎(从库报错, stop slave; start slave ; 忽略)
  2. 不支持 create table … select 语句(主库直接报错)
  3. 不支持一个 sql 同时更新一个事务引擎和非事务引擎的表
  4. 在一个复制组中,必须要求统一开启 gtid 或是关闭 gtid
  5. 开启 gtid 需要重启
  6. 开启 gtid 后,就不在使用原来传统的复制方式
  7. 对于 create temporary table 和 drop temporary table 语句不支持
  8. 不支持 sql_slave_skip_counter

MySQL（主从）配置相关参数

master 相关配置

master my.cnf
server-id = 1
log-bin = mysql-bin    #要生成的二进制日志文件名称
binlog_format=statement/row/mixed        ###复制实现模式/级别

#binlog-do-db = rtzc_pnc_base       ##要同步的库.应该尽可能的在从库上使用replicate_wild_*，不建议在主库上使用
#binlog-ignore-db = mysql   #不同步的数据库,应该在从库上尽可能的使用replicate_wild_*
##### 一般不再主服务器上过滤，虽然可以减少主的开销，但这样会导致二进制日志不完整
sync_binlog=0
########
# 0      表示MySQL 不控制binlog的刷新，由文件系统自己控制它的缓存的刷新。这时候的性能是最好的，但是风险也是最大的。因为一旦系统Crash，在binlog_cache中的所有binlog信息都会被丢失。
# sync_binlog>0    表示每N个sync_binlog次事务提交，MySQL调用文件系统的刷新操作将缓存刷下去。最安全的就是sync_binlog=1了，表示每次事务提交，MySQL都会把binlog刷下去，是最安全但是性能损耗最大的设置。这样的话，在数据库所在的主机操作系统损坏或者突然掉电的情况下，系统才有可能丢失1个事务的数据。但是binlog虽然是顺序IO，但是设置sync_binlog=1，多个事务同时提交，同样很大的影响MySQL和IO性能。对于高并发事务的系统来说，“sync_binlog”设置为0和设置为1的系统写入性能差距可能高达5倍甚至更多。

innodb_flush_log_at_trx_commit = 1
###########innodb特有参数
# N=0     每隔一秒，把事务日志缓存区的数据写到日志文件中，以及把日志文件的数据刷新到磁盘上；在这种情况下，MySQL性能最好，但如果 mysqld 进程崩溃，通常会导致最后 1s 的日志丢失。

# N=1     每个事务提交时候，把事务日志从缓存区写到日志文件中，并且刷新日志文件的数据到磁盘上；每次事务提交时，log buffer 会被写入到日志文件并刷写到磁盘。这也是默认值。这是最安全的配置，但由于每次事务都需要进行磁盘I/O，所以也最慢。

# N=2    当取值为 2 时，每次事务提交会写入日志文件，但并不会立即刷写到磁盘，日志文件会每秒刷写一次到磁盘。这时如果 mysqld 进程崩溃，由于日志已经写入到系统缓存，所以并不会丢失数据；在操作系统崩溃的情况下，通常会导致最后 1s 的日志丢失。 对于一些数据一致性和完整性要求不高的应用，配置为 2 就足够了；如果为了最高性能，可以设置为 0。有些应用，如支付服务，对一致性和完整性要求很高，所以即使最慢，也最好设置为 1. 

# replicate-wild-ignore-table=db.*               ###应该在从库上尽可能的使用replicate_wild_

max_binlog_size
expire_logs_days = 7          ##日志保存天数

slave 相关配置

my.cnf
server-id = 2
relay-log = mysql-relay-bin
#replicate-do-db = rtzc_pnc_base     ##应该在从库上尽可能的使用replicate_wild_*，并不会减少主往从复制数据占用带宽
#replicate-do-table = db.table      ##表级过滤
##replicate-ignore-table = db.table
#replicate_ignore_db=test
#replicate_wild_do_table=DB_NAME.%
#replicate_wild_ignore_table=DB_NAME.%
#slave-skip-errors = 1032,1062     ###跳过某些同步错误号
#log-slave-updates=1          ###从库需要记录binlog，如级联从库场景，级联从库必须有此参数
log-bin = mysql-bin

expire_logs_days = 7

read-only         ###从库Slave中使用read-only参数，确保从库数据不被非法更新。
#innodb_read_only = 1         ###控制root用户，慎用