Home arrow MySQL arrow Page 8 - Using Transactions In MySQL (Part 2)

Timberrrrrrrrrr! - MySQL

mysqlThis 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.

  1. Using Transactions In MySQL (Part 2)
  2. Isolating Yourself
  3. The Three Rs
  4. Peeping Tom
  5. Locks and Keys
  6. Nothing Like the Real Thing
  7. Holding Pattern
  8. Timberrrrrrrrrr!
  9. Perl of Wisdom
  10. End Work
By: icarus, (c) Melonfire
Rating: starstarstarstarstar / 41
December 22, 2003

print this article


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

>>> More MySQL Articles          >>> More By icarus, (c) Melonfire

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


- Oracle Unveils MySQL 5.6
- MySQL Vulnerabilities Threaten Databases
- MySQL Cloud Options Expand with Google Cloud...
- MySQL 5.6 Prepped to Handle Demanding Web Use
- ScaleBase Service Virtualizes MySQL Databases
- Oracle Unveils MySQL Conversion Tools
- Akiban Opens Database Software for MySQL Use...
- Oracle Fixes MySQL Bug
- MySQL Databases Vulnerable to Password Hack
- MySQL: Overview of the ALTER TABLE Statement
- MySQL: How to Use the GRANT Statement
- MySQL: Creating, Listing, and Removing Datab...
- MySQL: Create, Show, and Describe Database T...
- MySQL Data and Table Types
- McAfee Releases Audit Plugin for MySQL Users

Developer Shed Affiliates


Dev Shed Tutorial Topics: