The goal of this tutorial is to show you how to use Multi-Master and aggregate databases with the same name but with different data from different masters.
Example:
master1 => a French subsidiary
master2 => a British subsidiary
Both have the same database (PRODUCTION) but the data are totally different:
We will start with 3 servers (2 masters and 1 slave), you can add more master if needed.
Informations
10.10.16.231: first master (aka ”’master1”’) => a French subsidiary
10.10.16.232: second master (aka ”’master2”’) => a British subsidiary
10.10.16.233: slave (multi-master) (aka ”’slave”’)
If you have already your 3 servers correctly installed go directly to: “Dump your databases”
Default installation on 3 servers
The main reason why I put the apt configuration in a different file is that we use a configuration manager and this one overwrite /etc/apt/sources.list.
Also, if any trouble occurs just remove this file and you 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 crc32 command isn’t installed, so, we will use the MySQL function instead.
Even if your server have more interface you should have no trouble because the IP should be unique.
Find out a minimalist configuration file. Consider to use your own configuration.
If you are interested by my default MariaDB 10 configuration at Photobox, just click here
Then, restart the server:
Repeat these actions on the 3 servers.
Create users on both master
Create replication’s user on both master
On master1:
On master2:
Create an user for external backup
On master1 and on master2:
Only for testing
Considering you didn’t have a such configuration and you want to test it
Create a database on master1:
Create a database on master2:
Load slave with master data
All the commands until the end have to be done on the slave server:
* –master-data=2: Get the binary log and the position, and put it at the beginning of the dump in comment
* –single-transaction: This option issues a BEGIN SQL statement before dumping data from the server (works only with InnoDB tables)
Create both new databases:
Then load the data:
Set up replication on slave
Edit both dump files and get the name and the position of the binlog and replace it as following:
French subsidiary:
Get the line: (file and position will be different)
Replace name and position in this command:
English subsidiary
Get the line: (file and position will be different)
Replace name and position in this command:
Rules of replication on config file
Unfortunatly the option replicate-rewrite-db doesn’t exist as a variable and we can’t set up this configuration without restarting the slave server.
Add the following lines to /etc/mysql/conf.d/mariadb10.cnf on the slave:
Then you can restart MySQL (Don’t forget to launch the slaves because we skipped it at start):
Start replication:
* one by one:
* all together:
Check the replication:
Tests
On slave:
On master1:
On master2:
On slave:
It works!
Limitations
WARNING: it doesn’t work with the database specified in query.
This work fine:
This query will break the replication:
=> database `PRODUCTION` does not exist on this server.
Real examples
Missing update
On master1:
On slave:
In this case we missed the update.
it’s a real problem because the replication should crash, the slave is desynchronized with master1 and we didn’t know it.
Replication crash
On master1:
On pmacli:
On slave:
We got the error!
Author: Aurélien LEQUOY
License: This article is under : ”’The GNU General Public License v3.0”’ http://opensource.org/licenses/GPL-3.0
PlanetMySQL Voting:
Vote UP /
Vote DOWN