MySQL集群方案

[toc]

一. MySQL集群概述

1.1 mysql为什么要使用集群

• 高可用性：故障检测及迁移，多节点备份。

• 可伸缩性：新增数据库节点便利，方便扩容。

• 负载均衡：切换某服务访问某节点，分摊单个节点的数据库压力

1.2 mysql集群的常见问题

• 网络分裂：群集还可能由于网络故障而拆分为多个部分，每部分内的节点相互连接，但各部分之间的节点失去连接。

• 脑裂：导致数据库节点彼此独立运行的集群故障称为“脑裂”。这种情况可能导致数据不一致，并且无法修复，例如当两个数据库节点独立更新同一表上的同一行时

1.3 mysql复制架构

mysql集群中各实例的数据同步，均基于mysql的复制机制

mysql复制架构的演进：

1. 2000年，MySQL 3.23.15版本引入了Replication，作为准实时同步复制，存在主备存在较大延迟时候，会导致大量binary log没有备份到Slave端的问题

2. 2002年，MySQL 4.0.2版本优化了Slave端的同步线程，引入relay log，解决了binary log堆积无法同步的问题

3. 2010年，MySQL 5.5版本引入半同步复制，主库在应答客户端提交的事务前需要保证至少一个从库接收并写到relay log中，解决slave落后，master挂掉状况下的数据丢失问题

4. 2016年，MySQL在5.7.17引入InnoDB Group Replication，即全同步技术

image-20210309135630475

1）全异步复制

• 定义

  主库在执行完客户端提交的事务后会立即将结果返给给客户端，并不关心从库是否已经接收并处理

• 实现

  主库将事务 Binlog 事件写入到 Binlog 文件中，此时主库只会通知一下 Dump 线程发送这些新的 Binlog，然后主库就会继续处理提交操作，不保证这些 Binlog 传到任何一个从库节点上

  image-20210309140558162

2）全同步复制

• 定义

  主库执行完一个事务，所有的从库都执行了该事务才返回给客户端

• 实现

  主库提交事务之后，所有的从库节点必须收到、APPLY并且提交这些事务，然后主库线程才能继续做后续操作

image-20210309140720905

3）半同步复制

• 定义

  介于全同步复制与全异步复制之间，主库只需要等待至少一个从库节点收到并且 Flush Binlog 到 Relay Log 文件即可，主库不需要等待所有从库给主库反馈

• 实现

  主库在执行完客户端提交的事务后不是立刻返回给客户端，而是等待至少一个从库接收到并写到relay log中才返回给客户端

  image-20210309140940585

1.4 mysql复制原理

MySQL支持三种复制方式：基于行的复制、基于语句的复制、混合复制。两种复制方式都是主库记录二进制日志，从库执行日志来实现数据复制，具体分为三步：

1. 在主库把数据更改记录到二进制日志Binlog，这些记录称为二进制日志事件。

2. 备库将主库的日志复制到自己的中继日志（Relay Log）中。

3. 备库读取中继日志的事件，将其重放到备库数据之上

1）基于语句的复制(逻辑复制)

MySQL 5.0以及之前的版本均是基于语句的复制，即：主库记录的是改变数据的查询，从库执行的语句与主库一致

优点：

1. 实现简单

2. 日志数据量小，占用带宽少

缺点：

1. 更新必须是串行，需要大量的特殊代码、配置(比如InnoDB的next-key锁)

2. 部分存储引擎不支持基于语句的复制(比如MySQL Cluster引擎)

3. 主备库的基础环境必须一致，若语句含有不确定的函数调用，会导致主从库不一致

2）基于行的复制

MySQL 5.1开始支持基于行的复制，即：主库记录的是实际数据的改变

优点：

1. 几乎没有基于行复制模式无法处理的场景；任何语句都能正确工作，一些语句的效率更高

缺点：

1. 二进制日志可能会很大，占用更大的带宽

2. 不易理解，不能使用mysqlbinlog来查看二进制日志

3）混合复制(推荐)

