CN111639113A - 收集MySQL数据库执行的SQL数据的系统及其方法 - Google Patents

Abstract

本申请涉及数据库领域，公开了一种收集MySQL数据库执行的SQL数据的系统及其方法。该收集MySQL数据库执行的SQL数据的系统，包括：监控代理模块、存储队列、应用模块和监控数据库。通过实时监控MySQL数据库的端口，并解析出SQL数据，将解析出的SQL数据写入存储队列组件中，然后消费存储队列中的SQL数据，将SQL数据写入到监控数据库中，同时，分析进程定时工作，按照不同的数据维度对SQL数据进行分析，分析结果用于数据展现。能够监控MySQL数据库实时执行的SQL并做到对MySQL数据库性能无影响，同时，还可以准实时展现分析归类的SQL，为系统运维人员直观展现MySQL数据库的SQL情况。

Description

收集MySQL数据库执行的SQL数据的系统及其方法

技术领域

本申请涉及数据库领域，特别涉及一种收集MySQL数据库执行的SQL数据的技术。

背景技术

收集MySQL数据库实例上所有执行过的SQL数据，以方便了解数据库上正在发生的事情，并对SQL数据进行整理分类，分析出哪类SQL数据执行的较多、哪类SQL数据比较耗时、哪类SQL数据消耗IO资源等，以指导数据库管理或开发人员对数据库进行优化。

对于收集MySQL数据的实时执行SQL数据，常用的是调整Slow_log的阈值或者打开General_log，当使用Slow_log的方式收集运行SQL数据时，需要设置Long_query_time参数设置过滤SQL数据的执行时间，比如：可以设置Long_query_time＝0.5以收集大于0.5s的SQL数据，这样做通常有个缺陷，一些执行较快但执行频次较高的SQL数据就会被忽略掉；而且，如果慢日志过多，对业务数据库的性能也是有影响的。

如果打开General_log,则会把Select、Insert、Update，Create、Alter等DML、DDL语句全部记录在日志文件中，通常，日志文件的数据量会很大，查看很不方便，且日志文件内容没有唯一标识，按照时间顺序存放，可读性差，如果将日志文件内的日志读取出来，也需要消耗大量IO；同时，SQL数据写入Genel_log也消耗数据库性能。也就是说，通过打开数据库的General_log开关，将所有SQL数据操作都写到日志文件中，这种方法会存在以下两个问题：

1.由于数据库需要将执行的SQL数据实时写到日志文件中，会消耗数据库的性能，对数据库至少有10％以上的性能影响。

2.在读取文件时，是顺序读取，日志文件比较大，读取时会消耗大量IO。

因此，目前需要一种既能收集MySQL数据库执行的SQL数据，又尽量对MySQL数据库性能无影响的技术。

发明内容

本申请的目的在于提供一种收集MySQL数据库执行的SQL数据的系统及其方法，通过监控MySQL数据库端口，解析出SQL数据，输出到存储队列，并最终写入到监控数据库中，SQL数据可以持续保留，并能方便查询和读取。

为解决上述技术问题，本发明的实施方式公开了一种收集MySQL数据库执行的SQL数据的系统，包括：监控代理模块、存储队列、应用模块和监控数据库；

所述监控代理模块，用于监控并收集所述MySQL数据库指定端口传输的数据，解析出所述数据中的SQL数据，并将所述SQL数据输出到所述存储队列中；

所述存储队列，用于接收并临时存储所述监控代理模块输出的所述SQL数据；

所述应用模块，用于将所述存储队列中存储的所述SQL数据写入到所述监控数据库中；

所述监控数据库，用于存储所述SQL数据。

本发明的实施方式还公开了一种收集MySQL数据库执行的SQL数据的方法，用于上述收集MySQL数据库执行的SQL数据的系统，所述方法包括以下步骤：

监控并收集所述MySQL数据库指定端口传输的数据，解析出所述数据中的SQL数据，并将所述SQL数据输出到存储队列中；

