Mysql高阶面试问题总结

一、Mysql版本问题

使用的是什么版本？是否对MYSQL升级？最新的版本是什么，什么特性吸引你？
1、除了官方提供的社区版和企业版，还有一个版本使用的比较多：Perscona Mysql，该版本是原来的mysql开发者后续维护的改进版，对mysql的社区版进行了优化，性能要优于社区版的，落后于官方的一个版本。
下载地址：https://www.percona.com/downloads/Percona-Server-LATEST/

2、Mysql另一个常见发行版：MariaDB
该版本是mysql初创者在mysql被Oracle收购之后，另起炉灶重新成立的。（Mysql5.5之后发展起来的）增加了许多特有的功能。
下载地址：https://downloads.mariadb.org/
版本对比：




3、关于版本升级
在主从复制中，高版本的Mysql可以作为低版本的从来使用的，反过来有时候就不行
（1）升级可以给业务带来的益处

• 是否可以解决业务上某一方面的痛点？比如使用5.6时，主从复制场景下的数据同步延迟太大，就可以考虑升级到5.7，因为5.7版本新增了多线程复制功能，支持更高的并发，能够解决延迟的问题；再比如，数据库中大量使用了json类型，在Mysql8以前的版本中，进行二进制主从复制时，是全部复制的，而不是只复制变化的一部分，但是Mysql8进行了修改，支持只复制json中修改的部分。
• 是否可以解决运维上某一方面的痛点？比如，5.7就在原来的基础上提供了更好的管理工具。

（2）升级可能对业务带来的影响

• 是否对原来业务程序的支持有影响？比如，jdbc与mysql的版本匹配问题。
• 是否对原来业务的性能是否有影响？比如，在5.6中开启performance_schema，则有些处理的性能还不如5.5，因为performance_schema收集数据时会影响到性能。（一般会在5.6版本的mysql中关闭performance_schema，而5.6以上的版本对此进行了更多的优化，可以放心使用）

（3）数据库升级方案的制定
必须要在测试环境中不断的被验证可行，才能实施。

• 进行全备份
• 升级slave服务器版本
• 手动进行主从切换
• 升级master服务器版本
• 升级完成后进行业务检查

（4）数据库升级失败的回滚方案

4、最新的Mysql版本及其新特性
Mysql8.0版本的主要新特性：（官方将版本号从5.7直接跳到了8.0，所以其更新的功能是很多的）


在安全性上满足了许多大公司对审计的要求。

二、用户账号管理类

1、在给定场景下为某个用户授权
1.1 Mysql数据库账号
账号由两部分组成，形如：用户名@可访问控制列表
mysql5.7之前，用户名的最大长度是16字节
mysql5.7之后，用户名的最大长度是32字节
“可访问控制列表”限制了用户可以从哪些服务器上进行访问。比如：

• %：代表可以从所有外部主机访问；（不加，则默认就使用这个）
• 192.168.1.%：表示可以从192.168.1网段访问
• localhost:DB服务器本地访问
  注意，如果一个用户名配置了多个控制列表，mysql会使用最先匹配的情况。在mysql客户端访问时，会自动识别。

mysql5.7之前，使用create user命令建立用户时就指定权限，但是mysql5.7之后，是要先使用create user命令建立用户，然后再指定权限。

常用的用户权限：


通过命令:show privileges查看支持的权限。

给一个create的用户设置权限：

grant select,update,delete,insert on db.tb to user@ip;

收回某个用户的权限：

revoke delete on db.tb from user@ip;

2、如何保证数据库账号的安全？
慕课网的《
高性能可扩展MySQL数据库设计及架构优化——数据库开发规范的制定》观看笔记可参考：https://blog.csdn.net/weixin_38477351/article/details/90142597
基本上遵循以下几个原则即可：最小权限原则、密码强度策略、密码过期原则、限制历史密码重用原则。当然要注意，如果程序支持从配置中心读取最新密码的话可以使用密码过期，否则，会引起服务异常。
mysql8.0中设置密码过期和限制历史重用：

>\h create user
password_option: {
PASSWORD EXPIRE [DEFAULT | NEVER | INTERVAL N DAY]
| PASSWORD HISTORY {DEFAULT | N}
| PASSWORD REUSE INTERVAL {DEFAULT | N DAY}
| PASSWORD REQUIRE CURRENT [DEFAULT | OPTIONAL]
}

>create user test@‘localhost’ identified by ‘123456’ password history 1;
通过以下命令进行查看设置情况
>select * from mysql.user where user=‘test’\G

3、数据库迁移账号
首先要判断数据库版本是否一致，如果一致，备份后恢复即可。如果不一致，则需要先导出授权语句，在目的库中再执行一遍即可。

导出用户建立以及授权语句：
pt-show-grants u=root,p=123456, h=localhost

三、服务器配置类

1、Group By语句的异常原因
SQL MODE：配置Mysql处理SQL的方式
set [session/global/persist] sql_mode=‘xxxx’
8.0之后新增persist，对global范围进行持久化（保存到生成新的文件mysqld-auto.cnf中），重启mysql后还会存在，而如果是session和global则重启失效。

当然，可以在mysql的配置文件中进行配置：
[msyqlid] sql_mode=xxxx
(以前的版本只需要修改.cfg文件，但是新版本中则是将某些配置保存到了mysql日期文件中)

常用的SQL MODE：
ONLY_FULL_GROUP_BY: 对于group by聚合操作，如果出现在select中的列、having或者order by子句的非聚合列，没有在group by中出现，那么这个SQL语法检查报错。
ANSI_QUOTES:禁止用双引号来引用字符串（数据库迁移时不同的mysql版本对双引号不一定支持）
设置后，如果还是用双引号则会报错：ERROR 1054 （42s22）：Unknow column ‘xx’ in ‘field list’
REAL_AS_FLOAT: Real作为float的同义词（因为Real默认是double类型的）

