Mysql Replication from an existing slave

So you want to add another slave to an existing mysql replication environment?

There are multiple ways to do this.

First option, as mentioned in the mysql documentation:

Replication How to add additional slaves

This is the standard way most people follow. The issue with this is that you need to shutdown your mysql slave in this case. If it is the only slave present, it might not be feasible to do so.
You may use stop slave instead of a complete shutdown, but that might not be feasible as well. Your system probably cannot afford any replication lag.
Plus, the whole process is not straightforward and really prone to errors.

Another option is to use lvm snapshot to take backups:

Using lvm for mysql backup and replication setup

I've used this multiple times and it works like a charm. It is very fast to do with almost 0 downtime/lag. Make sure you copy the data to another server using rsync in a screen.

But what if you want to start partial replication? Say, you want to replicate only 2-3 tables from the entire mysql instance?

Both the methods mentioned here copy the entire mysql data files (/var/lib/mysql). You can obviously do so for partial replication as well and delete the unnecessary tables, but it is a really inefficient way of doing this.

There is a better way to do this.

Using mysqldump (for mysql >= 5.5.3):

Let us see the query to dump a single table from a single database:


mysqldump --single-transaction --flush-logs --dump-slave=2 --max_allowed_packet=512M -u root -p database_name table_name | gzip > dump_file_name.gz


Let us go through the options:
–single-transaction: This basically starts the dump process under a new transaction without blocking any other applications.

Read more: mysqldump: single-transaction

–flush-logs: Flushes the MySQL server log files before starting the dump.

Read more: mysqldump: flush-logs

–dump-slave: This is the heart of the query. It is used to print the binary log coordinates (file name and position) of the dumped slave's master in the dump file.
–dump-slave=1 prints the actual change master to statement in the sql dump.
Changing the value to 2 causes it to print the same but within a comment. Essentially, you can run it at your own convenience.
If you are replicating from a slave use –dump-slave. If you are running this query on the master itself, replace this with –master-data.
Read more: mysqldump: dump-slave

–max_allowed_packet: The maximum size of the buffer for client/server communication. The default is 24MB, the maximum is 1GB

gzip: Compress the data while being writhed into a dump file. You will see a good 70%~80% compression rate. This helps a lot when transferring files cross DC.

To run this for multiple tables use the –tables option. If you want to run it for one or more databases, use –databases option.
You can use this for full replication as well as long as you're using InnoDB tables.

Basically you can use any of the mysqldump flags as per your requirement.