Manually Synchronizing MySQL

The instructions below describe how to manually synchronize two MySQL database servers. The example illustrate the process of copying files from zincdb3 to zincdb4.

Create an LVM Snapshot on Source Server

Gather Information about Volumes

In the example below this will be "/dev/mapper/zincdb3_data_vg-zincdb3_data_lv" and "84".

   $ mount
       /dev/md2 on / type ext3 (rw)
       proc on /proc type proc (rw)
       sysfs on /sys type sysfs (rw)
       devpts on /dev/pts type devpts (rw,gid=5,mode=620)
       /dev/md0 on /boot type ext3 (rw)
       tmpfs on /dev/shm type tmpfs (rw)
       /dev/mapper/zincdb3_data_vg-zincdb3_data_lv on /zincdb3_data type ext3 (rw,noatime)
       none on /proc/sys/fs/binfmt_misc type binfmt_misc (rw)
       sunrpc on /var/lib/nfs/rpc_pipefs type rpc_pipefs (rw)
   $ lvdisplay
       --- Logical volume ---
         LV Name                /dev/zincdb3_data_vg/zincdb3_data_lv
         VG Name                zincdb3_data_vg
         LV UUID                6eWrxz-z9tm-15ID-YL0u-25e5-gZHo-OFHf2h
         LV Write Access        read/write
         LV snapshot status     source of
                            /dev/zincdb3_data_vg/snap [active]
         LV Status              available
         # open                 1
         LV Size                800.00 GB
         Current LE             800
         Segments               1
         Allocation             inherit
         Read ahead sectors     auto
         - currently set to     256
         Block device           253:0
   $ pvdisplay
       --- Physical volume ---
         PV Name               /dev/md3
         VG Name               zincdb3_data_vg
         PV Size               884.33 GB / not usable 338.44 MB
         Allocatable           yes 
         PE Size (KByte)       1048576
         Total PE              884
         Free PE               84
         Allocated PE          880
         PV UUID               f8Izzd-HodY-GlJW-EzRk-oncY-5wtb-IHoErD

We will use the following values in the steps below:

• (Size) Limit: 80 [-l]
• Snapshot Name: snap [-n] (note that the name "snapshot" is not allowed by LVM)
• Source LV: /dev/zincdb3_data_vg/zincdb3_data_lv

Have this information ready before proceeding.

Stop MySQL

   [root@zincdb3]$ service mysql stop
       Shutting down MySQL......................                  [  OK  ]

Synchronize the File System

   [root@zincdb3]$ sync

Create the Snapshot

   [root@zincdb3]$ lvcreate -l 80 -s -n snap /dev/zincdb3_data_vg/zincdb3_data_lv

Restart MySQL

   [root@zincdb3]$ service mysql start
       Starting MySQL...                                          [  OK  ]

Mount the Snapshot

   [root@zincdb3]$ mkdir /snapshot
   [root@zincdb3]$ mount -o ro /dev/mapper/zincdb3_data_vg-snap /snapshot

Verify

  [root@zincdb3] $ mount
       /dev/md2 on / type ext3 (rw)
       proc on /proc type proc (rw)
       sysfs on /sys type sysfs (rw)
       devpts on /dev/pts type devpts (rw,gid=5,mode=620)
       /dev/md0 on /boot type ext3 (rw)
       tmpfs on /dev/shm type tmpfs (rw)
       /dev/mapper/zincdb3_data_vg-zincdb3_data_lv on /zincdb3_data type ext3 (rw,noatime)
       none on /proc/sys/fs/binfmt_misc type binfmt_misc (rw)
       sunrpc on /var/lib/nfs/rpc_pipefs type rpc_pipefs (rw)
       /dev/mapper/zincdb3_data_vg-snap on /snapshot type ext3 (ro)

Duplicate Files from Source Server to Destination Server

Create a Temporary Destination for Snapshot Files

Make sure the temporary directory is created on the same volume as the existing MySQL database files. It is important to not overwrite any information while copying these files over.

   [root@zincdb4]$ cd /var/lib/mysql/
   [root@zincdb4]$ mkdir snapshot

Copy Files from Source Server

This step can take a very long time. Be prepared to wait at least an hour

   [root@zincdb4]$ scp -r root@zincdb3:/snapshot/mysql snapshot
       ib_logfile1                                                      100% 5120KB   2.5MB/s   00:02    
       bill.MYI                                                         100%   19MB   6.3MB/s   00:03    
       jstatus.MYD                                                      100% 2418     2.4KB/s   00:00    
       wstatus.frm                                                      100% 8642     8.4KB/s   00:00    
       job.frm                                                          100% 8746     8.5KB/s   00:00    
       wstatus.MYD                                                      100%   88     0.1KB/s   00:00    
       authorize.MYI                                                    100% 6144     6.0KB/s   00:00    
       upload.MYD                                                       100% 9797     9.6KB/s   00:00    
       task.frm                                                         100% 8718     8.5KB/s   00:00    
       epiklic.MYD                                                      100%    7     0.0KB/s   00:00    
       authorize.MYD                                                    100% 1916     1.9KB/s   00:00    
       flag.MYD                                                         100% 1275     1.3KB/s   00:00    
       ...