PIPES_AS_CONCAT：将||视为字符串的连接操作符而非或运算符

其实，上面的这些配置很接近于ANSI的标准，所以，同时设置这些值时，简单的设置为：SQL_MODE='ANSI’即可。

以上又被称为宽松模式，下面开始是严格模式。

Strict_trans_tables / strict_all_tables: 在事务存储引擎/所有存储引擎上启用严格模式出现，那么这个SQL语法检查报错。

Error_for_division_by_zero: 不允许0做为除数（该模式下，除0后会产生警告，而宽松模式下没有任何提示）

NO_AUTO_CREATE_USER：在用户不存在时不允许grant语句自动建立用户。

NO_ZERO_IN_DATE / NO_ZERO_DATE: 日期数据内 / 日期数据不能含0

NO_ENGINE_SUBSTITUTION: 当指定的存储引擎不可用时报错。

2、如何比较系统运行配置和配置文件中的配置是否一致
为什么会出现不一致的情况：使用set命令可以配置动态参数
如果判断是否一致呢：使用pt-confgi-diff工具比较配置文件
pt-confgi-diff该工具目前不支持8.0的mysql，具体使用方法如下：
pt-config-diff u=root,p=xxx,h=localhost /etc/my.cnf

3、MySQL中关键的性能参数

• max_connections：最大连接数
• interactive-timeout: 设置交互连接的timeout时间
• wait_timeout: 设置非交互连接的timeout时间
• max_allowed_packet: 控制mysql可以接收的数据包大小
• sync_binlog: 为了提高io性能而设置的参数，表示每写多少次缓冲会向磁盘同步一次binlog。对于master，最好设置为1，最大限度的同步，以免造成数据丢失。
• sort_buffer_size: 设置每个会话使用的排序缓存区的大小。
• join_buffer_size: 设置每个会话使用的连接缓冲的大小。
• read_buffer_size: 指定了当对一个MYISAM（就是一个临时表，8.0以前的查询中会存在临时表汇总最后结果的操作，排序）进行表扫描时所分配的读缓存池的大小（一定是4k的倍数）
• read_rnd_buffer_size: 设置控制索引缓冲区的大小

加粗的四个参数是针对每个线程分配的，所以，如果存在100个连接，就有可能出现内存溢出的风险。

• binlog_cache_size：设置每个会话用于缓存未提交的事务缓存大小。

存储引擎相关的参数：

• innodb_flush_log_at_trx_commit :
  0：每秒进行一次重做日志的磁盘刷新操作；
  1：每次事务提交都会刷新事务日志到磁盘中；（默认，最好使用这个）
  2：每次事务提交写入系统换存每秒向磁盘刷新一次（有可能丢失一秒的事务）。
• innodb_buffer_pool_size: 设置Innodb缓冲池的大小，应为系统可用内存的75%
• innodb_buffer_pool_instances: Innodb缓冲池的实例个数，每个实例的大小为总缓冲池大小 / 实例个数。
• innodb_file_per_table：设置每个表独立使用一个表空间文件。（5.7以及后续版本默认启动该参数）

四、Mysql日志

1、常用日志（服务器的日志，而不是存储引擎的日志）
错误日志error_log：记录启动、运行、停止出现的问题。
分析排查mysql运行中的错误；记录未经授权的访问。

• 指定错误日志的路径：log_error=$mysql/sql_log/mysql_error.log
• 设置记录错误日志的级别：log_error_verbosity=[1,2,3]
  1—Error messages
  2—Error and warning messages
  3—Error,warning and note messages
• 8.0中新增参数：log_error_services=[日志服务组件1；日志服务组件2]
  log_filter_internal—默认的日志过滤组件，依赖log_error_verbosity（默认）
  log_sink_internal—默认的日志输出组件，依赖log_error（默认）
  log_sink_json—将错误日志输出到json文件（需要安装组件）
  log_sink_syseventlog—将错误日志输出到系统日志文件中

mysql> select @@log_error_services;
+----------------------------------------+
| @@log_error_services                   |
+----------------------------------------+
| log_filter_internal; log_sink_internal |
+----------------------------------------+
1 row in set (0.00 sec)

# 安装json组件
mysql> install component 'file://component_log_sink_json';
Query OK, 0 rows affected (0.03 sec)

常规日志general_log：记录所有发向mysql的请求。
分析客户端发送到mysql的实际请求时打开：
general_log=on / off
分析完后，一定要记得关闭，否则在几分钟内，就有可能占满空间。
general_log_file=$path
log_output=[file | table | none]

慢查询日志slow_query_log：记录符合条件的查询。
找需要优化的SQL：超过一定的阈值，没有使用到索引。
slow_query_log = on / off
slow_query_log_file = $path
long_query_time=xxxx秒
log_queries_not_using_indexes=ON / OFF(默认off)
下面两个不常用
log_slow_admin_statements=ON / OFF(记录执行的管理类的命令，默认off)
log_slow_slave_statements=ON / OFF(默认off)

二进制日志binary_log: 记录全部有效的数据修改日志。直接关系到对Mysql的高可用配置。（非常重要）

• 记录所有对数据库中数据的修改
• 基于时间点的备份和恢复（然后再增量的备份和恢复，另外还可以恢复误操作的数据）
• 主从复制（高可用架构都是通过二进制日志实现的）

