Backup restore for MySQL based on the LV snapshot

Source: Internet
Author: User
Tags db2 flush uuid import database

Build the Environment:

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
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  
# 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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.