Mysql性能优化和高可用架构思维导图模板

mysql架构介绍

MySQL物理文件体系结构

数据目录

系统数据库

information_schema

提供数据库的元数据信息

如

数据库名称和数据库中表名称

字段名称、字段类型等

performance_schema

数据库性能相关的信息

如

服务器性能参数

保存历史事件汇总信息等

sys

系统运行信息，可⽤于⽅便查询数据库信息、性能瓶颈分析等

sys库中的信息通过视图的⽅式将information_ schema、
performance_schema库中的数据结合起来，可以更加直观查看信息

mysql

存储系统⽤⼾权限和帮助信息

用户数据库

临时表空间

存放SQL处理过程中的⽂件临时表

共享表空间和独立表空间

共享表空间

所有InnoDB表公共存储⽂件，⼀些元数据信息、 undo记录、
插⼊缓冲、双写缓冲等存放在共享表空间中

独⽴表空间

每个InnoDB表数据、索引数据的存储⽂件

general_log

通⽤查询⽇志，记录客⼾连接、执⾏语句等信息

show log

记录慢查询⽇志，默认对于执⾏时间超过10秒的执⾏语句信息进⾏记录

error log

记录启动、停⽌信息，及运⾏时错误信息

undo log

记录事务需要回滚的数据，主要⽤于事务回滚，以及MVCC

redo log

innoDB存储引擎的事务⽇志，⽤于机器崩溃恢复

binlog

记录逻辑操作，⽤于主从复制及精确的定点恢复

MySQL逻辑架构

客户端层

连接/线程管理

身份验证等

核心服务层(server)

查询缓存(MySQL8移除)

解析器

优化器

执行器

存储引擎层

MySQL存储引擎

InnoDB存储引擎

事务型数据库⾸选引擎，⽀持ACID

⽀持⾏级锁、⼀致性读、多⽤⼾并发

独⽴⽀持数据、索引的缓存

⽀持外键完整性约束、⽀持聚集索引

⽀持基于⽇志的崩溃恢复(crash-safe)

MyISAM存储引擎

不⽀持事务

仅⽀持表级锁

⽆MVCC机制，仅⽀持共享锁与排它锁

⾃⾝仅缓存索引，数据缓存由操作系统负责

适⽤于不需要事务、并发性低、以读为主的场景

Memory存储引擎

基于内存存储所有表数据

默认使⽤哈希索引，可修改为使⽤B+树索引

InnoDB存储引擎体系结构

体系结构

多个后台线程

刷新内存池中的数据，保证池中数据最新

将已修改数据⽂件刷回磁盘

保证数据库崩溃后可正常恢复

内存池

各种缓冲池组成⼀个⼤的InnoDB存储引擎内存池

维护线程需要访问的多个内部数据结构

缓存磁盘数据，同时对磁盘⽂件修改进⾏缓存

REDO⽇志缓存等

⽂件

缓冲池

缓冲池对InnoDB存储引擎的性能影响⾮常⼤，实际使⽤中，通常会将系统整体内存容量的50%〜 80%都分配给缓冲池使⽤

基本实现

读取数据时，先检查缓冲中是否存在，不存在则从磁盘读取并存⼊缓冲池，⽅便后续可以直接从缓冲池获取

修改数据时，先写⼊缓冲池，之后再根据⼀些算法机制⼀定频率将缓冲池中脏数据刷回磁盘

LRU(最近最少使用)

innoDB存储引擎基于LRU算法对超出容量限制的数据进⾏淘汰处理

innoDB存储对传统LRU算法进⾏了改进

提供了配置项来设置新⽼数据在LRU列表中的百分⽐，百分⽐之前的数据是新数据，
百份⽐之后的数据是⽼数据，新插⼊的数据仅能插⼊⽼数据范围的前⾯，
只有当新插⼊数据再次访问时才能有机会从⽼数据范围移到新数据范围中
（就是真正到LRU整个列表的前⾯），主要是防⽌对⼤表全表访问时误将LRU前⾯的热数据剔除

提供了配置项，当新记录在⽼数据范围停留⼀段时间后才能移到新数据范围，
主要是防⽌数据预读误（⼀⻚有多条记录，对⻚中所有记录访问会造成短暂
对该⻚多次访问）将LRU前⾯的热数据剔除

当系统关闭后，LRU前部的热点数据会丢失，重新重启后，对于热点数据的预热可能会随着数据量变⼤⽽造成预热时间过⻓，
新版⽀持在数据库关闭后，将LRU前部的热点数据存储到磁盘上，待数据库重启后，将暂存的热点数据快速加载回内存

Change Buffer

对于表中含多个⾮唯⼀性的⼆级索引，在数据更新较多的场景，对⼆级索引的维护会产⽣⼤量的随机IO，
从⽽对性能造成⼀定影响，Change Buffer会对⾮唯⼀性的⼆级索引变更暂存到Change Buffer缓冲中，
对⾮唯⼀性的⼆级索引多次累加，可以间接实现随机IO转变为顺序IO，从⽽提⾼性能，
之后根据算法在⼀定频率将Change Buffer缓冲中数据合并到真正的⼆级索引⻚中

自适应哈希索引

InnoDBD存储引擎⾃动监测表上⼆级索引，根据访问频率和模式来为该⻚建⽴哈希索引

⾃适应哈希索引只适⽤于等值谓词的查询

Redo Log Buffer

innoDB的事务⽇志缓存，避免频繁更新事务⽇志造成磁盘性能问题

为了防⽌系统崩溃造成REDO LOG丢失，可以通过系统属性配置确保每次事务提交时都将缓存刷新到磁盘中

双写

表数据每次写到磁盘是基于⻚为单位写⼊，为了防⽌⼀⻚数据在写的过程中还未完成就出现系统崩溃，
造成磁盘上该⻚数据损坏，通过双写机制，在每次数据⻚写⼊时，先向共享表空间的双写区写⼊该数据⻚，
再向原本磁盘位置写⼊数据⻚，当系统崩溃后，如果磁盘数据⻚损坏则可以通过双写区备份的好⻚覆盖掉损坏的⻚

