Server Fault is a question and answer site for system and network administrators. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I'm creating MySQL dump of 5 databases every hour. 3 of those databases very rarely change, so creating a MySQL dump for those 3 dbs is pretty much a waste of time and resources.

Is there a way I can retrieve a unix epoch seconds of when a specific db was last changed/updated? I would compare it with latest dump file and only dump another one if there are changes.

So question again: How can I get the unix epoch datetime of last update/change of a specific database?

share|improve this question

it's not exactly answer to what you are looking for, but i think that's what you need: enable binary logging, backup binlogs and create full dumps once a week or so.

share|improve this answer
aren't binary logs going to affect performance? And would I backup those logs or just use them for information? – user80666 Jun 9 '11 at 22:03
i think you gain more than you lose by turning them on. – pQd Jun 10 '11 at 9:50

I use:

    mysql -e "use <NAMEOFTHEDATABASE>;SELECT MAX(UPDATE_TIME) FROM information_schema.TABLES WHERE TABLE_SCHEMA = DATABASE() ;" | grep -v "\----" | grep -v "MAX(UPDATE_TIME)" | awk '{print $1}'


share|improve this answer

There is a tool from Maatkit that can do quick checksums on tables. It is mk-table-checksum

You could probably lock the tables, run mk-table-checksum and store the checksums, then unlock the tables and then look at the values to see if you need to run mysqldump or not.

share|improve this answer
Would work although it probably would place the same kind of stress onto the database as an entire mysqldump would do. – the-wabbit Jun 10 '11 at 7:27

Only works for MyISAM tables

You can run a MySQL query against the information_schema table:

Example (replace dbname with your database name):

FROM information_schema.tables 
share|improve this answer
I did a simple test with MySQL's world DB (updated the population of a City) and that query returns NULL after the update. – HTTP500 Jun 9 '11 at 22:08
Db name is 'world' ? Could you post up the output of SELECT UPDATE_TIME FROM information_schema WHERE TABLE_SCHEMA='world' onto – Philip Reynolds Jun 9 '11 at 22:11
@Phil, I think you meant FROM TABLES? Anyway, here is the output: Yes, the DB name is world. – HTTP500 Jun 9 '11 at 22:24
Running this on each database on my server I get a complete mix of results, with some databases returning a value and others returning either NULL or nothing at all. Not exactly something that can be relied on. – John Gardeniers Jun 9 '11 at 22:58
This seems to vary depending on table type and platform. i.e. it doesn't work for InnoDB tables.… – Philip Reynolds Jun 9 '11 at 23:03

This question comes up from time to time and the short answer is that this information cannot be reliably obtained by any one method for the simple reason that it is not necessarily recorded.

For MyISAM tables you can use the query Phil posted. I don't know about any of the others but for InnoDB at least there is no query that will return the information you want. In this case you might consider using triggers to record the timestamp in a table whenever the data is changed but realistically the performance loss will most likely be greater that just going ahead with the dump.

share|improve this answer
So I guess I need to know "How do others do it"? Is binary logs the answer? – user80666 Jun 10 '11 at 0:46
@user80666, I don't know what the answer is, although it's worth editing your question to let people know what DB engines you're dealing with so as to try and get better answers. Based on your concerns about performance I'd suggest simply setting up a slave and running your backups from that. – John Gardeniers Jun 10 '11 at 0:53

I found this metod, from

I'm not sure if it could 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.

ORDER BY 1, 2;
share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.