DB
MySQL

MySQL主从架构

实际生产中，往往数据量会极为庞大，并且数据的安全性要求也更高，生产环境中MySQL必须是要搭建一套主从复制架构，同时可基于一些工具实现高可用架构。在此基础上则可基于一些中间件实现读写分离架构。若数据量非常大，还必须可实现分库分表的架构。

通过搭建MySQL主从集群可缓解MySQL数据存储及访问压力，搭建主从集群时，双方MySQL必须版本一致，至少主服务版本低于从服务，且两节点间时间需要同步。

  • 数据安全：给主服务增加一个数据备份，可搭建主从架构或基于主从架构搭建互主架构
  • 读写分离：读多写少的情况，主服务访问压力过大时，可将数据读请求转为由从服务来分担，主服务只负责数据写入请求
  • 故障转移：当MySQL主服务宕机后可由一台从服务切换成为主服务，继续提供数据读写功能

MySQL主从架构一般都是通过binlog日志文件来进行的，即在主服务上打开binlog记录每一步数据库操作，从服务上会有一个IO线程，负责跟主服务建立一个TCP连接请求主服务将binlog传输过来。且主库上会有一个IO dump线程，负责通过该TCP连接把Binlog日志传输给从库IO线程。从服务IO线程会把读取到的binlog日志数据写入自己的relay日志文件中。然后从服务上另外一个SQL线程读取relay日志内容进行操作重演，达到还原数据的目的。通常对MySQL做的读写分离配置必须基于主从架构来搭建。

MySQL的Binlog不光可用于主从同步，还可用于缓存数据同步等场景。如Canal，可模拟一个Slave节点向MySQL发起Binlog同步，然后将数据落到Redis、Kafka等其他组件，实现数据实时流转。

主从搭建

主节点配置

对于主节点的配置文件my.cnf中，主要是添加打开binlog日志，以及指定server-id，配置好后service mysqld restart重启MySQL服务。

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
[mysqld]
# 服务节点的唯一标识，需要给集群中的每个服务分配一个单独的ID
server-id=47
# 打开Binlog日志记录，并指定文件名
log_bin=master-bin
# Binlog日志文件
log_bin-index=master-bin.index
skip-name-resolve
# 设置连接端口
port=3306
# 设置mysql的安装目录
basedir=/usr/local/mysql
# 设置mysql数据库的数据的存放目录
datadir=/usr/local/mysql/mysql-files
# 允许最大连接数
max_connections=200
# 允许连接失败的次数。
max_connect_errors=10
# 服务端使用的字符集默认为UTF8
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
# 默认使用mysql_native_password插件认证
default_authentication_plugin=mysql_native_password

# 需要同步的二进制数据库名
binlog-do-db=masterdemo
# 只保留7天的二进制日志，以防磁盘被日志占满(可选)
expire-logs-days=7
# 不备份的数据库
binlog-ignore-db=information_schema
binlog-ignore-db=performation_schema
binlog-ignore-db=sys

给root用户分配一个replication slave的权限，然后通过show master status命令查看主节点同步状态。

1
2
3
4
5
6
# 登录主数据库
mysql -u root -p
GRANT REPLICATION SLAVE ON *.* TO 'root'@'%';
flush privileges;
# 查看主节点同步状态：
show master status;

File和Position表示当前日志binlog文件和文件中的索引。Binlog_Do_DB表示需要记录binlog文件的库，Binlog_Ignore_DB不需要记录binlog文件的库，若没有进行配置，则表示针对全库记录日志。

开启binlog后数据库中所有操作都会被记录到datadir当中，以一组轮询文件的方式循环记录。上面指令查到的File和Position就是当前日志文件和位置，配置从服务时需要通过File和Position通知从服务从哪个地方开始记录binlog。

从节点配置

从服务同样需要配置服务节点的唯一标识server-id，且需要打开bin-log日志记录，且需要打开relay-log日志。

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
[mysqld]
# 主库和从库需要不一致
server-id=48
# 打开MySQL中继日志
relay-log-index=slave-relay-bin.index
relay-log=slave-relay-bin
# 打开从服务二进制日志
log-bin=mysql-bin
# 使得更新的数据写进二进制日志中
log-slave-updates=1
# 设置3306端口
port=3306
# 设置mysql的安装目录
basedir=/usr/local/mysql
# 设置mysql数据库的数据的存放目录
datadir=/usr/local/mysql/mysql-files
# 允许最大连接数
max_connections=200
# 允许连接失败的次数。
max_connect_errors=10
# 服务端使用的字符集默认为UTF8
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
# 默认使用mysql_native_password插件认证
default_authentication_plugin=mysql_native_password

