Restoring lost data from the Binary Update Log

Ian Gilfillan

One quiet Monday morning, I was easing into the week, when some of the staff started reporting 'strange errors' in their application (a content management system running on MySQL).

The machine had been having problems with what looked like overheating, so I quickly checked all the tables, and finding some corruption, repaired the tables. I saw that the machine had restarted shortly after the midnight backup, presumably due to overheating (after a backup, the SQL is bzipped, which is quite CPU and drive intensive, which could cause heat problems). Crisis over, I sat back and continued sifting through the hundreds of Monday morning emails.

Lunch came and went, and it looked like being a quiet Monday, when I had another report of 'strange behaviour'. It was definitely time for moving to that cooler cabinet, but in the meantime I had more corruption to repair. Feeling complacent, I started a REPAIR TABLE. It failed. In horror, I saw that the entire .MYD file (the data file) had disappeared. What was worse, having successfully done this many times recently, I hadn't backed up before doing the repair. All the data since last night's backup was gone! After some frantic scrambling around on the filesystem, there was no other conclusion - the data was lost.

Before this horror story scares everyone off MySQL, the real culprit seemed to be memory and motherboard-related, a topic not in my area of expertise. But the problem was now squarely in my domain - all data since last night had been lost.

The Binary Update Log

Fortunately, there was a solution. Some of you may remember a small section in my article entitled More MySQL Logs on the binary update log. The binary update log is a log that records each SQL statement that changes the data stored in the database, which includes all UPDATE and INSERT statements.

The binary update log is activated by placing
log-bin[=binary_update_log_filename]
in the configuration file (my.cnf or my.ini). The filename is optional. If it is not supplied, the binary log will be named after the host (hostname-bin). The first binary log, created when the server is set up, will be given the extension 001. Every time the server is restarted, is flushed (with FLUSH LOGS, mysqladmin flush-logs or mysqladmin refresh), or a single update log becomes too big (determined by the value of max_bin_log_size, which you can set in your config file, the file rolls over, and a new file is created, with an extension incremented by one. So the second binary log file will be called something like hostname-bin.002.

The binary update log can be used to restore lost data (since it records all changes to the data). It is also used for replication, which will not be covered in this article.

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:

--help, -?	Displays help and exits.
--database=dbname, -d dbname	Statements for a particular database only.
--force-read, -f	Continues even if MySQL encounters unknown log statements.
--host=hostname, -h hostname	Specify a particular host machine.
--local-load=path, -l path	Prepares local temp files in the specified directory (for use with LOAD DATA INFILE).
--offset=N, -o N	Skips the first N entries.
--password[=password], -p[password]	Server password
--port=portnum, -P portnum	TCP/IP port to use when connecting to a remote server.
--position=N, -j N	Starts reading at position N.
--protocol={TCP | SOCKET | PIPE | MEMORY}	Connection protocol to use (MySQL 4.1 only).
--read-from-remote-server, -R	Reads the binary log from a remote server. Will ignore --host, --password, --port, --protocol, --socket, and --user unless this option is supplied.
--result-file=name, -r name	Directs output to the specified file.
--short-form, -s	Displays the statements only, no extra information.
--socket=path, -S path	Socket file to use for the connection.
--user=user_name, -u user_name	Username when connecting to a remote server.
--version, -V	Displays version info and exits.

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!

Hello, nice site look this:
http://fastppc.info http://fastppc.info http://fastppc.info http://fastppc.info http://fastppc.info http://fastppc.info http://fastppc.info
End ^) See you