Manually Synchronizing MySQL
Jump to navigation
Jump to search
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 ...