LVM快照与mysql备份

LVM snapshot

http://tldp.org/HOWTO/LVM-HOWTO/snapshots_backup.html 

A snapshot volume is a special type of volume that presents all the data that was in the volume at the time the snapshot was created.

LVM1只支持read-only，LVM2 支持read-write;

LVM1通过exception table记录变化的block，如果原始卷有block被更新，首先将其image复制到快照，然后异常表标志该块为copied，最后新数据才被写入原始卷；

LVM2支持read-write，如果快照卷数据块发生更新，则其在异常表中被标为used，以后不会再从原始卷复制；

限制

LVM1不支持原始卷resize，LVM2只支持原始卷增长不支持收缩；

快照卷一旦写满就会自动被删除，不再可用；

LVM快照备份大致步骤

1 创建快照卷  lvcreate –L1G –s –n dbbackup /dev/ops/databases，为/dev/ops/databases创建快照卷dbbackup；

针对mysql，需要先执行flush tables with read lock, lvcreate执行完毕后再执行unlock tables;

2 加载快照卷  mkdir /mnt/ops;  mount /dev/ops/dbbackup /mnt/ops/dbbackup

3 复制数据到快照卷 可采用tar或rsync，tar –cf /dev/rmt0  /mnt/ops/dbbackup

4 卸载快照卷  复制完成后应立即卸载快照卷避免影响性能  umount /mnt/ops/dbbackup; lvremove /dev/ops/dbbackup

如何创建LVM http://space.itpub.net/15480802/viewspace-738660

http://www.mysqlperformanceblog.com/2006/08/21/using-lvm-for-mysql-backup-and-replication-setup/  
可使用mylvmbackup工具对mysql数据文件进行快照备份，大致流程为：
1
执行 flush tables with read lock读锁定所有表，该语句会等待现有sql运行完毕(包括select)
2
对mysql数据目录创建快照，LVM快照加载于临时目录，默认使用tar备份，也可用rsync/rsnap
lvcreate -L 16G -s -n dbbackup /dev/Main/Data
--为/Main/data创建名为dbbackup的快照，16G的undo
3
解锁表unlock tables
解锁前show master status记录下当前binlog位置
4
加载该文件系统 mount /dev/Main/dbbackup /mnt/backup
将mysql数据文件复制到此目录，然后unmount /mnt/backup
5
移除快照lvremove -f /dev/Main/dbbackup

如果想依据此快照系统创建slave，只需多执行如下几步
6
将快照数据恢复到slave目录，然后启动mysql
7
使用change master to将其指向master的特定binlog位置
change master to master_host="master", master_user="user", master_password="password", master_log_file="host-bin.000335", master_log_pos=401934686;
8
启动slave

LVM快照备份
优点
速度快--在OS级别复制文件
在线备份--不影响应用
劣势
需要root权限/恢复时间不好控制

LVM采用写时复制

http://www.mysqlperformanceblog.com/2012/02/23/faster-point-in-time-recovery-with-lvm2-snaphots-and-binary-logs/ LVM快照可用于point-in time恢复，通过lvconvert --merge实现
对于Innodb，其redo log要和data位于同一logcial volume；
假定mysql-data用于存储data，mysql-logs存储binlog，最新快照为mysql-data-201202230135

模拟案例
1
创建快照
[root@sb logs]# /root/bin/lvmsnap.sh snapshot
Taking a new snapshot ..
done
Trimming excess snapshots ..
  Logical volume "mysql-data-201202230135" successfully removed
done
  LV                      VG       Attr   LSize  Origin     Snap%  Move Log Copy%  Convert
  lv_root                 VolGroup -wi-ao  5.54g                                          
  lv_swap                 VolGroup -wi-ao  1.97g                                          
  mysql-data              sb       owi-ao 20.00g                                          
  mysql-data-201202230150 sb       swi-a-  5.00g mysql-data   0.00                        
  mysql-data-201202230153 sb       swi-a-  5.00g mysql-data   0.00                        
  mysql-data-201202230155 sb       swi-a-  5.00g mysql-data   0.00                        
  mysql-data-201202230157 sb       swi-a-  5.00g mysql-data   0.00                        
  mysql-logs              sb       -wi-ao 10.00g
2
执行误操作
mysql> delete from salaries where emp_no = 10001;
Query OK, 17 rows affected (0.15 sec)
mysql> drop table salaries; -- 此表被误删除
Query OK, 0 rows affected (0.49 sec)
mysql> alter table employees add column age smallint unsigned not null default 0;
Query OK, 300024 rows affected (13.28 sec)

