Build the Environment:
centos7
close Selinux:setenforce 0
Shut down firewall: systemctl stop firewalld
Build step:
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