注意：REDO⽇志并⾮完整⼀⻚的物理备份，所以当物理⻚损坏后⽆法仅通过REDO⽇志进⾏恢复

InnoDB后台线程

主线程

负责REDO⽇志缓冲刷新处理、插⼊缓冲的合并处理、UNDO⽆⽤⻚回收处理等

I/O线程

负责异步IO的回调处理

脏⻚刷新线程

负责将缓冲区脏⻚刷新处理

purge线程

undo log涉及的update undo log除了⽤于事务回滚，还⽤于MVCC，
当事务完成时，由于可能其它事务因为MVCC机制还在使⽤update undo log数据，
不能⽴即删除undo log，purge线程就会定期检查undo log涉及的⻚，
没有被其它事务引⽤的undo log⻚就可以安全回收

当记录被删除时仅仅是在记录条⽬中打上删除标记，并不⽴即删除，purge线程负责定期回收这些⻚

Redo Log

Redo Log 机制

检查点

当REDO LOG⽂件写满后，会⾃动切换到另⼀个⽇志⽂件，
切换时会触发检查点处理，将脏数据⻚刷回磁盘，同时更新检查点，
使得在系统崩溃后可以直接从检查点开始进⾏恢复

崩溃恢复

当系统崩溃重启后，检查磁盘数据⻚的LSN信息，
如果磁盘数据⻚的LSN⽐REDO LOG中的 LSN⼩，
则表明磁盘数据⻚的数据是旧的，
需要重放REDO LOG进⾏磁盘数据⻚的更新

作用

表数据通常会先写⼊缓冲区，之后再刷新到磁盘中，当系统崩溃后会造成部分⽤⼾数据丢失，
REDO⽇志机制则⽤于解决该数据丢失问题

Undo Log

记录事务需要回滚的数据，⽤于事务回滚

⽤于MVCC多版本并发控制，当事务A读取记录时，事务B并发对该记录进⾏修改，
当前记录的事务标识会修改为事务B在处理，同时会将记录的旧信息写⼊UNDO LOG，
此时事务A在读取记录时发现当前记录项的事务标识对⾃⼰是不可⻅的，
则需要去UNDO LOG中查找历史数据来使⽤

Query Cache(查询缓存)

查询缓存，该功能⽐较鸡肋，⽼版本使⽤都是建议关闭，mysql 8已经直接将该功能去除

Mysql事务和锁

MySQL事务隔离级别

⽀持的隔离级别

读未提交

读已提交

可重复读（mysql默认）

串行读(序列化)

并发可能产⽣的问题

脏读

不可重复读

幻读

⽀持MVCC多版本并发控制机制，可实现写和读之间⽆锁并发

InnoDB的锁机制介绍

主要⽀持两种级别锁机制

⾏级锁

共享锁

多个读可同时并⾏，写会被阻塞

排他锁

读和写都会被阻塞

表级锁

意向共享锁

基于节点层级，对需要加共享锁的节点，将到达该节点路径上所有其他节点加上意向共享锁，
阻⽌其它事务会同时对该路径上任何节点获取排他锁

意向排他锁

基于节点层级，对需要加排他锁的节点，将到达该节点路径上所有其他节点加上意向排他锁，
阻⽌其它事务会同时对该路径上任何节点进⾏访问

根据锁定范围分类

记录锁(Record Lock)

只锁定⼀⾏记录

间隙锁(Grab Lock)

锁定记录周边间隙范围（不包括记录⾃⾝），防⽌插⼊，⽤于解决幻读

邻间锁(Next Key Lock)

锁定记录周边间隙范围（同时包括记录⾃⾝），防⽌插⼊，⽤于解决幻读

锁等待和死锁

锁等待

多个事务对同⼀记录占锁，在锁不兼容时，⼀个事务成功获取锁，⼀个事务阻塞等待释放

可以通过sys.innodb_lock_waits表查看锁等待及锁占⽤的详细信息，
同时也可以接合 performance_schema.events_statements_ current表查看相关线程的执⾏SQL语句情况

死锁

两个或两个以上的事务相互持有和请求锁，并形成⼀个循环的依赖关系，就会产⽣死锁。
InnoDB会⾃动检测事务死锁，⽴即回滚其中某个事务，并返回⼀个错误

死锁查看⽅法

通过show engine innodb status 查看死锁信息(仅能记录最后⼀次死锁信息)

设置 set global innodb_print_all_deadlocks = 1；
能把InnoDB发⽣的所有死锁信息都记录在错误⽇志中(5.6版本之后,可以查看所有死锁信息)

避免死锁⽅法

不同程序并发访问多个表及记录时，尽量约定相同的访问顺序

把应⽤程序进⾏调整，将⼤事务分解为多个⼩事务，减少发⽣死锁概率

为表添加合理索引，必免全表扫描锁定每⾏记录

对⾮常容易产⽣死锁的业务可以尝试升级锁粒度，通过表锁来减少死锁概率

锁问题的监控

通过show full processlist 查看耗时⻓的语句，将有问题的线程kill掉，临时解决⼀些突发性问题

通过show engine innodb status命令查看事务锁占⽤情况

通过information_schema库中的innodb_trx、 innodb_locks、innodb_lock_waits表，查看事务、锁使⽤、锁阻塞情况
(MySQL 8已经移除，改为需要通过 performance_schema库的data_locks、data_ lock_waits、events_transactions_current表进⾏查看)

SQL语句性能优化

MySQL查询过程

客⼾端向MySQL服务器发送请求

服务器检查查询缓存，如果命中缓存，则⽴即返回在缓存中的结果，否则进⼊下⼀阶段

服务器进⾏SQL解析、预处理、再由优化器⽣成对应的执⾏计划

根据执⾏计划，调⽤存储引擎的API来执⾏查询

查询执⾏的最后⼀个阶段是将结果返回给客⼾端

创建⾼性能索引

索引原理

InnoDB基于B+树索引，在⾯对海量数据的场景可以以最少磁盘读取次数找出所需数据

表数据基于聚集索引的顺序存储在B+树的叶⼦节点上，可以实现范围匹配的快速定位

聚集索引和辅助索引

聚集索引

叶⼦节点存储了整⾏记录

当表定义含主键时，使⽤主键做为聚集索引；
否则使⽤表定义的唯⼀索引做为聚集索引；
否则存储引擎创建⼀个隐式的rowid做为聚集索引

辅助索引

叶⼦节点存储了主键值

查找时，通过辅助索引先找到对应的主键值，之后再使⽤主键值在聚集索引出找到⾏记录数据

index condition pushdown(ICP索引下推)

MySQL优化机制，将过滤条件处理从原来的核⼼服务层下推到存储引擎层处理

⽐如SQL语句为：WHERE AGE=10 AND NAME LIKE '%XXX%'语句，当前索引为（AGE， NAME），
由于NAME列含%开头导致⽆法参与索引匹配，所以只有AGE列可以在存储引擎中⽤于索引匹配，
在该功能关闭时，核⼼服务层从存储引擎拿到处理后的记录集后使⽤LIKE ‘% XXX%’进⾏过滤处理，
在该功能开启后，直接由存储引擎进⾏LIKE ‘%XXX%’过滤处理

multi-range read optimization(MRR多范围读取优化)

MySQL优化机制，对于辅助索引处理，获取的索引结果是按辅助索引KEY排序的，
⽽VALUE的数据主键则是⽆序的，此时基于当前结果在聚集索引中获取记录信息
则会出现⼤量随机IO读取，该功能是在辅助索引处理后，先对结果基于主键顺序排序，
再在聚集索引中获取记录信息，则间接将随机IO读取转变为跳跃顺序IO读取

Batched key access(BKA批量索引访问)

MySQL优化机制，对表的JOIN连接进⾏优化，将外层表数据批量读取到Join Buffer，
之后根据内层表的索引进⾏排序，使⽤批量排序后的结果再与内层表连接处理，
除了可以将随机IO读取转换为顺序IO读取外，也缩短了外层表遍历的次数

慢SQL语句优化思路

开启慢查询⽇志，设置慢查询超时时间，获取慢SQL语句

通过explain查看执⾏计划，对慢SQL语句分析

创建索引并调整语句，再查看执⾏计划，对⽐调优结果

索引使⽤的原则

表⼀定要有主键，显式定义主键且采⽤与业务关的列以避免修改

关于合理添加索引，有⼀个通常的法则，对经常被查询的列、经常⽤于表连接的列、经常排序分组的列，需要创建索引

选择选择性很⾼的列来做为索引

对于组合索引，⼀般把选择性⾼的列放在前⾯，通常最多组合索引段数不要超过5个

合理利⽤覆盖索引，避免使⽤select *

使⽤explain判断SQL语句是否会合理使⽤索引，避免出现file sort、using temporary的情况

单张表的索引数量建议控制在5个以内

不建议在频繁更新的字段上创建索引

避免在where的索引列上使⽤表达式或函数，导致索引⽆效

对于Join连接，需要确保被连接字段类型相同且建⽴索引

隐式类型转换会导致索性⽆效，应该避免

数据库分库分表及中间件介绍

关系数据库的架构演变

数据库读写分离

将数据库分为主从库，主库⽤于写数据，多个从库⽤于读数据

让主库负责数据更新和实时数据查询，从库负责⾮实时数据查询，
同时多个从库之间使⽤负载均衡减轻每个从库的查询压⼒

分库分表

垂直分库

垂直分库与业务架构设计有密切的联系，从业务领域对系统进⾏架构优化，
分成多个⼦业务系统，各个⼦业务系统偶合较低，
⼦业务系统间以接⼝⽅式进⾏数据通信和数据交换

⽔平分库与⽔平分表

⽔平分表是将原本在单库中的单个业务表拆分为⼏个逻辑相关的业务⼦表，
不同的业务⼦表各⾃负责不同区域的数据，对外形成⼀个整体，
让每个⼦表的数据量控制在⼀定范围内，保证SQL的性能

如果数据库因为表太多⽽造成海量数据，并且项⽬的各项业务逻辑划分清晰、耦合度底，
那么实施垂直切分是⾸选。如果数据库中的表并不多，但单表的数据量很⼤且数据热度很⾼，
这种情况应该选择⽔平切分。

分库分表带来的影响

分布式事务的问题

对于性能要求很⾼，但对⼀致性要求不⾼的系统，只要在允许的时间段内达到最终⼀致性即可，也可采⽤事务补偿的⽅式

解决⽅案

使⽤分布式事务中间件

尽量业务侧避免跨库操作

跨库join的问题

解决⽅案

全局表： 冗余基础数据，在所有库都有备份

字段冗余：把需要join的字段冗余在各个表中

数据组装： 在业务侧分两次查询，根据第⼀次查询结果找出关联数据id，
再根据id发起第⼆次查询得到关联数据

ER分⽚： 根据数据库设计的ER图，将表之间关联的表记录存放在⼀个分⽚上，
避免了跨分⽚库的join

结果集合并、排序问题

当排序字段就是分⽚字段时，通过分⽚规则就⽐较容易定位到指定分⽚

当排序字段⾮分⽚字段，则需要先在不同分⽚节点中将数据排序后返回，
然后将不同分⽚结果的结果集进⾏汇总和再次排序，最终返回给⽤⼾

MyCat中间件详解

简介

做为数据库中间件，对应⽤层透明

⽀持数据库分⽚需求

⽀持读写分离需求

MyCat核⼼概念

逻辑库

对应⽤层透明，将整个集群抽象为逻辑库

逻辑表

对应逻辑库中的逻辑数据表，逻辑表可以是数据切分后分布在⼀个或多个分⽚库中，也可以不做切分

分⽚表

指原有的拥有很多数据、需要切分到多个数据库的表

⾮分⽚表

数据库中某些表不⼤时，可以不⽤切分的表

ER表

基于E-R关系的数据分⽚策略，将有关联的⽗表和⼦表存放在相同分⽚，避免出现跨库Join

全局表

将⼀些规模⽐较⼩、变动不频繁的表冗余存储到所有分⽚节点中，避免跨库join

分⽚节点

数据切分后，⼀个⼤表分到不同的分⽚数据库上⾯，每个表分⽚所在的数据库就是分⽚节点

分⽚主机

分⽚节点所在的机器就是分⽚主机，分⽚主机可以有多台，并可实现读写分离、负载均衡

分⽚规则

数据划分到不同分⽚节的规则即为分⽚规则

全局序列号

数据切分后，原有的关系数据库中的主键约束在分布式条件下⽆法使⽤，
引⼊外部机制保证数据唯⼀性标识，这种保证全局性的数据唯⼀标识的机制就是全局序列号

配置⽂件

service.xml

配置MyCat⽤⼾名和密码使⽤的逻辑数据库等

schema.xml

主要配置数据库的信息，例如逻辑数据库、逻辑表、逻辑表对应的数据节点、
数据节点对应的真实数据库及数据主机、数据主机对应的真实服务器主机等

rule.xml

配置各种逻辑表的分⽚路由策略

Mysql服务器全面优化

硬件层⾯优化

使⽤固态硬盘

对于机械硬盘，使⽤RAID卡并配备Cache及BBU

对于RAID卡，设置写策略为Write Back，同时需要BBU备份电源保证断电后，在RAID卡缓冲上的数据不会⽴即丢失

需要在BIOS层⾯将NUMA关闭，避免该内存分配策略在某个CPU节点内存不⾜时出现swap产⽣

Linux操作系统层⾯优化

关闭SELinux和防⽕墙

将IO调度模式修改为deadline或noop模式，但决对不要使⽤cfq模式

设置 vm.swappiness=1，避免还有内存时就出现 swap

⽂件系统推荐选择 xfs

MySQL配置参数优化

max_ocnnections

设置客⼾端最⼤连接数

query_cache_type=0

关闭查询缓存

innodb_buffer_pool_size

设置缓冲池⼤⼩，通常设置总内存的50%到80%

innodb_io_capacity与innodb_io_capacity_max

设定磁盘IO处理能⼒，存储引擎会基于该值评估⼀次性处理脏⻚回收个数、
以及⼀次性进⾏插⼊缓冲合并个数

innodb_log_file_size

设置REDO⽇志⼤⼩，太⼩造成切换⽇志⽂件频繁，太⼤会造成崩溃恢复时间太久，建议为2G

innodb_flush_method

决定数据和⽇志刷新到磁盘的⽅式，如果有固定硬盘、RAID卡、断电保护、
采⽤双写机制的时候，最常⽤的值是o_direct

innodb_max_dirty_pages_pct

设置50，使得在缓冲池中脏⻚占50%时就触发检查点进⾏脏⻚刷新

binlog_format

建议将binlog⽇志格式设置为row模式，确保数据安全

innodb_flush_log_at_trx_commit

对数据⼀致性要求较⾼场景，建议将该值设置为 1，确保每次事务提交都将redo log缓冲刷到磁盘

sync_binlog=N

每写⼊N条binlog就刷到磁盘

MySQL设计规范

库表设计规范

建库原则是同⼀类业务的表放在⼀个库，不同业务的表尽量避免共⽤同⼀库，尽量避免跨库关联

每张表必须强制有主键，推荐使⽤⾃增列为主键

字符集统⼀使⽤utf8mb4，以满⾜中⽂、emoji 表情需求

⼩数字段推荐使⽤decimal类型，特别涉及⾦融必须使⽤decimal

尽量避免使⽤text/blob来存储⼤段⽂本、⼆进制数图、图⽚、⽂件等内容，应该将这些内存保存成本地⽂件，
在数据库中只保存位置引⽤信息

尽量不要使⽤外键，建议在应⽤层实现外键逻辑。外键在⾼并发场景影响性能，容易产⽣死锁

字段尽量定义为NOT NULL并加上默认值。有效的NULL判断是⽤IS NULL和IS NOT NULL，这两种情况都会造成索引⽆效

降低业务逻辑与数据存储的偶合度，数据库以存储数据为主，尽量减少对存储过程、触发器、函数、视图等功能的使⽤，
这些对移植性、可扩展性较差

短期内业务达不到很⼤量级，禁⽌使⽤分区表。分区表主要⽤作归档管理，对提升性能作⽤不⼤

对读压⼒较⼤，且⼀致性要求较低的场景，建议使⽤主从复制，通过从库分解读压⼒

索引设计规范

单表索引数建议不超过5个，复合索引字段数建议不超过5个

选择业务中SQL过滤最多、唯⼀性较⾼的列建⽴索引

建议复合索引时，区分度较⾼的列放在最左侧

对更新频繁、区分度不⾼的列上禁⽌建⽴索引

合理利⽤覆盖索引来降低回表的IO开销

SQL编写规范

禁⽌使⽤INSERT INTO t VALUES(...)，必须显式指定列属性，避免表结构变更

注意SQL索引常⻅失效情况

隐式类型转换

对索引列使⽤公式函数

模糊查询使⽤%开头

使⽤反⽅向查询（not 、!=、not in等）

避免使⽤select *

避免使⽤⼤事务，建议拆为⼩事务

事务要及时提交，避免不必要的锁等待

少⽤多表join、禁⽌⼤表join，表连接必须让⼩表做驱动表，join列必须字符集⼀致且建⽴索引

避免多层⼦查询嵌套

业务上线前做必要SQL审核，⽇常定期检查慢查询⽇志并做优化

Mysql性能监控

安装Lepus进⾏监控

MySQL主从复制详解

主从复制的概念和⽤途

概念

主库将变更写⼊本地binlog，并复制到从库

从库将主库发来的binlog存储为relay-log，重放 relay-log数据到本地实现主从复制同步

⽤途