3
现在需要恢复数据库，跳过drop table salaries语句
查看生成快照时的辅助信息，定位binlog
[root@sb logs]# cat mysql-data-201202230157-binlog-info
File    Position       Binlog_Do_DB   Binlog_Ignore_DB
mysql-bin.000022       336796712

[root@sb logs]# mysqlbinlog --verbose --base64-output=decode-rows --start-position 336796712 mysql-bin.000022
...
# at 336797160
#120223  1:59:55 server id 1  end_log_pos 336797275  Query   thread_id=47   exec_time=1        error_code=0
SET TIMESTAMP=1329980395/*!*/;
DROP TABLE `salaries` /* generated by server */

停止mysql服务器 /etc/init.d/mysql stop
卸载数据目录 umount /mysql/data
执行lvconvert  --merge /dev/sb/mysql-data-201202230157
重新加载数据目录mount /mysql/data
启动mysql /etc/init.d/mysql start
跳过drop操作--从336796712开始运行binglog，跳过336797160位置的drop
mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
...
| mysql-bin.000022 | 336797725 |
| mysql-bin.000023 |       107 |
+------------------+-----------+
[root@sb logs]# mysqlbinlog --verbose --base64-output=decode-rows --start-position 336796712 --stop-position 336797133 mysql-bin.000022 | mysql
[root@sb logs]# mysqlbinlog --verbose --base64-output=decode-rows --start-position 336797275 mysql-bin.000022 | mysql

https://github.com/dotmanila/mootools/blob/master/lvmsnap.sh

#!/bin/bash

# How many snapshots to keep

SNAPCNT=4

# Default sizes of snapshots

SNAPSZE=5G

# Volume group containing MySQL data

MYSQLVG=sb

# MySQL logical volume name

MYSQLLV=mysql-data

# MYSQL data directory mount point

MYSQLDD=/mysql/data

# Temporary file

TMPFILE=/tmp/lvmsnap-$$.tmp

# Binary logs directory

BLOGDIR=/mysql/logs

# Commands are snapshot, merge, list

CMD=$1

RESTRSNAP=$2

function trim {

  echo 'Trimming excess snapshots .. '

  lvs --noheadings -o lv_path|grep 'sb/mysql-data-'|head -n-4|awk '{print $1}'|xargs lvremove -f 

  ls $BLOGDIR|grep 'mysql-data-'|head -n-4|xargs rm -rf 

  echo 'done'

  echo

  lvs

  echo

}

function snap {

  echo 'Taking a new snapshot .. '

  snap="$MYSQLLV-`date +%Y%m%d%H%M`"

  mysql <

FLUSH TABLES WITH READ LOCK;

\! mysql -e 'SHOW MASTER STATUS' > $BLOGDIR/${snap}-binlog-info

\! lvcreate --size=$SNAPSZE --snapshot --name $snap /dev/${MYSQLVG}/${MYSQLLV} > /dev/null 2>&1

UNLOCK TABLES;

EOD

  echo 'done'

  echo

  trim

}

function restore {

  if [ -z $RESTRSNAP ]; then

    echo 'Invalid snapshot requested.'

    echo

    exit 1

  fi

  rstr=$(lvs --noheadings -o lv_path|grep "sb/${MYSQLLV}-${RESTRSNAP}")

  if [ -z $rstr ]; then

    echo 'Snapshot not found!'

    echo

    exit 1

  fi

  # Shutdown MySQL

  echo 'Shutting down MYSQL ..'

  mysqladmin shutdown

  # Sleep some 120 seconds to let MySQL shutdown

  sleep 10

  kltmout=110

  while [ $kltmout -gt 0 ]; do

    RESPONSE=$(mysqladmin ping 2>&1)

    echo "$RESPONSE" | grep 'failed' 2>&1 && break

    sleep 1

    let kltmout=${kltmout}-1

  done

  if [ $kltmout -eq 0 ]; then

    echo "Timeout error occurred trying to shutdown MySQL."

    exit 1

  fi

  umount $MYSQLDD

  lvconvert --merge $rstr

  mount $MYSQLDD

  service mysql start

  echo "${rstr} successfully restored!"

  echo

}

case $CMD in

  'snapshot')

    snap;;

  'restore')

    restore;;

esac

https://github.com/nmilford/scripts/blob/master/MySQL/makeMysqlSnapshot.sh

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/15480802/viewspace-762630/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/15480802/viewspace-762630/