MySQL Backups to AWS Cloud

A client asked me to write a script to perform nightly MySQL database backups to the AWS cloud. They also viewed this as an opportunity to test the Amazon Web Services (AWS) waters.

They had the following requirements:

1. Full backup every night. No need to worry about differentials or incrementals.
2. Backups must be encrypted prior to transmission.
3. Transmission must take place over secure channel.
4. Following a backup run, an email notification must be sent to the DBA's.
5. Documentation.

Rather than re-invent the wheel, and write the tool from scratch, I decided to do some research. I came across two Open Source resources, which combined together with some modifications, addressed my clients needs.

Resources:

1. MySQL Backup script with encryption
2. s3cp - Amazon S3 command line cp program
   

Modifications:

1. Instead of 3DES, I used AES192 CBC mode, mostly due to performance reasons. Also, due to Cipher Block Chaining mode, any file corruption during file transfer would be detected during decryption.
2. Integrated s3cp for uploading the resulting encrypted dump file to AWS S3.
3. Added lines to calculate backup size and md5 checksum to be included in the emails sent to DBAs.
4. Added email notification feature.

You can download the script and support files HERE. A redacted version of documentation will follow.

Update: Redacted documentation "AWS_MySQL_backup.pdf"

Thanks,
VVK

EC2 instance meta-data via SNMP

Here is a simple script and a sample snmp.conf file which will enable you to query your EC2 instance meta-data via SNMP. For more information on meta-data, see (see page 49 EC2 Developers Guide ).

metadata.py

#!/usr/bin/python
# This script returns metadata specific to AWS instances
# See Page 49, Amazon Elastic Compute Cloud - Developer Guide

from httplib import HTTPConnection
from sys import argv
import socket

if len(argv) > 1:
     try:
          conn = HTTPConnection("169.254.169.254")
          url = "/latest/meta-data/" + str(argv[1])
          conn.request("GET",url)
          r1 = conn.getresponse()
          if r1.status == 200:
               print r1.read()
          else:
               print r1.status,url
     except socket.error,msg:
         print msg[1] 
else:
     print "data key missing"

snmp.conf

rwcommunity EDF9ErIfzNFb 127.0.0.1
rocommunity public

syscontact user@host.com
syslocation AWS

com2sec notConfigUser default public
group notConfigGroup v1 notConfigUser
group notConfigGroup v2c notConfigUser

# RFC1213-MIB.iso.org.dod.internet.mgmt.mib-2
view systemview included .1.3.6.1.2.1.1
view systemview included .1.3.6.1.2.1.2

# HOST-RESOURCES-MIB.iso.org.dod.internet.mgmt.mib-2.host
view systemview included .1.3.6.1.2.1.25 view systemview included .1.3.6.1.2.1.25.1.1

# UCD-SNMP-MIB.iso.org.dod.internet.private.enterprises.ucdavis
view systemview included .1.3.6.1.4.1.2021

access notConfigGroup "" any noauth exact systemview none none

# Added for support of bcm5820 cards.
pass .1.3.6.1.4.1.4413.4.1 /usr/bin/ucd5820stat

#agentaddress 192.168.8.103

# Executable Scripts
# AWS Specific
exec ami-id /etc/snmp/aws_metadata.sh ami-id
exec ami-launch-index /etc/snmp/aws_metadata.sh ami-launch-index
exec ami-manifest-path /etc/snmp/aws_metadata.sh ami-manifest-path
exec ancestor-ami-ids /etc/snmp/aws_metadata.sh ancestor-ami-ids
exec block-device-mapping /etc/snmp/aws_metadata.sh block-device-mapping
exec instance-id /etc/snmp/aws_metadata.sh instance-id
exec instance-type /etc/snmp/aws_metadata.sh instance-type
exec local-hostname /etc/snmp/aws_metadata.sh local-hostname
exec local-ipv /etc/snmp/aws_metadata.sh local-ipv4
exec kernel-id /etc/snmp/aws_metadata.sh kernel-id
exec availability-zone /etc/snmp/aws_metadata.sh availability-zone
#exec product-codes /etc/snmp/aws_metadata.sh product-codes
exec public-hostname /etc/snmp/aws_metadata.sh public-hostname
exec public-ipv4 /etc/snmp/aws_metadata.sh public-ipv4
#exec public-keys /etc/snmp/aws_metadata.sh public-keys
exec ramdisk-id /etc/snmp/aws_metadata.sh ramdisk-id
exec reservation-id /etc/snmp/aws_metadata.sh reservation-id
exec security-groups /etc/snmp/aws_metadata.sh security-groups

