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

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.

Posted in Databases

Tagged ,

Comments for “How to Tell if a MySQL Database is Being Used”

  1. James Berger commented on April 29, 2013

    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>

  2. Alan McKay commented on October 21, 2013

    Is it also possible to tell when someone last opened the database?

  3. Mark Grennan commented on November 4, 2014

    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 ;

  4. DanL commented on April 13, 2016

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

  5. Sergio Alonso commented on July 30, 2019

    Thanks buddy
    Mi contribution. From the OS, you could also use
    find /var/lib/mysql -name ‘*’ | xargs stat | grep -v ‘Change\|Device\|Size\|Uid’ | less

    Output:

    File: `/var/lib/mysql/printer_monitor/models.MYD’
    Access: 2018-08-09 11:04:45.000000000 -0300
    Modify: 2018-08-09 11:04:45.000000000 -0300

    File: `/var/lib/mysql/averyolddatabase/users.MYD’
    Access: 2011-08-12 00:39:36.000000000 -0300
    Modify: 2011-08-12 00:39:36.000000000 -0300

Leave a Reply