Multi-source replication means that one server has many masters from which it replicates. This feature was added in MariaDB 10.0.
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:
CHANGE MASTER ['connection_name'] TO .... This creates or modifies a connection to a master.
FLUSH RELAY LOGS ['connection_name']
RESET SLAVE ['connection_name'] [ALL]. This is used to reset slave replication position or to remove a slave permanently.
SHOW RELAYLOG ['connection_name'] EVENTS
SHOW SLAVE ['connection_name'] STATUS
SHOW ALL SLAVES STATUS
START SLAVE ['connection_name'...]]
START ALL SLAVES ...
STOP SLAVE ['connection_name'] ...
STOP ALL SLAVES ...
The original old-style connection is an empty string
''. You don't have to use this connection if you don't want to.
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.
|Variable||max_relay_log_size||Max size of relay log. Is set at startup to
|Variable||replicate_do_db||Tell 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.|
|Variable||replicate_do_table||Tells the slave to restrict replication to tables in the comma-separated list|
|Variable||replicate_ignore_db||Tell 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.|
|Variable||replicate_ignore_table||Tells 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.|
|Variable||replicate_wild_do_table||Tells the slave thread to restrict replication to statements where any of the updated tables match the specified database and table name patterns.|
|Variable||replicate_wild_ignore_table||Tells the slave thread to not replicate to the tables that match the given wildcard pattern.|
|Status||Slave_heartbeat_period||How often to request a heartbeat packet from the master (in seconds).|
|Status||Slave_received_heartbeats||How many heartbeats we have got from the master.|
|Status||Slave_running||Shows 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
|Variable||Sql_slave_skip_counter||How 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
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!
set @@default_master_connection=''; show status like 'Slave_running'; set @@default_master_connection='other_connection'; show status like 'Slave_running';
@@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:
||Number of executed
||Number of executed
||Number of executed
||Number of executed
SHOW ALL SLAVES STATUS has the following new columns:
||Name of the master connection. This is the first variable.|
||State of SQL thread.|
||Number of retried transactions for this connection.|
||Max relay log size for this connection.|
||How many log entries the slave has executed.|
||How many heartbeats we have got from the master.|
||How often to request a heartbeat packet from the master (in seconds).|
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
When you are using multi source, the following new files are created:
||Contains the current master position for what's applied to in the slave. Extension is normally
||The relay-log name with a connection_name suffix. The xxxxx is the relay log number. This contains the replication data read from the master.|
|| Contains the name of the active
||Contains the current master position for the relay log. Extension is normally
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.
Instead of specifying names for
--log-bin-index you can just specify
--log-base-name and all the other variables are set with this as a prefix.
Master 'connection_name':. This makes it easy to see from where an error originated.
WARN_NO_MASTER_INFOnow includes connection_name.
log_warnings> 1 then you will get some information in the log about how the multi-master-info file is updated (mainly for debugging).
RESET SLAVEnow deletes all relay-log files.
replicate-...variables was added in MariaDB 10.0.2
replicate-...variables from the command line or in
my.cnffor a given connection by prefixing the variable with the connection name.
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.
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
One can also use this syntax to set
replicate-rewrite-db for a given connection.
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.
max_relay_log_size, which can't be changed at runtime.
max_binlog_sizeat startup if its value is 0.
Com_slave_startis replaced with Com_start_slave.
Com_slave_stopis replaced with Com_stop_slave.
FLUSH RELAY LOGSare not replicated anymore. This is not safe as connection names may be different on the slave.
© 2019 MariaDB
Licensed under the Creative Commons Attribution 3.0 Unported License and the GNU Free Documentation License.