How to replicate particular table in MySQL master-slave configuration?

Question

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.

Answer 1

To replicate a specific table in MySQL Master-Slave replication, you can use the following steps:

1. Ensure that you have already set up the Master-Slave replication between the master and slave servers. The replication process should be running and synchronized up to the point where you want to replicate the particular table.

2. On the master server, create a new replication user with appropriate permissions to access and replicate the specific table. You can use the following command:

   CREATE USER 'replication_user'@'slave_server_ip' IDENTIFIED BY 'password';
   GRANT REPLICATION SLAVE ON *.* TO 'replication_user'@'slave_server_ip';
   FLUSH PRIVILEGES;
   

   Replace 'replication_user' with the desired username, 'slave_server_ip' with the IP address or hostname of the slave server, and 'password' with a secure password for the replication user.

3. On the master server, identify the specific table you want to replicate. Let's assume the table is called your_table.

4. On the master server, open the MySQL configuration file (my.cnf or my.ini) and locate the [mysqld] section. Add the following configuration directive to enable table-level replication:

   replicate-do-table = your_database.your_table
   

   Replace 'your_database' with the name of the database containing the table, and 'your_table' with the name of the table you want to replicate.

5. Restart the MySQL service on the master server to apply the configuration changes.

6. Wait for the master and slave servers to synchronize the changes. You can monitor the replication status using the SHOW SLAVE STATUS command on the slave server. Ensure that the Slave_IO_Running and Slave_SQL_Running fields are both set to Yes.

By following these steps, only the specified table (your_table) will be replicated from the master server to the slave server in the MySQL Master-Slave replication setup.

Answer 2

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.