从服务器作为主服务器的实时数据备份

主从服务器实时读写分离，从服务器实现负载均衡

将多个从服务器根据业务需求拆分给不同⽬标⽤⼾使⽤

主从复制的原理及过程描述

主从复制涉及3个线程

log dump thread

主库创建binlog dump线程，⽤于给从库发送 binlog内容

i/o thread

从库创建i/o线程，⽤来连接主库，并请求从指定 binlog⽇志⽂件的指定位置之后的⽇志内容，
接收到⽇志内容后更新到本机的relay-log中

sql thread

从库的sql 线程实时检测relay log中新增加的内容，将relay log内容解析成具体的SQL语句操作，
并在从节点上按解析SQL语句的位置顺序执⾏和应⽤这些SQL

主从复制的部署架构

⼀主⼀从或⼀主多从

多级主从（级联同步）

双主（仅有⼀个是活动的）

多主⼀从（也称多源复制）

异步复制

MySQL默认采⽤异步复制⽅式，该⽅式主服务将⼆进制⽇志写⼊binlog⽂件后就返回客⼾端结果，
不会考虑⼆进制⽇志是否完整传输到从服务器上

搭建过程

配置⽂件

主服务器

[mysqld]
server-id=100 --必须唯⼀
log-bin=on -- 开启logbin
binlog_format=row --设置为⾏模式，确保复制数据的⼀致性

从服务器

[mysqld]
server-id=101
binlog_format=row
master_info_repository=TABLE --将复制元数据信息存储到表中，确保不丢失
relay_log_info_repository=TABLE --将复制中继⽇志存储到表中，确保不丢失
relay_log_recovery=1 --中继⽇志损坏后可以⾃修复
log-bin=on --不是必须项，⽅便后续升级为主服务器
log_slave_updates=on --将中继⽇志事件也同步到本地binlog，不是必须项，⽅便后续升级为主服务器

主服务器增加账⼾授权允许从服务器复制

GRANT REPLICATION SLAVE ON *.* TO ' mysync'@'%' IDENTIFIED BY 'q123456';
flush privileges;

主从数据的初始同步

mysqldump -uroot -p --single-transaction -- master-data=2 --databases mldn > /tmp/ mldn.dmp
1、将主服务器的数据库mldn进⾏备份
2、single-transaction -- 备份InnoDB表时基于完整事务，确保数据的事务⼀致性
3、master-data=2 -- 在备份⽂件中记录binlog的⽂件名及位置信息

mysql -uroot -p < /tmp/mldn.dmp
在从服务器使⽤上述备份⽂件进⾏恢复

从服务器执⾏主从复制命令

change master to
master_host='x.x.x.x'
master_user='mysync',
master_password='q123456',
master_port=3306,
master_log_file='xxxx'
master_log_pos=xxx
-- 解释：
master_log_file、master_log_pos为从服务器基于mysqldump的备份恢复后，后续从哪个binlog 位置开始与主服务器实时同步数据，
该信息已经通过--master-data=2选项记录到备份⽂件中，可以从该⽂件中获取

从服务器启动同步

start slave

半同步复制

与异步复制的差异为，在确保收到任何⼀个从库已经成功写⼊中继⽇志后，才会给客⼾端返回处理完成，确保⾄少有⼀个从服务器与主服务器⼀致

搭建过程

先按异步复制步骤完成异步复制搭建

安装插件

主服务器

install plugin rpl_semi_sync_master soname ' semisync_master.so';

从服务器

install plugin rpl_semi_sync_slave soname ' semisync_slave.so';

配置半同步

主服务器

set global rpl_semi_sync_master_enabled=1;
set global rpl_semi_sync_master_timeout= 30000;

从服务器

set global rpl_semi_sync_slave_enabled=1;

从服务器激活半同步

stop slave
start slave

状态查看

show status like '%semi%'

主服务器

Rpl_semi_sync_master_clients --有多少客⼾端连接
Rpl_semi_sync_master_status --是否启⽤半同步

从服务器

Rpl_semi_sync_slave_status --是否启⽤半同步

GTID复制

⽤来代替传统复制的⽅法，与普通复制模式最⼤不同就是不需要指定⼆进制⽂件名和位置

⼯作原理

主节点更新数据时，会在事务前产⽣GTID，⼀同记录到binlog⽇志中

从节点的I/O线程将变更的binlog写⼊到本地的 relay-log中

从节点的sql线程从relay-log中获取GTID，然后对⽐本地binlog是否有记录，
如果有记录，说明该GTID的事务已经执⾏，从节点会忽略。
如果没有记录，从节点就会从relay-log中执⾏该GTID 事务，并记录到binlog

GTID的限制

不⽀持⾮事务引擎

不⽀持crate table ... select 语句复制

不允许在⼀个SQL同时更新⼀个事务引擎和⾮事务引擎的表

在⼀个复制组中，必须统⼀开启GTID或关闭 GTID

开启GTID后，不再使⽤原传统复制⽅式

对于create temporary table和drop temporary table语句不⽀持

不⽀持sql_slave_skip_counter

搭建过程

配置⽂件

主服务器

[mysqld]
server-id=100 --必须唯一
log-bin=on --开启logbin
binlog_format=row --设置为行模式，确保复制数据的一致性
enforce_gtid_consistency=ON --确保事务不要违反GTID的一致性
gtid_mode=ON --开启GTID

从服务器

[mysqld]
server-id=101 --必须唯一
log-bin=on --开启logbin
binlog_format=row --设置为行模式，确保复制数据的一致性
enforce_gtid_consistency=ON --确保事务不要违反GTID的一致性
gtid_mode=ON --开启GTID

主服务器增加账⼾授权允许从服务器复制

GRANT REPLICATION SLAVE ON *.* TO ' mysync'@'%' IDENTIFIED BY 'q123456';
flush privileges;

主从数据的初始同步

