对MySQL进行逻辑卷备份与恢复

推荐原创

david_yeung 2009-04-15 15:57:16 博主文章分类：MySQL备份与恢复 ©著作权

文章标签 备份恢复 mysql lvm zrm 文章分类 MySQL 数据库

©著作权归作者所有：来自51CTO博客作者david_yeung的原创作品，请联系作者获取转载授权，否则将追究法律责任

ZRM 我之前我介绍过，这里就不多少了。

以下是关于用mysql-zrm 来测试基于LVM 逻辑卷管理的数据库全库备份。
我这里用的是SUN 的VBOX 虚拟机来做的测试，基于Red Hat AS 5.3。

1. 先建立逻辑卷。
fdisk 我就不介绍了，这里演示下怎么用创建逻辑卷以及怎么用LVM来备份MySQL 数据库。

[root@mysql01 ytt]# pvcreate /dev/hdb5
Physical volume "/dev/hdb5" successfully created
[root@mysql01 ytt]# vgcreate VolGroup01 /dev/hdb5
Volume group "VolGroup01" successfully created
用来存放数据。
[root@mysql01 ~]# lvcreate -L 800M -n mysqldata /dev/VolGroup01
Logical volume "test1" created
用来存放日志。
[root@mysql01 ~]# lvcreate -L 200M -n mysqlbinlog /dev/VolGroup01
Logical volume "test2" created
以下则是显示创建的LVM的信息:
[root@mysql01 ~]# pvdisplay /dev/hdb5
--- Physical volume ---
PV Name               /dev/hdb5
VG Name               VolGroup01
PV Size               1.99 GB / not usable 2.12 MB
Allocatable           yes
PE Size (KByte)       4096
Total PE              509
Free PE               259
Allocated PE          250
PV UUID               6aeXEj-MDLY-7k3B-332y-qpZX-CiIX-ME1CZy

[root@mysql01 ~]# vgdisplay /dev/VolGroup01
--- Volume group ---
VG Name               VolGroup01
System ID
Format                lvm2
Metadata Areas        1
Metadata Sequence No 37
VG Access             read/write
VG Status             resizable
MAX LV                0
Cur LV                2
Open LV               2
Max PV                0
Cur PV                1
Act PV                1
VG Size               1.99 GB
PE Size               4.00 MB
Total PE              509
Alloc PE / Size       250 / 1000.00 MB
Free PE / Size       259 / 1.01 GB
VG UUID               ux2a6e-DGQX-E05p-I0IP-75zq-TW22-M1jF2x
[root@mysql01 ~]# lvdisplay /dev/VolGroup01
--- Logical volume ---
LV Name                /dev/VolGroup01/mysqldata
VG Name                VolGroup01
LV UUID                KFMFgG-HhXt-1xSq-VbVh-fb20-75Am-Ag6EIP
LV Write Access        read/write
LV Status              available
# open                 1
LV Size                800.00 MB
Current LE             200
Segments               1
Allocation             inherit
Read ahead sectors     auto
- currently set to     256
Block device           253:2

--- Logical volume ---
LV Name                /dev/VolGroup01/mysqlbinlog
VG Name                VolGroup01
LV UUID                urvM12-yw24-9GT7-rWsm-kaL4-GKNd-z0deZP
LV Write Access        read/write
LV Status              available
# open                 1
LV Size                200.00 MB
Current LE             50
Segments               1
Allocation             inherit
Read ahead sectors     auto
- currently set to     256
Block device           253:3

随系统自动加载。

# Add the following new lines to /etc/fstab.
/dev/VolGroup01/mysqldata /var/lib/mysql/data                    ext3
defaults        0 0
/dev/VolGroup01/mysqlbinlog /var/lib/mysql/binlog                    ext3
defaults        0 0

2. 以下是配置文件：
backup-level=0
backup-mode=raw
lvm-snapshot=10M
snapshot-plugin="/usr/share/mysql-zrm/plugins/lvm-snapshot.pl"
backup-type=regular
destination=/var/lib/mysql-zrm
all-databases=1

user="backup"
password="123456"
port=3306

socket=/var/lib/mysql/mysql.sock
mysql-binpath="/usr/bin"
mysql-binlog-path="/var/lib/mysql/binlog"
verbose=1
mailto="[email]david.yang@actionsky.com[/email]"
html-reports=backup-status-info, backup-app-performance-info
~
3. 现在可以备份了：

