多源(主)复制
Contents
MariaDB starting with 10.0
多源复制意味着一个服务器能从多个从服务器上复制。这是即将发布的MariaDB 10.0的一个新特性
新语法
在命令指定你想起作用的master的连接名或者设置@@default_master_connection
变量为你想生效的连接。
连接名可以是任意的不超过64个字符的字符串.连接名对比的时候不考虑大小写(大小写不敏感)。 你应当保持连接名段(使用固定的连接名),因为他将用来作relay和master信息的index文件的前缀。
新语法介绍如何操作多个连接:
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 ...
原始的老式的连接是一个空字符串''
。你如果不想用的话可以不用这个连接。
用CHANGE MASTER来创建新的master连接.
用 RESET SLAVE "connection_name" ALL 来永久的删除连接。
多源复制的变量
新复制变量 @@default_master_connection
指定了如果你不指定一个连接的话那个链接将会被命令和变量使用。默认这个值是 ''
(默认连接名)。下面的复制变量是针对局部的连接的(换句话说,他们显示了 @@default_master_connection
的值)。我们正致力于将重要的对于连接局部化。
类型 | 名称 | 描述 |
---|---|---|
变量 | Max_relay_log_size | relay log的最大值. 如果是0的话,那么在启动的时候就会被设置成 max_binlog_size 的大小 |
状态 | Slave_heartbeat_period | 多久从master请求一个心跳包 (以秒计算). |
状态 | Slave_received_heartbeats | 我们从master收到了多少个心跳包. |
状态 | Slave_running | 显示slave是否正在运行。YESS表示 sql 和 IO 线程是活动的。No表示其中任意一个没在运行中。""表示 @@default_master_connection 不存在。 |
变量 | Sql_slave_skip_counter | 复制日志要忽略多少个条目(主要用在日志中有错误的情况下)。 |
你可以用 SESSION
或者是 GLOBAL
访问以上所有的变量。
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.
New status variables:
Name | Description |
---|---|
Com_start_all_slaves | Number of executed START ALL SLAVES commands. |
Com_start_slave | Number of executed START SLAVE commands. This replaces Com_slave_start . |
Com_stop_slave | Number of executed STOP SLAVE commands. This replaces Com_slave_stop . |
Com_stop_all_slaves | Number of executed STOP ALL SLAVES commands. |
SHOW ALL SLAVES STATUS
has the following new columns:
Name | Description |
---|---|
Connection_name | Name of the master connection. This is the first variable. |
Slave_SQL_State | State of SQL thread. |
Retried_transactions | Number of retried transactions for this connection. |
Max_relay_log_size | Max relay log size for this connection. |
Executed_log_entries | How many log entries the slave has executed. |
Slave_received_heartbeats | How many heartbeats we have got from the master. |
Slave_heartbeat_period | How 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:
Name | Description |
---|---|
multi-master-info-file | The master-info-file (normally master.info ) with a multi- prefix. This contains all master connections in use. |
master-info-file -connection_name.extension | Contains the current master position for what's applied to in the slave. Extension is normally .info |
relay-log -connection_name.xxxxx | 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. |
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.extension | Contains 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
andWARN_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.
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. - One can change
max_relay_log_size
for any active connection, but new connections will always use the server startup value formax_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 thanSlave_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. Will be fixed for next release.
- All open tasks and known bugs for multi-source can be found here.
- allow to replicate 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 ifmax_binlog_size
is changed. To keep things compatible with old config files, we set it tomax_binlog_size
at startup if its value is 0.- You can now access replication variables that depend on the active connection with either
GLOBAL
orSESSION
. - 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 over all slaves.- The status variable
Com_slave_start
is replaced withCom_start_slave
. - The status variable
Com_slave_stop
is replaced withCom_stop_slave
. FLUSH RELAY LOGS
are not replicated anymore. This is not safe as connection names may be different on the slave.
See also:
- The work in MariaDB is based on the project description at MDEV-253.
- The original code base comes from Taobao, developed by Peng Lixun. A big thanks to them for this important feature!