How to Tell if a MySQL Database is Being Used

December 19, 2012

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):

Copy Code 


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):

Copy Code 


+-------------+
| 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:

Copy Code 


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:

Copy Code 


+---------------------+
| 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:

Copy Code 


SHOW FULL PROCESSLIST\G

Sample output to expect when no processes are happening:

Copy Code 


*************************** 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

Copy Code 


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

Copy Code 


+--------------------+-------------+---------------------+
| 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.

Posted in Databases

Tagged ,

Comments are closed.