Percona MySQL View Tables Not Accessed

It's really hard to find out if a table in an instance has access.(

A common method is to open a tcpdump or tshark persistent packet to filter suspected table names.(


However, if we were using MySQL from the Percona branch, this would be much easier.


Under Percona MySQL, we can open the userstat parameter and query the system statistics tables to get access to the user, client, and library tables. Note that this function is only available in the Percona distribution of MySQL, not in the community version.


(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;


To clean up statistics, you can use the following commands:

FLUSH CLIENT_STATISTICS  ;
FLUSH INDEX_STATISTICS   ;
FLUSH TABLE_STATISTICS   ;
FLUSH THREAD_STATISTICS  ;
FLUSH USER_STATISTICS    ;







Tags: Linux MySQL Unix socket

Posted on Sun, 28 Jun 2020 12:51:50 -0400 by Sno