MySQL主从复制与异地容灾

MySQL主从复制用途、条件和问题

用途

  1. 实时灾备,用于故障切换
  2. 读写分离,提供查询服务

主从复制的必要条件

  1. 主库开启binlog日志(设置log-bin参数)
  2. 主库和从库的server-id不同
  3. 从库能连接到主库,并且有权限

主从复制原理

MySQL最基本的主从复制是异步复制(Asynchronous replication)
Master事务的提交不需要经过Slave的确认,Master对Slave接收Master binlog成功与否并不关心。Slave接收到Master的binlog后先写relay log,然后异步执行relay log的SQL。
从库有两个线程

  1. I/O线程,请求主库的binlog,并将得到的binlog日志写入到relay log(中继日志)文件中。
  2. SQL线程,读取中继日志,并解析成具体的操作执行,保证主从一致
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    Slave1  --------------------------------> relay log ---> apply ---> binlog ---> commit ------------->
    /|\
    Commit | End
    ------ | ----->
    \|/ | | 不用等待直接返回成功
    Master -----> execute ---> binlog -----> commit --------------------------------------------------->
    |
    |
    \|/
    Slave2 --------------------------------> relay log ---> apply ---> binlog ---> commit ------------->

主库生成log dump线程,给从库的I/O线程传递binlog

问题

  1. 主库和从库的同步会出现延时,主库宕机可能导致数据丢失
  2. 主库和从库的同步因为网络延迟等各种问题,导致主库和从库的差距过大
  3. 主库和从库的由于配置问题、权限问题等,导致主从同步失败
  4. 主库只负责把binlog日志发送到从库,当从库故障时,导致主从数据库数据不一致

单一的主从复制并不能保证数据的安全可靠,也不能做到异地容灾

解决方案

  1. 基于GTID的半同步复制
    1. 解决数据丢失问题(GTID)
    2. 解决从库故障时主库无感知的问题(半同步)
  2. 并行复制,解决从库复制延迟的问题

MySQL基于GTID的半同步复制

GTID

GTID(Global Transaction ID 全局事物标记)是对于一个已提交事务的编号,并且是一个全局唯一的编号.GTID实际上是由UUID+TID组成的.其中UUID是一个MySQL实例的唯一标识.server_uuid在MySQl启动时,使用当前时间,随机数等拼接成一个128bit位的uuid.然后该server_uuid被写入到auto.conf文件,当MySQL再次重启时不再随即生成,直接使用保存的这个server_uuid.
TID代表了该实例上已经提交的事务数量,并且随着事务提交单调递增.
MySQL数据库从5.6.5开始新增一种基于GDIT的复制方式.通过GDIT保证每个主库上提交的事务在集群中有一个唯一的ID.这种方式强化了数据库的主备一致性,故障恢复以及容错能力.

全同步复制(Fully synchronous replication)

主库完成一个事务,所有从库都需要返回事务执行成功才返回给客户端。由于要所有从库都返回Ack成功才能返回,所有全同步复制的性能会受到严重影响

半同步复制(Semisynchronous replication)

半同步复制的效率介于异步复制和半同步复制之间,主库完成一个事务不直接返回给客户端,而是等待至少一个从库接收到并写到relay log中然后给主库返回Ack,才返回客户端。

1
2
3
4
5
6
7
8
9
10
11
12
Slave1  ------------------------------> relay log ----------> apply ---> binlog ---> commit ------------->
/|\ |
| Ack 至少等待一个从库的Ack才给客户端返回成功
| |
Commit | | Commit End
------ | | ----->
\|/ | \|/ |
Master -----> execute ---> binlog -----> engine commit ------------------------------------------------->
| /|\
| Ack
\|/ |
Slave2 ------------------------------> relay log ----------> apply ---> binlog ---> commit ------------->

半同步复制Slave对relay log的应用仍然是异步的

开启基于GTID的主从复制,在从库和主库都需要在配置文件加入如下配置:

1
2
3
gtid-mode=ON
enforce-gtid-consistency=ON
log-slave-updates=ON

半同步复制的增强

MySQL5.7增强半同步复制,两种方式:AFTER SYNCAFTER COMMIT

AFTER COMMIT

5.6版本MySQL半同步的方式,现在引擎层做提交后再等待Ack确认。
如果一个事务在该模式下,如果在接收Ack的阶段Master实例故障,数据库切到从库,有两种请求:

  1. binlog未发送到从库,但主库已经提交了,主从数据不同步
  2. binlog已发送到从库,但Client未收到提交成功的消息,但该事务实际已执行成功,如果业务不判断则会将该事务执行两次

AFTER SYNC

官方推荐方式,客户端发出Commit请求后,在主库上写入binlog并推送给Slave,Slave接收到binlog并写入relay log,再发送Ack到Master,Master收到Ack后再在引擎层提交。

1
2
3
4
5
6
7
8
9
10
11
12
Slave1  ------------------------------> relay log ---> apply ---> binlog ---> commit ------------->
/|\ |
| Ack 至少等待一个从库的Ack才给客户端返回成功
| |
Commit | | End
------ | | ----->
\|/ | \|/ |
Master -----> execute ---> binlog ------------------>----engine commit -------------------------->
| /|\
| Ack
\|/ |
Slave2 ------------------------------> relay log ---> apply ---> binlog ---> commit ------------->

该模式下,现将binlog发送到从库,然后再在引擎层提交

基于GTID的主从复制实例

配置

