How to check the last time a table has been modified?

Question

I regularly backup tables individually via mysqldump. To avoid unnecessary backup, I would like to check if a table has been modified since the last backup. By "modified", I mean any change (INSERT, UPDATE, DELETE, ALTER, ...).

Using the schema, we can get UPDATE_TIME like

SELECT UPDATE_TIME
FROM   information_schema.tables
WHERE  TABLE_SCHEMA = 'db' AND TABLE_NAME = 't1'

but that's not enough.

A practical way is to check the last modified time of /var/lib/mysql/t1.ibd via common Linux commands such as stat, ls, etc. The problem is that I should give the script running user permission to access /var/lib/mysql, which I prefer not to do so for security reasons.

Is there any way to check the last time a table or t1.ibd has been modified through MySQL (without giving permission to another user for accessing /var/lib/mysql)?

Answer 1

Well this turned out to be quite an interesting side project. There are a couple of ways that this can be accomplished, and Rolando shared four common methods on this site back in 2014. That said, something told me that there had to be a better way ...

You're right that looking at UPDATE_TIME in information_schema is not enough, because this value is not updated when the base tables are InnoDB. The only reliable way to determine if an InnoDB table has been modified — to the best of my knowledge — is to query the file system. However, like you said, it's not a great idea to grant access to the MySQL data directory to just anybody.

So, with this in mind, if a database is small, consisting of perhaps a dozen or so tables, the fastest way to accomplish the goal would be to use a trigger that updates a specific table, let's call it TableTracker, to say that an INSERT, UPDATE, or DELETE was committed as a given time. However, this is not particularly scalable if a database consists of hundreds of tables, nor does it take into account new tables that are created unless people remember to also include the necessary trigger. Looking at this logically, we must query the file system. It's the only way to automate a lot of the potential problems away.

And here's a possible way of doing it:

1. Create a TableTracker table in a MySQL database somewhere
2. Write a shell script that will query the file system and record last modification times to TableTracker
3. Automate it in a cron job

The TableTracker table does not need to be in the same database (or databases) that is being tracked, but it does need to exist. Here's a very rough table:

DROP TABLE IF EXISTS `TableTracker`;
CREATE TABLE IF NOT EXISTS `TableTracker` (
    `table_name`    varchar(120)    NOT NULL,
    `created_at`    timestamp       NOT NULL    DEFAULT CURRENT_TIMESTAMP,
    `updated_at`    timestamp       NOT NULL    DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`table_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Now we need a shell script. This was written and tested on Ubuntu Sever 20.04, though it should work on any modern Linux platform:

#!/bin/bash
srcpath="/etc/mysql/my_dbname/*.ibd"
username="watcher"
password="superSecretPassword!123"
dbname="my_dbname"

sql=""
sql+="INSERT INTO TableTracker (table_name, updated_at) "
sql+="SELECT tmp.table_name, tmp.updated_at as updated_at "
sql+="FROM ("

for name in $srcpath; do
    ts=$(date -r $name "+%Y-%m-%d %H:%M:%S")
    fname="${name##*/}"

    tbl="SELECT '${fname%.*}' as table_name, '$ts' as updated_at UNION ALL "
    sql=$sql$tbl
done

sql+="SELECT '' as table_name, '2000-01-01 00:00:00' as updated_at) tmp "
sql+="WHERE tmp.updated_at > '2000-01-01 12:00:00' "
sql+="ON DUPLICATE KEY UPDATE updated_at = tmp.updated_at;"

# Record the Updates to MySQL
mysql -u $username -p$password $dbname <<< $sql

echo "All Done!"

Remember to set the file as executable with chmod +x.

From there, you can throw this file into a cron job and have it run as often as you'd like.

Notes:

• this crude tool could be set up to watch multiple databases
• this tool could be set up to watch any database file, not just InnoDB files
• if this is running on the main database server, then no MySQL password will need to exist in the file, as root in Ubuntu can (usually) access root on MySQL directly
• there is no error checking, so be sure to clean up the shell script if this is used in a production environment

Answer 2

There's no good way to query the last modified time of an InnoDB table as metadata.

The problem is what counts as the time of modification? The time you executed INSERT/UPDATE/DELETE? Or the time the transaction including that modification was committed, which might be seconds later? Or the time when modified pages for data and and indexes finally got synced to the tablespace, which might be minutes later?

And what if you dump and restore the table? That could happen days after the last time any data was modified, so does that count as a modification?

Another method is to use a timestamp column in the table, and make it DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP. Though this only tracks insert and update, not delete or alter table.