It’s a common practice to use MySQL’s auto increment feature for things like product ids or invoice numbers. While it always works when the system is first setup it can cause major headaches over time. The problems usually fall into one of three categories, dual master, test/staging setups, and reconciliation of ids.

MySQL in a dual master configuration requires that each master not be able to assign the other masters id. Replication events that use auto increment are tagged with the id that was allocated for that transaction. If you run mysqlbinlog on a bin log it will show something like SET INSERT_ID=123; This ensures that auto increment events are replicated to slaves correctly.

In a dual master configuration each master will try to allocate the same id, replicate the event to the other master where that id is already taken. This causes replication to stop on a duplicate key error. In MySQL 5 there are the …

In 5.1 I modified the compression methodology in response to seeing some schema's which were only seeing compression in the 50% range (which is low compared to what I had seen in most common cases). The main problem I identified was that the compression of rows with NULLS and long varchars was sub-optimal.

In 5.0 and 4.1 the methodology for compression was pretty simple. Take a row, add it to the compression buffer, and then write that out when it was full. In 5.1 this has changed:

1) Rows are first packed. This leads to a varchar being copied into the compression buffer with only the contents and length ever being seen for compression. The extra space is never copied.

2) NULL removal. A set of bytes are stored representing NULLS are stored before any row is copied in. If the attribute (we call them fields internally) was NULL no length or data is stored. Effectively NULLs no longer count …

The MySQL database has become the world?s most popular open source database because of its fast performance, high reliability and ease of use. The DBAs expect the same virtues from a backup solution for MySQL. Often times we are asked what performance to expect from Zmanda Recovery Manager (ZRM) for MySQL. That is why recently we measured backup and recovery performance for MyISAM and InnoDB storage engines using various backup methods available with ZRM.

In our testing we used MySQL 5.0 running Netflix databases available as part of Netflix Prize project. Our 2-way Red Hat Enterprise 4 server with 4 GB of memory was able to copy large files with an average transfer rate of 37 Mb/s.

All details and results are described in a white paper available with a free registration to the Zmanda Network. Here are the important takeaways:

• Logical backups and …

MySQL AB today announced an expanded partnership with Carahsoft Technology Corp., a leading government information technology provider, to accelerate the adoption of the MySQL database among U.S. federal government agencies. Carahsoft is now MySQL AB's Master GSA partner and distributor to the federal public sector channel. MySQL government customers and resellers now have convenient, one-stop access to official MySQL information and training through Carahsoft.

Well, that’s right. Student applications close this Saturday, the 24th, so I’d be right in saying its mid-week, so get your applications coming in for the Google Summer of Code.

To be a bit of a tease, MySQL has actually upped the amount of projects that we think students can accomplish, by adding a brand new section on Replication and Backup Development. Everyone talks about backing up, and this is an excellent way for a student to get inspired by doing something seriously important!

Sheeri Kritzer, long time MySQL DBA and prolific Planet MySQL author, has also decided that she’s going to mentor …

Our friendly Human Resources department started a new initiative this week: We have a Global Sports Day for all our >360 employees in >29 countries. That means that us MySQLers are to get off the keyboard, move our bodies, and document our activities for the rest of the company to see. We are reimbursed for expenses, and the most creative activity / picture / story will get an additional reward.

Lenz and I happened to have planned a Community meeting for today already quite a while ago, so we decided to combine this with the Sports Day. Our local version in Hamburg took us 5,43 km through Niendorfer Gehege in 0:31:35. For myself, this means that I have run a total of 161 km so far this year, for about 15 hours, at an average …

For the first time today I heard someone make this comment:

"When we called Redhat about a question their support hemmed and hawed over an answer."

"Now when we called Oracle they turned around the answer very quickly, and gave us a great response."

Customer support can be very hit or miss, but it did confirm to me that someone is both using and is happy with Oracle's approach to its distribution.

What else did I find interesting about this? The user in question wasn't an Oracle database user, they had for some reason just picked Oracle over Redhat.

Redhat is way a head of the game installations. Even with white box vendors and Oracle making its version available for download, the install base of RHEL is larger. Oracle's Linux is market validation for Redhat's approach to creating a Linux distribution (which is pretty important when you consider the encroachment that Ubuntu …

<p>MyEnv is an environment to run comfortably multiple MySQL instance on one server. You can run even multiple instances with the same binaries.</p> <p>The concept behind this is to separate binaries from databases itself.</p> <p>MyEnv bases on OFA which was adapted and improved by my former employer Trivadis. Their product is called Basenv. MyEnv is not so comfortable but it is free and open.</p>

MyEnv is an environment to run comfortably multiple MySQL instance on one server. You can run even multiple instances with the same binaries.

The concept behind this is to separate binaries from databases itself.

MyEnv bases on OFA which was adapted and improved by my former employer Trivadis. Their product is called TVD-BasEnv. MyEnv is not so comfortable yet but it is free and open.

Requirements

• Perl must be installed.
• A user where mysql runs on must exist.

How to install myenv

 root > su - mysql
 mysql> mkdir -p myenv data etc log product source tmp
 mysql> wget http://www.shinguz.ch/MySQL/myenv.tgz …

In one of my previous posts: MySQL backups, I talked about using a script for automating backups. I show that we can use gzip to compress backup file to compress and save. Since then, our backup file has been growing meg or two a day which is causing our backup files to get [...]