A couple of weeks ago I wrote a lua script to use with the MySQL Proxy that transforms the Proxy into a key=>value lookup dictionary.

But I couldn't just stop there. So I decided to add replication to it :).


The basic idea is that you have one proxy that acts like a master, it can handle all write/read operations. Once it receives a write query, it will send that query to the slave proxy instances, and after the last slave gets the query, the master will return a confirmation to the mysql client.
And of course, you send your read queries to the slave proxy instances.

Show me the code.
It is available on the …

This Thursday (May 28th, 14:00 UTC), Lars Thalmann will give a MySQL University session on MySQL Replication: Walk-through of the new 5.1 and 6.0 features. (This session was originally scheduled for May 7th, but had to be put off due to technical problems. Apologies.) Lars is leading the replication and backup teams at MySQL, so this is one of the best opportunities to ask whatever questions you might have about new replication features in MySQL.

For MySQL University sessions, point your browser to this page. You need a browser with a working Flash plugin. You may register for a Dimdim account, but you don't have to. (Dimdim is the …

There are various setups and solutions to solve the problem of having redundant mysql masters and scaling writes on them. MySQL Cluster can be an alternative. Here is why and how:
Pros:

• No need to worry about DRBD failover and recovery times
• No need to worry about potential data loss when failover from active master to standby master (has all binlog really been replicated over from the failed master?)
• Writes go to any mysql server connected to the Cluster - thus writes are easy to scale
  The data nodes will then send the committed writes to every mysql server with --log-bin enabled.
  
• MySQL Cluster can handle a lot of writes (but the slaves can become the bottleneck)
  
• Data nodes can be added online (MySQL Cluster 7.0) - …

I see a lot of people filtering replication with binlog-do-db, binlog-ignore-db, replicate-do-db, and replicate-ignore-db. Although there are uses for these, they are dangerous and in my opinion, they are overused. For many cases, there's a safer alternative.

The danger is simple: they don't work the way you think they do. Consider the following scenario: you set binlog-ignore-db to "garbage" so data in the garbage database (which doesn't exist on the slave) isn't replicated. (I'll come back to this in a second, so if you already see the problem, don't rush to the comment form.)

Now you do the following:

PLAIN TEXT CODE:

1. $ mysql
2. mysql> delete from garbage.junk;
3. mysql> use garbage;
4. mysql> update production.users set disabled = 1 where user …

Introduction

A question I recently saw on Stack Overflow titled Faster way to delete matching [database] rows? prompted me to organize my thoughts and observations on the subject and quickly jot them down here.

Here is the brief description of the task: say, you have 2 MySQL tables a and b. The tables contain the same type of data, for example log entries. Now you want to delete all or a subset of the entries in table a that exist in table b.

Solutions Suggested By Others

DELETE FROM a WHERE EXISTS (SELECT b.id FROM b WHERE b.id = a.id);

DELETE a FROM a INNER JOIN b on a.id=b.id;

DELETE FROM a WHERE id IN (SELECT id FROM b)

The Problem With Suggested Solutions

Solutions above are all fine if the tables are quite small and the …

I’ve just come across the following post on new 5.1 and 6.0 replication features which was quite interesting.

A few features mentioned such as:

• pluggable replication architecture
• semi-synchronous replication
• time-delayed replication
• scriptable replication

sound like things what are going the right way. For those of still using 5.0 we’ll have to wait for quite a long time before we will actually be able to use them. At least some of this gives us an incentive to upgrade.

No mention yet of N:1 replication, or making the replication process separate from the main mysqld process. The latter is much easier now and would only require minimal hooks in a plugin at each send of the replication path. However, these are things that can come in the …

This Thursday (May 7th, 14:00 UTC), Lars Thalmann will give a MySQL University session on MySQL Replication: Walk-through of the new 5.1 and 6.0 features. Lars is leading the replication and backup teams at MySQL, so this is one of the best opportunities to ask whatever questions you might have about new replication features in MySQL.

For MySQL University sessions, point your browser to this page. You need a browser with a working Flash plugin. You may register for a Dimdim account, but you don't have to. (Dimdim is the conferencing system we're using for MySQL University sessions. It provides integrated voice streaming, chat, …

Migrating from Oracle to MySQL is not easy. A few weeks ago Baron Schwartz summarized the culture shock in 50 things to know before migrating Oracle to MySQL. It's a great article but as you read through the comments it's easy to forget that culture shock can run the other way.

For example, try building horizontally scaled systems. Oracle has excellent "small" database editions like SE and SE1. However, they lack built-in replication of the type provided by MySQL. Even simple and effective deployment patterns like master-master replication do not exist. The usual approach in the Oracle world is to use RAC + Enterprise Edition features like Streams and DataGuard. That's great for large enterprises, but it's not a good method for smaller businesses and start-ups.

We have been working for some time on a …

Now, relax. This is not haircut blogging. There is actually a true relationship between MySQL Community and sushi. Just bear with me for a while.
I went to meet Drew in front of a quiet sushi restaurant in Santa Clara, CA. In his latest email, he said "we will meet you there", implying that there was more than one person. He mentioned a wife in one of his email, and so I expected at least two people.
Curious thing, this meeting. It all started in 2006, when I published an article about replication techniques. In answer to that article, I received dozens of email messages, with comments, congratulations, requests for help, job offers. Drew's message started as a praise, than he asked some questions, and we exchanged some more emails in the …

A little challenge for you… given an existing app that does not know about separate master/slave connections, and you want to enable working in a replicated infrastructure. Simply redirecting all SELECTs to the slave connection will not work. Why?

Hint: there are at least two reasons, depending on other factors. There may be more.

Comments are set to be moderated so providing answers will not spoil it for others. I’ll leave it run for a bit and then approve all comments.