# 若salve库名称与master库名相同，使用本配置
replicate-do-db = masterdemo 
# 若master库名[mastdemo]与salve库名[mastdemo01]不同，使用以下配置[需要做映射]
replicate-rewrite-db = masterdemo -> masterdemo01
# 若不是要全部同步[默认全部同步]，则指定需要同步的表
replicate-wild-do-table=masterdemo01.t_dict
replicate-wild-do-table=masterdemo01.t_num

启动从服务设置其主节点同步状态，CHANGE MASTER指令中需要指定的MASTER_LOG_FILE和MASTER_LOG_POS必须与主服务中查到的保持一致。且后续若要检查主从架构是否成功，也可通过检查主服务与从服务之间File和Position两个属性是否一致。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
#登录从服务
mysql -u root -p;
#设置同步主节点：
CHANGE MASTER TO
MASTER_HOST='192.168.232.128',
MASTER_PORT=3306,
MASTER_USER='root',
MASTER_PASSWORD='root',
MASTER_LOG_FILE='master-bin.000008',
MASTER_LOG_POS=154
GET_MASTER_PUBLIC_KEY=1;
# 开启slave
start slave;
# 查看主从同步状态，或者用show slave status \G;这样查看比较简洁
show slave status;

主从架构可能失败，若在slave从服务上查看slave状态，发现Slave_SQL_Running=no表示主从同步失败。这可能因为在从数据库进行了写操作，与同步过来的SQL操作冲突了，也可能slave从服务重启后有事务回滚了。

读写分离

主从集群是单向的只能从主服务同步到从服务，而从服务的数据表更新无法同步到主服务。为了保证数据一致，通常需要保证数据只在主服务上写，而从服务只进行数据读取。但MySQL主从本身无法提供读写分离服务，需要由业务自己来实现。

需要限制用户写数据，可在从服务中将read_only参数的值设为1，set global read_only=1;可限制用户写入数据。但该属性有两个需要注意的地方：

  • read_only=1设置只读模式，不影响slave同步复制功能
  • read_only=1设置的只读模式， 限定的是普通用户进行数据修改的操作，但不限定具有super权限的用户的数据修改操作。 若需限定super权限的用户写数据可设置super_read_only=0。若想连super权限用户的写操作也禁止使用flush tables with read lock;，但该设置会阻止主从同步复制。
其他集群方式

若想进一步提高整个集群的读能力，可扩展出一主多从，为了减轻主节点进行数据同步的压力，可继续扩展出多级从的主从集群。为了提高整个集群的高可用能力，可扩展出多主集群。

也可扩展出互为主从的互主集群甚至是环形的主从集群，实现MySQL多活部署。搭建互主集群只需要按照主从方式，且在主服务上打开一个slave进程，且指向slave节点binlog当前文件地址和位置。

传统的Binlog方式搭建集群是基于日志记录点的方式来进行主从同步，还可通过GTID搭建方式搭建主从同步，GTID本质也是基于Binlog来实现的主从同步，GTID是基于一个全局事务ID来标识同步进度。该GTID全局事务ID是一个全局唯一且趋势递增的分布式ID策略。

GTID搭建方式是从MySQL5.6版本引入，即用到上面Executed_Grid_Set列，首先从服务器会告诉主服务器已经在从服务器执行完了哪些事务的GTID值，主库会把所有没有在从库上执行的事务，发送到从库上进行执行，且使用GTID复制可保证同一个事务只在指定从库上执行一次，可避免由于偏移量问题造成数据不一致。

1
2
3
4
5
6
7
8
9
10
11
# 主节点my.cnf文件中添加如下配置
gtid_mode=on
enforce_gtid_consistency=on
log_bin=on
server_id=1
binlog_format=row
# 从节点my.cnf文件中添加如下配置
gtid_mode=on
enforce_gtid_consistency=on
log_slave_updates=1
server_id=2
集群扩容

