MySQL 慢日志收集平台详解

ELK 介绍

ELK 最早是 Elasticsearch(以下简称ES)、Logstash、Kibana 三款开源软件的简称,三款软件后来被同一公司收购,并加入了Xpark、Beats等组件,改名为Elastic Stack,成为现在最流行的开源日志解决方案,虽然有了新名字但大家依然喜欢叫她ELK,现在所说的ELK就指的是基于这些开源软件构建的日志系统。

我们收集mysql慢日志的方案如下:

1535077408-4711-7FjictpBGeByvulpbIXxibcnOpMA

  • mysql 服务器安装 Filebeat 作为 agent 收集 slowLog
  • Filebeat 读取 mysql 慢日志文件做简单过滤传给 Kafka 集群
  • Logstash 读取 Kafka 集群数据并按字段拆分后转成 JSON 格式存入 ES 集群
  • Kibana读取ES集群数据展示到web页面上

慢日志分类

目前主要使用的mysql版本有5.5、5.6 和 5.7,经过仔细对比发现每个版本的慢查询日志都稍有不同,如下:

5.5 版本慢查询日志

 

5.6 版本慢查询日志

 

5.7 版本慢查询日志

1535077408-1356-PNe8c6IlfcCbvgXtTabhSb9TXmaw

慢查询日志异同点:

  1. 每个版本的Time字段格式都不一样
  2. 相较于5.6、5.7版本,5.5版本少了Id字段
  3. use db语句不是每条慢日志都有的
  4. 可能会出现像下边这样的情况,慢查询块# Time:下可能跟了多个慢查询语句

1535077408-4980-lKvIYRERaSHVcMH61JibC5ibOszg

处理思路

上边我们已经分析了各个版本慢查询语句的构成,接下来我们就要开始收集这些数据了,究竟应该怎么收集呢?

  1. 拼装日志行:mysql 的慢查询日志多行构成了一条完整的日志,日志收集时要把这些行拼装成一条日志传输与存储。
  2. Time行处理:# Time: 开头的行可能不存在,且我们可以通过SET timestamp这个值来确定SQL执行时间,所以选择过滤丢弃Time行
  3. 一条完整的日志:最终将以# User@Host: 开始的行,和以SQL语句结尾的行合并为一条完整的慢日志语句
  4. 确定SQL对应的DB:use db这一行不是所有慢日志SQL都存在的,所以不能通过这个来确定SQL对应的DB,慢日志中也没有字段记录DB,所以这里建议为DB创建账号时添加db name标识,例如我们的账号命名方式为:projectName_dbName,这样看到账号名就知道是哪个DB了
  5. 确定SQL对应的主机:我想通过日志知道这条SQL对应的是哪台数据库服务器怎么办?

    慢日志中同样没有字段记录主机,可以通过filebeat注入字段来解决,例如我们给filebeat的name字段设置为服务器IP,这样最终通过beat.name这个字段就可以确定SQL对应的主机了。

Filebeat配置

filebeat 完整的配置文件如下:

 

