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

Restore S3 backup to MySQL

In this blog post, we backed up our MySQL database: http://cloudconclave.blogspot.com/2013/12/backup-of-mysql-on-aws-to-local.html. Now we will restore this backup.

1. Assumes this directory has been created: /backup/restore. If it does not exist on the EC2 instance, issue this command: mkdir -p /backup/restore

2. aws s3 cp s3://sysopsmysqlbackup/backups/<BACKUP-FILE> /backup/restore/restore.sql --region us-west-2

Note: BACKUP-FILE is the name of the backup file in S3

3. mysql -u root -ppassw-lab awslabrestore < /backup/restore/restore.sql

Oracle Secure Backup restore

A benchmark test using OSB, a DBA, was able to restore 3.8 terabytes in 2.5 hours over gigabit Ethernet. This amounts to 25 gigabytes per minute, or 422MB per second. This 2.5 hours using OSB and S3 compares to, conservatively, 10-15 hours that would be required to restore from tape.

Disaster recovery in the cloud options

These are by all means not all the options, but at a high level these are the common options:

• Backup and restore - Have to set up security groups set up and set up Route53. Data is not being mirrored or replication. You are doing an import and export from database to S3. When system goes down then resource S3 data to the database. You also have to provision AMIs.
• Pilot light – In this case, you are doing Data mirroring/replication from source system to DR site. Could run GoldenGate. The EC2 instances are present but stopped. Have to prepare all required resources from automatic start – AMIS, network settings, load balancing etc.
• Fully working low capacity standby – Blurring lines between HA and DR. In this case have an issue with RDS as the data needs to replicated both ways as traffic could go to either configuration.
• Multi-site hot standby – production load on both sides. Cost and data sync are the biggest challenges.