若集群已运行一段时间，这时若要扩展新的从节点，之前的数据没办法从binlog来恢复。这时在扩展新的slave节点时，需要增加一个数据复制的操作。使用mysql的bin目录下的mysqldump工具生成数据备份文件。

1
2
3
4
5
# 在主库生成数据备份文件
mysqldump -u root -p --all-databases > backup.sql

# 从库上将该文件导入即可
mysql -u root -p < backup.sql
延迟问题

主从复制之间会有延迟，在做了读写分离后会更容易体现出来。即数据往主服务写，而读数据在从服务读。主从复制延迟可能造成刚插入了数据但查不到，大型集群中会很容易出现。

出现这个问题的根本在于，面向业务的主服务数据都是多线程并发写入的，而从服务是单个线程慢慢拉取binlog，这中间会有效率差。所以解决这个问题的关键是要让从服务也用多线程并行复制binlog数据。MySQL5.7开始支持并行复制。可在从服务上设置slave_parallel_workers为一个大于0的数，然后把slave_parallel_type设置为LOGICAL_CLOCK。

半同步赋值

MySQL主从集群默认采用异步复制机制。主服务在执行用户提交的事务后写入binlog日志，然后给客户端返回成功响应，而binlog会由一个dump线程异步发送给Slave从服务。

由于发送binlog过程是异步的，主服务在向客户端反馈执行结果时，不知道binlog是否同步成功。若此时主服务宕机，而从服务还没有备份到新执行的binlog可能会丢数据。这就要靠MySQL的半同步复制机制来保证数据安全。

半同步复制机制是一种介于异步复制和全同步复制之前的机制，主库在执行完客户端提交的事务后，并不是立即返回客户端响应，而是等待至少一个从库接收并写到relay log中才返回给客户端，MySQL在等待确认时默认等待10s，若超过10s没有收到ack则会降级成为异步复制。

半同步复制相比异步复制，能够有效的提高数据的安全性，但这种安全性不是绝对的，其只保证事务提交后的binlog至少传输到了一个从库，不保证从库应用该事务binlog成功。

半同步复制机制会造成一定程度的延迟，该延迟时间至少是一个TCP/IP请求往返的时间。整个服务性能会有所下降。而当从服务出现问题时，主服务需要等待的时间就会更长，要等到从服务的服务恢复或者请求超时才能给用户响应。

半同步复制需要基于特定扩展模块来实现，而MySQL5.5版本开始默认自带了该模块。该模块包含在MySQL安装目录下lib/plugin目录下semisync_master.so和semisync_slave.so两个文件中。需在主服务上安装semisync_master模块，从服务上安装semisync_slave模块。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
# 通过扩展库来安装半同步复制模块，需要指定扩展库的文件名
mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so';
Query OK, 0 rows affected (0.01 sec)
# 打开半同步复制开关
mysql> set global rpl_semi_sync_master_enabled=ON;
Query OK, 0 rows affected (0.00 sec)
# 查看系统全局参数，rpl_semi_sync_master_timeout就是半同步复制等待应答最长等待时间，默认10秒
mysql> show global variables like 'rpl_semi%';
+-------------------------------------------+------------+
| Variable_name                             | Value      |
+-------------------------------------------+------------+
| rpl_semi_sync_master_enabled              | ON        |
| rpl_semi_sync_master_timeout              | 10000      | # 默认10秒
| rpl_semi_sync_master_trace_level          | 32         |
| rpl_semi_sync_master_wait_for_slave_count | 1          |
| rpl_semi_sync_master_wait_no_slave        | ON         |
| rpl_semi_sync_master_wait_point           | AFTER_SYNC | # 表示一种半同步复制的方式
+-------------------------------------------+------------+
6 rows in set, 1 warning (0.02 sec)

半同步复制有AFTER_SYNC和AFTER_COMMIT两种方式：

  • AFTER_SYNC方式，主库把日志写入binlog且复制给从库，然后开始等待从库的响应。从库返回成功后主库再提交事务，接着给客户端返回一个成功响应。
  • AFTER_COMMIT方式，主库写入binlog后等待binlog复制到从库，主库就提交自己本地事务，再等待从库返回给自己一个成功响应，然后主库再给客户端返回响应。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
