Multi-source Replication

You are viewing an old version of this article. View the current version here.
MariaDB starting with 10.0.1

Multi-source replication means that one server has many masters from which it replicates. This feature was added in MariaDB 10.0.

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 without regard to case (case insensitive). You should preferably keep the connection name 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:

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
Variable max_relay_log_sizeMax size of relay log. Is set at startup to max_binlog_size if 0
Variablereplicate_do_dbTell the slave to restrict replication to updates of tables whose names appear in the comma-separated list. For statement-based replication, only the default database (that is, the one selected by USE) is considered, not any explicitly mentioned tables in the query. For row-based replication, the actual names of table(s) being updated are checked.
Variablereplicate_do_tableTells the slave to restrict replication to tables in the comma-separated list
Variablereplicate_ignore_dbTell the slave to restrict replication to updates of tables whose names do not appear in the comma-separated list. For statement-based replication, only the default database (that is, the one selected by USE) is considered, not any explicitly mentioned tables in the query. For row-based replication, the actual names of table(s) being updated are checked.
Variablereplicate_ignore_tableTells the slave thread to not replicate any statement that updates the specified table, even if any other tables might be updated by the same statement.
Variablereplicate_wild_do_tableTells the slave thread to restrict replication to statements where any of the updated tables match the specified database and table name patterns.
Variablereplicate_wild_ignore_tableTells the slave thread to not replicate to the tables that match the given wildcard pattern.
StatusSlave_heartbeat_periodHow often to request a heartbeat packet from the master (in seconds).
StatusSlave_received_heartbeatsHow many heartbeats we have got from the master.
StatusSlave_runningShows if the slave is running. YES means that the sql thread and the IO thread are active. No means either one is not running. '' means that @@default_master_connection doesn't exist.
Variable Sql_slave_skip_counterHow many entries in the replication log that should be skipped (mainly used in case of errors in the log).

You can access all of the above variables with either SESSION or GLOBAL.

Note that the replicate_... variables were added in MariaDB 10.0.2

Note that in contrast to MySQL, all variables always show the correct active value!

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. For example, Slave_retried_transactions now shows the total number of retried transactions over all slaves.

If you need to set gtid_slave_pos you need to set this for all masters at the same time.

New status variables:

NameDescription
Com_start_all_slavesNumber of executed START ALL SLAVES commands.
Com_start_slaveNumber of executed START SLAVE commands. This replaces Com_slave_start.
Com_stop_slaveNumber of executed STOP SLAVE commands. This replaces Com_slave_stop.
Com_stop_all_slavesNumber of executed STOP ALL SLAVES commands.

SHOW ALL SLAVES STATUS has the following new columns:

NameDescription
Connection_nameName of the master connection. This is the first variable.
Slave_SQL_StateState of SQL thread.
Retried_transactionsNumber of retried transactions for this connection.
Max_relay_log_sizeMax relay log size for this connection.
Executed_log_entriesHow many log entries the slave has executed.
Slave_received_heartbeatsHow many heartbeats we have got from the master.
Slave_heartbeat_periodHow often to request a heartbeat packet from the master (in seconds).

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 before the extension. The main exception is the file that holds all connection is named as the normal master-info-file with a multi- prefix.

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

NameDescription
multi-master-info-fileThe master-info-file (normally master.info) with a multi- prefix. This contains all master connections in use.
master-info-file-connection_name.extensionContains the current master position for what's applied to in the slave. Extension is normally .info
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.extension  Contains the name of the active relay-log-connection_name.xxxxx files. Extension is normally .index
relay-log-info-file-connection_name.extensionContains the current master position for the relay log. Extension is normally .info

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

Hint:

Instead of specifying names for mysqld 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 are no conflicts in data between the different masters.
  • All executed commands are stored in the normal binary log (nothing new here).
  • If the 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!
  • RESET SLAVE now deletes all relay-log files.

replicate-... variables

  • The support for replicate-... variables was added in MariaDB 10.0.2
  • One can set the values for the replicate-... variables from the command line or in my.cnf for a given connection by prefixing the variable with the connection name.
  • If one doesn't use any connection name prefix for a replicate.. variable, then the value will be used as the default value for all connections that don't have a value set for this variable.

Example:

mysqld --main_connection.replicate_do_db=main_database --replicate_do_db=other_database

The have sets the replicate_do_db variable to main_database for the connection named main_connection. All other connections will use the value other_database.

One can also use this syntax to set replicate-rewrite-db for a given connection.

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

  • 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.
  • One can change max_relay_log_size for any active connection, but new connections will always use the server startup value for max_relay_log_size, which can't be changed at runtime.
  • Option innodb-recovery-update-relay-log (xtradb feature to store and restore relay log position for slaves) only works for the default connection ''. As this option is not really safe and can easily cause loss of data if you use storage engines other than InnoDB, we don't recommend this option to be used.
  • slave_net_timeout affects all connections. We don't check anymore if it's less than Slave_heartbeat_period, as this doesn't make sense in a multi-source setup.

TODO

  • Semisync replication ('semisync_slave.so') doesn't yet work with multi-source.
  • All open tasks and known bugs for multi-source can be found here.
  • Allow replication from one master to one slave in many threads

Incompatibilities with MariaDB/MySQL 5.5

  • max_relay_log_size is now (almost) a normal variable and not automatically changed if max_binlog_size is changed. To keep things compatible with old config files, we set it to max_binlog_size at startup if its value is 0.
  • You can now access replication variables that depend on the active connection with either GLOBAL or SESSION.
  • We only write information about relay log positions for recovery if innodb-recovery-update-relay-log is set.
  • Slave_retried_transactions now shows the total count of retried transactions over all slaves.
  • The status variable Com_slave_start is replaced with Com_start_slave.
  • The status variable Com_slave_stop is replaced with Com_stop_slave.
  • FLUSH RELAY LOGS are not replicated anymore. This is not safe as connection names may be different on the slave.

See also

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.