MySQL5.1及其以后的版本推荐使用混合模式的复制，它是根据事件的类型实时的改变binlog的格式。当设置为混合模式时，默认为基于语句的格式，但在特定的情况下它会自动转变为基于行的模式：

• 语句中调用了uuid()函数、用户自定义函数、CURRENT_USER或USER函数、LOAD_FILE函数等

• 同一个语句更改了两张或更多包含AUTO_INCREMENT列的表

• 语句中使用了服务器变量

• 存储引擎不允许使用基于语句复制，如：MySQL Cluster引擎

1.5 常见的mysql集群架构

1）主从/主主复制

即数据可以从一个MySQL数据库服务器主节点复制到一个或多个从节点，包含一主一从、一主多从、多主一从(Mysql5.7开始支持)等模式

• MySQL Replication：mysql(3.0版本开始)官方提供的异步复制方案；在 Master 与 Slave 之间的实现整个复制过程主要由三个线程来完成：
  • master端的I/O线程：生成binlog日志
  • slave端的I/O线程：从主库读取bin log，并存储到relay log中继日志文件中)
  • slave端的SQL线程：读取中继日志，解析后，在从库中重放
• MySQL Fabric：2014年5月28日Oracle发布，在MySQL Replication的基础上，增加了故障检测与转移，自动数据分片功能；依旧是一主多从的结构，MySQL Fabirc只有一个主节点，区别是当该主节点挂了以后，会从从节点中选择一个来当主节点
• MySQL Group Replication：mysql(5.7.17版本开始)官方提供的群组复制方案，以插件的方式提供的高可用、高扩展、高可靠的MySQL集群服务
  • 高一致性：基于原生复制及paxos协议的组复制技术，提供一致数据安全保证
  • 高容错性：只要不是大多数节点坏掉就可以继续工作，有自动检测机制，当不同节点产生资源急用冲突时时，不会出现错误，按照先到者优先进行处理，并且内置了自动化脑裂防护机制
  • 高扩展性：节点的新增和移除都是自动的，新节点加入后，会自动从其他节点上同步状态，直到新节点和其他节点保持一致，如果某节点被移除了，其他节点自动更新组信息，自动维护新的组信息
  • 高灵活性：有单主模式和多主模式，单主模式下，会自动选主，所有更新操作都在主上进行；
  • 多主模式下：所有server都可以处理更新操作

2）双主多从架构(MMM)

    MMM（Master Replication Manager for MySQL）是双主多从结构，这是Google的开源项目，使用Perl语言来**对MySQL Replication做扩展**，提供一套支持双主故障切换和双主日常管理的脚本程序，主要用来监控mysql主主复制并做失败转移

• 虽然是双主节点，但是业务上同一时刻只允许对一个主进行写入，另一台备选主上提供部分读服务，以加速在主主切换时刻备选主的预热
• 自动的主主Failover切换，一般3s以内切换备机
• 多个从节点读的负载均衡

3）高可用架构(MHA)

    MHA（Master High Availability）是多主多从结构，这是日本DeNA公司的youshimaton开发，在MySQL Replication的基础上，对其进行优化。主要提供更多的主节点，但是缺少VIP（虚拟IP），需要配合keepalived等一起使用

• 要求一个复制集群中必须最少有三台数据库服务器，一主二从，即一台充当master，一台充当备用master，另外一台充当从库
• 多个复制集由MHA manager进行管理
• 可以进行故障的自动检测和转移，具备自动数据补偿能力，在主库异常崩溃时能够最大程度的保证数据的一致性
• 关于读负载均衡可以使用F5、LVS、HAPROXY或者SQL Proxy等工具

4）分布式协议方案

• MySQL InnoDB Cluster：基于群组复制，提供了易于管理的 API、应用故障转移和路由、易于配置，提供比群组复制更高级别的可用性
• MySQL NDB Cluster：提供更高级别的可用性和冗余性。适用于分布式计算环境，使用内存型的 NDB 存储引擎
• Zookeeper + proxy：Zookeeper使用分布式算法保证集群数据的一致性，使用zookeeper可以有效的保证proxy的高可用性，可以较好的避免网络分区现象的产生
  • 扩展性较好，可以扩展为大规模集群
  • 但是搭建Zookeeper 集群，并配置一套代理，整个系统的逻辑变得非常复杂
