Diamond Notes

I just wanted to write a quick post about something I did earlier today. For many of you this will be no trick, but for new DBA’s it is something you probably haven’t run across before.

So here is the problem.  I have a master server that has a slave currently replicating from it. I need to add another slave to the mix for an upgrade we are performing.  I can snapshot the data from the master using LVM snapshots and rsync it across, but even then you have to lock the tables for a few seconds. Not something you want to do on a production master if you can avoid it.

Instead I use the current slave to bring the new slave online. Here are the steps:

Assuming database1-a is current master, database1-b is current slave and database1-c is the going to be the new slave:

• stop the slave thread from running on database1-b

  stop slave

• execute table lock on database1-b

  flush tables with READ lock;

• assess free lvm space with the pvs command

• create snapshot partition on database1-b

  lvcreate -s -n snap-data -L 10G /dev/database-1b/data

• show slave status on database1-b

  SHOW slave status;

• release table lock

  unlock tables;

• restart slave thread on database1-b

  start slave;

• mount new snapshot

  mount /dev/database1-a/snap-data /mnt

• rsync from database1-b to database1-c

  rsync -Wav --delete --progress /mnt/mysql/ database1-c:/data/mysql

• get mysql up and running on database1-c

  /etc/init.d/mysql start

• configure database1-c to point to the new master as follows

>reset slave;

>change master to master_log_file= ‘mysql-bin.160150′ ,
master_log_pos= “current position as of snapshot”, master_user= ‘replication-user’ ,
master_password= ‘master-password’ , master_host= ‘IP OF database1-a’;

All of this information comes from the show slave status command we issued earlier.

• start up the slave on database1-c

  start slave;

The important thing here is that we use the log position shown in the slave status command and the master_host parameter points to database1-a … NOT … database1-b which is where we got the data from.

Hope this is useful to someone.