Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Mar 17, 2004

Restoring lost data from the Binary Update Log - Page 2

By Ian Gilfillan

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:


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 dbnameStatements for a particular database only.
--force-read, -fContinues even if MySQL encounters unknown log statements.
--host=hostname, -h hostnameSpecify a particular host machine.
--local-load=path, -l pathPrepares local temp files in the specified directory (for use with LOAD DATA INFILE).
--offset=N, -o NSkips the first N entries.
--password[=password], -p[password]Server password
--port=portnum, -P portnumTCP/IP port to use when connecting to a remote server.
--position=N, -j NStarts reading at position N.
--protocol={TCP | SOCKET | PIPE | MEMORY}Connection protocol to use (MySQL 4.1 only).
--read-from-remote-server, -RReads 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 nameDirects output to the specified file.
--short-form, -sDisplays the statements only, no extra information.
--socket=path, -S pathSocket file to use for the connection.
--user=user_name, -u user_nameUsername when connecting to a remote server.
--version, -VDisplays 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!

» See All Articles by Columnist Ian Gilfillan

MySQL Archives

Latest Forum Threads
MySQL Forum
Topic By Replies Updated
MySQL in high availability and transction secure banking application klamor 1 August 28th, 10:24 AM
MySQL rollback UAL225 1 August 28th, 10:15 AM
Browsing a DB file that uses MySql finleytech 1 July 26th, 10:51 AM
php cookie won't pass variable to next page ITdevGirl 0 June 22nd, 12:13 PM