I haven't been able to find anything so I'm sure the answer is no, what are you, a noob? but I feel the need to ask anyway 🙂
Is there a simple utility that would allow a new slave server to be brought up on an existing master without the need to perform a dump on the master?
I've set up slaves in the traditional manner several times so I'm not without knowledge of the process, but I'm curious if anyone has felt the same as I do, that there must be an easier way.
I would expect such a utility would use something similar to Percona's Xtrabackup, but instead of outputting to a dump file, it would stream the output directly to the slave, and then automatically enable the slave relationship after the feed has ended.
Is this realistic?
I noticed the streaming option in Xtrabackup, but couldn't find examples outside of its ability to just save a tar on another server, which isn't what I was looking for. I want a solution that could stream directly into the destination DB without the requirement of working with a dump file at all. This is particularly handy when working with large datasets.
Best Answer
Something close to what you're asking can be done with
mysqldump
. You can stream its output directly to the slave.mysqldump
will addCHANGE MASTER TO
to the output. However, it doesn't includeMASTER_HOST
,MASTER_USER
andMASTER_PASSWORD
. It has to be configured separately.Additional notes:
-h
option inmysqldump
andmysql
. See mysql Options.mysqldump -h master
, the dump will come from the master. Respectively, the binlog coordinates will point to the binlog on the master.--single-transaction
is specified which negates the requirement of a table lock for a consistent dump.--quick
will ensure large tables are read one row at a time rather than buffering the entire row set in memory, which is more conducive to this type of streaming dump.