MySQL Recover from EBS snapshots for logical volume

In this blog post http://cloudconclave.blogspot.com/2013/12/mysql-ebs-snapshots-for-backing-up.html, we backed up a MySQL database that stores its data across an LVM using multiple EBS volumes.  Now we will do a restore.

sudo /etc/init.d/mysqld stop 

sudo umount /dev/md0 

sudo mdadm --stop /dev/md0 

sudo mdadm --zero-superblock /dev/sdf 

sudo mdadm --zero-superblock /dev/sdg 

aws ec2 detach-volume --volume-id vol-1 aws ec2 detach-volume --volume-id vol-2

aws ec2 create-volume --snapshot-id snap-1 --availability-zone AZ -- volume-type standard

aws ec2 create-volume --snapshot-id snap-2 --availability-zone AZ -- volume-type standard

aws ec2 attach-volume --volume-id vol-New1 --instance-id INSTANCE -- device /dev/sdf

aws ec2 attach-volume --volume-id vol-New2 --instance-id INSTANCE -- device /dev/sdg

Create the stripped volumes:

yes | sudo mdadm \ --create /dev/md0 \ --level 0 \ --metadata=1.1 \ --raid-devices 2 \

/dev/xvdf /dev/xvdg

sudo mount -a sudo /etc/init.d/mysqld start

MySQL : EBS snapshots for backing up a logical volume manager

It is possible to use EBS snapshots to backup a MySQL databases when the data is stored on a logical volume manager.   You have to be make sure all active/cached data is written to disk and no write happens to the data files during the snapshots.

Snapshotting a stripped volume:

Flush data to disk, lock tables, and freeze disk writes:
1. mysql -u root -p password 

(at the MYSQL prompt) 

A. FLUSH TABLES WITH READ LOCK;

B. SHOW MASTER STATUS; 

C. SYSTEM sudo xfs_freeze -f /data

Snapshot all EBS volumes that are part of the logical volume manager:

2. At the Linux prompt:

A. aws ec2 create-snapshot --volume-id vol-xxxxxxxx --description "Snapshot of /dev/sdf" 

B. aws ec2 create-snapshot --volume-id vol-xxxxxxxx --description "Snapshot of /dev/sdg"

Unfreeze disk writes and unlock tables
3. mysql -u root -ppassw-lab awslab

(at the MYSQL prompt) 

A. SYSTEM sudo xfs_freeze -u /data 

B. UNLOCK TABLES;