Mysql – adding another table to existing MySQL replication scenario

MySQLmysqldumpreplication

I'm running a simple MySQL replication scenario MASTER ➙ BLACKHOLE ➙ SLAVE, replicating only a number of tables via replicate-do-table on BLACKHOLE and SLAVE.
Now I would like to add several more tables (that already exist and filled with data on MASTER) to existing replication, with minimum downtime on MASTER and SLAVE.
I have general understanding of steps to undertake in order to have a consistent snapshot (lock master tables, stop replication, dump&source etc), but this process includes 10+ steps and prone to human mistakes, especially if we set up more slaves in future.

Question: is there an easier option to add new table(s) to replication? Like using

mysqldump --master-data ...

and then somehow start replication of a particular table from a certain binlog position?

Thank you.

Best Answer

Are you using InnoDB? Xtrabackup will allow you to do "hot" or "online" copies of your running master.

Propagate the backup made via xtrabackup to the slave. There is no need to disrupt the relay (blackhole) server, just add the extra tables to be replicated to the relay server config before you "START SLAVE" on your slave.

Related Question