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

Source: Internet
Author: User

The following is a look at mariadb multi-source replication (mariadb Multi-master replication) example, I hope to help you.

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

Modified syntax:
For each replication thread there is a corresponding connection_name, 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 value of Connection_Name is any string that is less than 64 in length and is not case sensitive. You need to keep the connection name as fixed as possible, as it will be part of the file name.

Here are some new additions to the syntax:
Focus on the main 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
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 it without this connection."
You can use change master to create a new master connection.
You can use the Reset slave ' connect_name ' all; completely delete a connection.

Multi-source copy variable
new copy variable @ @default_master_connection Specifies that if you don't specify a connection, that link will be used by commands and variables. The
Default value is "(the 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 the important for the connection.
Type   name   Description
Variable  Max_relay_log_size  maximum value of relay log. If it is 0, then it will be set to the size of max_binlog_size when it is started.
Status  Slave_heartbeat_period  How long to request a heartbeat packet (in seconds) from master.
Status  Slave_received_heartbeats  How many heartbeat packs we received from master.
Status  Slave_running  Show whether Slave is running. Yess indicates that the SQL and IO threads are active. No indicates that any one of them is not in operation. "" indicates that the @ @default_master_connection does not exist.
Variables  Sql_slave_skip_counter  How many entries will be ignored by the replication log (mainly in case of errors in the log).
You can use the SESSION or GLOBAL to access all of the above variables.
Note that, in contrast to MySQL, all variables always show the correct valid values!
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 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 times the stop all slaves command was executed

The SHOW all slaves STATUS has the following new columns:
Name Description
The connection name of the Connection_Name master. This is the first variable
Slave_sql_state Status of SQL threads
Retried_transactions the number of times this connection retries the transaction.
Max_relay_log_size the maximum size of the relay log for this connection.
Executed_log_entries the number of log entries that the slave has pointed to.
Slave_received_heartbeats How many heartbeats were received from master.
Slave_heartbeat_period how often to request a heartbeat packet (in seconds) from master.

New file
The basic guidelines for new files that are used by multi-source replication are: they have names that are similar to the original trunk log files that were prefixed with the name of the connection before the extension.
The main exception is that the file that holds all the concatenated names Master-info-file is simply named Master-info-file with the multi-prefix.

When you use multi-source replication, the following files will be created:
Name Description
The Multi-master-info-file master-info-file (typically is prefixed with the multi-. This is all the master connection information in use.
The master-info-file-connection_name.extension contains the location where the current master is applied to the slave. The extension is typically. info
Relay-log-connection_name.xxxxx Relay-log has a connection_name prefix. XXXXX is the number of the relay log. This holds the copied data that is read from master.
Relay-log-index-connection_name.extension contains the name of the available relay-log-connection_name.xxxxx file. 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 typically. info

When these files are created, the connection name is converted to lowercase, and all of the special characters are converted, just as the special characters in the MySQL table name are converted. This is done to facilitate porting of file names to 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 the names of these.

Other matters
All slave error messages are added with connection name and are then written to error log.
Er_master_info and Warn_no_master_info will now add Connection_Name
There is no conflict resolution, 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, then you will receive some information about the Multi-master-info file update
show slave status; the first line of sight is increased, called connection_name.
The Reset slave command now deletes all relay-log files.

Typical use Cases
Consolidate multiple master data into one slave, and query analytics.
Consolidate data from multiple Mariadb/mysql servers into one slave for easy backup.

Restricted matters
A slave can have a maximum of 64 master
Each active connection creates two threads (same as MARIADB replication)
You need to make sure that all master needs to have different server-id.
Max_relay_log_size after the start of the modification is not effective.
The Innodb-recovery-update-relay-log value is in effect for the default replication connection, which is the XTRADB's new location number used to store the relay log. However, this scheme has security implications and we do not recommend using
Slave_net_timeout is valid for all parameters, we do not check whether it is less than slave_heartbeat_period because it is not particularly meaningful for multi-master replication.

Features to be completed
Multi-Source is not currently supported for Semisync, we will fix it in the next release release.
All bugs about multi-source can be found here requestid=11400
Allow multithreaded replication

incompatible 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 the old config files, we set it to max_binlog_size at startup if the IT value is 0.
You can now access replication variables this 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_transaction now shows the total count of retried transactions through all slaves. The
the status variable Com_slave_start is replaced with Com_start_slave. The
the status variable com_slave_stop is replaced with Com_stop_slave.
FLUSH RELAY LOGS is not replicated anymore. This isn't safe as connection names may be different on the slave.

MySQL library needs to be filtered out and cannot be copied.
Half-sync (Semi-sync) is temporarily unavailable.
If you need sql_slave_skip_counter, you need to set a channel set @ @default_master_connection = ' connection_name '

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

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.