MySQL性能管理及架构设计 --- 理论篇

2023-11-11 来源：骅佗教育

MySQL性能管理及架构设计

一丶IO,内存,吞吐量理解

IO 是指设备与设备之间操作次数,比如mysql与php互插内存是程序运行都在里面执行吞吐量是单位时间内处理的请求数量

二丶究竟是myisa还是innodb ?

业界争论不休的情况下,低版本默认引擎是myisam,高版本mysql默认引擎是innodb,也是innodb高版本一个梗吧,尽量使用innodb引擎,不要混合使用myisam这两种引擎,因为在事物中,如果回滚的话 ,表连接 myisam是没有办法回滚的,innodb支持并发比myisam要好,支持热备份,灾难恢复也比myisam好,大数据还是使用innodb要好!

MySQL性能管理及架构设计

一丶IO,内存,吞吐量理解

IO 是指设备与设备之间操作次数,比如mysql与php互插内存是程序运行都在里面执行吞吐量是单位时间内处理的请求数量

二丶在myisam和innodb 业界争论不休的情况下,低版本默认引擎是myisam,高版本mysql默认引擎是innodb,也是innodb高版本一个梗吧,尽量使用innodb引擎,不要混合使用myisam这两种引擎,因为在事物中,如果回滚的话 ,表连接 myisam是没有办法回滚的,innodb支持并发比myisam要好,支持热备份,灾难恢复也比myisam好,大数据还是使用innodb要好!

MySQL性能管理及架构设计 --- 理论篇

标签：事物 list fan mooc class mysq php 3.5 运行

小编还为您整理了以下内容，可能对您也有帮助：

MySQL性能管理及架构设计（一）：什么影响了数据库查询速度、什么影响了MySQL性能

Tips：最好不要在主库上数据库备份，大型活动前取消这样的计划。

效率低下的sql：超高的QPS与TPS。

大量的并发：数据连接数被占满（max_connection默认100，一般把连接数设置得大一些）。

并发量:同一时刻数据库服务器处理的请求数量

超高的CPU使用率：CPU资源耗尽出现宕机。

磁盘IO：磁盘IO性能突然下降、大量消耗磁盘性能的计划任务。解决：更快磁盘设备、调整计划任务、做好磁盘维护。

1.3 网卡流量：如何避免无法连接数据库的情况

减少从服务器的数量（从服务器会从主服务器复制日志）

进行分级缓存（避免前端大量缓存失效）

避免使用select *?进行查询

分离业务网络和服务器网络

1.4 大表带来的问题（重要）

1.4.1 大表的特点

记录行数巨大，单表超千万

表数据文件巨大，超过10个G

1.4.2 大表的危害

1.慢查询：很难在短时间内过滤出需要的数据

查询字区分度低 -> 要在大数据量的表中筛选出来其中一部分数据会产生大量的磁盘io?-> 降低磁盘效率

2.对DDL影响：

建立索引需要很长时间：

MySQL -v<5.5?建立索引会锁表

MySQL -v>=5.5?建立索引会造成主从延迟（mysql建立索引，先在组上执行，再在库上执行）

修改表结构需要长时间的锁表：会造成长时间的主从延迟(‘480秒延迟‘)

1.4.3 如何处理数据库上的大表

分库分表把一张大表分成多个小表

难点：

分表主键的选择

分表后跨分区数据的查询和统计

1.5 大事务带来的问题（重要）

1.5.1 什么是事务

1.5.2事务的ACID属性

1、原子性（atomicity)：全部成功，全部回滚失败。银行存取款。

2、一致性（consistent)：银行转账的总金额不变。

3、隔离性（isolation)：

????隔离性等级：

未提交读(READ UNCOMMITED)?脏读,两个事务之间互相可见；

已提交读(READ COMMITED)符合隔离性的基本概念,一个事务进行时，其它已提交的事物对于该事务是可见的，即可以获取其它事务提交的数据。

可重复读(REPEATABLE READ)?InnoDB的默认隔离等级。事务进行时，其它所有事务对其不可见，即多次执行读，得到的结果是一样的！

可串行化（SERIALIZABLE）在读取的每一行数据上都加锁，会造成大量的锁超时和锁征用，严格数据一致性且没有并发是可使用。

????查看系统的事务隔离级别：show variables like ‘%iso%‘;

????开启一个新事务：begin;

????提交一个事务：commit;

????修改事物的隔离级别：set session tx_isolation=‘read-committed‘;

4、持久性(DURABILITY)：从数据库的角度的持久性，磁盘损坏就不行了

redo log机制保证事务更新的一致性和持久性

1.5.3 大事务

运行时间长，操作数据比较多的事务；

风险：锁定数据太多，回滚时间长，执行时间长。

锁定太多数据，造成大量阻塞和锁超时；

回滚时所需时间比较长，且数据仍然会处于锁定；

如果执行时间长，将造成主从延迟，因为只有当主服务器全部执行完写入日志时，从服务器才会开始进行同步，造成延迟。

解决思路：

避免一次处理太多数据，可以分批次处理；

移出不必要的SELECT操作，保证事务中只有必要的写操作。

二、什么影响了MySQL性能（非常重要）

2.1 影响性能的几个方面

服务器硬件。

服务器系统（系统参数优化）。

存储引擎。?

MyISAM：不支持事务，表级锁。

InnoDB: 支持事务，支持行级锁，事务ACID。

数据库参数配置。

数据库结构设计和SQL语句。（重点优化）

2.2 MySQL体系结构

分三层：客户端->服务层->存储引擎

