How to undelete MySQL database without third party applications

When handling MySQL database server administrations, it’s possible that you may delete the records or drop a table/ database accidentally.

This tutorial will help you learn how to use mysqlbinlog and mysqldump utility tools to undo any data, table, or database deletion.

Undelete MySQL database with mysqlbinlog tool

MySQL database server commonly generates binary log files that record the events modifying your database content.

First, you need to check if the binary log is active on your server by checking the log_bin global variable value as shown below:

mysql> SHOW VARIABLES LIKE 'log_bin';

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+

The log_bin should be ON by default, unless someone has modified the value.

Next, you need to find out how many binlog files have been generated by MySQL using the SHOW BINARY LOGS query.

In my case, I have two log files as shown below:

mysql> SHOW BINARY LOGS;

+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000001 |      1051 | No        |
| binlog.000002 |     63810 | No        |
+---------------+-----------+-----------+

After that, you need to create a .sql file out of the binlog files.

The binlog files are stored inside the datadir/ folder of your MySQL installation, and you can find the folder by using the SELECT @@datadir query.

Because I installed MySQL using Homebrew, my datadir/ is located below:

mysql> SELECT @@datadir;

+--------------------------+
| @@datadir                |
+--------------------------+
| /opt/homebrew/var/mysql/ |
+--------------------------+

From your command-line program, navigate to the datadir/ folder and extract the binlog files as .sql files using the > symbol.

Here’s the example:

cd /opt/homebrew/var/mysql/
mysqlbinlog binlog.000001 > log1.sql
mysqlbinlog binlog.000002 > log2.sql

Additionally, the mysqlbin log command has several parameters that you can add to filter the log entries. Some of the most useful options are:

• --database to filter entries for just this database
• --start-datetime to read binary log from the first event with a timestamp equal to or later than this option
• --stop-datetime to stop reading the log at this timestamp

Once you have the .sql files created from the binlog files, it’s time to restore the data using the source command.

Go back to your mysql command-line client and use the source command as follows:

mysql> source log2.sql;

And that’s how you can restore accidentally deleted data using the mysqlbinlog utility program.

Undelete MySQL database with mysqldump tool

MySQL database server installation comes with the mysqldump utility program that you can use to “dump” one or more MySQL databases as a .sql file.

The dump result can then be imported into the MySQL server to restore the database and all its data, so I’d recommend you to periodically backup your database using the mysqldump tool.

For example, suppose you have a database named school_db that you want to backup.

First, you need to create an SQL dump file of the existing database using the mysqldump command as follows:

mysqldump --user=[username] --password=[password] school_db > dump.sql

Replace [username] and [password] above with your actual MySQL username and password.

Once you have the dump.sql file, you can restore the database by executing the script as follows:

mysql --user=[username] --password=[password] school_db < dump.sql

The command above would apply the statements in dump.sql file to the school_db database.

Since mysqldump tool’s main function is to help you create a dump .sql file from your database server, it won’t be effective when you delete your data before creating the dump file.

Using both mysqlbinlog and mysqldump tools, you can restore accidentally deleted data and create a dump file at certain periods as a disaster recovery plan.