首页  思维导图  详情

Mysql全局优化与8.0主从复制原理分析与高可用集群实战

2026-01-14 19:24:37   0  举报





AI智能生成

java

mysql

作者其他创作

大纲/内容

mysql server系统核心参数

max_connections

mysql最大链接数

一个链接最少占用内存256kb，最大占用64M，如果一个链接的请求数据超过64M（比如排序），就会申请临时空间，放到硬盘上。

max_user_connections

允许用户链接的最大数量

back_log

mysql能暂存的链接数量，如果mysql的链接数量达到max_connections时，新请求的链接会被放在堆栈中等待其余链接释放资源，这个堆栈就是back_log,如果back_log也满了，则会拒绝新请求的链接

wait_timeout

client链接mysql后，空闲多久断开，默认28800，即8小时。

sort_buffer_size

每个链接使用的排序内存，可以增加order by，group by的操作，但并不是越大越好，由于是connections链接级的参数，在高并发下会耗尽系统内存资源

join_buffer_size

用于表关联缓存的大小，和sort_buffer_size一样，该参数对应的分配内存也是每个连接独享。

innodb参数

innodb_thread_concurrency

此参数用来设置innodb线程的并发数，默认值为0表示不被限制，若要设置则与服务器的CPU核心数相 同或是CPU的核心数的2倍，如果超过配置并发数，则需要排队，这个值不宜太大，不然可能会导致线 程之间锁争用严重，影响性能。

innodb_buffer_pool_size=40G

innodb存储引擎buffer pool缓存大小，一般为物理内存的60%-70%。 内存大小直接反应数据库的性能。

mysql8.0新特性

新增降序索引

create table t1(c1 int,c2 int,index idx_c1_c2(c1,c2 desc));

group by不在隐式排序

mysql 8.0 对于group by 字段不再隐式排序，如需要排序，必须显式加上order by 子句。

增加隐藏索引

使用 invisible 关键字在创建表或者进行表变更中设置索引为隐藏索引。索引隐藏只是不可见，但是数据库后台还是会维护隐藏索引的，在查询时优化器不使用该索引，即使用force index，优化器也不会使用该索引 

create table t2(c1 int, c2 int, index idx_c1(c1), index idx_c2(c2) invisible);

启用：alter table t2 alter index idx_c2 visible;

增加函数索引

innodb存储引擎select for update跳过锁等待

对于select ... for share(8.0新增加查询共享锁的语法)或 select ... for update，在语句后面添加 NOWAIT、SKIP LOCKED语法可以跳过锁等待，或者跳过锁定。 在5.7及之前的版本，select...for update，如果获取不到锁，会一直等待，直到innodb_lock_wait_timeout超时。 在8.0版本，通过添加nowait，skip locked语法，能够立即返回。如果查询的行已经加锁，那么nowait 会立即报错返回，而skip locked也会立即返回，只是返回的结果中不包含被锁定的行。 应用场景比如查询余票记录，如果某些记录已经被锁定，用skip locked可以跳过被锁定的记录，只返 回没有锁定的记录，提高系统性能。

参数修改持久化

MySQL 8.0版本支持在线修改全局参数并持久化，通过加上PERSIST关键字，可以将修改的参数持久 化到新的配置文件（mysqld-auto.cnf）中，重启MySQL时，可以从该配置文件获取到最新的配置参 数。set global 设置的变量参数在mysql重启后会失效。

set persist innodb_lock_wait_timeout=25;  系统会在数据目录下生成一个包含json格式的mysqld-auto.cnf 的文件，格式化后如下所示，当my.cnf 和 mysqld-auto.cnf 同时存在时，后者具有更高优先级。

mysql8.0主从复制原理及搭建

什么是复制

Replication(复制)使来自一个 MySQL数据库服务器（称为源（Source））的数据能够复制到一个或多个 MySQL 服务器（称为副本 （Replica））。即主数据库的数据能复制到一个或者多个从数据库

复制的优势

高可用

通过配置一定的复制机制，MySQL 实现了跨主机的数据复制，从而获得一定的高可用能力，如果需要 获得更高的可用性，只需要配置多个副本，或者进行级联复制就可以达到目的。

性能扩展

由于复制机制提供了多个数据备份，可以通过配置一个或多个副本，将读请求分发至副本节点，从而获得整体上读写性能的提升。

异地灾备

只需要将副本节点部署到异地机房，就可以轻松获得一定的异地灾备能力。实际当中，需要考虑网络延迟等可能影响整体表现的因素。

交易分离

通过配置复制机制，并将低频、大运算量的交易发送至副本节点执行，就可以避免这些交易与高频交易竞争运算资源，从而避免整体的性能问题。

复制的缺点

没有故障自动转移，容易造成单点故障

主库从库之间有主从复制延迟问题，容易造成最终数据的不一致

从库过多对主库的负载以及网络带宽都会带来很大的负担

复制的方式

基于从源的二进制日志（binlog）复制事件

基于全局事务标识符(GTID)的方式

复制类型

单向异步复制

流程参照图

主库把外界接收的SQL请求记录到自己的binlog日志中，从库的I/O thread去请求主库的binlog日志，并将得到的binlog日志写到自己的Relay log(中继日志)文件中。然后在从库上重做应用中继日志中的SQL语句。主库通过I/O dump thread给从库I/O thread传送binlog日志。

缺点：提交事务和复制这两个流程在不同的线程中执行，互相不会等待，这是异步复制。异步复制的劣势 是，可能存在主从延迟，如果主节点宕机，可能会丢数据。

