MySQL 运维中的疑难问题解读.docx

《MySQL 运维中的疑难问题解读.docx》由会员分享，可在线阅读，更多相关《MySQL 运维中的疑难问题解读.docx（17页珍藏版）》请在优知文库上搜索。

1、【摘要】更多的企业将核心业务运行在MySQ1.之上，与此同时，大数据量大流量带来的性能问题也愈演愈烈，数据库的操作往往成为整个系统的瓶颈，如何使MySQ1.跑得更快已是一项迫在眉睫的任务。本文是针对相关问题，多位DBA进行的分享，主要分为几个方面：1 .性能问题排查；2 .优化方法；3 .高可用问题；4 .安全防范；5 .迁移问题；6.其他问题1、性能问题排查Q：MySQ1.如何排查CPU占用高的问题？问题描述：重点是关于通过哪些系统表或者常用的sql来确定导致问题的sql?感觉这方面的资料很少，不像OralCe的那些v$视图，网上资料很多，sql语句也很多。答：可以通过将系统线程号与SQ1.

2、对应来查看top-H-pPIDUSERPRNlVlRTRESSHRS%CPU%MEMTIME+COMMAND23974mysql2001658m358m12mR99.91.10:05.52mysqld12295mysql2001658m358m12mS0.31.10:02.44mysqld.SE1.ECTa.THREAD_OS_ID,b.user,b.host,b.db,mand,b.time,b.state,b.infoFROMperfbrmance_schema.threadsa,infbrmation_schema.processlistbWHEREb.id=a.processlist_

3、id;THREAD-OsjDusERHoSTdbcommandTIMEstateinfo23974*root10.10.18.201:21466SySQUERY29SendingDATASE1.ECTa.*FROMtesta,testb,teste,testdORDERBYa.value1.IMITO,1000.Q：MySQ1.数据库内存使用率高，应该如何进行排查？问题描述：内存使用率，通过系统命令能定位到mysql占用的内存高，如何通过系统表或者相关的sql语句，定位到占用内存高的那部分sql?MysqlServerMemoryUsage=SumofGlobalBuffers+（number

4、ofConnection*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的

5、bufferpool占用内存的表select*frominnodb_buffer_stats_by_tableorderbypagesdesclimitlO;Q：MySQ1.数据扇磁盘IO横用高，请问如何进行排查？问题描述：通过系统能确定是数据库的IO读写高，有哪些系统表或者sql联合起来可以把关键的sql定位出来？答：mysql5.7版本为例，结合PerfOrmance_schema来查看MySQ1.数据库的各种指标。相当于Oracle数据库中的各种性能视图，可以查看几乎所有的数据库状态。IO的话，可以查看这张表：performance_schema.file_instances：列出了文件

6、I/O操作及其相关文件的工具实例排查思路：1、慢SQ1.排除2、硬件问题-RAlD降级，磁盘故障等排除3、innodbog、innodb_buffer_pOO1.Wait相关配置和等待4、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建

7、议：最好部署相关的监控平台或者对比历史性能记录，结合业务以及负载来分析。2、优化方法Q：MySQ1.优化的常用方法有哪些？答：一、最常见是慢查询优化1、打开慢查询记录，设置记录SQ1.的最短时间2、使用Pt工具，分类统计慢查询语句3、针对执行次数多或者时间长的语句进行优化（索引优化、SQ1.改写、业务逻辑优化）ps:也可以在系统表中，查看全表扫描多的表等二、配置文件优化1、内存使用量2、各种方面写盘策略Q：MySQ1.中执行计划如何解读？问题描述：1：执行计划如何解读？db2中按照从下往上，从左到右的顺序来解读2：执行计划中需要关注的特殊标识有哪些？例如：Usingwhereusingfile

8、sortUsingtemporary等等答：1、执行顺序，看ID列id值相同执行顺序从上到下。id值不同时id值大的先执行。2、关注的特殊标识SE1.EeT_TYPE-执行查询类型，不同类型对应的Type：访问类型，很重要possible_keys：索引使用关于explain输出参数，可参考官方文档：以MySQ1.5.7为例Q：MySQ1.中关于表维护的操作（提升性能相关的）有哪些？问题描述：MySQ1.中关于表维护的操作（提升性能相关的）有哪些？例如db2中的表重组，db2rbind绑定包等操作答：MySQ1.的表维护语句：ANA1.YZETAB1.E：更新表统计信息。执行该语句的时候inn

9、odb及myisam表会加上读锁,停止数据更新。该语句支持innodb,myisam及ndb表,针对myisam表，该语句等同myisamchkanalyzeOPTlMlZETAB1.E:整理数据,表碎片CHECKTAB1.E:用来检查数据库表和索引是否损坏REPAIRTAB1.E：CheCktabIe语句可以检查一个表中的的问题，若表或索引损坏，可以使用repairtable语句尝试修正它Q：有哪些工具可以帮助优化MySQ1.的？答1：SQ1.优化主要还是看经验和对慢查询梳理。配置文件优化，一般来说就几个参数需要优化，其他可以不动答2：以下工具可以参考：pt-mysql-summarypt-

