lvm snapshot mysql_MySQL物理备份 lvm-snapshot

正常安装MySQL：1. 安装系统2. 准备LVM，例如 /dev/vg_back/lv-mysql，mount /usr/local/mysql3. 源码安装MySQL到 /usr/local/mysql

可选操作：　将现在的数据迁移到LVM1. 准备lvm及文件系统#lvcreate -L 2G -n lv-mysql vg_back#mkfs.ext4 /dev/vg_back/lv-mysql

2. 将数据迁移到LVM#service mysqld stop#mount /dev/vg_back/lv-mysql /u01/ //临时挂载点#rsync -va /usr/local/mysql/ /u01/ //将MySQL原数据镜像到临时挂载点

#umount /u01/#mount /dev/vg_back/lv-mysql /usr/local/mysql //加入fstab开机挂载#df -Th

/dev/mapper/vg_back-lv-mysql ext4 2.0G 274M 1.7G 15% /usr/local/mysql#service mysqld start

手动基于LVM快照实现备份：1. 加锁

mysql>flush table with read lock;2.创建快照#lvcreate -L 500M -s -n lv-mysql-snap /dev/vg_back/lv-mysql#mysql -uroot -p123 -e 'show master status' > /backup/`date +%F`_position.txt

或者

mysql>show master status;+-------------------+----------+--------------+------------------+-------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+-------------------+----------+--------------+------------------+-------------------+

| mysqld-bin.00003 | 135 | | | |

+-------------------+----------+--------------+------------------+-------------------+

3. 释放锁

mysql>unlock tables;4. 从快照中备份#mount -o ro /dev/vg_back/lv-mysql-snap /u01/#mkdir /backup/`date +%F`#rsync -a /u01/ /backup/2015-07-02/

5. 移除快照#umount /u01/#lvremove -f /dev/vg_back/lv-mysql-snap

脚本+Cron完成：#!/bin/bash#LVM backmysql...

back_dir=/backup/`date +%F`

[-d $back_dir ] || mkdir -p $back_dir

mysql-uroot -p123 -e 'flush table with read lock'lvcreate-L 500M -s -n lv-mysql-snap /dev/vg_back/lv-mysql

mysql-uroot -p123 -e 'show master status' |grep mysql > $back_dir/position.txt

mysql-uroot -p123 -e 'flush logs'mysql-uroot -p123 -e 'unlock tables'mount-o ro /dev/vg_back/lv-mysql-snap /u01

rsync-a /u01/$back_dirif [ $? -eq 0 ];then

umount/u01/lvremove-f /dev/vg_back/lv-mysql-snap

fi===============================================================mylvmbackup

功能：利用LVM快照实现物理备份，即LVM快照备份的自动版

安装perl模块1. 在线安装

http://www.lenzg.net/mylvmbackup

它依赖于perl 模块，可用以下命令安装

perl-MCPAN -e 'install Config::IniFiles'

2. 离线安装#rpm -ivh mylvmbackup-0.16-0.noarch.rpm

warning: mylvmbackup-0.16-0.noarch.rpm: Header V4 DSA/SHA1 Signature, key ID b27291f2: NOKEY

error: Failed dependencies:

perl(Config::IniFiles)is needed by mylvmbackup-0.16-0.noarch

perl(Date::Format)is needed by mylvmbackup-0.16-0.noarch

perl(File::Copy::Recursive)is needed by mylvmbackup-0.16-0.noarch

解决：#yum -y localinstall atrpms-77-1.noarch.rpm perl-File-Copy-Recursive-0.38-1.el6.rfx.noarch.rpm perl-IO-stringy-2.110-1.2.el6.rfx.noarch.rpm perl-Config-IniFiles-2.56-1.el6.rf.noarch.rpm

安装mylvmbackup软件包#yum -y install mylvmbackup-0.15-0.noarch.rpm 解决依赖关系perl-TimeDate

备份方法一：#mylvmbackup --user=root --password=123 --host=localhost --mycnf=/etc/my.cnf --vgname=vg_back --lvname=lv-mysql --backuptype=tar --lvsize=100M --backupdir=/backup

#tar xf backup-20140903_000236_mysql.tar.gz#ls

backup backup-cnf-20150702_000236_mysql

backup-20150702_000236_mysql.tar.gz backup-pos

备份方法二：#vim /etc/mylvmbackup.conf

[mysql] #连接数据库配置

user=root

password=123456host=localhost

port=3306socket=/tmp/mysql.sock

mycnf=/etc/my.cnf

[lvm]#LVM逻辑卷的配置

vgname=vg_server #卷组名称

lvname=lv_mysql #逻辑卷名称

backuplv=mysql_snap #快照卷名称

lvsize=500M

[fs]#文件系统配置

xfs=0

mountdir=/var/tmp/mylvmbackup/mnt/ #挂载目录

backupdir=/backup #备份目录，也可以备份到行程主机

[misc] #定义备份选项

backuptype=tar #定义备份的类型

backupretention=0

prefix=backup #定义备份文件名前缀

suffix=_mysql #定义备份文件名后缀

tararg=cvf #定义tar参数，默认为cvf

tarfilesuffix=.tar.gz #定义备份文件后缀名格式

datefmt=%Y%m%d_%H%M%S #定义备份文件名时间戳格式

keep_snapshot=0 #是否保留snaphot

keep_mount=0 #是否卸载snaphot

quiet=0 #定义记录日志类型

注释：其他配置保持输入即可

然后直接执行mylvmbackup即可