• Paxos：分布式一致性算法，Paxos 算法处理的问题是一个分布式系统如何就某个值（决议）达成一致。这个算法被认为是同类算法中最有效的。Paxos与MySQL相结合可以实现在分布式的MySQL数据的强一致性

5）DRDB磁盘复制

    这是linux内核板块实现的快级别的同步复制技术。通过各主机之间的网络，复制对方磁盘的内容。当客户将数据写入本地磁盘时，还会将数据发送到网络中另一台主机的磁盘上，这样的本地主机(主节点)与远程主机(备节点)的数据即可以保证明时同步

• 保证数据的强一致性，且与mysql解耦
• 对io性能影响较大，且从库不提供读操作

6）共享存储

    主库从库用的一个存储。SAN的概念是允许存储设施和解决器（服务器）之间建立直接的高速连接，通过这种连接实现数据的集中式存储

• 保证数据的强一致性
• 与mysql解耦，不会由于mysql的逻辑错误发生数据不一致的情况
• 但是SAN价格昂贵

1.6 常见的mysql读写分离方案

1）客户端解决方案(应用层)

• Sharding-Jdbc

• TDDL

2）中间件解决方案(代理层)

• mycat

• altas

• mysql proxy

  image-20210309133735609

二. MySQL集群实践

2.1 原生主从复制(冷备)

基于MySQL8.0在linux环境进行搭建，主从库需要注意如下：

1. 主从服务器操作系统版本和位数一致；

2. Master 和 Slave 数据库的版本要一致；

3. Master 和 Slave 数据库中的数据要一致；

4. Master 开启二进制日志， Master 和 Slave 的 server_id 在局域网内必须唯一；
5. 须确认防火墙状态，命令systemctl [stop/start/status] firewalld.servic

1）安装mysql

• 安装包安装

# 下载安装包
wget http://mirrors.163.com/mysql/Downloads/MySQL-8.0/mysql-8.0.13-el7-x86_64.tar.gz
# 解压
mysql tar -zxvf mysql-8.0.4-rc-linux-glibc2.12-x86_64.tar.gz -C /usr/local
# 修改文件夹名
mv mysql-8.0.4-rc-linux-glibc2.12-x86_64/ mysql
# 添加默认配置文件
vim/etc/my.cnf
# 创建mysql数据目录
mkdir $MYSQL_HOME/data
# 初始化mysql
/usr/local/mysql/bin/mysqld  --initialize --user=mysql --basedir=/usr/local/mysql/ --datadir=/usr/local/mysql/data/
# 初始化报错的解决方法
yum install -y libaio
# 启动mysql服务器
service mysqld start

• rpm安装

# 下载rpm
wget https://dev.mysql.com/get/mysql80-community-release-el8-1.noarch.rpm
# 升级安装包
# rpm -Uvh mysql80-community-release-el7-3.noarch.rpm
# 安装mysql
yum install mysql-community-server
# 启动mysql
systemctl start mysqld

# 查询默认密码
grep 'temporary password' /var/log/mysqld.log

2）主库配置同步账号

# 修改默认账号密码
use mysql
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456';
# 创建同步账号
CREATE USER 'slave'@'%' IDENTIFIED WITH mysql_native_password BY '123456'; 
# 为同步账号授权
GRANT REPLICATION SLAVE ON *.* TO 'meitian_slave'@'%';
FLUSH PRIVILEGES;
# 设置全局编码
SET CHARACTER_SET_DATABASE = 'UTF8';

3）修改主库配置文件

