MySQL 使用lvm 快照备份

两台虚拟机，系统为CentOS 5.4，分别有三块磁盘来做逻辑卷。

IP分配及磁盘情况：
HA1                eth0：192.168.0.77        eth1：192.168.10.1   /dev/sdc /dev/sdd /dev/sde
HA2                eth0：192.168.0.69        eth1：192.168.10.2   /dev/sdc /dev/sdd /dev/sde

一、配置逻辑磁盘

查看磁盘情况：
[root@HA1 ~]# fdisk -l
Disk /dev/sda: 10.7 GB, 10737418240 bytes
255 heads, 63 sectors/track, 1305 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Device Boot      Start         End      Blocks   Id  System
/dev/sda1   *           1          13      104391   83  Linux
/dev/sda2              14        1305    10377990   8e  Linux LVM
Disk /dev/sdb: 6442 MB, 6442450944 bytes
255 heads, 63 sectors/track, 783 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Disk /dev/sdb doesn’t contain a valid partition table
Disk /dev/sdc: 536 MB, 536870912 bytes
64 heads, 32 sectors/track, 512 cylinders
Units = cylinders of 2048 * 512 = 1048576 bytes
Disk /dev/sdc doesn’t contain a valid partition table
Disk /dev/sdd: 536 MB, 536870912 bytes
64 heads, 32 sectors/track, 512 cylinders
Units = cylinders of 2048 * 512 = 1048576 bytes
Disk /dev/sdd doesn’t contain a valid partition table
Disk /dev/sde: 536 MB, 536870912 bytes
64 heads, 32 sectors/track, 512 cylinders
Units = cylinders of 2048 * 512 = 1048576 bytes
Disk /dev/sde doesn’t contain a valid partition table

