12

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?

8 Answers 8

6

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;
6
  • 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, 2011 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? Jun 9, 2011 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, 2011 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. Jun 9, 2011 at 22:58
  • 1
    This seems to vary depending on table type and platform. i.e. it doesn't work for InnoDB tables. stackoverflow.com/questions/5622178/… Jun 9, 2011 at 23:03
4

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!

1
  • 1
    I only see entries for MyISAM tables, all the innodb tables have update_time as null.
    – slim
    Jun 11, 2018 at 19:37
3

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.

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

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.

2
  • 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, 2011 at 7:27
  • @the-wabbit Not sure about that: Check the doc: "..Checksums typically take about twice as long as COUNT (*) on very large InnoDB tables in my tests. For smaller tables, COUNT (*) is a good bit faster than the checksums". By the way, it has been replaced (in ubuntu at least) by pt-table-checksum from percona toolkit
    – Marinos An
    Jul 29, 2020 at 9:13
2

The way I do this is not automated: you have to still read the modified times, but FWIW...

SELECT TABLE_SCHEMA, UNIX_TIMESTAMP(MAX(UPDATE_TIME)) as last_update  FROM information_schema.tables  WHERE TABLE_SCHEMA in ('shovel', 'sleek', 'whiskerando', 'pickle')  GROUP BY
TABLE_SCHEMA;

This will list the the modified times for the four tables shown in parentheses.

1

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

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.

SELECT MAX(UPDATE_TIME), MAX(CREATE_TIME), TABLE_SCHEMA 
FROM `TABLES` 
GROUP BY TABLE_SCHEMA 
ORDER BY 1, 2;
1

Due to not being able to get update_time for innodb tables I check the db files directly:

find /var/lib/mysql -name \*.frm | xargs ls -l --time-style="+%m-%d-%Y"  | awk '{print $6,$7}'

This gives me days which I use to verify tables are inactive and can be archived.

You are looking for unix timestamp which just needs a change to ls's time-style:

find /var/lib/mysql -name \*.frm | xargs ls -l --time-style="+%s"  | awk '{print $6,$7}'

This assumes your database files are in /var/lib/mysql but you can get that location by running the following query:

mysql -e "SHOW VARIABLES LIKE 'datadir';"
2
  • 1
    Wouldn't it be better to check the *.MYD files?
    – simon
    Mar 20, 2021 at 21:31
  • It would be a bit better for readability and sortability. Active tables have very recent time stamps and inactive tables stick out.
    – DDS
    Mar 31, 2023 at 17:23
0

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.

2
  • So I guess I need to know "How do others do it"? Is binary logs the answer?
    – user80666
    Jun 10, 2011 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. Jun 10, 2011 at 0:53

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .