Practical way of finding last update/insert/delete of any table in a database?

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?

The Solutions:

Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.

Method 1

There is no database-wide (or system-wide) “last modified timestamp” for InnoDB in either MySQL or MariaDB.

At the other end of the spectrum, there are Temporal tables in MariaDB. This allows you to keep a history of all changes.

Method 2

Replication would allow you to “continually” have a “Replica” with the entire set of databases up to date.

You can, if you desire, do whatever is desired to back up the Replica.

This technique would not involve stopping the Primary server at all.

Furthermore, the binlog (on the Primary) and the relay-log (on the Replica actually give you an indication of whether any changes have been made — by looking at the “position” of the replication stream in either of those files.

What is the purpose of the backup? If it is an ordinary backup; please use an existing technique, such as I outline here, not a new technique that is being invented on the fly in this forum.

LVM as a technique for taking a “snapshot” on a single server. It is so fast that some of your design thoughts may be unnecessary. (It uses “copy-on-write” and has some low level help from the OS.)

Method 3

Yet another approach… (Not centralized, but somewhat “automated” and “simple”):

Add these to every table:

`last_modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
INDEX(last_modified)

Then have a script that checks this on each table:

SELECT MAX(last_modified) FROM ...

When it finds a “new” timestamp, dump the table.

Caution: If two tables are updated “at the same time”, you might dump one table without dumping the other. So, you really need to do some kind of locking or shutdown that assures that sets of tables that were updated together are dumped together.

Method 4

I think I may have found an answer for my purposes. As explained, the use case is a recurrent script to check whether a dump (e.g. using mysqldump) of one or more database(s) is needed, for backup purposes.

I was considering the following:

• make a new table called “dbase_last_modified” with one column, “last_modified” of type DATETIME, and one row of data.
• putting 3 triggers on each table in each database: AFTER INSERT, AFTER DELETE and AFTER UPDATE. Each trigger then updates dbase_last_modified.last_modified with CURRENT_TIMESTAMP

But there is a simpler solution, I think:

• make a new table called “dbase_last_modified” with one column, “last_modified” of type DATETIME, and one row of data.
• each run of the recurrent script, i.e. every 10 minutes, check the values of information_schema.tables.update_time for all tables in each user database. As mentioned, the problem with this is that these are set to NULL when the server starts. But each time a non-NULL value is found, update dbase_last_modified.last_modified to the most recent such value found (for all the tables in that database).

This then obviously makes this a persistent value in the database. The attraction of using information_schema.tables.update_time is that this gets updated automatically by the system by INSERT, DELETE and UDPATE: no need for many manually created triggers.

PS I wondered about putting a trigger on information_schema.tables.update_time, but this is a complete non-starter: the only access to information_schema is read-only: you can’t put a trigger on it, you can’t change its privileges in any way, and in fact people say that this is a VIEW, not a table.

Opinions from experts?

I can in fact think of a problem with this: what if some changes are made in a table and the server then goes down before the next run of the recurrent script? When the server is restarted there will be no record of any change having occurred.

A simple workaround solution to that is to do a mysqldump for every user database, each time the MariaDB service is started. Not ideal but acceptable.

One final niggle
Haha. Timezones!
MariaDB sets information_schema.tables.update_time using the local time. Where does it get the local time from? By default from the OS, which itself may or may not be setting the timezone automatically. The MariaDB policy is found here, which also explains how to obtain the setting and the zone. In order to make things as robust as feasible, the dump files’ datetime value (incorporated into the name) should be recorded in UTC, with an indicator in the name that this is a UTC time, e.g.

“my_database_dump_2022-04-16_1928_UTC.sql”

This also means that the code of the utility has to convert information_schema.tables.update_time to UTC (on the basis of what MariaDB tells you about its current timezone) in order to make comparisons. The single column in dbase_last_modified should be called last_modified_UTC (NB MySQL/MariaDB DATETIME fields are always “timezone-naive”, to use the Python way of putting it).

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0