log-bin：静态参数，只能在配置文件中配置，且只能是重启生效。
可以配置存放的目录和前缀名，默认存放在mysql的数据目录中且以主机名作为前缀。
binlog_format=[ROW | STATEMENT | MIXED]，默认ROW，避免出现主从不一致的问题。statement是基于段的方式，比如一条SQL语句修改一万行数据，row会记录一万次，而statement只是记录SQL语句。所以，ROW方式会产生大量的记录，如何减少ROW日志呢？办法就是下面的这个参数设置：binlog_row_image
binlog_row_image=[FULL | MINIMAL | NOBLOB]，定义二进制日志记录的方式。FULL——默认使用该参数，会将修改前后的数据都记录下来；MINIMAL——只记录修改的数据，一般使用该参数；NOBLOB——记录除blob以外的数据。

flush logs; // 刷新日志文件，从而生成一个新的日志文件。

上面有提到，在主从复制下，不建议使用statement，只能使用ROW，但是如果出现问题想看一下执行的SQL语句时，怎么办呢？这时就需要使用：binlog_rows_query_log_events参数来记录执行SQL语句
binlog_rows_query_log_events=[ON | OFF]

log_slave_updates=[ ON | OFF ]，开启后，slave服务器也会记录日志到slave服务器上。默认为off，就不会同步主上的日志到slave上。
sync_binlog=[ 1 | 0 ]；控制mysql如何刷新二进制日志到磁盘，5.7后的版本默认为1，也就是每写一次二进制日志，就会向磁盘刷新一次。即使是mysql崩溃，数据也不会丢失。如果是0，则由操作系统自己决定何时刷新数据到磁盘。

随着操作的不断增加，二进制日志也会不断的增大，最好是定期备份到其他地方。
expire_logs_days = days // 过期时间为一天
purge binary logs to ‘mysql-bin.010’ // 将mysql-bin.0001到mysql-bin.009的文件全部删除。
purge binary logs before ‘2019-12-01 22:00:00’ // 将这个时间点之前的二进制文件删除

中继日志relay_log：用于主从复制，临时存储从主库同步的二进制日志。
主要作用是：临时记录从master服务器同步的二进制日志。
重要的参数有两个：
relay_log = filename // 指定位置和前缀
relay_log_purge = [ ON | OFF ] // 清理功能

2、通过日志审计

https://www.jianshu.com/p/8339a4c86bd4
https://blog.csdn.net/qq_41930193/article/details/82776159

五 存储引擎

Mysql数据库也被称为插件式的数据库，因为可以更换不同的存储引擎。
1、常见的存储引擎以及适用场景：

• MyIsam：不支持事务；5.6之前默认使用，最常用的非事务型存储引擎。
  一般情况下的DB是服务层实现事务的，而mysql是通过存储引擎实现的。
  MyISAM是以堆表的方式存储，所以没有特定顺序的，没有聚集索引的概念，所以索引的叶子节点存储的就是指向数据的物理地址，而不是聚集索引的位置，因此避免了索引中的二次查找，对于大表的查询性能会有一定的提高，单从大表的查询性能上来看，MyISAM的性能一定是好于InnoDB的；
  MyISAM使用的是表级锁；在查询时，加的是共享锁，修改数据时加的是排他锁。所以，读写时会阻塞，MyISAM天生不适合高并发场景下；
  MyISAM支持Btree索引，空间索引，全文索引；
  MyISAM的表索引和数据是分开存储的，MYD、MYI，可以对这种表压缩，损坏时可以修复。
  
  使用场景：读操作远远大于写操作的场景；不需要事务支持的场景；

• CSV：不支持事务；以CSV格式存储的非事务型存储引擎。
  CSV格式指的是：每一列数据用逗号分隔，字符串用双引号括起来。（Comma-Separated Values的缩写）
  CSV存储引擎要求每一列都不能为null；
  CSV存储引擎不支持索引；
  适用场景：做为数据交换的中间表使用；

• Archive：不支持事务；只允许查询和新增数据而不允许修改的非事务型存储引擎。（适用场景很显然就是用在归档数据和日志中）
  Archive存储引擎的表数据会使用zlib压缩；
  Archive存储引擎只支持Insert和Select；
  Archive存储引擎只允许在自增ID上建立索引；
  适用场景：日志和数据采集类应用；数据归档存储；

• Memory：不支持事务；是一种易失性的非事务存储引擎。（在内存中，重启数据消失；在mysql中如何要临时存储中间的结果，并且这些临时数据在符合一定条件的前提下，就会把中间结果数据存储在Memory存储引擎的表中）
  数据在内存中；
  所有字段长度固定；
  支持Btree和Hash索引；
  适用场景：用于缓存字典映射表；缓存周期性分析数据（类似于redis）；

• InnoDB：支持事务；最常用的事务型存储引擎。（5.6以后默认）
  事务型存储引擎支持ACID；
  数据按主键聚集存储；每一个非主键索引的叶子节点所指向的都是数据行的主键，而不是数据存储的物理位置，因此主键的大小直接影响查找的性能，另一方面，数据时按照主键的逻辑顺序存储的，如果主键的顺序经常的无规则的变化，那么一定会造成数据的不断迁移，这样会带来IO性能的影响。（所以，分布式中常用雪花算法生成ID）
  支持行级锁以及MVCC；mvcc就是多版本并发控制，避免读写操作的相互阻塞。
  支持Btree和自适应Hash索引；自适应Hash索引指的是：InnoDB存储引擎会根据数据统计信息，在内存中建立Hash索引，这种索引能用于等值查找，并且不需要DBA人为的干预。
  支持全文索引和空间索引；5.6版本以及之后开始支持全文索引，5.7以及之后支持空间索引。
  适用场景：大多数OLTP场景。—— On-Line Transaction Processing联机事务处理过程(OLTP)，也称为面向交易的处理过程，其基本特征是前台接收的用户数据可以立即传送到计算中心进行处理，并在很短的时间内给出处理结果，是对用户操作快速响应的方式之一。

