mysql集群架构

发表于 2023-06-17 分类于基础知识， mysql 本文字数： 14k

集群架构设计

集群架构设计的三个维度：可用性、扩展性、一致性

可用性设计

1）站点高可用，冗余站点

2）服务高可用，冗余服务

3）数据高可用，冗余数据

高可用的方案：主从模式，双主模式（分双主双写和双主单写）

扩展性设计

1）加从库，从库过多会引发主库性能损耗。

2）分库分表，分为垂直拆分和水平拆分，垂直拆分可以缓解部分压力，水平拆分可以无限扩展。

一致性设计

1）不使用从库

2）增加访问路由层，得到主从同步最长时间t，在数据发生修改后的t时间内，先访问主库，保证数据一致。

主从模式使用场景

数据可以从一个MySQL数据库服务器主节点复制到一个或多个从节点，默认采用异步复制方式，这样从节点不用一直访问主服务器来更新自己的数据，从节点可以复制主数据库中的所有数据库，或者特定的数据库，或者特定的表。

主从复制用途

1）实时灾备，用于故障切换（高可用）

2）读写分离，提供查询服务（读扩展）

3）数据备份，避免影响业务（高可用）

主从部署必要条件

1）从库服务器能连通主库

2）主库开启binlog日志（设置log-bin参数）

3）主从server-id不同

主从模式实现原理

实现步骤：

1）主库将数据库的变更操作记录到Binlog日志文件中

2）从库读取主库中的Binlog日志文件信息写入到从库的Relay Log中继日志中

3）从库读取中继日志信息在从库中进行Replay,更新从库数据信息

具体触发机制如下：

1） Master服务器对数据库更改操作记录在Binlog中，BinlogDump Thread接到写入请求后，读取 Binlog信息推送给Slave的I/O Thread。

2） Slave的I/O Thread将读取到的Binlog信息写入到本地Relay Log中。

3） Slave的SQL Thread检测到Relay Log的变更请求，解析relay log中内容在从库上执行。

存在的问题：

1）主库宕机后，数据可能丢失

2）从库只有一个SQL Thread，主库写压力大，复制很可能延时

解决的办法：

1）半同步复制—解决数据丢失的问题

2）并行复制—-解决从库复制延迟的问题

半同步复制

从5.5开始，MySQL让Master在某一个时间点等待Slave节点的ACK消息，接收到ACK消息后才进行事务提交，这就是半同步复制。

主从复制时的完整过程：

1） InnoDB Redo File Write (Prepare Write)

2） Binlog File Flush & Sync to Binlog File

3） InnoDB Redo File Commit（Commit Write）

4） Send Binlog to Slave

当Master不需要关注Slave是否接受到Binlog Event时，即为传统的主从复制。

当Master需要在第三步等待Slave返回ACK时，即为 after-commit，半同步复制（MySQL 5.5引入）。

当Master需要在第二步等待 Slave 返回 ACK 时，即为 after-sync，增强半同步（MySQL 5.7引入）。

并行复制

MySQL从5.6版本开始追加了并行复制功能，改善复制延迟。

5.6版本并行复制

基于库的并行复制，也就是多线程分别执行各自库的操作，互不干扰，单库多表并发效率就不高了。

5.7版本并行复制

基于组提交的并行复制，不再有库的并行复制限制。当事务提交时，通过在主库上的二进制日志中添加组提交信息，并将在单个操作中写入到二进制日志中。如果多个事务能同时提交成功，那么它们意味着没有冲突，因此可以在Slave上并行执行。MySQL 5.7的并行复制基于一个前提，即所有已经处于prepare阶段的事务，都是可以并行提交的。

InnoDB事务提交采用的是两阶段提交模式。一个阶段是prepare，另一个是commit。

在MySQL 5.7版本中，其设计方式是将组提交的信息存放在GTID中。为了避免用户没有开启GTID功能，MySQL 5.7又引入了称之为Anonymous_Gtid的二进制日志event类型，即日志中具有相同的last_committed，表示这些事务都在一组内。

8.0 并行复制

