Adding a new MySQL slave on EC2

There are a few posts around for doing this easily, but this has just worked nicely for me. This was done between two instances on Amazon EC2 cloud.

On the existing slave

(in mysql)
SLAVE STOP;
SLAVE SHOW STATUS\G; # Note the result

(in bash)
sudo service mysql stop
sudo -i 
cd /var/lib
scp -i /home/ubuntu/ec2.pem -r mysql ubuntu@{new-DB-ip}:
exit

On the new DB

cd
sudo chown mysql:mysql mysql
cd mysql
rm master.info relay-log.info {and any old relay-bin files}
chown root:root mysql_upgrade_info
sudo rm -rf /var/lib/mysql
cd ..
sudo mv mysql /var/lib/
sudo vi /etc/mysql/my.cnf
{uncomment and increment server-id}
sudo service mysql start

(in mysql)
SLAVE RESET
(using the existing slave details)
CHANGE MASTER TO master_host='[master host]',master_user='[slave username]',master_password='[slave password]',master_log_file='[relay_master_log_file]',master_log_pos=[exec_master_log_pos];
SLAVE START;
SLAVE SHOW STATUS\G;
(should show replicating, and if repeated it should show decreasing number of seconds, or 0, behind master)

Back to existing slave

sudo service mysql start

(in mysql)
SLAVE START;
SLAVE SHOW STATUS\G;
(should show replicating, and if repeated it should show decreasing number of seconds, or 0, behind master)

Instead of copying the whole mysql dir over, it is possible to do a mysqldump, but I chose this route today and it worked fine.

Leave a Comment


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