MySQL High-performance backup solution to data uninterrupted access (LVM Snapshot method Backup)

MySQL LVM snapshot backup features:

1, in most cases, this approach is almost a hot standby. It does not need to shut down the service, just set a read-only or similar limit.

2, support all local disk based storage engine, such as MyISAM, InnoDB and BDB, also support solid, primext and faction.

3, the fastest backup speed, because you only need to copy the relevant binary data files.

4, because only simple copy files, so the server overhead is very low.

5, save a variety of ways, you can back up to tape, FTP server, NFS server or other network server, and the use of various network backup software to backup.

It's easy to do this, in the final analysis is copying files.

6, the recovery speed quickly. The time required to recover is equal to the time you copy the data back. You can think of more ways to make this time shorter.

7, no need to use expensive commercial software.

Pre-preparation work

1. Create LVM partitions

# lvcreate-l 3G mysqlvg1-n mysqllv \ Create a logical volume with a size of 3G MYSQLLV

# MKFS.EXT4/DEV/MYSQLVG1/MYSQLLV \ \ Format logical volumes

2. Stop MySQL Service

#/etc/rc.d/init.d/mysqld Stop

3, hanging in the logical volume to the temporary directory/tmp

# mount/dev/mysqlvg1/mysqllv/tmp/\ \ Mount logical volume to temp directory

4, using the TAR command to migrate the original database directory files to the temporary directory/tmp

# cd/var/lib/mysql/

# Tar CF-. | Tar xf--c/tmp/\ \ Data is decomposed into/tmp, which is placed in the directory where the logical volume is mounted

5, hang again in the logical volume to the database directory, and in the/etc/fstab to add open automatic mount

# umount/tmp/Uninstall Logical Volumes

# rm-rf/var/lib/mysql/*//Delete all the information in the database directory, be sure to confirm the previous operation OK

# mount/dev/mysqlvg1/mysqllv/var/lib/mysql///Mount logical volumes back to database MySQL directory

and add an open mount to the/etc/fstab.

# BLKID/DEV/MYSQLVG1/MYSQLLV | awk ' {print $} ' \ \ mysqllv The UUID value of the logical volume

# cat >>/etc/fstab <<endf \ \ Append Logical volume mount information under/etc/fstab

> Uuid=29502f33-fde2-4c21-b6c3-9fe1b74c0f0d/var/lib/mysql ext4 Defaults 0 0

> ENDF

6. Close SELinux

# Setinforce 0 \ set to premission mode

7. Start MySQL Service

#/etc/rc.d/init.d/mysqld Start

MySQL Database LVM snapshot backup

1. Lock table

Mysql> flush tables with read lock; \ \ Prevents data writes during the creation of a snapshot

2, check the position signal

Mysql> Show Master Status\g; \ \ Check the current position signal for ease of sync to slave use

3. Create Snapshots

# lvcreate-n mysql-snap-l 100m-s/dev/mysqlvg1/mysqllv \ \ Creates the data in the logical volume as a snapshot of 100M size, which is very fast.

4, Unlock

mysql> unlock tables;\\ unlock

5. Mount Snapshot creation

# mount/dev/mysqlvg1/mysql-snap/mnt/\ Mount snapshot, prepare for backup

6, read the snapshot for backup (backup needs to be backed up)

# tar CF mysql-01.tar.gz db1 db_2 \ Backup Snapshot information to storage device

7, uninstall the mounted snapshot, and then delete the snapshot

# umount/mnt/

# lvremove-f/dev/mysqlvg1/mysql-snap \ \ Delete snapshots, save space

By this, the entire backup process is over. If you want to sync content to the slave machine, then there are a few more steps to take.

1, copy the backup content to the slave data file directory.

2, restart the MySQL server, waiting for recovery to complete.

3. Use the change MASTER to command to tell slave the new binary log location and start the sync from there (the one we just recorded) for example:

mysql> Change Master to master_host= "192.168.100.109", master_user= "slave", master_password= "123.com", Master_log_ File= "host-bin.000006″,master_log_pos=198;

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/MySQL/