10、variable-advisorpt-duplicate-key-checkerpt-deadlock-logger或者tuning-primer.sh3、高可用问题Q：MySQ1.原厂有OraCle的ClUSter集群，有哪些主流的开源适合高并发集群呢？一、MySQ1.高可用方案MySQ1.以及各种开源数据库，也有自身的集群方案，但是大多需要和业务以及借助第三方工具来实现。或者通过分布式来均衡高并发。主要的高可用集群架构可以分为如下几种：1、基于共享存储的高可用方案-SAN基于共享存储的高可用，及使用传统的基于SAN共享存储，结合开源的KeePliVe做主从同步，可避免除存储外的组件损坏引起

11、的宕机，部署相对简单，对应用透明，但是存储时单点，且存在性能瓶颈。2、基于磁盘复制的高可用方案-DRBD保证主备的数据一致性，不依赖共享存储，此方案处理failover的方式上依旧需要借助主机层面的高可用组件，如keeplive,Heaabeat等。不同的是，在数据共享方面，采用了基于块级别的数据同步软件DRBD来实现，但是可扩展性较差。它并不共享存储，而是通过服务器之间的网络复制数据。适用于数据库访问量不太大，短期内访问量增长不会太快，对数据库可用性要求非常高的场景。3、基于MySQ1.自身的主从复制-RePIiCation基于MySQ1.自身的主从复制，5.7以后的GTID,以及之前的re

12、plicationo主从复制，部署简单，但是只能有一个MaSter进行读写，其余都为备库，还需要结合业务。并发量不大的情况下，可采取主从，管理简单。4、MHA图可用方案MHA是一套MySQ1.高可用管理软件，除了检测MaSter宕机后，提升候选Slave为NewMaster之外（漂虚拟IP）,还会自动让其他Slave与NewMaster建立复制关系。MHAManager可以单独部署在一台独立的机器上，并管理多个master-slave集群。但是，只支持一主多从架构，集群中必须最少有三台数据库服务器，要保持切换对应用透明，依然依赖于VIP,不适用于大规模集群部署，配置比较复杂。且MHA管理节点本

13、身的HA无法保证。MySQ1.5.7之前数据不丢的前提是Master服务器还可以被MHAManager进行SSH连接，通过应用保存的binlog的方式来保证。MySQ1.5.7之后通过无损复制，仅仅是减少了丢数据的可能性，假如此时的状态为切成异步的状态，那就和之前一样了（可以设置超时的时间很大）；当MaSter恢复的时候，最后一部分数据是否需要FIaShback,MHA也是不负责这个事情，需要人工介入。5、基于zookeeper/consul的高可用方案借助zookeeper组件，结合MHA或者其他高可用架构场景，实现强制一致性的高可用集群分布，可适应大规模高并发场景，需要一定的技术实力，引入

14、zookeeper,架构复杂度上升，但是整体扩展性非常好，可以管理大规模集群。保证了整个系统的高可用，主从的强一致依赖于MySQ1.本身，比如半同步，或者外围工具的回补策略6、基于MMM高可用方案MMM提供了MySQ1.主主复制配置的监控、故障转移和管理的一套可伸缩的脚本套件。在MMM高可用方案中，典型的应用是双主多从架构，通过MySQ1.replication技术可以实现两个服务器互为主从，且在任何时候只有一个节点可以被写入，避免了多点写入的数据冲突。同时，当可写的主节点故障时，MMM套件可以立刻监控到，然后将服务自动切换到另一个主节点，继续提供服务，从而实现MySQ1.的高可用。可以灵活选

15、择VlP方案或者全局目录数据库方案（更改MasterIP映射）来进行切换。MMM提供了自动和手动两种方式移除一组服务器中复制延迟较高的服务器的虚拟ip,同时它还可以备份数据，实现两节点之间的数据同步等。由于MMM无法完全的保证数据一致性，所以MMM适用于对数据的一致性要求不是很高,但是又想最大程度的保证业务可用性的场景。对于那些对数据的一致性要求很高的业务，非常不建议采用MMM这种高可用架构。7、基于中间件proxy高可用组件的集群方案中间件：阿里Cobar、MyCAT360Atlas淘宝Tddl网易CutusMySQ1.ProxyProxySQ1.（Percona）KingShardMaxScale（MariaDB）OneProxy切换对应用透明,可扩展性强，方便分片扩展,可以跨机房部署切换,但是需要有一定自研能力，或者选择有完整的后期技术支持的中间件，以及社区活跃度较高的，有一定能力，后期可自研或者自己优化开发相关的中间件。以适应自身的业务需求。二、集群/分布式基于集群或者分布式的HA包括：MysqlGroupReplicationMysqlInnoDBClusterPerconaXtraDBClusterMar