Sometimes you may need to undo the changes made to MySQL database. You can easily do it using MySQL Rollback query. Here’s how to rollback MySQL database using MySQL Rollback command.
How does MySQL rollback work
MySQL Rollback statement allows you to rollback or undo one more statements that have been recently executed. For example, if you have accidentally deleted or updated rows, you can use MySQL rollback those statements and restore original databases. However, MySQL rollback works only if the database changes have NOT been committed to the database.
Bonus Read : How to Use MySQL Rollup
Can we rollback after commit in MySQL
No. Unfortunately you cannot undo changes that have been committed to the database.
How to Rollback MySQL database
Please note, by default, MySQL is configured to auto commit every SQL query. So it is important to turn off auto commit in MySQL using the following statement, if you want to be able to use MySQL Rollback.
Log into MySQL database and run the following command to disable autocommit
SET autocommit = 0
Or
SET autocommit = OFF
You use the following statement to enable the autocommit mode explicitly:
SET autocommit = 1
Or
SET autocommit = ON
Bonus Read : How to Check MySQL Version
Let’s say you have the following table sales(product,order_date,sale). We will look at how to issue MySQL rollback after delete command.
mysql> select * from sales; +---------+------------+------+ | product | order_date | sale | +---------+------------+------+ | A | 2020-01-01 | 20 | | B | 2020-01-02 | 25 | | B | 2020-01-03 | 15 | | A | 2020-01-04 | 30 | | A | 2020-01-05 | 20 | +---------+------------+------+
Now let us turn off autocommit as mentioned above,
mysql> set autocommit=0;
Next, let us delete all rows in sales table.
mysql> delete from sales; Query OK, 5 rows affected (0.00 sec) mysql> select count(*) from sales; +----------+ | count(*) | +----------+ | 0 | +----------+
As you can see all the 5 rows in sales table have been deleted.
Now, let us issue MySQL Rollback after delete command
mysql> rollback; Query OK, 0 rows affected (0.07 sec) mysql> select count(*) from sales; +----------+ | count(*) | +----------+ | 5 | +----------+
As you can see, the 5 deleted rows have been restored since the changes were not committed. However, if you had issues COMMIT statement after deletion and before ROLLBACK command, then MySQL would not have restored your table.
Bonus Read : How to Disable Foreign Key Check in MySQL
MySQL Rollback Update command
Similarly, you can rollback update command. Let us update the sale column in above sales table.
mysql> set autocommit=0; mysql> select * from sales; +---------+------------+------+ | product | order_date | sale | +---------+------------+------+ | A | 2020-01-01 | 20 | | B | 2020-01-02 | 25 | | B | 2020-01-03 | 15 | | A | 2020-01-04 | 30 | | A | 2020-01-05 | 20 | +---------+------------+------+ 5 rows in set (0.00 sec) mysql> update sales set sale=sale*2; Query OK, 5 rows affected (0.05 sec) mysql> select * from sales; +---------+------------+------+ | product | order_date | sale | +---------+------------+------+ | A | 2020-01-01 | 40 | | B | 2020-01-02 | 50 | | B | 2020-01-03 | 30 | | A | 2020-01-04 | 60 | | A | 2020-01-05 | 40 | +---------+------------+------+
As you can see we have doubled the sale column’s value. Now let us rollback update command.
mysql> rollback; mysql> select * from sales; +---------+------------+------+ | product | order_date | sale | +---------+------------+------+ | A | 2020-01-01 | 20 | | B | 2020-01-02 | 25 | | B | 2020-01-03 | 15 | | A | 2020-01-04 | 30 | | A | 2020-01-05 | 20 | +---------+------------+------+
As you can see the sale column old values have been restored.
You can also include MySQL Rollback in stored procedures and MySQL transactions for MySQL Rollback on error. The key is to disable autocommit to execute MySQL rollback.
Ubiq makes it easy to visualize data in minutes, and monitor in real-time dashboards. Try it Today!