mysqldump -uroot -p --single-transaction -- master-data=2 --all-databases > /tmp/all.sql
1、将主服务器的所有数据库进⾏备份
2、single-transaction -- 备份InnoDB表时基于完整事务，确保数据的事务⼀致性
3、master-data=2 -- 在备份⽂件中记录binlog的⽂件名及位置信息

mysql -uroot -p < /tmp/all.sql
在从服务器使⽤上述备份⽂件进⾏恢复

从服务器执⾏主从复制命令

change master to
master_host='x.x.x.x'
master_user='mysync',
master_password='q123456',
master_port=3306,
master_auto_position=1; --让从服务器⾃动查找需要同步的binlog位置

从服务器启动同步

start slave

多源复制

多源复制是指⼀个slave可以指向多个master，多⽤于汇总不同数据库数据进⾏统⼀分析

搭建过程

配置文件

主服务器A

[mysqld]
server-id=100 --必须唯一
log-bin=on --开启logbin
binlog_format=row --设置为行模式，确保复制数据的一致性
enforce_gtid_consistency=ON --确保事务不要违反GTID的一致性
gtid_mode=ON --开启GTID

主服务器B

[mysqld]
server-id=101 --必须唯一
log-bin=on --开启logbin
binlog_format=row --设置为行模式，确保复制数据的一致性
enforce_gtid_consistency=ON --确保事务不要违反GTID的一致性
gtid_mode=ON --开启GTID

从服务器

[mysqld]
server-id=102 --必须唯一
log-bin=on --开启logbin
binlog_format=row --设置为行模式，确保复制数据的一致性
enforce_gtid_consistency=ON --确保事务不要违反GTID的一致性
gtid_mode=ON --开启GTID

主服务器增加账⼾授权允许从服务器复制

GRANT REPLICATION SLAVE ON *.* TO ' mysync'@'%' IDENTIFIED BY 'q123456';
flush privileges;

主从数据的初始同步

主服务器A

mysqldump -uroot -p --single-transaction -- master-data=2 data_a > /tmp/data_a.sql
1、将主服务器A的数据库进⾏备份
2、single-transaction -- 备份InnoDB表时基于完整事务，确保数据的事务⼀致性
3、master-data=2 -- 在备份⽂件中记录binlog的⽂件名及位置信息

主服务器B

mysqldump -uroot -p --single-transaction -- master-data=2 data_b > /tmp/data_b.sql
1、将主服务器B的数据库进⾏备份
2、single-transaction -- 备份InnoDB表时基于完整事务，确保数据的事务⼀致性
3、master-data=2 -- 在备份⽂件中记录binlog的⽂件名及位置信息

从服务器

mysql -uroot -p data_a < /tmp/data_a.sql
mysql -uroot -p data_b < /tmp/data_b.sql

mysql -uroot -p < /tmp/all.sql
在从服务器使⽤上述备份⽂件进⾏恢复

从服务器执⾏主从复制命令

change master to
master_host='x.x.x.x'
master_user='mysync',
master_password='q123456',
master_port=3306,
master_auto_position=1
for channel 'm1';
--建⽴与主服务器A的复制通道

change master to
master_host='y.y.y.y'
master_user='mysync',
master_password='q123456',
master_port=3306,
master_auto_position=1
for channel 'm2';
--建⽴与主服务器B的复制通道

从服务器启动同步

start slave for channel 'm1';

start slave for channel 'm2';

主从延迟解决⽅案

主从延迟排查⽅法

通过在从服务器上使⽤show slave status查看 seconds_behind_master参数值来粗略判断

通过在主从服务器使⽤第三⽅⼯具pt-heartbeat 来精确检测

解决⽅法

建议从库的硬件配置要和主库⼀样，强烈建议使⽤固态硬盘，并且修改innodb_flush_method为 O_DIRECT

适当增⼤从库的innodb_buffer_pool_size，减少 IO压⼒

从库的sync_binlog、innodb_flushlog_at_trx_ commit适当放低要求，不需要频繁的刷新硬盘

升级MySQL5.7版本，使⽤并⾏复制

并⾏复制

MySQL 5.7并⾏复制Multi-Threaded Slave原理

通过对事务进⾏分组，如果事务能同时提交成功，那么它们就不会共享任何锁，⼀个组提交的事务都可以并⾏回放，
因为这些事务都已进⼊事务的prepare阶段，可以在slave上并⾏执⾏

从服务器并⾏复制配置

slave_parallel_type='LOGICAL_CLOCK' --启⽤基于组提交的并发复制
slave_parallel_workers = xx --设置并发的数量
master_info_repository = TABLE --并⾏复制对主服务信息更新频繁，这⾥设置信息记录存储到表中，
⽽⾮存储到⽂件中，提升性能

PXC高可用集群解决方案

概述

PXC基于多主架构，任何⼀个节点都可以进⾏读写，PXC是基于引擎层的同步复制

PXC实现数据强⼀致性，⼀个节点提交事务时，必须等待其它节点全部确认事务通过或事务失败才给客⼾端返回结果

与传统的基于主从复制模式的集群架构相比 PXC 最突出特点就是解决了诟病已久的数据复制延迟问题，
基本上可以达到实时同步

而且节点与节点之间，他们相互的关系是对等的

实现原理

各节点基于写集进⾏同步，写集包括了⾏的主键（写集的KEY）和Binlog（写集的DATA），
主键⽤于各节点检测是否与本地事务冲突， Binlog⽤于各节点将同步数据应⽤到本地

流程

客⼾端发起⼀个事务到其中⼀个节点，该节点在本地处理数据更新，
当客⼾端进⾏提交时，该节点将⽣成的写集⼴播到其它所有节点

其它节点尝试将同步数据合并到本地，没有冲突后，执⾏应⽤和事务提交动作，并返回OK

接收客⼾端的节点收到其它所有节点都成功同步数据后，给客⼾端返回OK

PXC优点

数据同步复制，事务要么在所有节点提交或不提交，⼏乎⽆延迟

多主复制，多个可同时读写节点

新加⼊节点可⾃动部署，数据一致性，不再是异步复制