补充：数据处理大致可以分成两大类：联机事务处理OLTP（on-line transaction processing）、联机分析处理OLAP（On-Line Analytical Processing）。OLTP是传统的关系型数据库的主要应用，主要是基本的、日常的事务处理，例如银行交易。OLAP是数据仓库系统的主要应用，支持复杂的分析操作，侧重决策支持，并且提供直观易懂的查询结果。
OLTP 系统强调数据库内存效率，强调内存各种指标的命令率，强调绑定变量，强调并发操作；
OLAP 系统则强调数据分析，强调SQL执行市场，强调磁盘I/O，强调分区等。

• NDB存储引擎：用于集群中，能够保证一致性。但是并不常用，因为不适合高并发，大数据量中应用。
  主要用于Mysql集群中的存储引擎；
  事务存储引擎；
  数据存储在内存中；
  支持行级锁；
  支持高可用集群；
  支持Ttree索引；
  适用场景：需要数据完全同步的高可用场景；

2、Innodb在什么场景下无法在线修改表结构
5.5版本中如果要执行DDL操作时，会产生排它锁，阻塞写操作。5.6以及后续已经改善了。

（1）首次增加全文索引和空间索引的场景下
InnoDB在增加全文索引和空间索引的场景下，由于要增加一个隐藏列来记录索引的信息，所以要重新建表，就不能在线修改表结构。也就是说，当第一次对表进行建全文索引和空间索引时，不能在线进行。

（2）删除主键的场景下
InnoDB是按照主键的逻辑顺序存储的，当修改主键时，就需要重新排列，所以无法在线执行删除主键的操作。

（3）增加自增列的场景下
自增列作为主键的一部分，如果在线增加自增列时，就会影响到主键，就会影响到排列顺序想，所以，不能在线执行该操作。

（4）修改列类型的场景下
因为修改类型时，要对所有的数据进行校验，所以，也不能在线完成。

（5）修改表的字符集场景下
因为这种情况下，要对所有的数据进行校验转换，所以，也不能在线完成。

在线DDL存在的问题：

• 部分语句不支持在线DDL；
• 长时间的DDL操作会引起严重的主从延迟
• 无法对DDL操作进行资源限制（尤其是操作大表时，有可能出现内存不足导致语句执行失败）

3、如何更安全的执行DDL？如果在无法进行在线修改表结构的情况下，要如何操作？

解决方案：pt-online-schema-change [options] DSN
这是Perscona 的工具，pt-online-schema的原理是：先建立一个和所要修改的表相同的新表，结构是修改后的结构，然后把原表中的数据更新到这个新表中，数据导入完成后，新旧表重命名即可，只有短暂的锁定。

执行的过程和原理可以从输出中看出：

4、InnoDB时如何实现事务的？
（1）什么是事务？

• 原子性A：全成功完成或者全失败不操作；
• 一致性C：事务开始前和事务结束以后，数据库的完整性没有被破坏；
• 隔离性I：事务之间的可见性；
• 持久性D：事务一旦提交，其结果就是永久的；

并发状态下事务会产生的问题：
脏读（针对未提交数据）：即事务A读到了事务B还没有提交的数据。
不可重复读（在一个事务里面读取了两次某个数据，读出来的数据不一致，针对修改操作）。
幻读（在一个事务里面的操作中发现了未被操作的数据，针对增删操作）

（2）事务的实现方式

• 原子性A：回滚日志Undo log，用于记录数据修改前的状态；
• 一致性C：重作日志Redo log，用于记录数据修改后的状态；
• 隔离性I：锁——用于资源隔离，分为共享锁和排它锁；
• 持久性D：重作日志+回滚日志共同实现；

以A账户向B账户汇款500元为例：

5、InnoDB读操作是否会阻塞写操作？读数据时加共享锁，修改数据时加排它锁，而共享锁和排他锁是不兼容的，为什么实际使用中没有阻塞呢？
查询需要对资源加共享锁（S），防止其他事务修改数据
数据修改需要对资源加排他锁（X），防止其他事务修改数据

Innodb中使用了**MVCC（多版本并发控制）**实现了读写不阻塞
简单的说MVCC的过程如下：（还是以转账业务为例）

也就是说，虽然加了排它锁，其他事务是读取的undo.log里的值，与锁无关。
MVCC只工作在REPEATABLE READ和READ COMMITED隔离级别下。

六 高可用架构

1、主从复制是如何工作的
（1）实现原理

主从复制——异步复制
Mysql默认采用异步复制
一旦Master宕机会丢失数据

mysql从5.6开始引入了半同步复制机制
mysql>show plugins; // 查看已经安装的插件
mysql>install plugin rpl_semi_sync_master SONAME ‘semisync_master.so’; // 在Master上安装半同步复制-Linux环境
mysql>install plugin rpl_semi_sync_master SONAME ‘semisync_master.dll’; // 在Master上安装半同步复制-Windows环境
mysql>install plugin rpl_semi_sync_slave SONAME ‘semisync_slave.so’; // 在Slave上安装半同步复制-Linux环境
mysql>install plugin rpl_semi_sync_slave SONAME ‘semisync_slave.dll’; // 在Slave上安装半同步复制-Windows环境

（2）异步方式主从复制的配置步骤（Master版本尽量不要高于Slave版本）
Master:

• 开启binlog（必须），开启gtid(可选，大于5.6版本即可)
• 建立同步所需要的账号
• 使用master_data参数备份数据库
• 把备份文件传输到Slave服务器

# 查看版本是否一致，如果是8.0版本要注意密码的认证方式
>select @@version;
# 查看是否启用了binary_log和gtid模式
>show variables like 'log_bin%';
>show variabels like 'gtid_mode';	// 要求要在配置文件中配置：enforce-gtid-consistency / log-slave-updates=on / master_info_repository=TABLE / relay_log_info_repository=TABLE
# 建立复制所用账号并授权
>  create user repl@'192.168.1.%' identified by '123456';
>   grant replication slave on *.* to repl@'192.168.1.%';
# 导出现有的数据库(single-transaction保证导出数据时事务的一致性,master-data记录当前备份的日志点)
>  mysqldump --single-transaction -uroot -p --routines --triggers --events --master-data=2 --all-databases > master.sql
// master-data有两种取值：1和2,1代表只会生成change master command；2代表不仅仅生成change master command 而且会注释掉这些命令。

