Using Transactions In MySQL (Part 2) - Timberrrrrrrrrr!

(Page 8 of 10 )

Let's move on to durability, another of the ACID properties. As noted previously, durability implies that once a transaction has been completed, the changes it has made must continue to exist even in the event of a system failure. MySQL does this through the use of a "binary log," a log file that tracks the changes made to its tables and can be used to revert the system to an earlier state.

In order to activate MySQL binary logging, the MySQL daemon must be started with the special "--log-bin" parameter.

$ /usr/bin/safe_mysqld --log-bin &
Starting mysqld daemon with databases from /var/lib/mysql

Once binary logging is turned on, MySQL will create a log file (usually named with the machine's host name) in its data directory. Every change made to the various tables will be logged in this file, together with a timestamp. Here's an example of what the file might look like:

$ cat /var/lib/mysql/localhost-bin.001 þbinBl`?E3.23.54-logBl`?vl`?@testcreate table a (a int(10) not null)­®a?/create database masterË®a?xmastercreate table users (uid int(4), uname varchar (200) not null, pass varchar(200) not null)Ù®a?Smasterinsert into users values (1, 'joe'

Doesn't make much sense, huh? Keep reading...

In the event of a system crash, the binary log can be used to revert the system to the state it was in prior to the crash. This is accomplished by means of the "mysqlbinlog" utility that ships with MySQL, which is used to read the binary log and display its contents in a more-readable format. Take a look:

$ mysqlbinlog /var/lib/mysql/localhost-bin.001
# at 4#030911 18:06:18 server id 1 Start: binlog v 1, server v 3.23.54-logcreated 030911 18:06:18# at 73#030911 18:07:10 server id 1 Query thread_id=2 exec_time=0error_code=0use test;SET TIMESTAMP=1063283830;create table a (a int(10) not null);# at 137#030912 17:01:57 server id 1 Query thread_id=3 exec_time=0error_code=0SET TIMESTAMP=1063366317;create database master;# at 184#030912 17:02:27 server id 1 Query thread_id=3 exec_time=0error_code=0use master;SET TIMESTAMP=1063366347;create table users (uid int(4), uname varchar (200) not null, passvarchar(200) not null);# at 304#030912 17:02:41 server id 1 Query thread_id=3 exec_time=0error_code=0SET TIMESTAMP=1063366361;insert into users values (1, 'joe', password('joe'));# at 387#030912 17:03:35 server id 1 Stop

This output, which is in the form of regular SQL commands, can be piped to the "mysql" command-line client to reproduce the exact state the system was in before it went down, as below:

$ mysqlbinlog /var/lib/mysql/localhost-bin.001 | mysql

Next: Perl of Wisdom >>
 

More MySQL Articles
More By icarus, (c) Melonfire

Comments On: Using Transactions In MySQL (Part 2)

>>> Be the FIRST to comment on this article!