MySQL Backup series (4)--lvm-snapshot backup MySQL data (full + incremental) operation record

The three most commonly used backup tools for MySQL are mysqldump, xtrabackup (Innobackupex tools), and Lvm-snapshot snapshots.
The previous sections describe:
MySQL Backup series (1)--Summary of backup scenarios
MySQL Backup series (2)--mysqldump Backup (full + incremental) scheme operation record
MySQL Backup series (3)--innobackupex backup MySQL Big data (full + incremental) operation record

Needless to say, the following is the use of Lvm-snapshot snapshot backup of MySQL operation Records, only according to the use of my experiment described.

Operation Record:
In the following environments, this machine is a cloud host on OpenStack, creating a 30G cloud drive on OpenStack to mount to this machine and then making LVM logical volumes.

One, migration:
1) create a partition or save to another hard disk
2) Create PV, VG, LVM
3) format LV0
4) mount the LV to temp directory
5) Verify that the service is in the stop state
Span style= "COLOR: #0000ff" >6) migrate data to LV0
7) to re-mount LV0 to the MySQL database's home directory/var/lib/ MySQL
8) Audit permissions and start Services
[[email protected] ~]# fdisk-l
.........
disk/dev/vdc:32.2 GB, 32212254720 bytes
Heads, sectors/track, 62415 cylinders
Units = cylinders of 1008 * 516096 bytes
Sector size (logical/physical): bytes/512 bytes
I/o size (minimum/optimal): BYTES/5 Bytes
Disk identifier:0x00000000

[[email protected] ~]# FDISK/DEV/VDC //input p->n->p->1-> Enter and enter->w
.........
Command (M for help): P

disk/dev/vdc:32.2 GB, 32212254720 bytes
Heads, Sectors/track, 62415 cylinders
Units = Cylinders of 1008 * 516096 bytes
Sector size (logical/physical): bytes/512 bytes
I/O size (minimum/optimal): 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
Heads, Sectors/track, 62415 cylinders
Units = Cylinders of 1008 * 516096 bytes
Sector size (logical/physical): bytes/512 bytes
I/O size (minimum/optimal): 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 directory is/data/mysql/data, password is 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

It is important to note that:
When the SELinux function is turned on, the MySQL database restart will fail, so the SELinux security context must be restored by executing the following command.
[Email protected] data]# restorecon-r/data/mysql/data/
[[email protected] data]#/etc/init.d/mysql start
Starting MySQL ... success!

Second, Backup: (Recommended Binlog log and library files separately backup)
1) Lock table
2) Check the position number and record it to facilitate later recovery
3) Create snapshot
4) Solution Table
5) Mount Snapshot
6) Start copying snapshot data
7) Remove Snapshot

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

Only some libraries, such as Huanqiu, Ceshi, are backed up here.
You can back up Binlog log files Elsewhere, or you can mix backups together, but it is recommended that you separate backups or Binlong log files.
[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

Third, Recovery:
1) Stop MySQL
2) Delete the library file, but the Binlog log file is not deleted (because only the library file is backed up above)
3) Restore the database in tar mode
4) Start the service
5) Read Log & Replay operation

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

Analog data loss
[Email protected] ~]# cd/data/mysql/data/
[[email protected] data]# RM-RF Ceshi Huanqiu/or mistakenly deleted in MySQL database

Data recovery operations
[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 is insecure.

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

The final check, found that the deleted two library Huanqiu, Ceshi data has been normal recovery!
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| Information_schema |
| Ceshi |
| Huanqiu |
| MySQL |
| Performance_schema |
| Test |
+--------------------+
6 rows in Set (0.01 sec)

MySQL Backup series (4)--lvm-snapshot backup MySQL data (full + incremental) operation record