逻辑卷备份mysql脚本_对MySQL进行逻辑卷备份与恢复示例代码

本文详细介绍了如何利用LVM逻辑卷管理在RedHatAS5.3环境下为MySQL数据库创建全库备份。通过pvcreate、vgcreate和lvcreate等命令创建逻辑卷,然后使用mysql-zrm进行备份,最后展示了恢复过程。备份过程中包括锁定表、创建快照、计算备份大小和校验和,恢复时则涉及关闭数据库、复制数据和日志文件。
摘要由CSDN通过智能技术生成

以下是关于用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="david.yang@actionsky.com"

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.

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值