Knowledgebase

I have a customer who has over 100GB of MySQL data and taking their site down for even a few minutes is not feasable. I really wanted to get a slave set up in case the main server ever dies. Even though the server is backed up, it would take 2-3 hours (or longer) to restore the MySQL server.

The solution is to use replication. The traditional problem with this approach is locking the tables for so long while the mysqldump happens... we are talking close to 4-5 hours for the database size.

Idera's Free Tool called Linux Hot Copy (hcp) was the answer I was looking for. By using hcp, you can lock the tables, make a near instant "snapshot", record the master position, and unlock the tables. At your leisure, just copy the snapshot of the mysql data to your slave device, and start up your replication! This makes setting up new slaves a snap with minimal impact on your business.

First off, I will assume you have a production MySQL server in use and running. In my scenario, I am using CentOS 5.6 64Bit and MySQL 5.5. This tutorial will probably will work for older versions as well. I also will assume you know how to edit and copy files at the linux command line. If you don't, you probably should get help from an experienced system administrator.

If you have not done so already, set up another mysql server for your slave. It should be a decent server, equal to your current live production server so you can switch to it in the event of failure.

I will also assume:
master server = 192.168.1.100
slave server = 192.168.2.200

On Master Server (192.168.1.100):

1. Install Linux Hot Copy. Linux Hot Copy. If you need help with installation, here's some documentation

2. Setup your Server ID and enable bin-logs. Note that bin logs record every change to your database, so make sure you have ample space to continue!)

Edit your /etc/my.cnf file and put these lines at the top, just under the [mysqld] line.

# enable mysql bin logs and server-id for mysql replication
       log-bin=mysql-bin
       server-id=1

Restart MySQL so bin logs are started. e.g. /etc/init.d/mysql restart you can verify it's working by issuing the show master statusG command.

3. Create a user that has replication privs on the Master Server.

mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.2.200' IDENTIFIED BY 'password';

4. The next few steps will need to be done quickly so that you minimize your mysql server's downtime. Make sure you know up-front the device (e.g. /dev/sda2) where your MySQL installation is located (typically /var/lib/mysql on CentOS):

Lock your Master MySQL Tables and show the status location of the bin log....

mysql> FLUSH TABLES WITH READ LOCK; SHOW MASTER STATUS;

Make sure you record and copy the information down, e.g. the filename and log position

From the command line, enter the following command, replacing /dev/sda2 with your raw device:

hcp -o /dev/sda2

Back to MySQL, unlock your tables:

mysql> unlock tables;

Now you have a perfect copy of your "frozen" data at the following location: (may vary)..

FROZEN DATA LOCATION:
/var/hotcopy/sda2_hcp1/var/lib/mysql

On Slave Server: 192.168.2.200

On the slave server, make sure MySQL is stopped and move the old mysql folder: (make sure this is the SLAVE SERVER 192.168.2.200 and NOT the live server!):

/etc/init.d/mysql stop
mv /var/lib/mysql /var/lib/mysql.old

Back on the Master Server: 192.168.1.100

1. Copy the "frozen" mysql data:

rsync -avz /var/hotcopy/sda2_hcp1/var/lib/mysql  root@192.168.2.200:/var/lib/

2. Copy my.cnf to slave:

scp /etc/my.cnf root@slaveip_or_host:/etc/my.cnf

3. Once the Copy is Complete you can delete your "hot copy"

hcp -r /dev/hcp1

Now, go to your Slave Server: 192.168.2.200

1. edit /etc/my.cnf and change server-id to 2 and comment out or delete the log-bin line you added from the master..

2. start up mysql, and then enter commands to connect to master.. replacing the log file and position number with the ones you recorded earlier:

mysql> CHANGE MASTER TO 
      MASTER_HOST='192.168.1.100', 
      MASTER_USER='repl', 
      MASTER_PASSWORD='password', 
      MASTER_LOG_FILE='mysql-bin.000001',
      MASTER_LOG_POS=12345678;

mysql> START SLAVE;

 mysql> SHOW SLAVE STATUS/G;

MySQL will show how far it's behind, it might take a few minutes to catch up depending on the number of changes that happened to your database during the copy.