基于write-set的并行复制。有一个集合变量来存储事务修改的记录信息（主键哈希值），所有已经提交的事务所修改的主键值经过hash后都会与那个变量的集合进行对比，来判断改行是否与其冲突，并以此来确定依赖关系，没有冲突即可并行，row级别的粒度，类似于之前的表锁行锁差异，效率肯定更高。

MySQL安装

上传mysql安装包到Linux服务器并进行解压

tar -xvf mysql-5.7.28-1.el7.x86_64.rpm-bundle.tar

检查是否已有Mysql安装，存在就移除‘

rpm -qa | grep mariadb

rpm -e mariadb-libs-5.5.41-2.el7_0.x86_64 –nodeps

安装mysql-community-common-5.7.28-1.el7.x86_64.rpm

rpm -ivh mysql-community-common-5.7.28-1.el7.x86_64.rpm

安装mysql-community-libs-5.7.28-1.el7.x86_64.rpm

rpm -ivh mysql-community-libs-5.7.28-1.el7.x86_64.rpm

安装mysql-community-libs-compat-5.7.28-1.el7.x86_64.rpm

rpm -ivh mysql-community-libs-compat-5.7.28-1.el7.x86_64.rpm

6）安装客户端 mysql-community-client-5.7.28-1.el7.x86_64.rpm

rpm -ivh mysql-community-client-5.7.28-1.el7.x86_64.rpm

7）安装服务端mysql-community-server-5.7.28-1.el7.x86_64.rpm

rpm -ivh mysql-community-server-5.7.28-1.el7.x86_64.rpm

8）安装开发包 mysql-community-devel-5.7.28-1.el7.x86_64.rpm

rpm -ivh mysql-community-devel-5.7.28-1.el7.x86_64.rpm

9）初始化Mysql

mysqld –initialize –user=mysql

10）查看root随机生成默认密码

cat /var/log/mysqld.log #root@localhost: Ajtag.WsR3,J

11）设置自动启动Mysql

systemctl start mysqld.service

12）查看状态

systemctl status mysqld.service

13）登录Mysql

mysql -uroot -p # 密码：Ajtag.WsR3,J

14）修改数据库密码

set password=password(‘root’); 修改密码

exit #退出

15）关闭服务器防火墙

systemctl stop iptables

systemctl stop firewalld

systemctl disable firewalld.service

注意1：如果安装执行出现Header V3 DSA/SHA1 Signature, key ID 5072e1f5:错误，这是由于yum安装了旧版本的GPG keys造成的，在尾部加上–force –nodeps即可。

注意2：如果初始化mysql失败，执行yum -y install numactl命令

注意3：如果安装完mysql之后，mysql命令登录不成功，使用yum install libncurses*命令

主从同步实战

1）修改主库my.cnf配置文件

执行命令vim /etc/my.cnf

log_bin=mysql-bin #开启binlog,文件名称为mysql-bin

server-id=1 #指定server-id

sync-binlog=1 #执行几次后进行磁盘同步1就代表次数

#忽略以下库的同步

binlog-ignore-db=performance_schema

binlog-ignore-db=information_schema

binlog-ignore-db=sys

#指定同步的库不设置就是同步所有库

binlog-do-db=test

2）完成配置修改重启mysql

systemctl restart mysqld

主库授权

#登录MySQL

mysql -uroot -p

#主库授权设置

grant replication slave on . to ‘root‘@’%’ identified by ‘root’;

grant all privileges on . to ‘root‘@’%’ identified by ‘root’;

#刷新权限，立即生效

flush privileges;

#查看主库状态

show master status;

从库配置修改

执行命令 vim /etc/my.cnf

server-id=2

relay_log=mysql-relay-bin #relay-log名称

read_only=1 #此库只读

5）完成配置修改重启mysql

systemctl restart mysqld

6）从库启动授权

#登陆数据库设置复制的主库

change master to master_host=’47.106.138.46’,master_port=3306,master_user=’root’,

master_password=’root’,master_log_file=’ master-bin.000002’,master_log_pos=154;

#查看从库状态

show slave status \G;

#开启从库

start slave;

#停止从库

stop slave;

#重新绑定主库

reset master;

#主库修改配置，从库无法启动，重置

reset slave;

半同步复制实战

1）主库半同步复制设置

#是否支持动态加载

select @@have_dynamic_loading;

#查看插件列表

