Question :
I have setup a new slave in a set and I just want to allow it to naturally cach up to the master and start using the binary log once it has caught up.
However, I cannot seem to find anything on this, every method seems to require a pre-requisite dump of data. It would be a lot easier for me to just let the slave catch up naturally over a period of days.
Is there anyway I can do this or do I really have to take a full dump etc cetc?
Answer :
It’s pretty easy to create a slave either from the running master or slave.
Here Percona explains how to do it with XtraBackup http://www.percona.com/doc/percona-xtrabackup/2.1/howtos/setting_up_replication.html#adding-more-slaves-to-the-master
You can use this procedure with slight variation to add new slaves to a master. We will use Percona XtraBackup to clone an already configured slave. We will continue using the previous scenario for convenience but we will add TheNewSlave to the plot.
At TheSlave, do a full backup:
TheSlave$ innobackupex --user=yourDBuser --password=MaGiCiGaM /
--slave-info /path/to/backupdir
By using the –slave-info Percona XtraBackup creates additional file called xtrabackup_slave_info.
Apply the logs:
TheSlave$ innobackupex --apply-log --use-memory=2G /path/to/backupdir/$TIMESTAMP/
Copy the directory from the TheSlave to TheNewSlave (NOTE: Make sure mysqld is shut down on TheNewSlave before you copy the contents the snapshot into its datadir.):
rsync -avprP -e ssh /path/to/backupdir/$TIMESTAMP TheNewSlave:/path/to/mysql/datadir
Add additional grant on the master:
TheMaster|mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'$newslaveip'
IDENTIFIED BY '$slavepass';
Copy the configuration file from TheSlave:
TheNEWSlave$ scp user@TheSlave:/etc/mysql/my.cnf /etc/mysql/my.cnf
Make sure you change the server-id variable in /etc/mysql/my.cnf to 3 and disable the replication on start:
skip-slave-start
server-id=3
After setting server_id, start mysqld.
Fetch the master_log_file and master_log_pos from the file xtrabackup_slave_info, execute the statement for setting up the master and the log file for The NEW Slave:
TheNEWSlave|mysql> CHANGE MASTER TO
MASTER_HOST='$masterip',
MASTER_USER='repl',
MASTER_PASSWORD='$slavepass',
MASTER_LOG_FILE='TheMaster-bin.000001',
MASTER_LOG_POS=481;
and start the slave:
TheSlave|mysql> START SLAVE;
If both IO and SQL threads are running when you check the TheNewSlave, server is replicating TheMaster.