MySQL运维中的疑难问题解读

MySQL运维中的疑难问题解读1、性能问题排查Ｑ：MySQL如何排查CPU占用高的问题？问题描述：重点是关于通过哪些系统表或者常用的sql来确定导致问题的sql？感觉这方面的资料很少，不像Oralce的那些v$视图，网上资料很多，sql语句也很多。答：可以通过将系统线程号与SQL对应来查看top-H-p<mysqld进程id>PIDUSERPRNIVIRTRESSHRS%CPU%MEMTIMECOMMAND23974mysql2001658m358m12mR99.91.10:05.52mysqld12295mysql2001658m358m12mS0.31.10:02.44mysqldSELECTa.THREAD_OS_ID,b.user,b.host,b.db,mand,b.time,b.state,FROMperformance_schema.threadsa,information_cesslistbWHEREb.id=cesslist_id;THREAD_OS_IDUSERHOSTdbcommandTIMEstateinfo**23974**root01:21466sysQUERY29SendingDATASELECTa.*FROMtesta,testb,testc,testdORDERBYa.valueLIMIT0,1000Ｑ：MySQL数据库内存使用率高，应该如何进行排查？问题描述：内存使用率，通过系统命令能定位到mysql占用的内存高，如何通过系统表或者相关的sql语句，定位到占用内存高的那部分sql？答：MysqlServerMemoryUsage=SumofGlobalBuffers(numberofConnection*Perthreadmemoryvariables)a）单个mysql连接线程的内存消耗统计，这里只是统计分配值（具体驻留内存占用值统计不到）selectb.thd_id,b.user,current_count_used,current_allocated,current_avg_alloc,current_max_alloc,total_allocated,current_statementfrommemory_by_thread_by_current_bytesa,sessionbwherea.thread_id=b.thd_idlimit1;

b）统计top10的bufferpool占用内存的表select*frominnodb_buffer_stats_by_tableorderbypagesdesclimit10;Ｑ：MySQL数据库磁盘IO使用高，请问如何进行排查？问题描述：通过系统能确定是数据库的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_DIRECTinnodb_file_per_table=1innodb_doublewrite=1delay_key_writeinnodb_read_io_threadsinnodb_read_io_threadsinnodb_io_capacityinnodb_flush_neighborssync_binlog主要关注：sync_binlog建议：最好部署相关的监控平台或者对比历史性能记录，结合业务以及负载来分析。2、优化方法Q：MySQL优化的常用方法有哪些？答：一、最常见是慢查询优化1、打开慢查询记录，设置记录SQL的最短时间2、使用pt工具，分类统计慢查询语句3、针对执行次数多或者时间长的语句进行优化（索引优化、SQL改写、业务逻辑优化）ps:也可以在系统表中，查看全表扫描多的表等二、配置文件优化1、内存使用量2、各种方面写盘策略Q：MySQL中执行计划如何解读？问题描述：1:执行计划如何解读？db2中按照从下往上，从左到右的顺序来解读2：执行计划中需要关注的特殊标识有哪些？例如：usingwhereusingfilesortUsingtemporary等等答：1、执行顺序，看ID列id值相同执行顺序从上到下。id值不同时id值大的先执行。2、关注的特殊标识SELECT_TYPE--执行查询类型，不同类型对应的Type：访问类型，很重要possible_keys：索引使用关于explain输出参数，可参考官方文档：以MySQL5.7为例/doc/refman/5.7/en/explain-output.htmlQ：MySQL中关于表维护的操作（提升性能相关的）有哪些？问题描述：MySQL中关于表维护的操作（提升性能相关的）有哪些？例如db2中的表重组，db2rbind绑定包等操作答：MySQL的表维护语句：ANALYZETABLE：更新表统计信息。执行该语句的时候innodb及myisam表会加上读锁，停止数据更新。该语句支持innodb，myisam及ndb表，针对myisam表，该语句等同myisamchk--analyzeOPTIMIZETABLE:整理数据,表碎片CHECKTABLE:用来检查数据库表和索引是否损坏REPAIRTABLE：checktable语句可以检查一个表中的的问题，若表或索引损坏，可以使用repairtable语句尝试修正它Q：有哪些工具可以帮助优化MySQL的？答1：SQL优化主要还是看经验和对慢查询梳理。配置文件优化，一般来说就几个参数需要优化，其他可以不动/major/MySQLTuner-perl答2：以下工具可以参考：pt-mysql-summarypt-variable-advisorpt-duplicate-key-checkerpt-deadlock-logger