# 通过扩展库来安装半同步复制模块，需要指定扩展库的文件名
mysql> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
Query OK, 0 rows affected (0.01 sec)
# 打开半同步复制开关
mysql> set global rpl_semi_sync_slave_enabled = on;
Query OK, 0 rows affected (0.00 sec)
# 查看系统全局参数
mysql> show global variables like 'rpl_semi%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled     | ON    |
| rpl_semi_sync_slave_trace_level | 32    |
+---------------------------------+-------+
2 rows in set, 1 warning (0.00 sec)
# 安装完slave端的半同步插件后，需要重启下slave服务
mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)

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

MySQL高可用方案

使用MySQL自身功能来搭建的集群，不具备高可用功能，若MySQL主服务挂了，从服务没办法自动切换成主服务。常见的MySQL集群方案有三种: MMM、MHA、MGR。共同点：

  • 对主从复制集群中的Master节点进行监控
  • 自动的对Master进行迁移，通过VIP虚拟IP。
  • 重新配置集群中其它slave对新的Master进行同步
MMM

Master-Master replication manager for MySQL即MySQL主主复制管理器是一套由Perl语言实现的脚本程序，可对MySQL集群进行监控和故障迁移，需要两个Master，但同一时间只有一个Master对外提供服务，可以说是主备模式。

通过一个VIP即虚拟IP机制来保证集群高可用，主节点上会通过一个虚拟IP地址来提供数据读写服务，当出现故障时，虚拟IP从原来主节点漂移到其他节点，由其他节点提供服务。

提供了读写VIP的配置，使读写请求都可以达到高可用；工具包相对比较完善，不需要额外的开发脚本；完成故障转移之后可对MySQL集群进行高可用监控；但故障简单粗暴，容易丢失事务，建议采用半同步复制方式，减少失败的概率；目前MMM社区已经缺少维护，不支持基于GTID的复制；适用于读写都需要高可用的场景以及基于日志点的复制方式。

MHA

Master High Availability Manager and Tools for MySQL由日本人开发基于Perl脚本。专门用于监控主库状态，当发现Master节点故障时，会提升其中拥有新数据的Slave节点成为新的Master节点，在此期间MHA会通过其他从节点获取额外信息来避免数据一致性方面的问题。MHA还提供了Mater节点的在线切换功能，MHA能够在30秒内实现故障切换，并能在故障切换过程中，最大程度的保证数据一致性。

MHA需要单独部署，分为Manager节点和Node节点。Manager节点一般是单独部署一台机器。而Node节点一般部署在每台MySQL机器上，Node节点得通过解析各个MySQL的日志来进行一些操作。

Manager节点会通过探测集群里Node节点去判断各个Node所在机器上的MySQL运行是否正常，若发现某个Master故障则直接把他的一个Slave提升为Master，然后让其他Slave都挂到新的Master上去，完全透明。

支持日志点复制方式和GTID方式；同MMM相比，MHA会尝试从旧Master中恢复旧二进制日志，但未必每次都能成功。若希望更少的数据丢失场景，建议使用MHA架构。但MHA需要自行开发VIP转移脚本；MHA只监控Master状态未监控Slave状态；

MGR

MGR：MySQL Group Replication是MySQL官方在5.7.17版本正式推出的一种组复制机制。主要是解决传统异步复制和半同步复制的数据一致性问题。由若干个节点共同组成一个复制组，一个事务提交后，必须经过超过半数节点的决议并通过后才可提交。MGR依靠分布式一致性协议Paxos协议的一个变体，实现了分布式下数据的最终一致性，提供了真正的数据高可用方案。

支持多主模式，但官方推荐单主模式，多主模式下，客户端可以随机向MySQL节点写入数据，单主模式下MGR集群会选出primary节点负责写请求，primary节点与其它节点都可以进行读请求处理。

基本无延迟，延迟比异步的小很多，支持多写模式，但是目前还不是很成熟，数据的强一致性，可以保证数据事务不丢失；仅支持innodb，且每个表必须提供主键，只能用在GTID模式下，且日志格式为row格式。适用于对主从延迟比较敏感，希望对写服务提供高可用，又不想安装第三方软件，数据强一致的场景。