复制重写数据库是否具有任何实际应用程序？ [英] Does replicate-rewrite-db have any real world application?

问题描述

我一直在尝试在两个MySQL服务器之间进行主-主复制，但是使用不同的数据库名称，并且遇到了一些绊脚石。看来，尽管UPDATE可以按预期正常工作，但INSERT却不能。

I've been experimenting with master-master replication between two MySQL servers but with different database names and I've come accross a bit of a stumbling block. It appears that although UPDATEs work correctly as expected, INSERTs do not.

虽然我会说这是一个错误，或者至少是我的设置有问题，但似乎MySQL表示这是一项功能（此处和此处）。

While I would say that this is a bug or at least a problem with my setup, it appears that MySQL says that this is a feature (here and here).

如果这样，如MySQL所说，它只能在默认数据库上运行，那么此设置如何具有实际的实际用途？

If then, as MySQL say, it only works on the default database, then how can this setting have a practical real world purpose?

我错过了什么吗？

请参考my.cnf中的相关部分。我一直在通过在phpMyAdmin中进行插入和更新（如果有任何区别）进行测试。

For reference the relevant parts in my.cnf. I've been testing this by doing inserts and updates from within phpMyAdmin if that makes any difference.

master

log-bin=mysql-bin
binlog_do_db=db1

从设备

replicate-rewrite-db=db1->db2
replicate-wild-do-table=db2.table1

推荐答案

复制-rewrite-db 的用法与其他复制选项相同。 Replication-rewrite-db 不仅可在默认数据库上运行，而且还可以： replicate-do-db，replicate-ignore-db，binlog- do-db和binlog-ignore-db 。请参考此和此。

Replication-rewrite-db usage is same as other replication options. Not only Replication-rewrite-db works on the default database, but these also: replicate-do-db, replicate-ignore-db, binlog-do-db and binlog-ignore-db. Refer this and this.

这是现实生活中的用途，否则MySQL不会实现此选项。而且它仅适用于默认数据库因为 -

There are real world purpose, else MySQL wouldn't have implemented this option. And it works only on default database because-

仅检查默认数据库行为的主要原因是
，仅凭语句就很难知道是否应该复制
（例如，如果您正在使用跨
多个数据库运行的多表
DELETE语句或多表UPDATE语句）。如果不需要，仅检查默认的
数据库而不是所有数据库也更快。

The main reason for this "check just the default database" behavior is that it is difficult from the statement alone to know whether it should be replicated (for example, if you are using multiple-table DELETE statements or multiple-table UPDATE statements that act across multiple databases). It is also faster to check only the default database rather than all databases if there is no need.

您还应该了解复制规则。从此处。

You should also know the replication rules. From here.

我通过发出 INSERT，DELETE和UPDATE 测试了phpmyadmin，并注意到了（通过启用 general_query_log ）发出 INIT DB'db_name'（ mysql_select_db（） API调用。）

I tested phpmyadmin by issuing INSERT, DELETE and UPDATE and noticed(By enabling general_query_log) that it issues INIT DB 'db_name'('Init DB' is logged for the mysql_select_db() API call).

例如：

Init DB sakila
1 Query INSERT INTO `sakila`.`actor_info` (`actor_id`, `first_name`, `last_name`, `film_info`) VALUES ('1235', 'abc', 'efg', NULL)

因此，最终复制应该不会中断，因为phpmyadmin通过在每次查询之前执行USE db来正确执行复制。

So ultimately the replication shouldn't break as phpmyadmin does it correctly by executing USE db before every query.

这篇关于复制重写数据库是否具有任何实际应用程序？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！