Mysql備份系列（4）--lvm-snapshot備份mysql資料(全量+增量）操作記錄

標籤：日誌   top   blocks   mod   min   remove   pvc   oca   tor   

 

Mysql最常用的三種備份工具分別是mysqldump、Xtrabackup（innobackupex工具）、lvm-snapshot快照。
前面分別介紹了：
Mysql備份系列（1）--備份方案總結性梳理
Mysql備份系列（2）--mysqldump備份（全量+增量）方案操作記錄
Mysql備份系列（3）--innobackupex備份mysql大資料(全量+增量）操作記錄

廢話不多說，下面即是使用lvm-snapshot快照方式備份mysql的操作記錄，僅依據本人實驗中使用而述.

操作記錄：
如下環境，本機是在openstack上開的雲主機，在openstack上建立一個30G的雲硬碟掛載到本機，然後製作lvm邏輯卷。

一、遷移：
1） 建立一個分區或儲存到另一塊硬碟上面
2） 建立PV、VG、LVM
3） 格式化 LV0
4） 掛載LV到臨時目錄
5） 確認服務處於stop狀態
6） 將資料移轉到LV0
7） 重新掛載LV0到mysql資料庫的主目錄/var/lib/mysql
8） 審核許可權並啟動服務
[[email protected] ~]# fdisk -l
.........
Disk /dev/vdc: 32.2 GB, 32212254720 bytes
16 heads, 63 sectors/track, 62415 cylinders
Units = cylinders of 1008 * 512 = 516096 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x00000000

[[email protected] ~]# fdisk /dev/vdc                            //依次輸入p->n->p->1->斷行符號->斷行符號->w
.........
Command (m for help): p

Disk /dev/vdc: 32.2 GB, 32212254720 bytes
16 heads, 63 sectors/track, 62415 cylinders
Units = cylinders of 1008 * 512 = 516096 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x343250e4

Device Boot Start End Blocks Id System

Command (m for help): n
Command action
e extended
p primary partition (1-4)
p
Partition number (1-4): 1
First cylinder (1-62415, default 1):
Using default value 1
Last cylinder, +cylinders or +size{K,M,G} (1-62415, default 62415):
Using default value 62415

Command (m for help): w
The partition table has been altered!

Calling ioctl() to re-read partition table.
Syncing disks.

[[email protected] ~]# fdisk /dev/vdc

WARNING: DOS-compatible mode is deprecated. It‘s strongly recommended to
switch off the mode (command ‘c‘) and change display units to
sectors (command ‘u‘).

Command (m for help): p

Disk /dev/vdc: 32.2 GB, 32212254720 bytes
16 heads, 63 sectors/track, 62415 cylinders
Units = cylinders of 1008 * 512 = 516096 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x343250e4

Device Boot Start End Blocks Id System
/dev/vdc1 1 62415 31457128+ 5 Extended

Command (m for help):

[[email protected] ~]# pvcreate /dev/vdc1
Device /dev/vdc1 not found (or ignored by filtering).
[[email protected] ~]# vgcreate vg0 /dev/vdc1
Volume group "vg0" successfully created
[[email protected] ~]# lvcreate -L +3G -n lv0 vg0
Logical volume "lv0" created.
[[email protected] ~]# mkfs.ext4 /dev/vg0/lv0
[[email protected] ~]# mkdir /var/lv0/
[[email protected] ~]# mount /dev/vg0/lv0 /var/lv0/
[[email protected] ~]# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/VolGroup00-LogVol00
8.1G 6.0G 1.7G 79% /
tmpfs 1.9G 0 1.9G 0% /dev/shm
/dev/vda1 190M 37M 143M 21% /boot
/dev/mapper/vg0-lv0 2.9G 4.5M 2.8G 1% /var/lv0

[[email protected] ~]# lvs
LV VG Attr LSize Pool Origin Data% Meta% Move Log Cpy%Sync Convert
LogVol00 VolGroup00 -wi-ao---- 8.28g
LogVol01 VolGroup00 -wi-ao---- 1.50g
lv0 vg0 -wi-a----- 3.00g

mysql的資料目錄是/data/mysql/data,密碼是123456
[[email protected] ~]# ps -ef|grep mysql
mysql 2066 1286 0 07:33 ? 00:00:06 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql/ --datadir=/data/mysql/data --plugin-dir=/usr/local/mysql//lib/plugin --user=mysql --log-error=/data/mysql/data/mysql-error.log --pid-file=/data/mysql/data/mysql.pid --socket=/usr/local/mysql/var/mysql.sock --port=3306
root 2523 2471 0 07:55 pts/1 00:00:00 grep mysql
[[email protected] ~]# /etc/init.d/mysql stop
Shutting down MySQL.... SUCCESS!

[[email protected] ~]# cd /data/mysql/data/
[[email protected] data]# tar -cf - . | tar xf - -C /var/lv0/

[[email protected] data]# umount /var/lv0/

[[email protected] data]# mount /dev/vg0/lv0 /data/mysql/data
[[email protected] data]# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/VolGroup00-LogVol00
8.1G 6.0G 1.7G 79% /
tmpfs 1.9G 0 1.9G 0% /dev/shm
/dev/vda1 190M 37M 143M 21% /boot
/dev/mapper/vg0-lv0 2.9G 164M 2.6G 6% /data/mysql/data

[[email protected] data]# ll -d /data/mysql/data
[[email protected] data]# ll -Z /data/mysql/data
[[email protected]-huanqiu data]# ll -Zd /data/mysql/data

需要注意的是:
當SElinux功能開啟情況下，mysql資料庫重啟會失敗，所以必須執行下面命令，恢複SElinux安全上下文.
[[email protected] data]# restorecon -R /data/mysql/data/
[[email protected] data]# /etc/init.d/mysql start
Starting MySQL... SUCCESS!

二、備份： (建議binlog日誌和庫檔案分開備份)
1）鎖表
2）查看position號並記錄，便於後期恢複
3）建立snapshot
4）解表
5）掛載snapshot
6）開始拷貝snapshot資料
7）移除快照

mysql> flush tables with read lock
Query OK, 0 rows affected (0.00 sec)

mysql> show master status ;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000014 | 1775 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

[[email protected] ~]# mkdir /var/snap1
[[email protected] ~]# lvcreate -s -L 2G -n snap1 /dev/vg0/lv0
Logical volume "snap1" created.

mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

[[email protected] ~]# mount /dev/vg0/snap1 /var/snap1
[[email protected] snap1]# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/VolGroup00-LogVol00
8.1G 6.0G 1.7G 79% /
tmpfs 1.9G 0 1.9G 0% /dev/shm
/dev/vda1 190M 37M 143M 21% /boot
/dev/mapper/vg0-snap1
2.9G 164M 2.6G 6% /var/snap1

[[email protected] ~]# cd /var/snap1/
[[email protected] snap1]# mkdir -p /backup/mysql/data/
total 0

這裡只備份部分庫，比如huanqiu、ceshi。
可以將binlog記錄檔備份到別處，也可以放在一起混合備份，但是建議分開備份或binlong記錄檔放著不動。
[[email protected] snap1]# tar czf /backup/mysql/data/`date +%Y-%m-%d`dbbackup.tgz huanqiu ceshi
[[email protected] snap1]# ll /backup/mysql/data/
total 4
-rw-r--r--. 1 root root 376 Dec 3 08:39 2016-12-03dbbackup.tgz

[[email protected] ~]# umount /var/snap1/
[[email protected] ~]# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/VolGroup00-LogVol00
8.1G 5.8G 1.9G 77% /
tmpfs 1.9G 0 1.9G 0% /dev/shm
/dev/vda1 190M 37M 143M 21% /boot
[[email protected] ~]# lvremove /dev/vg0/snap1
Do you really want to remove active logical volume snap1? [y/n]: y
Logical volume "snap1" successfully removed

三、恢複：
1）停止mysql
2）刪除庫檔案，但是binlog記錄檔不刪除（因為上面備份的只是庫檔案）
3）Tar方式恢複資料庫
4）啟動服務
5）讀取日誌&重演操作

[[email protected] ~]# /etc/init.d/mysql stop
Shutting down MySQL.... SUCCESS!

類比資料丟失
[[email protected] ~]# cd /data/mysql/data/
[[email protected] data]# rm -rf ceshi huanqiu //或者是在mysql資料庫中誤刪除

資料恢複操作
[[email protected] data]# tar xf /backup/mysql/data/2016-12-03dbbackup.tgz -C ./
[[email protected] data]# ll -d ceshi
drwx------. 2 mysql mysql 4096 Dec 3 08:19 ceshi
[[email protected] data]# ll -d huanqiu
drwx------. 2 mysql mysql 4096 Dec 3 08:23 huanqiu

[[email protected] data]# mysqlbinlog mysql-bin.000014 --start-position=1775 | mysql -p123456
Warning: Using a password on the command line interface can be insecure.

[[email protected] data]# /etc/init.d/mysql start
Starting MySQL.. SUCCESS!

最後檢查下，發現刪除的兩個庫huanqiu、ceshi的資料已經正常恢複了！
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| ceshi |
| huanqiu |
| mysql |
| performance_schema |
| test |
+--------------------+
6 rows in set (0.01 sec)

Mysql備份系列（4）--lvm-snapshot備份mysql資料(全量+增量）操作記錄