mysql 跟踪_MySQL跟踪SQL&慢查询分析工具

简介

之前的工作一直使用的SQL SERVER, 用过的都知道，SQL SERVER有配套的SQL跟踪工具SQL Profiler，开发或者定位BUG过程中，可以在操作页面的时候，实时查看数据库执行的SQL语句，十分方便。最近的项目使用MySQL，没有类似的功能，感觉到十分的不爽，网上也没有找到合适的免费工具，所以自己研究做了一个简单工具。

功能

实时查询MySql执行的SQL语句

查看性能异常的SQL(执行超过2秒)

技术方案

前端vue，样式bootstrap

后台dotnet core mvc

先看一下的效果：

实现原理

Mysql支持输出日志，通过以下命令查看当前状态

show VARIABLES like '%general_log%' //是否开启输出所有日志

show VARIABLES like '%slow_query_log%' //是否开启慢SQL日志

show VARIABLES like '%log_output%' //查看日志输出方式(默认file，还支持table)

show VARIABLES like '%long_query_time%' //查看多少秒定义为慢SQL

下面我们将所有日志、慢SQL日志打开，日志输出修改为table，定义执行2秒以上的为慢SQL

set global log_output='table' //日志输出到table(默认file)

set global general_log=on; //打开输出所有日志

set global slow_query_log=on; //打开慢SQL日志

set global long_query_time=2 //设置2秒以上为慢查询

repair table mysql.general_log //修复日志表(如果general_log表报错的情况下执行)

注意：以上的设置，数据库重启后将失效，永久改变配置需要修改my.conf文件

现在日志文件都存在数据库表里面了，剩下的工作就是取数并展示出来就行了。本项目后台使用的MVC取数，然后VUE动态绑定，Bootstrap渲染样式。

前端代码

开发工具

• SQL跟踪

• 性能异常SQL

---

---

时间执行语句

{{log.time}}

@**@

{{log.sql}}

执行时长(时：分：秒，毫秒)锁定时长(时：分：秒，毫秒)开始时间数据库操作者执行语句

{{query.queryTime}}

@**@

{{query.lockTime }}

{{query.startTime }}

{{query.db }}

{{query.userHost}}

{{query.sql}}

new Vue({

el: '#app',

data: {

startShow: true,

pauseShow: false,

clearShow: true,

logs: [],

slowQuerys: []

},

methods: {

start: function () {

this.timer = setInterval(this.trace, 5000);

this.pauseShow = true;

this.startShow = false;

},

pause: function () {

clearInterval(this.timer);

this.pauseShow = false;

this.startShow = true;

},

clear: function () {

this.logs = null;

},

trace: function () {

//发送 post 请求

this.$http.post('/home/start', {}, { emulateJSON: true }).then(function (res) {

this.logs = res.body;

}, function (res) {

console.log(logs);

});

}

},

created: function () {

},

mounted: function () {

this.$http.post('/home/slow', {}, { emulateJSON: true }).then(function (res) {

this.slowQuerys = res.body;

}, function (res) {

console.log(this.slowQuerys);

});

},

destroyed: function () {

clearInterval(this.time)

}

});

后端代码

using Ade.Tools.Models;

using Microsoft.AspNetCore.Mvc;

using Microsoft.Extensions.Configuration;

using System;

using System.Collections.Generic;

using System.IO;

using System.Linq;

using System.Text.RegularExpressions;

using MySql.Data.MySqlClient;

using Microsoft.Extensions.Caching.Memory;

namespace Ade.Tools.Controllers

