HomeMySQL Page 8 - Using Transactions In MySQL (Part 2)
Timberrrrrrrrrr! - MySQL
This concluding segment looks at the MySQL transactional model in a multi-user scenario, illustrating some of the data corruption problems that are likely to arise and explaining how to control them using MySQL's various isolation levels. It also includes a sample Perl application demonstrating transaction usage at the application level, and shows you how to emulate transactions with non-transactional MyISAM tables.
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
/ 41 
