Just got Bug#16993 closed, which provides a good lesson into the complications of replication. The original title was RBR: ALTER TABLE ZEROFILL AUTO_INCREMENT is not replicated correctly, but the problem is not related to row-based replication (RBR) nor to ZEROFILL. The culprit is adding an AUTO_INCREMENT column to a table, and it is not actually a bug (to be frank, it depends on your point of view, but "fixing" this bug causes more headaces than it solves as you'll see in a moment).

What the example code in the bug description does is creating a table on the slave and the master, but inserting (identical) rows into the tables in different order on the slave and the master; for example in this way:

master> CREATE TABLE ages(name CHAR(30), age INT);
master> SET SQL_LOG_BIN=FALSE;
master> INSERT INTO ages SET …

OnLamp has just published an article I wrote about some interesting things you can do with MySQL 5.x and replication.

• You can create an array of peer-to-peer nodes that are at the same time master and slave, without much worry about insertion conflicts, thanks to some server variables introduced in MySQL 5.0;
• You can achieve automatic failover using a combination of federated tables (MySQL 5.0) and events (5.1)

The article is a proof of concept that using such techniques you can achieve your goals without help from external applications.

If you want to get your hands dirty, using the companion package …

As promised when I talked about the MySQL Sandbox and as mentioned in this (very) recent article on replication, I have released a testing environment for replication, called the MySQL 5 Replication Playground.
It's a package that creates in one single server an array of four replicating nodes using MySQL 5.0 or 5.1. The purpose of this package is to help developers and administrators when they need to test applications involving replication. You don't need to use several boxes. One machine is enough. With just one instance of MySQL installed, the Replication Playground will install four data directories and it will create the necessary scripts to start, stop, …

Just in time for the user conference, Giuseppe publishes a great article on how to handle auto increment columns in bi-directional replication. The article is a nice tutorial on how to use the new auto increment varibles that has been introduced in MySQL 5.0.

Check it at OnLamp:
Advanced MySQL Replication Techniques
Thanks Giuseppe for this excellent article!

[1][2][3]

RDBMSs (or for clarity, Databases) are a basic element of any
computer-based appliance in the same way as are an Operating System,
Web Server and software development platform.  As is common for
developers of Open Source software, I am quite familiar with Linux as
the Operating System, Apache as the Web Server, MaxDB and MySQL as
Databases and Perl (and to a lesser degree, PHP) as the software
development platform.  This combination is often called the "LAMP"
stack [4].  Take the following as coming from that background.

Databases are the storage mechanism of the content presented to the
user of any computer-based appliance.  This content may be displayed
via a desktop application or through a web browser.  The main display
mechanisms I think of when writing applications are 1) Through the end
users's computer in real-time via a desktop application, in an
interactive manner.  2) Through a web server, not in real-time, via a
user's web browser (I prefer to think of …

Tomorrow morning around 4, I will be leaving for San Francisco to attend MySQL UC like many of my friends. It will be a very long flight but I am very excited to meet people whom I know by name but not by face.

I am really short of words to thank MySQL for giving me the opportunity to come to SF.

If you are arriving in SF a bit early, please drop me a line and lets plan to get together for dinner or drinks.

Frank

There’s been a lot of buzz lately about new storage engines (Solid’s SolidDB and Jim Starkey’s Falcon) being developed for MySQL. Quite a few people have asked me what I think about them, and if it’s really a seamless process to switch storage engines. Everybody still has Oracle’s acquisition of Innobase Oy fresh on their minds, so nobody is really terribly surprised by the recent announcements. As for my opinion on the matter, well, it’ll take some discussion. I was quoted in ComputerWorld’s article …

Sun Microsystems, Inc. today announced new benchmark results involving the performance of the open source MySQL database running online transaction processing (OLTP) workload on 8-way Sun Fire(TM) V40z servers. The testing, which measured the performance of both read/write and read-only operations, showed that MySQL 5.0.18 running on the Solaris(TM) 10 Operating System (OS) executed the same functions up to 64 percent faster in read/write mode and up to 91 percent faster in read-only mode than when it ran on the Red Hat Enterprise Linux 4 Advanced Server Edition OS.

This week, I?m teaching ?MySQL 5.0 for Database Adminstrators? near Philadelphia. Of course, we covered Backup and Recovery. With one exercise that I made up, we had to spend some more time as expected. The main reason for it was that I knew about a specific mysqldump feature, but I didn?t know the little details.

The exercise asked the students to have a per table backup of the world database and later restore a single table only.

Usually, mysqldump would create a single text file containing CREATE TABLE and INSERT statements. With the --tab option, it would create two files for every table being affected.

My first guess was to create a new directory and have all the files being created there.

h-67-102-186-164:~ root# mkdir /tmp/worldbackup h-67-102-186-164:~ …