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

最新推荐文章于 2021-02-05 19:48:12 发布

尔瓜

最新推荐文章于 2021-02-05 19:48:12 发布

阅读量82

点赞数

文章标签：逻辑卷备份mysql脚本

本文链接：https://blog.csdn.net/weixin_32296949/article/details/113721103

版权

本文介绍了如何利用LVM逻辑卷管理在RedHatAS5.3环境中为MySQL数据库创建全量备份。首先，通过fdisk创建逻辑卷，然后用LVM创建用于存放数据和日志的逻辑卷。接着，配置mysql-zrm进行基于LVM快照的备份，并展示了备份过程的日志。最后，演示了如何使用mysql-zrm进行数据库恢复操作，包括恢复InnoDB数据和日志文件。

摘要由CSDN通过智能技术生成

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.

备份恢复mysqlMySQL备份与恢复

尔瓜

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
逻辑卷备份mysql脚本_对MySQL进行逻辑卷备份与恢复

ZRM 我之前我介绍过，这里就不多少了。以下是关于用mysql-zrm 来测试基于LVM 逻辑卷管理的数据库全库备份。我这里用的是SUN 的VBOX 虚拟机来做的测试，基于Red Hat AS 5.3。1. 先建立逻辑卷。fdisk 我就不介绍了，这里演示下怎么用创建逻辑卷以及怎么用LVM来备份MySQL 数据库。[root@mysql01 ytt]# pvcreate /dev/hdb5Phy...
复制链接

扫一扫