show plugins;

#安装半同步复制插件并起别名

install plugin rpl_semi_sync_master soname ‘semisync_master.so’;

#查看半同步复制相关参数

show variables like ‘%semi%’;

#开启半同步复制

set global rpl_semi_sync_master_enabled=1;

#设置超时时间，默认10秒，设置为1秒

set global rpl_semi_sync_master_timeout=1000;

2）从库半同步复制设置

#是否支持动态加载

select @@have_dynamic_loading;

#查看插件列表

show plugins;

#安装从库半同步复制插件并起别名

install plugin rpl_semi_sync_slave soname ‘semisync_slave.so’;

#查看半同步复制相关参数

show variables like ‘%semi%’;

#开启从库半同步复制

set global rpl_semi_sync_slave_enabled=1;

#重新加载从库

stop slave;

start slave;

切换到/var/log目录，查看mysqld.log日志文件核验半同步复制是否生效

并行复制实战

1）主库设置

#查看数据库组信息

show variables like ‘%binlog_group%’;

#设置延迟时间

set binlog_group_commit_sync_delay=1000;

#设置组内事务数量

set binlog_group_commit_sync_no_delay_count=100;

2）从库配置

#查看从库可设置参数

show variables like ‘%slave%’;

#修改并行复制方式，由库改组

set global slave_parallel_type=‘LOGICAL_CLOCK’;

#设置组内最大线程数

set global slave_parallel_workers=8;

#查看relay相关参数

show variables like ‘%relay_log%’;

#打开relay_log写入权限

set global relay_log_recovery=1;

#设置日志信息源为table，提高效率

set global relay_log_info_repository=’TAABLE’;

#重启服务，使配置生效

systemctl restart mysqld

读写分离

大多数互联网业务基本上读多写少，因此读写分离就可以提高读的效率，读写分离首先需要将数据库分为主从库，一个主库用于写数据，多个从库完成读数据的操作，主从库之间通过主从复制机制进行数据的同步，而且这样还可以进行主库主写，不加索引，从库主读，加索引，把两个库的效率提升到最高，不过需要解决主从同步延迟和读写分配机制。

主从同步延迟

1）写后立刻读：在写入数据库后，某个时间段内读操作就去主库，之后读操作访问从库。

2）二次查询：先去从库读取数据，找不到时就去主库进行数据读取，注意对恶意攻击限制。

3）根据业务特殊处理：根据业务特点和重要程度进行调整，实时性高数据读写都在主库，其他在从库。

读写分配机制

控制何时去主库写，何时去从库读。

1）基于编程和配置实现：根据操作类型进行路由分配，增删改时操作主库，查询时操作从库。

2）基于服务器端代理实现：使用中间件代理，动态分配，常用MySQL Proxy、MyCat以及Shardingsphere等。

读写分离实战

1）代理主机上解压mysql-proxy代理包

tar -xzvf mysql-proxy-0.8.5-linux-el6-x86-64bit.tar

2）创建一个mysql-proxy配置文件

vim /etc/mysql-proxy.cnf

3）在配置文件内写入相应配置参数

user=root #运行mysql-proxy用户

admin-username=root#主从mysql共有的用户

admin-password=root#用户的密码

proxy-address= 117.50.5.252:4040 #mysql-proxy运行ip和端口，不加端口，默认4040

proxy-backend-addresses= 47.106.138.46 #指定后端主master写入数据

proxy-read-only-backend-addresses= 49.235.85.246 #指定后端从slave读取数据

proxy-lua-script=/usr/local/mysql-proxy/lua/rw-splitting.lua #指定读写分离配置文件位置

log-file=/var/ logs/mysql-proxy.log #日志位置

log-level=debug #定义log日志级别，由高到低分别有(error|warning|info|message|debug)

daemon=true #以守护进程方式运行

keepalive=true #mysql-proxy崩溃时，尝试重启

#保存退出！并修改文件权限

chmod 660 /etc/mysql-porxy.cnf

4）修改lua脚本配置文件

vi /usr/local/mysql-proxy/lua/rw-splitting.lua

min_idle_connections = 1, #最小连接数，默认超过4个连接数时，才开始读写分离，改为1

max_idle_connections = 8, #最大连接数，默认8

