这篇MySQL主从复制与分库分表读取分离稳了!

前言

MySQL主从复制、分库分表以及读写分离是在数据库领域中常用的一些技术手段，它们可以帮助我们提高数据库的性能、可用性和扩展性。

⚠️ 本篇对应Mysql基础以上的同学们食用、本篇文章作者使用了2台服务器进行操作用作主、从.

一、数据库瓶颈

概述

不管是 IO 瓶颈，还是 CPU 瓶颈，最终都会导致数据库的活跃连接数增加，进而逼近甚至达到数据库可承载活跃连接数的阈值。

IO 瓶颈

第一种

• 磁盘读 IO 瓶颈，热点数据太多，数据库缓存放不下，每次查询时会产生大量的 IO，降低查询速度
• 分库和垂直分表

第二种

• 网络 IO 瓶颈，请求的数据太多，网络带宽不够

CPU 瓶颈

第一种

• SQL 问题，如 SQL 中包含 join，group by，order by，非索引字段条件查询等，增加 CPU 运算的操作
• SQL 优化，建立合适的索引，在业务 Service 层进行业务计算

第二种

• 单表数据量太大，查询时扫描的行太多，SQL 效率低，CPU 率先出现瓶颈
• 水平分表

二、MySQL 主从架构

部署架构

一主一从

一对一主从

一主多从

一对多

级联复制

级联复制

双主

双主

主从集群

数据安全，给主服务增加一个数据备份。读写分离，对于大部分的 Java 业务系统来说，都是读多写少的，读请求远远高于写请求。这时，当主服务的访问压力过大时，可以将数据读请求转为由从服务来分担，主服务只负责数据写入的请求，这样大大缓解数据库的访问压力，故障转移-高可用，当 MySQL 主服务宕机后，可以由一台从服务切换成为主服务，继续提供数据读写功能。

主从同步原理

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

主从同步原理示例图

主要涉及 3 个线程：

• binlog dump thread：根据从节点 IO thread 的请求，分批读取 binlog 文件指定位置之后的信息并返回从节点
• IO thread：当从节点执行 START SLAVE 命令开启主从复制后，从节点会创建 IO thread，用来连接主节点，请求指定 binlog、指定位置之信的白志内容，并将获得的内容存到 relay log
• SQL thread：检测 relay log 新增的内容，并将 relay log 的内容解析成具体的 SQL，在从节点按照位置顺序执行，从而保证主从节点数据一致

三、Docker 部署 MySQL 主从复制

默认的异常复制

拉取 MySQL8 镜像：

docker pull mysql:8

主库，创建主从 MySQL 的配置及数据文件的存储目录命令如下：

mkdir -p /usr/local/mysqlData/master/config

mkdir -p /usr/local/mysqlData/master/data

配置主服务器的配置文件命令如下：

vim /usr/local/mysqlData/master/config/mysql.cnf

[mysqld]
server-id=10

# 开启 binlog
log_bin=master-bin
log_bin-index=master-bin.index
skip-name-resolve

# 允许最大连接数
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

创建主从 MySQL 镜像，主服务器实例化命令如下：

docker run -itd -p 3307:3306 --name master -v /usr/local/mysqlData/master/config:/etc/mysql/conf.d -v /usr/local/mysqlData/master/data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=root mysql:8

• -p：指定容器暴露的端口，宿主机（物理机）端口，-p 3307:3306 把物理机的 3307 端口给实例的端口 3306 端口进行映射
• -v：给容器挂载存储卷，挂载到容器的某个目录，-v /usr/local/mysqlData/master/cnf:/etc/mysql/conf.d 把刚创建的配置文件夹映射成实例的 /etc/mysql/conf.d，-v /usr/local/mysqlData/master/data:/var/lib/mysql，数据文件夹的映射

进入命令模式进入命令如下：

docker exec -it master bash

登录 MySQL 输入如下命令：

mysql -u root -p

然后在输入密码即可登录：

linux

给主服务器授权命令如下：

GRANT REPLICATION SLAVE ON *.* TO 'root'@'%';

flush privileges;

查看主节点同步状态查看的信息需要记一下：

show master status;

从库，创建主从 MySQL 的配置及数据文件的存储目录命令如下：

mkdir -p /usr/local/mysqlData/slave/config

mkdir -p /usr/local/mysqlData/slave/data

配置从服务器的配置文件命令和内容如下：

vim /usr/local/mysqlData/slave/config/mysql.cnf

