Frodo at 22:53, 10 March 2014

2014-03-10T22:53:38Z

← Older revision		Revision as of 22:53, 10 March 2014
Line 95:		Line 95:
	...		...

	[[Category:~~Internal]]~~		[[Category:Sysadmin]]
	~~[[Category:Databases~~]]

Therese: 5 revisions

2012-10-08T20:29:21Z

5 revisions

New page

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
...

[[Category:Internal]]
[[Category:Databases]]

Manually Synchronizing MySQL - Revision history

Frodo at 22:53, 10 March 2014

Therese: 5 revisions