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.

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);

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.

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

解决方案

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.

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

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.

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