claudionanni

MariaDB 10.0 has introduced the functionality to replicate data from more than one Master server. This is what many have been waiting for, especially those who do business intelligence analysis, aggregation and reporting on data coming from different and sometimes related applications.

The way multi-source replication is implemented is extremely simple - for every Master you need what is called a 'connection'. Previously you just had one, the default. Each 'connection' points to a Master and it has, just like regular replication, two threads: IO Thread and SQL Thread. This means that if you have two different Masters using the same Schema name, the two 'connections' associated with the separate Masters would operate on the same Schema on the Slave (specifically the Slave SQL Thread of both connections would work in the same schema). Moreover should the two source schemas contain tables with the same name, the changes will be applied on the very same table by both Slave SQL threads. However this behaviour is probably unwanted in most cases.

Problems arise when you have two or more Master servers that have one or more schema names that conflict. If this is the case, each SQL Thread of each connection will apply changes in the same schema. At the very least there would be a 'logical' mix of tables in the same schema (if none of the tables names conflict). But more likely data consistency would be lost by having two different sources applying different changes to the same tables.

How can we resolve this?

MySQL Replication has the facility to rename the schema that is used on the Slave. This means that if the schema on the Master is named 'customers', you can have the Slave replicating the changes inside a schema with a different name, say 'customers_usa'. The option is called --replicate-rewrite-db.

Would this option work with Multisource replication? (Sort of rhetorical question)

Multisource replication introduces the notation to select the Master connection, so that the --replicate-rewrite-db option can be set on a per-master-connection basis. The option must be set in the configuration file (my.cnf) and it is picked up at instance startup.

If we use as example two Master sources named: master_usa and master_emea, the (multisource) Slave configuration would look like:

[mysqld]
...
...
...
master_usa.replicate-rewrite-db=customer->customer_usa
master_emea.replicate-rewrite-db=customer->customer_emea
...
...

Once you have the Slave up and running, to define the Master connections you must use the notation for Multi-source replication that is:

mysql> CHANGE MASTER 'master_usa' TO MASTER_HOST='10.0.0.101', MASTER_PORT=10101, MASTER_USER='repl';
mysql> CHANGE MASTER 'master_emea' TO MASTER_HOST='10.0.0.102', MASTER_PORT=10102, MASTER_USER='repl';
mysql> START ALL SLAVES;

Keep in mind that Multi-source replication has a slightly modified syntax which is nicely described on our Knowledge Base.

While with simple Replication you have only one Slave worker, with Multi-source one you can have multiples. This means you always need to specify whether you're refer to a specific worker or all of them. This can be done by either using the connection name ('master_usa' and 'master_emea' in our example), the keyword ALL, or the session variable 'default_master_connection'. If everything is working as expected the changes that happen on the 'customer' schema on '10.0.0.101' will end up in a schema named 'customer_usa' on the Slave. Similarly the changes to '10.0.0.102' will go into 'customer_emea' schema on the Slave.

Use Case

An example use case is reporting data from two different geographical databases (sharded servers) that are now, thanks to multi-source replication, consolidated in one place.

If you have the table 'sales' in the 'customer' schema which are both identical on the USA & EMEA Master servers, and now you have a multi-source slave instance with the schemas 'customer_usa' and 'customer_emea', you may want to create a report including data from both geographical datasets.

One way is to create a view over the 'sales' table on both schemas similar to this:

SELECT 'usa' as `Area`, s1.* FROM customer_usa.sales s1
UNION ALL
SELECT 'emea' as `Area`, s2.* FROM customer_emea.sales s2;

(PS: UNION ALL avoids check for duplicates making the UNION faster)

aurélienlequoy

Hi Claudio, I can see that you liked my article : http://www.mysqlplus.net/2014/07/04/real-life-multi-master-case/

Aurélien

claudionanni

Hi, I haven't read it yet but thanks for the link.

vadim_pestovnikov_g

Hi,
Thank for this article.

I have got a very strange behavior during my set up. I have MySQL 5.6 db1, db2 are masters and MariaDB 10.0.x db3 is the slave. Both masters have the same schema name e.g. schema_name.

The replication set up goes well but when I start all slaves I am getting an error that the same table doesn't exist either in test_schema_db1 or in test_schema_db2.

my.cnf from db3

...
db1.replicate-do-db = test_schema
db1.replicate-rewrite-db = test_schema->test_schema_db1

db2.replicate-do-db = test_schema
db2.replicate-rewrite-db = test_schema->test_schema_db2
...

CHANGE MASTER 'db1' TO MASTER_HOST = "db1_ip", MASTER_USER = "user", MASTER_PASSWORD ="secret", MASTER_LOG_FILE='binary_log_file', MASTER_LOG_POS=position;

START SLAVE 'db1';

CHANGE MASTER 'db1' TO MASTER_HOST = "db1_ip", MASTER_USER = "user", MASTER_PASSWORD ="secret", MASTER_LOG_FILE='binary_log_file', MASTER_LOG_POS=position;

START SLAVE 'db2';

SHOW SLAVE 'db1' STATUS\G
...
Slave SQL: Error executing row event: 'Table 'test_schema.table_name' doesn't exist', Internal MariaDB error code: 1146
...

SHOW SLAVE 'db2' STATUS\G
...
Slave SQL: Error executing row event: 'Table 'test_schema.table_name' doesn't exist', Internal MariaDB error code: 1146
...

If I comment my settings:

#db2.replicate-do-db = test_schema
#db2.replicate-rewrite-db = test_schema->test_schema_db2

and restart the server. Then ebnable db1 replication then uncomment above settings and restart the server and enable replication db2 everything works as expected.

Another problem I have got by replicating from Percona Server 5 .6

[ERROR] Master 'db1': Error in Log_event::read_log_event(): 'Found invalid event in binary log', data_len: 44, event_type: 33
[ERROR] Master 'db1': Error reading relay log event: slave SQL thread aborted because of I/O error
[ERROR] Master 'db1': Slave SQL: Relay log read failure: Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave. Internal MariaDB error code: 1594

I saw some posts on the internet that it might be caused by different format of binary logs like MariaDB recently changed the binary log format?

truptikmali

I also got the same error while trying replication Table 'DBName.TableName' doesn't exist Error_code: 1146.

This is a fresh slave and master setup. Any idea?

aurélien_lequoy_g

please send me your problem by email or there : http://www.mysqlplus.net/2014/07/04/real-life-multi-master-case/

with right format (human readable I mean). I wIll try to fix your problem

Newsletter Signup

Subscribe to get MariaDB tips, tricks and news updates in your inbox: