How to 'rollback' my content to a specific date, reparing an update or delete or insert

Question

I am maintaining a MySQL database (roughly 100 tables, lot of relations etc) here (version: 5.7). I am wondering if I can add a table etc (audit-table(s) and/or history-table(s) or add columns to the original tables) so that it will be easier for me to maintain that database in the future (for instance if the user 'joe' inserted/updated and deleted things that he should not have messed with, then I could 'rollback' only his dealings with the database). Is there a good pattern for this? Or is this functionality already built in the Enterprise-version of for-instance MySQL 5.7 and MySQL 8.x?

I have found articles like this one (1) https://kahimyang.com/kauswagan/code-blogs/552/create-history-record-for-every-change-in-mysql-table-audit-trail-mysql-trigger and this one (2) https://blog.programster.org/mysql-history-tables when searching. Could you propose or point me to a tutorial or a whitepaper from MySQL on how to do this? Or can you recommend a good book on the subject?

Answer 1

There are a few different approaches to solving this difficult problem. It's often called point-in-time restore rather than rollback, because ROLLBACK means something else in SQL.

One: Add start_date and end_date columns to your tables along with the other identifying items (user_id I guess). Then, instead of simply doing an UPDATE operation, first UPDATE the end_date of the existing row to NOW() and INSERT the new value with a start_date of NOW() and a NULL end_date.

When looking up items do this to get their current value.

 SELECT col, col, col 
   FROM tbl
  WHERE start_date <= NOW()
    AND (end_date > NOW() OR end_date IS NULL)
    AND whateverElseYouNeed

When looking up items for a particular date do this:

 SELECT col, col, col 
   FROM tbl
  WHERE start_date <= @date
    AND (end_date > @date OR end_date IS NULL)
    AND whateverElseYouNeed

Then you can write code to look up old values and treat them as new values. Indexing these tables so lookups are still efficient when they get really large is a pain in the xxs neck.

Two: Create a history table to match each table, but also including a change_date column. Write BEFORE triggers for INSERT, UPDATE, and DELETE to insert a row in the history table for each change to the main table. Then write code to look back in the history table when you need to restore something.

This may be best if you already have lots of data. And, it can implement the required history logging you need without code changes to your existing application. You'll still have to create the code to actually perform the point-in-time restore you need. The good news with this approach is you'll only actually search your history table when you need to restore something, so you don't have to optimize searches.

Three: MariaDB, since v 10.3.4, has offered system-versioned tables to handle the history within the database server. (I haven't tried that feature myself.)

Prof. Richard T. Snodgrass wrote Developing Time-Oriented Database Applications in SQL and generously posted a pdf of the book.