将master.sql文件上传到从服务器上

Slave:

• 开启binlog（可选），开启gtid(可选，大于5.6版本即可)
• 恢复Mster上的备份数据
• 使用Change Master配置链路
• 使用start slave启动复制

# 导入备份文件
>msyql -uroot -p < maser.sql
# 完成主从复制链路，master_log_file和master_log_pos从备份文件中获取
>change master to master_host='192.168.1.1', master_log_file='mysql-bin.0000001',master_log_pos=400;
# 查看复制链路具体信息
>show slave status \G;
# 启动复制链路，需要指定复制账号
>start slave user='repl' password='123456';
# 查看复制链路具体信息，可以看到Slave_IO_Running：Yes; Slave_SQL_Running: Yes
>show slave status \G;

（3）半同步方式的主从复制步骤
首先要保证安装了半同步方式的插件（master和slave都需要）

# 查看半同步方式的变量
>show variables like 'rpl%';
# 设置半同步的超时时间500ms
>set persist rpl_semi_sync_master_timeout=500;
>set persist rpl_semi_sync_master_enabled=on;

Slave Mysql中只需要开启即可。

以上配置是在Mysql正在运行的时候进行的，要想起作用，需要重启复制链路，在Slave mysql中只需要重新启动io线程即可。
mysql>stop slave io_thread;
mysql>start slave io-thread user=‘repl’ password=‘123456’;
mysql>show slave status \G // 查看启动状态

在master mysql上可以查看半同步复制的启动状态：
mysql>show global status like ‘rpl%’;

在slave mysql也执行该命令，会看到只有Rpl_semi_sync_slave_status ‘ON’

2、基于日志点和GTID方式的复制
5.6版本之后引入的GTID方式（全局事务ID）
https://blog.51cto.com/13434336/2178937
https://blog.csdn.net/woailyoo0000/article/details/88981380

（1）对于基于日志的复制方式，是slave请求master的增量日志依赖于日志偏移量的，所以在配置链路时需要指定master_log_file和master_log_pos参数。同一个事务在master和slave中的记录都是不相同的，所以，一旦出现master宕机，需要做主从迁移的时候，很难在master中找到正确的二进制文件和偏移量。这也就是为什么在5.6中引入GTID方式的主要原因。

（2）GTID
所谓的全局仅仅指的是：在同一个集群范围之内，也就是说，如果有一个一主三从的主从复制架构的话，四台服务器上的事务ID是唯一的。GTID的组成如下：
GTID=source_id:transaction_id
GTID的作用：source_id标识出事务从哪一台服务器上最初提交的；另一个可以方便的对Mysql实例进行故障转移。

slave 增量同步Master的数据依赖于其未同步的事务ID；
配置复制链路时，Slave可以根据已经同步的事务ID继续自动同步。

两种复制方式的比较：

基于日志方式	基于GTID方式
兼容性好	同老版本的Mysql / MariaDB不兼容
支持MMM和MHA架构	仅支持MHA架构
主备切换后很难找到新的同步点	基于事务ID复制，可以很方便的找到未完成同步的事务ID
可以方便的跳过复制错误	只能通过设置入空事务的方式跳过错误

如何选择复制方式：能用GTID就用GTID，优先选择GTID。

3、MMM和MHA两种高可用架构的优缺点
MMM和MHA是两种对主从复制架构管理的系统。
https://www.jianshu.com/p/7331779dbae8

数据中间层的作用是：负责数据库读写分离、负载均衡或分库分表的。
MMM和MHA的作用是：保障数据库服务的高可用。
这两种架构的作用可以概括为以下几点：

• 对主从复制集群中的Master的健康进行监控；
• 当Master宕机后把写VIP迁移到新的Master；(VIP是独立于数据库服务器物理IP的另外的虚拟IP，这个虚拟IP可以按需要任意绑定到具有Master角色的服务器上，当然，同一时间虚拟IP只能绑定一个IP上，上层只是访问这个虚拟IP即可)
• 重新配置集群中的其他Slave对新的Master同步；

3.1 MMM(Master-Master replication managerfor Mysql)
两个master（一主一备，同一时间只有一个提供服务）
正常情况下的主从布局：

当目前的主Mysql出现宕机后，从Mysql自动迁移到主备Mysql上，并且写VIP也绑定到主备：

当某一个从Mysql出现问题后，MMM会自动将VIP绑定到其他从Mysql上：

当然，除了Mysql服务器之外，还需要有一个运行监控这些Mysql服务的服务：MMM_Monitor，上面图中的每一个mysql服务器上都有一个mmm_agent组件。

优点：

• 提供了读写VIP的配置，使读写请求都可以达到高可用；
• 工具包相对完善，不需要额外开发脚本；
• 完成故障转移后，可以持续对Mysql集群进行高可用监控；

缺点：

• 故障切换简单粗暴容易丢失事务；(解决方案：主备使用5.7以后的半同步复制)
• 不支持GTID的复制方式；(解决方案：自行修改perl脚本实现)
• 社区不活跃，很久不再更新版本

适用场景：

• 使用基于日志点的主从复制方式
• 已经使用主主复制架构的
• 需要考虑读高可用的场景

3.2 MHA(Master High Availability)
正常情况下的布局：

当主mysql出现问题后，在从mysql中选举一个master

故障转移步骤：