单机启动3306和3307两个MySQL实例,配置以3306作为主库,3307为从库 同步主库的test.t_test1test.t_test2
两个实例的server-id不能相同

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
[mysqld3306]
user = mysql
port = 3306
socket = /data/mysql/3306/mysql.sock
pid-file = /data/mysql/3306/mysqld.pid
log-error = /data/mysql/3306/mysqld.log
datadir = /data/mysql/3306/data
max_connections = 200
default-storage-engine = innodb
character_set_server = utf8

log-bin=mysql-bin #开启二进制日志
server-id=1 #服务器ID
binlog-do-db=test #需要同步的数据库名
binlog-ignore-db=mysql #禁止同步的数据库名

gtid-mode=ON
enforce-gtid-consistency=ON
log-slave-updates=ON

[mysqld3307]
user = mysql
port = 3307
socket = /data/mysql/3307/mysql.sock
pid-file = /data/mysql/3307/mysqld.pid
log-error = /data/mysql/3307/mysqld.log
datadir = /data/mysql/3307/data
max_connections = 200
default-storage-engine = innodb
character_set_server = utf8

log-bin=mysql-bin
server-id=2

relay_log=relay-bin
replicate-wild-do-table=test.t_test1
replicate-wild-do-table=test.t_test2
slave-skip-errors=all

gtid-mode=ON
enforce-gtid-consistency=ON
log-slave-updates=ON

3307同步3306的test.t_test1和test.t_test2

  1. 在3306上创建同步所用的帐号 grant replication slave on *.* to rep@'%' identified by '123456';
  2. 在3307上设置同步配置 change master to master_host='127.0.0.1',master_user='rep',master_password='123456',master_port=3306;
  3. 开启同步 start slave;
  4. 关闭同步 stop slave;
  5. 查看同步状态 show slave status\G
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    MariaDB [(none)]> show slave status\G
    *************************** 1. row ***************************
    Slave_IO_State: Waiting for master to send event
    Master_Host: 127.0.0.1
    Master_User: root
    Master_Port: 3306
    Connect_Retry: 60
    Master_Log_File: mysql-bin.000001
    Read_Master_Log_Pos: 502
    Relay_Log_File: relay-bin.000002
    Relay_Log_Pos: 801
    Relay_Master_Log_File: mysql-bin.000001
    Slave_IO_Running: Yes
    Slave_SQL_Running: Yes
    Replicate_Do_DB:
    Replicate_Ignore_DB:
    Replicate_Do_Table:
    Replicate_Ignore_Table:
    Replicate_Wild_Do_Table: test.t_test1,test.t_test2
    Replicate_Wild_Ignore_Table:
    Last_Errno: 0
    Last_Error:
    Skip_Counter: 0
    Exec_Master_Log_Pos: 502
    Relay_Log_Space: 1104
    Until_Condition: None
    Until_Log_File:
    Until_Log_Pos: 0
    Master_SSL_Allowed: No
    Master_SSL_CA_File:
    Master_SSL_CA_Path:
    Master_SSL_Cert:
    Master_SSL_Cipher:
    Master_SSL_Key:
    Seconds_Behind_Master: 0
    Master_SSL_Verify_Server_Cert: No
    Last_IO_Errno: 0
    Last_IO_Error:
    Last_SQL_Errno: 0
    Last_SQL_Error:
    Replicate_Ignore_Server_Ids:
    Master_Server_Id: 1
    Master_SSL_Crl:
    Master_SSL_Crlpath:
    Using_Gtid: No
    Gtid_IO_Pos:
    Replicate_Do_Domain_Ids:
    Replicate_Ignore_Domain_Ids:
    Parallel_Mode: conservative
    SQL_Delay: 0
    SQL_Remaining_Delay: NULL
    Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
    Slave_DDL_Groups: 0
    Slave_Non_Transactional_Groups: 0
    Slave_Transactional_Groups: 1
    1 row in set (0.000 sec)

MySQL组复制(MySQL Group Replication,简称MGR)

传统异步复制和半同步复制的缺陷(数据的一致性问题无法保证)
由若干个节点共同组成一个复制组,一个事物的提交,必须经过组内大多数节点(N/2+1)决议并通过才能进行提交。
组复制主要是解决传统异步复制和半同步复制可能产生的数据不一致的问题。组复制依靠分布式一致性协议(Paxos协议的变体),实现分布式下数据的一致性。

MGR原理

1
2
3
4
5
6
7
8
9
10
11
12
13
14
                      --------------------
| |
Slave1 --------------|--------->------> certify ---> relay log ---> apply ---> binlog ---> commit ------------->
| /|\ |
Commit | | | End
------ | | | ----->
\|/ | | | |
Master -----> execute|----------------> certify ---> binlog ------------------>-----------> commit ------------>
| | |
| \|/ |
Slave2 --------------|--------->------> certify ---> relay log ---> apply ---> binlog ---> commit ------------->
| |
| Consensus |
--------------------

一个复制组由若干个节点(数据库实例)组成,组内各个节点维护各自的数据副本,通过一致性协议实现原子消息和全局有序消息,来实现组内实例数据的一致。

MGR特性

  • 数据一致性保障:确保集群中大部分节点收到日志
  • 多节点写入支持:多写模式下支持集群中的所有节点都可以写入
  • 容错:确保系统发生故障(包括脑裂)依然可用
  • MGR的性能比Semi-sync(半同步复制)好
  • MGR能极大的简化业务逻辑架构

碰到问题

mysqld_multi启动实例会默认读取mysql默认配置

mysqld_multi启动实例会读取默认mysql配置,导致配置有问题,尝试给mysqld_multi使用--defaults-file设置默认配置,但仍未解决,最后解决方法先移除mysql默认配置,启动mysqld_multi实例后再恢复回去

参考资料