识别MySQL中活跃的数据库和用户

可以使用各种方法来识别活跃数据库。我们可以根据数据文件更新的时间戳、慢日志中的条目，解析二进制日志，解析审计日志或通用日志。不过我们可以忽略提到的通用日志，因为它会消耗大量磁盘空间，是一种很大的开销。

故我们有以下方法来识别活跃的数据库对象:

·检查慢日志以识别正在查询的数据库对象。

·在datadir文件上查找更新的时间戳。

·扫描二进制日志以识别正在修改的表。

·扫描审计日志，识别数据库对象。

在测试过程中，我们发现上述方法可以帮助我们识别活跃数据库;然而，我们有时不得不使用两种或两种以上的方法来获得最好的结果。此外，如果有按月读或写的表，审查的范围就会增加，将不得不在更长的时间内维护日志。

在Percona MySQL和MySQL社区版本中有两个统计表功能，如下:

(1)用户统计信息

在percona、mariadb版本mysql中，有另一种方法来识别活跃的数据库、用户以及其他信息。这两个版本都有userstat，该插件在缺省情况下是不开启的。

(2)Sys-schema-table-statistics

这个表提供了表的摘要信息。sys库中的状态表记录了表的统计信息，但是重启后，这些信息就丢失了。

1.使用pt-table-usage识别数据库的表

pt-table-usage从日志中读取信息，分析表的使用。可以从慢查询日志中获取。要确保以下一些参数做了配置，如：

SET GLOBAL slow_query_log=1;
SET GLOBAL long_query_time=0;
SET GLOBAL slow_query_log_use_global_control='long_query_time';

也可以加上以下的参数，做一些限制：

SET GLOBAL Log_slow_rate_type = query;
SET GLOBAL Log_slow_rate_limit = 10;

pt-table-usage打印出语句中每个表的使用，类似如下输出：

$ sudo pt-table-usage /var/lib/mysql/slow.log.log > table_usage.log

$ cat table_usage.log | head -10
Query_id: 0xA212AD93263CF26F.1
SELECT DUAL

Query_id: 0xC684EA1D78348D23.1
SELECT information_schema.innodb_metrics
WHERE information_schema.innodb_metrics

Query_id: 0x153F1CE7D660AE82.1
SELECT information_schema.processlist
WHERE information_schema.processlist

可以进一步做一下处理：

$ cat table_usage.log | egrep "WHERE|JOIN" | grep -v "__SQ" | grep -v "DUAL" | awk '{print $2}' | sort | uniq -c | sort -nr
6703 information_schema.innodb_metrics
3352 performance_schema.table_lock_waits_summary_by_table
3352 information_schema.processlist
3352 INFORMATION_SCHEMA.PLUGINS
2790 information_schema.tables
558 performance_schema.table_io_waits_summary_by_table
558 performance_schema.table_io_waits_summary_by_index_usage
558 information_schema.schemata
5 authors
4 information_schema.table_statistics

2.使用慢查询日志识别数据库

一旦我们开启了的慢日志，并运行了一段时间，就可以通过linux的命令来分析数据库的使用。

例如，列出使用的数据库

$ sudo grep Schema: /var/lib/mysql/slow.log| awk -F' ' '{print $4 $5}' | sort | uniq -c
2717 Schema:mytestdb
123 Schema:percona
762238 Schema:sbtest

以下是8.0版本的示例：

$ sudo grep Schema: /var/lib/mysql/slow.log| awk -F' ' '{print $2 $3}' | sort | uniq -c
2717 Schema:mytestdb
273 Schema:percona
223567 Schema:sbtest

3.识别MySQL中活跃的用户

方法1：使用审计日志

percona的审计插件可以监控和记录连接信息、查询活动，记录在审计日志中。在percona mysql中默认是安装了，但是没有开启。

检查是否开启了审计插件：

mysql> SELECT * FROM information_schema.PLUGINS WHERE PLUGIN_NAME LIKE '%audit%';

为了控制空间的使用，可以设置审计日志的rotation功能：

SET GLOBAL audit_log_rotate_on_size=1073741824;
SET GLOBAL audit_log_rotations=5;

配置完成后，就可以借助审计日志进行分析了：

$ sudo grep -w 'USER' /var/lib/mysql/audit.log | tail
USER="pmm[pmm] @ localhost [127.0.0.1]"
USER="pmm[pmm] @ localhost [127.0.0.1]"
USER="pmm"
USER="pmm[pmm] @ localhost [127.0.0.1]"
USER="pmm"
USER="pmm[pmm] @ localhost [127.0.0.1]"
USER="pmm[pmm] @ localhost [127.0.0.1]"
USER="pmm[pmm] @ localhost [127.0.0.1]"
USER="pmm"
USER="pmm"

方法2：从processlist记录中获取

从processlist记录中获取，信息可能不够精确，不要频繁地查看。

比如写个定时任务，每个5秒查看一次，并将记录写入文件。

$ cat processlist_DB1_20221215.log | head
================================================================================
2022-12-15 00:00:05: Uptime: 8935629 Threads: 7 Questions: 5258388359 Slow queries: 96680 Opens: 4096 Flush tables: 1 Open tables: 1981
40 sampleuser 10.11.8.11:54540 sampledb Sleep 5 None 1 1 
41 sampleuser 10.11.8.11:54542 sampledb Sleep 5 None 38 38 
64 sbtest_user 10.11.8.11:54610 sbtest Sleep 5 None 3 3 
65 sbtest_user 10.11.8.11:54612 sbtest Sleep 5 None 0 544 
66 sbtest_user 10.11.8.11:54614 sbtest Sleep 5 None 1 15 
101 sampleuser 10.11.8.11:54736 sampledb Sleep 5 None 2 14 
106 percona 10.11.8.52:53386 percona Sleep 5 None 0 0

收集以后，执行分析：

$ for f in `ls processlist_DB1*202212*`; do echo $f; cat $f | grep -e ^[1-9] | grep -vie "system\ user\|Uptime" | awk -F' ' '{print $2}' >> /tmp/USERZ;done; cat /tmp/USERZ | sort | uniq -c
11 mytestdb_user
42498 percona
8640 sbtest_user