HOW TO START A SINGLE MULTI SOURCE REPLICATION WITH WINDOWS + MARIADB 10.0.0
来源:互联网 发布:hm淘宝旗舰店 编辑:程序博客网 时间:2024/06/11 03:26
呵呵 现在要做一个基于windows的mysql数据同步,但是mysql的MULTI 功能是在windows下不能用。(只能做到one master per slavery!)好蛋疼,于是查资料,问大神!诶!还是google NB 终于让我找到了--MARIADB 。哈哈 !MULTI SOURCE REPLICATION。查了查它可以做到多主从一的复制!哈哈不多说。看配置
serverid=1 :ip 192.168.1.68 (my.ini: server-id=1 binlog-do-db=t1 relay_log_space_limit=1024000000 log-bin=bin)
serverid=2 :ip 192.168.1.74 (my.ini: server-id=2 binlog-do-db=t2 relay_log_space_limit=1024000000 log-bin=bin)
serverid =3 (my.ini server-id=3)
重启服务
serverid 1、2: SHOW MASTER STATUS; 能看到一条数据 不是空的就行
serverid3:SET @@default_master_connection='t1';
CHANGE MASTER 't1' TO MASTER_HOST = '192.168.1.68',
MASTER_USER = 'root', MASTER_PASSWORD = 'root', MASTER_PORT = 3306;
SET @@default_master_connection='t2';
CHANGE MASTER 't2' TO MASTER_HOST = '192.168.1.74',
MASTER_USER = 'root', MASTER_PASSWORD = 'root', MASTER_PORT = 3307;
SHOW ALL SLAVES STATUS
START ALL SLAVES(stop ALL SLAVES;);
基本上不会出现问题 之后你可以在server3上看到 serverid 1、2的数据库t1,t2都跑到 serverid3上了,但是分成了2个库--t1,t2。
这个不是我想要的!我想要的是serverid 1、2 2个库合并到一个库。看他的官网说明。最后修改:
serverid=1 :ip 192.168.1.68 (my.ini: server-id=1 relay_log_space_limit=1024000000 log-bin=bin)
serverid=2 :ip 192.168.1.74 (my.ini: server-id=2 relay_log_space_limit=1024000000 log-bin=bin)
serverid =3 (my.ini server-id=3 replicate_rewrite_db=t2->tb replicate_rewrite_db=t1->tb)
重启服务 ok
更多配置自己看:
官网:https://kb.askmonty.org/it/la-replica-multi-source/
MariaDB starting with 10.0
Multi-source replication means that one server has many masters from which it replicates. This is a new feature in the upcoming MariaDB 10.0 release.
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:
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-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.
Max_relay_log_size
Max size of relay log. Is set at startup tomax_binlog_size
if 0Variablereplicate_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.Variablereplicate_do_table
Tells the slave to restrict replication to tables in the comma-separated listVariablereplicate_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.Variablereplicate_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.Variablereplicate_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.Variablereplicate_wild_ignore_table
Tells the slave thread to not replicate to the tables that match the given wildcard pattern.StatusSlave_heartbeat_period
How often to request a heartbeat packet from the master (in seconds).StatusSlave_received_heartbeats
How many heartbeats we have got from the master.StatusSlave_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@@default_master_connection
doesn't exists.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 SESSION
or GLOBAL
.
Note that the replicate_...
variables where 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.
New status variables:
Com_start_all_slaves
Number of executed START ALL SLAVES
commands.Com_start_slave
Number of executed START SLAVE
commands. This replacesCom_slave_start
.Com_stop_slave
Number of executed STOP SLAVE
commands. This replacesCom_slave_stop
.Com_stop_all_slaves
Number of executed STOP ALL SLAVES
commands.SHOW ALL SLAVES STATUS
has the following new columns:
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:
master-info-file
The master-info-file
(normally master.info
) with amulti-
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.
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 inmy.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
- 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!
Comments
HOW TO START A SINGLE MULTI SOURCE REPLICATION WITH WINDOWS + MARIADB 10.0.0
1)Install MariaDB 10.x
2)Open 3 MariaDB terminals
3)Run 3 MariaDB instances, example:
"C:/Program Files/MariaDB 10.0/bin/mysqld" --datadir="C:/Program Files/MariaDB 10.0/data1" --log-error="C:/Program Files/MariaDB 10.0/log/1.log" --port=3306 --server-id=1 --relay_log_space_limit=1024000000 --log-bin=bin1 --binlog-do-db=t1
"C:/Program Files/MariaDB 10.0/bin/mysqld" --datadir="C:/Program Files/MariaDB 10.0/data2" --log-error="C:/Program Files/MariaDB 10.0/log/2.log" --port=3307 --server-id=2 --relay_log_space_limit=1024000000 --log-bin=bin2 --binlog-do-db=t2
"C:/Program Files/MariaDB 10.0/bin/mysqld" --datadir="C:/Program Files/MariaDB 10.0/data3" --log-error="C:/Program Files/MariaDB 10.0/log/3.log" --port=3308 --server-id=3
Here server-id 1 and 2 are masters, and 3 is slave
4)Create database t1 in server-id=1, and t2 in server-id=2, like:
CREATE TABLE t1; CREATE TABLE t2;
5)Check if t1 and t2 exists in server-id=3 (slave), if not create it
6)Configure Masters in server-id=3:
SET @@default_master_connection='t1';
CHANGE MASTER 't1' TO MASTER_HOST = '127.0.0.1', MASTER_USER = 'user', MASTER_PASSWORD = 'password', MASTER_PORT = 3306;
SET @@default_master_connection='t2';
CHANGE MASTER 't2' TO MASTER_HOST = '127.0.0.1', MASTER_USER = 'user', MASTER_PASSWORD = 'password', MASTER_PORT = 3307;
7)Here Slave is up, now let's check status: (at server-id=3) SHOW ALL SLAVES STATUS you will see two connection (if everything is ok)
8)Check Masters: (at server-id=1 and 2)
SHOW MASTER STATUS;
you will see log files and positions
9)Start replications: (at server-id=3)
START ALL SLAVES;
10)TEST IT! =D (open server-id=1)
CREATE TABLE t (a int);
INSERT INTO t VALUES (0),(1),(2),(3);
11)CHECK REPLICATION (open server-id=3)
USE t1;
SELECT * FROM t;
you will see 0,1,2,3 rows! :D
12) BE HAPPY =D
- HOW TO START A SINGLE MULTI SOURCE REPLICATION WITH WINDOWS + MARIADB 10.0.0
- How to set up a multi-domain SSO demonstration for a quick start with CAS.
- How To Open Number Of Sites With A Single Click ?
- How to Delete a Windows Services with Command?
- How to Make a Single Page Website
- 论文阅读笔记之How to Keep a Knowledge Base Synchronized with Its Encyclopedia Source
- how to start a new goal
- How To Start a simple OAI Simulaton
- 多源复制(Multi-source Replication)
- How to create openstack single node with devstack
- How to contribute to open source with idea
- Unable to add a source with url
- How to get a 64-bit compiler under windows to use with matlab
- How to start CDE/JDS with xinit command
- How To Repair MySQL Replication
- Clustering/Session Replication HOW-TO
- PowerShell - How to enter a multi-line command
- how to begin with a new project
- dede采集正则过滤
- POJ 3608 Bridge Across Islands(旋转卡壳 求不相交凸包之间的最短距离)
- jvm调优(一) 概念
- Java异步通信
- poj 1458 hdoj 1159 Common Subsequence
- HOW TO START A SINGLE MULTI SOURCE REPLICATION WITH WINDOWS + MARIADB 10.0.0
- properties配置文件将中文转换为unicode
- 如何在VC下检测当前存在的串口及串口热拔插
- jvm调优(二)概念
- 浅谈ASP.NET中ViewState与ViewData的区别
- Windows下ora-01031:insufficient privileges
- GUI系统之SurfaceFlinger(14)handleTransaction
- thinkphp关联查询(多表查询)
- hdu 4604 Deque (二分法求最长单调子序列)