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.
You can convert it to human readable / normal date format like so:
mysql> SELECT from_unixtime(UNIX_TIMESTAMP(MAX(UPDATE_TIME))) as last_update FROM information_schema.tables WHERE TABLE_SCHEMA=’blah’ GROUP BY TABLE_SCHEMA;
+———————+
| last_update |
+———————+
| 2010-06-14 05:40:15 |
+———————+
1 row in set (0.05 sec)
mysql>
Is it also possible to tell when someone last opened the database?
If you are looking for a list of un-used databases… I find this more accurate.
— Turn on user stats and then wait some length of time.
SET GLOBAL userstat = 1;
— find databases not being used
SELECT
table_schema
FROM
information_schema.tables
WHERE table_schema NOT IN
(SELECT
table_schema
FROM
INFORMATION_SCHEMA.TABLE_STATISTICS
GROUP BY table_schema)
GROUP BY table_schema ;
I’ve tried the these on an older MySQL 5.0.37 DB, a MySQL 5.0.95 and a more current MariaDB 5.5.44 and they get Lots of false negatives for all the above queries in my databases.
Lots of NULL update_time values for tables I *know* are actively being used.
Perhaps this is the last time the *schema* itself was updated? (instead of just a regular INSERT, UPDATE, DELETE?)