MySQL, is there a way to tell when was database last changed/modified?

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;

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!

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.

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.

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.

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;

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';"

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.