How to setup mysql replication with virtually no downtime (without locking tables, without lvm)

Update 1: This is not a copy/paste material. You must understand what you do, otherwise you may lose your data.

Prerequisites:

– you have to understand mysql replication principles.
– Operating system – linux.
– Not working with openvz virtual private servers.

So, you decide to initiate a master-slave replication. The official way of doing this implies some downtime for taking the snapshot, whatever your way of taking snapshot is (mysqldump, tar or rsync). In this article I’ll explain how to take a consistent snapshot of the mysql datadir, while mysql is running and making various inserts. This snapshot can be transferred to the slave to set up the replication.

Steps:

1. Configure master for replication. While mysql is running, edit the /etc/my.cnf file on the master. Add the following to [mysqld] section:

log-bin=mysql-bin
server-id=1

And be sure that you comment the line:

# bind-address           = 127.0.0.1

so that mysql will listen on all interfaces.

Login to mysql as root and execute the following:

GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.1.7' IDENTIFIED BY 'slavepass';

where 192.168.1.7 is the IP address of the slave server.

2. Install R1soft linux hot copy on the master server. After installing, execute:

hcp-setup --get-module

No reboot is required.

This is the tool that we will use to create a hot copy of the filesystem containing the mysql datadir (/var/lib/mysql). Operations on the original filesystem will not affect the snapshot. And the snapshot does not take any additional space but it preserves the changes made to the original system for the period being hot mounted.

3. Shutdown mysql on master and start the hcp mount:

/etc/init.d/mysql stop
# considering the partition mounted on /var is /dev/sda7:
hcp -o /dev/sda7
# look for the phrase :
Hot Copy mounted at: /var/hotcopy/sda7_hcp1
# restart mysql:
/etc/init.d/mysql start

That is all downtime needed for setting up the replication. It should not take more than 10 seconds.

At this point you have a snapshot of the /dev/sda7 filesystem at the time mysql was stopped. You can see the files in /var/hotcopy/sda7_hcp1. This is a virtual folder, its contents will not survive a restart for example. In the original /var/lib/mysql folder you will see that mysql-bin.000001 and mysql-bin.index files were created. These are needed for replication. They are not present in /var/hotcopy/sda7_hcp1 though.

4. On the slave server. Remove th ecurrent /var/lib/mysql (this will wipe current databases on the slave):

/etc/init.d/mysql stop
rm -rf /var/lib/mysql
# sync the snapshot from the master server:
rsync -avz root@172.16.220.1:/var/hotcopy/sda7_hcp1/lib/mysql /var/lib/

5. When the sync in point 4 finishes, umount the snapshot on the master:

hcp -r /dev/hcp1

6. Configure replication on the slave server:

vi /etc/mysql/my.cnf
[mysqld]
# add the following settings. Change master-host to the master ip, master-user to the replication user and master-password to the replication user password.
server-id=2
master-host=172.16.220.1
master-user='repl'
master-password='slavepass'
master-connect-retry=60
log-bin=mysql-bin

7. Start mysql on the slave server. Check replication status:

/etc/init.d/mysql start
mysql
mysql> show slave status\G
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 278
               Relay_Log_File: mysqld-relay-bin.000002
                Relay_Log_Pos: 423
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

The last 2 lines confirm replication is functioning correctly.

References:

MySQL online manual – http://dev.mysql.com/doc/refman/5.1/en/replication.html
Howtoforge tutorial – http://www.howtoforge.com/mysql_database_replication
SoftwareProjects tutorial – http://www.softwareprojects.com/resources/programming/t-how-to-move-copy-a-live-mysql-database-and-what-1257.html