MySQL是插件式的存储引擎，其中存储引擎分很多种。只要实现符合mysql存储引擎的接口，可以开发自己的存储引擎!

所有跨存储引擎的功能都是在服务层实现的。

MySQL的存储引擎是针对表的，不是针对库的。也就是说在一个数据库中可以使用不同的存储引擎。但是不建议这样做。

2.3 InnoDB存储引擎

MySQL5.5及之后版本默认的存储引擎：InnoDB。

2.3.1 InnoDB使用表空间进行数据存储。

show variables like ‘innodb_file_per_table

如果innodb_file_per_table 为 ON 将建立的表空间，文件为tablename.ibd；

如果innodb_file_per_table 为 OFF 将数据存储到系统的共享表空间，文件为ibdataX（X为从1开始的整数）；

.frm?：是服务器层面产生的文件，类似服务器层的数据字典，记录表结构。

2.3.2 (MySQL5.5默认)系统表空间与(MySQL5.6及以后默认)表空间

1.1 系统表空间无法简单的收缩文件大小，造成空间浪费，并会产生大量的磁盘碎片。

1.2 表空间可以通过optimeze table?收缩系统文件，不需要重启服务器也不会影响对表的正常访问。

2.1 如果对多个表进行刷新时，实际上是顺序进行的，会产生IO瓶颈。

2.2 表空间可以同时向多个文件刷新数据。

强烈建立对Innodb 使用表空间，优化什么的更方便，可控。

2.3.3 系统表空间的表转移到表空间中的方法

使用mysqlmp 导出所有数据库数据（存储过程、触发器、计划任务一起都要导出）可以在从服务器上操作。

停止MYsql 服务器，修改参数（my.cnf加入innodb_file_per_table），并删除Inoodb相关文件（可以重建Data目录）。

重启MYSQL，并重建Innodb系统表空间。

重新导入数据。

或者?Alter table?同样可以的转移，但是无法回收系统表空间中占用的空间。

2.4 InnoDB存储引擎的特性

2.4.1 特性一：事务性存储引擎及两个特殊日志类型：Redo Log 和 Undo Log

Innodb?是一种事务性存储引擎。

完全支持事务的ACID特性。

支持事务所需要的两个特殊日志类型：Redo Log?和Undo Log

????Redo Log：实现事务的持久性(已提交的事务)。

????Undo Log：未提交的事务，于表空间，需要随机访问，可以存储在高性能io设备上。

Undo日志记录某数据被修改前的值，可以用来在事务失败时进行rollback；Redo日志记录某数据块被修改后的值，可以用来恢复未写入data file的已成功事务更新的数据。

2.4.2 特性二：支持行级锁

InnoDB支持行级锁。

行级锁可以最大程度地支持并发。

行级锁是由存储引擎层实现的。

2.5 什么是锁

2.5.1 锁

2.5.2 锁类型

2.5.3 锁的粒度

MySQL的事务支持不是绑定在MySQL服务器本身，而是与存储引擎相关

将table_name加表级锁命令：lock table table_name write;?写锁会阻塞其它用户对该表的‘读写’操作，直到写锁被释放：unlock tables；

锁的开销越大，粒度越小，并发度越高。

表级锁通常是在服务器层实现的。

行级锁是存储引擎层实现的。innodb的锁机制，服务器层是不知道的

2.5.4 阻塞和死锁

（1）阻塞是由于资源不足引起的排队等待现象。

（2）死锁是由于两个对象在拥有一份资源的情况下申请另一份资源，而另一份资源恰好又是这两对象正持有的，导致两对象无法完成操作，且所持资源无法释放。

2.6 如何选择正确的存储引擎

参考条件：

事务

备份(Innobd免费在线备份)

崩溃恢复

存储引擎的特有特性

总结:**Innodb好。

?注意:**尽量别使用混合存储引擎，比如回滚会出问题在线热备问题。

2.7 配置参数

2.7.1 内存配置相关参数

确定可以使用的内存上限。

内存的使用上限不能超过物理内存，否则容易造成内存溢出；（对于32位操作系统，MySQL只能试用3G以下的内存。）

确定MySQL的每个连接单独使用的内存。

sort_buffer_size #定义了每个线程排序缓存区的大小，MySQL在有查询、需要做排序操作时才会为每个缓冲区分配内存（直接分配该参数的全部内存）；

join_buffer_size #定义了每个线程所使用的连接缓冲区的大小，如果一个查询关联了多张表，MySQL会为每张表分配一个连接缓冲，导致一个查询产生了多个连接缓冲；

read_buffer_size #定义了当对一张MyISAM进行全表扫描时所分配读缓冲池大小，MySQL有查询需要时会为其分配内存，其必须是4k的倍数；

read_rnd_buffer_size #索引缓冲区大小，MySQL有查询需要时会为其分配内存，只会分配需要的大小。

注意：以上四个参数是为一个线程分配的，如果有100个连接，那么需要×100。

MySQL数据库实例：

①MySQL是单进程多线程（而oracle是多进程），也就是说MySQL实例在系统上表现就是一个服务进程，即进程；

②MySQL实例是线程和内存组成，实例才是真正用于操作数据库文件的；

一般情况下一个实例操作一个或多个数据库；集群情况下多个实例操作一个或多个数据库。

如何为缓存池分配内存：

Innodb_buffer_pool_size，定义了Innodb所使用缓存池的大小，对其性能十分重要，必须足够大，但是过大时，使得Innodb 关闭时候需要更多时间把脏页从缓冲池中刷新到磁盘中；