{

public class HomeController : Controller

{

public IConfiguration Configuration { get; set; }

public HomeController(IConfiguration configuration)

{

this.Configuration = configuration;

this.ConnStr = Configuration["Sql:DefaultConnection"];

}

public static DateTime StartTime { get; set; } = DateTime.MinValue;

public static List TableNames { get; set; }

public string ConnStr { get; set; }

public JsonResult Start()

{

if (StartTime == DateTime.MinValue)

{

StartTime = DateTime.Now;

}

int size = int.Parse(Configuration["Size"]);

string[] blackList = Configuration["Blacklist"].Split(",");

List tableNames = GetTableNames();

List logItems = new List();

List logItemDTOs = new List();

using (MySqlConnection mySqlConnection = new MySqlConnection(this.ConnStr))

{

//string sqlStart = "set global log_output='table';set global general_log=on; repair table mysql.general_log;";

//Dapper.SqlMapper.Execute(mySqlConnection, sqlStart);

logItemDTOs = Dapper.SqlMapper.Query(mySqlConnection, $" select * from mysql.general_log " +

$"where event_time>'{StartTime.ToString("yyyy-MM-dd HH:mm:ss")}' " +

$"order by event_time desc ")

//+ $"limit {size} "

.ToList();

}

logItemDTOs.ForEach(e =>

{

LogItem logItem = new LogItem()

{

Time = e.event_time.ToString("yyyy-MM-dd HH:mm:ss.fff"),

CommondType = e.command_type,

ServerId = e.server_id,

ThreadId = e.thread_id,

UserHost = e.user_host,

Sql = System.Text.Encoding.Default.GetString(e.argument)

};

if (tableNames.Any(a => logItem.Sql.Contains(a))

&& !blackList.Any(b => logItem.Sql.Contains(b))

)

{

logItems.Add(logItem);

}

});

return new JsonResult(logItems);

}

public JsonResult Slow()

{

List slowQueries = new List();

using (MySqlConnection mySqlConnection = new MySqlConnection(this.ConnStr))

{

string sql = "select * from mysql.slow_log order by query_time desc";

List slowDtos = Dapper.SqlMapper.Query(mySqlConnection, sql).ToList();

slowDtos.ForEach(e => {

slowQueries.Add(new SlowQuery()

{

DB = e.db,

LockTime = DateTime.Parse(e.lock_time.ToString()).ToString("HH:mm:ss.fffff"),

QueryTime = DateTime.Parse(e.query_time.ToString()).ToString("HH:mm:ss.fffff"),

RowsExamined = e.rows_examined,

RowsSent = e.rows_sent,

Sql = System.Text.Encoding.Default.GetString( (byte[])e.sql_text),

StartTime = e.start_time.ToString("yyyy-MM-dd HH:mm:ss"),

UserHost = e.user_host

});

});

}

return new JsonResult(slowQueries);

}

public string On()

{

using (MySqlConnection mySqlConnection = new MySqlConnection(this.ConnStr))

{

string sql = "set global log_output='table';set global general_log=on; repair table mysql.general_log;";

Dapper.SqlMapper.Execute(mySqlConnection, sql);

}

return "ok";

}

public string Off()

{

using (MySqlConnection mySqlConnection = new MySqlConnection(this.ConnStr))

{

string sql = "set global general_log=off;";

Dapper.SqlMapper.Execute(mySqlConnection, sql);

}

return "ok";

}

public string Clear()

{

using (MySqlConnection mySqlConnection = new MySqlConnection(this.ConnStr))

{

string sql = $@"

SET GLOBAL general_log = 'OFF';

RENAME TABLE general_log TO general_log_temp;

DELETE FROM `general_log_temp`;

RENAME TABLE general_log_temp TO general_log;

SET GLOBAL general_log = 'ON';

";

Dapper.SqlMapper.Execute(mySqlConnection, sql);

}

return "ok";

}

public IActionResult Index()

{

return View();

}

private List GetTableNames()

{

MemoryCache memoryCache = new MemoryCache(new MemoryCacheOptions());

var cacheKey = "MySqlProfile_TableNames";

List tableNames = memoryCache.Get >(cacheKey);

if (tableNames != null)

{

return tableNames;

}

string[] traceDbs = Configuration["TraceDatabaseNames"].Split(",");

string sqlTables = "SELECT distinct TABLE_NAME FROM information_schema.columns";

foreach (var db in traceDbs)

{

if (!sqlTables.Contains("WHERE"))

{

sqlTables += " WHERE table_schema='" + db + "'";

}

else

{

sqlTables += " OR table_schema='" + db + "'";

}

}

using (MySqlConnection mySqlConnection = new MySqlConnection(this.ConnStr))

{

// WHERE table_schema='mice'

tableNames = Dapper.SqlMapper.Query(mySqlConnection, sqlTables).ToList();

}

memoryCache.Set(cacheKey, tableNames, TimeSpan.FromMinutes(30));

return tableNames;

}

}

}

源代码

修改完appsettings.json文件里面的连接字符串以及其他配置(详情自己看注释，懒得写了)，就可以使用了。

https://github.com/holdengong/MysqlProfiler

最后一点

开启日志会产生大量的文件，需要注意定时清理

SET GLOBAL general_log = 'OFF'; // 关闭日志

RENAME TABLE general_log TO general_log_temp; //表重命名

DELETE FROM general_log_temp; //删除所有数据

RENAME TABLE general_log_temp TO general_log; //重命名回来

SET GLOBAL general_log = 'ON'; //开启日志