# Disk Checks
disk /

# Process Checks
proc crond
proc ntpd
proc rsyslogd
proc sshd

Sample Output

[root@ami1 ~]# snmpwalk -Os -c public -v 2c localhost .1.3.6.1.4.1.2021.8.1.101.1
extOutput.1 = STRING: ami-08f41161

[root@ami1 ~]# snmpwalk -Os -c public -v 2c localhost .1.3.6.1.4.1.2021.8.1.101.2
extOutput.2 = STRING: 101

Hope this helps!
VVK

Minimal i386 CentOS5 AMI

Here is a quick cheat-sheet to help you create a minimal CentOS5.x image to create a custom Amazon Machine Image (AMI).

My target instance type is small (see page 49, 57 EC2 Developers Guide ) , so adjust fstab accordingly. To create the image you will need to create a custom fstab file and a yum config file. Additionally, the following cheat-sheet assumes you are using a Red Hat based distribution to create the image (CentOS 5 in my case), otherwise some of the tool parameters will not work, not to mention yum might not be available.

/etc/fstab

/dev/sda1 / ext3 defaults 1 1
none /dev/pts devpts gid=5,mode=620 0 0
none /dev/shm tmpfs defaults 0 0
none /proc proc defaults 0 0
none /sys sysfs defaults 0 0
/dev/sda2 /mnt ext3 defaults 0 0
/dev/sda3 swap swap defaults 0 0


yum-aws.conf

[main]
exclude=authd lha libsdp iptstate emacs-common emacs-nox bluez-utils aspell-en rdist libmthca NetworkManager dhcpv6_client lksctp-tools autofs finger bluez-libs vconfig libibverbs ash jpackage-utils rdate aspell irda-utils rp-pppoe pcmcia-cs bluez-bluefw udapl jwhois rsh wireless-tools ipsec-tools bluez-hcidump krb5-workstation fbset apmd dosfstools dump ed eject ethereal-gnome ethereal mkbootdisk redhat-lsb gtk2 fonts-xorg-base fonts-xorg-truetype freetype fontconfig xorg-x11-libs pango ttmkfdir xorg-x11-font-utils xorg-x11-xfs xorg-x11-Mesa-libGL xorg-x11-xauth chkfontpath rmt sysreport stunnel talk wvdial yp-tools ypbind xine xinetd td statserial cups cups-libs minicom bc isdn4k-utils lrzsz mtr mt-st nfs-utils portmap nc nano crash up2date netdump oddjob oddjob-libs ccid coolkey conman cpuspeed Deployment_Guide-en-US firstboot-tui pcsc-lite ifd-egate mdadm postfix cyrus-sasl sendmail numactl pcmciautils pscs-lite-libs quota usbutils logwatch iptables-ipv6 system-config-securitylevel-tui dhcpv6-client ibmasm ksh tcsh mtools rhpl system-config-lvm system-config-network-tui at smartmontools mailcap acpid cyrus-sasl-plain cryptsetup-luks hal pm-utils desktop-file-utils redhat-menus htmlview pinfo anacron sos setuptool pciutils pkinit-nss pax pam_krb5 pam_smb amtu


#Base distro
[base]
name=CentOS-5 - Base
baseurl=http://destiny/centos5-i386-prepatched-latest-install/
gpgcheck=1
gpgkey=http://destiny/centos/RPM-GPG-KEY-CentOS-5