[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

# 允许最大连接数
max_connections=200

# 允许连接失败的次数
max_connect_errors=10

# 服务端使用的字符集默认为UTF8
character-set-server=utf8

# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB

# 默认使用“mysql_native_password”插件认证
# mysql_native_password
default_authentication_plugin=mysql_native_password

实例化从服务器 MySQL 命令如下实例化之前也需要拉取一下 MySQL 镜像：

docker pull mysql:8

docker run -itd -p 3308:3306 --name slaver -v /usr/local/mysqlData/slave/config:/etc/mysql/conf.d -v /usr/local/mysqlData/slave/data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=root mysql:8

进入到运行的 MySQL 容器当中：

docker exec -it slaver bash

登录 MySQL：

mysql -u root -p

开启从服务器，如下命令当中的 MASTER_LOG_FILE 参数值与 MASTER_LOG_POS 这两个属性的参数值是利用 show master status; 命令进行查看所得到的如下图，如果你所得到的和我的不一样需要自己修改一下：

CHANGE MASTER TO MASTER_HOST='127.0.0.1',MASTER_PORT=3307,MASTER_USER='root',MASTER_PASSWORD='root',MASTER_LOG_FILE='master-bin.000003',MASTER_LOG_POS=543,GET_MASTER_PUBLIC_KEY=1;

start slave;

show slave status \G;

查看从状态

创建表测试

当中的字段就两个 id、name 同学们动手操作吧 分别给主、从 数据库创建相同表

测试主、从：

进入 master 对表 tab_user 插入一条数据 打开数据库工具查看从库是否同步

测试

如上的步骤需要注意的就是服务器之间的 MySQL 端口号是否开放了，否则会造成搭建失败的问题。

优点

异步复制，性能比较好。

缺点

主从之间存在延迟，数据不一致，可能会丢数据。

四、半同步复制

原理

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

搭建

在 MySQL5.5 以后，自带了这个模块，在 MySQL 安装目录下的 lib/plugin 中有。

主服务安装：通过扩展库来安装半同步复制模块指令如下：

install plugin rpl_semi_sync_master soname 'semisync_master.so';

查看系统全局参数指令如下：

show global variables like 'rpl_semi%';

打开半同步复制的开关指令如下：

set global rpl_semi_sync_master_enabled=ON;

从服务安装，就直接上指令了：

install plugin rpl_semi_sync_slave soname 'semisync_slave.so';

show global variables like 'rpl_semi%';

set global rpl_semi_sync_slave_enabled = on;

show global variables like 'rpl_semi%';

从服务器需要重启 slave 服务指令如下：

stop slave;

start slave;

查看状态

show status like '%semi_sync%%';

优点，主节点会等待至少 1 个节点返回后才会提交，提供两种模式，新模式不会丢数据，到超时时间时，会自动降级成异步复制的模式。缺点，存在等待从节点的开销，性能比异步复制要差一些。

五、测试半同步复制

首先将一台机子挂掉(主)，就是把防火墙关闭，命令在 Linxu 防火墙相关操作 有自行查找，防火墙关闭了之后在去云上面找到控制台将安全组的安全策略对应的端口号也进行关闭，如下图所示：

去腾讯云控制台关闭端口访问策略

查看从机的主从连接状态如下：

已经关闭完毕，然后我们在主当中往测试表当中插入数据查看效果如下：

insert into tab_user(id, name) values(4, 'BNTang4');

继续执行插入语句你会发现不需要在等待 10 秒钟了，这就是半同步复制，第一次超时了就切换成了异步了，如下：

insert into tab_user(id, name) values(5, 'BNTang5');

这时查看主数据库表的数据如下：

最新的数据已经进去了主库当中在来看看从库的数据库数据如下：

发现没有同步过来，因为我们现在是断开的状态，这时我们在将安全组放开，防火墙开启（放开的是主的哈）然后在进行查看从服务器数据库的数据：

经过如上的测试就已经验证了如上的切换规则，超过了超时时长之后就会切换成异步同步，然后我们在来正常的进行一次插入在主服务器插入数据然后在从服务器查看数据即可如下：

insert into tab_user(id, name) values(8, 'BNTang8');

六、Linux 防火墙相关操作

查看端口状态

netstat -tunlp

查看防火墙状态

firewall-cmd --state

开启防火墙

systemctl start firewalld

向 firewall 添加需要开放的端口

永久的添加该端口。去掉 --permanent 则表示临时。

firewall-cmd --permanent --zone=public --add-port=3308/tcp

关闭端口

firewall-cmd --zone=public --remove-port=3308/tcp --permanent

加载配置使得修改有效

firewall-cmd --reload

查看开启的端口

firewall-cmd --permanent --zone=public --list-ports

启动防火墙

systemctl start firewalld.service

七、MySQL 高可用方案

MySQL 高可用

MySQL 主服务挂了，从服务是没办法自动切换成主服务的，想要实现高可用，需要借助第三方工具来实现。

常见高可用架构

PXC

优点，多主模式，同步复制，多节点写，且能保证数据一致，搭建简单，故障切换较容易。缺点，同步复制，性能会取决于集群中性能最差的节点，存在短板效应存在写扩大的问题，节点数不建议超过 8 个。场景，对—致性要求非常高的场景，例如金融业务。

MHA

优点，自动故障转移，转移速度快，故障转移时会最大程度保持数据—致性，对已有主从复制架构侵入性小。缺点，自身不提供 VIP 配置工具，需要额外搭建，另外功能是否强大很大程度取决于脚本。场景，主从复制模式下的各种场景。

MGR

优点，既支持单主模式，又支持多主模式，且都能保持数据一致性，借鉴了 PXC 等技术，融合了其优势，性能表现比 PXC 优越。缺点，多主模式局限性比较多。

MMM

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

分库分表

垂直拆分

垂直分库

概念

以表为依据，按照业务归属不同，将不同的表拆分到不同的库中。

结构

• 每个库的结构都不一样
• 每个库的数据也不一样，没有交集
• 所有库的并集是全量数据

使用场景

系统绝对并发量上来了，并且可以抽象出单独的业务模块。

分析

到这一步，基本上就可以服务化了。例如，随着业务的发展一些公用的配置表、字典表等越来越多，这时可以将这些表拆到单独的库中，甚至可以服务化。再有，随着业务的发展孵化出了一套业务模式，这时可以将相关的表拆到单独的库中，甚至可以服务化。

垂直分表

概念

以字段为依据，按照字段的活跃性，将表中字段拆到不同的表（主表和扩展表）中。

结构

每个表的结构都不一样，每个表的数据也不一样，一般来说，每个表的字段至少有一列交集，一般是主键，用于关联数据，所有表的并集是全量数据。

使用场景

系统绝对并发量并没有上来，表的记录并不多，但是字段多，并且热点数据和非热点数据在一起，单行数据所需的存储空间较大。以至于数据库缓存的数据行减少，查询时会去读磁盘数据产生大量的随机读 IO，产生 IO 瓶颈。

分析

可以用列表页和详情页来帮助理解。垂直分表的拆分原则是将热点数据（可能会冗余经常一起查询的数据）放在一起作为主表，非热点数据放在一起作为扩展表。这样更多的热点数据就能被缓存下来，进而减少了随机读 IO。拆了之后，要想获得全部数据就需要关联两个表来取数据。但记住，千万别用 join，因为 join 不仅会增加 CPU 负担并且会将两个表耦合在一起（必须在一个数据库实例上）。关联数据，应该在业务 Service 层做，分别获取主表和扩展表数据然后用关联字段关联得到全部数据。

水平拆分

水平分表

概念

以字段为依据，按照一定策略（hash、range 等），将一个表中的数据拆分到多个表中。

结构

每个表的结构都一样，每个表的数据都不一样，没有交集。所有表的并集是全量数据。

使用场景

系统绝对并发量并没有上来，只是单表的数据量太多，影响了 SQL 效率，加重了 CPU 负担，以至于成为瓶颈。表的数据量少了，单次 SQL 执行效率高，自然减轻了 CPU 的负担。

水平分库

以字段为依据，按照一定策略（hash、range 等），将一个库中的数据拆分到多个库中。

结构

每个库的结构都一样，每个库的数据都不一样，没有交集，所有库的并集是全量数据。

使用场景

系统绝对并发量上来了，分表难以根本上解决问题，并且还没有明显的业务归属来垂直分库。库多了，io 和 cpu 的压力自然可以成倍缓解。 像订单表，可以根据来源进行分库。

最后

选题思维导图

选题思维导图

本期结束咱们下次再见👋~

🌊 关注我不迷路，如果本篇文章对你有所帮助，或者你有什么疑问，欢迎在评论区留言，我一般看到都会回复的。大家点赞支持一下哟~ 💗

我正在参与 腾讯云开发者社区数据库专题有奖征文。