MySQL 运维和第三方工具

Yearning

Yearning简介

Yearning 开源的MySQL SQL语句审核平台，提供数据库字典查询，查询审计，SQL审核等多种功能。
Yearning 1.x 版本需Inception提供SQL审核及回滚功能。

Inception是集审核，执行，回滚于一体的自动化运维系统，它是根据MySQL代码修改过来的，
工作模式和MySQL相同。Yearning是基于python实现的Web版人机交互界面。

Yearning 2.0 版本开始无需Inception，已自己实现了SQL审核及回滚功能。
Yearning1.0 python版本已不再进行官方维护。
Yearning2.0 golang版本为后续维护项目。

如仍使用python版本Yearning须知:

• Yearning python版本不会闭源，仍可基于AGPL3.0许可进行二次开发。
• 由于inception已闭源失去后续支持,python版本将失去对审核规则及审核逻辑的维护。(此问
  题即使Yearning项目也无法解决。go版本已实现相关审核逻辑，由Yearning自己维护,保证后续维护的可控性。)
• 已知python版本含有多个提权漏洞(用户 -> 管理员) golang版本通过token内嵌角色信息的方式避免了此类问题。

强烈建议使用Yearning2.0。

Yearning功能

• Yearning 工具包含的主要功能如下：
  • SQL查询
    • 查询导出
    • 查询自动补全
  • SQL审核
    • 流程化工单
    • SQL语句检测
    • SQL语句执行
    • SQL回滚
    • 历史审核记录
  • 推送
    • 站内信工单通知
    • E-mail工单推送
    • 钉钉webhook机器人工单推送
  • 其他
    • todoList
    • LDAP登陆
  • 用户权限及管理
    • 拼图式细粒度权限划分
• 功能界面
  • dashboard
    dashboard主要展示Yearning各项数据包括用户数/数据源数/工单数/查询数以及其他图表。个人信息栏内用户可以修改密码/邮箱/真实姓名，同时可以查看该用户权限以及申请权限
    
  • 我的工单
    展示用户提交的工单信息。对于执行失败/驳回的工单点击详细信息后可以重新修改sql并提交；对于执行成功的工单可以查看回滚语句并且快速提交SQL。
    
  • DDL审核
    DDL相关SQL提交审核，查看表结构/索引，SQL语法高亮/自动补全
    
  • DML审核
    DML相关SQL提交审核，SQL语法高亮/自动补全。所有的SQL只有在检测后错误等级为0时提交按钮才会激活。
    
  • 查询审核
    查询/导出数据 SQL语法高亮/自动补全，快速DML语句提交。
    
  • 工单审核
    DDL/DML管理员审核并执行。
    
  • 数据库管理
    添加/编辑/删除 数据源。所有添加的数据源应在添加之前点击测试连接按钮进行连接性测试,保证连接性。数据源分为查询数据源/非查询数据源。查询数据源仅会出现在细粒度权限的查询数据源范围内。非查询数据源同理。
    
  • 用户权限管理
    用户权限修改。
    
  • 审核规则管理
    设置SQL检测规则。数值型规则0值都为不受限制，保存后即时生效。
    

想了解更多Yearning工具安装和使用详情，可参考官网文档

canal

canal简介

canal 译意为水道/管道，主要用途是基于MySQL数据库增量日志解析，提供增量数据订阅和消费。
早期阿里巴巴因为杭州和美国双机房部署，存在跨机房同步的业务需求，实现方式主要是基于业务trigger 获取增量变更。从 2010 年开始，业务逐步尝试数据库日志解析获取增量变更进行同步，由此衍生出了大量的数据库增量订阅和消费业务。

基于日志增量订阅和消费的业务包括以下内容：

• 数据库镜像
• 数据库实时备份
• 索引构建和实时维护(拆分异构索引、倒排索引等)
• 业务 cache 刷新
• 带业务逻辑的增量数据处理
  当前的 canal 支持源端 MySQL 版本包括 5.1.x , 5.5.x , 5.6.x , 5.7.x , 8.0.x