总内存-（每个线程所需要的内存*连接数）-系统保留内存

key_buffer_size，定义了MyISAM所使用的缓存池的大小，由于数据是依赖存储操作系统缓存的，所以要为操作系统预留更大的内存空间；

select?sum(index_length)?from?information_schema.talbes?where?engine=‘myisam‘

注意：即使开发使用的表全部是Innodb表，也要为MyISAM预留内存，因为MySQL系统使用的表仍然是MyISAM表。

max_connections?控制允许的最大连接数，一般2000更大。

不要使用外键约束保证数据的完整性。

2.8 性能优化顺序

从上到下：

MySQL性能管理及架构设计（一）：什么影响了数据库查询速度、什么影响了MySQL性能

标签：key操作系统约束mat运行内存配置inf不同的分区

如何设计或配置mysql,才能达到高效使用的目的

编译借助诸如Apach、Perl、PHP和Python等工具，构建一个MySQL应用时很容易的。然而确保它们运行快速，则需要一点洞察力。本文就是你需要知道的东西。

　　MySQL对于成为一个非常快速的数据库服务器有着当之无愧的名声，它也非常容易设置和使用。随着它作为网站后端数据库得声望日增，其效果在去年开始有明显提高。但是很多MySQL用户更多地知道如何创建一个数据库并编写对它的查询。就像成千上万的人通过载闲暇时用Linux做实验来学习Unix那样，很多人通过玩MySQL学习关系数据库。这些MySQL新手的大多数既没有关系数据库理论的背景，又没有时间阅读MySQL手册全文。

　　因此，我们决定研究某些方法，你可以用针对优化性能来调节MySQL。在读完本文后，你将理解一些帮助你设计你的MySQL数据库和查询的技术，值得你的应用很有效率。我们将假定你熟悉MySQL和SQL基础，但不假定你有这两方面的广博知识。

　　只存储你需要的信息

　　这听上去是常识，但人们常常采取“厨房下水道”的方式进行数据库设计。他们认为可能项要得每样东西都要存储并设计数据库保存所有者这些数据。你需要对你的需求现实些，并确定取确实需要什么信息。你常常能随意产生一些数据而不把它存在数据库表中。在这种情况下，从一个应用开发者的角度看也有道理这样做。

　　例如，在线目录的产品表可能包含各种产品的名称、介绍、尺寸、重量和价格。除了价格，你可能想存储每个项目相关的税和运输成本。但实际上不必这样做。首先税和运输成本可以方便地（由你的应用或MySQL）计算出来。其次，如果税和运输成本改变了，你可能必须编写必要的查询更新每个产品记录中的税和运输的费率。

　　有时人们认为这太难不能在以后往数据库表中加入字段，所以他们感觉不得不定义尽可能多的列。这是明显的概念错误。在MySQL中，你可以用ALTER
TABLE命令方便地修改表定义以适应你改变的需求。

　　例如，如果你突然认识到你需要给你的产品表增加一个级别列（可能你想允许用户在你的目录中给产品评级），你可以这样做：

　　ALTER TABLE procts ADD rank INTEGER
这给你的产品表增加了一个整数类型的级别列，你能用ALTER
TABLE做什么的完整介绍参见MySQL手册。

　　只要求你需要的东西--要清晰

　　就像说“只存储你需要的东西”那样，这可能看来是常识，但这一点常常被忽视，为什么呢？因为在一个应用开发时，需求经常改变，所以很多查询最终看来是这样：

　　SELECT * FROM sometable
当你不能肯定你将需要哪一列时，要求所有列明显是最省力的事情，然而随着你的表不断增大和修改，这可能变成一个性能问题。最好是在你的最初开发完成后再花些时间并确定你真正从你的查询中需要什么：

　　SELECT name, rank, description FROM procts
　

　　这带来了一个相关的观点，即代码维护比性能更重要。大多数变成语言（Perl、Python、PHP、Java等）允许通过字段名和数字编号访问一条查询的结果，这意味着你可以访问命名字段或字段0都可以得到相同的数据。

　　长期看，最好使用列名而不是其编号位置，为什么？因为一个表中或一条查询中地列的相对位置可以改变。它们在表中可能因为重复使用ALTER
TABLE而改变，它们在查询中将因重写了查询而忘记更新应用逻辑来匹配而改变。

　　当然，你仍然需要小心改变列名！但如果你使用列名而非标号位置，如列名改变，你可以用grep搜索源代码或使用编辑器的搜索能力查找你需要修改的代码。

　　规范化你的表结构

　　如果你以前从未听说过“数据规范化”，不要害怕。规范化可能是一个复杂的专题，你可以从只理解最基本的规范化概念中正真正获益。

　　理解它的最容易的方法是认为你的表是一个电子报表。如果你想以一个报表跟踪你的CD收藏，你可以如图1种那样进行设计：

　　图1

　　album track1 track2
track10
----- ------ ------
-------
Billboard Top Hits - 1984 Loverboy Shout St.
Elmo's Fire
(Billy Ocean) (Tears for Fears) (John
Parr)

　　这看上去很合理。大多数CD只有10首曲子，对否？不尽然。如果你拥有一张有100首曲子的CD且几张超过20首改怎么办。这意味着用这种方法，在极端的情况下，你将需要一个非常宽的表格（或一个超过100个字段的表）来保存所有的数据。

　　规范化表结构的目标是使“空单元”的数量最少，在上述CD表的情况下，如果你允许CD可能包含100首曲子，你会有很多这样的空单元。不

如何设计或配置mysql,才能达到高效使用的目的