[mysqld]
# 服务器编号，注意主备编号不要重复
server_id       = 1
# binlog日志存放目录
log_bin         = /var/log/mysql/mysql-bin.log
# 开启Binlog同步，在每次提交事务前会将二进制日志同步到磁盘
sync_binlog     = 1
# binlog的日志格式，ROW-基于行的复制
binlog_format   = ROW
# 需要记录Binlog的DB，有多个的话逗号分隔
binlog_do_db    = test
# 忽略的DB
binlog_ignore_db= mysql,information_schema
# 配置客户端连接的认证方式配置，非必须，当发生备库无法连接主库时可能需要配置
default_authentication_plugin = mysql_native_password

4）修改从库配置文件

[mysqld]
server_id               = 2
log_slave_updates       = 1
log_bin                 = /var/log/mysql/mysql-bin.log
binlog_format           = ROW
replicate_do_db         = test
replicate_ignore_db     = mysql,information_schema

5）设置从库与主库的连接

# 启动主库，并查看主库状态
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 156 | test         | mysql            |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

# 复制位置MASTER_LOG_POS默认为0
# MASTER_LOG_FILE需要和通过查看主库状态的binlog文件一致
mysql> CHANGE MASTER TO
    -> MASTER_HOST='${主机地址}',
    -> MASTER_USER='slave',
    -> MASTER_PASSWORD='123456',
    -> MASTER_LOG_FILE='mysql-bin.000001',
    -> MASTER_LOG_POS=0;

# 启动从库
start slave;
# 等价于下面两个命令
# 启动I/O线程(从主库读取bin log,并存储到relay log中继日志文件中)
start slave sql_thread;
# 启动 SQL线程(读取中继日志，解析后，在从库重放)
start slave io_thread;

# 查看从库状态【\G：表示查询结果按列展示】
mysql> SHOW SLAVE STATUS\G;

# 验证：查看主从库的processlist
# 主库应有：Command:Binlog Dump
# 从库应有：State:Waiting for master to send event
mysql> show processlist;

2.2 原生双主互备(热备)

1）安装mysql

同 '2.1 原生主从复制(冷备)' ，略

2）双主库配置同步账号

同'2.1 原生主从复制(冷备)' ，略

3）修改双主库配置文件

• 主库1配置

server-id = 1
log-bin=mysql-bin
binlog-do-db = meitian
binlog-ignore-db = mysql
#主－主形式需要多添加的部分
log-slave-updates = 1
sync_binlog = 1
auto_increment_offset = 1
auto_increment_increment = 2
replicate-do-db = test
replicate-ignore-db = mysql,information_schema
gtid-mode=on
enforce-gtid-consistency=true
character_set_server=utf8
init_connect='SET NAMES utf8'
lower_case_table_names=1
change master to master_host='172.16.0.14',master_user='slave',master_password='123456',master_log_file='mysql-bin.000003',master_log_pos=986;

• 主库2配置

server-id = 2
log-bin=mysql-bin
binlog-do-db = meitian
binlog-ignore-db = mysql
#主－主形式需要多添加的部分
log-slave-updates = 1
sync_binlog = 1
auto_increment_offset = 2
auto_increment_increment = 2
replicate-do-db = test
replicate-ignore-db = mysql,information_schema
gtid-mode=on
enforce-gtid-consistency=true
character_set_server=utf8
init_connect='SET NAMES utf8'
lower_case_table_names=1
change master to master_host='172.16.0.13',master_user='slave',master_password='123456',master_log_file='mysql-bin.000003',master_log_pos=2568;

4）启动&验证

# 启动I/O线程(从主库读取bin log,并存储到relay log中继日志文件中)和SQL线程(读取中继日志，解析后，在从库重放)
start slave;
# 等价于下面两个命令
# start slave sql_thread;
# start slave io_thread;
# 查询状态
show slave status \G

2.3 MySQL Group Replication

参考：https://dwj999.github.io/MySQL-Group-Replication%E5%88%9D%E6%8E%A2.html

2.4 MySQL InnoDB Cluster

参考：https://jeremyxu2010.github.io/2019/05/mysql-innodb-cluster%E5%AE%9E%E6%88%98/

参考：https://www.modb.pro/db/15033