5）运行proxy脚本，在对应bin目录找到mysql-proxy

./mysql-proxy –defaults-file=/etc/mysql-proxy.cnf

双主模式

主从模式，一主多从、读写分离，如果是单主模式，单主故障问题没法避免，是双主或者多主，增加MySQL入口，可以提升了主库的可用性，推荐使用双主单写，因为双主双写存在ID冲突和双主更新覆盖丢失问题。

双主模式实战

1）修改主库1的配置

执行命令vim /etc/my.cnf

relay_log=mysql-relay-bin #relay_log名称

log_slave_updates=1

#双主双写自增主键设置 1，3，5，7… 双主单写不用设置

auto_increment_offset=1 #自动递增

auto_increment_increment=2 #递增量

2）完成配置修改重启mysql

systemctl restart mysqld

3）修改主库2的配置

执行命令vim /etc/my.cnf

log_bin=mysql-bin #开启binlog,文件名称为mysql-bin

server-id=3 #指定server-id

sync-binlog=1 #执行几次后进行磁盘同步 1就代表次数

#忽略以下库的同步

binlog-ignore-db=performance_schema

binlog-ignore-db=information_schema

binlog-ignore-db=sys

relay_log=mysql-relay-bin #relay_log名称

log_slave_updates=1

#双主双写自增主键设置 2，4，6，8… 双主单写不用设置

auto_increment_offset=2 #自动递增

auto_increment_increment=2 #递增量

4）登陆mysql并授权

#主库授权设置

grant replication slave on . to ‘root‘@’%’ identified by ‘root’;

grant all privileges on . to ‘root‘@’%’ identified by ‘root’;

#刷新权限，立即生效

flush privileges;

5）主库1和主库2互复制设置

#设置复制的master1主库为master2

change master to master_host=’47.106.138.46’,master_port=3306,

master_user=’root’,master_password=’root’,master_log_file=’mysql-bin.000004’,master_log_pos=154;

#设置复制的master2主库为master1

change master to master_host=’49.235.85.246’,master_port=3306,

master_user=’root’,master_password=’root’,master_log_file=’mysql-bin.000001’,master_log_pos=867;

MMM架构

MMM架构是管理和监控双主复制，支持双主故障切换的第三方软件。

MMM故障处理机制

MMM 划分writer和reader两类角色，分别对应写节点和读节点。

1）当 writer节点出现故障，程序会自动移除该节点上的VIP

2）写操作切换到 Master2，并将Master2设置为writer

3）将所有Slave节点会指向Master2

MMM 也会管理 Slave 节点，在出现宕机、复制延迟或复制错误，MMM会移除该节点的VIP，直到节点恢复正常。

MMM监控机制

MMM 包含monitor和agent两类程序

monitor：监控集群内数据库的状态，在出现异常时发布切换命令。

agent：运行在每个MySQL服务器上的代理进程，monitor 命令的执行者。

MHA架构

MHA是一款优秀的故障切换和主从提升的高可用软件。能30秒之内自动完成数据库的故障切换并最大保证数据一致性，并支持在线快速将Master切换到其他主机，通常只需0.5－2秒。

MHA由两部分组成：MHA Manager（管理节点）和MHA Node（数据节点）

MHA Manager会定时探测集群中的master节点，当master出现故障时，它可以自动将最新数据的 slave提升为新的master，然后将所有其他的slave重新指向新的master。

MHA故障处理机制

1）把宕机master的binlog保存下来

2）根据binlog位置点找到最新的slave

3）用最新slave的relay log修复其它slave

4）将保存下来的binlog在最新的slave上恢复

5）将最新的slave提升为master

6）将其它slave重新指向新提升的master，并开启主从复制

MHA优点

1）自动故障转移快

2）主库崩溃不存在数据一致性问题

3）性能优秀，支持半同步复制和异步复制

4）一个Manager监控节点可以监控多个集群

MHA架构实战

1）环境说明

腾讯云主机作为MHA管理，阿里云作为主服务器，华为云和Ucloud作为从服务器。

2）把mha4mysql-node-0.58-0.el7.centos.noarch.rpm节点程序上传到每个服务器，在MHA管理服务器再上传mha4mysql-manager-0.58-0.el7.centos.noarch.rpm管理端程序。

