Is there any way I - via any commmand - can see which data have last been added to a MySQL DB and when that data was added?

Dist: Debian 7.0 (wheezy)


Not directly. However there are two ways you could get this information:

A. Use of logs

MySQL provides the ability to store changes in a "log". This is primarily used for replication and backup.

Prior to 5.0 this was an ASCII format containing all SQL statements that actually made changes to the data - http://dev.mysql.com/doc/refman/4.1/en/update-log.html

However, 5.0 and later move to a binary log format which would require some type of analysis (though this is no doubt possible either through a standalone utility or using a second replica of MySQL to just see changes processing is outside the scope of my knowledge and this answer) - http://dev.mysql.com/doc/refman/5.0/en/binary-log.html

B. A messier approach would be to check for file update times (these binary files are where the databases are stored on disk). Theoretically you could see which DBs have been updated (files updated with timestamp - though this is liable to be imperfect owing to indexing and other non-update changes), and then look at the individual databases.

Using this approach would only indicate which databases might have been updated and certainly not indicate to what extent or which tables.

An alternative may be a custom script that gets a list of all your tables and stores perhaps the highest primary key (for auto increment keys), or the number of rows, then periodically checks again (all of this can be easily automated as MySQL provides commands to describe table structure etc).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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