Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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
add comment

5 Answers

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
add comment

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
add comment

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}'

Regards!

share|improve this answer
add comment

Only works for MyISAM tables

You can run a MySQL query against the information_schema table:

Example (replace dbname with your database name):

SELECT UNIX_TIMESTAMP(MAX(UPDATE_TIME)) as last_update 
FROM information_schema.tables 
WHERE TABLE_SCHEMA='dbname' 
GROUP BY TABLE_SCHEMA;
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 codepad.org? –  Philip Reynolds Jun 9 '11 at 22:11
    
@Phil, I think you meant FROM TABLES? Anyway, here is the output: codepad.org/WP14ErTk 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. stackoverflow.com/questions/5622178/… –  Philip Reynolds Jun 9 '11 at 23:03
show 1 more comment

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
1  
@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
add comment

Your Answer

 
discard

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.