mysql - 将多个数据库复制到单个数据库，反之亦然

Question

这是我的主主复制场景

-------------------
| Master/Slave 1  |
| Database = MainDB |
-------------------
/\
|
|
|
\/
-------------------
| Master/Slave 2  |
| Database = DB1  |
| Database = DB2  |
| Database = DB3  |
-------------------

这是我的 Master/Slave1 配置:

[mysqld]
binlog_format                   = MIXED
user                            = mysql
port                            = 3306
socket                          = /tmp/mysql.sock
#bind-address                    = 127.0.0.1
basedir                         = /usr/local
datadir                         = /var/db/mysql
tmpdir                          = /var/db/mysql_tmpdir
slave-load-tmpdir               = /var/db/mysql_tmpdir
secure-file-priv                = /var/db/mysql_secure
log-bin                         = MainDB.log
replicate-do-db                 = MainDB
replicate-rewrite-db            = "DB1->MainDB"
replicate-rewrite-db            = "DB2->MainDB"
replicate-rewrite-db            = "DB3->MainDB"
replicate-rewrite-db            = "DB4->MainDB"
log-output                      = TABLE
master-info-repository          = TABLE
relay-log-info-repository       = TABLE
relay-log-recovery              = 1
slow-query-log                  = 1
server-id                       = 1

和我的 Master/Slave2 配置:

[mysqld]
binlog_format                   = mixed
user                            = mysql
port                            = 3306
socket                          = /tmp/mysql.sock
#bind-address                    = 127.0.0.1
basedir                         = /usr/local
datadir                         = /var/db/mysql
tmpdir                          = /var/db/mysql_tmpdir
slave-load-tmpdir               = /var/db/mysql_tmpdir
secure-file-priv                = /var/db/mysql_secure
log-bin                         = mysql-bin
log-output                      = TABLE
master-info-repository          = TABLE
relay-log-info-repository       = TABLE
relay-log-recovery              = 1
slow-query-log                  = 1
server-id                       = 2
log_bin                         = cloud.log
binlog-do-db                    = DB1
binlog-do-db                    = DB2
binlog-do-db                    = DB3
binlog-do-db                    = DB4

使用此配置，我可以从 Master/slave2 复制到 Master/slave1，但不能反过来复制。云如何更改 my.cnf 以便两者一起复制？

Answer 1

(1)If a master server does not write a statement to its binary log, the statement is not replicated. If the server does log the statement, the statement is sent to all slaves and each slave determines whether to execute it or ignore it.

Master/Slave1 收到一个 binlog，其中包含来自 Master/Slave2 的所有 DB%，这样他就可以将每个 DB 重写到 <强>主数据库 。 (使用这个 replicate-rewrite-db 甚至这个选项对我来说有点危险，你必须小心，否则它会打破这个 option_mysqld_replicate-rewrite-db )。

首先，在master master中我认为你应该启用登录slave的选项log_slave_updates因此另一个从站“主站”可以将语句存储在二进制日志(1)中。也检查一下以了解 how Servers Evaluate Replication Filtering Rules

另一方面，Master/Slave2 收到一个包含一个数据库 MainDB 的 binglog，并且您编写的任何内容都无法帮助将一部分转移到另一个数据库。或者将所有内容重写为一个。

On the master, you can control which databases to log changes for by using the --binlog-do-db and --binlog-ignore-db options to control binary logging.

所以你必须在Master/Slave1中添加这个选项来告诉他记录MainDB的更改，然后在Master/Slave2中添加这个选项> 您指定要重写哪个数据库或要在哪个数据库中复制哪些表