The Ultimate Guide to High Availability with MariaDB

Multi-source replication

You are viewing an old version of this article. View the current version here.

Contents

  1. New Syntax
  2. Replication variables for multi-source
  3. New files
  4. Other things
  5. Typical use cases
  6. Limitations
  7. TODO
  8. See also:

Multi-source replication means that one server has many masters from which it replicates. This is a new feature in the upcoming MariaDB 10.0 release.

New Syntax

You specify which master connection you want to work with by either specifying the connection name in the command or setting @@default_master_connection to the connection you want to work with.

The connection name may include any characters and should be less than 64 characters. Connection names are compared case insensitive. You should preferably keep this short as it will be used as a suffix for relay logs and master info index files.

The new syntax introduced to handle many connections are:

The original old-style connection is an empty string ''. You don't have to use this connection if you don't want to.

You create new master connections with CHANGE MASTER.
You delete the connection permanently with RESET SLAVE "connection_name" ALL.

Replication variables for multi-source

The new replication variable @@default_master_connection specifies which connection will be used for commands and variables if you don't specify a connection. By default this is '' (the default connection name).

The following replication variables are local for the connection. (In other words, they show the value for the @@default_master_connection connection). We are working on making all the important ones local for the connection.

TypeNameDescription
StatusSlave_runningShows if the slave is running
Variable sql_slave_skip_counterHow many entries in the replication log that should be skipped (mainly used in case of errors in the log). This variable contains always the correct value (which is not the case with MySQL).

Example:

set @@default_master_connection='';
show status like 'Slave_running';
set @@default_master_connection='other_connection';
show status like 'Slave_running';

If @@default_master_connection contains a non existing name, you will get a warning.

All other master related variables are global and affect either only the "" connections or all connections.

New files

The basic principle of the new files used by multi source replication is that they have the same name as the original relay log files suffixed with connection_name. The main exception is the file that holds all connection that is named as the normal master-info-file with a .index suffix.

When you are using multi source, the following new files are created:

NameDescription
multi-master-info-filenameThe master_info file (normally master.info) with a multi- prefix. This contains all master connections in use.
master-info-filename.connection_nameContains the current master position for what's applied to in the slave.
relay-log-connection_name.xxxxxThe relay-log name with a connection_name suffix. The xxxxx is the relay log number. This contains the replication data read from the master.
relay-log-index-connection_name  Contains the name of the active relay-log#-connection_name.xxxxx files.
relay-log-info_file.connection_nameContains the current master position for the relay log.

When creating the file, the connection name is converted to lower case and all special characters in the file name is converted, the same way as MySQL table names are converted, to make the name portable across different systems.

Hint: Instead of specifying names with relay-log, relay-log-index, relay-log-index, general-log, slow-log, log-bin, log-bin-index you can just specify log-base-name and all the other variables are set with this as a prefix.

Other things

  • All error messages from a slave with a connection name, that are written to the error log, are prefixed with Master 'connection_name':. This makes it easy to see from where an error originated.
  • Errors ER_MASTER_INFO and WARN_NO_MASTER_INFO now includes connection_name.
  • There is no conflict resolution. The assumption is that there is no conflicts in data between the different masters.
  • All executed are stored in the normal binary log (nothing new here).
  • If server variable log_warnings > 1 then you will get some information in the log about how the multi-master-info file is updated (mainly for debugging).
  • SHOW [FULL] SLAVE STATUS has one line per connection and more columns than before. Note that the first column is the connection_name!

Typical use cases

  • You are partitioning your data over many masters and would like to get it all together on one machine to do analytical queries on all data.
  • You have many databases spread over many MariaDB/MySQL servers and would like to have all of them on one machine as an extra backup.

Limitations

  • You can for now only have 64 masters (trivial to increase if necessary).
  • Each active connection will create 2 threads (as is normal for MariaDB replication).
  • You should ensure that all master have different server-id's. If you don't do this, you will get into trouble if you try to replicate from the multi-source slave back to your masters.

TODO

  • Not all slave system or status variables are yet multi-source ready. This is work in progress...

See also:

  • The work in MariaDB is based on the project description at MDEV-253.
  • The original code base comes from Taobao, developed by Plinux. A big thanks to them for this important feature!

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.