mycat实战（二）-mysql数据库管理

前面介绍了基于mycat中间的MySQL架构，从mysql数据库、mycat集群，haproxy(或者lvs)各个环节灭掉了单点，再加上mycat的守护进程，在高可用和负载均衡上投入到生产都没啥问题。

我们提供的服务有：网站设计、网站建设、微信公众号开发、网站优化、网站认证、诸城ssl等。为千余家企事业单位解决了网站和推广的问题。提供周到的售前咨询和贴心的售后服务，是有科学管理、有技术的诸城网站制作公司

但是随着数据量越来越大，数据库越来越多，管理问题就被提上来了。特别是整个项目组只有你一个dba的时候，真的是压力山大。没个工具把整个集群管起来，效率会很低。目前我们mycat后面有16个mysql实例(只算主的数量，另有16个从)，每个mysql实例差不多分10个分库，合计约160个分库。因为单逻辑表达到几十亿甚至百亿，所以每个分库中那些大表还是好几千万数据量的。

以下是我们遇到的一些问题，所以我只能通过建设工具平台来提高效率。

平台的部分功能已经投入使用，部分还在开发，继续完善中。这里介绍下我们的几个功能：

(说明：我们平台的开发使用非常多的开源程序，我们并不是想要重新造轮子，大部分都是基于现实问题来做的实用工具。比如平台是在开源程序yearning基础上扩展的，只是做了较大的改动，后端还用了inception、mha、gh-ost等等各种开源软件，最主要的是我们希望基于mycat形成的集群管理，而不是单个数据库的管理)

一、sql语句审核

我们作为dba角色要考虑的事情，就是帮助开发写出高效、正确的sql语句。

开源软件inception(现在好像闭源了)提供了单库下的sql审核，但是不针对mycat这种分布式的结构，同时他也不提供效率的检查。(不过他的sql规范性检查挺好的，我们也在使用。)

在以前作为单库的时候，开发一般写一个正确的sql一般没问题，效率可能差一些。但是分库之后对开发是透明的，那就有可能出现问题。

查询类的sql我们做3点检查：

1、是否出现了跨库关联

mycat虽然支持跨库关联(那也是只支持2表的跨库)，写法上也会不一样，前面需要加一些注解，另外性能也会是个问题。所以我们都是不允许的。

2、sql语句在多少分库中运行，在分库不是很多的时候，一个sql语句在多个库中执行也许不是问题，但是一旦分库很多，并且语句查询量很大时，对mycat本身会造成很大的压力，这回引起mycat的处理能力下降，数据返回延时等。如下面某个语句我们按照账号查询时，分析的结果将是在139个分库中执行，就会有存在可能的风险。

3、sql语句在后端数据库执行的效率

通常我们会使用explain检查sql的执行计划。

后端有很多分库的时候，是否可能存在部分库索引问题等?

我们的sql语句审核功能中就提供了以上的功能。有了这个之后开发人员基本上能自己评估sql了，当然最后还是要我审核之后sql才能生效。

根据我们的分类，sql语句的风险等级分为1-10个等级，我们可以分级定权限，比如1级可以让普通运维审核即可，2级以上则需要DBA来审核。

二、DDL操作

假设要创建一张新表，那么要看这个表的分库方式，全局?分片?不分片?等等

如果是全局表或者分库表，那么我们第一步就是要确定哪些分库上要建表，没有工具的话，这并不容易。我们之前写了一些脚本来帮助我们做这些事情，但是终归不是很方便。建表之后我们还得往mycat集群上的每个mycat配置文件中添加表，也不方便。

在建表方面的规范性,这很重要。比如说我们要使用一些工具如 pt-ocs或者gh-ost，他们都需要主键(或者唯一索引)，这些常常被开发同事所忽略。另外注释、字符集等等这些更容易被忽略。所以我们特别要感谢下inception这个工具，可以帮助我们实施标准规范。

在ddl方面，如果要对现有的表做修改，这个更加悲催了，以我们表的规模，即使分成了100多个分库表，每张单表也还是有几千万记录。我们都知道mysql上对大表直接操作alter table 是会造成非常可怕的后果。所以我们还需要能够支持批量处理ddl语句的功能。

现在流行的操作方式是借助通过pt-ocs 或者gh-ost 这种开源工具进行。gh-ost的侵入更少，更可控，所以我们采用了gh-ost。如页面上所示，定好时间，基本上就能自动执行了。

三、mha高可用，监控和操作

我们上面介绍了，使用mha搭建了mysql高可用方案。数据库多了之后，人肉方式的监控和执行肯定不行的。zabbix和nagios能帮我们建立一些监控。执行方面，在后台命令行方式固然很酷，但是实例多了之后就不方便。所以我们还得有一个mha监控和操作的平台。有了这个平台，有时候我们需要人工切换的时候可以直接操作了。这样即使是普通的运维人员，只要授权就可以进行切换.

