OS: W10.
This question is specifically about what's possible in MariaDB, specially version "Ver 10.16 Distrib 10.2.11-MariaDB".
Edit
In light of the answer by Rick James I have now upgraded to "Ver 15.1 Distrib 10.7.3-MariaDB".
I want to implement a way of having a "last modified" timestamp for a database, i.e. which shows when the last INSERT or DELETE or UPDATE occurred, regardless of the table where it happened.
Use case (due to comment below): I have a recurrent sys admin task running. Every 10 minutes it checks whether databases need dumping using mysqldump
, for backup purposes, to a directory which gets routinely snapshotted/backed up (using restic). At the moment this recurrent script checks whether any of the following MariaDB files have been updated: ibdata1, ib_logfile0, ib_logfile1. If the most recent last-modified date of these 3 is more recent than datetime (incorporated into the name) of the last .sql dumpfile it does a new dump. Otherwise not. I keep the last 5 such .sql dumpfiles. I don't want a dump to happen unless some data has changed. The problem with this arrangement is that it doesn't show which database(s) need dumping, so I dump all the non-system databases. I'm looking for a more fine-grained arrangement.
I found this, which looked promising... but then some comments there about InnoDB and information_schema.tables.update_time
led me to do an experiment.
Contrary to what is asserted in some places about update_time
always being NULL
in InnoDB tables, this does not seem to be the case with what I've got. Unfortunately, the comment there about this value being set to NULL
whenever the server starts does appear to be true.
Elsewhere I read that newer versions of MariaDB, pulling away from vanilla MySQL, do in fact implement some sort of history of changes.
I'm currently working on the assumption that I shall have to implement AFTER UPDATE
, AFTER INSERT
and AFTER DELETE
triggers for every table, and then get values for each of these and find the most recent, in order to determine the "last modified" time for the whole database.
Is there any other possibility with this version of MariaDB?