Looking for a way to pull the last update time of all mysql databases rather than querying each one individually? I had this question posed to me earlier today and decided to document this here as my google searches were returning lots of advice that was close, but not really what I thought should be a fairly straight forward question.

After a bit of fiddling around, I finally came to:

select * from (SELECT UPDATE_TIME, TABLE_SCHEMA FROM information_schema.tables where UPDATE_TIME IS NOT NULL ORDER BY TABLE_SCHEMA, UPDATE_TIME desc)Q group by TABLE_SCHEMA;

For example:


MariaDB [(none)]> select * from (SELECT UPDATE_TIME, TABLE_SCHEMA FROM information_schema.tables where UPDATE_TIME IS NOT NULL ORDER BY TABLE_SCHEMA, UPDATE_TIME desc)Q group by TABLE_SCHEMA;
+---------------------+--------------------+
| UPDATE_TIME | TABLE_SCHEMA |
+---------------------+--------------------+
| 2017-01-02 09:50:56 | information_schema |
| 2016-07-14 10:45:23 | mysql |
+---------------------+--------------------+

Or from a test environment I spun up to make sure I was getting the actual results I wanted.

mysql> select * from (SELECT UPDATE_TIME, TABLE_SCHEMA FROM information_schema.tables where UPDATE_TIME IS NOT NULL ORDER BY TABLE_SCHEMA, UPDATE_TIME desc)Q group by TABLE_SCHEMA;
+---------------------+--------------------+
| UPDATE_TIME | TABLE_SCHEMA |
+---------------------+--------------------+
| 2017-01-02 09:44:32 | db1 |
| 2017-01-02 09:50:10 | db2 |
| 2017-01-02 08:49:48 | db3 |
| 2016-09-14 14:12:11 | mysql |
| 2017-01-02 09:46:37 | db4 |
+---------------------+--------------------+
5 rows in set (0.01 sec)

Using this query, you should be able to see at a glance what the last update time for every database on your system was.


Category: mysql

Leave a Reply

Categories


gives good tech

tech.superhappykittymeow.com
Kale is one of the smartest people I know

Racker Hacker
Major is always good for leet deetz