基于MySQL双主的高可用解决方案理论及实践

MySQL在互联网应用中已经遍地开花，但是在银行系统中，还在生根发芽的阶段。本文记录的是根据某生产系统实际需求，对数据库高可用方案从需求、各高可用技术特点对比、实施、测试等过程进行整理，完善Mysql高可用方案，同时为后续开展分布式数据库相关测试做相应准备。

存储复制技术：传统IOE架构下，常用高可用方案，靠存储底层复制技术实现数据的一致性，优点数据安全性有保障，在于是依赖存储硬件，实施成本较高。

keepalived+双主复制：两台MySQL互为主从关系，即双主模式，通过Keepalived配置虚拟IP，实现当其中的一台数据库故障时，自动切换VIP到另外一台MySQL数据库，备机快速接管业务来保证数据库的高可用。

MHA： MHA部署在每台mysql服务器上，定时探测集群中的master节点，当master出现故障时，它可以自动将最新的slave提升为新的master，然后将所有其他的slave重新指向新的master，优点在最大程度保证数据的一致性的前提下实现快速切换，最少需要3台服务器，存在数据丢失的可能性。

PXC： Percona eXtra Cluster是Percona基于galera cluster封装的集群方案。不同于普通多主复制，PXC保障强一致性和实时同步，故障切换更快。但是也需要3个节点，配置相对复杂，对性能也稍有影响。

除了上述方案外，还有MMM、Heartbeat+DRBD等高可用方案，此处不做详细介绍。

综合评估下，本次实施采用了 keepalived+mysql双主实现数据库同城双机房的高可用。MySQL版本为： 5.7.21。操作系统：Red Hat Enterprise Linux Server 7.3。

配置过程如下：

Mysql-master1: IP地址1 --以下简称master1

Mysql-master2: IP地址2 --以下简称master2

Mysql-vip : VIP地址 --应用连接使用

Mysql复制相关概念描述：

1、 Mysql主从复制图示：

2、 Mysql主从复制过程描述：

（1）master记录二进制日志：在每个事务更新数据完成之前，master在二进制日志记录这些改变。MySQL将事务写入二进制日志。在事务写入二进制日志完成后，master通知存储引擎提交事务。

（2）slave将master的binarylog拷贝到自己的中继日志：首先，slave开始一个工作线程——I/O线程。I/O线程在master上打开一个普通的连接，然后开始binlog mp process。Binlog mp process从master的二进制日志中读取事务，如果已经同步了master，它会睡眠并等待master产生新的事件。I/O线程将这些事务写入中继日志。

（3）SQL slave thread处理该过程的最后一步：SQL线程从中继日志读取事务，并重放其中的事务而更新slave的数据，使其与master中的数据一致。只要该线程与I/O线程保持一致，中继日志通常会位于OS的缓存中，所以中继日志的开销很小。

主主同步就是两台机器互为主的关系，在任何一台机器上写入都会同步至备端。

为了便于后续数据库服务器的扩展，且在整个复制环境中能够自动地切换，降低运维成本，引入了当前主流的基于Mysql GTID的复制特性，工作原理及优缺点简介如下。

3、 GTID工作原理简介：

（1） master更新数据时，会在事务前产生GTID，一同记录到Binlog日志中。

（2） slave的I/O线程将变更的binlog写入到本地的relay log中。

（3） slave的sql线程从relay log中获取GTID，然后对比slave端的binlog是否有记录。

（4）如果有记录说明该GTID的事务已经执行，slave会忽略。

（5）如果没有记录，slave就会从relay log中执行该GTID的事务，并记录到binlog。

（6）在解析的过程中会判断是否有主键，如果有就用索引，如果没有就用全部扫描。

4、 GTID优点：

（1）一个事务对应一个唯一的ID，一个GTID在一个服务器上只会执行一次。（2） GTID是用来替代传统复制的方法，GTID复制与普通复制模式的最大不同就是不需要指定二进制文件名和位置。

（3）减少手工干预和降低服务故障时间，当主机宕机之后会通过软件从众多的备机中提升一台备机为新的master。

5、 GTID也存在一些：

（1）不支持非事务引擎。

（2）不支持create table … select 语句复制(主库直接报错)。

（3）不允许一个sql同时更新一个事务引擎表和非事务引擎表。

（4）在一个复制组中，必须要求统一开启GTID或者是统一关闭GTID。

（5）开启GTID需要重启(5.7版本除外)。

（6）开启GTID后，就不再使用原理的传统复制方式。

（7）不支持create temporary table 和 drop temporary table语句。

（8）不支持sql_slave_skip_counter。

前置条件：

主备两个节点使用行内统一的安装部署脚本安装mysql5.7.21介质(略)

Master1端创建应用的数据库(略)

1、修改MySQL配置文件

参考相关配置规范，分别设置master1、master2的my.cnf文件，

其中server-id参数设置为不同值;

由于后续keepalived会挂起VIP，应用通过VIP连接数据库，为了避免应用程序无法通过VIP访问，需将两个节点的bind-address参数注释掉；

2、设置master1端自动半同步模式

Mysql的同步模式主要有如下3种：

a. 主从同步复制：数据完整性好，但是性能消耗略高；

b. 主从异步复制：性能消耗低，但容易出现不一致；

c. 主从半自动复制：介于上述两种之间，既保持了数据的完整性，又提高了性能；

基于上述特性，建议采用半自动同步模式，由于后续要配置为双主模式，因此任一节点其角色既为master又为slave，因此相关的master/slave插件要同时配置，过程如下。