1. 选举具有最新更新的Slave
2. 尝试从宕机的master保存二进制日志
3. 应用差异的中继日志到其他slave
4. 应用从Master保存的二进制日志
5. 提升选举的slave为新的master
6. 配置其他slave向新的master同步

优点：

• 支持GTID的复制方式和基于日志点的复制方式；
• 可从多个Slave中选举最适合的新master;
• 会尝试从旧master中尽可能多的保存未同步的日志；

缺点：

• 未必能获取到旧主未同步的日志；(解决方案：主备使用5.7后的半同步复制)
• 需要自行开发写VIP转移脚本；
• 只监控master而没有对slave实现高可用的办法；

适用场景:

• 使用基于GTID的复制方式；
• 使用一主多从的复制架构；
• 希望更少的数据丢失场景；

4、如何减小主从复制的延迟
产生原因：

几种减小主从复制延迟的方法:
（1）大事务：数万行的数据更新以及对大表的DDL操作

• 化大事务为小事务，分批进行更新
• 使用pt-online-schema-change工具进行DDL操作
  先建立一个与原表结构相同但不包括任何数据的新表，对该表进行DDL操作后，再将数据逐步复制到该表中，然后同时修改原始表和新表的名字，最后将原始表删除。

（2）网络延迟

• 减小单事务处理的数据量以减少产生的日志文件的大小
• 减少主上所同步的slave数量（最好不要超过5个）

（3）Master多线程写入，Slave单线程恢复引起的延迟

• 使用5.7之后的多线程复制（5.6不成熟）
• 使用MGR复制架构（多写复制的集群）

5、MGR
5.7之后引入了全新的主从复制技术：MGR，这是将来的趋势。
MGR(MySQL Group Replication)是一个MySQL Server插件，可用于创建弹性，高可用MySQL集群方案。有一个内置的组成员服务，在任何给定的时间点,保持组的视图一致并可供所有服务器使用。服务器可以离开并加入组，视图也会相应更新。当成员离开组，故障检测机制会检测到此情况并通知组视图已更改。

简单的说：复制组就是一组彼此之间可以通过消息通讯来保持数据一致性的mysql服务器，而在复制组中每一台服务器都可以独立的完成数据的更新。也就是说，在MGR中，可以实现多个组同时对数据的修改。
MGR是在Mysql异步复制之上使用Paxos协议来实现的。 Paxos是分布式一致性的算法，主要就是为了解决多个节点并发操作数据时的读写数据一致性的问题。

MGR是一种不同于异步复制的多Master复制集群。MGR和异步复制的主要区别还在于：MGR中已经不存在slave角色了，每个节点都需要安装Group_replication插件

MGR的复制原理：

其中一个节点发送修改数据的请求（上图中是master1发起），向其他节点广播，只有当集群中大多数节点都认可了这些数据修改后才能够执行，MGR可以保证事务在各个集群节点中进行的顺序一致的，但是事务在各个节点中是分别提交的，所以，各个节点的数据还是会有不同步的情况，但是这个不同步的情况已经很小很小了，比异步复制的情况好很多。

MGR中支持多种模式，官方更推荐使用单主模式，因为在多主模式下更容易出现死锁和写冲突，并且多主模式并不能减少写负载。

单主模式下：只有一个节点可以处理写和读请求，其他节点负责读请求（只读），MGR集群会自动选出主节点，宕机后重新选择主节点。
设置group_replication_single_primary_mode=on; 该变量在运行状态下是不能修改的，并且各个节点都需要设置该参数。

多主模式：
每个节点都可以处理 / 同时处理写和读请求，多个事务时的顺序和冲突就难免出问题，一旦出现问题后，MGR会自动选择某个事务回滚。所以建议使用单主模式。

MGR所需要的资源

MGR复制架构的配置步骤：

• 安装group_replication插件
  mysql>install plugin group_replication soname ‘group_replication.so’; // 每个节点都需要安装

• 在第一个实例上新建复制用户和权限

• 配置第一个组实例
  mysql>show variables like ‘transaction_write_set_extraction’; // 事务的加密算法，设置成"XXHASH64"，解决冲突的加密算法
  mysql>show variable like ‘binlog_checksum’; // 默认为CRC32，设置该参数为none
  mysql>set persist binlog_checksum=none;
  mysql>show variable like ‘group_replication_group_name’; // 查看默认组名称
  mysql>select uuid(); // 一般使用UUID函数重新生成一个
  mysql>show variable like ‘group_replication_local_address’; // 设置本地服务IP/port，此时的端口是内部通信端口
  mysql>set global group_replication_bootstrap_group=on; // 初始化主复制集群，在第一次启动后可以设置为off
  mysql>start group_replication; // 启动主复制集群
  mysql>set global group_replication_bootstrap_group=off; // 已经启动过了，就可以关闭了
  mysql>use performance_schema; // 切换库，找主从复制信息表replication_group_member_stats
  mysql>select * from replication_group_member_stats\G // 查看主从复制信息

• 把其他实例加入组
  mysql>change master to master_user=‘repl’ password=‘123456’ for channel ‘group_replication_recovery’; // 同步数据
  mysql>show slave status for channel ‘group_replication_recovery’\G;
  mysql>start group_replication;

MGR复制架构的优点：

• Group Replication组内成员间基本无延迟；（事务的提交是在各个节点中，并不能保证绝对的同步）
• 可以支持多写操作，读写服务高可用；
• 数据强一致，可以保证不丢失事务；

MGR复制架构的缺点：

• 只支持InnoDB存储引擎的表，并且每个表上必须有一个主键
• 在单主模式下很难确认下一个primary
• 只能在grid模式的复制形式下，且日志格式必须为row

MGR复制架构的适用场景：

• 对主从延迟十分敏感的应用场景
• 希望可以对读写提供高可用的场景
• 希望可以保证数据强一致性的场景