重要参数解释:

  • input_type:指定输入的类型是log或者是stdin
  • paths:慢日志路径,支持正则,比如/data/*.log
  • exclude_lines:过滤掉# Time开头的行
  • multiline.pattern:匹配多行时指定正则表达式,这里匹配以# Time或者# User开头的行,Time行要先匹配再过滤
  • multiline.negate:定义上边pattern匹配到的行是否用于多行合并,也就是定义是不是作为日志的一部分
  • multiline.match:定义如何将皮排行组合成时间,在之前或者之后
  • tail_files:定义是从文件开头读取日志还是结尾,这里定义为true,从现在开始收集,之前已存在的不管
  • name:设置filebeat的名字,如果为空则为服务器的主机名,这里我们定义为服务器IP
  • output.kafka:配置要接收日志的kafka集群地址可topic名称

Kafka 接收到的日志格式:

 

{“@timestamp”:”2018-08-07T09:36:00.140Z”,”beat”:{“hostname”:”db-7eb166d3″,”name”:”10.63.144.71″,”version”:”5.4.0″},”input_type”:”log”,”message”:”# User@Host: select[select] @  [10.63.144.16]  Id: 23460596\n# Query_time: 0.155956  Lock_time: 0.000079 Rows_sent: 112  Rows_examined: 366458\nSET timestamp=1533634557;\nSELECT DISTINCT(uid) FROM common_member WHERE hideforum=-1 AND uid != 0;”,”offset”:1753219021,”source”:”/data/slow/mysql_slow.log”,”type”:”log”}

Logstash配置

logstash完整的配置文件如下:

 

重要参数解释:

  • input:配置 kafka 的集群地址和 topic 名字
  • filter:过滤日志文件,主要是对 message 信息(看前文 kafka 接收到的日志格式)进行拆分,拆分成一个一个易读的字段,例如User、Host、Query_time、Lock_time、timestamp等。

    grok段根据我们前文对mysql慢日志的分类分别写不通的正则表达式去匹配,当有多条正则表达式存在时,logstash会从上到下依次匹配,匹配到一条后边的则不再匹配。

    date字段定义了让SQL中的timestamp_mysql字段作为这条日志的时间字段,kibana上看到的实践排序的数据依赖的就是这个时间

  • output:配置ES服务器集群的地址和index,index自动按天分割

kibana查询展示

打开Kibana添加 mysql-slowlog-* 的Index,并选择timestamp,创建Index Pattern

 

进入Discover页面,可以很直观的看到各个时间点慢日志的数量变化,可以根据左侧Field实现简单过滤,搜索框也方便搜索慢日志,例如我要找查询时间大于2s的慢日志,直接在搜索框输入 query_time: > 2 回车即可。

 

点击每一条日志起边的很色箭头能查看具体某一条日志的详情。

1535077408-6683-Dibdog7XPG3UGEDBUZgSyY2OWTeA

如果你想做个大盘统计慢日志的整体情况,例如top 10 SQL等,也可以很方便的通过web界面配置。

1535077408-2887-AYtL3RW4ibMUQbmx6n9kFwdTsibw

mysql 已设置utf8mb4 还是存不了表情【解决方法】

1,emoji介绍

(1)emoji 就是表情符号,来自日语词汇“絵文字”(假名为“えもじ”,读音即 emoji)。
(2)最早由栗田穰崇(Shigetaka Kurita)创作,并在日本网络及手机用户中流行。 自苹果公司发布的 iOS 5 输入法中加入了 emoji 后,这种表情符号开始席卷全球。
(3)目前 emoji 已被大多数现代计算机系统所兼容的 Unicode 编码采纳,普遍应用于各种手机短信和社交网络中。
1535075603-7981-201610
2,问题描述

最近使用 MySQL 作为一个移动应用的数据库。但是不管使用 Anroid 设备,还是 iOS 设备。只要插入包含有 emoji 表情符号的记录时就报错。
1535075604-3223-201610

3,问题原因

MySQL 我使用的是默认的 utf8 编码,UTF8 编码只支持 1-3 个字节。而 emoji 占有 4 个字节的存储空间,所以自然保存不了。
从 MYSQL5.5 开始,可支持 4 个字节 UTF 编码,只要将编码标记成 utf8mb4 即可。并且 utf8mb4 是兼容 utf8 的。

了解一下mysql分区示例(partition)

一.数据分区

mysql数据库中的数据是以文件的形式存储在磁盘介质上,具体存储目录,请查阅“my.cnf”中的“datadir”属性。一张数据表主要对应着三个文件,一个是FRM存放表结构的,一个是MYD存放表数据的,一个是MYI存表索引的。如果一张表的数据量太大的话,那么MYD、MYI就会变的很大,查找数据就会变得很慢,这个时候可以利用mysql的分区功能,在物理上将这一张表对应的三个文件,分割成许多个小块,这样做的话,我们查找一条数据时,就不用全部查找了,只要知道这条数据在哪一块,然后在那一块找就行了。如果表的数据太大,可能一个磁盘放不下,这个时候,我们可以把数据分配到不同的磁盘里面去。

1.横向分区

就是横着来分区,举例:假如有100W条数据,分成十份,前10W条数据放到第一个分区,第二个10W条数据放到第二个分区,依此类推。也就是把表分成了十分,根用merge来分表,有点像哦。取出一条数据的时候,这条数据包含了表结构中的所有字段,也就是说横向分区,并没有改变表的结构。

2.纵向分区

什么是纵向分区呢?就是竖来分区了,举例来说明,在设计用户表的时候,开始的时候没有考虑好,而把个人的所有信息都放到了一张表里面去,这样这个表里面就会有比较大的字段,如个人简介,而这些简介呢,也许不会有好多人去看,所以等到有人要看的时候,在去查找,分表的时候,可以把这样的大字段,分开来。

mysql提供的分区属于第一种,横向分区,并且细分成很多种方式

一款性能监控好工具 – NewRelic

在读《高性能MySql》一书中,认识了NewRelic,但是还未能及时用上,所以在网上找了一个帖子以记录。

NewRelic是一家提供Rails性能监测服务的网站, NewRelic提供了不同级别的监测功能,免费的是Lite版本,最高有Gold版本.

New Relic工具有两种运行模式:

(1)Production 模式:当您的工程以生产模式运行时,您可以在rpm.newrelic.com 网站上实时对它进行监督。

(2)Developer模式:当您的工程以开发模式运行时,您可以在本地localhost:3000/newrelic网页上查看到性能分析数据。

 

New Relic工具安装步骤(默认Rails 3版本):

  1. gem install newrelic_rpm
  2. 在Gemfile文件中,添加gem ‘newrelic_rpm’语句

3.在当前工程下,bundle update

  1. 在newrelic.com/get-RPM.html注册Lite版本的账号,然后将从此网站下载到的newrelic.yml文件,复制到当前工程的config文件夹中。
  2. 在development模式下启动工程,访问工程,然后在http://localhost:3000/newrelic页面您会看到对当前工程的性能分析数据。

6.若在production模式下启动工程,则您访问http://localhost:3000/newrelic链接会失效,这时您可以通过https://rpm.newrelic.com/login进行登录,在NewRelic的网站上查看关于工程的性能分析数据。

了解mysql基准测试工具sysbench

1、什么是基准测试

数据库的基准测试是对数据库的性能指标进行定量的、可复现的、可对比的测试。

基准测试与压力测试

基准测试可以理解为针对系统的一种压力测试。但基准测试不关心业务逻辑,更加简单、直接、易于测试,数据可以由工具生成,不要求真实;而压力测试一般考虑业务逻辑(如购物车业务),要求真实的数据。

2、基准测试的作用

对于多数Web应用,整个系统的瓶颈在于数据库;原因很简单:Web应用中的其他因素,例如网络带宽、负载均衡节点、应用服务器(包括CPU、内存、硬盘灯、连接数等)、缓存,都很容易通过水平的扩展(俗称加机器)来实现性能的提高。而对于MySQL,由于数据一致性的要求,无法通过增加机器来分散向数据库写数据带来的压力;虽然可以通过前置缓存(Redis等)、读写分离、分库分表来减轻压力,但是与系统其它组件的水平扩展相比,受到了太多的限制。

而对数据库的基准测试的作用,就是分析在当前的配置下(包括硬件配置、OS、数据库设置等),数据库的性能表现,从而找出MySQL的性能阈值,并根据实际系统的要求调整配置。

12312