MySQL – Automatic Undo for Version Control

MySQL

I'm not a mysql expert, but it seems like there is no obvious way to do that.
I'll explain what I want.
Let's say I'm doing a data migration which would update some rows in some table.

I'd like to be able to make a backup, execute migration script and get undo script automatically.

I don't see why this should be impossible, since rdbms stores all changes in a log already for transaction (mysql has --log-bin option for example) and they already know how to revert them (because they can rollback a transaction). So maybe it's possible to extract and use this data for the purpose of creating undo scripts for migrations?

EDIT.

Okay, so thanks to one of the answers I've found that mariadb actually partially support what I want already. This feature called flashbacks. It's actually doing what I've suggested – translating INSERTS into DELETE via binlog. So what I am missing is the upcoming support of flashbacks over DDL statements and making flashbacks only for one transaction.

Best Answer

The main reason RDBMS logs changes is crash recovery. Unless logical replication is set up, changes are usually stored in vendor specific format, not as series of sql statements. Rolling transaction back involves restoring data to previous state, not generating and issuing another sql statement. Generating such a statement for a random transaction appears to me as if not impossible ,but at least ambiguous and impractical. There is more than way to write "inverse" query, and this query will become invalid as soon as any new transaction commit.

For instance , 

BEGIN TRANSACTION;
DELETE FROM table1;
ROLLBACK;

does not generate a bunch of INSERT statements. Most RDBMS will just not apply any changes to data files, so ROLLBACK will be executed immediately.

If you don't want to write sql scripts that undo effect of migration scripts, and your database doesn't support flashback (like Oracle or to some extent MariaDB) or Snapshot backups (like SQL Server) , there is no alternative to backup/restore. You can reduce time of backup/restore to seconds even for huge databases by utilizing zfs filesystem or commercial products like NetApp

Related Question