MySQL Replication – Creating Additional Slaves.

So you have MySQL Replication setup and working and now you want to create more slaves to your existing deployment; these are the steps that have worked for me:

Here Slave One is the slave that is currently in production and Slave Two is the new slave you want to populate and put in to production.

  • On Slave One issue the command STOP SLAVE;
  • In another terminal on Slave One tar up the MySQL data dir.; tar czf mysql.tgz /var/lib/mysql .
  • After the tar finishes go ahead and start the slave process back up on Slave One; START SLAVE;
  • On Slave Two un-tar the tarball; tar -C / -xzf mysql.tgz .
  • Edit Slave Two’s mysql.cnf to be similar to Slave One’s, but make sure to change the server-id variable.
  • Start the MySQL daemon on Slave Two, and issue the following commands: STOP SLAVE; RESET SLAVE;
  • Now issue a CHANGE MASTER filling in the correct values for your deployment; most of the info. can be found by issuing a SHOW SLAVE STATUS\G; on Slave One.

mysql> CHANGE MASTER TO
-> MASTER_HOST=’sql_host’,
-> MASTER_USER=’replication_user’,
-> MASTER_PASSWORD=’replication_password’,
-> MASTER_LOG_FILE=’update_log.000007′,
-> MASTER_LOG_POS=560;

  • Finally issue a START SLAVE; on Slave Two.

That’s it; you can issue a SHOW SLAVE STATUS\G; on your new slave and see how its health is. This has worked many times for me; the only time I had a problem was when the InnoDB files were in a weird state on the slave I was taring, in which case I waited a bit and re-tarred the MySQL data directory.

One thought on “MySQL Replication – Creating Additional Slaves.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>