[Case Study] Computer Forensics: A Method to Recover Data from MySQL Database by Utilizing Binlog

Editor’s note: Binlog is short for binary log, the binary log contains “events” that describe database changes such as table creation operations or changes to table data. It also contains events for statements that potentially could have made changes (for example, a DELETE which matched no rows), unless row-based logging is used. The binary log also contains information about how long each statement took that updated data.

Two types of binlog:

1. Binary log index files (extension: .index): It records the entries of all binary log files.
2. Binary log files (extension: .000000*): the log files record all DDL, DML events.

Binlog is a crucial part of MySQL database. When important data is lost in the database, it is possible to recover the data by utilizing the binlog records. Thus, binlog can be considered an important tool in forensic cases.

How to check if binlog is enabled?

Method 1.

Check MySQL configuration file. (In Windows OS, the configuration file is named as: my.ini, which is normally located at the root folder of MySQL. In Linux OS, the configuration file is named as: my.cnf, which is normally located at /usr/local/mysql/etc) Find the row “log-bin=mysql-bin” and see if it is marked by “#” or not. If this row is not marked by “#”, the binlog feature is enabled, otherwise, binlog is disabled.

Method 2.

Use MySQL command “show variables like ‘log_bin’”. The command window will return a value that tells you the status of binlog.

Method 3.

Check the storage folder of the target database. If there are binlog files with the filename like “mysql-bin.000001”, then binlog feature is enabled.

Common binlog operation commands.

1. “show master logs”

This command will show you a list of all binlog entries and their size.

“

2.  “show master status”

This command will show you the master status, which gives you information about the latest log entry and its position.

3. “flush logs”
This command will refresh binlog, and starts to re-number the log entries. Actually every time the mysql service restarts, this command will be executed.

4. “reset master”
This command will reset and clear all binlog entries.

How to read a binlog?
Method 1.
Binlog file is recorded in binary format so it cannot be opened by conventional file viewer software. But we can use MySQL’s built-in commands to view binlog files. Input below command line under MySQL’s bin folder:
C:\xampp\mysql\bin>mysqlbinlog C:\xampp\mysql\data\mysql-bin.000009

Method 2.
By using method 1, the full binlog content will be displayed and is not easy for us to read. Therefore, sometimes we also use the binlog query command:
mysql> show binlog events [IN ‘log_name’] [FROM pos] [LIMIT [offset,] row_count]

IN ‘log_name’: specify the binlog file name to run the query.
FROM pos: specify the starting position of the query.
LIMIT [offset,]: offset will be 0 if not specified.
row_count: number of rows for the query.

How to recover MySQL data with binlog?
Now let’s see a real practice on how to recover MySQL data with binlog. For example, there’s a database named “test”, there is no table to be found so we would presume that some data is deleted. What’s more, a backup of this database is also found, last modified on 2018-11-21 15:27:12.

So how binlog is going to help on this case? First, we can use binlog to check for operation logs and verify if the data is truly deleted. And then, we can use binlog to help us recover deleted data from the database.

Step 1.
Of course, the first step is to check if binlog is enabled or not. Remember our instructions? Let’s input the command line to check “show variables like ‘log_bin’”.

Step 2.
Input “show master logs”, in this example, there are 8 binlog files in total.

Step 3.
Input command “show binlog events in ‘mysql-bin.00000*’” to check log events. In this example, we found an entry “use‘test‘；delete from t1，use `test`；DROP TABLE `t1`” from binlog file 000008. This event log tells us that a table named “t1” has been deleted.

Step 4.
Because table “t1’ has been deleted, without the table structure we are unable to recover deleted data only through binlog. But fortunately, we still have the database backup. So we can restore the backup and acquire the table structure of “t1”. (We’ll not introduce the restoration details here, if you are interested, contact us at [email protected])

Step 5.
Input command “show binlog events in ‘mysql-bin.000008’” and find the first and last position of this binlog, which is “4” and “1223”.

Step 6.
Extract this part of the binlog. Input command under bin folder: “mysqlbinlog C:\xampp\data\mysql-bin.000008 –start-position=4 –stop-position=1223 -r 1.sql”. This command line extracts all specified events and saves to the file named “1.sql”.

Step 7.
Analyze the events, and we’ll be able to know every command executed after the backup has been created. Let’s save it as “2.sql”.

Step 8.
The last two rows are deletion commands, let’s remove them and execute the remaining MySQL commands on the backup database. With these commands and operations, now we have successfully recovered deleted database data from the backup.

Conclusion

Thanks for your time reading our blog post. If you are interested in our forensic solutions, come and check out our website for more information. You can also go to our resource page to download our forensic products for free. We welcome you to contact us and claim your free product trial!