完全兼容MySQL，实现数据库⾼可⽤和数据强⼀⼀致

在从服务器上并行应用事件，真正意义上的并行复制

故障切换：因为支持多点写入，所以在出现数据库故障时可以很容易的进行故障切换

自动节点克隆：在新增节点或停机维护时，增量数据或基础数据不需要人工手动备份提供，
galera cluster会自动拉取在线节点数据，集群最终会变为一致

PXC局限性

只⽀持InnoDB存储引擎表

多节点并发写时容易锁冲突、死锁等问题

强⼀致导致性能会降低

新加⼊节点的全数据同步开销⼤

所有节点都能写⼊，存在写扩⼤问题

搭建过程

所有集群节点安装 percona xtraDB cluster

注意PXC包含了MySQL服务器，所以需要把当前已存在的MySQL停⽤

所有集群节点进⾏配置

mysqld]
datadir=/var/lib/mysql
user=mysql
# Path to Galera library
wsrep_provider=/usr/lib/libgalera_smm.so
# Cluster connection URL contains the IPs of node#1, node#2 and node#3
wsrep_cluster_address=gcomm://192.168.70. 61,192.168.70.62,192.168.70.63
# In order for Galera to work correctly binlog format should be ROW
binlog_format=ROW
# MyISAM storage engine has only experimental support
default_storage_engine=InnoDB
# This InnoDB autoincrement locking mode is a requirement for Galera
innodb_autoinc_lock_mode=2
# Node #1 address
wsrep_node_address=192.168.70.61
# SST method
wsrep_sst_method=xtrabackup-v2
# SST password
wsrep_sst_auth="sstuser:s3cretPass"
# Cluster name
wsrep_cluster_name=my_ubuntu_cluster

三个集群节点只需要wsrep_node_address修改为个⾃IP地址

在第⼀个节点上配置⽤于SST同步的帐号授权

mysql@pxc1> CREATE USER 'sstuser'@' localhost' IDENTIFIED BY 's3cretPass';
mysql@pxc1> GRANT PROCESS, RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'sstuser'@'localhost';
mysql@pxc1> FLUSH PRIVILEGES;

其它节点连上第⼀个节点后会⾃动同步授权

启动MySQL

第⼀个配置了帐号授权的节点启动：mysql bootstrap-pxc

其它节点启动： mysql start

查看集群状态

mysql> show status like 'wsrep%';

PXC集群适⽤场景

适应于对数据⼀致性和完整性要求特别的场景，如交易场景

PXC集群维护总结

尽量单点写⼊操作，不要多节点同时写，并发⼤的场景同时写性能会降低

整个集群最好为3个节点，不要超过8个

执⾏⼤表的改表操作会导致整个集群⼀段时间卡死，对⼤表改表操作请使⽤pt-online-schema- change来处理

对于新加节点尽量不要使⽤SST全量备份，可以将新加节点先通过主从进⾏同步，之后再转⼊集群

PXC 常用端口

3306：数据库对外服务的端口号

4444：请求SST的端口

4567：组成员之间进行沟通的一个端口号

4568：用于传输IST

pxc集群的启动和关闭

状态机变化阶段

OPEN：节点启动成功，尝试连接到集群，如果失败则根据配置退出或创建新的集群

PRIMARY：节点处于集群PC中，尝试从集群中选取donor进行数据同步

JOINER：节点处于等待接收/接收数据文件状态，数据传输完成后在本地加载数据

JOINED：节点完成数据同步工作，尝试保持和集群进度一致

SYNCED：节点正常提供服务：数据的读写，集群数据的同步，新加入节点的sst请求

DONOR(贡献数据者)：节点处于为新节点准备或传输集群全量数据状态，对客户端不可用

状态机变化因素

新节点加入集群

节点故障恢复

节点同步失效

传输SST的几种方法

mysqldump

xtrbackup

rsync

基于MHA实现MYSQL自动故障转移集群

概述

基于MySQL主从复制集群，监控主服务器是否宕机，在主服务器宕机后，
从多个从服务器中挑选出最新数据的从服务器做为最新主服务器，
同时，将最新主服务器与其它从服务器的中继⽇志差异合并到其它从服务器，
将其他从服务器主从复制配置切换到最新主服务器

MHA原理

如果服务宕机的主库仍然可以通过SSH连接，那么MHA把宕机的主库上的所有⼆进制⽇志保存起来

根据其他从库同步到主库的binlog位置点识别由更新的从库作为备⽤主库

应⽤差异的中继⽇志到其他从库，使得其他所有从库与备⽤主库的数据保持⼀致

应⽤从原主库上保存的⼆进制⽇志，将其恢复到所有的数据库节点上（如果有的话）

将备⽤主库正式提升为新的master主库

使其他的从库重新连接到新的主库保持主从复制状态

注意点

MHA各从库的数据同步依赖于中继⽇志，默认情况下从库的SQL线程执⾏完后⾃动删除中继⽇志，
因此需要配置禁⽤中继⽇志的⾃动删除功能

优点

主⾃动监控和⾃动故障转移，并且故障转移的速度会很快

主Crash不会导致主从数据不⼀致

MHA在进⾏故障转移时更不易产⽣数据丢失

不需要添加额外的服务器，同⼀个监控节点可以监控多个集群

MHA不需要对当前MySQL集群环境做出重⼤更改，只要是MySQL主从复制⽀持的存储引擎， MHA都⽀持

缺点

需要编写脚本或利⽤第三⽅⼯具来实现VIP配置

原⾃动切换脚本太简单，需要⾃⾏完善

需要开启LINUX基于SSH免登录认证配置，存在安全隐患

没有提供从库的读负载均衡功能

搭建过程

配置所有机器互信，SSH免密码登录

搭建主从集群环境

在所有主从服务器上安装MHA-node数据节点

在MHA管理服务器上安装MHA-Manager

在MHA管理服务器上创建MHA⽬录，并编写 mha.conf配置⽂件

在MHA管理服务器上创建master_ip_failover和 master_ip_online_change脚本，⽤于VIP漂移

