数据库自动化运维方案

数据库自动化运维方案一、前言有赞作为”新零售”的软件服务供应商，随着业务的不断发展，从第一批几十家商户到现在300万商家，涉及零售，美业，餐饮，自媒体等众多商家，业务规模以及访问量爆发式增长。

一方面给后端数据库带来的影响是服务器数量和DB 实例的数据量出现成倍增加。

各种业务需求:快速交付实例，慢查询优化以及备份恢复管理等都给DBA 的日常运维支持带来更高的要求。

另一方面最开始以excel 作为CMDB 管理数据库实例的纯人肉运维又给高效的数据库运维带来阻碍。

本文介绍有赞DBA 研发的数据库自动化管理平台-ZanDB，解决上面的业务方发展中遇到的问题，抛砖引玉，希望能给面临同样需求的同行带来帮助。

（图1）整体的web 界面2.1、标准化从事过大规模化运维的朋友都知道:标准化是规模化，自动化的基础。

在我们开发MySQL 自动化运维平台的之前，面临的主要问题就是各种”不标准”:OS 软件初始化不统一,软件目录结构不标准,配置文件路径不标准,主从配置不对称。

于是我们开始着手制定标准:OS 层面1、磁盘统一做成RAID5 模式扩大空间利用率。

2、统一RAID 卡读写策略为WB，IO 调度策略为deadline，以及其他SSD IO 方面的优化。

数据库层面1、统一目录配置，通过端口进行区分，例如my3306,my3307，在my3306下面创建对应的数据目录、日志目录、运行文件目录，tmp 目录等。

2、每个实例独享一个配置文件，除server_id , innodb_buffer_pool_size 等参数外其他参数均保持一致。

3、线上环境的MySQL 软件目录和版本保持一致。

有了以上标准和规范，我们花了2个月左右的时间将以前不符合的标准的主机和实例进行改造，并且使用saltstack 来维护DB 服务器基础的软件安装和文件配置规范。

2.2、ZanDB 的技术栈ZanDB 系统采用Python Django + Percona-Toolkit + Agent(servant) + Celery+前端相关(JQuery + Ajax)技术，同时利用了缓存Redis 和MySQL DB 作为存储，整套系统采用的技术栈较简单，实现的功能对于目前来说比较实用。

对于任何具有数据资产的公司而言，数据备份重于一切。

由于历史原因，有赞数据库的备份是由shell 脚本堆砌的，没有统一的入口来查看备份结果是成功还是失败，如果DBA 对自己维护的数据库的备份有效性一无所知，出现异常问题需要恢复而又恢复不了的时候，对有赞以及有赞的商家而言会是致命的打击。

因此，我们第一期的工作是开发ZanDB 备份监控系统。

它的主要功能:1、实时查看备份的执行情况，当前应备份实例个数，已完成实例数，备份失败的个数。

2、显示每个备份的耗费时长。

3、查看过去5天的备份统计信息，如总个数，大小等。

完成ZanDB 备份监控系统开发，我们对备份情况情况有了基本的掌握，之后开始着手设计ZanDB 的二期设计研发工作。

在设计ZanDB 系统时架构时，我们选择使用B/S 架构模式，在数据库服务器上部署我们使用go 自研的agent—servant，ZanDB 系统通过http 服务调度agent 执行各种任务，避免数据库服务器通过明文密码直连ZanDB 的元数据库，增加系统的健壮性和安全性。

总体上我们将ZanDB 的业务逻辑分成了七部分：元数据管理,备份管理,实例管理,主机管理,任务管理,日志管理,日常维护。

（图2）ZanDB 系统设计逻辑架构4.1、任务系统所有的自动化管理平台中都需要一个核心组件-任务管理系统，主动或者被动进行各种任务调度。

我们在ZanDB 中实现了一个相对健壮的任务调度系统，用于执行实例的备份,元数据收集,实例维护比如添加从库，创建主从实例等工作, 该系统支持多种类型的任务：支持按照时间(分钟，小时，每天，星期，月份)，还支持一定间隔的重复性任务。

该任务系统由数据库服务器上的agent-servant 和下发任务的调度逻辑构成，任务调度的元数据表中记录了所有的任务和任务关联主机的时间策略。

通过任务系统，我们彻底的去掉了DB 主机上的crontab 脚本，动态修改任务执行时间、策略以及是否需要执行变得轻而易举。

（图3）任务管理系统4.2、备份子系统有赞的数据库备份是利用xtrabackup 做物理备份，经过压缩，然后rsync 到备份目的机器上，定期远程备份到异地机房。

在一期的基础上，我们完善了备份系统。

1、使用python 重构底层备份脚本，由db 服务器上的agent 执行，添加回调api 接口用于设置备份任务的运行状态，如果一台主机上存在备份失败的实例，会发送报警到DBA 的手机，DBA 可以直接在备份系统中查看其备份报错日志，执行重试，省去了登录DB 主机执行的步骤。

2、和任务系统耦合，我们去掉了一期中依赖crontab 进行备份的定时任务。

3 、通过ZanDB 系统设置备份时间以及实例是否需要备份，支持动态调整备份的目的机器。

同时，备份系统每天针对核心数据库的备份执行有效性校验。

如果发现备份校验失败，通过告警平台触发微信或者短信告警，通知DBA 进行检查并进行重新备份。