canal工作原理

MySQL主备复制原理

• MySQL master 将数据变更写入二进制日志( binary log, 其中记录叫做二进制日志事件binary log events，可以通过 show binlog events 进行查看)
• MySQL slave 将 master 的 binary log events 拷贝到它的中继日志(relay log)
• MySQL slave 重放 relay log 中事件，将数据变更反映它自己的数据

canal的工作原理类似mysql主从同步原理：

• canal模拟MySQL slave的交互协议，伪装自己为MySQL slave，向MySQL master发送dump协议
• MySQL master收到dump协议请求，开始推送binary log 给canal
• canal解析binary log对象（原始为byte流）

想了解更多cannal工具详情，可参考官网文档

DataX

DataX简介

DataX 是阿里巴巴集团内被广泛使用的离线数据同步工具/平台，实现包括 MySQL、Oracle、
SqlServer、Postgre、HDFS、Hive、ADS、HBase、TableStore(OTS)、MaxCompute(ODPS)、DRDS等各种异构数据源之间高效的数据同步功能。

• 设计理念
  为了解决异构数据源同步问题，DataX将复杂的网状的同步链路变成了星型数据链路，DataX作为中间传输载体负责连接各种数据源。当需要接入一个新的数据源的时候，只需要将此数据源对接到DataX，便能跟已有的数据源做到无缝数据同步。
• 当前使用现状
  DataX在阿里巴巴集团内被广泛使用，承担了所有大数据的离线同步业务，并已持续稳定运行了多年。当年每天完成同步8w多道作业，每日传输数据量超过300TB。

DataX框架设计

DataX本身作为离线数据同步框架，采用Framework + plugin架构构建。将数据源读取和写入抽象成为Reader/Writer插件，纳入到整个同步框架中。

• Reader：Reader为数据采集模块，负责采集数据源的数据，将数据发送给Framework。
• Writer： Writer为数据写入模块，负责不断向Framework取数据，并将数据写入到目的端。
• Framework：Framework用于连接reader和writer，作为两者的数据传输通道，并处理缓冲，流控，并发，数据转换等核心技术问题。

DataX插件体系

DataX Framework提供了简单的接口与插件交互，提供简单的插件接入机制，只需要任意加上一种插件，就能无缝对接其他数据源。经过几年积累，DataX目前已经有了比较全面的插件体系，主流的RDBMS数据库、NOSQL、大数据计算系统都已经接入。DataX目前支持数据如下：

DataX核心架构

DataX 3.0 开源版本支持单机多线程模式完成同步作业运行，按一个DataX作业生命周期的时序图，从整体架构设计非常简要说明DataX各个模块相互关系。

• DataX完成单个数据同步的作业，我们称之为Job，DataX接受到一个Job之后，将启动一个进程来完成整个作业同步过程。DataX Job模块是单个作业的中枢管理节点，承担了数据清理、子任务切分(将单一作业计算转化为多个子Task)、TaskGroup管理等功能。
• DataXJob启动后，会根据不同的源端切分策略，将Job切分成多个小的Task(子任务)，以便于并发执行。Task便是DataX作业的最小单元，每一个Task都会负责一部分数据的同步工作。
• 切分多个Task之后，DataX Job会调用Scheduler模块，根据配置的并发数据量，将拆分成的Task重新组合，组装成TaskGroup(任务组)。每一个TaskGroup负责以一定的并发运行完毕分配好的所有Task，默认单个任务组的并发数量为5。
• 每一个Task都由TaskGroup负责启动，Task启动后，会固定启动Reader—>Channel—>Writer的线程来完成任务同步工作。
• DataX作业运行起来之后， Job监控并等待多个TaskGroup模块任务完成，等待所有TaskGroup任务完成后Job成功退出。否则，异常退出，进程退出值非0

