Diamond Notes

This tip is a follow-up to yesterday’s post about setting up a new slave server with a new master (one that has no current data on it). Many times you need to add a slave to a master server that already has data on it. This is a little trickier, but it is possible.

There are other ways of doing this. For me, this is typically the fastest way to do it and the one I use most often.

Assumptions I am making: your operating system is Linux and you are using what is called LVM (logical volume manager). The lvm programs are used to manage the partitions and filesystems on your server. You can use it to, among other things, resize partitions and most importantly, in this case, take “snapshots” of your partitions. WARNING: before performing this on production systems you NEED to research LVM and test it out on test servers.

step one - set up your slave server with the operating systems and MySQL. Your master server MUST have LVM on it (and I would recommend you set up LVM on the slave also).

step two - on the master you need to add the replication user:

GRANT REPLICATION SLAVE ON *.* TO ‘repl_user’@‘ip_of_new_slave_server’

IDENTIFIED BY ‘repl_user_password’;

step three - open up two terminal screens on the master server.

step four - Issue a ‘FLUSH TABLES WITH READ LOCK’ on the master server’s mysql client. This will block all database activity on the master server.

step five - As soon as the ‘FLUSH TABLES’ command returns issue a ‘SHOW MASTER STATUS’ command. You need to save the information returned as you will need it in step eleven.

step six - In the second terminal screen you need to create the lvm snapshot. This is done by running the following command:

lvcreate -s -n snap-data -L 10G /dev/vg0/data

The exact format will vary somewhat depending on your setup. In this case it creates a snapshot of the data partition that is 10 gigabytes in size. This means that up to 10 gigabytes of changes can be recorded before the snapshot fills up.

step seven - Now back in the original terminal screen (the one that you issued the ‘FLUSH TABLES’ and ‘SHOW MASTER STATUS’ commands) you need to execute an ‘UNLOCK TABLES’ command. This will return your database to normal activity. You need to execute steps four through seven as quickly as possible.

step eight - Now you need to mount the snapshot partition. Do something like this (as root):

mount /dev/vg0/snap-data /mnt

step nine - Now you rsync the files to the slave server:

rsync -Wav --delete --progress /mnt/mysql slave:/data/mysql

step ten - Start the mysqld daemon on the slave server. It might spit and sputter a bit as it is starting up, but only once have I had the daemon not start up (and that appeared to be a completely unrelated issue). In the last year I have probably done this procedure fifty times so it is well tested!

step eleven - Now it is time to tell the slave server about the master. You will also need the IP address of the master server. On Linux run the ifconfig command to determine the IP address. To put in the information on the slave server:

RESET SLAVE;

CHANGE MASTER TO master_log_file=‘master_log’,

master_log_pos=master_log_position, master_user=‘repl_user’,

master_password=‘repl_user_password’, master_host=‘ip_of_master’;

SHOW SLAVE STATUS;

step twelve - Cleanup!! Now you need to remove the snapshot partition you made on the master:

umount /mnt

lvremove /dev/vg0/snap-data

That’s it. A little more complicated. But worth it!!