MySQL supports AWS S3 for backup

MySQL Enterprise Backup(MEB) now supports backup to the cloud with support for direct backup to AWS S3.

https://www.linkedin.com/nhome/updates?activity=5889951769257070592&goback=&trk=hb_ntf_LIKED_UPDATE_YOU_CREATED

and the tweet: 

https://twitter.com/paintrichpaint/status/484332341892890624 

AWS EC2 user data

You can perform any bootstrapping action you would like using user data.  Here is an example of installing Apache, PHP, and MySQL.  Then Apache, PHP and MySQL are started.  Then a sample application is installed on Apache.

#!/bin/sh
yum -y install httpd php mysql php-mysql
chkconfig httpd on
/etc/init.d/httpd start
cd /tmp
wget http://us-east-1-aws-training.s3.amazonaws.com/self-paced-lab-4/examplefiles-as.zip
unzip examplefiles-as.zip
mv examplefiles-as/* /var/www/html

Amazon RDS using private IP to connect to database - not the right approach

You should always connect to your Amazon RDS instance using the RDS endpoint in the AWS console. However, some IT folks chose to use the private IP address of the RDS instance.  It is easy for you to determine the private IP address of your RDS instance by using the host or dig commands as follows (Keep in mind this is not recommended but it shows how easy it is for IT personnel that don't want to use the RDS endpoint can do so):

[ec2-user@ip-10-0-0-50 ~]$ host postgres.cyve56loidht.us-west-2.rds.amazonaws.com
postgres.cyve56loidht.us-west-2.rds.amazonaws.com is an alias for ec2-54-201-99-99.us-west-2.compute.amazonaws.com.
ec2-54-201-75-58.us-west-2.compute.amazonaws.com has address 10.0.5.204
[ec2-user@ip-10-0-0-50 ~]$ ping 10.0.5.204
PING 10.0.5.204 (10.0.5.204) 56(84) bytes of data.
^C
--- 10.0.5.204 ping statistics ---
10 packets transmitted, 0 received, 100% packet loss, time 9792ms

[ec2-user@ip-10-0-0-50 ~]$ dig postgres.cyve56loidht.us-west-2.rds.amazonaws.com

; <<>> DiG 9.8.2rc1-RedHat-9.8.2-0.17.rc1.28.amzn1 <<>> postgres.cyve56loidht.us-west-2.rds.amazonaws.com
;; global options: +cmd
;; Got answer:
;; ->>HEADER<<- opcode: QUERY, status: NOERROR, id: 25864
;; flags: qr rd ra; QUERY: 1, ANSWER: 2, AUTHORITY: 0, ADDITIONAL: 0

;; QUESTION SECTION:
;postgres.cyve56loidht.us-west-2.rds.amazonaws.com. IN A

;; ANSWER SECTION:
postgres.cyve56loidht.us-west-2.rds.amazonaws.com. 5 IN CNAME ec2-54-201-99-99.us-west-2.compute.amazonaws.com.
ec2-54-201-99-99.us-west-2.compute.amazonaws.com. 60 IN A 10.0.5.204

;; Query time: 19 msec
;; SERVER: 10.0.0.2#53(10.0.0.2)
;; WHEN: Fri Jun  6 12:28:44 2014
;; MSG SIZE  rcvd: 132

AWS SLAs

The AWS services that have SLAs and the SLAs themselves can be found here:

http://aws.amazon.com/ec2/sla/

http://aws.amazon.com/route53/sla/

http://aws.amazon.com/rds/sla/

http://aws.amazon.com/s3/sla/

MySQL horizontal scaling

ScaleBase is a distributed database built on MySQL and optimized for the cloud. It is a relational database cluster that dynamically optimizes workloads and availability by logically distributing data. ScaleBase automates the data lifecycle, including analysis, data migration and node rebalancing.  ScaleBase provides an easy to manage horizontally scalable database cluster built on MySQL that dynamically optimizes workloads across multiple instances.  is  based in Newton, MA. The AWS Marketplace offering can be found here:  https://aws.amazon.com/marketplace/pp/B00K8B5BOG.  ScaleBase provides the scalability and availability benefits of NoSQL databases while using a relational database.

AWS Elastic Beanstalk basics

When first getting started with AWS Elastic Beanstalk here are some basic things to know

1. Logging : In Java, logging is done using the Apache Commons Logging framework. Logs can be captured with Apache Log4j or any other component that supports Apache Commons Logging.

2. Custom AMIs can be used : The process is documented here: http://docs.aws.amazon.com/elasticbeanstalk/latest/dg/using-features.customenv.html

3. RDS support: Amazon RDS Oracle, MySQL and SQL Server databases can deployed as part of you Elastic Beanstalk application. 

4. Launch New Environment to get OS patches : Amazon periodically updates the AMIs that were used to build the server instances, but servers can’t be updated while their running. When you launch a new environment, you get the updates.
5. Load Balancing : The Elastic Beanstalk service creates the load balancer for you.
6. Auto Scaling : The service creates the auto scaling configuration and group for you.

7..Custom configuration : Example Uses for YAML Configuration 

• Define custom environment variables beyond PARAMx
• Identify files to be downloaded to hosts 
• Can automatically unpack downloaded archive files
• Specify software to install Specify which services should run on hosts 
• Create and run scripts 
• Create and configure AWS resources

More on Elastic Beanstalk here: http://docs.aws.amazon.com/elasticbeanstalk/latest/dg/Welcome.html

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

Backup of Mysql on AWS to local directory and then to S3

Here is a method of backing up a MySQL database using the standard MySQL dump command and then moving the database dump to S3. This backups up the entire database each time which may not be what you want to do for a large production database.

1. Backup to local EC2 directory

sudo chown ec2-user /backup

sudo echo "mysqldump -uroot -ppassw-lab awslab > /backup/db_backup\`date '+%Y%m%d.%H%M'\`.sql" > mysqlbackup.sh

chmod +x mysqlbackup.sh

2. Create cron job to backup on a schedule. 

echo "* * * * * /home/ec2-user/mysqlbackup.sh" > ec2cron sudo crontab -u ec2-user ec2cron

sudo crontab -u ec2-user ec2cron

crontab -l

3. Send to S3

echo "aws s3 mv /backup/db_backup\`date '+%Y%m%d.%H%M'\`.sql s3://sysopsmysqlbackup/backups/db_backup\`date '+%Y%m%d.%H%M'\`.sql --region us-west-2 " >> /home/ec2-user/mysqlbackup.sh

aws s3 ls s3://sysopsmysqlbackup/backups --region us-west-2

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;

Boot Strap : MySQL

Here is a user data boot strap script for installing and starting Oracle MySQL:

#!/bin/sh 

yum -y install mysql-server 

/etc/init.d/mysqld start 

echo -e '\n\nsudo /etc/init.d/mysqld start' >> /etc/rc.d/rc.local

RDS cross region snapshots

Great news for AWS RDS users looking to provide extra DR protection to their SQL Server, Oracle and MySQL databases: http://aws.amazon.com/about-aws/whats-new/2013/10/31/announcing-point-and-click-copy-of-amazon-rds-db-snapshots-across-aws-regions/

AWS EBS PIOPS : block size and IOPS

Having spent more time in the database world than in the web development world, I am accustomed to measuring (database) performance/through put in terms of IOPS or TPS.  The web/video/image world like to use MB/sec.  Why I am saying this? Because it relates to the conversation about getting a certain level of PIOPS (based upon a 16 KB block) on AWS EBS and how this effects MB/sec.  MB/sec, I am beginning to understand, and maybe move to the 'dark side', is the ultimate measure of disk 'performance'.  

Example: A 2000 Provisioned IOPS volume can handle:

•2000 16KB read/write per second, or 1000 32KB read/write per second, or 500 64KB read/write per second 

•You will get consistent 32 MB/sec throughput (with 16KB or higher IOs)

•Perform an index creation action and sends I/O of 32K, IOPS becomes 1000, you still get 32MB/sec throughput

•On best effort, you may get up to 40 MB/sec throughput 

So, you may be better off using a 64 KB block size but your PIOPS will show up as lower but your MB/sec could be better.

AWS RDS : Changing OS time zone

This entry helps you change the database time zone: http://cloudconclave.blogspot.com/2013/08/aws-rds-timezone.html

However, it does not address changed the operating system time zone.  With RDS, the OS timezone can’t be changed (UTC, by design).  Each database engine supports different functions that query OS (for Oracle it is sysdate),   database engine, or session level parameters for timezone information . 

Here’s a quick synopsis of each DB engine’s datetime function details which identifies which calls are OS, database instance, or session based and which include timezone information or if its implied:

·         MySQL: http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html

·         Oracle: http://docs.oracle.com/cd/B19306_01/server.102/b14225/ch4datetime.htm#g1010984

·         SQL Server: http://msdn.microsoft.com/en-us/library/ms186724.aspx

AWS RDS timezone

Changing the time zone can be found here:

http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.Oracle.CommonDBATasks.html

Near the bottom you will find this:

Setting the Database Time Zone

You can alter the time zone of a database by running the rdsadmin_util procedure as shown in the following table. You can use any time zone name or GMT offset that is accepted by Oracle.

Oracle Method	Amazon RDS Method
alter database set time_zone = '+3:00';	exec rdsadmin.rdsadmin_util.alter_db_time_zone('+3:00');

After you alter the time zone, you must reboot the DB Instance for the change to take effect.

There are additional restrictions on setting time zones listed in the Oracle documentation.

AWS : Storing Session State

AWS SimpleDB, Memcache and DynamoDB can all be used.  DynamoDB is a good option as there is already a session provider for DynamoDB :

http://aws.typepad.com/aws/2012/09/managing-aspnet-session-state-with-dynamodb.html

For SQL Server, you can also look at session management in SQL server for persistence  and use built in .net session provider modules.

You can also manage session state using AWS RDS for SQL Server, MySQL or Oracle.

EBS data transfer costs to snapshot Oracle DB to S3

Obviously, there is a cost to snapshot your EBS volumes of your Oracle database and store them in S3 for backup and recovery.  This is the standard S3 cost of $.095 GB a month.  The cost can go down to $.055 when you store more data.  There is no cost to transfer the data in and out of S3 to and from EC2. 

Oracle MySQL Connect session time set

The session I am co-presenting at has been set for this time and place:

Session ID: CON4513
Session Title: Best Practices for Deploying MySQL on Amazon Web Services
Venue / Room: Hilton - Powell
Date and Time: 9/21/13 (Saturday), 16:00 - 17:00

Hope to see you there.

AWS RDS caching query results

The first place to look when attempting to speed query performance when running AWS RDS MySQL is the MySQL query cache size:
http://survivalguides.wordpress.com/2012/07/11/change-the-query-cache-size-amazon-aws-rds/

With support of MySQL 5.6 for AWS RDS, MySQL RDS now has support for memcached:
http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.MySQL.Options.html

MySQL Session at MySQL Connect

Here are the two MySQL on AWS session an MySQL Connect in September:

https://oracleus.activeevents.com/2013/connect/search.ww?eventRef=mysqlconnect#loadSearch-event=null&searchPhrase=aws&searchType=session&tc=0&sortBy=&p=&i(11180)=20802

I will be co-presenting the Best Practices for Deploying MySQL on Amazon Web Services session.