Restoring lost data from the Binary Update Log - Page 2

The mysqlbinlog application

In the situation described earlier, where I had lost all data in certain tables, the first thing of course was to move to the stable hardware environment, and then to restore from the earlier backup. The binary log files were copied across as well, and then I needed to run through them one by one, re-doing all the updates made since the night's backup. To restore from the binary update log, use the mysqlbinlog application.

If your MySQL installation uses the binary update log, go to the data directory and take a look at the listing of all the files (you can not view them usefully with a normal text editor because, as the name suggests, they're in binary format). The data directory differs for each installation - Windows installations usually have it in C:/MySQL/data. If you don't know where it is, you can find it with:

mysql> SHOW VARIABLES LIKE 'data%';
+---------------+-----------------------------+
| Variable_name | Value                       |
+---------------+-----------------------------+
| datadir       | /usr/local/build/mysql/var/ |
+---------------+-----------------------------+

Notice that there is also a file called something like hostname-bin.index. If you look inside it, you will see it contains a text format list of all the binary update logs, for example:

./hostname-bin.001
./hostname-bin.002
./hostname-bin.003

The mysqlbinlog application allows you to view the contents of the binary logs, and to restore if needs be. Without any arguments, it simply displays the contents on the screen. Here is a sample:

% mysqlbinlog hostname-bin.001

# at 4
#040129 11:56:13 server id  1   Start: binlog v 3, server v 4.0.18-log created 040129 11:56:13
# at 73
#040129 11:56:22 server id  1   Intvar
SET INSERT_ID = 9502817;
# at 95
#040129 11:56:22 server id  1   Query   thread_id=392   exec_time=0     error_code=0
use subscription;
SET TIMESTAMP=1075370182;
INSERT INTO outgoing...;
# at 312
#040129 11:56:02 server id  1   Query   thread_id=260   exec_time=22    error_code=0
use news;
SET TIMESTAMP=1075370162;
UPDATE article SET... ;
# at 428
#040129 11:56:03 server id  1   Query   thread_id=179   exec_time=21    error_code=0
SET TIMESTAMP=1075370163;
UPDATE article SET...

It begins with the server version (4.0.18 in this case, and the date and time. After that, each statement is recorded in the order that it ran (I have shortened the SQL statements for ease of reference).

To restore, simply pipe the results to a database, for example:

mysql> mysqlbinlog hostname-bin.001 | mysql news_database

When dealing with multiple binary update logs (most well-used servers will contain more than one), it is easy to decide which to use by looking at the date and time each starts. You can restore the database backup, and then start with the first binary log after the backup, going through one by one until the data was lost.

Let's simulate a database crash and restore from the binary log. You should use an empty database for this exercise (perhaps the test database, or create a new one). For this exercise I will use test - specify your database accordingly.

mysql> CREATE TABLE names(id INT, surname VARCHAR(50));

Now backup this database - this will become our 'nightly' backup.

% mysqldump -B test > /db_backups/test_backup.sql

The -B option ensures you only backup the test database (there is a full list of mysqlbinlog options at the end of this article). The 'day' commences, and the database gets updated - we will just use a single INSERT statement to illustrate the concept.

mysql> INSERT INTO names VALUES(1,'Alfonso'),(2,'Etienne');

Simulate a crash by deleting the data from the data directory. The names table is of the default MyISAM table type, which stores data in directories, so you can just delete the files from the names directory. Find the data directory on your setup. Make sure you are using an empty database for this!

% rm /usr/local/build/mysql/var/test/*

The Windows equivalent would of course be the del command, or you can just use your graphical interface to delete it. Now the database has 'crashed'. Restore the backup, and check the data as follows:

% mysql test < /db_backups/test_backup.sql

% mysql test

mysql> SELECT * FROM names;
Empty set (0.00 sec)

You still have no data - just the database structure which we backed up. Let's look in the binary log to see what we can find (use the most recent binary log, if you have many in your setup). The --database option allows us to specify a specific database. 108 happens to be the most recent binary log in my environment.

% mysqlbinlog --database=test hostname-bin.108

# at 4268396
#040315  7:53:54 server id 1  log_pos 4268396   Query   thread_id=4755937       exec_time=0     error_code=0
SET TIMESTAMP=1079330034;
INSERT INTO names VALUES(1,'Alfonso'),(2,'Etienne');

Now we restore this to the test (or your equivalent) database, and confirm that the data has been correctly restored.

% mysqlbinlog --database=test hostname-bin.108 | mysql test

% mysql test

mysql> SELECT * FROM names;
+------+---------+
| id   | surname |
+------+---------+
|    1 | Alfonso |
|    2 | Etienne |
+------+---------+

Everything has been restored correctly! There are other mysqlbinlog options available as well. Here's the full list:

I hope that you will never need to use the binary update log for restoring (it is also used for replication), but if you do (which is probably why you've read this far), hopefully you will find the process as painless as I did. Good luck!

» See All Articles by Columnist Ian Gilfillan