3）修改主库的/etc/my.cnf配置并重启mysql

4）修改从库的/etc/my.cnf配置并重启mysql

server-id=2

relay-log = relay-log #开启中继日志

log-bin = master-log #开启二进制日志

read_only = ON #启用只读属性

relay_log_purge = 0 #是否自动清空不再需要中继日志

skip_name_resolve #关闭名称解析（非必须）

log_slave_updates = 1 #使得更新的数据写进二进制日志中

5）重启修改配置的库，使配置生效

systemctl restart mysqld

6）安装依赖

yum install perl-DBI -y

yum install perl-DBD-MySQL -y

yum install perl-Config-Tiny -y

yum install https://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm

yum install perl-Log-Dispatch -y

yum install perl-Parallel-ForkManager -y

7）主、从、MHA安装node程序

rpm -ivh mha4mysql-node-0.58-0.el7.centos.noarch.rpm

8） MHA安装manager程序

rpm -ivh mha4mysql-manager-0.58-0.el7.centos.noarch.rpm

9）四台服务器之间建立免密设置，输入第一个命令，回车然后再输入各个服务器地址命名并输入登录密码

#MHA 49.235.99.6

ssh-keygen -t rsa

ssh-copy-id -i /root/.ssh/id_rsa root@47.106.138.46

ssh-copy-id -i /root/.ssh/id_rsa root@49.235.85.246

ssh-copy-id -i /root/.ssh/id_rsa root@117.50.5.252

#master 47.106.138.46

ssh-keygen -t rsa

ssh-copy-id -i /root/.ssh/id_rsa root@49.235.85.246

ssh-copy-id -i /root/.ssh/id_rsa root@117.50.5.252

#slave1 49.235.85.246

ssh-keygen -t rsa

ssh-copy-id -i /root/.ssh/id_rsa root@47.106.138.46

ssh-copy-id -i /root/.ssh/id_rsa root@117.50.5.252

#slave2 117.50.5.252

ssh-keygen -t rsa

ssh-copy-id -i /root/.ssh/id_rsa root@47.106.138.46

ssh-copy-id -i /root/.ssh/id_rsa root@49.235.85.246

10）MHA配置

mkdir -p /etc/mha

vim /etc/mha/mha.cnf

#下载scripts 并放到/etc/mha/scripts路径下，记得给脚本加执行权限

下载连接：https://github.com/yoshinorim/mha4mysql-manager/samples/scripts

[server default]

manager_workdir=/etc/mha/ #manager工作目录

manager_log=/etc/mha/manager.log #mananger日志

master_binlog_dir=/var/lib/mysql #binlog复制目录

user=root

password=root

ping_interval=1

remote_workdir=/tmp

repl_password=root

repl_user=root

secondary_check_script= /usr/bin/masterha_secondary_check -s 49.235.85.246 -s 117.50.5.252 –user=root –master_host=47.106.138.46 –master_ip=47.106.138.46 –master_port=3306

master_ip_failover_script=/etc/mha/scripts/master_ip_failover #切换脚本

master_ip_online_change_script=/etc/mha/scripts/master_ip_online_change #手动switchover时候的切换

#shutdown_script=””

ssh_user=root

[server1]

hostname=47.106.138.46

port=3306

candidate_master=1

check_repl_delay=0

[server2]

hostname=49.235.85.246

port=3306

candidate_master=1

check_repl_delay=0

[server3]

hostname=117.50.5.252

port=3306

10）检查ssh连接和复制状态

#检查ssh免密连接

masterha_check_ssh –conf=/etc/mha/mha.cnf

#检查复制

masterha_check_repl –conf=/etc/mha/mha.cnf

11）启动Manager

nohup masterha_manager –conf=/etc/mha/mha.cnf< /dev/null > /etc/mha/manager.log 2>&1 &

12）查看状态

masterha_check_status –conf=/etc/mha/mha.cnf

13）停止Manager

masterha_stop –conf=/etc/mha/mha.cnf

rm /etc/mha//mha.failover.complete

主备切换策略

主备切换是指将备库变为主库，主库变为备库，有可靠性优先和可用性优先两种策略，可靠性优先为常用。

