LVM快照與mysql備份

LVM snapshot

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

#!/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