将所述存储队列中存储的所述SQL数据写入到监控数据库中。

本发明实施方式与现有技术相比，主要区别及其效果在于：

通过监控MySQL数据库端口，解析出SQL数据，输出到存储队列，并最终写入到监控数据库中，能够监控MySQL数据库实时执行的SQL并做到对MySQL数据库性能无影响，同时，还可以准实时展现分析归类的SQL，为系统运维人员直观展现数据库SQL情况。

通过在MySQL数据库之外收集执行的SQL数据，不会消耗MySQL数据库的性能。

相对于现有技术中将SQL数据实时写入到日志文件中，不利于查询的缺陷，本申请通过将SQL数据持续存储在监控数据库中，可以通过SQL语句方便地查询，不会消耗大量IO。

解析出SQL数据后，没有实时写入到监控数据库中，而是写入到存储队列中，可以在应用中消减写入峰值，使系统更加平稳。

通过对监控数据库中存储的SQL数据进行分析和归类，并对分析和归类的结果进行展示，可以方便使用者快速直观地识别出MySQL数据库系统中哪些SQL比较多或比较慢，哪些SQL对MySQL系统的影响大。

本申请的说明书中记载了大量的技术特征，分布在各个技术方案中，如果要罗列出本申请所有可能的技术特征的组合(即技术方案)的话，会使得说明书过于冗长。为了避免这个问题，本申请上述发明内容中公开的各个技术特征、在下文各个实施方式和例子中公开的各技术特征、以及附图中公开的各个技术特征，都可以自由地互相组合，从而构成各种新的技术方案(这些技术方案均因视为在本说明书中已经记载)，除非这种技术特征的组合在技术上是不可行的。例如，在一个例子中公开了特征A+B+C，在另一个例子中公开了特征A+B+D+E，而特征C和D是起到相同作用的等同技术手段，技术上只要择一使用即可，不可能同时采用，特征E技术上可以与特征C相组合，则，A+B+C+D的方案因技术不可行而应当不被视为已经记载，而A+B+C+E的方案应当视为已经被记载。

附图说明

图1是根据本申请第一实施方式的一种收集MySQL数据库执行的SQL数据的系统的结构示意图；

图2是根据本申请第一实施方式的一种SQL数据展示图；

图3是根据本申请第一实施方式的另一种SQL数据展示图；

图4是根据本申请第二实施方式的一种收集MySQL数据库执行的SQL数据的方法的流程示意图。

具体实施方式

在以下的叙述中，为了使读者更好地理解本申请而提出了许多技术细节。但是，本领域的普通技术人员可以理解，即使没有这些技术细节和基于以下各实施方式的种种变化和修改，也可以实现本申请所要求保护的技术方案。

为使本发明的目的、技术方案和优点更加清楚，下面将结合附图对本发明的实施方式作进一步地详细描述。

本发明第一实施方式涉及一种收集MySQL数据库执行的SQL数据的系统。图1是该收集MySQL数据库执行的SQL数据的系统的结构示意图。

首先，需要说明的是，MySQL是一种开放源代码的关系型数据库管理系统(RDBMS)，使用最常用的数据库管理语言——结构化查询语言SQL进行数据库管理。

该收集MySQL数据库执行的SQL数据的系统实时监控MySQL数据库的端口，并解析出SQL数据，将解析出的SQL数据写入存储队列组件中，然后消费存储队列中的SQL数据，将SQL数据写入到监控数据库中，同时，分析进程定时工作，按照不同的数据维度对SQL数据进行分析，分析结果用于数据展现。

具体地说，如图1所示，该收集MySQL数据库执行的SQL数据的系统，包括：监控代理模块、存储队列、应用模块和监控数据库；

所述监控代理模块，用于监控并收集所述MySQL数据库指定端口传输的数据，解析出所述数据中的SQL数据，并将所述SQL数据输出到所述存储队列中。

