Mysql备份系列（4）--lvm-snapshot备份mysql数据(全量+增量）操作记录

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） 审核权限并启动服务
[root@test-huanqiu ~]# 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

[root@test-huanqiu ~]# 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.

[root@test-huanqiu ~]# 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):

[root@test-huanqiu ~]# pvcreate /dev/vdc1
Device /dev/vdc1 not found (or ignored by filtering).
[root@test-huanqiu ~]# vgcreate vg0 /dev/vdc1
Volume group "vg0" successfully created
[root@test-huanqiu ~]# lvcreate -L +3G -n lv0 vg0
Logical volume "lv0" created.
[root@test-huanqiu ~]# mkfs.ext4 /dev/vg0/lv0
[root@test-huanqiu ~]# mkdir /var/lv0/
[root@test-huanqiu ~]# mount /dev/vg0/lv0 /var/lv0/
[root@test-huanqiu ~]# 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

[root@test-huanqiu ~]# 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
[root@test-huanqiu ~]# 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
[root@test-huanqiu ~]# /etc/init.d/mysql stop
Shutting down MySQL.... SUCCESS!

[root@test-huanqiu ~]# cd /data/mysql/data/
[root@test-huanqiu data]# tar -cf - . | tar xf - -C /var/lv0/

[root@test-huanqiu data]# umount /var/lv0/

[root@test-huanqiu data]# mount /dev/vg0/lv0 /data/mysql/data
[root@test-huanqiu 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

[root@test-huanqiu data]# ll -d /data/mysql/data
[root@test-huanqiu data]# ll -Z /data/mysql/data
[root@test-huanqiu data]# ll -Zd /data/mysql/data

需要注意的是:
当SElinux功能开启情况下，mysql数据库重启会失败，所以必须执行下面命令，恢复SElinux安全上下文.
[root@test-huanqiu data]# restorecon -R /data/mysql/data/
[root@test-huanqiu 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)

[root@test-huanqiu ~]# mkdir /var/snap1
[root@test-huanqiu ~]# lvcreate -s -L 2G -n snap1 /dev/vg0/lv0
Logical volume "snap1" created.

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

[root@test-huanqiu ~]# mount /dev/vg0/snap1 /var/snap1
[root@test-huanqiu 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

[root@test-huanqiu ~]# cd /var/snap1/
[root@test-huanqiu snap1]# mkdir -p /backup/mysql/data/
total 0

这里只备份部分库，比如huanqiu、ceshi。
可以将binlog日志文件备份到别处，也可以放在一起混合备份，但是建议分开备份或binlong日志文件放着不动。
[root@test-huanqiu snap1]# tar czf /backup/mysql/data/`date +%Y-%m-%d`dbbackup.tgz huanqiu ceshi
[root@test-huanqiu snap1]# ll /backup/mysql/data/
total 4
-rw-r--r--. 1 root root 376 Dec 3 08:39 2016-12-03dbbackup.tgz

[root@test-huanqiu ~]# umount /var/snap1/
[root@test-huanqiu ~]# 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
[root@test-huanqiu ~]# 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）读取日志&重演操作

[root@test-huanqiu ~]# /etc/init.d/mysql stop
Shutting down MySQL.... SUCCESS!

模拟数据丢失
[root@test-huanqiu ~]# cd /data/mysql/data/
[root@test-huanqiu data]# rm -rf ceshi huanqiu //或者是在mysql数据库中误删除

数据恢复操作
[root@test-huanqiu data]# tar xf /backup/mysql/data/2016-12-03dbbackup.tgz -C ./
[root@test-huanqiu data]# ll -d ceshi
drwx------. 2 mysql mysql 4096 Dec 3 08:19 ceshi
[root@test-huanqiu data]# ll -d huanqiu
drwx------. 2 mysql mysql 4096 Dec 3 08:23 huanqiu

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

[root@test-huanqiu 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)

原文：http://www.cnblogs.com/kevingrace/p/6129660.html