Buy Percona ServicesBuy Now!

Multi-master with MariaDB 10 – a tutorial

 | September 10, 2018 |  Posted In: MariaDB, MySQL, Open Source Databases

PREVIOUS POST
NEXT POST

The goal of this tutorial is to show you how to use multi-master to aggregate databases with the same name, but different data from different masters, on the same slave.

Example :

  • master1 => a French subsidiary
  • master2 => a British subsidiary

Both have the same database PRODUCTION but the data are totally different.

 

PmaControl schema topology

This screenshot is made from my own monitoring tool: PmaControl. You have to read 10.10.16.232 on master2 and not 10.10.16.235.
The fault of my admin system! :p)

We will start with three servers—2 masters and 1 slave—you can add more masters if needed. For this tutorial, I used Ubuntu 12.04. I’ll let you choose the right procedure for your distribution from Downloads.

Scenario

  • 10.10.16.231 : first master (referred to subsequently as master1) => a French subsidiary
  • 10.10.16.232 : second master (referred to subsequently as master2) => a British subsidiary
  • 10.10.16.233 : slave (multi-master) (referred to subsequently as slave)

If you already have your three servers correctly installed, you can scroll down directly to “Dump your master1 and master2 databases from slave“.

Default installation on 3 servers

The main reason I put it in a different file because we use Chef as the configuration manager and this overwrites /etc/apt/sources.list . The other reason is that if any trouble occurs, you can just remove this file and restart with the default configuration.

The goal of this small script is to get the IP of the server and make a CRC32 from this IP to generate one unique server-id. Generally the command CRC32 isn’t installed, so we will use the one from MySQL. To set account // password we use the account system of Debian / Ubuntu.

Even if your server has more interfaces, you should have no trouble because the IP address should be unique.

This configuration file is not one I use in production, but a minimal version that’s shown just as an example. The config may work fine for me, but perhaps it won’t be the same for you, and it might just crash your MySQL server.

If you’re interested in my default install of MariaDB 10  you can see it here: https://raw.githubusercontent.com/Esysteme/Debian/master/mariadb.sh  (this script as been updated since 4 years)

example :

 

We restart the server

Repeat these actions on all three servers.

Create users on both masters

Create the replication user on both masters

on master1 (10.10.16.231)

on master2 (10.10.16.232)

Create a user for external backup

On master1 and on master2

If you are just testing…

If you don’t have a such a configuration and you want to set up tests:

Create a database on master1 (10.10.16.231)

Create a database on master2 (10.10.16.232)

Dump your master1 and master2 databases from slave (10.10.16.233)

All the commands from now until the end have to be carried out on the slave server

  • --master-data=2  get the file (binary log) and its position, and add it to the beginning of the dump as a comment
  • --single-transaction  This option issues a BEGIN SQL statement before dumping data from the server (this works only on tables with the InnoDB storage engine)

Create both new databases:

Load the data :

Set up both replications on the slave

Edit both dumps to get file name and position of the binlog, and replace it here: (use the command “less” instead of other commands in huge files)

French subsidiary – master1

get the line : (the MASTER_LOG_FILE and MASTER_LOG_POS values will be different to this example)

replace the file and position in this command:

English subsidiary – master2

get the line: (the MASTER_LOG_FILE and MASTER_LOG_POS values will be different to this example, and would normally be different between master1 and master2. It’s just in my test example they were the same)

replace the file and position in this command:

Rules of replication on config file

Unfortunately, the option replicate-rewrite-db doesn’t exist for variables, and we cannot set up this kind of configuration without restarting the slave server. In the section relating to the slave, add the following lines to

add these lines :

After that, you can restart the daemon without a problem – but don’t forgot to launch the slaves because we skipped that at the start ;).

Start the replication:

  • one by one

  • all at the same time:

Now to check the replication :

Tests

on slave:

on master1:

on master2:

on slave:

It works!

 

If you want do this online, please add +1 to : https://jira.mariadb.org/browse/MDEV-17165

 

Limitations

WARNING: it doesn’t work with the database specified in query. (With Binlog_format = STATEMENT or MIXED)

This works fine:

This query will break the replication :

=> databases PRODUCTION does not exist on this server.

A real example

Missing update

on master1:

on slave:

In this case we missed the update. It’s a real problem, because if the replication should crash, our slave is desynchronized with master1 and we didn’t realize it.

Crash replication

on master1:

on PmaControl:

pmacli schema diagram showing error

on slave:

And we got the error which crash replication : Error TABLE 'PRODUCTION.france' doesn't exist' ON query. DEFAULT DATABASE: 'PRODUCTION_FR'. Query: 'UPDATE `PRODUCTION`.`france` SET id=4 WHERE id =3

NB : Everything works fine with binlog_format=ROW.

Author: Aurélien LEQUOY <aurelien.lequoy@esysteme.com> you don’t copy/paste the email, it won’t work. You didn’t think I would post it like that in the open for all bots, right? ;).

License

This article is published under: The GNU General Public License v3.0 http://opensource.org/licenses/GPL-3.0

Others

The point of interest is to describe a real use case with full technical information to allow you to reproduce it by yourself.

This article was originally published just after the release of MariaDB 10.0 on the now defunct website www.mysqlplus.net.

PREVIOUS POST
NEXT POST
Aurélien LEQUOY

Founder & CEO at 68 Koncept Inc, MySQL Expert, Entrepreneur, Author of Glial (PHP framework) & PmaControl (MySQL Tools)

Leave a Reply