（1）首先查看库是否支持动态加载(默认都支持)

（2）主从库上分别安装插件

作为主库，安装插件semisync_master.so

作为从库，安装插件semisync_slave.so

（3）安装完成后，从plugin表中能够看到刚刚安装的插件

（4）分别打开主从库半同步复制

同时添加到各自的my.cnf中，在后续数据库实例重启时自动加载该配置。

此时查看状态还没有启动

（5）两个节点分别启动IO进程

（6）查看半同步状态

3、将master1设为master2的主服务器

（1）在master1主机上创建授权账户，允许在master2主机上连接

（2）将主库master1数据导出

（3）将master.sql传输到master2上并导入

（4）在master2端将master1设置为自己的主库，并开启slave功能

在master2上查看slave状态

至此master1到master2的主从复制关系已经建立完成。

4、将master2设为master1的主服务器

在master1上执行

在master1上查看slave状态

1、keepalived相关概念说明：

keepalived是集群管理中保证集群高可用的一个软件解决方案，其功能类似于heartbeat，用来防止单点故障

keepalived是以VRRP协议为实现基础的，VRRP全称VirtualRouter Rendancy Protocol，即虚拟路由冗余协议。

虚拟路由冗余协议，可以认为是实现路由器高可用的协议，即将N台提供相同功能的路由器组成一个路由器组，这个组里面有一个master和多个backup，master上面有一个对外提供服务的vip，master会发组播（组播地址为224.0.0.18），当backup收不到vrrp包时就认为master宕掉了，这时就需要根据VRRP的优先级来选举一个backup当master，这样的话就可以保证路由器的高可用了。

keepalived主要有三个模块，分别是core 、check和vrrp。core模块为keepalived的核心，负责主进程的启动、维护以及全局配置文件的加载和解析。check负责健康检查，包括常见的各种检查方式。vrrp模块是来实现VRRP协议的。同时为了避免出现脑裂，应关闭防火墙或者开启防火墙但允许接收VRRP协议。

2、keepalived的安装配置

（1）配置本地yum源，在master1和master2两台服务器上安装keepalived的相关依赖包Kernel-devel/openssl-devel/popt-devl等

配置指向rhel-7.5.iso的yum本地源，步骤略

注意：如不知道keepalived需要哪些依赖包，可到下载后的源码解压目录下查看INSTALL 文件内容，安装需要的依赖包，源码安装任何一个软件都要养成查看源码包文档的习惯，比如INSTALL，README,doc等文档，可以获得很多有用的信息。

（2）在两台mysql上解压缩并编译安装keepalived

（3）master1、master2上分别配置keepalived.conf

注意上图红色字体中两个节点配置相同处及差异。

说明：keepalived只有一个配置文件keepalived.conf，里面主要包括以下几个配置区域：

· global_defs：主要是配置故障发生时的通知对象以及机器标识。

· vrrp_instance：用来定义对外提供服务的VIP区域及其相关属性。

· virtual_server：虚拟服务器定义

（4）同时两个节点上都需要添加检测脚本

作用：是当mysql停止工作时自动关闭本机的keeplived服务，从而实现将故障主机踢出热备组，因每台机器上keepalived只添加了本机为realserver，所以当mysqld正常启动后，我们还需要手动启动keepalived服务。

（5）分别启动两个节点的keepalived服务

检查两个节点keepalived启动进程

检查两个节点的vip挂载情况

（6）主备机故障切换测试

停止master2的mysql服务，看keepalived 健康检查程序是否会触发脚本，自动进行故障切换，步骤略

查看master1节点的VIP挂载情况，验证是否实现了自动切换，步骤略

说明在master2服务器的mysql服务发生故障时，触发了脚本，自动完成了切换。

（7）现在我们把master2的mysql服务开起来，并且keepalived的服务也需要启动。

即便master2的mysql服务和keepalived服务都重新开启了，master1仍然是主master了，master2未对主master的权利进行抢夺，说明设置的nopreempt参数生效了，为了保证群集的稳定性，生产环境不允许抢占配置，只有当master1的mysql服务坏掉的时候，master2才会再次成为主master，否则它永远只能当master1的备份。（注：nopreempt一般是在优先级高的mysql上设置）

Sysbench是一个模块化的、跨平台、多线程基准测试工具，可用于评估数据库负载情况，通过sysbench命令配置IP地址、端口号、用户名、密码连接到指定的数据库db1中，创建多个表，并快速插入指定条数的记录，观察主备库同步效率

(1) 下载开源工具sysbench-0.4.12.14.tar.gz，放置在相应目录下并解压

(2) 使用iso配置本地yum源并安装Sysbench如下的依赖包(步骤略)：autoconf/automake/cdbs/debhelper(>=9)/docbook-xml/docbook-xsl/libmysqlclient15-dev/libtool/xsltproc

(3) 编译sysbench

编辑配置文件/etc/ld.so.conf中添加mysql lib目录/mysql/app/5.7.21/lib，并执行命令ldconfig生效

(4) 执行sysbench压测

使用sysbench工具向主节点的db1数据库中创建5张表，并且每张表分别插入10万条记录

同时观察备机同步效率

几个重要的参数说明：

B、半自动同步模式、异步模式切换测试

(1) 检查主备同步状态，及同步参数设置

rpl_semi_sync_master_enabled参数表示启用半同步模式；

rpl_semi_sync_master_timeout参数单位为毫秒，表示主库事务等待从库返回commit成功信息超过10秒就降为异步模式，不再等待从库，等探测到从库io线程恢复后，再返回为半自动同步；