监控代理模块主要是负责实时监控并收集MySQL数据库指定端口(例如，可以是MySQL默认端口号3306)网络传输的数据，并按照一定的格式解析数据，并将解析结果输入到存储队列(例如阿里云MQ)中，这个模块的工作是生成这个系统所需要的原始数据。MySQL数据库网络传输的数据包中的SQL信息、连接信息、用户信息等是需要收集的有用信息，如图2或图3所示，按照一定的格式将这些有用信息列组成一行数据，即解析出的所述SQL数据。

所述存储队列，用于接收并临时存储所述监控代理模块输出的所述SQL数据。

监控代理模块生成原始数据后，考虑到系统复杂性和监控数据库写入的压力，SQL数据没有实时写入到监控数据库中，而是写入到存储队列(例如阿里云MQ)，这样可以在应用中消减写入峰值，使系统更加平稳。

所述应用模块，用于将所述存储队列中存储的所述SQL数据写入到所述监控数据库中。并且，所述应用模块，还用于控制将所述存储队列中存储的所述SQL数据写入到所述监控数据库中的速度。

应用模块主要是消费存储队列(例如阿里云MQ)中的日志数据，并控制写入速度；如果存储队列(例如阿里云MQ)中数据积累较多，则可适当调大消费的速度，反之，如果写入的数据库有压力，则可调低写入速度。

所述监控数据库，用于存储所述SQL数据。将SQL数据写入到监控数据库中，SQL数据可以持续保留，方便查询和读取。

进一步地，优选地，该收集MySQL数据库执行的SQL数据的系统，还可以包括：

应用分析模块，用于对所述监控数据库中存储的所述SQL数据进行分析和归类。

具体地，该应用分析模块，对SQL数据的执行时长、消耗IO、访问用户、来源IP、访问数据库、返回数据行数等进行分析。

数据展现模块，用于展示所述应用分析模块对所述SQL数据的分析和归类结果。

应用分析模块主要工作是对SQL数据进行分类，比如，访问某个表、同一个查询条件的访问次数、消耗时长等，可以方便使用者快速直观地识别出MySQL数据库系统中哪些SQL比较多或比较慢，哪些SQL对MySQL系统的影响大。

数据展现模块主要是展示应用分析模块分析的结果。图2和图3是两种SQL数据展示图。

综上所述，本申请所公开的该收集MySQL数据库执行的SQL数据的系统实时地监控MySQL数据库各个实例端口，并将解析出的SQL数据输入到存储队列(例如阿里云MQ)，同时，起一个进程消费存储队列中的数据，并写入监控数据库，然后，通过网络的方式，将SOL数据展现出来。因此，该系统具有2个功能：一个是实时监控MySQL数据库端口，同时解析出SQL数据；第二是，可以将SQL分类，做出统计分析，以方便了解数据库上正在发生的事情，并对SQL进行整理分类，分析出哪类SQL执行的较多、哪类SQL比较耗时、哪类SQL消耗IO资源等，以指导数据库管理或开发人员对数据库进行优化。

该收集MySQL数据库执行的SQL数据的系统(即本系统)与现有技术相比，具有如下优势：

1.现有技术需要将执行的SQL实时写到日志文件中，会消耗数据库的性能，对数据库至少有10％以上的性能影响。而本系统将SQL数据输出到存储队列(例如阿里云MQ)，并最终写入到监控数据库，数据可以持续保留，并且因为是在MySQL数据库之外收集执行的SQL数据，到对数据库性能无影响；

2.现有技术只是将SQL数据的输出到日志文件中，不利于查询，在读取文件时，是顺序读取，日志文件比较大，读取时会消耗大量IO。而本系统的SQL数据在数据库内，可以通过SQL语句很方便的查询；

3.现有技术在收集执行SQL数据时，解析出的内容有限，在大部分时候，此只解析出SQL，并将结果输出到日志文件中；而作为系统人员所关注的执行时间、影响行数等细节问题不会输出出来，输出内容有限。而本系统可以根据需求定义分析维度，如执行时长、消耗IO、访问用户、来源IP、访问DataBase、返回数据行数、执行SQL等。

