Rollback Data Changes
Prior Backup - MySQL/SQL Server/Oracle
Before a data change (UPDATE
,DELETE
) is made, Bytebase can create a backup of the affected rows. This allows you to revert that data change if needed.
How to use
-
Create a database called
bbdataarchive
on the instance where your database is located. -
Before creating the issue, turn on the Prior Backup switch.
-
Create the issue and click Rollout. Scroll down, you'll find the comment with the backup information.
-
Go to
bbdataarchive
, you'll find the table. You may query it via SQL Editor.
SQL Rollback - MySQL
After a data change completes, Bytebase can parse MySQL binary logs and build rollback SQL statements from the logs. This allows you to revert that data change if needed.
Requirements and limitations
- MySQL version is 5.7 or greater.
- MySQL has row-based logging enabled.
- The affected tables should have
PRIMARY KEY
orUNIQUE
constraints. - Bytebase cannot generate rollback statements that are bigger than
8 MB
currently.
How to use
-
Check if row-based logging is enabled
Connect to your MySQL instance and execute the following commands.
A convenient way is to use the SQL Editor Admin Mode.
The value should be
ON
.The value should be
ROW
. -
Check binlog retention time
Bytebase needs your MySQL binlog to generate rollback SQL statements. Your binlog retention time should be at least
1 hour (3600 seconds)
.The following commands returns the binary log expiration period in seconds.
-
Enable rollback SQL generation
Click SQL Rollback switch to request Bytebase to generate rollback SQL after data changes are completed.
-
Rollback
Click Preview rollback issue.
FAQ
-
Why I get
The rollback statement is empty
?Some common reasons might lead to empty rollback statement.
- The task didn't change any row.
- The binlog has been purged. You may need to extend your binlog retention time.