要找到实例里面某个表是否有访问,确实是比较难搞。
常用方法就是 开tcpdump或tshark持续抓包过滤怀疑的表名。
但是,如果我们用的是Percona分支的MySQL,这件事就变得简单多了。
Percona MySQL 下,我们可以打开userstat参数,通过查询系统统计表来获取用户、客户端和库表访问情况,注意:该功能只在MySQL的 Percona 发行版有效,社区版不带这个功能。
(none) > status -------------- mysql Ver 14.14 Distrib 5.6.38-83.0, for Linux (x86_64) using 6.2 Connection id:1222143 Current database: Current user:root@localhost SSL:Not in use Current pager:stdout Using outfile:'/root/audit.log' Using delimiter:; Server version:5.6.38-83.0-log Percona Server (GPL), Release 83.0, Revision dc97471bd40 Protocol version:10 Connection:Localhost via UNIX socket Server characterset:utf8 Db characterset:utf8 Client characterset:utf8 Conn. characterset:utf8 UNIX socket:/tmp/mysql.sock Uptime:110 days 8 hours 34 min 25 sec Threads: 32 Questions: 1074250447 Slow queries: 1474 Opens: 136 Flush tables: 1 Open tables: 129 Queries per second avg: 112.665 -------------- (none) > use INFORMATION_SCHEMA (INFORMATION_SCHEMA) > show tables like '%_STATISTICS'; +---------------------------------------------+ | Tables_in_information_schema (%_STATISTICS) | +---------------------------------------------+ | CLIENT_STATISTICS | | INDEX_STATISTICS | | TABLE_STATISTICS | | THREAD_STATISTICS | | USER_STATISTICS | +---------------------------------------------+ 5 rows in set (0.00 sec) set global userstat=ON; select * from INFORMATION_SCHEMA.CLIENT_STATISTICS; select * from INFORMATION_SCHEMA.INDEX_STATISTICS; select * from INFORMATION_SCHEMA.TABLE_STATISTICS; select * from INFORMATION_SCHEMA.THREAD_STATISTICS; select * from INFORMATION_SCHEMA.USER_STATISTICS;
如果要清理统计信息,可以使用下面的这几个命令:
FLUSH CLIENT_STATISTICS ; FLUSH INDEX_STATISTICS ; FLUSH TABLE_STATISTICS ; FLUSH THREAD_STATISTICS ; FLUSH USER_STATISTICS ;