Mysql – replicate slaves without a dump

MySQL

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?

Best 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.

Related Question