半同步复制

主节点在收到客户端的请求后，必须在完成本节点日志写入的同时，还需要等待至少一个从节点完成数据同步的 响应之后（或超时），才会响应请求。 从节点只有在写入 relay-log 并完成刷盘之后，才会向主节点响应。 当从节点响应超时时，主节点会将同步机制退化为异步复制。在至少一个从节点恢复，并完成数据追赶后，主节 点会将同步机制恢复为半同步复制。

相比于异步复制，半同步复制在一定程度上提高了数据的可用性，在未退化至异步复制 时，如果主节点宕机，此时数据已复制至至少一台从节点。同时，由于向客户端响应时需要从节点完 成响应，相比于异步复制，此时多出了主从节点上网络交互的耗时以及从节点写文件并刷盘的耗时， 因此整体上集群对于客户端的响应性能表现必然有所降低。

rpl_semi_sync_master_wait_slave_count（8.0.26之后改为 rpl_semi_sync_source_wait_for_replica_count）：至少等待数据复制到几个从节点再返回。这个数量配置的越 大，丢数据的风险越小，但是集群的性能和可用性就越差。 rpl_semi_sync_master_wait_point（8.0.26之后改为rpl_semi_sync_source_wait_point）：这个参数控制主库 执行事务的线程，是在提交事务之前（AFTER_SYNC）等待复制，还是在提交事务之后（AFTER_COMMIT） 等待复制。默认是 AFTER_SYNC，也就是先等待复制，再提交事务，这样就不会丢数据。

延迟复制

基于binlog的主从复制原理

参照图

1、主库会生成多个 binlog 日志文件。 2、从库的 I/O 线程请求指定文件和指定位置的 binlog 日志文件（位点）。 3、主库 dump 线程获取指定位点的 binlog 日志。 4、主库按照从库发送给来的位点信息读取 binlog，然后推送 binlog 给从库。 5、从库将得到的 binlog 写到本地的 relay log (中继日志) 文件中。 6、从库的 SQL 线程读取和解析 relay log 文件。 7、从库的 SQL 线程重放 relay log 中的命令。

痛点

1.首次开启主从复制的步骤复杂

第一次开启主从同步时，要求从库和主库是一致的。 找到主库的 binlog 位点。 设置从库的 binlog 位点。 开启从库的复制线程。

2.恢复主从复制的步骤复杂

找到从库复制线程停止时的位点。 解决复制异常的事务。无法解决时就需要手动跳过指定类型的错误，比如通过设置 slave_skip_errors=1032,1062。当然这个前提条件是跳过这类错误是无损的。（1062 错误是插入数据时唯一键 冲突；1032 错误是删除数据时找不到行）

基于全局事务标识符（GTID）复制

什么是GTID？

GTID是一个基于原始mysql服务器生成的一个已经被成功执行的全局事务ID，它由服务器ID以及事务ID组合而成。

一个GTID在一个服务器上只执行一次，避免重复执行导致数据混乱或者主从不一致。

GTID用来代替传统复制方法，不再使用MASTER_LOG_FILE+MASTER_LOG_POS开启复制。而是使用 MASTER_AUTO_POSTION=1的方式开始复制。

在传统的replica端，binlog是不用开启的，但是在GTID中replica端的binlog是必须开启的，目的是记录执行过的 GTID（强制）。

优势

1.更简单的实现 failover，不用以前那样在需要找位点（log_file 和 log_pos）。 2.更简单的搭建主从复制。 3.比传统的复制更加安全。 4.GTID 是连续的没有空洞的，保证数据的一致性，零丢失。

GTID工作原理

主库计算主库 GTID 集合和从库 GTID 的集合的差集，主库推送差集 binlog 给从库。

从库 B 指定主库 A，基于主备协议建立连接。

从库 B 把集合 y 发给主库 A。

主库 A 计算出集合 x 和集合 y 的差集，也就是集合 x 中存在，集合 y 中不存在的 GTID 集合。比如集合 x 是 1~100，集合 y 是 1~90，那么这个差集就是 91~100。这里会判断集合 x 是不是包含有集合 y 的所有 GTID，如 果不是则说明主库 A 删除了从库 B 需要的 binlog，主库 A 直接返回错误。

主库 A 从自己的 binlog 文件里面，找到第一个不在集合 y 中的事务 GTID，也就是找到了 91。

主库 A 从 GTID = 91 的事务开始，往后读 binlog 文件，按顺序取 binlog，然后发给 B。

从库 B 的 I/O 线程读取 binlog 文件写入 relay log，SQL 线程解析 relay log，然后执行 SQL 语句。

GTID 同步方案和位点同步的方案区别是：

位点同步方案是通过人工在从库上指定哪个位点，主库就发哪个位点，不做日志的完整性判断。

而 GTID 方案是通过主库来自动计算位点的，不需要人工去设置位点，对运维人员友好。

组复制

什么是组复制？

Mysql5.7.17推出了一个高可用与高扩展的解决方案Mysql GroupReplication(简称MGR)，将原有的gtid复制功能进行了增强，支持单主模式和多主模式。

与传统复制相比，做的改进有哪些？

传统复制的主从复制方式有一个主和不等数量的从。主节点执行的事务会异步发送给从节点，在从节点重新执 行。而Group Replication采用整组写入的方式，避免了单点争用。

Group Replication在传输数据时使用了Paxos协议。Paxos协议保证了数据传输的一致性和原子性。基于Paxos协 议，Group Replication构建了一个分布式的状态复制机制，这是实现多主复制的核心技术。