4.本系统中写入监控库的数据可以用于审计，以验证在什么时间做了什么事情。

5.本系统有网络页面可以准实时查到SQL数据分析结果。

需要说明的是，本发明各实施方式中提到的各模块都是逻辑模块，在物理上，一个逻辑模块可以是一个物理模块，也可以是一个物理模块的一部分，还可以以多个物理模块的组合实现，这些逻辑模块本身的物理实现方式并不是最重要的，这些逻辑模块所实现的功能的组合才是解决本发明所提出的技术问题的关键。此外，为了突出本发明的创新部分，本发明上述各设备实施方式并没有将与解决本发明所提出的技术问题关系不太密切的模块引入，这并不表明上述设备实施方式并不存在其它的模块。

本发明第二实施方式涉及一种收集MySQL数据库执行的SQL数据的方法，用于上述所示的系统。图4是该收集MySQL数据库执行的SQL数据的方法的流程示意图。

具体地说，如图4所示，该收集MySQL数据库执行的SQL数据的方法，包括以下步骤：

在步骤401中，监控并收集所述MySQL数据库指定端口传输的数据，解析出所述数据中的SQL数据，并将所述SQL数据输出到存储队列中。

此后进入步骤402，将所述存储队列中存储的所述SQL数据写入到监控数据库中。

此后结束本流程。

进一步地，优选地，在步骤402之后，还可以包括以下步骤：

对所述监控数据库中存储的所述SQL数据进行分析和归类。

展示对所述SQL数据的分析和归类结果。在此步骤中，可以根据需求定义分析的维度，具体可包括：SQL数据的执行时长、消耗IO、访问用户、来源IP、访问数据库、返回数据行数等。

更进一步地，优选地，步骤402中还可以包括以下子步骤：

控制将所述存储队列中存储的所述SQL数据写入到所述监控数据库中的速度。

综上所述，本申请上述方法通过实时监控MySQL数据库的端口，并解析出SQL数据，将解析出的SQL数据写入存储队列组件中，然后消费存储队列中的SQL数据，将SQL数据写入到监控数据库中，同时，分析进程定时工作，按照不同的数据维度对SQL数据进行分析，分析结果用于数据展现。能够监控MySQL数据库实时执行的SQL并做到对MySQL数据库性能无影响，同时，还可以准实时展现分析归类的SQL，为系统运维人员直观展现MySQL数据库的SQL情况。

本实施方式是与第一实施方式相对应的方法实施方式，本实施方式可与第一实施方式互相配合实施。第一实施方式中提到的相关技术细节在本实施方式中依然有效，为了减少重复，这里不再赘述。相应地，本实施方式中提到的相关技术细节也可应用在第一实施方式中。

本发明的各方法实施方式均可以以软件、硬件、固件等方式实现。不管本发明是以软件、硬件、还是固件方式实现，指令代码都可以存储在任何类型的计算机可访问的存储器中(例如永久的或者可修改的，易失性的或者非易失性的，固态的或者非固态的，固定的或者可更换的介质等等)。同样，存储器可以例如是可编程阵列逻辑(Programmable ArrayLogic，简称“PAL”)、随机存取存储器(Random Access Memory，简称“RAM”)、可编程只读存储器(Programmable Read Only Memory，简称“PROM”)、只读存储器(Read-Only Memory，简称“ROM”)、电可擦除可编程只读存储器(Electrically Erasable Programmable ROM，简称“EEPROM”)、磁盘、光盘、数字通用光盘(Digital Versatile Disc，简称“DVD”)等等。

