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:
- MySQL developers website: http://dev.mysql.com/downloads/tools/utilities/
- Launchpad project: https://launchpad.net/mysql-utilities
- Commercial customers can get it here: https://edelivery.oracle.com/
The documentation of MySQL Utilities can be obtained from the following link: http://dev.mysql.com/doc/index-gui.html
Contributing Ideas:
- Community users: http://bugs.mysql.com (MySQL Workbench: Utilities)
- Oracle customers: http://bug.oraclecorp.com (Product = MySQL Workbench, Component = WBUTILS)