Question :
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.
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
I’d like to be able to make a backup, execute migration script and get undo script automatically.
Why?
If the migration fails so badly that it’s going to be abandoned, then restore your backup of the database.
That’s the only way to guarantee that you’ll get back to the point you started from.
I don’t know of any way to create an “undo” script but, even if you could, there’s no guarantee that it would work, potentially failing part way through its “recovery” and leaving you in an even worse position, somewhere between the last known Good State and the point where the migration failed but you won’t be exactly sure where. From then on, you’re clutching at straws to try and clean up the mess.
Far better ( / easier / more reliable) to toss the “corrupted” database in the bin and restore it from the backup.