四、mycat之间的配置同步生效

haproxy将mycat组成了集群，对数据库的请求可能发往任何一个mycat服务，所以必须保证mycat的配置一致性。在单个mycat情况下，这不是问题，在多个mycat下这就会成为一个风险点。

mycat是通过文件配置的，mycat之间不会有通信，当然也就没有同步上锁等待的操作。(据说2.0版会开发mycat集群，也许就没有这个问题了)

理想的操作方式：mycat做统一配置，统一配置发布后，各个mycat上锁不允许新的请求进来，然后加载新配置，新配置完成并生效后解锁可用。

现实的操作方式：选择在业务较为空闲的时间，在haproxy配置中只保留一个mycat，其他的全部注释掉。

其他mycat配置修改并生效之后，放开注释，原来留下的那个注释掉，重载haproxy配置。

将最后那个mycat配置修改好

最后把全部mycat都放开

这一系列的操作通过手工操作，容易出现纰漏，于是我们把他做成程序的自动调度方式。

建议：如果mycat能够提供一个配置版本号的话，我们更容易检测(监控)到配置是否是最新的。如以时间戳为版本号，我们可以比对不同mycat之间是否配置一致。

五、mysql实例迁移调度管理

在一个mysql实例下运行很多库(database),还是一个实例一个库?

我们现在的规模，最大的逻辑库被分为1百多个库，当然也有些是单库，加起来总有个150个库以上。那些比较大的逻辑表，记录数一般都是几十亿到百亿 。

分库多了之后就会带来一个问题，我们应该起多少个mysql实例?

先说现状：我们现在的规模是15个mysql，每个mysql实例内大约建10个database。每个实例数据量大约1T左右。

单个mysql到1T左右，迁移、备份和恢复都是有比较大的困难的(别跟说有从库就不用备份，那是瞎扯)。

现实是我们每个mysql都很大，也没有共享存储，所以我们无法进行快速的调度(网速也只有千兆，如果数据迁移，这个时间太长)。

如果将大的mysql实例分成10个小的mysql实例，100G左右的数据库，就会灵活很多。

假设每个mysql能快速调度到需要的服务器上(这个时间只要小于能容忍的做大时间)，理论上我们每台服务器可以使用掉90%的资源，一台挂掉之后，将该台上的10个mysql分别调度到其他10台服务器上，这是不是听起来很棒，但是在4个9的稳定性要求之下，没有共享存储这种又是不可能的。

如果每个库都独立一个mysql实例，带来的问题就是一下子变成150多个实例(然后再加上1-2倍的从库)，就你一个dba，立马有种要疯掉的感觉。

所以我坚持了一个mysql实例上分多个库，即使他单实例数据量大了不方便。

现在我们在平台的调度管理，10个和100个没有什么区别的时候，我们就可以把他细分了。这个我们暂时还没有完成。

问题六、备份与恢复的管理以及分片后带来的挑战

日常备份和恢复是DBA的一项重要工作，但是自动化的备份脚本扔在那里是否执行成功呢，备份是否可用呢，这些都是问题。

所以我们需要做一个备份和恢复的管理。(备份结果在mha的图中有最后备份成功时间，其他功能正在开发中)

最后我还想说下在数据被分片之后，在备份和恢复上是带来很大的问题的。

分片之后数据库多一点没有关系，只要我们有管理平台就可以。

最大的挑战是多台数据库服务器之间的备份时间一致性问题。

事务型数据库(oltp)中很重要的一个要求就是数据的时间一致性。

分库之后，我们能保证的是单库内部的一致性，却很难保证多库之间的一致性。

比如:用户U1的数据在D1库中，用户U2的数据在D2库中，如果U1和U2之间有关联的交易(如转账等)。

这个时间D1发生的奔溃，在恢复的时候必须保证D1库和D2库的时间点一致性。这很难。

事实上我们没有能够做到这点。

可做的是尽可能的将同一用户的数据存放在一个库中，加以订单表和一些冗余日志进行校验恢复。

这些工作需要在设计之初就要考虑。

七、现场慢查询的查杀

这个功能很简单，但是却非常实用。

因为分库很多，如果线上出现影响性能的慢查询，我们需要立刻把他找出来并且杀掉。以前我们手工操作，通过脚本再怎么熟练也需要不少的时间。

现在页面上可以一键查杀非常方便。

七、数据flashback

现在也有开源程序可以实现对数据更新语句的回滚，很好用。

如果是直接通过连接mysql的方式执行的语句，我们通常可以通过连接进程号就能精确的知道具体执行的内容。

通过mycat中间件之后执行的sql语句，就很难获得非常准确的内容。

这时只能通过时间、执行的数据库和表名等从每个mysql上获得一个大致范围，然后通过人工刷选进行确认。(这个功能还未完成)

其他还有很多我们需要的功能，目前是列了一堆计划，如果你有兴趣，可以给我提提意见。