Backup restore for MySQL based on the LV snapshot

Build the Environment:

centos7  
  close Selinux:setenforce 0  
  Shut down firewall: systemctl stop firewalld  

Build step:

1. Creating a logical Volume

# Pvcreate/dev/sda6
# vgcreate Vg0/dev/sda6
# lvcreate-l 5g-n lv_mysql vg0
# lvcreate-l 3g-n lv_binlog vg0
# Mkfs.xfs/dev/vg0/lv_mysql
# Mkfs.xfs/dev/vg0/lv_binlog
# Mkdir-pv/data/{mysql,backup} Create mount point
# Vim/etc/fstab Permanent Mount
 UUID="25f718e7-d795-41f7-a3ac-19288360ec0e" /data/mysql/xfs defaults 0  0 
 UUID="1848fe51-9b42-45fe-a66f-02ebc2694a52" /data/binlog/xfs defaults 0  0 

# Mount-a re-mount the device in the/etc/fstab file
# chown-r Mysql.mysql/data Modify the file affiliation so that the MySQL user has write permissions

2. Modify the MySQL configuration file and create a snapshot

# vim/etc/my.cnf Modify the MySQL configuration file
[Mysqld]
 datadir=/data/mysql
 log_bin=/data/binlog/mysql-bin mysql-bin is the prefix for binary log files
# systemctl Restart mariadb
# mysql< Hellodb_innodb.sql Import Database for post-validation

Description: in a production environment to ensure that snapshot creation is successful, you need to ensure that the snapshot space is large enough

mysql> flush tables with read lock; Lock database locks to avoid data modifications during snapshot execution
mysql> flush logs; Rolling logs, using the new binary log
mysql> show master logs; View binary log list information for binary recovery use
# lvcreate-l 1g-n lv_mysql_snap-s-P r/dev/vg0/lv_mysql Create snapshot (-S option,-p option specified as read-only)
mysql> unlock tables; Unlock
mysql> CREATE DATABASE db1; User creates new data after unlocking (changes in this section need to be restored using binary logs)
# Mount-o Nouuid, norecovery/dev/vg0/lv_mysql/snap/mnt Note Specify nouuid and Norecovery
# cp-a/mnt/*/backup/will mount the snapshot file under/mnt to the/backup path to save
# umount/mnt Unmount snapshot mount, otherwise the database's update operation to write to the snapshot affects the database write efficiency
# lvremove/dev/vg0/lv_mysql_snap Remove Snapshot

Description : simulating a database crash scenario

mysql> CREATE DATABASE DB2; Action to create library before crash
mysql> Use DB2
mysql> drop table teachers; Operation to delete table before crash
# rm-rf/data/mysql/* Delete data file simulate database crash
# systemctl Stop mariadb

3. Restore the Database

# cp-av/backup/*/data/mysql/* Copy the snapshot backup to the Data folder
# systemctl Start mariadb
mysql> flush tables with read lock; Lock the database to prevent data changes during recovery
# mysqlbinlog--start-position=245 mysql-bin.000004 >/backup/bin.sql
# Mysqlbinlog mysql-bin.000005 >>/backup/bin.sql The above two steps are export binary logs for recovery

Description : to ensure that the user cannot modify the database when resuming the binary log, you need to turn off network snooping

# vim/etc/my.cnf  
 skip_networking    
 # systemctl restart mariadb  
 # MySQL </backup/bin.sql   Use binary logs to recover data after a snapshot backup 

4. Delete the skip_networking in the configuration file and restart the service to verify that the database is back to normal

Backup restore for MySQL based on the LV snapshot