2

I've known for quite awhile that Continuent's Tungsten Replicator supports fan-in replication (example).

By fan-in replication I mean combining multiple datasources into a single instance (like a datawarehouse).

I recently came across this MySQL deck that seems to suggest that this could be done with MySQL 5.6. Specifically slide/page 35 says "Arbitrary Topology" under Topologies:.

Can someone confirm that fan-in replication is possible with MySQL 5.6?

Has anyone setup this topology in 5.6?

Thanks!

3 Answers 3

2

At present, MySQL does not support a native Multiple Master, Single Slave topology.

The phrase "Arbitrary Topology" is exact what it says. It is a topology of your making.

If you really want to create a Multiple Master, Single Slave topology, you came to the right place.

You will have to construct the topology based on the book

sdocns

Pages 373-375 has two diagrams of three DB Servers (M1, M2, S1)

DIAGRAM #1

  • M1 has the following characteristics
  • M2 has the following characteristics
  • S1 has the following characteristics
    • Slave of M2
    • Database db1 has real data
    • Database db2 has real data

DIAGRAM #2

  • M1 has the Database db1
  • M2 has the following characteristics
  • S1 has the following characteristics
    • Slave of M2
    • Database db1 has real data
    • Database db2 has real data

WARNING : As long as you do not run ALTER TABLE statements against a database that is made up of all BLACKHOLE tables, this approach is the closest you can get to Single Slave/Multiple Master Replication. You will have to live with possible latency issues.

Give it a Try !!!

I have written about such topologies before

1
1

The "Arbitrary Topology" on page 35 seems to be referring to the fact that the masters and slaves can cascade each other in an arbitrary order (a process that is simplified by GTIDs) but each slave subtends only one master... there is no fan-in in MySQL 5.6.

There is, however, such a feature, Multi-Source Replication, in MariaDB 10.0.2 (Alpha) which is based on MySQL 5.5 with some back-ported capabilities from 5.6.

The system shown below was a spare slave with about 100GB of data in around 11,000 tables that was running MySQL 5.5.29, which I upgraded to MariaDB 10.0.2 using the binary upgrade process (i.e., using all of the existing frm and ibd files by creating a symlink to the datadir inside the MariaDB directory and using the mysql_upgrade that came with MariaDB after starting it up for the first time).

This was already a working slave of one master and that slave reconnected to the original master and started replicating without intervention after the upgrade. I then synched the tables from a second master that contains a completely different data set (using mysqldump) and declared a second master connection, then started that one up. So far, so good, as you can see below:

mysql> show all slaves status;
+-----------------+-----------------------------------------------------------------------------+----------------------------------+-------------+---------------+-------------+---------------+------------------+---------------------+--------------------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+----------------+--------------------+------------+----------------------+--------------------+----------------------+---------------------------+------------------------+-----------+
| Connection_name | Slave_SQL_State                                                             | Slave_IO_State                   | Master_Host | Master_User   | Master_Port | Connect_Retry | Master_Log_File  | Read_Master_Log_Pos | Relay_Log_File                       | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master | Master_SSL_Verify_Server_Cert | Last_IO_Errno | Last_IO_Error | Last_SQL_Errno | Last_SQL_Error | Replicate_Ignore_Server_Ids | Master_Server_Id | Master_SSL_Crl | Master_SSL_Crlpath | Using_Gtid | Retried_transactions | Max_relay_log_size | Executed_log_entries | Slave_received_heartbeats | Slave_heartbeat_period | Gtid_Pos  |
+-----------------+-----------------------------------------------------------------------------+----------------------------------+-------------+---------------+-------------+---------------+------------------+---------------------+--------------------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+----------------+--------------------+------------+----------------------+--------------------+----------------------+---------------------------+------------------------+-----------+
|                 | Slave has read all relay log; waiting for the slave I/O thread to update it | Waiting for master to send event | xxxxxx      | xxxxxxxxxxxxx |        3306 |            60 | mysql-bin.002061 |           539132634 | xxxxxxxx-relay-bin.002186            |       2262766 | mysql-bin.002061      | Yes              | Yes               |                 |                     |                    |                        |                         |                             |          0 |            |            0 |           539132634 |       270698293 | None            |                |             0 | No                 |                    |                    |                 |                   |                |                     0 | No                            |             0 |               |              0 |                |                             |               11 |                |                    |          0 |                    0 |          268435456 |             44912452 |                         0 |               1800.000 | 0-25-1445 |
| yyyyyyyyyy      | Slave has read all relay log; waiting for the slave I/O thread to update it | Waiting for master to send event | yyyyyyyyyy  | zzzzzzzzz     |        3306 |            60 | mysql-bin.000006 |           496574930 | xxxxxxxx-relay-bin-yyyyyyyyyy.000003 |      43913408 | mysql-bin.000006      | Yes              | Yes               |                 |                     |                    |                        |                         |                             |          0 |            |            0 |           496574930 |        45396213 | None            |                |             0 | No                 |                    |                    |                 |                   |                |                     0 | No                            |             0 |               |              0 |                |                             |               81 |                |                    |          0 |                    0 |          268435456 |               211662 |                         4 |               1800.000 | 0-25-1445 |
+-----------------+-----------------------------------------------------------------------------+----------------------------------+-------------+---------------+-------------+---------------+------------------+---------------------+--------------------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+----------------+--------------------+------------+----------------------+--------------------+----------------------+---------------------------+------------------------+-----------+
2 rows in set (0.08 sec)

This is not limited to 2 masters, that's just all I've had time to set up so far. I plan to add more just to stress the system and see what it is capable of.

The same "database" (schema) names can be used on more than one of the masters but the table names within each schema must be unique:

There is no conflict resolution. The assumption is that there are no conflicts in data between the different masters.

Of course, if you do have overlapping schema names, you also have to watch your mysqldump configuration when synchronizing the data, such as not re-synching the mysql schema and using the --skip-add-drop-database to keep your already-copied overlapping schemas from being accidentally thwacked. If someone on one of the masters were to drop a database, that would be problematic, too, if names overlap.

1

Fan in (multi-source replication) will be supported from MySQL 5.7.

A labs release is available here: http://labs.mysql.com/

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.