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:
- Create a
TableTracker
table in a MySQL database somewhere
- Write a shell script that will query the file system and record last modification times to
TableTracker
- 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