MySQL Multi-Master – Single-Slave – Replication (Episode 2)

Introduction

One of the features that make MySQL so great is its easy replication set-up. If you are experienced and know-how to do it, it takes you about 15 minutes to set-up a slave. What you have in the end is a replication from one master to one or several slaves. So you can build a top-down data stream pyramid and spread your data on many slaves.

From time to time some customers are asking for the other way: Many masters replicating to one slave (which is also called multi-source replication). For this requirement MySQL replication cannot help you directly.

Possibilities

You can circumvent this situation in the following ways:

• Implement your own data transfer mechanism.
• Use this ugly hack I have written down 2 years ago: Multi-Master-Single-Slave-Replication
• Test the approach described in the following article.
• Wait until MySQL has implemented it...

Possibility number 4 for would be the nicest one. Because then the solution would be properly supported by your database vendor and you do not have to take care much about problems, as long as you have a support contract.

When we look at the MySQL work log and search for replication we find a work log entry with the title: WL#1697: Multi-source replication. But for whatever reason it has the following status: Affects: Server-7.0 — Status: On-Hold — Priority: Low. :-(

What can we do now:

1. a) Resign.
2. b) Become an important customer (by paying much money), complain about the priority and if nothing changes escalate it to the top management of MySQL.
3. c) Find many many other fellow sufferers, unite and make your database vendors management aware of your desire.
4. d) Help yourself (it is eventually an Open Source product...).

Possibilities number 1 to 3 from above are some kind of Help yourself.

Help yourself

Because I am not a programmer and I have no clue about programming, possibility number 1 is out of question for me. But I am sure there are many other MySQL users out there in the world which would appreciate your effort.

Possibility number 2 is a quick and ugly hack but may work in some situations.

And number 3 I was pointed to by a user called Kwame who wrote me an email (thanks Kwame for the cool hint!).

A possible solution

One and a half year ago I wrote down a little article about MySQL Active - Active Clustering because we had ever and ever customer asking for a replacement for a well known but very expensive product from an other big database vendor.

Peter Zaitsev was not very happy with the content of it: MySQL MyISAM Active Active Clustering - looking for trouble?. But comments about the critics already gave a sign for the solution: When you combine the MySQL Active-Active Clustering with MySQL replication you can finally get a many-master single slave replication!

So what I did was the following:

I have 3 servers which act as a master and on the 4^th server I install 3 MySQL instances (mysqld) running on the same datadir. Please make sure, that you replicate ONLY ONE SCHEMA per master slave pair!

As configuration file for my slaves I have used something like this:

#
# my.cnf
#

[mysqld]

port                = 3308
socket              = /home/mysql/tmp/mysql-3308.sock
pid-file            = /home/mysql/data/mysqld5127/mysql-3308.pid
datadir             = /home/mysql/data/mysqld5127

skip-innodb
external-locking
log-error           = /home/mysql/data/mysqld5127/error_4.log

server_id           = 4
master-info-file    = /home/mysql/data/mysqld5127/master.info
relay-log           = /home/mysql/data/mysqld5127/mysql-3308-relay-bin
relay-log-index     = /home/mysql/data/mysqld5127/mysql-3308-relay-bin
relay-log-info-file = /home/mysql/data/mysqld5127/relay-log.info

Requirements

Make sure that:

• You only write to one schema per master (schema_a in master 1, schema_b in master 2 and so on...)
• You comply with all the requirements described in the document MySQL Active - Active Clustering
• You flush the tables on ALL slave before reading.

Very simple first tests showed, that it is possible to "aggregate" the data from many masters into a single slave. But please verify this proof of concept very carefully before you deploy it to your productive system with your real data. This approach is neither recommended nor supported by MySQL.

Drawback

A further replication from this slave system to other slaves is not possible (or at least no feasible solution comes to my mind) except you use MySQL cluster as "aggregator". Then it could be possible...