master_ip_failover --⾃动failover时执⾏

master_ip_online_change --⼿动切换时执⾏

在MHA管理服务器上利⽤MHA⼯具检测各服务器的SSH免密码登录是否正常

在MHA管理服务器上利⽤MHA⼯具检测主从集群环境是否正常

在MHA管理服务器上启动masterha_manager

Keepalive+双主复制高可用架构

介绍

使⽤两台MySQL节点互为主备，使得两个节点都可以读写，任意⼀节点写⼊都会同步到另⼀个节点

在双主备的基础上配合keepalived，使得两个节点只有⼀个节点可以拿到虚拟IP，
拿到虚拟IP的节点做为活动节点统⼀对外服务，当对外服务的节点出现故障后，
在备份节点的keepalived检测到主节点故障，⾃动将虚拟IP切换到备份节点，
使得备份节点做为主节点统⼀对外服务

搭建过程

配置两个节点互为主从服务器

在两个节点分别安装keepalived

在两个节点分别编写监控mysql服务异常后强杀 keepalived进程的脚本

在两个节点编写keepalived.conf配置

两个节点分别启动keepalived

适⽤场景及注意项

该⽅案主要在中⼩型公司使⽤，如果主从服务器多于两台时，建议切换到MHA

在云平台环境下，keepalived只能设置单播⽅式，通常不⽀持浮动IP，往往需要⽤⼾提请⼯单来开通⾼可⽤虚拟IP

Mysql Group replication(MGR)

概述

MySQL官⽅推出的⼀种基于Paxos协议的分布式数据库集群

特点(优点)

⽀持多节点并发地执⾏事务

⾃动事务冲突检测

数据强⼀致性保障

容错性⾼

基本原理

传统异步主从复制

传统异步主从复制，在主服务器写完binlog后，dump binlog给从服务器发送日志，
主服务器不关⼼从服务器是否成功收接binlog写⼊relay-log就直接提交事务，
在主服务器宕机时，从服务器可能会有⼀部分同步数据没接收到⽽丢失

半同步主从复制

半同步主从复制，在主服务器写完 binlog后，dump binlog给从服务器，
此时需要等待从服务器响应，从服务器成功接收binlog并写⼊relay-log会给主服务器响应接收成功，
在主服务器收到任⼀从服务器的响应后才提交事务

MGR组复制

⼀个复制组由若⼲个节点组成，组内各个节点维护各⾃的数据库副本，
依靠分布式⼀致性协议实现了分布式下数据的最终⼀致性

当客⼾端发起⼀个更新事务时，接收节点先在本地执⾏数据更新，之后将写⼊集⼴播给其它节点，
其它节点全部检测⽆事务冲突后，本地节点则可以继续写binlog、进⾏事务提交，
其它节点完成写⼊relay-log、对relay-log 重放应⽤、写⼊binlog、进⾏事务提交

MGR服务模式

⽀持single-primary和multi-primary两种模式，默认为单主模式

单主模式

此模式下只有⼀个节点可进⾏写⼊，其它节点都为只读

主节点宕机时，在满⾜⼤多数节点存活的情况下，内部发起选举，选出下⼀个可⽤的主节点

多主模式

此模式所有节点都提供读写服务，没有主从之分，也没有选主流程

多主模式的限制

不⽀持SERIALIZABLE串⾏隔离级别

不能完全⽀持级联外键约束

不⽀持在不同节点对同⼀个数据库对象并发执⾏ DDL

MGR的注意事项

在MGR集群中，只⽀持InnoDB存储引擎表，并且该表必须显式定义主键

MGR组通信引擎⽬前仅⽀持IPV4

官⽅建议在同⼀节点进⾏修改操作，多节点修改容易出现⼤量回滚，导致性能降低

在多主模式下可能导致死锁

集群⽬前最多⽀持9个节点

整个集群写⼊吞吐量由最弱的节点限制

不提供VIP机制，需要结合第三⽅软件

搭建过程

所有节点进⾏my.cnf配置

[mysqld]
server-id=xx
log-bin=on
log_slave_updates=on
binlog_format=row
binlog_checksum=NONE
gtid_mode=on
enforce_gtid_consistency=on
master_info_repository=TABLE
relay_log_info_repository=TABLE
transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name='aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa'
loose-group_replication_start_on_boot=off
loose-group_replication_bootstrap_group=off
losse-group_replication_local_address='10.10.75.100:33061'
loose-group_replication_group_seeds='10.10.75.100:33061,10.10.75.101:33061,10.10.75.102:33061'
loose-group_replication_single_primary_mode=false
loose-group_replication_enforce_update_everywhere_checks=true

其他节点只需要修改server-id、losse-group_ replication_local_address的差异

所有节点创建从服务器复制授权帐号

set sql_log_bin=0;
grant replication slave on *.* to rpl_user@'%' identified by '1234@pass';
flush privileges;
set sql_log_bin=1;

所有节点都需要安装组复制插件

install plugin group_replication SONAME ' group_replication.so';

所有节点创建同步认证信息

change master to master_user='rpl_user', master_password='1234@pass' for channel 'group_replication_recovery';

启动MGR集群中第⼀个节点来引导

set global group_replication_bootstrap_group= on
start group_replication;
set global group_replication_bootstrap_group= o

分别启动MGR集群中其它节点

start group_replication;

查看集群状态

performance_schema.replication_group_ members --查看组成员状态
performance_schema.replication_group_ member_stats --查看同步情况、复制状态
performance_schema.replication_connection_ status --查看组复制信息

MGR的主节点故障⽆感知切换

MGR内部没有提供⼀种机制来实现主节点故障切换对应应⽤⽆感知，需要第三⽅中间件实现

MGR+ProxySQL实现

部署MGR集群为多主模式

配置PorxySQL选定⼀个节点为写⼊节点，其它节点为读节点

使⽤PorxySQL调度检查脚本定期检查写⼊节点是否宕机，检测到宕机后，选择⼀个读节点做为新的写⼊节点