为磁盘分区：
[root@HA1 ~]# fdisk /dev/sdc
Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel
Building a new DOS disklabel. Changes will remain in memory only,
until you decide to write them. After that, of course, the previous
content won’t be recoverable.
Warning: invalid flag 0×0000 of partition table 4 will be corrected by w(rite)
Command (m for help):  m  # 获取帮助
Command action
a   toggle a bootable flag
b   edit bsd disklabel
c   toggle the dos compatibility flag
d   delete a partition
l   list known partition types
m   print this menu
n   add a new partition
o   create a new empty DOS partition table
p   print the partition table
q   quit without saving changes
s   create a new empty Sun disklabel
t   change a partition’s system id
u   change display/entry units
v   verify the partition table
w   write table to disk and exit
x   extra functionality (experts only)
Command (m for help):  n  # 新建分区
Command action
e   extended
p   primary partition (1-4)
p  # 新建主分区
Partition number (1-4):  1  # 输入分区号
First cylinder (1-512, default 1):     # 回车，默认即可
Using default value 1
Last cylinder or +size or +sizeM or +sizeK (1-512, default 512):     # 回车，使用所有磁盘空间
Using default value 512
Command (m for help):  t  # 设置分区类型
Selected partition 1
Hex code (type L to list codes):  L  # 查看分区类型
0  Empty           1e  Hidden W95 FAT1 80  Old Minix       bf  Solaris
1  FAT12           24  NEC DOS         81  Minix / old Lin c1  DRDOS/sec (FAT-
2  XENIX root      39  Plan 9          82  Linux swap / So c4  DRDOS/sec (FAT-
3  XENIX usr       3c  PartitionMagic  83  Linux           c6  DRDOS/sec (FAT-
4  FAT16 <32M      40  Venix 80286     84  OS/2 hidden C:  c7  Syrinx
5  Extended        41  PPC PReP Boot   85  Linux extended  da  Non-FS data
6  FAT16           42  SFS             86  NTFS volume set db  CP/M / CTOS / .
7  HPFS/NTFS       4d  QNX4.x          87  NTFS volume set de  Dell Utility
8  AIX             4e  QNX4.x 2nd part 88  Linux plaintext df  BootIt
9  AIX bootable    4f  QNX4.x 3rd part 8e  Linux LVM       e1  DOS access
a  OS/2 Boot Manag 50  OnTrack DM      93  Amoeba          e3  DOS R/O
b  W95 FAT32       51  OnTrack DM6 Aux 94  Amoeba BBT      e4  SpeedStor
c  W95 FAT32 (LBA) 52  CP/M            9f  BSD/OS          eb  BeOS fs
e  W95 FAT16 (LBA) 53  OnTrack DM6 Aux a0  IBM Thinkpad hi ee  EFI GPT
f  W95 Ext’d (LBA) 54  OnTrackDM6      a5  FreeBSD         ef  EFI (FAT-12/16/
10  OPUS            55  EZ-Drive        a6  OpenBSD         f0  Linux/PA-RISC b
11  Hidden FAT12    56  Golden Bow      a7  NeXTSTEP        f1  SpeedStor
12  Compaq diagnost 5c  Priam Edisk     a8  Darwin UFS      f4  SpeedStor
14  Hidden FAT16 <3 61  SpeedStor       a9  NetBSD          f2  DOS secondary
16  Hidden FAT16    63  GNU HURD or Sys ab  Darwin boot     fb  VMware VMFS
17  Hidden HPFS/NTF 64  Novell Netware  b7  BSDI fs         fc  VMware VMKCORE
18  AST SmartSleep  65  Novell Netware  b8  BSDI swap       fd  Linux raid auto
1b  Hidden W95 FAT3 70  DiskSecure Mult bb  Boot Wizard hid fe  LANstep
1c  Hidden W95 FAT3 75  PC/IX           be  Solaris boot    ff  BBT
Hex code (type L to list codes):  8e  # 设置分区类型为Linux LVM
Changed system type of partition 1 to 8e (Linux LVM)
Command (m for help):  w  # 保存退出
The partition table has been altered!
Calling ioctl() to re-read partition table.
Syncing disks.
[root@HA1 ~]# fdisk /dev/sdd
Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel
Building a new DOS disklabel. Changes will remain in memory only,
until you decide to write them. After that, of course, the previous
content won’t be recoverable.
Warning: invalid flag 0×0000 of partition table 4 will be corrected by w(rite)
Command (m for help):  n
Command action
e   extended
p   primary partition (1-4)
p
Partition number (1-4):  1
First cylinder (1-512, default 1):
Using default value 1
Last cylinder or +size or +sizeM or +sizeK (1-512, default 512):
Using default value 512
Command (m for help):  t
Selected partition 1
Hex code (type L to list codes):  8e
Changed system type of partition 1 to 8e (Linux LVM)
Command (m for help):  w
The partition table has been altered!
Calling ioctl() to re-read partition table.
Syncing disks.
[root@HA1 ~]# fdisk /dev/sde
Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel
Building a new DOS disklabel. Changes will remain in memory only,
until you decide to write them. After that, of course, the previous
content won’t be recoverable.
Warning: invalid flag 0×0000 of partition table 4 will be corrected by w(rite)
Command (m for help):  n
Command action
e   extended
p   primary partition (1-4)
p
Partition number (1-4):  1
First cylinder (1-512, default 1):
Using default value 1
Last cylinder or +size or +sizeM or +sizeK (1-512, default 512):
Using default value 512
Command (m for help):  t
Selected partition 1
Hex code (type L to list codes):  8e
Changed system type of partition 1 to 8e (Linux LVM)
Command (m for help):  w
The partition table has been altered!
Calling ioctl() to re-read partition table.
Syncing disks.
分区完成后查看磁盘情况：
[root@HA1 ~]# fdisk -l
Disk /dev/sda: 10.7 GB, 10737418240 bytes
255 heads, 63 sectors/track, 1305 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Device Boot      Start         End      Blocks   Id  System
/dev/sda1   *           1          13      104391   83  Linux
/dev/sda2              14        1305    10377990   8e  Linux LVM
Disk /dev/sdb: 6442 MB, 6442450944 bytes
255 heads, 63 sectors/track, 783 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Disk /dev/sdb doesn’t contain a valid partition table
Disk /dev/sdc: 536 MB, 536870912 bytes
64 heads, 32 sectors/track, 512 cylinders
Units = cylinders of 2048 * 512 = 1048576 bytes
Device Boot      Start         End      Blocks   Id  System
/dev/sdc1               1         512      524272   8e  Linux LVM
Disk /dev/sdd: 536 MB, 536870912 bytes
64 heads, 32 sectors/track, 512 cylinders
Units = cylinders of 2048 * 512 = 1048576 bytes
Device Boot      Start         End      Blocks   Id  System
/dev/sdd1               1         512      524272   8e  Linux LVM
Disk /dev/sde: 536 MB, 536870912 bytes
64 heads, 32 sectors/track, 512 cylinders
Units = cylinders of 2048 * 512 = 1048576 bytes
Device Boot      Start         End      Blocks   Id  System
/dev/sde1               1         512      524272   8e  Linux LVM

创建物理卷：
[root@HA1 ~]# pvcreate /dev/sdc1 /dev/sdd1 /dev/sde1
Physical volume “/dev/sdc1″ successfully created
Physical volume “/dev/sdd1″ successfully created
Physical volume “/dev/sde1″ successfully created

查看物理卷：
[root@HA1 ~]# pvdisplay
— Physical volume —
PV Name               /dev/sda2
VG Name               VolGroup00
PV Size               9.90 GB / not usable 22.76 MB
Allocatable           yes (but full)
PE Size (KByte)       32768
Total PE              316
Free PE               0
Allocated PE          316
PV UUID               1zBHox-Dla7-0ozU-0IFp-Onl4-V7V2-R10XXW
“/dev/sdc1″ is a new physical volume of “511.98 MB”
— NEW Physical volume —
PV Name               /dev/sdc1
VG Name
PV Size               511.98 MB
Allocatable           NO
PE Size (KByte)       0
Total PE              0
Free PE               0
Allocated PE          0
PV UUID               DwoEeZ-NmK5-ZDR6-qCmx-vJsw-7Wet-2qGako
“/dev/sdd1″ is a new physical volume of “511.98 MB”
— NEW Physical volume —
PV Name               /dev/sdd1
VG Name
PV Size               511.98 MB
Allocatable           NO
PE Size (KByte)       0
Total PE              0
Free PE               0
Allocated PE          0
PV UUID               YfolqL-6Qlm-bUki-qWTJ-8zIW-zeJI-Ssjxln
“/dev/sde1″ is a new physical volume of “511.98 MB”
— NEW Physical volume —
PV Name               /dev/sde1
VG Name
PV Size               511.98 MB
Allocatable           NO
PE Size (KByte)       0
Total PE              0
Free PE               0
Allocated PE          0
PV UUID               Rhdkyp-MBB6-UeTK-dmuP-6Dza-L69O-sW6eNv

创建逻辑卷组：
[root@HA1 ~]# vgcreate dataVg /dev/sdc1 /dev/sdd1 /dev/sde1
Volume group “dataVg” successfully created

创建逻辑卷：
[root@HA1 ~]# lvcreate –name dataLv –size 1G dataVg
Logical volume “dataLv” created
查看逻辑卷：
[root@HA1 ~]# lvdisplay
— Logical volume —
LV Name                /dev/dataVg/dataLv
VG Name                dataVg
LV UUID                gXPZmP-c41N-Yeu8-mT8U-0sUx-Mu2X-pR1PyE
LV Write Access        read/write
LV Status              available
# open                 0
LV Size                1.00 GB
Current LE             256
Segments               3
Allocation             inherit
Read ahead sectors     auto
- currently set to     256
Block device           253:2
— Logical volume —
LV Name                /dev/VolGroup00/LogVol00
VG Name                VolGroup00
LV UUID                yTby3S-TYzd-x7fP-T8HJ-GOEg-lt7E-i90qZy
LV Write Access        read/write
LV Status              available
# open                 1
LV Size                8.88 GB
Current LE             284
Segments               1
Allocation             inherit
Read ahead sectors     auto
- currently set to     256
Block device           253:0
— Logical volume —
LV Name                /dev/VolGroup00/LogVol01
VG Name                VolGroup00
LV UUID                bNfOaD-vcTc-hq4c-7Bd0-3a6S-wD0B-aFZMzM
LV Write Access        read/write
LV Status              available
# open                 1
LV Size                1.00 GB
Current LE             32
Segments               1
Allocation             inherit
Read ahead sectors     auto
- currently set to     256
Block device           253:1

格式化逻辑卷：
[root@HA1 ~]# mkfs.ext3 /dev/dataVg/dataLv
mke2fs 1.39 (29-May-2006)
Filesystem label=
OS type: Linux
Block size=4096 (log=2)
Fragment size=4096 (log=2)
131072 inodes, 262144 blocks
13107 blocks (5.00%) reserved for the super user
First data block=0
Maximum filesystem blocks=268435456
8 block groups
32768 blocks per group, 32768 fragments per group
16384 inodes per group
Superblock backups stored on blocks:
32768, 98304, 163840, 229376
Writing inode tables: done
Creating journal (8192 blocks): done
Writing superblocks and filesystem accounting information: done
This filesystem will be automatically checked every 25 mounts or
180 days, whichever comes first.  Use tune2fs -c or -i to override.

挂载逻辑卷到/data目录：
[root@HA1 ~]# mount /dev/dataVg/dataLv /data/

设置开机自动挂载挂逻辑卷：
[root@HA1 ~]# vi /etc/fstab
/dev/dataVg/dataLv      /data                   ext3    defaults        0 0

在HA2上执行上面步骤。

二、安装MySQL并迁移MySQL数据到HA1 /data下。

三、安装MySQL LVM备份工具：
[root@HA1 ~]# wget  http://search.cpan.org/CPAN/authors/id/S/SH/SHLOMIF/Config-IniFiles-2.54.tar.gz
[root@HA1 ~]# tar xzvf Config-IniFiles-2.54.tar.gz
[root@HA1 ~]# cd Config-IniFiles-2.54
[root@HA1 Config-IniFiles-2.54]# perl Makefile.PL
Checking if your kit is complete…
Looks good
Writing Makefile for Config::IniFiles
[root@HA1 Config-IniFiles-2.54]# make
cp lib/Config/IniFiles.pm blib/lib/Config/IniFiles.pm
Manifying blib/man3/Config::IniFiles.3pm
[root@HA1 Config-IniFiles-2.54]# make install
Installing /usr/lib/perl5/site_perl/5.8.8/Config/IniFiles.pm
Installing /usr/share/man/man3/Config::IniFiles.3pm
Writing /usr/lib/perl5/site_perl/5.8.8/i386-linux-thread-multi/auto/Config/IniFiles/.packlist
Appending installation info to /usr/lib/perl5/5.8.8/i386-linux-thread-multi/perllocal.pod
[root@HA1 Config-IniFiles-2.54]# cd ..
[root@HA1 ~]# wget  http://www.lenzg.net/mylvmbackup/mylvmbackup-0.13.tar.gz
[root@HA1 ~]# tar xzvf mylvmbackup-0.13.tar.gz
[root@HA1 ~]# cd mylvmbackup-0.13
[root@HA1 mylvmbackup-0.13]# make install
[root@HA1 mylvmbackup-0.13]# cd ..
配置mylvmbackup：
[root@HA1 ~]# vi /etc/mylvmbackup.conf
[mysql]
user=root
password=
host=localhost
port=3306
socket= /data/mysql/mysql.sock
mycnf=/etc/my.cnf
#
# LVM-specific options
#
[lvm]
vgname= dataVg
lvname= dataLv
backuplv= backupLv
lvsize= 0.45G
#
# File system specific options
#
[fs]
xfs=0
mountdir= /var/tmp/mylvmbackup/mnt/
backupdir= /var/tmp/mylvmbackup/backup/
relpath=
注意修改上面标红的配置项。

创建下面目录：
[root@HA1 ~]# mkdir -p  /var/tmp/mylvmbackup/backup
[root@HA1 ~]# mkdir -p  /var/tmp/mylvmbackup/mnt

查看数据库情况（employees库使用InnoDB 引擎）：
[root@HA1 ~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 8
Server version: 5.0.77 Source distribution
Type ‘help;’ or ‘h’ for help. Type ‘c’ to clear the buffer.
mysql> show databases;
+——————–+
| Database           |
+——————–+
| information_schema |
| employees          |
| mysql              |
+——————–+
3 rows in set (0.01 sec)
mysql> use employees;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show engines;
+————+———+—————————————————————-+
| Engine     | Support | Comment                                                        |
+————+———+—————————————————————-+
| MyISAM     | DEFAULT | Default engine as of MySQL 3.23 with great performance         |
| MEMORY     | YES     | Hash based, stored in memory, useful for temporary tables      |
| InnoDB     | YES     | Supports transactions, row-level locking, and foreign keys     |
| BerkeleyDB | YES     | Supports transactions and page-level locking                   |
| BLACKHOLE  | NO      | /dev/null storage engine (anything you write to it disappears) |
| EXAMPLE    | NO      | Example storage engine                                         |
| ARCHIVE    | NO      | Archive storage engine                                         |
| CSV        | NO      | CSV storage engine                                             |
| ndbcluster | NO      | Clustered, fault-tolerant, memory-based tables                 |
| FEDERATED  | NO      | Federated MySQL storage engine                                 |
| MRG_MYISAM | YES     | Collection of identical MyISAM tables                          |
| ISAM       | NO      | Obsolete storage engine                                        |
+————+———+—————————————————————-+
12 rows in set (0.00 sec)
mysql> show tables;
+———————+
| Tables_in_employees |
+———————+
| departments         |
| dept_emp            |
| dept_manager        |
| employees           |
| salaries            |
| titles              |
+———————+
6 rows in set (0.00 sec)
mysql> select count(*) from employees;
+———-+
| count(*) |
+———-+
|   300024 |
+———-+
1 row in set (1.94 sec)
mysql> quit
Bye

lvm快照备份数据库：
[root@HA1 ~]# mylvmbackup
20091125 14:50:10 Info: Connecting to database…
20091125 14:50:10 Info:  Flushing tables with read lock…   # 锁定库表，准备备份
20091125 14:50:10 Info:  Taking position record into /tmp/mylvmbackup-backup-20091125_145009_mysql-odzMgs.pos…    # 如开启bin-log则记录日志位置信息
20091125 14:50:10 Info: Running: lvcreate -s –size=0.45G –name=backupLv /dev/dataVg/dataLv
File descriptor 4 (socket:[21544]) leaked on lvcreate invocation. Parent PID 6062: /usr/bin/perl
Rounding up size to full physical extent 464.00 MB
Logical volume “backupLv” created
20091125 14:50:13 Info:  DONE: taking LVM snapshot    # 只需3s完成lvm快照备份
20091125 14:50:13 Info:  Unlocking tables…    # 完成备份，解除锁定，至此数据库完全恢复正常访问
20091125 14:50:13 Info: Disconnecting from database…
20091125 14:50:13 Info: Mounting snapshot…
20091125 14:50:13 Info: Running: mount -o rw /dev/dataVg/backupLv /var/tmp/mylvmbackup/mnt/backup
20091125 14:50:13 Info: DONE: mount snapshot
20091125 14:50:13 Info: Copying/tmp/mylvmbackup-backup-20091125_145009_mysql-odzMgs.pos to/var/tmp/mylvmbackup/mnt/backup-pos/backup-20091125_145009_mysql.pos…
20091125 14:50:13 Info: Copying /etc/my.cnf to /var/tmp/mylvmbackup/mnt/backup-pos/backup-20091125_145009_mysql_my.cnf…
20091125 14:50:13 Info: Taking actual backup…
20091125 14:50:13 Info: Creating tar archive /var/tmp/mylvmbackup/backup/backup-20091125_145009_mysql.tar.gz
20091125 14:50:13 Info: Running: cd ‘/var/tmp/mylvmbackup/mnt’ ;’tar’cvf – backup/  backup-pos/backup-20091125_145009_mysql.posbackup-pos/backup-20091125_145009_mysql_my.cnf| gzip –stdout –verbose–best ->/var/tmp/mylvmbackup/backup/backup-20091125_145009_mysql.tar.gz.INCOMPLETE-54lIVbU
backup/
backup/lost+found/
backup/logs/
backup/logs/www.access.log
backup/logs/error.log
backup/backup/
backup/backup/cib.xml
backup/backup/ifcfg-lo:0
backup/mysql/
backup/mysql/ib_logfile0
tar: backup/mysql/mysql.sock: socket ignored
backup/mysql/employees/
backup/mysql/employees/departments.frm
backup/mysql/employees/dept_emp.frm
backup/mysql/employees/salaries.frm
backup/mysql/employees/employees.frm
backup/mysql/employees/db.opt
backup/mysql/employees/dept_manager.frm
backup/mysql/employees/titles.frm
backup/mysql/ib_logfile1
backup/mysql/mysql/
backup/mysql/mysql/help_category.MYD
backup/mysql/mysql/help_topic.MYI
backup/mysql/mysql/help_relation.MYD
backup/mysql/mysql/db.frm
backup/mysql/mysql/time_zone.frm
backup/mysql/mysql/time_zone.MYD
backup/mysql/mysql/time_zone_transition.MYI
backup/mysql/mysql/columns_priv.MYI
backup/mysql/mysql/tables_priv.frm
backup/mysql/mysql/host.MYD
backup/mysql/mysql/procs_priv.MYI
backup/mysql/mysql/proc.frm
backup/mysql/mysql/user.MYD
backup/mysql/mysql/db.MYI
backup/mysql/mysql/time_zone_name.MYI
backup/mysql/mysql/time_zone.MYI
backup/mysql/mysql/func.MYI
backup/mysql/mysql/help_keyword.MYI
backup/mysql/mysql/help_topic.MYD
backup/mysql/mysql/procs_priv.MYD
backup/mysql/mysql/db.MYD
backup/mysql/mysql/time_zone_name.MYD
backup/mysql/mysql/host.MYI
backup/mysql/mysql/time_zone_leap_second.frm
backup/mysql/mysql/time_zone_transition_type.MYD
backup/mysql/mysql/time_zone_transition_type.MYI
backup/mysql/mysql/help_relation.MYI
backup/mysql/mysql/time_zone_leap_second.MYI
backup/mysql/mysql/help_keyword.MYD
backup/mysql/mysql/user.frm
backup/mysql/mysql/func.MYD
backup/mysql/mysql/tables_priv.MYI
backup/mysql/mysql/tables_priv.MYD
backup/mysql/mysql/time_zone_transition.frm
backup/mysql/mysql/user.MYI
backup/mysql/mysql/help_category.frm
backup/mysql/mysql/procs_priv.frm
backup/mysql/mysql/columns_priv.MYD
backup/mysql/mysql/help_category.MYI
backup/mysql/mysql/help_keyword.frm
backup/mysql/mysql/time_zone_leap_second.MYD
backup/mysql/mysql/proc.MYI
backup/mysql/mysql/proc.MYD
backup/mysql/mysql/time_zone_transition_type.frm
backup/mysql/mysql/time_zone_transition.MYD
backup/mysql/mysql/func.frm
backup/mysql/mysql/time_zone_name.frm
backup/mysql/mysql/host.frm
backup/mysql/mysql/help_relation.frm
backup/mysql/mysql/help_topic.frm
backup/mysql/mysql/columns_priv.frm
backup/mysql/ibdata1
backup/html/
backup/html/www.baihe.com/
backup/html/www.baihe.com/test.html
backup/html/www.baihe.com/index.html
backup-pos/backup-20091125_145009_mysql.pos
backup-pos/backup-20091125_145009_mysql_my.cnf
64.0%
20091125 14:56:00 Info: DONE: create tar archive
20091125 14:56:01 Info: Cleaning up…
20091125 14:56:01 Info: Running: umount /var/tmp/mylvmbackup/mnt/backup
20091125 14:56:02 Info: DONE: Unmounting /var/tmp/mylvmbackup/mnt/backup
20091125 14:56:02 Info: LVM Usage stats:
20091125 14:56:02 Info:   LV       VG     Attr   LSize   Origin Snap%  Move Log Copy%  Convert
20091125 14:56:02 Info:   backupLv dataVg swi-a- 464.00M dataLv   0.09
20091125 14:56:02 Info: Running: lvremove -f /dev/dataVg/backupLv
Logical volume “backupLv” successfully removed
20091125 14:56:03 Info: DONE: Removing snapshot
[root@HA1 ~]# cd /var/tmp/mylvmbackup/backup
You have new mail in /var/spool/mail/root
[root@HA1 backup]# ls
backup-20091125_145009_mysql.tar.gz
[root@HA1 backup]# scp backup-20091125_145009_mysql.tar.gz HA2:/root/
root@ha2’s password:
backup-20091125_145009_mysql.tar.gz                                         100%   80MB 799.2KB/s   01:42
在HA2上进行有效性验证：
[root@HA2 data]# tar xzvf /root/backup-20091125_145009_mysql.tar.gz
[root@HA2 data]# ls
backup  backup-pos  lost+found
You have new mail in /var/spool/mail/root
[root@HA2 data]# cd backup
[root@HA2 backup]# ls
backup  html  logs  lost+found  mysql
[root@HA2 backup]# mv mysql/ ..
[root@HA2 backup]# cd ..
[root@HA2 data]# service mysqld start
Starting MySQL:                                            [  OK  ]
[root@HA2 data]# mysql
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 2
Server version: 5.0.77 Source distribution
Type ‘help;’ or ‘h’ for help. Type ‘c’ to clear the buffer.
mysql> show databases;
+——————–+
| Database           |
+——————–+
| information_schema |
| employees          |
| mysql              |
+——————–+
3 rows in set (0.00 sec)
mysql> use employees;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+———————+
| Tables_in_employees |
+———————+
| departments         |
| dept_emp            |
| dept_manager        |
| employees           |
| salaries            |
| titles              |
+———————+
6 rows in set (0.00 sec)
mysql> select count(*) from employees;
+———-+
| count(*) |
+———-+
|   300024 |
+———-+
1 row in set (0.58 sec)
mysql>

四、配置数据库主从复制：

HA1（主）
配置mysql
[root@HA1 ~]# cat /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
log-bin=/data/mysql/log/mysql-bin.log
server-id=1
[mysqld_safe]
log-error=/data/mysql/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[mysql]
socket=/data/mysql/mysql.sock
如果对配置文件有改动需要重启MySQL。
lvm快照备份数据库：
[root@HA1 backup]# mylvmbackup
拷贝备份文件到HA2
[root@HA1 backup]# scp backup-20091125_155132_mysql.tar.gz HA2:/root/
root@ha2’s password:
backup-20091125_155132_mysql.tar.gz                                         100%   80MB   1.2MB/s   01:07
在主库上添加同步账户：
[root@HA1 backup]# mysql
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 5
Server version: 5.0.77-log Source distribution
Type ‘help;’ or ‘h’ for help. Type ‘c’ to clear the buffer.
mysql> GRANT REPLICATION SLAVE ON *.* TO ‘rep’@'192.168.10.%’ IDENTIFIED BY ’slavepass’;

HA2（从）
解压主库备份数据文件到从库数据目录：
[root@HA2 data]# tar xzvf /root/backup-20091125_155132_mysql.tar.gz
拷贝数据文件到mysql数据目录：
[root@HA2 data]# mv backup/mysql/ .
查看备份时mysql日志位置：
[root@HA2 data]# cat backup-pos/backup-20091125_155132_mysql.pos
Master:File=mysql-bin.000001
Master:Position=244
Master:Binlog_Do_DB=
Master:Binlog_Ignore_DB=
修改mysql配置文件：
[root@HA2 data]# cat /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
server-id=2
[mysqld_safe]
log-error=/data/mysql/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[mysql]
socket=/data/mysql/mysql.sock
启动MySQL：
[root@HA2 log]# service mysqld start
Starting MySQL:                                            [  OK  ]

配置mysql从库：
[root@HA2 log]# mysql
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 4
Server version: 5.0.77 Source distribution
Type ‘help;’ or ‘h’ for help. Type ‘c’ to clear the buffer.
mysql> CHANGE MASTER TO
-> MASTER_HOST=’192.168.10.1′,
-> MASTER_USER=’rep’,
-> MASTER_PASSWORD=’slavepass’,
-> MASTER_LOG_FILE=’mysql-bin.000001′,
-> MASTER_LOG_POS=244;
Query OK, 0 rows affected (0.00 sec)
mysql> slave start;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave statusG
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.10.1
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 500
Relay_Log_File: mysqld-relay-bin.000003
Relay_Log_Pos: 637
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running:  Yes
Slave_SQL_Running:  Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 500
Relay_Log_Space: 637
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
1 row in set (0.00 sec)
mysql> quit
Bye

验证同步配置情况：
在HA1上插入一条数据：
[root@HA1 ~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 62
Server version: 5.0.77-log Source distribution
Type ‘help;’ or ‘h’ for help. Type ‘c’ to clear the buffer.
mysql> use employees;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> insert into employees values (66666666,’1982-10-17′,’Shi’,'Dongliang’,”M”,’2008-06-01′) ;
Query OK, 1 row affected (0.03 sec)
在HA2上查询，看同步情况：
[root@HA2 data]# mysql
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 7
Server version: 5.0.77 Source distribution
Type ‘help;’ or ‘h’ for help. Type ‘c’ to clear the buffer.
mysql> use employees;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from employees where emp_no=66666666;
+———-+————+————+———–+——–+————+
| emp_no   | birth_date | first_name | last_name | gender | hire_date  |
+———-+————+————+———–+——–+————+
| 66666666 | 1982-10-17 | Shi        | Dongliang  | M      | 2008-06-01 |
+———-+————+————+———–+——–+————+
1 row in set (0.04 sec)

不管你使用MyISAM存储引擎还是InnoDB存储引擎，通过LVM快照都很容易得到一个一致的MySQL备份。LVM快照备份MySQL数据时，一但锁定数据库完成只需几秒就可以做一个快照备份，释放表锁，数据库便可完全恢复正常访问，剩下的事情便是压缩数据备份并拷贝到从库进行恢复（根据数据大小，这可能需要很长时间，但基本不会再影响主库了）。