rpl_semi_sync_master_wait_no_slave参数表示事务提交后需要等待从库返回确认信息；

(2) 将slave的io线程停止

(3) 使用sysbench向master写入少量的数据，本例创建一张表，并插入10条记录，命令包装在1.sh测试脚本中

通过记录的时间戳发现，master在等待了slave10秒无响应，自动切换为异步模式，将数据写入本地。

(4) Slave启动io线程，数据自动追平

至此MySQL主主复制配置完成，运行在半自动同步模式，通过keepalived实现Mysql的HA高可用。

上线后应符合统一的标准监控策略，添加备份协议对数据进行周期备份并保存到带库中，以及定期的数据恢复测试。

由于是靠keepalived实现的高可用，还应将如下资源添加到监控管理平台：

1、对每台数据库主机的3个keepalived进程进行监控；

2、对主备节点的io线程、sql线程工作状态进行监控；

《MySQL性能调优与架构设计》epub下载在线阅读,求百度网盘云资源_百 ...

《MySQL性能调优与架构设计》（简朝阳）电子书网盘下载免费在线阅读

资源链接：

链接：https://pan.baidu.com/s/1jRNyGypVd4gXmV5-fJgA-g

提取码：1aeg

书名：MySQL性能调优与架构设计

作者：简朝阳

豆瓣评分：8.1

出版年份：2009-6

页数：392

内容简介：《MySQL性能调优与架构设计》以 MySQL 数据库的基础及维护为切入点，重点介绍了 MySQL 数据库应用系统的性能调优，以及高可用可扩展的架构设计。

全书共分3篇，基础篇介绍了MySQL软件的基础知识、架构组成、存储引擎、安全管理及基本的备份恢复知识。性能优化篇从影响 MySQL 数据库应用系统性能的因素开始，针对性地对各个影响因素进行调优分析。如 MySQL Schema 设计的技巧，Query 语句的性能优化方式方法及MySQL Server中SQL层和存储引擎层的优化思路。同时还分析了 MySQL 数据库中主要存储引擎的锁定机制。架构设计篇则主要以设计一个高可用可扩展的分布式企业级数据库集群环境为目标，分析介绍了通过 MySQL 实现这一目标的多种架构方式。主要包括可扩展和高可用两部分内容，可扩展部分包括设计原则、Replication 的利用、数据切分、如何使用 Cache 和 Search，以及 NDB Cluster等内容。高可用则主要包括 Dual Master、DRBD、NDB Cluster，以及系统监控等方面。

本书主要面向有一定的 MySQL 基础或至少有一定SQL语言基础的读者朋友。

《MySQL性能调优与架构设计》epub下载在线阅读,求百度网盘云资源_百 ...

《MySQL性能调优与架构设计》（简朝阳）电子书网盘下载免费在线阅读

资源链接：

链接：https://pan.baidu.com/s/1jRNyGypVd4gXmV5-fJgA-g

提取码：1aeg

书名：MySQL性能调优与架构设计

作者：简朝阳

豆瓣评分：8.1

出版年份：2009-6

页数：392

本书主要面向有一定的 MySQL 基础或至少有一定SQL语言基础的读者朋友。

如何设计和实现高可用的MySQL

王甲坤，腾讯高级工程师、腾讯云关系型数据库MySQL负责人，拥有多年客户端、数据库研发经验。在IOS客户端、MySQL、PostgreSQL、SQL Server等产品有丰富的研发和产品策划经验。

下面开始我们今天的主要内容，今天主要是通过什么、为什么、怎么做，这条思路跟大家呈现MySQL的高可用。

首先介绍一下什么是高可用？在我看来就是业务在高质量的情况下，对用户提供服务的可运行的总时长。其实我们从事MySQL相关的工作，大家对9这个数字比较敏感，大家选择云厂商云产品的时候，首先会看它的数据库有几个9。目前腾讯云MySQL可以做到99.95，全年在25分钟的样子。

据我了解，高可用最高是能做到3个9，1个6，做到4个9很困难，做到5个9就是极限了。

为什么我们要做高可用？因为我们不可控的因素太多了，比如说，挖挖机，我记得基本上每隔一年都会出现这种类似的事件，让我记忆犹新的是2015年杭州萧山的某个主干网被刮断，导致阿里的部分服务不可用。另外，还有类似的一些停电，或者一些天灾等等。值得一提是，运维人员的一些操作失误案例，rm整个目录或者drop表，民间有说法叫从删库到跑路。不可控制的因素很多，你的数据、用户是你的，如果不可控的话，你的业务上不去。

一般来说，有两个指标会被当作衡量的标准，第一是RPO，第二是RTO。RPO从故障开始到业务恢复所丢失的数据量，RTO就是从故障开始到业务恢复所耗费的时长，两者都是越短越好。

我们怎么做呢？一般来说业界有三种方式，左边是基于单机存储方式，这种方式在游戏场景比较多，大家上层是用单独的计算机节点，下层用三副本保证数据的可靠性。在计算节点发生故障以后可以快速迁移到另一个计算节点，当然我们腾讯云的MySQL已经推出了这种模式，相对来说非常廉价，是基础版，大家在官网都可以购买到这种模式。第二种是基于共享存储方式，也叫share disk模式，这种比较典型的是oracle的RAC架构。底层基于共享存储的方式，上层采用多个计算节点，某个计算节点故障可立即从ip列表中提出，不影响用户访问。第三种就是基于数据复制模式，也就share nothing模式，通过数据传输、复制协议达到两台主机数据一致性，也是本次讲解的重点。另外，除了存储节点的高可用，其整个链路也需要高可用，比如，咱们的IDC机房，交换机，以及主机服务器等。

