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.
SELECT update_time
FROM information_schema.tables
WHERE table_schema = 'dbName'
AND table_name = 'tableName'
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;
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With