Baron Schwartz started a good discussion about MMM use cases that quickly veered into an argument about clustering in general. As Florian Haas put it on his blog, this is not just an issue of DRBD vs. MySQL Replication. Is a database cluster something you cobble together through bits and pieces like MMM? Or is it something integrated that we can really call a cluster? This is the core question that will determine the future of clustering for open source databases.

I have a strong personal interest in this question, because Tungsten clustering, which I designed, is betting …

I’ve been thinking recently about the failure scenarios of MySQL replication clusters, such as master-master pairs or master-master-with-slaves. There are a few tools that are designed to help manage failover and load balancing in such clusters, by moving virtual IP addresses around. The ones I’m familiar with don’t always do the right thing when an irregularity is detected. I’ve been debating what the best way to do replication clustering with automatic failover really is.

I’d like to hear your thoughts on the following question: what types of scenarios require what kind of response from such a tool?

I can think of a number of failures. Let me give just a few simple examples in a master-master pair:

Problem: Query overload on the writable master makes mysqld unresponsive

Do nothing. Moving the queries to another server will cause cascading failures.

Problem: The …

When one wants to script automated replication chain building, certain things are quite annoying, like immutable replication configuration variables. For example, at certain moments log_slave_updates is more than needed, and thats what the server says:

mysql> show variables like 'log_slave_updates';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| log_slave_updates | OFF   |
+-------------------+-------+
1 row in set (0.00 sec)

mysql> set global log_slave_updates=1;
ERROR 1238 (HY000): Variable 'log_slave_updates' is a read only variable

Of course, there are few options, roll in-house fork (heheeeee!), restart your server, and keep warming up your tens of gigabytes of cache arenas, or wait for MySQL to ship a feature change in next major release. Then there are evil tactics:

mysql> system gdb -p $(pidof mysqld)
                       -ex "set opt_log_slave_updates=1" -batch …

Several months ago, I started a little project at work, called Mockload. It started as a fun way of using the MySQL Proxy, to test our monitoring agent, as well as the rules engine and graphs on the Service Manager.

Why?
I needed a tool that would be easy to use, and improve over time. And that it would allow our QA team to send custom values to the service manager. The goal was to pretend having some very busy MySQL servers.

And what better tool, than the MySQL Proxy itself to pretend being a busy MySQL Server!
The way our agent collects the MySQL related data, is by issuing different sql queries. So, I thought that I could have a MySQL proxy instance in between …

Tungsten Replicator has built-in backup and restore for MySQL! I checked in the final touches over the weekend. Here's how to run a backup on a database and store it so you can restore it later. If you leave off options, we use the default back-up procedure and storage that you select when setting up replication.

trepctl backup [-backup agent] [-storage agent] [-limit timeout]

And here's how to restore. If you leave off the options, we find the latest backup in your default storage and load that.

trepctl restore [-uri backup_uri] [-limit timeout]

That's the syntax. Now here's what happens behind the scenes. First Tungsten Replicator has a new BackupAgent plug-in that implements backup procedures. We have a backup agent for each the following types of backups for MySQL:

• Mysqldump - …

Recently with a client while running a number of disaster recovery tests I came across a nasty situation which was not part of the original plan and provided a far worse disaster situation then expected.

I should preface this condition with some of the environment conditions.

• MySQL 5.0 Enterprise 5.0.54
• RHEL 5 64bit
• Master and 2 Slaves
• MySQL Data and MySQL Binary Logs/MySQL Error Logs are on separate disk partitions

While running stress tests under high load, we tested the filling of partition containing the logs. This partition included the binary log and MySQL error log.

The observed output was.

• An error message was written to the MySQL error log. See below.
• Application throughput dropped, but did not stop.
• Binary logs stopped occuring.
• MySQL proactively stopped logging but continued to process …

It seems pretty common to find customers install DRBD for the wrong reasons. There are many pros/cons to compare DRBD to replication, but I've managed to cut down my spiel I give to customers to these two points:

• DRBD's aim (assuming replication mode C) is to provide 100% consistency, and then as much uptime as possible.
• MySQL Replication (with a manager such as MMM) aims to have 100% availability, at the potential loss of some data surrounding a failure.

So if you are installing DRBD with the aim of purely "availability", and are not worried about losing that last write on the crash to your master database that (hopefully) happens only once every few years, you may be using the wrong technology.

While the prized "1 minute failover" is possible in DRBD, it doesn't really explain the full picture. The typical crash …

After some fiddling around with the alternatives, I convinced myself that Gearman is the way to go. Before I proceed to show what I did, though, perhaps it's useful if I spend a few words about Gearman …

I reported a bug about SHOW BINLOG EVENTS not working with relay logs a couple of years ago - Bug #28777.

It’s now been fixed in MySQL 5.4, by adding a new SHOW statement - SHOW RELAYLOG EVENTS.

The replication team are really hammering through things at the moment - Kudos!

This post doesn’t contain a tip, I’m asking the q and I don’t know the answer yet. Could just be a logic error on my part, in which case it was just a personal mystery and I thank you for helping!

MySQL error 1449 “There is no ‘username’@'host’ registered”

You can get a replication fail with this error if you have triggers, views or stored routines that run as their creator (which is usual) but the slave does not have that user. So far so good.

We just encountered a replication fail on multiple slaves (i.e. not a single isolated mishap) with this error on an UPDATE statement accessing a single base table (i.e. no view), and no triggers defined or routines called in the query. Indeed the slave did not have the reported user (intentionally), but that shouldn’t have mattered. Creating the user allowed replication to continue successfully – that’s no surprise either.

The question …