下面我们介绍下基础设施的高可用。大家经常听到几个术语，第一是同城双活，第二是两地三中心，两地三中心对于金融相关的场景是个强需求，其实说白了就是说我们在同城两个节点相差十公里之外有两个数据中心，在100公里异地以外有另个灾备中心，保证了机房的高可用。另外包括网络、主机，其实架构上是这样的，至少说你的交换机网络都有备份，一个倒了以后，另一个需要替换上去。

下面进入我们的重点，基于数据复制的高可用，首先介绍一下备份，备份确实是非常重要的，而且备份是一个实在没办法最后的一个保障，所以说建议大家不管是在云上用的业务，还是自己的IDC尽量做好备份。

MySQL备份基本上是这两种：逻辑备份、物理备份。逻辑备份通常使用官方的MySQLDump与第三方工具MyDumper，MyDumper优势在于多线程备份，速率快。物理备份使用Percona的xtrabackup，可以不落盘，通过基于流式并发与压缩，生产出成功率较高、速率较快并且暂用存储空间较低的备份。最后一种就是快照，我们腾讯云的基础版的备份就是通过快照生成的。

那基于数据复制方式，一般是主从两个节点，数据怎么保证一致性呢？其实是通过复制协议进行数据传输，通过Switch切换保证故障以后服务能够尽快恢复。右边的图基本和腾讯云MySQL差不多的架构，我们采用了一主一从的方式，从节点只负责故障的转移，当主节点挂了以后，通过自动故障探测与自动切换，从而做到业务尽快恢复。另外针对读写分离，腾讯云MySQL现可以支持一主挂5个只读节点。

下面介绍一下复制，在介绍复制之前有必要介绍一个重要的概念:binlog，binlog是二进制文件，主要记录用户对数据库更新的sql信息，binlog是什么样子呢？它是在磁盘上是这个样子，使用show binlog events后它是这样的，里面会记录一些元信息，比如位点、事件等等，我们通过MySQL官方解析工具mysqlbinlog解析后是这样的，里面sql语句是使用base64编码的，解码后是这样的，可以看到这里是条插入语句。那什么时候写binlog呢？大家来看这个图，我们知道事务提交有两个阶段：prepare与commit，请问是哪个阶段写binlog呢？binlog其实是在prepare后commit前写入的，同时写事务过程中，会产生redolog与undolog，那这两者有什么区别呢？我们知道MySQL是多引擎的关系型数据库，binlog是MySQL Server层的日志，而redolog是MySQL引擎InnoDB层的日志；另外一个不同是两者写入时机不同，redolog是prepare阶段每执行sql语句就写redo了，而binlog是在prepare完commit前写的。那MySQL在主从架构下怎么保证数据一致性呢？众所众知，MySQL为了保证性能，数据是先写内存后落盘的。当你数据库运行的时候，发生了宕机，机器再次恢复的时候可能是部分数据落盘了，部分未落盘。这时，mysql是找到binlog最新同步的位点或GTID，来确定redolog或者undolog中哪些实例需要回滚，哪些事务需要重做。另外，在写日志的时候，比如redolog或binlog，MySQL为保证高性能，也是先写内存后落盘的，所以日志的落盘策略也会影响数据的一致性。为保证数据的一致性，建议大家将涉及日志的参数配置为“双1”，也就是如图上所示。

下面我们来看看复制整个流程，其实很简单，Master通过mp线程将binlog落盘，在Slave上会有两个线程，分别是IO线程和SQL线程。IO线程接受来自Master的binlog并落地形成Relaylog，SQL线程并行读取relaylog中的sql信息，执行回放动作。一般来说，复制分三种：异步复制、半同步、强同步。这三者的区别在于何时将sql执行的结果反馈给客户端。异步复制，Master不管Slave，Master执行sql完毕后立即返回给客户端，这种方式性能最好，但是存在数据不一致的可能；强同步呢，Master完全关心Slave，等待Slave将relaylog回放后才返回给客户端，这种方式能够保证数据强一致，但是其性能有一定损耗；半同步则是Master部分关心Slave，认为只要binlog传输到Slave侧，落为relaylog后，即可以返回给客户端了。半同步是一种兼顾的实现，一方面保证数据一致性，另一方面兼顾了数据库的性能。

在复制过程中，我们经常遇到延迟问题，大家看图中所示，复制经历三个阶段：Dump线程落盘binlog、IO线程落盘relaylog、以及SQL线程回放，请问三个步骤里面哪个步骤是一个瓶颈？是SQL线程，是因为SQL线程在回放日志过程中是串行执行sql的，而Master对外是并行提供服务的。所以这里瓶颈是SQL线程。大家可用通过开启并行复制来解决延迟问题，MySQL5.6基于库级别并行复制；MySQL 5.7基于逻辑时钟并行复制，也就是表级别的并行；而MySQL8.0则是行级别的并行复制，粒度更细，复制效率更高。

刚才是说在协议级别进行复制，其实还有一种方式是块级别的数据复制，其不关心上层是什么，只需要保证在磁盘层面数据复制即可。当然这种方式的话，应用的比较少。说完复制后，咱们来说一下切换，其实MySQL官方之前并没有提供故障自动发现与转移的能力，基本上靠第三方工具来实现。