主备延迟就是同一个事务，在备库执行完成的时间和主库执行完成的时间之间的差值。

同步延迟的原因：备库机器性能差、备库执行其他操作消耗CPU，分工问题、大事务耗时长。

可靠性优先

主备切换过程一般由专门的HA高可用组件完成，但是切换过程中会存在短时间不可用，为保证数据一致性。

可用性优先

不等主从同步完成，直接把业务请求切换至从库B ，并且让从库B可读写。

分库分表

分库分表，主要有垂直拆分和水平拆分两种拆分模式，都属于物理空间的拆分。

分库分表方案：只分库、只分表、分库又分表。

垂直拆分：由于表数量多导致的单个库大。将表拆分到多个库中。

水平拆分：由于表记录多导致的单个库大。将表记录拆分到多个表中。垂直拆分

垂直拆分又称为纵向拆分，垂直拆分是将表按库进行分离，或者修改表结构按照访问的差异将某些

列拆分出去。

垂直分表就是将一张表中不常用的字段拆分到另一张表中，从而保证第一张表中的字段较少，避免出现数据库跨页存储的问题，从而提升查询效率。

按业务分表

字段过多分字段

优点：

1）拆分后业务清晰，拆分规则明确；

2）易于数据的维护和扩展；

3）可以使得行数据变小，一个数据块就能存放更多的数据，在查询时就会减少 I/O 次数；

4）可以达到最大化利用 Cache 的目的。

5）便于实现冷热分离的数据表设计模式。

缺点：

1）主键出现冗余，需要管理冗余列；

2）会引起表连接 JOIN 操作，提高了系统的复杂度；

水平拆分

水平拆分又称为横向拆分，根据某种规则将数据分散至多个库或表中，每个表仅包含数据的一部分。

水平拆分：解决表中记录过多问题。

垂直拆分：解决表过多或者是表字段过多问题。

优点：

1）不存在单库大数据，解决高并发的性能瓶颈；

2）切分的表的结构相同，应用层改造较少，只需要增加路由规则即可；

3）提高了系统的稳定性和负载能力。

缺点：

1）分片事务的一致性难以解决；

2）数据扩容的难度和维护量极大。

主键策略

1） UUID

2） SNOWFLAKE(雪花算法)

3）数据库ID表

4） Redis生成ID

分片策略

数据分片是根据指定的分片键和分片策略将数据水平拆分，拆分成多个数据片后分散到多个数据存储节点中，其实就是把原本数据打散，存在多个数据库中，他和分库分表的差异就是：分片可以制定规则，相当于逻辑，达到对应目的，而分库分表是最终的物理实现。

基于范围分片

根据特定字段的范围进行拆分，比如用户ID、订单时间、产品价格等。

优点：新的数据可以落在新的存储节点上，如果集群扩容，数据无需迁移。

缺点：数据热点分布不均，数据冷热不均匀，导致节点负荷不均。

哈希取模分片

整型的Key可直接对设备数量取模，其他类型的字段可以先计算Key的哈希值，然后再对设备数量取模。

优点：实现简单，数据分配比较均匀，不容易出现冷热不均，负荷不均的情况。

缺点：扩容时会产生大量的数据迁移，比如从n台设备扩容到n+1，绝大部分数据需要重新分配和迁移。

一致性哈希分片

一致性Hash是将数据按照特征值映射到一个首尾相接的Hash环上，同时也将节点（按照IP地址或者机器名Hash）映射到这个环上。对于数据，从数据在环上的位置开始，顺时针找到的第一个节点即为数据的存储节点。

扩容方案

数据库达到承受极限时，就需要增加新服务器节点数量进行横向扩容。

横向扩容需要解决的问题：

1）数据迁移问题

2）分片规则改变

3）数据同步、时间点、数据一致性

停机扩容

停止所有对外服务，新增n个数据库，然后写一个数据迁移程序，将原有x个库的数据导入到最新的y个库中，数据迁移完成，修改数据库服务配置，并重启所有服务。

平滑扩容

持续对外提供服务，保证服务的可用性，通过配置双主同步、双主双写、检测数据同步等来实现。

------ 本文结束感谢您的阅读 ------

请我一杯咖啡吧！

微信打赏