想了解更多DataX工具详情，可参考官网文档

percona-toolkit

percona-toolkit介绍

MySQL数据库是轻量级、开源数据库的佼佼者，因此有很多功能强大第三方的衍生产品，如perconatoolkit，XtraBackup等。percona-toolkit是一组高级命令行工具的集合，可以查看当前服务的摘要信息，磁盘检测，分析慢查询日志，查找重复索引，实现表同步等等。

percona-toolkit工具是 MySQL一个重要分支产品percona的，它是一组命令的集合。下面给大家介绍几个生产中常用的命令。

percona-toolkit安装

工具包的下载地址
安装过程很简单，先解压：

tar -zxvf percona-toolkit-3.0.3_x86_64.tar.gz

由于是二进制的包，解压完可以直接进到percona-toolkit-3.0.3/bin目录下使用。

pt-query-digest

pt-query-digest是用于分析mysql慢查询的一个工具，它可以分析binlog、General log、slowlog，也可以通过showprocesslist或者mysqldumpslow命令来进行分析。可以把分析结果输出到文件中，分析过程是先对查询语句的条件进行参数化，然后对参数化以后的查询进行分组统计，统计出各查询的执行时间、次数、占比等，可以借助分析结果找出问题进行优化。

大家都知道数据库大多数的性能问题是 slow sql 语句造成的，需要及时做相关的优化处理。使用示例如
下：

1. 直接分析慢查询文件:

   pt-query-digest slow_OAK.log > slow_report.log
   

2. 分析最近24小时内的查询：

   pt-query-digest --since=24h slow_OAK.log > slow_report.log
   

3. 分析只含有select语句的慢查询

   pt-query-digest --filter '$event->{fingerprint} =~ m/^select/i' slow_OAK.log> slow_report.log
   

查看SQL报告，总结慢语句有哪些，并可以看对应时间的消耗。分析结果如下所示：

可以看到报告中，列举出了一些sql语句响应时间占比情况，以及SQL语句的执行时间情况。方便我们可以很直观的观察哪些语句有问题。

pt-index-usage

pt-index-usage命令能够连接到MySQL数据库服务器，读取慢查询日志，并使用EXPLAIN询问MySQL如何执行每个查询。分析完成时，它打印出一个关于查询没有使用的索引的报告。

对于我们已有的生产环境，随着系统运行的时间越长，DML操作越来越慢，这可能和我们最初设计的索引是有关的（变慢的情况很多），项目一旦上线，很少会有人去关注索引的使用情况。某些索引是从create开始就没使用过，这无形中就给MySQL增加了维护负担，任何对该表的DML操作，都要维护这些没有被使用的索引。我们可以使用pt-index-usage工具找出哪些索引一直没有被使用，然后进行删除。

pt-index-usage语法格式如下：

pt-index-usage [OPTION...] [FILE...]

打印报告：

pt-index-usage /path/to/slow_OAK.log --host localhost

也可以将报告写入到一个数据库表中，存入后方便我们的查看。如果在生产服务器上使用此功能，则应该小心，它可能会增加负载。

pt-index-usage slow.log --no-report --save-results-database percona

pt-online-schema-change

pt-online-schema-change命令可以在线整理表结构，收集碎片，给大表添加字段和索引。避免出现锁表导致阻塞读写的操作。针对 MySQL 5.7 版本，就可以不需要使用这个命令，直接在线 online DDL 就可以了。

pt-online-schema-change --user=root --password=root --host=localhost -- alter="ADD COLUMN city_bak VARCHAR(256)" D=learn,t=city --execute

pt-table-checksum

pt-table-checksum命令可以检查主从复制一致性。pt table checksum通过在主机上执行校验和查询来执行在线复制一致性检查。如果发现任何差异，或者出现任何警告或错误，则工具的“退出状态”为非0