第一种是Keepalived，Master和Slave相互探测对方，时刻询问对方存活状态。当出现网络抖动或者网络出现问题的时候，可能会出现脑裂问题，变成了两主，数据就写错乱了。第二种就是MMM的方式， M1M2互为主备，再加上一个Slave节点做冗余。从图上看，虽然是双主，但该模式下同一时间点下只能有一个节点可以写，当发现这个主写节点出现故障，会将vip切换到另一个主上比。整体看，这种方式比较老，问题比较多。第三种是MHA，其应用广泛，这种方式是由复制组与管理节点组成，每个复制组里是由至少三个数据节点组成，数据节点上部署监控agent，定时上报到管理节点，当主节点出现问题时，由管理节点裁决是否切换到从节点。腾讯云是自己实现了一套故障检测，结构如右边的图，由高可用保证的Monitor节点来进行故障检测与切换。另外，目前我们还在做MySQL高可用的重构，届时能够做到故障检测恢复30秒钟以内，大大提高了高可用。

下面我们来说下集群的高可用架构，比较有名的就是PXC、MGC、MGR，PXC和MGC是结构比较类似，MGR是官方提供的，具有故障转移的高可用架构。大体的层级是这样的，MGR以插件的形式存在的，MGR主要是把复制协议进行改造，因为MGR支持多活，所以这里另一个重点是冲突检测，若多个节点同时写同一主键时，依照哪个为准呢？MGR是采用基于Paxos协议实现的冲突检测。下面，我们大致看下结构，MGR是支持多个节点写，即多活，支持某个节点挂了后自动剔除，恢复后自动加入集群。这张图是介绍一下MGR数据流逻辑，图上有三个节点构成最小MGR集群。假设DB1有一次写提交，在Prepare阶段，MGR插件会生成一个叫WriteSet的集合，并将其广播给其他节点。这个WriteSet集合包含此次提交的binlog和更新的唯一键，此唯一键由db名、表名和主键组成。这里可以看出MGR有个，表中必须要有主键，要不无法进行冲突检测。我们再说回来当节点收到这一信息时，会进行比对，每个节点都有一个缓存，保存当前同步情况，即唯一键对应的GTID SET。通过比对后将结果返回给DB1,只要多于半数的节点返回说OK，可以提交，那DB1接下来就会执行binlog的落盘操作，然后返回OK到客户端。其他节点则执行写Relaylog的动作，接下来进行回放的动作。若多数节点返回冲突，DB1则执行回滚操作，其他节点会drop掉复制过来的binlog。

其实PXC和MGC思路是差不多，应该说是MGR借鉴的，因为PXC和MGC是比较早就出来的，这里大同小异，主节点将WriteSet写集合广播出去，广播完后进行验证与裁决。

最后我们说一下NewSQL高可用架构，首先对AWS表示致敬，孵化出非常优秀的NewSQL产品-Aurora。那Aurora是怎么产生出来的呢？这与AWS数据库架构有关。我们来看看这个图，AWS数据库是架构在虚拟机与云盘上的，我们都知道MySQL的log比较多，所以很多IO是通过耗时较高的网络来完成的，因此AWS这种架构网络IO是它的瓶颈，性能也跑不上去。在此基础上，我们来认识下Aurora。

Aurora是计算与存储分离的架构，典型的share disk 的结构。底层存储采用6副本，部署在三个不同的AZ上，可以保证一个AZ挂了，或者至多两个AZ的一个副本丢失的情况下数据不丢失，业务可以正常对外服务。Aurora的理念是“日志即数据库”，其把MySQL存储层进行了彻底的改造，摒弃了很多LOG，只留下了Redolog，具备将redolog转换到Innodb page的能力。通过这种方式，Aurora宣称其减少至少85%比例的IO。另外其把备份和回档下沉到存储节点，使得备份恢复更快并得到保障。Aurora整体感觉相对比较接地气，成本相对比较低。

另一个就是阿里云的Polar，理念和AWS不同，阿里云觉得未来网络不是问题，未来网络可以接近总线的质量，所以是架构在RDMA网络的机房里，日志方面大动作较少，保证后续MySQL社区新特性可快速迭代近来。Polardb也是share disk的架构，其存储节点是通ParallelRaft协议保证数据的完备性。可见这也是个伟大的架构，但是相对来说成本比较高一些。

我们腾讯云自己的NewSQL在研发中，只是目前还没有正式上线，我们的名字叫CynosDB，相比来说我们的理念是兼顾两者，未来在高网络新硬件的基础实施下，会发挥更大的性能，更稳健的服务和更高的可用性。请大家拭目以待。

本次我的分享就到此为止。

Q & A

Q：我想问一下在腾讯游戏的高并发行业里面，我们主要采用哪种架构？

A：腾讯内部有很多自研项目，但基本上我们是基于数据复制的方式。内部有phxsql等分布式集群架构。

Q：如何在高并发情况下，保证总库的定延时呢？

A：可以开启并行复制，业务做分库分表，分散到多个实例上。

Q：比如说像游戏类的，在游戏高峰期的话会有很多人同时在线，这种情况下怎么在后台看数据呢？

A：可以对比较热的数据进行分层，前一层可以通过KV方式缓存，比如Redis，来提高热数据的读取，后一层使用MySQL,定期将数据同步落盘。

Q：这种情况下怎么保证数据库是一致的呢？

A：写数据可以不经过KV缓存，直接写MySQL数据库，读取时，缓存内没有数据，需要从DB中捞取出来。另外，KV缓存也有落地能力，非关键数据也可以不使用MySQL落地。

全部频道

MySQL性能管理及架构设计 --- 理论篇