MySQL backup and recovery (2) Almost Hot Backup Based on LVM

LVM (LogicalVolumeManagement logical volume Management) achieves almost hot backup principle: the snapshot volume of LVM can be used to save the instantaneous state of the database at a certain time point. You only need to back up the snapshot at that time. LVM snapshot principle: After lvm creates a snapshot volume for a volume, the snapshot volume serves as another access point for the original volume.

LVM (Logical Volume Management) achieves almost hot standby principle: the snapshot Volume of LVM can be used to save the instantaneous state of the database at a certain time point, you only need to back up the snapshot at that time. LVM snapshot principle: After lvm creates a snapshot volume for a volume, the snapshot volume serves as another access point for the original volume.

LVM (Logical Volume Management) achieves almost hot standby principle: the snapshot Volume of LVM can be used to save the instantaneous state of the database at a certain time point, you only need to back up the snapshot at that time.

LVM snapshot principle: After lvm creates a snapshot volume for a volume, the snapshot volume is used as another original volume.Access portalWhen the data of the original volume changesCopy the content of the original volume to the snapshot volume first.And then the original volume data will be modified. That is, the reason why the capacity of the snapshot volume should be larger than that of the database during the whole backup processChange volume. Therefore, after the backup is complete, the snapshot volume can be removed. Its function is to copy the status of the changed data snapshot during the backup process to a copy.

Prerequisites for using lvm to back up mysql:Data Files and transaction log files should be on the same volumeOtherwise, the snapshot volume data is inconsistent with the transaction log time point, causing database disorder after recovery.

The principle is clear, and the operation is relatively simple:

Of course, you must first prepare the lvm volume and ensure that mysql's datadir and innodb_log_group_home_dir are on this volume.

/* CREATE a new TABLE tb1 and insert some data for testing */MariaDB [hellodb]> create table tb1 (id int not null AUTO_INCREMENT primary key, Name VARCHAR (30) not null, Gender ENUM ('F', 'M', 'O') not null); Query OK, 0 rows affected (0.11 sec) MariaDB [hellodb]> DESC tb1; + -------- + upper + ------ + ----- + --------- + -------------- + | Field | Type | Null | Key | Default | Extra | + -------- + ----------------- + ------ + ----- + --------- + ---------------- + | id | int (11) | NO | PRI | NULL | auto_increment | Name | varchar (30) | NO | NULL | Gender | enum ('F', 'M', 'O ') | NO | NULL | + -------- + ----------------- + ------ + ----- + --------- + ---------------- + 3 rows in set (0.00 sec) MariaDB [hellodb]> insert into tb1 (Name, gender) VALUES ('bob', 'M'), ('tina ', 'F'), ('axx', 'O'); Query OK, 3 rows affected (0.03 sec) Records: 3 Duplicates: 0 Warnings: 0/* Add a read lock to all tables before backup to check the current binary log location */MariaDB [(none)]> flush tables with read lock; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> SHOW MASTER STATUS; + metric + ---------- + -------------- + ---------------- + | File | Position | Binlog_Do_DB | usage | + usage + ---------- + -------------- + usage + | mysql-bin.000007 | 245 | + usage + -------- + -------------- + ------------------ + 1 row in set (0.01 sec)

/* Open another terminal. Do not exit the mysql session. */[root @ node1 ~] # Mkdir/backup/mydata-'date + % F'/* You can also save the current binary location to the file for viewing during recovery */[root @ node1 ~] # Mysql-e 'show master status; '>/backup/mydata-2014-07-29/bin-pos-'date + % F-% t'/* Ensure available vg */[root @ node1 ~] # Vgs VG # PV # LV # SN Attr VSize VFree myvg 2 1 0 wz -- n-10.02g 7.02g vg0 3 1 0 wz -- n-29.29g 0/* Create a snapshot volume, -L | -- size (LogicalVolumeSize) specifies the snapshot volume size, which cannot be smaller than the change volume. Otherwise, the snapshot volume will crash and the backup will fail; -n | -- name specifies the snapshot volume name;-p | -- permission specifies the snapshot volume permission r read-only, -s -- snap-shot/dev/myvg/mylv specifies to create a snapshot volume for/dev/myvg/mylv. If the database server is busy at this time, you need to wait for the submitted logs to be flushed to the disk. It may take some time */[root @ node1 ~] # Lvcreate-L 100 M-n mydata-snap-p r-s/dev/myvg/mylv Logical volume "mydata-snap" created [root @ node1 ~] # Mkdir/mnt/snap [root @ node1 ~] # Mount/dev/myvg/mydata-snap/mnt/snap/mount: block device/dev/mapper/myvg-mydata -- snap is write-protected, mounting read-only [root @ node1 ~] # Cd/mnt/snap/[root @ node1 snap] # lsdata lost + found [root @ node1 snap] # ls data/aria_log.00000001 ib_logfile0 mysql-bin.000001 mysql-bin.000005 node1.bob.org. erraria_log_control ib_logfile1 mysql-bin.000002 mysql-bin.000006 node1.bob.org. pidhellodb mydb mysql-bin.000003 mysql-bin.000007 performance_schemaibdata1 mysql mysql-bin.000004 mysql-bin.index test [root @ node1 snap] # cp-ap/mnt/snap/data/backup/mydata-2014-07-29/[root @ node1 snap] # ls/ backup/mydata-2014-07-29/data/aria_log.00000001 ib_logfile0 mysql-bin.000001 mysql-bin.000005 node1.bob.org. erraria_log_control ib_logfile1 mysql-bin.000002 mysql-bin.000006 node1.bob.org. pidhellodb mydb mysql-bin.000003 mysql-bin.000007 performance_schemaibdata1 mysql mysql-bin.000004 mysql-bin.index test

Back to mysql session after the backup is complete, unlock the table

MariaDB [(none)]> UNLOCK TABLES;Query OK, 0 rows affected (0.01 sec)

In this way, a mysql Backup Based on lvm snapshot is completed, and then Incremental backup and recovery can be performed based on binary logs. You can refer to the previous article MySQL backup and recovery (1) mysqldump