[root@mysql01 ~]# mysql-zrm-backup --backup-set=ytt
...
Wed Apr 15 07:20:22 2009: ytt:backup:INFO: Locking tables and creating
snapshot
Wed Apr 15 07:20:22 2009: ytt:backup:INFO: "/usr/bin"/mysql -e "flush tables
with read lock; flush logs; show master status;system
(/usr/share/mysql-zrm/plugins/lvm-snapshot.pl --action create-snapshot --dev
/dev/VolGroup01/mysqldata --size 10M --sname zrm3pItp8EAx4 --directory
/var/lib/mysql-zrm/ytt/20090415072018/ZRM_MOUNTS/zrm3pItp8EAx4
>/tmp/zrm3pItp8EAx4.log 2>&1 );system
>(/usr/share/mysql-zrm/plugins/lvm-snapshot.pl --action create-snapshot --dev
>/dev/VolGroup01/mysqlbinlog --size 10M --sname zrmeQrLWt8M2E --directory
>/var/lib/mysql-zrm/ytt/20090415072018/ZRM_MOUNTS/zrmeQrLWt8M2E
>>/tmp/zrmeQrLWt8M2E.log 2>&1 ); unlock tables;"
Wed Apr 15 07:20:25 2009: ytt:backup:INFO: File Position        Binlog_Do_DB
Binlog_Ignore_DB
mysql.000010    98
Wed Apr 15 07:20:25 2009: ytt:backup:INFO: Output of command: 'Locking tables
and creating snapshot' is {
Rounding up size to full physical extent 12.00 MB
   Logical volume "zrm3pItp8EAx4" created
}
Wed Apr 15 07:20:25 2009: ytt:backup:INFO: Output of command: 'Locking tables
and creating snapshot' is {
Rounding up size to full physical extent 12.00 MB
   Logical volume "zrmeQrLWt8M2E" created

Wed Apr 15 07:20:34 2009: ytt:backup:INFO:
innodb-data=/var/lib/mysql/data/ibdata1;
Wed Apr 15 07:20:34 2009: ytt:backup:INFO:
innodb-logs=/var/lib/mysql/binlog/ib_logfile*
Wed Apr 15 07:20:34 2009: ytt:backup:INFO: raw-databases-snapshot=test mysql t_girl
Wed Apr 15 07:20:34 2009: ytt:backup:INFO: PHASE END: Creating snapshot based backup
Wed Apr 15 07:20:34 2009: ytt:backup:INFO: PHASE START: Calculating backupsize & checksums
Wed Apr 15 07:20:34 2009: ytt:backup:INFO: next-binlog=mysql.000010
Wed Apr 15 07:20:34 2009: ytt:backup:INFO: backup-size=54.83 MB
Wed Apr 15 07:20:34 2009: ytt:backup:INFO: PHASE END: Calculating backup size& checksums
Wed Apr 15 07:20:34 2009: ytt:backup:INFO: read-locks-time=00:00:03
Wed Apr 15 07:20:34 2009: ytt:backup:INFO: flush-logs-time=00:00:00
Wed Apr 15 07:20:34 2009: ytt:backup:INFO: backup-time=00:00:16
Wed Apr 15 07:20:34 2009: ytt:backup:INFO: backup-status=Backup succeeded
Wed Apr 15 07:20:34 2009: ytt:backup:INFO: Backup succeeded

4. 以下是恢复过程：
[root@mysql01 ~]# mysql-zrm-restore --backup-set=ytt --source-directory=/var/lib/mysql-zrm/ytt/20090415072018

Wed Apr 15 07:21:04 2009: ytt:restore:INFO: Shutting down MySQL
Wed Apr 15 07:21:06 2009: ytt:restore:INFO: cp -p
"/var/lib/mysql-zrm/ytt/20090415072018//var/lib/mysql/binlog/ib_logfile0" "/var/lib/mysql/binlog"
Wed Apr 15 07:21:06 2009: ytt:restore:INFO: Restored innodb log '/var/lib/mysql/binlog/ib_logfile0'
Wed Apr 15 07:21:06 2009: ytt:restore:INFO: cp -p "/var/lib/mysql-zrm/ytt/20090415072018//var/lib/mysql/binlog/ib_logfile1"
"/var/lib/mysql/binlog"
Wed Apr 15 07:21:06 2009: ytt:restore:INFO: Restored innodb log '/var/lib/mysql/binlog/ib_logfile1'
Wed Apr 15 07:21:06 2009: ytt:restore:INFO: cp -p "/var/lib/mysql-zrm/ytt/20090415072018//var/lib/mysql/data/ibdata1"
"/var/lib/mysql/data"
Wed Apr 15 07:21:06 2009: ytt:restore:INFO: Restored innodb data file '/var/lib/mysql/data/ibdata1'

Wed Apr 15 07:21:10 2009: ytt:restore:INFO: Restored database from raw backup:mysql
Wed Apr 15 07:21:10 2009: ytt:restore:INFO: cp -p "/var/lib/mysql-zrm/ytt/20090415072018/t_girl/db.opt" "/var/lib/mysql/data/t_girl"
Wed Apr 15 07:21:10 2009: ytt:restore:INFO: cp -p "/var/lib/mysql-zrm/ytt/20090415072018/t_girl/http_auth.frm" "/var/lib/mysql/data/t_girl"
Wed Apr 15 07:21:10 2009: ytt:restore:INFO: Restored database from raw backup:t_girl
Wed Apr 15 07:21:10 2009: ytt:restore:INFO: Restore done in 7 seconds.

上一篇：Redhat as5.1 上NFS与MySQL的搭建

下一篇：NDB7.0 新特性

提问和评论都可以，用心的回复会被更多人看到评论

发布评论

相关文章

官方博客	全部文章	热门标签	班级博客
了解我们	网站地图	意见反馈

鸿蒙开发者社区	51CTO学堂
51CTO	软考资讯