#released updates
[updates]
name=CentOS-5 - Updates
baseurl=http://destiny/centos/5/updates/i386/stable/
gpgcheck=1
gpgkey=http://destiny/centos/RPM-GPG-KEY-CentOS-5

#packages by vendors
[vendor]
name=CentOS-5 - Vendor
baseurl=http://destiny/centos/5/vendor/i386/
gpgcheck=0
enabled=1

#custom built packages
[custom]
name=CentOS-5 - Custom
baseurl=http://destiny/centos/5/custom/i386/
gpgcheck=0
enabled=0


Build Image
As root, run the following:

# dd if=/dev/zero of=centos5_i386_minimal.img bs=1M count=1024
# mkfs.ext3 -F ~/centos5_i386_minimal.img
# mount -o loop centos5_i386_minimal.img /mnt
# mkdir /mnt/dev
# mkdir /mnt/proc
# mkdir /mnt/etc
# /sbin/MAKEDEV -d /mnt/dev -x console
# /sbin/MAKEDEV -d /mnt/dev -x null
# /sbin/MAKEDEV -d /mnt/dev -x zero
# cp ~/fstab /mnt/etc/
# mount -t proc none /mnt/proc

# yum -c yum-aws.conf --installroot=/mnt -y groupinstall Base

Next I install few hand picked packages

# yum -c yum-aws.conf --installroot=/mnt -y install dhclient dmidecode file iptables openssh openssh-clients openssh-server prelink rootfiles sysfsutils sysklogd tar mkinitrd nash lvm2 cpio ntp net-snmp net-snmp-utils net-snmp-libs vim-enhanced screen strace setools selinux-policy selinux-policy-targeted libvolume_id mailx gcc cpp gcc-c++ libtool pkgconfig gettext autoconf automake bison flex dos2unix unix2dos elinks procinfo

Customization
Create eth0 ( /mnt/etc/sysconfig/network-scripts/ifcfg-eth0 ) network script to use DHCP for configuration:

DEVICE=eth0
BOOTPROTO=dhcp
ONBOOT=yes
TYPE=Ethernet
USERCTL=yes
PEERDNS=yes
IPV6INIT=no


Enable networking ( /mnt/etc/sysconfig/network)

NETWORKING=yes

Chroot
At this point you might consider spending some time customizing your build ( chroot /mnt /bin/bash ). Pay special attention to permissions of the file you will create (/etc/shadow, authorized_keys etc.). Here is a short checklist:

1. Setup user accounts and SSH authorized_keys for password-less login. Don't forget to create the home directories, unless you have pam mkhomedir enabled.
   
2. Setup various daemons such as NTP, syslog, SSH, cron. Make sure they are enabled for the appropriate run-level.
3. Setup policies for passwords, firewall etc.

Clean-up
To reduce the image size, clear yum cached data.

yum clean all

Exit out of chroot environment and /mnt folder and run the following:

# umount /mnt/proc
# umount /mnt

Bundling

Thats it! Now you can proceed with bundling your AMI as per EC2 Developers Guide (page 28).


# ec2-bundle-image -i centos5_i386_minimal.img -k .ec2/pk-.pem -c .ec2/cert-.pem -u -r i386

# ec2-upload-bundle -b osc-ami -m /tmp/centos5_i386_minimal.img.manifest.xml -s -a --location US


Register AMI

See EC2 Developers Guide (page 311).


# ec2-register my-ami/centos5_i386_minimal.img.manifest.xml


You should see an output with your AMI ID. You can use this AMI ID to launch new instances.

Notes

1. Carefully audit the exclude parameter in main section of the yum-aws.conf file. I have chosen to remove a lot of core packages to trim down the install base from 350+ packages to 139, but you might need some of those excluded packages.
2. I am using a pre-patched local yum repository to do the install, and maybe some day I'll blog about how to set it up. In the mean time you might need to adjust the baseurl in yum-aws.conf to some public yum repo or a private one if available.
3. Error "rpmdb: unable to lock mutex: Invalid argument" is caused by Bug 463921
   

Cheers,
VVK