需要说明的是，在本专利的申请文件中，诸如第一和第二等之类的关系术语仅仅用来将一个实体或者操作与另一个实体或操作区分开来，而不一定要求或者暗示这些实体或操作之间存在任何这种实际的关系或者顺序。而且，术语“包括”、“包含”或者其任何其他变体意在涵盖非排他性的包含，从而使得包括一系列要素的过程、方法、物品或者设备不仅包括那些要素，而且还包括没有明确列出的其他要素，或者是还包括为这种过程、方法、物品或者设备所固有的要素。在没有更多限制的情况下，由语句“包括一个”限定的要素，并不排除在包括所述要素的过程、方法、物品或者设备中还存在另外的相同要素。本专利的申请文件中，如果提到根据某要素执行某行为，则是指至少根据该要素执行该行为的意思，其中包括了两种情况：仅根据该要素执行该行为、和根据该要素和其它要素执行该行为。多个、多次、多种等表达包括2个、2次、2种以及2个以上、2次以上、2种以上。

在本申请提及的所有文献都被认为是整体性地包括在本申请的公开内容中，以便在必要时可以作为修改的依据。此外应理解，在阅读了本申请的上述公开内容之后，本领域技术人员可以对本申请作各种改动或修改，这些等价形式同样落于本申请所要求保护的范围。

Claims (10)

1.一种收集MySQL数据库执行的SQL数据的系统，其特征在于，包括：监控代理模块、存储队列、应用模块和监控数据库；

所述监控代理模块，用于监控并收集所述MySQL数据库指定端口传输的数据，解析出所述数据中的SQL数据，并将所述SQL数据输出到所述存储队列中；

所述存储队列，用于接收并临时存储所述监控代理模块输出的所述SQL数据；

所述应用模块，用于将所述存储队列中存储的所述SQL数据写入到所述监控数据库中；

所述监控数据库，用于存储所述SQL数据。

2.如权利要求1所述的收集MySQL数据库执行的SQL数据的系统，其特征在于，还包括：

应用分析模块，用于对所述监控数据库中存储的所述SQL数据进行分析和归类。

3.如权利要求2所述的收集MySQL数据库执行的SQL数据的系统，其特征在于，还包括：

数据展现模块，用于展示所述应用分析模块对所述SQL数据的分析和归类结果。

4.如权利要求1所述的收集MySQL数据库执行的SQL数据的系统，其特征在于，所述应用模块，还用于控制将所述存储队列中存储的所述SQL数据写入到所述监控数据库中的速度。

5.如权利要求2所述的收集MySQL数据库执行的SQL数据的系统，其特征在于，所述应用分析模块，对所述SQL数据的执行时长、消耗IO、访问用户、来源IP、访问数据库、返回数据行数进行分析。

6.一种收集MySQL数据库执行的SQL数据的方法，用于权利要求1-5所述的系统，其特征在于，所述方法包括以下步骤：

监控并收集所述MySQL数据库指定端口传输的数据，解析出所述数据中的SQL数据，并将所述SQL数据输出到存储队列中；

将所述存储队列中存储的所述SQL数据写入到监控数据库中。

7.如权利要求6所述的收集MySQL数据库执行的SQL数据的方法，其特征在于，在所述将所述存储队列中存储的所述SQL数据写入到监控数据库中的步骤之后，还包括以下步骤：

对所述监控数据库中存储的所述SQL数据进行分析和归类。

8.如权利要求7所述的收集MySQL数据库执行的SQL数据的方法，其特征在于，在所述对所述监控数据库中存储的所述SQL数据进行分析和归类的步骤之后，还包括以下步骤：

展示对所述SQL数据的分析和归类结果。

9.如权利要求6所述的收集MySQL数据库执行的SQL数据的方法，其特征在于，在所述将所述存储队列中存储的所述SQL数据写入到监控数据库中的步骤之后，还包括以下子步骤：

控制将所述存储队列中存储的所述SQL数据写入到所述监控数据库中的速度。

10.如权利要求7所述的收集MySQL数据库执行的SQL数据的方法，其特征在于，在所述对所述监控数据库中存储的所述SQL数据进行分析和归类的步骤中，对所述SQL数据的执行时长、消耗IO、访问用户、来源IP、访问数据库、返回数据行数进行分析。

Images