该命令将连接到本地主机上的复制主机，对每个表进行校验和，并报告每个检测到的复制副本的结果：
比较learn库的差异情况，在主库上面执行：

上述结果显示diff都为0，证明主从的learn库没有差异情况。

比较learn库哪些表有差异（需要添加replicate-check-only），在主库上面执行

上述结果显示learn库下面position_detail表主从数据不一致。

除了上述命令外，还有很多，例如pt-ioprofile可以命令方便定位IO问题；pt-slave-restart可以监
控主从错误，尝试重启MySQL主从。想了解更多percona-toolkit工具详情，可参考官网文档

MySQLMTOP

MySQLMTOP简介

MySQLMTOP 是一个由Python+PHP开发的开源MySQL企业监控系统。该系统由Python实现多进程数据采集和告警，PHP实现Web展示和管理，优点如下：

• MySQL服务器无需安装任何Agent，只需在监控WEB界面配置相关数据库信息
• 启动监控进程后，即可对上百台MySQL数据库的状态、连接数、QTS、TPS、数据库流量、复制、性能慢查询等进行实时监控。
• 可以在数据库偏离设定的正常运行阀值(如连接异常，复制异常，复制延迟) 时发送告警邮件通知到DBA进行处理。
• 可以对历史数据归档，通过图表展示数据库近期状态，以便DBA和开发人员能对遇到的问题进行分析和诊断。
  

MySQLMTOP 发展历史

• 2014年01月，开源MySQLMTOP企业MySQL监控系统正式上线并开源
• 2014年06月，MySQLMTOP进行重构，加入了Oracle、Mongodb、Redis的支持，正式更名为Lepus
• 2014年08月，Lepus 成功取得商业软件著作权
• 2015年01月，开源MySQLMTOP被评为2014优秀开源软件
• 2015年01月，Lepus正式开源，并建立官方网站向大家免费提供服务
• 2015年06月，Lepus网站软件下载总数量统计超过10000+,并广泛应用于各大互联网企业生产数据库的监控
• 2017年01月，Lepus代码托管至github,网站软件下载总数量统计超过30000+

MySQLMTOP功能

MySQLMTOP主要功能如下：

• 实时 MySQL 状态监控和警报
  MySQLMTOP 持续监视 MySQL 的基本状态和性能信息，包括数据库连接状态，启动时间，数据库版本，总连接数，活动进程，QPS，TPS，进出MySQL数据库的流量信息。在数据库状态异常或偏离正常基准水平时发出报警邮件通知。
  
• 实时 MySQL复制监控
  MySQLMTOP自动发现 MySQL 复制拓扑结构，自动监视数据库的延时和binlog信息，可以了解所有 MySQL 主服务器和从服务器的性能、可用性和运行状况。并在问题（如从服务器延迟）导致停机前向管理员提供改正建议。
• 远程监控云中的 MySQL
  适合于云和虚拟机的设计，能远程监视MySQL服务器不需要任何远程代理器。
• 直观管理所有 MySQL
  MySQLMTOP提供一个基于 Web 的界面，可令全面深入地了解数据库性能、可用性、关键活动等；直观地查看一台服务器、自定义的应用组或所有服务器。一组丰富的实时图形和历史图形将帮助您深入了解详细的服务器统计信息。
  
• 可视化MySQL慢查询分析
  监视实时查询性能，查看执行统计信息，筛选和定位导致性能下降的 SQL 代码。结合使用
  Information Schema 可直接从 MySQL 服务器收集数据，无需额外的软件或配置。
• 性能监控
  监视影响 MySQL 性能的主要指标。如Key_buffer_read_hits、Key_buffer_write_hits、
  Thread_cache_hits、Key_blocks_used_rate、Created_tmp_disk_tables_rate等信息，根据相关性能指标可以对服务器核心参数进行调整优化。

想了解更多MySQLMTOP工具详情，可参考官网文档