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;

Striping volumes for an Oracle Database

Often times you will want to stripe your Oracle database volumes for better performance.  Here are the steps for creating the EBS volumes and creating the Linux logical volume:

1. aws ec2 create-volume --availability-zone us-west-2b --size 100

2. aws ec2 create-volume --availability-zone us-west-2b --size 100

3. aws ec2 describe-volumes --volume-ids vol-7c2e9955 vol-2e2e9907

4. aws ec2 attach-volume --volume-id vol-7c2e9955 --instance-id i-f0ef69c6 --device /dev/sdc

5. aws ec2 attach-volume --volume-id vol-2e2e9907 --instance-id i-f0ef69c6 --device /dev/sdd

6. aws ec2 start-instances --instance-id i-NewDBServer

7. ssh -i YourFile.pem ec2-user@10.50.x.x

Once you are logged into the EC2 instance,  stripe and then recognize your additional volumes with this series of commands:

1. sudo mkdir /mnt/oraclestripe

2. sudo vgcreate vg /dev/sdc /dev/sdd

3. lvcreate -L 200 -n lv -i 2 vg

(Notes:

1. Using default stripesize 64.00 KiB

2. The parameter -i in the lvcreate command specifies the number of physical volumes to stripe across. If you wanted to stripe across more than 2 drives, you would have to specify that number.

)

4. sudo mkfs -t ext3 /dev/vg/lv

5. sudo mount /dev/vg/lv /mnt/oraclestripe

6. Next, update /etc/fstab to ensure these mounted drives are automatically mounted on reboot.

A. sudo vi /etc/fstab and add these lines:
/dev/sdb /mnt/piops1 ext3 defaults 0 2
/dev/vg/lv /mnt/oraclestripe ext3 defaults 0 2

7. df -h (to view the new volumes mounted to the logical volumes created)