4.3、主机管理主机元数据是维护数据库实例的基础，包含主机名，ip 地址，机房位置，内存，空间大小等核心信息，在ZanDB 系统中，我们设置了定时任务通过Zabbix/open-falcon 的api 获取主机信息，比如磁盘可用空间，内存可用空间等定期更新元数据基本信息，为分配实例提供准确的数据决策。

同时可以做数据库集群数据运营，比如预警空间剩余多少天，为数据库集群扩容提供数据判断。

4.4、实例管理（图4）实例管理功能为了尽可能的发挥主机的性能，有赞的数据库采用单机多实例的模式，主机与DB 实例是一对多的关系。

通过实例管理系统，我们可以实现如下功能:1、查看当前的实例列表，获取实例当前的数据大小，日志大小，主从延迟状态，慢查个数等等。

我们还可以通过实例列表设置实例是否启用2、新增单个实例，一对主从，添加一个或者多个从库。

新增实例的过程是通过rsync 命令远程备份机或者本地机器上标准的数据库模板(一个预生成且关闭的mysql实例)，然后用f 模板渲染server_id,buffer_pool_size 等生成标准f 配置文件，执行的具体步骤可以通过web 界面的流程系统查看，任务调度系统支持部分步骤的失败重试。

3、实例的主从一致性校验。

在MySQL 主从复制中，有可能因为主从复制错误、主从切换或者应用使用不当等导致主从数据不一致。

为了提早发现数据的不一致,ZanDB 每天都针对核心数据库，进行主从的一致性校验，避免产生线上影响。

4、实例拆分，用来将之前在同一个实例里面的多个schema 拆分到不同的实例里面。

5、每天将实例的元数据进行快照，如慢查数据，数据目录大小等，方便实例的历史数据分析。

4.5、日志管理ZanDB 定义的日志管理和慢查询有关，用于维护slow_log 和killed_sql，慢查询日志大家都了解，这里解释一下killed_sql。

为了防止实例被慢查拖垮，我们为每个实例启用类似pt-killer 的工具—sql-killer 进行实时监控，将被kill 的sql 写入到具体的指定规则的日志文件中。

大多数DBA 优化的SQL 路径是登陆机器，查看慢查询日志，登陆实例，获取表结构，explain sql，检查执行计划。

对于规模化的DB 运维而言，如果只能通过登录每台DB 主机才能检查慢查询是一件非常痛苦的事情。

为了解放DBA 的双手，同时更好的发现和优化慢日志，保障DB 的稳定性，ZanDB 日志系统由此诞生，主要做TopN 展示和慢查分析。

我们在收集实例元数据的过程中会去统计慢查和被kill 的SQL 的记录数并更新到ZanDB 的元数据中，通过页面展示各个业务中慢查询最多的topN。

当然我们也设定慢查询报警阈值，慢查询超过一定阈值的实例会触发短信报警，及时通知DBA 和开发关注。

（图5）慢查询系统有了慢查询的数据之后如何解决”不在登陆主机查看慢查sql”呢？我们的系统每天会将慢查询日志做轮转切割，每天产生一个日志文件，ZanDB 通过agent 调用pt-query-digest 分析指定的慢查日志并返回给ZanDB 的页面端，展示表结构，慢sql ，对应的执行计划，以及表的大小信息。

系统要获取慢查详情的时候，通过调用pt-query-digest，分析慢日志文件，先将结果存到对应的实例slow log 里，系统下次再获取慢查的时候，如果发现该日期的慢查已经存在分析后的结果，直接返回。

同时，日志管理里面还包含了被kill 的SQL 的top 情况，和慢查是类似的。

4.6、元数据管理（图6）分片信息查询元数据管理包含了binlog 元数据、主键的溢出校验，分片信息信息等。

binlog 元数据管理主要记录每个实例的每个binlog 起始时间和结束时间，binlog 保留时长，在进行数据恢复的时候可以快速的定位到某个日志。

通过主键溢出校验，我们可以及时的发现哪些表的主键自增已经达到了临界值，避免因主键自增溢出无法插入导致故障。

由于我们商品，交易等核心库是分库的，分析慢查，问题定位的时候，需要根据分片键找到对应的实例ip:port。

我们开发了一个分片元数据查询功能，只要提供数据库名,表名,分片键，就可以快速的定位到一个实例，减少之前人工计算的过程。

4.7、日常维护（图7）日常维护功能界面日常维护主要是解决部分低频但是耗时的人肉操作，批量查看实例的某些参数，批量修改配置，紧急的binlog 恢复等。

批量执行SQL 是选择一批实例，执行维护的SQL。

例如，需要修改内存中某个参数的值，或者获取参数的值。

这个SQL 只允许维护相关的，DML 是不允许执行的。

批量修改配置和执行SQL 类型的修改配置类似，不同的是，修改配置是会同步变更配置文件，永久生效，同时也修改内存，例如调整慢查时间等。

解析binlog 是基于开源的binlog2sql 做的，根据提供的数据库名称，表名，时间段，利用binlog 元数据查到指定的binlog 进行解析得到文本文件可以在网页查看和下载，在解决突发的开发误操作需要紧急恢复过程中特别有效。

4.8、数据运营ZanDB 从开发落地到现在已经半年多时间了，积累了一定量的实例数据空间大小，内存大小等,我们利用这些积累的数据做运营分析，开发了趋势图和成本核算功能。

趋势图用于展示数据库总体的空间和内存利用率情况，以及核心业务的增长曲线，方便dba 对机器资源进行调配。

成本核算功能统计各个业务耗费的成本以及占用比例，为业务层决策提供一定的参考。