Back up a mysql database using lvm-snapshot

Source: Internet
Author: User

Back up a mysql database using lvm-snapshot

Prerequisites:

Transaction logs and data files must be on the same volume;

Before creating a snapshot volume, you must request the Global lock of MySQL. After the snapshot is created, manually release the lock;

After the global lock is requested, a log scroll is performed, and binary log files and positions are marked (manually );

 

1. Apply a read lock to the data, scroll the binary log file, and record the start time of the Current binary file:

2. Create snapshots;

3. Release the read lock:

4. Mount the snapshot, copy the data in the snapshot, and delete the snapshot:

[root@localhost mydata]# mount /dev/VG/mysql /mnt/ -o ro[root@localhost mydata]# mkdir /mysqlback[root@localhost mydata]# cp -a /mnt/ /mysqlback/20141121[root@localhost mydata]# umount /mnt/[root@localhost mydata]# lvremove /dev/VG/mysqlDo you really want to remove active logical volume mysql? [y/n]: yLogical volume "mysql" successfully removed

5. When inserting data into a table, the data changes and the binary log is used to restore the data.

mysql> INSERT INTo newtb values('jack');Query OK, 1 row affected (0.01 sec)mysql> select * from newtb;+------+| Name |+------+| tom || jack |+------+

2 rows in set (0.00 sec)

6. Export a file based on the recorded binary log Start Time

[Root @ localhost mydata] # mysqlbinlog -- start-position = 107 mysql-bin.000011>/tmp/20141121. SQL

7. Stop the database, delete data, and try to restore data.

8. Start the mysql service and check whether the data is restored:

9. Use binary logs to restore users created later:

Mysql> source/tmp/20141121. SQL;

 

Summary:

Backup process:

1. Apply a read lock to the MySQL database to prevent data inconsistency after backup due to user insertion during the backup process

2. roll back the log to make a complete backup of the data in the current status, and then restore the data with binary logs.

3. Use the show master status Command to record the log Start Time

4. Use the lvcreate command to take a snapshot of lv

5. Read lock Removal

 

Recovery Process:

6. Mount the created lv snapshot to the directory and copy the data in it.

7. Unmount and delete snapshots

8. copy the data copied from the snapshot to/data/mydata

9. Use mysqlbinlog in combination with the previous recorded start time to export the required binary data into a xx. SQL File

10. Importing SQL files to the database completes the rectification and recovery process.

Related Article

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: info-contact@alibabacloud.com 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.