MySQL 运维中的疑难问题解读
【分享者】刘诚杰、renou2012、岳彩波、冯帅、ACDante、zymh_zy、counship 、大力水手、韩成亮、大张猥、王立进、xuchenliang等
1、性能问题排查
问题描述：重点是关于通过哪些系统表或者常用的sql来确定导致问题的sql？感觉这方面的资料很少，不像Oralce的那些v$视图，网上资料很多，sql语句也很多。
答：可以通过将系统线程号与SQL对应来查看
top -H -p <mysqld进程id>
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
23974 mysql 20 0 1658m 358m 12m R 99.9 1.1 0:05.52 mysqld
12295 mysql 20 0 1658m 358m 12m S 0.3 1.1 0:02.44 mysqld
....
SELECT a.THREAD_OS_ID,b.user,b.host,b.db,b.command,b.time,b.state,b.info
FROM performance_schema.threads a,information_schema.processlist b
WHERE b.id = a.processlist_id;
THREAD_OS_ID USER HOST db command TIME state info
** 23974 ** root 10.10.18.201:21466 sys QUERY 29 Sending DATA SELECT a.* FROM test a,test b,test c ,test d ORDER BY a.value LIMIT 0, 1000
....
问题描述：内存使用率，通过系统命令能定位到mysql占用的内存高，如何通过系统表或者相关的sql语句，定位到占用内存高的那部分sql？
答：Mysql Server Memory Usage= Sum of Global Buffers + (number of Connection * Per thread memory variables)
a）单个mysql连接线程的内存消耗统计，这里只是统计分配值（具体驻留内存占用值统计不到）
select b.thd_id, b.user, current_count_used,current_allocated, current_avg_alloc, current_max_alloc,total_allocated,current_statement from memory_by_thread_by_current_bytes a,session b where a.thread_id = b.thd_id limit 1;
b）统计top 10的buffer pool占用内存的表
select * from innodb_buffer_stats_by_table order by pages desc limit 10;
问题描述：通过系统能确定是数据库的IO读写高，有哪些系统表或者sql联合起来可以把关键的sql定位出来？
答：mysql5.7版本为例，结合performance_schema来查看MySQL数据库的各种指标。相当于Oracle数据库中的各种性能视图，可以查看几乎所有的数据库状态。
IO的话，可以查看这张表：
performance_schema.file_instances：列出了文件I / O操作及其相关文件的工具实例
排查思路：
1、慢SQL排除
2、硬件问题-RAID降级，磁盘故障等排除
2、innodb_log、innodb_buffer_pool_wait相关配置和等待
3、IO相关参数配置
innodb_flush_method = O_DIRECT
innodb_file_per_table = 1
innodb_doublewrite = 1
delay_key_write
innodb_read_io_threads
innodb_read_io_threads
innodb_io_capacity
innodb_flush_neighbors
sync_binlog
主要关注：sync_binlog
建议：最好部署相关的监控平台或者对比历史性能记录，结合业务以及负载来分析。
2、优化方法
Q：MySQL优化的常用方法有哪些？
答：一、最常见是慢查询优化
1、打开慢查询记录，设置记录SQL的最短时间
2、使用pt工具，分类统计慢查询语句
3、针对执行次数多或者时间长的语句进行优化（索引优化、SQL改写、业务逻辑优化）
ps:也可以在系统表中，查看全表扫描多的表等
二、配置文件优化
1、内存使用量
2、各种方面写盘策略
问题描述：1:执行计划如何解读？
db2中按照从下往上，从左到右的顺序来解读
2：执行计划中需要关注的特殊标识有哪些？
例如：using where
using filesort
Using temporary
等等
答：
1、执行顺序，看ID列
id值相同执行顺序从上到下。
id值不同时id值大的先执行。
2、关注的特殊标识
SELECT_TYPE--执行查询类型，不同类型对应的
Type：访问类型，很重要
possible_keys：索引使用
关于explain输出参数，可参考官方文档：以MySQL5.7为例
https://dev.mysql.com/doc/refman/5.7/en/explain-output.html
问题描述：MySQL中关于表维护的操作（提升性能相关的）有哪些？例如db2中的表重组，db2rbind 绑定包等操作
答：MySQL的表维护语句：
ANALYZE TABLE：更新表统计信息。执行该语句的时候innodb及myisam表会加上读锁，停止数据更新。该语句支持innodb，myisam及ndb表，针对myisam表，该语句等同myisamchk --analyze
OPTIMIZE TABLE:整理数据,表碎片
CHECK TABLE:用来检查数据库表和索引是否损坏
REPAIR TABLE：check table语句可以检查一个表中的的问题，若表或索引损坏，可以使用repair table语句尝试修正它
答1：SQL优化主要还是看经验和对慢查询梳理。
配置文件优化，一般来说就几个参数需要优化，其他可以不动
https://github.com/major/MySQLTuner-perl
答2：以下工具可以参考：
pt-mysql-summary
pt-variable-advisor
pt-duplicate-key-checker
pt-deadlock-logger
或者
tuning-primer.sh
3、高可用问题
答：一、MySQL高可用方案
MySQL以及各种开源数据库，也有自身的集群方案，但是大多需要和业务以及借助第三方工具来实现。或者通过分布式来均衡高并发。主要的高可用集群架构可以分为如下几种：
1、基于共享存储的高可用方案--SAN
基于共享存储的高可用，及使用传统的基于SAN共享存储，结合开源的Keeplive做主从同步，可避免除存储外的组件损坏引起的宕机，部署相对简单，对应用透明，但是存储时单点，且存在性能瓶颈
2、基于磁盘复制的高可用方案-DRBD
保证主备的数据一致性，不依赖共享存储，此方案处理failover的方式上依旧需要借助主机层面的高可用组件，如keeplive，Heartbeat等。不同的是，在数据共享方面，采用了基于块级别的数据同步软件DRBD来实现，，但是可扩展性较差。它并不共享存储，而是通过服务器之间的网络复制数据。
适用于数据库访问量不太大，短期内访问量增长不会太快，对数据库可用性要求非常高的场景。
3、基于MySQL自身的主从复制-Replication
基于MySQL自身的主从复制，5.7以后的GTID，以及之前的replication。主从复制，部署简单，但是只能有一个Master进行读写，其余都为备库，还需要结合业务。并发量不大的情况下，可采取主从，管理简单。
4、MHA高可用方案
MHA是一套MySQL高可用管理软件，除了检测Master宕机后，提升候选Slave为New Master之外（漂虚拟IP），还会自动让其他Slave与New Master 建立复制关系。MHA Manager可以单独部署在一台独立的机器上，并管理多个master-slave集群。但是，只支持一主多从架构，集群中必须最少有三台数据库服务器，要保持切换对应用透明，依然依赖于VIP，不适用于大规模集群部署，配置比较复杂。且MHA管理节点本身的HA无法保证。
MySQL 5.7 之前数据不丢的前提是Master服务器还可以被MHA Manager进行SSH连接，通过应用保存的binlog 的方式来保证。
MySQL 5.7 之后通过无损复制， 仅仅是减少了丢数据的可能性，假如此时的状态为切成异步的状态，那就和之前一样了（可以设置超时的时间很大）；当Master恢复的时候，最后一部分数据是否需要Flashback，MHA也是不负责这个事情，需要人工介入。
5、基于zookeeper/consul的高可用方案
借助zookeeper组件，结合MHA或者其他高可用架构场景，实现强制一致性的高可用集群分布，可适应大规模高并发场景，需要一定的技术实力，引入zookeeper，架构复杂度上升，但是整体扩展性非常好，可以管理大规模集群。保证了整个系统的高可用，主从的强一致依赖于MySQL本身，比如半同步，或者外围工具的回补策略
6、基于MMM高可用方案
MMM提供了MySQL主主复制配置的监控、故障转移和管理的一套可伸缩的脚本套件。在MMM高可用方案中，典型的应用是双主多从架构，通过MySQL replication技术可以实现两个服务器互为主从，且在任何时候只有一个节点可以被写入，避免了多点写入的数据冲突。同时，当可写的主节点故障时，MMM套件可以立刻监控到，然后将服务自动切换到另一个主节点，继续提供服务，从而实现MySQL的高可用。可以灵活选择VIP方案或者全局目录数据库方案(更改Master IP映射)来进行切换。
MMM提供了自动和手动两种方式移除一组服务器中复制延迟较高的服务器的虚拟ip，同时它还可以备份数据，实现两节点之间的数据同步等。由于MMM无法完全的保证数据一致性，所以MMM适用于对数据的一致性要求不是很高，但是又想最大程度的保证业务可用性的场景。对于那些对数据的一致性要求很高的业务，非常不建议采用MMM这种高可用架构。
7、基于中间件proxy高可用组件的集群方案
中间件：
阿里 Cobar、MyCAT
360 Atlas
淘宝 Tddl
网易 Cutus
MySQLProxy
ProxySQL(Percona)
KingShard
MaxScale(MariaDB)
OneProxy
切换对应用透明,可扩展性强，方便分片扩展,可以跨机房部署切换,但是需要有一定自研能力，或者选择有完整的后期技术支持的中间件，以及社区活跃度较高的，有一定能力，后期可自研或者自己优化开发相关的中间件。以适应自身的业务需求。
二、集群/分布式
基于集群或者分布式的HA包括：
Mysql Group Replication
Mysql InnoDB Cluster
Percona XtraDB Cluster
MariaDB Galera Cluster
1、MGR
关于MGR原理，可参考可以参考阿里的数据库内核月报，关于mgr的文档。http://mysql.taobao.org/monthly/2017/08/01/
基于传统异步复制和半同步复制的缺陷——数据的一致性问题无法保证，MySQL官方在5.7.17版本正式推出组复制（MySQL Group Replication，简称MGR）。由若干个节点共同组成一个复制组，一个事务的提交，必须经过组内大多数节点（N / 2 + 1）决议并通过，才能得以提交。如上图所示，由3个节点组成一个复制组，Consensus层为一致性协议层，在事务提交过程中，发生组间通讯，由2个节点决议(certify)通过这个事务，事务才能够最终得以提交并响应。
引入组复制，主要是为了解决传统异步复制和半同步复制可能产生数据不一致的问题。组复制依靠分布式一致性协议(Paxos协议的变体)，实现了分布式下数据的最终一致性，提供了真正的数据高可用方案。
2、MySQL InnoDB Cluster
Cluster解决方案其实是由MySQL的几个不同产品和技术组成的，比如MySQL Shell, MySQL Router, Group Replication. 一组MySQL服务器可以配置为一个MySQL集群。在默认的单主节点模式下，集群服务器具有一个读写主节点和多个只读辅节点。辅助服务器是主服务器的副本。客户端应用程序通过MySQL Router连接到主服务程序。如果主服务连接失败，则次要的节点自动提升为主节点，MySQL Router请求到新的主节点。InnoDB Cluster不提供NDB Cluster支持
3、Percona XtraDB Cluster
官网地址：https://www.percona.com/doc/percona-xtradb-cluster/5.7/intro.html
节点在接收sql 请求后，对于ddl 操作，在commit之前，由WSREP API 调用galera 库进行集群内广播，所有其他节点验证成功后事务在集群所有节点进行提交，反之rollback。pxc 保证整个集群所有数据的强一致性，满足CAP理论中满足：Consistency 和 Availability。
PXC提供的特性
同步复制，事务要么在所有节点提交或不提交
多主复制，可以在任意节点进行写操作
在从服务器上并行应用事件，真正意义上的并行复制
节点自动配置
数据一致性，不再是异步复制
限制：
只支持INNODB表
不允许大事务的产生（否则的话后果很严重）
写性能取决于最差的节点
不能解决热点更新问题
乐观锁控制
对于写密集型应用需要控制单个节点的大小，单个节点数据越大，新加节点如果采用自动添加可能产生很大抖动（添加节点建议用备份或者备份+binlog 进行IST(Incremental State Transfer)增量同步
Ｑ：目前银行主流的MySQL高可用采用哪种方式，MHA还是MGR？
问题描述：目前银行主流的MySQL高可用采用哪种方式？MHA还是MGR，各自的优缺点是什么？各自有哪些坑需要注意？
答：银行主流何种MySQL高可用方案不太了解。可能是主从+中间件+自研套件的模式。
由于MGR技术相对较新，目前使用MHA更多。但个人认为，MGR或者基于此的innodb cluster架构（或替代方案）会成为未来主流。
MHA：
优点：成熟稳定，自动切换主从，主节点宕机后尽可能少丢失数据（自动抓取未复制的binlog）。
缺点：管理节点单点、可能脑裂、可能有不必要切换、还是有丢数据风险、组件多维护相对麻烦
MGR：
优点：基于paxos的高可用架构，支持多主（不建议），强一致
缺点：需要innodb引擎（丢业务有改造代价），应用端没有自动切换（可以通过中间件解决），技术太新可能有未知bug
其他的话还有PXC，但是因为性能问题不太建议。
4、安全防范
答：一定是做到对权限的全方位掌控。
根据账户的不同类型，以前缀区分。简单的分类，分为业务账户和实名账户。细分来讲，业务账号分为网站应用、手机应用、报表应用、服务应用、查询服务，实名账户可以跟踪到具体的员工。
网站应用(web_业务简称)
手机应用(mob_业务简称)
报表应用(rep_业务简称)
服务应用(dae_业务简称)
查询服务(sea_业务简称)
实名查询(dev_姓名拼音)
业务账号权限最大到 SELECT、UPDATE、DELETE 和 INSERT，查询服务和实名查询账户只能有查询权限。每个用户只有一个密码，授权时需要知悉此用户是否存在，如果存在，使用旧密码授权，如果不存在，生成随机密码进行授权。
实名权限只能通过堡垒机或者跳板机进行查询，堡垒机有用户登录和执行 SQL 日志。
线上 IDC 数据库只允许线上 Web 机连接，不允许测试机连接。
员工申请权限需要工单申请，授权只能 DBA 操作。DBA 需要做好权限控制，相关业务负责人可以申请较高权限，但需要邮件抄送上一级领导进行审批。
DBA 有一套完整的元数据库，里面记录了所有的用户相关信息，此数据库重要级别最高，做好安全控制。
用户的密码需要足够复杂，而且有一套完整的随机密码生成规则。
业务方通知业务账户存在异常，需要制定快速更改账户的流程。
员工申请的临时高权账号，需要有备案，需要设置密码过期时间，而且需要制定回收流程。
MySQL root 密码只有 DBA 拥有，而且不允许将此密码保存在任何云笔记或者云存储上，只能保存到本地。另外，定期修改 MySQL root 密码。
通过终端进入 MySQL，不允许将密码明文显示。
用户授权操作建议在 Web 页面完成，需要做好安全控制。此项也就是 DB 运维管理平台，需要编码实现。
做好数据备份，可以在误操作最快恢复数据。
如有可能，在新业务上线 MySQL 审计方案，可以通过 init-connect 参数 + access_log + binlog 实现审计。
关于精细化，主要是各个权限分配细致，做到，不重复，其次是权限的定义明确，该给什么权限给什么权限，不存在模糊权限，最后是权限的记录，做到从权限开始，审批，授权，收回，删除等一整套的规章流程，最重要的是一个精细化的思想，做到心中有数。
答：开审计，监控软件商业的现在有很完善的，开源的也有免费的插件，没有最成熟，只有最适合。
还想说一下，数据库的安全不止要从数据库方面考虑，还要考虑网络和系统，网络和系统如果在入侵的过程中防不住了，数据库层次的防御力也有限，在前边两个层次就要做到万无一失才对，数据库的安全只是针对数据，针对一些sql注入等等进行一些安全配置。还要做好备份，主从，异地备，高可用等，其实这些都可以算在数据库的安全里边，特别是MySQL，作为一个DBA，也许我们做不到万无一失，但我们要用一万种方法来防止出问题，能考虑到的，能做到的，我们都要用上。
需要确定你当前所想要达到的目标，其次是对业务的影响。
目前常规是通过数据库防火墙的策略规则，进行告警处理，主要的还是事后审计的报表分析。
5、迁移问题
Q：使用MySQL替换Oracle 20TB左右的库，如何设计才能确保性能和高可用？
答1：建议分表分库，建立好相应的索引，使用多节点主从+ heartbeat/keepalived/MHA/MMM等等一类的方案来保证高可用。
答2：首先需要明确 ORACLE 20TB !=# MYSQL 20T
其次 MySQL 替换Oracle 首先需要探讨可行性
还有就是如果使用 MySQL 对于这么大的量需要考虑的一点是冷热数据，这不是单出的分库分表就能解决的，需要根据事情去探讨。
答3：首先要知道这是一个OLAP还是一个OLTP。
是前者的话，数据再大一倍也无妨，毕竟数据的抽取方式要变化，甚至手工作业也能满足；
如果是后者，并且业务很复杂，那做的工作可要多了，先说数据的迁移，数据库中的各种对象（比如函数和过程的改写），再说大一点的查询，分库分表要考虑进来（分区不建议考虑，MySQL的分区表处处受限）。
答4：MySQL高可用架构可以参考MHA/PXC/MGR，根据自己的实际需要进行选择。
数据安全性方面考虑增强半同步，数据库版本建议8.0以上。
MySQL 单实例承担20TB的数据量不是不可以，只不过负担太重了，你需要考虑的有以下几个方面：
1、单实例TPS/QPS限制
2、备份、恢复的影响
3、磁盘容量的规划
4、锁争用、单实例连接数等等
如果全部迁移到MySQL，建议：
1、先垂直拆分，由多个集群承担对应的业务
2、水平拆分，确定分片键，需要多少分片承担压力（单表建议不要超过500w，单实例不要超过500张，单实例数据总容量不要超过1T）
3、由多个从库承担读压力（需要考虑延迟，交易类型一律走主库）
有了高可用，有了拆分，那么还需要中间访问层，目前比较好的开源proxy有：
1、简单读写分离的：ProxySQL、DBLE、Cetus等
2、具备分库分表的：DBLE、Cetus、vitness等
以上只是简单的一点看法
答：Db2 迁移到 MySQL 的工作难点、问题还真不在工具上！最大问题在 Db2 应用的迁移上，估计 Db2 应用重构工作量、难度，是你们没法想象的！也正因如此， 想把 Oracle 这种迁移到国产平台，难度很大！
首先，Db2 中的数据即使迁移到 MySQL 中。但是，Db2 中复杂的 SQL、Xquery、SQLPL存储过程等如何重写，业务程序中依赖 Db2 的部分如何重构，都是问题！
问题描述：数据量为TB级，分表分库，大概120个库左右，后台开发用的.net，所以前期数据处理什么的都是在sql server上进行，之后再迁移到mysql上，所以要经常性的在两种数据库间迁移数据，前面用kettle这类传统的ETL工具来做数据抽取，但是速度慢，品牌多，配置起来也麻烦，想问下各位大佬有没有什么高效的数据迁移工具或者方案没有？
答1：针对特定的规则逻辑还是自己开发工具吧 ，直接文本的导出导入会更快， 对于你们经常性的迁移，没有什么太适合的工具。
kettle 速度慢，品牌多，配置起来也麻烦 应该是你的使用姿势不对。
答2：楼主明确一下每天的变化的数据有多大，如果只是1-10G这样一个量级，数据抽取工具随便哪一个都能应付过来，不建议超过2个，太多了维护成本过高。
6、其他问题
问题描述：MySQL有很多特性，不容易掌控。请问下MySQL适用的场景是什么？数据数量级在什么区间内？运维团队需要什么规模？是否需要有开发专门配合？灾备使用什么模式和工具？
答：不依赖存储过程、函数、触发器的传统oltp场景都适用
数据量建议单实例T级或以内
运维团队任何规模都可以，因为是一个相对轻量级的数据库
需要开发专门配合，毕竟语法和特性每个库都不一样
灾备使用MySQL各类的高可用方案即可，比如主从、mha、mgr等
答1：提供一点思路：
个人认为巡检应该获取的是相对静态的数据，而动态数据应该由监控系统去完成，那么巡检我认为应该可以从以下几个方面去做：
1、服务器配置
2、操作系统配置及重要参数
3、MySQL层配置及重要参数
4、MySQL对象（如表、索引、用户、权限等）
5、MySQL运行时的重要状态（如历史锁等待等等）
答2：
优先查看日志中的报错信息。
然后查看监控系统中的历史数据（各类峰值，骤变值），报警信息。
然后查看慢查询日志。
深入可以看各类锁记录、等待事件等（需要开启统计，并且没有重启过）。
答1：没有相关命令查看索引创建时间，最多在binlog等日志中查看
可以在mysql.innodb_index_stats查看最后更新时间
答2：SHOW INDEX FROM table_name [FROM db_name]，这样子查看下，是不是有相关信息
场景：mysql数据库作为系统数据存储，使用分库分表方式进行数据的分流。按照数据湖方式建设将数据归集到tidb（newsql数据库）。采集方式使用类主从同步近实时采集，省去etl（T+1）。
存在的问题：由于mysql数据库作为系统数据存储，考虑到数据量问题对数据的修改、删除在原数据基础上进行，导致没有历史数据可用。数据分析对历史数据依赖性很强，有没有好的解决方案？
答：一个方法就是区分冷热数据，常规而言业务数据提供实时查询的不会超过6个月，当然如果你的数据量太大，也可以考虑更短，数据的修改分两种，因为定义了冷热数据，热数据在mysql修改，冷数据在tidb修改，然后查询的问题统一从tidb这边提供接口的方式处理。
关于数据分析对历史数据的依赖，可以把历史数据当初ods数据，然后生成各种的dm数据，乃至于dw数据。
觉得本文有用，请转发或点击“在看”，让更多同行看到
资料/文章推荐：
MySQL性能故障优化利器之索引优化
http://www.talkwithtrend.com/Article/244357
MySQL诊断分析低效SQL方法
http://www.talkwithtrend.com/Article/242995
欢迎关注社区 “mySQL”技术主题 ，将会不断更新优质资料、文章。地址：
http://www.talkwithtrend.com/Topic/3873
下载 twt 社区客户端 APP
长按识别二维码即可下载
或到应用商店搜索“twt”
长按二维码关注公众号
*本公众号所发布内容仅代表作者观点，不代表社区立场