介绍和使用比较全面的连接如下：
https://blog.csdn.net/L835311324/article/details/89345195

6、如何解决数据库读写负载过大的问题

如何解决读负载大的问题
读写分离，映入中间层MyCat、ProxySQL、Maxscale

• 为原DB增加Slave服务器
• 进行读写分离，把读分担到Slave
• 增加数据库中间层，进行负载均衡

如何解决写负载大的问题

七 备份恢复

1、如何对数据进行备份

逻辑备份：转储sql语句，速度较慢
物理备份：直接copy数据文件备份，速度很快

• 全量备份
• 增量备份
• 差异备份

常用的备份工具：

• mysqldump：最常用的逻辑备份工具，支持全量备份以及条件备份
  优点：备份结果为可读sql文件，可用于夸版本夸平台的数据恢复；易于压缩；自带工具，无需安装。
  缺点：只能单线程执行备份恢复任务，速度偏慢；为完成一致性备份需要对备份表加锁，容易造成阻塞；会对Innodb buffer pool造成污染。
  mysqldump --help查看如何使用
  可以使用–where进行有条件的备份

• mysqlpump：多线程逻辑备份工具，mysqldump的增强版本（5.7引入）
  优点：mysqlpump语法同msyqldump高度相似；支持基于库和表的并行备份，可以提高逻辑备份的性能；支持使用ZLIB和Lz4算法对备份进行压缩；
  缺点：基于表进行并行备份，对于大表来说性能较差；5.7.11之前版本不支持一致性并行备份；会对Innodb buffer pool造成污染。
  mysqlpump --help | more // 查看帮助信息
  示例：https://www.cnblogs.com/zhoujinyi/p/5684903.html
  mysqlpump --compress-output=zlib --set-gtid-purged=off --databases 库名 > xxx.zlib
  利用mysql自带的解压缩工具：zlib-decompress xxx.zlib 输出的名称.sql

• xtrabackup：Innodb在线物理备份工具，支持多线程和增量备份。（Percona公司开发）
  优点：支持Innodb存储引擎的在线热备份，对Innodb缓冲没有影响(启动时记录redolog的序列号，后台新线程监视有没有变化的redolog)；支持并行对数据库的全备份和增量备份；备份和恢复的效率比逻辑备份更高。
  缺点：做单表恢复时比较复杂；完整的数据文件copy，故备份文件较大；对跨平台和数据库的备份恢复支持度不如逻辑备份。（需要安装）
  示例：https://www.cnblogs.com/linuxk/p/9372990.html

2、如何对Mysql进行增量备份和恢复

• 逻辑备份+二进制日志
• xtrabackup

3、如何对binlog进行备份

• 利用系统命令直接复制保存(非正在使用的)
• 使用mysqlbinlog命令在线实时备份
  mysqlbinlog --raw --read-from-remote-server --stop-never --host 备份主机IP --port 3306 -u repl -p 123456 起始二进制日志文件名
  repl用户必须要具有replication slave权限

八 Mysql管理与监控

1、MySQL常见监控指标

性能指标：

• QPS: 数据库每秒处理的请求数量
• TPS:数据库每秒处理的事务数量，一般远远小于QPS
• 并发数：数据库实例当前并行处理的会话数量，反应当前Mysql处理是否繁忙的指标。每一个处理中的mysql线程都会占用一个CPU的内核。
• 连接数：连接到数据库会话的数量。这个连接数不等于并发数。
• 缓存命中率：Innodb的缓存命中率。决定了查询是否可以快速的返回处理的结果给调用的程序。

功能性指标：

• 可用性：数据库是否可以正常对外提供服务
• 阻塞：当前是否有阻塞的会话（一个事务锁住了其他事务同样需要的资源）
• 死锁：当前事务是否产生了死锁（两个不同的事务相互锁住了对方需要的资源）。
• 慢查询：实时慢查询监控
• 主从延迟：数据库主从延迟时间
• 主从状态：数据库主从复制链路是否正常

死锁的危害并没有阻塞的危害大，因为mysql会定期的检查死锁，一旦发现有死锁，mysql会自行处理（选择一个资源占用较少的进行回滚，从而使得另一个事务继续执行），但对业务会有一定的影响。

2、如何监控QPS

show global status like 'Com%';	//服务器启动以来请求的数量
sum(Com_xxxx)		// 就是服务器启动以来某个操作的数量
mysql已经提供了查询这些指标的统计功能
show global status like 'Queries';	// 可以每隔多长时间采样一次，再除时间，就能显示出目前的QPS值

QPS=(Queries2 - Queries1) / 采样间隔

例如：
show global status where variable_name in(‘Queries’, ‘uptime’) ;

3、如何监控TPS（每秒处理的事务量）和并发数
show global status where variable_name in(‘com_insert’, ‘com_delete’, ‘com_update’) ;
Tc≈com_insert + com_delete + com_update

TPS≈（Tc2 - Tc1）/ （time2 - time1）

并发数越大服务器的负载就越大，也就越慢。
show global status like ‘Threads_running’;

4、如何监控连接数和Innodb缓存命中率
连接数:
show global status like ‘Threads_connected’;

报警阈值：
Threads_connected / max_connections > 0.8

Innodb缓存命中率：
（Innodb_buffer_pool_read_requests - Innodb_buffer_pool_reads）/ Innodb_buffer_pool_read_requests * 100% > 95%

Innodb_buffer_pool_read_requests代表从缓冲池中读取的次数
Innodb_buffer_pool_reads表示从物理磁盘读取的次数

show global status like ‘Innodb_buffer_pool_read%’;

5、如何监控数据库可用性
周期性的连接数据库并执行：select @@version;
或者在操作系统中执行：mysqladmin -uxxxx -pxxxx -hxxxx ping; // 返回mysqld is alive

6、如何监控阻塞
5.7版本之前，使用下面的语句监控，查询阻塞时间大于30秒的SQL：
select b.trx_mysql_thread_id AS ‘被阻塞的线程’
, b.trx_query AS ‘被阻塞SQL’
, c.trx_mysql_thread_id ‘阻塞线程’
, c.trx_query AS ‘阻塞SQL’
, (UNIX_TIMESTAMP()-UNIX_TIMESTAMP(c.trx_started)) AS ‘阻塞时间’
from information_schema.innodb_lock_waits a
JOIN information_schema.innodb_trx b ON a.requesting_trx_id=b.trx_id
JOIN information_schema.innodb_trx c ON a.blocking_trx_id=c.trx_id
WHERE (UNIX_TIMESTAMP()-UNIX_TIMESTAMP(c.trx_started)) > 30

5.7以及之后的版本，监控语句：
SELECT waiting_pid AS ‘被阻塞的线程’,
waiting_query AS ‘被阻塞的SQL’,
blocking_pid AS ‘阻塞线程’,
blocking_query AS ‘阻塞SQL’,
wait_age AS ‘阻塞时间’,
sql_skill_blocking_query AS ‘建议操作’
FROM sys.innodb_lock_waits
WHERE (UNIX_TIMESTAMP() - UNIX_TIMESTAMP(wait_started)) > 30;

sys.innodb_lock_waits其实就是上面5.7版本以前的视图而已。
建议操作往往是建议杀死某个线程，执行kill 12;即可。

7、如何监控慢查询

• 通过慢查询日志，周期性的人为查看
• 通过information_schema.'PROCESSLIST’表实时监控
  例如：查看查询时间大于60秒，并且处于sleep状态的
  select * from information_schema.PROCESSLIST where time > 60 and COMMAND<> ‘sleep’;

如果查出来只有以下一条是很正常的，event_scheduler该进程是Mysql的后台进程。

8、如何监控主从延迟
在slave上使用：
show slave status \G
看其中参数Seconds_Behind_Master的值

更加准确的方式是使用PT工具：
在master上：周期性的写入数据
pt-heartbeat --user=xx --password=xx -h master --createtable --database xxx --update --daemonize --interval=1;

在slave上：周期性的读取上面语句创建表中的数据
pt-heartbeat --user=xx --password=xx -h slave --database crn --monitor --daemonize --log /tmp/slave_lag.log;

9、如何监控主从状态（链路）
在slave上使用：
show slave status \G
看其中参数Slave_IO_Running和Slave_SQL_Running的值，都为Yes时代表时正常的，否则会在参数Last_Error中看到具体的故障信息。

10、如何监控死锁
show engine innodb status; // 查看最近一次死锁
但是通过这条语句查看到的结果太复杂，不容易看懂，所以有其他方法：

• 使用PT工具
  pt-deadlock-logger u=dab, p=xxx, h=xxxx
  –create-dest-table
  –dest u=dba,p=xxxx,h=xxxx,D=crn,t=deadlock
  上面的语句最后将查询的结果存储到了deadlock表中，create-dest-table是自动建表；dest是要存储到表的信息

• 将死锁记录到错误日志中
  set global innodb_print_all_deadlocks=on;

更好的方式还是使用PT工具。

九 Mysql优化和异常处理思路

1、数据库负载过大
服务器磁盘IO超负荷
存在大量阻塞线程
存在大量并发慢查询
其他原因

慢查询造成磁盘IO爆表情况排查

可能的原因：

• mysql正在输出大量的日志
• mysql正在进行大批量的写
• 慢查询产生了大量的磁盘临时表

如果是慢查询造成的IO爆表：
首先，利用lsof命令查看某个进程打开文件的情况，是否有大量的临时表创建与写入
然后，通过show global status like ‘%tmp%’;执行两次，对created_tmp_disk_tables参数做差，得出是否建立临时表的数量。
最后，优化慢查询，减少磁盘临时表；或者增加tmp_table_size和max_heap_table_size参数的大小，直接建立内存表，而不是临时表。

2、主从数据库无延迟情况下的数据不一致（IO_THREAD和SQL_THREAD状态都为Yes）
相同的查询在主从服务器上查询结果不一致的原因：

• 对从库中的数据进行了修改
• 使用sql_slave_skip_counter或者注入空事务的方式修复错误
• 使用了statement格式的复制（这种格式是分开执行事务的，默认取UUID或者当前时间的列就会出现不一致）

解决思路：

• slave中设置read_only=on
• 设置super_read_only=on
• 使用row格式复制（记录了master的修改，slave中直接应用）
• 使用pt_table_sync这个工具修复数据
  pt_table_sync --execute --charset=utf8 --database=数据库名 --table=表名 --sync-to-master h=从库的地址, u=dba, p=123456;
  可以使用checksum table 库名.表名，查看数据表的唯一值，可以对比主从上的表是否相同。

3、主服务器连接不上

• 主从网络是否畅通(ping IP, telnet ip port)
• 是否存在防火墙，过滤了数据库端口
• 复制链路配置的用户名和密码是否正确、权限

4、主键冲突问题

• 跳过故障数据
• 检查主从数据一致性
• OR直接删除从库主键冲突数据（最好使用这个）

5、数据行不存在

• 跳过故障数据
• pt-table-sync工具(最好使用这个)

6、slave宕机引起的relay_log损坏

解决方法：

1. 找到已经正确同步的日志点
2. 使用reset slave删除relay_log
3. 在正确同步日志点后重新同步日志

7、优化

十、 附

1、SQL语言共分为四大类：数据定义语言DDL，数据操纵语言DML，数据查询语言DQL，数据控制语言DCL。举例如下：
DDL：create
DML：insert、delete、update
DQL：select
DCL：grant