So the scenario goes you have inherited a some legacy systems and your auditing what is being used or not, since the old person in charge was bad at cleaning up after himself, with legacy servers strewn about and then… uh oh you come across a MySQL 5.x database server with multiple DB’s.
Assuming you can login to MySQL as root or something with similar privileged you can run the following to check the epoch time of the last update on each individual database (using MyISAM tables):
SELECT UNIX_TIMESTAMP(MAX(UPDATE_TIME)) as last_update FROM information_schema.tables WHERE TABLE_SCHEMA='replace-with-database-name' GROUP BY TABLE_SCHEMA; |
Sample output to expect (you will need to convert your epoch time):
+-------------+ | last_update | +-------------+ | 1316645223 | +-------------+ 1 row in set (0.00 sec) |
If you happen to be running Innodb you can use the following on a table to table bases:
SELECT update_time FROM information_schema.tables WHERE table_schema='replace-with-database-name' AND table_name='replace-with-table-name'; |
Sample output to expect:
+---------------------+ | update_time | +---------------------+ | 2011-09-21 15:47:03 | +---------------------+ 1 row in set (0.00 sec) |
Another good thing to do is check what processes are running:
SHOW FULL PROCESSLIST\G |
Sample output to expect when no processes are happening:
*************************** 1. row *************************** Id: 20 User: root Host: localhost db: NULL Command: Query Time: 0 State: NULL Info: SHOW FULL PROCESSLIST Rows_sent: 0 Rows_examined: 0 Rows_read: 150 1 row in set (0.00 sec) |
Another query you can run to find out what tables have changed in the past X hours is:
*Note this can be a intense query so do it when its the right time to
SELECT table_schema,table_name,update_time FROM information_schema.tables WHERE update_time > (NOW() - INTERVAL PUT-NUMBER-HERE HOUR); |
Expected output would be something like this:
*Note the information_schema tables will pretty much always pop-up
+--------------------+-------------+---------------------+ | table_schema | table_name | update_time | +--------------------+-------------+---------------------+ | information_schema | COLUMNS | 2012-12-19 19:07:48 | | information_schema | EVENTS | 2012-12-19 19:07:48 | | information_schema | PARTITIONS | 2012-12-19 19:07:48 | | information_schema | PLUGINS | 2012-12-19 19:07:48 | | information_schema | PROCESSLIST | 2012-12-19 19:07:48 | | information_schema | ROUTINES | 2012-12-19 19:07:48 | | information_schema | TRIGGERS | 2012-12-19 19:07:48 | | information_schema | VIEWS | 2012-12-19 19:07:48 | | your_apps_table1 | app1 | 2012-12-19 19:06:27 | | your_apps_table2 | app2 | 2012-12-19 19:06:28 | +--------------------+-------------+---------------------+ 10 rows in set (0.00 sec) |
Hope this gives a little help to your MySQL auditing adventures, obviously there are way more ways to check usage but these are a couple of simple ways.