Announcing MySQL Multi-Source Replication Utility

By Nuno Mariz, on 1 April 2014 @ 15:05

The MySQL Utilities team is pleased to announce a new MySQL utility, mysqlrplms, that allows users to setup multi-source replication. This utility is available for server versions 5.6.9 and later with GTIDs enabled. The new utility is included in MySQL Utilities release-1.4.2 RC.

What is multi-source replication?

Multi-source replication is often regarded as a means to aggregate and consolidate different streams of data (from master servers - also called sources) into one single server (slave) instance. This is useful for consolidating non-conflicting data changes into a central server. Not only does this allow users to perform backups of the consolidated data, it also permits users to execute queries that may span the datasets.

Requirements and assumptions

  • The mysqlrplms utility supports MySQL 5.6.9 and higher version servers with GTIDs enabled.
  • There must not be any overlap or data conflicts for the data from different masters. For example, there are no updates to the same object from multiple masters.
  • Replication must be asynchronous.
  • A round-robin scheduling is used to setup replication among all masters and slave.

Note: a POSIX system is required for using the daemon option.

Examples

Basic multi-source replication setup:

shell> mysqlrplms --slave=root:pass@host1:3306 \
                  --masters=root:pass@host2:3306,root:pass@host3:3306
# Starting multi-source replication...
# Press CTRL+C to quit.
# Switching to master 'host2:3306'.
# master on localhost: ... connected.
# slave on localhost: ... connected.
#
# Current Master Information:
+-------------------+-----------+---------------+-------------------+
| Binary Log File   | Position  | Binlog_Do_DB  | Binlog_Ignore_DB  |
+-------------------+-----------+---------------+-------------------+
| clone-bin.000001  | 594       | N/A           | N/A               |
+-------------------+-----------+---------------+-------------------+
# GTID Executed Set: 0cd1200c-b5a2-11e3-a342-28d244017f26:1-2
#
# Health Status:
+------------+-------+---------+--------+------------+---------+
| host       | port  | role    | state  | gtid_mode  | health  |
+------------+-------+---------+--------+------------+---------+
| host2      | 3306  | MASTER  | UP     | ON         | OK      |
| host1      | 3306  | SLAVE   | UP     | ON         | OK      |
| host3      | 3306  | MASTER  | UP     | ON         | OK      |
+------------+-------+---------+--------+------------+---------+
(...)
# Switching to master host3:3306'.
# master on localhost: ... connected.
# slave on localhost: ... connected.
#
# Current Master Information:
+-------------------+-----------+---------------+-------------------+
| Binary Log File   | Position  | Binlog_Do_DB  | Binlog_Ignore_DB  |
+-------------------+-----------+---------------+-------------------+
| clone-bin.000001  | 594       | N/A           | N/A               |
+-------------------+-----------+---------------+-------------------+
# GTID Executed Set: c30a1796-b998-11e3-bd1a-28d244017f26:1-2
#
# Health Status:
+------------+-------+---------+--------+------------+---------+
| host       | port  | role    | state  | gtid_mode  | health  |
+------------+-------+---------+--------+------------+---------+
| host3      | 3306  | MASTER  | UP     | ON         | OK      |
| host1      | 3306  | SLAVE   | UP     | ON         | OK      |
| host2      | 3306  | MASTER  | UP     | ON         | OK      |
+------------+-------+---------+--------+------------+---------+
(...)

Multi-source replication setup using different report values:

shell> mysqlrplms --slave=root:pass@host1:3306 \
                  --masters=root:pass@host2:3306,root:pass@host3:3306 \
                  --report-values=health,gtid,uuid

Start multi-source replication running as a POSIX daemon:

shell> mysqlrplms --slave=root:pass@host1:3306 \
                  --masters=root:pass@host2:3306,root:pass@host3:3306 \
                  --log=rplms_daemon.log --pidfile=rplms_daemon.pid \
                  --daemon=start

Restart a multi-source replication running as a POSIX daemon:

shell> mysqlrplms --slave=root:pass@host1:3306 \
                  --masters=root:pass@host2:3306,root:pass@host3:3306 \
                  --log=rplms_daemon.log --pidfile=rplms_daemon.pid \
                  --daemon=restart

Stop a multi-source replication running as a POSIX daemon:

shell> mysqlrplms --slave=root:pass@host1:3306 \
                  --masters=root:pass@host2:3306,root:pass@host3:3306 \
                  --log=rplms_daemon.log --pidfile=rplms_daemon.pid \
                  --daemon=stop

Send us your feedback

MySQL Utilities release-1.4.2 RC is available for download from the following links:

The documentation of MySQL Utilities can be obtained from the following link: http://dev.mysql.com/doc/index-gui.html

Contributing Ideas:

Comments

No comments yet.

Post a comment