15

I'm working with MySQL and I would like to get the date/time of the very last change to any table within a database. Each of my tables has an auto updating '*_modified' timestamp so I could use this (* is a prefix).

The purpose of this is to work out if the data has changed at all since the date/time of the last database backup.

I was wondering is there an simpler way to do this using DESCRIBE or SHOW? I've searched and experimented, but found nothing yet.

Thanks for the help.

RichM
  • 272
  • 1
  • 2
  • 14

5 Answers5

18
SELECT update_time
FROM   information_schema.tables
WHERE  table_schema = 'dbName'
       AND table_name = 'tableName'
raphinesse
  • 19,068
  • 6
  • 39
  • 48
Zak
  • 6,976
  • 2
  • 26
  • 48
  • Hi Zak, thanks for the answer - exactly what I was looking for. Just one supplimentary question, if I may... on my Linux system the query had to be updated to information_schema.TABLES rather than the lower case example. Will this be safe and stable considering it'll always by hosted on Linux? Thanks – RichM Nov 15 '12 at 23:43
  • The case sensitivity should not be an issue if you are using PHP to access it .. To be honest I am not sure about .sh scripts etc .. – Zak Nov 16 '12 at 01:19
  • This returns only changes to the schema of the tables right? Changes to the data of the table go unnoticed with this approach. – petermeissner Jun 03 '14 at 18:04
  • 3
    Actually it returns the `TIME` that data was changed IN the table. That is stored in the Schema, and not the table itself. It's not getting the time that the schema was changed (I don't even think MySQL stores that info) -- It's getting the time that the table was last queried with an `UPDATE`, `INSERT`, etc etc etc. To the best of my knowledge, and you are welcome to provide evidence otherwise, every time you query a table like so, it updates the schema UPDATE_TIME for said table. – Zak Jul 13 '14 at 22:55
  • 4
    For **InnoDB** tables `update_time` gives `NULL` always (databases below MySQL 5.7.2). http://bugs.mysql.com/bug.php?id=14374 – inckka Jan 09 '17 at 10:22
  • 2
    Be careful using update time from `information_schema.tables`. It's a volatile value and can come back NULL after a server restart. Avoid using it for situations where the integrity of your data depends on it being correct. – O. Jones Dec 15 '17 at 13:03
  • Good point -- I use it sometimes just to detect if a table was updated without using timestamp .. More of a cursory check rather than a "solid" way to check for updates ... That's why we use `timestamp` - `ON_UPDATE` lol – Zak Dec 15 '17 at 17:56
  • What if I need to know the last time ANY table was updated in a database, rather than specifying one table? – Elliott B Aug 01 '18 at 22:01
  • @ElliottB then remove `AND table_name = 'tableName'`, and google how to get `max` of a column. Here, `update_time` is that column - or see Giovanni's answer `MAX(UPDATE_TIME)` – ToolmakerSteve Apr 02 '19 at 20:17
  • Refer to https://stackoverflow.com/questions/2785429 for innodb. – Otheus Sep 26 '22 at 09:03
3

I found this metod, from http://mysqladministrators.blogspot.it/2012/02/get-database-size.html

I'm not sure if it can help you, since i'm not so prepared in MySql

Get the database size, free space and last update

To get the current database size just by querying into your query browser or CLI from the INFORMATION_SCHEMA database in table TABLES.

SELECT table_schema "Data Base Name", 
sum( data_length + index_length ) / 1024 / 1024 "Data Base Size in MB" 
FROM information_schema.TABLES 
GROUP BY table_schema ;

Get the database free space

SELECT table_schema "Data Base Name", 
sum( data_length + index_length ) / 1024 / 1024 "Data Base Size in MB", 
sum( data_free )/ 1024 / 1024 "Free Space in MB" 
FROM information_schema.TABLES 
GROUP BY table_schema;

Get the database last update ordered by update time then by create time.

SELECT MAX(UPDATE_TIME), MAX(CREATE_TIME), TABLE_SCHEMA 
FROM `TABLES` 
GROUP BY TABLE_SCHEMA 
ORDER BY 1, 2;
Giovanni Di Maggio
  • 61
  • 3
2

Simply, to get all tables under one database use this query

SELECT TABLE_NAME,CREATE_TIME,UPDATE_TIME
FROM   information_schema.tables
WHERE  table_schema = 'database_name'

That's all

Dhanu K
  • 11,288
  • 6
  • 24
  • 38
1

The accepted answer is great, but I think a better format in case it's not immediately apparent to a rookie would be:

SELECT table_name, update_time
FROM   information_schema.tables
WHERE  table_schema = 'myDBName'
order by update_time DESC

Since, we don't always know what table(s) have been touched, and this has the bonus of giving a clue as to what the activity was by showing the most-recently-updated tables.

Oliver Williams
  • 5,966
  • 7
  • 36
  • 78
0

For a join query that covers both INNODB and MySQL, you can create separate queries for each, or try to combine into a stored function like this:

SELECT db_name,min(table_name) as table_name,update_time FROM (
    SELECT m1.table_schema AS db_name, table_name, update_time 
     FROM information_schema.tables m1
     JOIN ( 
         SELECT table_schema, MAX(update_time) AS last_update_time 
         FROM information_schema.tables group by table_schema 
     ) AS m2 ON m2.table_schema = m1.table_schema AND m2.last_update_time = m1.update_time

    UNION ALL

    SELECT i1.database_name AS db_name, table_name, last_update AS update_time
     FROM mysql.innodb_table_stats as i1
     JOIN ( 
         SELECT database_name, MAX(last_update) as last_update_time 
         FROM mysql.innodb_table_stats GROUP BY database_name
     ) AS i2 ON i2.database_name = i1.database_name AND i2.last_update_time = i1.last_update
) AS combined
  WHERE db_name NOT IN ('mysql','sys') GROUP BY db_name;

Output should be something like:

+----------+--------------+---------------------+
| db_name  | table_name   | update_time         |
+----------+--------------+---------------------+
| db1      | asset        | 2022-09-26 18:20:53 |
| db2      | session      | 2020-09-30 16:23:24 |
+----------+--------------+---------------------+
2 rows in set (0.06 sec)
Otheus
  • 785
  • 10
  • 18