来自MySQL工作台的MySQL查询不会复制。

I am preparing to make some changes to a database I manage and was unsure that what I wanted to do would replicate properly so I ran some tests in a test environment and it turns out they will but only as long as I do not run the commands from the MySQL Workbench.

我准备做一些更改一个数据库管理和不确定,我想做复制正确所以我进行了几项测试,在测试环境中,事实证明他们将但只要我不从MySQL工作台运行的命令。

For example if have a database named db_test and a table in that database named test_a having only a single column id and I try to execute this from the workbench: INSERT INTO db_test.test_a (id) VALUES (114);

例如，如果有一个名为db_test的数据库和一个名为test_a的数据库中只有一个列id的表，我尝试从工作台执行这个操作:插入到db_test。test_a(id)值(114);

I get the expected row in the master database, but it never replicates to the slave.

我在主数据库中得到了预期的行，但它从不复制到奴隶。

When I perform a SHOW SLAVE STATUS - it shows everything is fine, and current. If I then use a different SQL client such as SequelPro and insert another row the same way (but obviously a different id) it shows in the master and replicates to the slave.

当我执行显示从属状态时——它显示一切正常，并且是当前的。如果我然后使用一个不同的SQL客户端(比如SequelPro)，并以相同的方式插入另一行(但显然是不同的id)，它将显示在master中，并复制到从行。

This has me baffled, and concerned as I want to understand what the difference is so I can avoid performing actions that never replicate.

这让我很困惑，也很担心，因为我想知道它们之间的区别是什么，这样我就可以避免执行从来没有重复过的动作。

1 个解决方案

#1

If you have set --replicate-do-db on the slave to filter replication for database db_test, replication is filtered based on the default database, so make sure that you issue USE db_test. Your client may be working differently in this manner, or you may be issuing different statements between clients.

如果您已经设置了-复制-do-db在从服务器上过滤数据库db_test的复制，那么将基于默认数据库对复制进行过滤，所以请确保您使用的是db_test。您的客户可能以不同的方式工作，或者您可能在客户之间发布不同的声明。

Using --replicate-do-db set to db_test on the slave, this will replicate:

在从服务器上使用-replicate-do-db设置为db_test，这会复制:

USE db_test;
INSERT INTO test_a (id) VALUES (114);

but this will not:

但这不会:

USE other_db;
INSERT INTO db_test.test_a (id) VALUES (114);

To get replication to work regardless of the current default database, use --replicate-wild-do-table to configure the database and table to replicate or don't filter at all.

为了让复制工作与当前的默认数据库无关，可以使用复制-wild-do表来配置数据库和表，以复制或根本不过滤。

Also, make sure that you are connected to the Master database server.

另外，请确保您已连接到主数据库服务器。

#1