MARIADB multi-source Replication (mariadb Multi-master replication)

Source: Internet
Author: User
Tags flush table name

MARIADB multi-source Replication (mariadb Multi-master replication)
Multi-master replication is added to the mariadb-10.0.

Modified syntax:
there is a corresponding connection_name for each replication thread, and Connection_Name is the value of the default_master_connection variable, if you want to manipulate the corresponding replication thread, This variable needs to be set to the name of the corresponding replication thread.
The Connection_Name value is any string that is less than 64 in length and is insensitive to capitalization. You need to keep the connection name as much as possible because it will be part of the file name.

Here are some new additions to the syntax:
focus on the connection_name, that is, in the previous syntax added connection_name, if not added Connection_Name, then the default is empty (").
Change MASTER [' connection_name '] ...
FLUSH RELAY LOGS [' Connection_Name ']
Master_pos_wait (..., [' Connection_Name '])
RESET SLAVE [' Connection_Name ']
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 fashioned connection is an empty string "and if you don't want to use it you can do without this connection."
You can use change master to create a new master connection.
You can use reset slave ' connect_name ' all; completely delete a connection.

Multi-source replication variable
new replication variable @ @default_master_connection Specifies that if you do not specify a connection, the link will be used by commands and variables. The
defaults to this value is "(Default connection name). The following replication variables are for local connections (in other words, they show the value of @ @default_master_connection).
We are working to localize important connections to the connection. The maximum value of the
type   name   description
variable  Max_relay_log_size  relay log. If it is 0, it will be set to the max_binlog_size size at boot time.
State  Slave_heartbeat_period  How long to request a heartbeat packet from master (in seconds).
Status  Slave_received_heartbeats  How many heartbeat packs we received from master. The
status  Slave_running  shows whether Slave is running. Yess indicates that the SQL and IO threads are active. No means any one of them is not running. "" indicates that @ @default_master_connection does not exist.
Variables  Sql_slave_skip_counter  Replication Log to ignore the number of entries (mainly used in the case of errors in the log).
You can access all of the above variables with session or GLOBAL.
Note that, in contrast to MySQL, all variables always display the correct valid value!
For example:
SET @ @default_master_connection = ";
Show status like ' Slave_running ';
SET @ @default_master_ connection= ' other_connection ';
Show status like ' slave_running ';

If the @ @default_master_connection contains a name that does not exist, you will receive a warning.
All other master-related variables are global and affect the "" connection or all of the connections. For example, Slave_retried_transactions now displays the number of retry transactions for all Slave.

Newly added state variables:
Name Description
Com_start_all_slaves the number of times the start all slaves command was executed.
Com_start_slave the number of times the start slave command was executed. Replaced the Com_slave_start.
Com_stop_slave the number of times the Stop slave command was executed. Replaced the com_slave_stop.
Com_stop_all_slaves the number of execution stop all slaves commands

Show all slaves STATUS has the following new columns:
Name Description
Connection_Name Master's connection name. This is the first variable
Slave_sql_state the state of the SQL thread
Retried_transactions the number of times the transaction was retried for this connection.
Max_relay_log_size the size of the maximum relay log for this connection.
Executed_log_entries Slave has pointed to the number of log entries.
How many heartbeats did Slave_received_heartbeats get from master?
How often slave_heartbeat_period requests a heartbeat packet (in seconds) from master.

New file
the basic guidelines for new files used by multiple sources are: they have a name similar to the original relay log file prefixed by the connection name before the extension.
The main exception is that the file master-info-file that holds all the connection names is simply named Master-info-file with the multi-prefix.

When you use multiple source replication, the following files will be created:
Name Description
The Multi-master-info-file master-info-file (usually is prefixed with the multi-prefix. This is all the master connection information in use.
The master-info-file-connection_name.extension contains the location of the current master application to the slave. The extension is generally. info
The Relay-log-connection_name.xxxxx Relay-log has a connection_name prefix. XXXXX is the number of relay log. This holds the copy data that is read from master.
Relay-log-index-connection_name.extension contains the names of the available relay-log-connection_name.xxxxx files. The extension is typically. Index
Relay-log-info-file-connection_name.extension contains the location of the current master in the relay log. The extension is generally. info

When these files are created, the connection name is converted to lowercase, and all of the special characters in it are converted, as are the special characters in the MySQL table name. This is done to make it easier for file names to be ported on different systems.
You only need to specify –log-base-name, and all other variables will use this as a prefix instead of specifying –relay-log,–relay-log-index,–general-log,–slow-log,–log-bin for MYSQLD , –log-bin-index these names.

Other matters
All slave error messages are added with connection name and then written to the error log.
Er_master_info and Warn_no_master_info will now add Connection_Name.
There are no conflicting solutions, and we assume that there is no conflict between all of the master.
All executed commands are stored in the normal binary log.
If you have server variable log_warnings>1, you will receive information about Multi-master-info file updates
show slave status; the first line to see is increased, called connection_name.
The Reset slave command will now delete all relay-log files.

Typical use Cases
consolidate multiple master data onto a single slave, aspect query analysis.
Consolidate data from multiple Mariadb/mysql servers into one slave for easy backup.

Restricted matters
a slave can have up to 64 master
Each active connection creates two threads (same as MARIADB replication)
You need to verify that all master needs to have different server-id.
Max_relay_log_size modification is not effective after startup.
The Innodb-recovery-update-relay-log value is in effect for the default replication connection, which is the XTRADB's new location number to store the relay log. However, there are security risks in this scheme, we do not recommend using
Slave_net_timeout is valid for all parameters, we do not check if it is less than slave_heartbeat_period, because it is not particularly meaningful for multiple master replications.

Features to be completed
Multi-Source does not yet support semisync, and we will fix it in the next release version.
All bugs about multi-source can be found here requestid=11400
Allow multithreaded replication

is incompatible with Mariadb/mysql 5.5
max_relay_log_size is now (almost) a normal variable and not automatical Ly changed if max_binlog_size is changed. To keep things compatible with the old config files, we have it to max_binlog_size at startup if it value is 0.
You can now access replication variables that depend in the active connection with either GLOBAL or session.
We only write information about relay log positions to recovery if Innodb-recovery-update-relay-log is set.
Slave_retried_transaction 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 isn't safe as connection names may was different on the slave.

The MySQL library needs to be filtered out and not replicated.
Semi-synchronous (Semi-sync) is temporarily unavailable.
If you need to sql_slave_skip_counter, you need to first make a channel set @ @default_master_connection = ' connection_name '

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.