Mysql – How to replicate particular table in MySQL master-slave configuration

MySQLmysql-5.1replicationtable

How do I replicate a particular table in a MySQL master-slave configuration?
I have tried replicate-do-table and replicate-wild-do-table.
I tried in my.ini file; is there any alternative to replicate without using replicate-do-table and replicate-wild-do-table?
I need a binary log statement to replicate a single table.

Best Answer

The only other way would be at the application level but it will be a little messy.

TECHNIQUE #1

In your application, you can prevent replication of an INSERT of any table as follows:

SET sql_log_bin = 0;
INSERT INTO ...
SET sql_log_bin = 1;

You would surround SQL statement or transaction with SET sql_log_bin = 0; and SET sql_log_bin = 1;.

TECHNIQUE #2

Another way (less code editing) would be to run SET sql_log_bin = 0; as the first statement after establishing a persistent DB Connection.

You would then need to enable binary logging for the table you want replicated like this:

SET sql_log_bin = 1;
INSERT INTO ...
SET sql_log_bin = 0;

EPILOGUE

Obviously, TECHNIQUE #2 would be best. However, you would still need to deploy the code changes for this. You must make sure you have persistent DB Connections. You would also need to test this if you are using Connection Pooling.

UPDATE 2019-06-01 21:57 EDT

replicate-do-table must be setup on a slave, not a master.

In your particular situation, there is just one major problem: This option was first created in MySQL 5.6. It is also available in MySQL 5.7 and MySQL 8.0.

You had posted the same question in another post (which I closed). In that post, you specified MySQL 5.1. replicate-do-table does not exist in MySQL 5.1 or MySQL 5.5. This make my somewhat convoluted answer more applicable because MySQL 5.1 does not have this option.

You could just update Master and Slave to MySQL 5.6 and then setup the replication-do-table on the slave to your heart's content.

CAUTION

You may want to not use such filtering if you only have one slave. The use of only one slave makes it virtually impossible to perform any kind of point-in-time recovery using a slave. See Percona Blog "Why MySQL’s binlog-do-db option is dangerous" on this. Even though the Blog in 10 years old, it still applies.

If you do upgrade Master and Slave to MySQL 5.6, get a second slave. Setup one slave with no filters and the second slave with replication-do-table.

If you cannot upgrade MySQL, then you must go with the application-level techniques I first suggested. At least it will allow Master and Slave to be identical in data content.

Upgrading to MySQL 5.6 is actually the best option.

Related Question