Back up a mysql database using lvm-snapshot

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.