It's good to get out of the office and meet people. This year I'll be doing several presentations at the 2009 MySQL Conference and adjacent Percona Performance Conference in Santa Clara. These include among others a talk on Tungsten Replicator on Thursday April 23 at 10:50.

In case you don't read this blog regularly, Tungsten Replicator provides advanced open source replication for MySQL. The term "advanced" is not an exaggeration. I'll be covering how to solve practical problems including the following:

• How to install and configure Tungsten Replicator in 5 …

This time I'll write about a nice featured we now have on the MySQL Proxy.

Parsing master.info
The master.info is a file that the MySQL server creates when you are running a slave server, you can read the manual for more details.
And now you can use the MySQL Proxy to read that file and parse the data from it.

Background
It all started when Jan emailed the proxy discuss mailing with the Changelog from 0.6.1 to 0.7.0. The part that got my attention was:
"...
Core
[skip]
* added parser for master.info files"
... "
As I was working on a Lua script that did some simulation, I …

In December 2007 Werner Vogels posted a blog article entitled Eventual Consistency, since updated with a new article entitled Eventually Consistent - Revisited. In a nutshell it described how to scale databases horizontally across nodes by systematically trading off availability, strict data consistency, and partition resilience as defined by the CAP theorem. According to CAP, you can only have two of three of these properties at any one time. The route to highly available and performant databases, according to Vogels, is eventual consistency in which distributed database contents at some point converge to a single value but at any given time may be …

Sometimes you just have to laugh at the crazy things that can kill a good evening. I had this brilliant idea to change our replication setup on one of our Master-Master replication server setups this week. I got sick of having to restart MySQL every time we wanted to add a new database and have it included in the list of replicated databases - we were using replicate-do-db in our configs.So it

The reference manual explains it in detail.
There are two things that you must remember, if you are …

Suppose you have a MySQL master-slave replication setup. It is very straightforward, nothing exotic at all. Imagine that it is the simplest possible setup. Everything is fine, there is nothing wrong or misconfigured with either server.

Now you add another slave, and replication on your existing slave fails. You did not change anything on your existing slave or master. How did this happen?

I was setting up a slave manually for a quick-and-dirty experiment, when I found this one:

mysql  %gt; slave start;
ERROR 1200 (HY000): The server is not configured as slave; fix in config file or with CHANGE MASTER TO

Hmm... I did the CHANGE MASTER TO already, but just for the sake of it, let's do it again.

mysql %gt; change master to master_host='127.0.0.1', master_port=22346, 
master_user='msandbox', master_password='msandbox', 
master_log_file='mysql-bin.000002', master_log_pos=106;
Query OK, 0 rows affected (0.00 sec)

mysql  %gt; slave start;
ERROR 1200 (HY000): The server is not configured as slave; fix in config file or with CHANGE MASTER TO

Again! Why is it asking to execute CHANGE MASTER TO, which has been accepted right now?

Then it came to me. Because I did a manual configuration, and I …

Problem

With the release of MySQL 5.1, many DBAs are going to be scheduling downtime to upgrade their MySQL Server. As with all upgrades between major version numbers, it requires one of two upgrade paths:

• Dump/reload: The safest method of upgrading, but it takes out your server for quite some time, especially if you have a large data set.
• mysql_upgrade: A much faster method, but it can still be slow for very large data sets.

I’m here to present a third option. It requires minimal application downtime, and is reasonably simple to prepare for and perform.

Preparation

First of all, you’re going to need a second server (which I’ll refer to as S2). It will act as a ’stand-in’, while the main server (which I’ll refer to as S1) is upgraded. Once S2 is ready to go, you can begin the preparation:

• If you haven’t already, enable …

Slaves can be used for:

1. Horizontal read scalability — take the load off a master database by spreading reads to a replicated slave.
2. Disaster recovery — some disasters, such as a hardware failure, can be solved by having a slave ready to propagate to a master. This technique also works to make offline changes to a master/slave pair without having database downtime (see below).
3. Consistent Backups — without disrupting production usage, a slave can be used to take a consistent backup by stopping the database and copying the database files. This is a free and uncomplicated way to get consistent backups (innodb hot backup is not free, and using a snapshotting tool (such as LVM’s snapshot capability) can be complex. Not everyone wants to manage snapshots.)
   

Be careful when using a slave for more than one purpose. Using a slave for more than one purpose can be done, but carefully. For …

Problem

A problem we occasionally see is Relay Log corruption, which is most frequently caused by network errors. At this point in time, the replication IO thread does not perform checksumming on incoming data (currently scheduled for MySQL 6.x). In the mean time, we have a relatively easy workaround: encrypt the replication connection. Because of the nature of encrypted connections, they have to checksum each packet.

Solution 1: Replication over SSH Tunnel

This is the easiest to setup. You simply need to do the following on the Slave:

shell> ssh -f user@master.server -L 4306:master.server:3306 -N

This sets up the tunnel. slave.server:4306 is now a tunnelled link to master.server:3306. So now, you just need to alter the Slave to go through the tunnel:

mysql> STOP SLAVE;
mysql> CHANGE MASTER TO …