或者tuning-primer.sh3、高可用问题Q：MySQL原厂有Ｏracle的cluster集群，有哪些主流的开源适合高并发集群呢？答：一、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为NewMaster之外（漂虚拟IP），还会自动让其他Slave与NewMaster建立复制关系。MHAManager可以单独部署在一台独立的机器上，并管理多个master-slave集群。但是，只支持一主多从架构，集群中必须最少有三台数据库服务器，要保持切换对应用透明，依然依赖于VIP，不适用于大规模集群部署，配置比较复杂。且MHA管理节点本身的HA无法保证。MySQL5.7之前数据不丢的前提是Master服务器还可以被MHAManager进行SSH连接，通过应用保存的binlog的方式来保证。MySQL5.7之后通过无损复制，仅仅是减少了丢数据的可能性，假如此时的状态为切成异步的状态，那就和之前一样了（可以设置超时的时间很大）；当Master恢复的时候，最后一部分数据是否需要Flashback，MHA也是不负责这个事情，需要人工介入。5、基于zookeeper/consul的高可用方案借助zookeeper组件，结合MHA或者其他高可用架构场景，实现强制一致性的高可用集群分布，可适应大规模高并发场景，需要一定的技术实力，引入zookeeper，架构复杂度上升，但是整体扩展性非常好，可以管理大规模集群。保证了整个系统的高可用，主从的强一致依赖于MySQL本身，比如半同步，或者外围工具的回补策略6、基于MMM高可用方案MMM提供了MySQL主主复制配置的监控、故障转移和管理的一套可伸缩的脚本套件。在MMM高可用方案中，典型的应用是双主多从架构，通过MySQLreplication技术可以实现两个服务器互为主从，且在任何时候只有一个节点可以被写入，避免了多点写入的数据冲突。同时，当可写的主节点故障时，MMM套件可以立刻监控到，然后将服务自动切换到另一个主节点，继续提供服务，从而实现MySQL的高可用。可以灵活选择VIP方案或者全局目录数据库方案(更改MasterIP映射)来进行切换。MMM提供了自动和手动两种方式移除一组服务器中复制延迟较高的服务器的虚拟ip，同时它还可以备份数据，实现两节点之间的数据同步等。由于MMM无法完全的保证数据一致性，所以MMM适用于对数据的一致性要求不是很高，但是又想最大程度的保证业务可用性的场景。对于那些对数据的一致性要求很高的业务，非常不建议采用MMM这种高可用架构。7、基于中间件proxy高可用组件的集群方案中间件：阿里Cobar、MyCAT360Atlas淘宝Tddl网易CutusMySQLProxyProxySQL(Percona)KingShardMaxScale(MariaDB)OneProxy切换对应用透明,可扩展性强，方便分片扩展,可以跨机房部署切换,但是需要有一定自研能力，或者选择有完整的后期技术支持的中间件，以及社区活跃度较高的，有一定能力，后期可自研或者自己优化开发相关的中间件。以适应自身的业务需求。二、集群/分布式基于集群或者分布式的HA包括：MysqlGroupReplicationMysqlInnoDBClusterPerconaXtraDBClusterMariaDBGaleraCluster1、MGR关于MGR原理，可参考可以参考阿里的数据库内核月报，关于mgr的文档。/monthly/2017/08/01/基于传统异步复制和半同步复制的缺陷——数据的一致性问题无法保证，MySQL官方在5.7.17版本正式推出组复制（MySQLGroupReplication，简称MGR）。由若干个节点共同组成一个复制组，一个事务的提交，必须经过组内大多数节点（N/21）决议并通过，才能得以提交。如上图所示，由3个节点组成一个复制组，Consensus层为一致性协议层，在事务提交过程中，发生组间通讯，由2个节点决议(certify)通过这个事务，事务才能够最终得以提交并响应。引入组复制，主要是为了解决传统异步复制和半同步复制可能产生数据不一致的问题。组复制依靠分布式一致性协议(Paxos协议的变体)，实现了分布式下数据的最终一致性，提供了真正的数据高可用方案。2、MySQLInnoDBClusterCluster解决方案其实是由MySQL的几个不同产品和技术组成的，比如MySQLShell,MySQLRouter,GroupReplication.一组MySQL服务器可以配置为一个MySQL集群。在默认的单主节点模式下，集群服务器具有一个读写主节点和多个只读辅节点。辅助服务器是主服务器的副本。客户端应用程序通过MySQLRouter连接到主服务程序。如果主服务连接失败，则次要的节点自动提升为主节点，MySQLRouter请求到新的主节点。InnoDBCluster不提供NDBCluster支持3、PerconaXtraDBCluster官网地址：/doc/percona-xtradb-cluster/5.7/intro.html节点在接收sql请求后，对于ddl操作，在commit之前，由WSREPAPI调用galera库进行集群内广播，所有其他节点验证成功后事务在集群所有节点进行提交，反之rollback。pxc保证整个集群所有数据的强一致性，满足CAP理论中满足：Consistency和Availability。PXC提供的特性同步复制，事务要么在所有节点提交或不提交多主复制，可以在任意节点进行写操作在从服务器上并行应用事件，真正意义上的并行复制节点自动配置数据一致性，不再是异步复制限制：只支持INNODB表不允许大事务的产生（否则的话后果很严重）写性能取决于最差的节点不能解决热点更新问题乐观锁控制对于写密集型应用需要控制单个节点的大小，单个节点数据越大，新加节点如果采用自动添加可能产生很大抖动（添加节点建议用备份或者备份binlog进行IST(IncrementalStateTransfer)增量同步Ｑ：目前银行主流的MySQL高可用采用哪种方式，MHA还是MGR？问题描述：目前银行主流的MySQL高可用采用哪种方式？MHA还是MGR，各自的优缺点是什么？各自有哪些坑需要注意？答：银行主流何种MySQL高可用方案不太了解。可能是主从中间件自研套件的模式。由于MGR技术相对较新，目前使用MHA更多。但个人认为，MGR或者基于此的innodbcluster架构（或替代方案）会成为未来主流。MHA：优点：成熟稳定，自动切换主从，主节点宕机后尽可能少丢失数据（自动抓取未复制的binlog）。缺点：管理节点单点、可能脑裂、可能有不必要切换、还是有丢数据风险、组件多维护相对麻烦MGR：优点：基于paxos的高可用架构，支持多主（不建议），强一致缺点：需要innodb引擎（丢业务有改造代价），应用端没有自动切换（可以通过中间件解决），技术太新可能有未知bug其他的话还有PXC，但是因为性能问题不太建议。4、安全防范Q：如何做到数据库账号权限的精细化管理？答：一定是做到对权限的全方位掌控。根据账户的不同类型，以前缀区分。简单的分类，分为业务账户和实名账户。细分来讲，业务账号分为网站应用、手机应用、报表应用、服务应用、查询服务，实名账户可以跟踪到具体的员工。网站应用(web_业务简称)手机应用(mob_业务简称)报表应用(rep_业务简称)服务应用(dae_业务简称)查询服务(sea_业务简称)实名查询(dev_姓名拼音)业务账号权限最大到SELECT、UPDATE、DELETE和INSERT，查询服务和实名查询账户只能有查询权限。每个用户只有一个密码，授权时需要知悉此用户是否存在，如果存在，使用旧密码授权，如果不存在，生成随机密码进行授权。实名权限只能通过堡垒机或者跳板机进行查询，堡垒机有用户登录和执行SQL日志。线上IDC数据库只允许线上Web机连接，不允许测试机连接。员工申请权限需要工单申请，授权只能DBA操作。DBA需要做好权限控制，相关业务负责人可以申请较高权限，但需要邮件抄送上一级领导进行审批。DBA有一套完整的元数据库，里面记录了所有的用户相关信息，此数据库重要级别最高，做好安全控制。用户的密码需要足够复杂，而且有一套完整的随机密码生成规则。业务方通知业务账户存在异常，需要制定快速更改账户的流程。员工申请的临时高权账号，需要有备案，需要设置密码过期时间，而且需要制定回收流程。MySQLroot密码只有DBA拥有，而且不允许将此密码保存在任何云笔记或者云存储上，只能保存到本地。另外，定期修改MySQLroot密码。通过终端进入MySQL，不允许将密码明文显示。用户授权操作建议在Web页面完成，需要做好安全控制。此项也就是DB运维管理平台，需要编码实现。做好数据备份，可以在误操作最快恢复数据。如有可能，在新业务上线MySQL审计方案，可以通过init-connect参数access_logbinlog实现审计。关于精细化，主要是各个权限分配细致，做到，不重复，其次是权限的定义明确，该给什么权限给什么权限，不存在模糊权限，最后是权限的记录，做到从权限开始，审批，授权，收回，删除等一整套的规章流程，最重要的是一个精细化的思想，做到心中有数。Q：如何做到数据库账号权限的精细化管理？答：开审计，监控软件商业的现在有很完善的，开源的也有免费的插件，没有最成熟，只有最适合。还想说一下，数据库的安全不止要从数据库方面考虑，还要考虑网络和系统，网络和系统如果在入侵的过程中防不住了，数据库层次的防御力也有限，在前边两个层次就要做到万无一失才对，数据库的安全只是针对数据，针对一些sql注入等等进行一些安全配置。还要做好备份，主从，异地备，高可用等，其实这些都可以算在数据库的安全里边，特别是MySQL，作为一个DBA，也许我们做不到万无一失，但我们要用一万种方法来防止出问题，能考虑到的，能做到的，我们都要用上。需要确定你当前所想要达到的目标，其次是对业务的影响。目前常规是通过数据库防火墙的策略规则，进行告警处理，主要的还是事后审计的报表分析。5、迁移问题Q：使用MySQL替换Oracle20TB左右的库，如何设计才能确保性能和高可用？答1：建议分表分库，建立好相应的索引，使用多节点主从heartbeat/keepalived/MHA/MMM等等一类的方案来保证高可用。答2：首先需要明确ORACLE20TB!=#MYSQL20T其次

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等以上只是简单的一点看法Q：Db2迁移到MySQL，有什么好的工具与方法？答：Db2迁移到MySQL的工作难点、问题还真不在工具上！最大问题在Db2应用的迁移上，估计Db2应用重构工作量、难度，是你们没法想象的！也正因如此，想把Oracle这种迁移到国产平台，难度很大！首先，Db2中的数据即使迁移到MySQL中。但是，Db2中复杂的SQL、Xquery、SQLPL存储过程等如何重写，业务程序中依赖Db2的部分如何重构，都是问题！Q：寻求TB级SQLserver和

MySQL数据高效迁移工具或方案？问题描述：数据量为TB级，分表分库，大概120个库左右，后台开发用的.net，所以前期数据处理什么的都是在sqlserver上进行，之后再迁移到mysql上，所以要经常性的在两种数据库间迁移数据，前面用kettle这类传统的ETL工具来做数据抽取，但是速度慢，品牌多，配置起来也麻烦，想问下各位大佬有没有什么高效的数据迁移工具或者方案没有？答1：针对特定的规则逻辑还是自己开发工具吧，直接文本的导出导入会更快，对于你们经常性的迁移，没有什么太适合的工具。kettle速度慢，品牌多，配置起来也麻烦

应该是你的使用姿势不对。答2：楼主明确一下每天的变化的数据有多大，如果只是1-10G这样一个量级，数据抽取工具随便哪一个都能应付过来，不建议超过2个，太多了维护成本过高。6、其他问题Q：MySQL适用的场景是什么？问题描述：MySQL有很多特性，不容易掌