2

I already have replication set up for, say, dbname. I want to add a new replicated table. I created the tables on both master and slave. I entered replicate-do-table=dbname.tablename to my.cnf on slave.

To reload my.cnf the server needs to be restarted, and since lots of queries are coming I don't want to lose any of them.

Is there a way that I can add a new table to be replicated on the fly?

2 Answers 2

3

No, there is no way that you can add tables to replication on the fly.

You can read it in the docs:

Others, such as the --replicate-* options, can be set only when the slave server starts.

1

I beg to differ with aslisabanci - you can change replication filters on-the-fly using the CHANGE REPLICATION FILTER Statement as described in the official docs, just make sure to stop the slave before changing the rule and restart it afterwards. Even if you don't, MySQL will warn you to stop the slave first.

I literally just ran the following commands and it worked fine:

STOP SLAVE;
CHANGE REPLICATION FILTER REPLICATE_WILD_DO_TABLE = ('mana_user.privilege,mana_user.calendar');
START SLAVE;

Note that if you are adding one table to your list of tables, you must specify all tables in the statement that you wish to be replicated, not just the one table you want to append to the list.

I'm not sure what the statement "Others, such as the --replicate- options, can be set only when the slave server starts."* refers to, perhaps they meant that the commands can't be run while replication is running? This is true, the replication engine needs to be stopped for a few seconds while you execute the command but it will pick up where it left off once you restart it. The actual MySQL server does not need to be stopped.

Hint: make like easy for yourself and add the table to your replication filters before actually creating the table on master, this way the command to create the table will be replicated across, saving you the hassle of having to manually create it on the slave.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .