Archive for the ‘Sysadmin’ Category

Go Go Ghetto MySQL Parallel Dump and Import (or Hitting Things With Hammers Is Fun)!

Tuesday, October 18th, 2011

I am not a sophisticated man and my idea of elegance is hitting things with hammers. Bear that in mind when you look over the below code, which is here more for posterity than for any public edification.

One of the backup strategies we have is a complete table level dump and subsequent encryption of those files for storage in S3. Sure, it’s not a scalable solution as we discovered when the first iteration of the script did a complete dump of the db rather than by tables. This version runs them in parallel for the dump and works well enough for me not to sweat the timing at this point (~20 minutes from start to finish).

#!/bin/bash

# set variables
DAYNOW=$(date +%j)
TIMENOW=$(date +%H%M)
RPTIME=$(date +%x-%H:%M)
ME=$(hostname)

# stop the slave
mysql -utears -plamentations -e 'slave stop;'

# create directories
mkdir -p /mnt/tmp/backup/$ME-$DAYNOW-$TIMENOW

function r {
for J in $(cat $1) ; do
mysqldump -utears -plamentations sorrowdb --tables $J | gzip > "/mnt/tmp/backup/$ME-$DAYNOW-$TIMENOW/$J.sql.gz"
done
exit
}

mysql -u tears -plamentations -e 'show tables from sorrowdb' -s --skip-column-names | sort -R | split -d -l 5

for I in $(find x*) ; do
(r $I &)
done

sleep 15s

while [ "$(pgrep -c -f mysqldump)" -gt "0" ]
do
sleep 1s
done

#start slave
mysql -utears -plamentations -e 'slave start;'

cd /mnt/tmp/backup
gpg-zip --encrypt -r yourmom --output $ME-$DAYNOW-$TIMENOW.gpg $ME-$DAYNOW-$TIMENOW

# copy to s3
BACKUP=$(s3cmd --acl-private put /mnt/tmp/backup/$ME-$DAYNOW-$TIMENOW.gpg s3://sorrowdb-db-backups/ | sed \
-e 's/File //g' \
-e 's/\/mnt\/tmp\/backup\///g' \
-e 's/s3:\/\/sorrowdb-db-backups\/'"$HOSTNAME"'-[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9].tgz.gpg//g' \
-e 's/\[1 of 1\]//g' \
-e 's/(//g' \
-e 's/)//g' | awk '{ print $1 " " $2 " " $3 " " $5/1048576 "MB" }')

echo $BACKUP | mutt yourmom@sorrowdb.com -s "$ME - $DAYNOW $TIMENOW"

# clean up
rm -r /mnt/tmp/backup/

Now just like the World’s Most Interesting Man, I often only test things after it’s shipped. In this case I’ve tested that I can decrypt the package and see that all the files are there but I never took the time to actually do an import. Today is that day.

Version 0.01-ALPHA-WTFISTHISSHIT-b of the script just handles parallel imports of the tables from the dump file. As a bonus shoddiness, you need to run it in the directory where the files are sitting.

#!/bin/bash
function r {
for J in $(cat $1) ; do
gunzip < $J | mysql -utears -plamentations sorrowdb
done
exit
}

ls $1 | sort -R | split -d -l 18

for I in $(find x*) ; do
(r $I &)
done

Ugly but it works. Feel free to laugh, I always do when I look at my work.

Getting your tunnel on with EC2 and OpenVPN (or wrap it before you ride it)

Tuesday, November 16th, 2010

I worry about data. I worry about whether it’ll be lost, compromised, or corrupted. I worry whether or not I am doing enough: did I check the backups, are they “good”, did any fail to run, do I have a “plan”, will it work? It is easier for me to get a handle on my fretting with regards to the server I tend but what about those assets that get to see the wider world and networks other than my own?

As a company, we are traveling more and it isn’t unusual for people to work from airports, coffee shops, clients, taxi cabs, or bars. Its just how business is getting done. But what about that data I worry about so much, both company and personal? Things like Firesheep intrigue me at the same time as leave me with the feeling that I need to do more for the people on the road.

The most obvious solution is to implement a VPN but which one, how, and moreover could we run it in our EC2 stack. I experimented with PPTP and loved the ease and simplicity of setting it up and it worked great with the built in OS X VPN client. However, once I left the free love environment that is my home network it was all but useless as I encountered plenty of public WiFi that disallowed TCP/1723. Next up was Openswan’s IPSec implementation, a complex beast full of options and configurations. It enjoyed wide support on the public networks I tried but, sadly, I seemingly could not get the built in OS X VPN client to consistently play nice. Last up was OpenVPN.

OpenVPN is easy to setup, configure, and roll out. We use it on our production servers as part of the CohesiveFT VPN-Cubed product that allows us to easily throw up IPSec tunnels with clients and hook it back stack so we know it is rock solid. The only drawback that I encountered with OpenVPN is that neither the iPad nor the iPhone support it so this does limit the devices that we can secure at the moment but life is about compromise.

Eric Hammond wrote an excellent article as a proof-of-concept for running OpenVPN over TCP/80 on EC2. Per usual he made it dead simple and easy to implement and with the slightest bit of finessing it can be adapted for rolling out to small teams.

For this I chose a micro instance running Ubuntu 10.04 for both the typical miserly and familiarity reasons. Prepping I ran with Hammond’s opening tasks:

sudo apt-get update && sudo apt-get upgrade -y
sudo apt-get install openvpn -y
sudo modprobe iptable_nat
echo 1 | sudo tee /proc/sys/net/ipv4/ip_forward
sudo iptables-save > /some/place/you/can/recall
sudo iptables -t nat -A POSTROUTING -s 10.4.0.1/2 -o eth0 -j MASQUERADE

Once that inital prep work was out of the way I hoped over to the official Ubuntu OpenVPN documentation where we can set up OpenVPN to use asymmetrical keys instead.

But first we need to make a couple of tweaks to the server config (/etc/openvpn/server.conf). Look for the following and adjust them:

port 80
proto tcp
server 10.4.0.0 255.255.255.0

The port and protocol is so that our tunnel will run over TCP/80 and this, in theory, let you toss up your tunnel on nearly any network that has access to the Internet. That said, I’ve tested this on my free love network, a tighter administered open WiFi where my daughter goes to school (this is the one that disallows TCP/1723), and my T-Mobile data plan and while it worked on all three your mileage may vary.

The next step is to create a CA for OpenVPN and generate keys for clients and the server. Rather than do that work in /etc/openvpn/ as suggested in the documentation I opted to perform all that work in another directory so we can easily manage it with tools like git or puppet.

sudo mkdir /mnt/easy-rsa/
sudo cp -r /usr/share/doc/openvpn/examples/easy-rsa/2.0/* /mnt/easy-rsa/
sudo chown -R $USER /mnt/easy-rsa/

Adjust the variables in /mnt/easy-rsa/vars to best fit how you want things to look:

export KEY_COUNTRY="US"
export KEY_PROVINCE="CT"
export KEY_CITY="Hartford"
export KEY_ORG="Awesome Pancake, LLP"
export KEY_EMAIL="muffin.man@awepancake.com"

Then, because the devs and maintainers are awesome, just run some handy scripts and you’ll be all set:

cd /mnt/easy-rsa/
source vars
./clean-all
./build-dh
./pkitool --initca
./pkitool --server server
cd keys
openvpn --genkey --secret ta.key
sudo cp server.crt server.key ca.crt dh1024.pem ta.key /etc/openvpn/

Now we just need to generate some client certs:

cd /mnt/easy-rsa/
source vars
./pkitool jimmy

Replace “jimmy” with whatever you want to name your client, I recommend easy to remember nicknames that will cause you to snicker when you use them.

Now for the clients, it all depends on the what you’ll be using to connect to the server with. For testing I tried Tunnelblick and while it’s FLOSS I found it to be a little harder to configure than Viscosity, which is an affordable commercial client. The additional benefit to Viscosity is that it will allow you create easy to distribute connection bundles that include the CA cert, client key, and client cert that you can distribute to your clients for importing.

Setting up Viscosity is about as easy as it gets, you’ll need the CA.crt, and the host key and cert that you created above. Since pictures are a more valuable currency:

Once that is all set, connect to your tunnel and marvel at your secure connection. To check how things look on the client you can check ifconfig to ensure that the tunnel was added:

Also, do a quick nslookup to ensure that DNS is going to EC2 rather than to the local network:

Viscosity as a nifty detail page for visualizing the traffic over the tunnel:

On the server you can use tcpdump to watch the traffic and make sure that it is flowing like you want:

sudo tcpdump -i tun0 | grep "ip-10-4-0-6.ec2.internal"

Where 10-4-0-6 is the same as the ip address given by the tunnel.

That’s pretty much it, very easy thanks to Eric Hammond and the Ubuntu documentation team. From here you could set up connections in Viscosity for each of your users, export a zipped connection and distribute it (securely, of course!).

The next big test will be setting up all our road warriors and asking them to try it while they are traveling and report back with concerns, problems, or suggestions because in the end I want it to be easy as possible as they’ll be more inclined to use it.

Because my brain is a sieve here’s how to serve custom maintenance pages based on the subdomain

Tuesday, May 25th, 2010

Just like the favicons post of yore, this is another one that came up in conversation, “Can we display a custom maintenance page for each subdomain?” Since I had managed to sketch out a proof-of-concept with the favicons I figured this one shouldn’t be too hard.

The way this works is that Apache checks for a file called maintenance.on and if it exists it rewrites all traffic to maintenance-[subdomain].html. In practice, maintenance.on is set during deploys that require downtime such as one that might do work on the database, and at the end of a successful deploy the file is either removed or moved to something like maintenance.off.

Granted, this is a little kludgy as it depends on a separate html file for each subdomain and I don’t have a fallback position if that maintenance file doesn’t exist, Apache will just serve up a 404.

Anyway, here’s what’s in the Apache site file:

# Check for the maintenance.on file and redirect all requests to
# to a custom maintenance page based on the following formula
# maintenance-[subdomain].html
# get the requested subdomain name into variable %1
RewriteCond %{HTTP_HOST} ^([^.]+)\.*


# skip the rules for images and css
RewriteCond %{REQUEST_URI} !\.(png|jpg|jpeg|gif|css|ico)$


# check to see if maintenance.on exists
RewriteCond %{DOCUMENT_ROOT}/system/maintenance.on -f
RewriteCond %{SCRIPT_FILENAME} !maintenance-1%.html


# write out the custom maintenance page based on the domain
RewriteRule ^.*$ /system/maintenance-%1.html [L]

This was gratefully stolen and extended from the Codahale post, Time For A Grown-Up Server: Rails, Mongrel, Apache, Capistrano and You.

Because my brain is a sieve here’s how to serve custom favicons based on the subdomain.

Thursday, January 21st, 2010

The ability to serve, from the same code base, custom favicons based on subdomains came up recently and because my mod_write fu is about as strong as a water soaked kleenex I’m documenting just what the hell I did to get it working as a proof-of-concept.


RewriteEngine on
# This is just for troubleshooting purposes, comment it out
RewriteLog /var/log/apache2/rewrite.log
RewriteLogLevel 9


# Rewrite index.html to %2
RewriteRule ^/index.html$ /%2.html


# If the request is not for the the main domain or the www subdomain
RewriteCond %{HTTP_HOST} !^(www\.)?megafuntimefor\.us


# get the requested subdomain name into variable %2
RewriteCond %{HTTP_HOST} ^(www\.)?([^.]+)\.megafuntimefor\.us


RewriteRule ^/images/favicon.ico /images/%2.ico

Above is just the mod_rewrite block and while I’m sure that their is a drier way to write it I have yet to attain that level of enlightenment.

Using ELB to Serve Multiple Domains Over SSL on EC2 for Giggles and Unicorns

Wednesday, December 23rd, 2009

One of the complaints about EC2 is that you only have one IP address allocated per instance which makes it difficult to host, in a clean manner, multiple domains that require SSL certs.  Where you have control over IP allocation you could punch down a couple for one server and then set up your domains and SSL certs by IPs. That method is a no go so you are left with the ugly method of allocating those certs by port, something that Joe and Jane public are a little skittish about (https://superawesomefuntime.com is cool but https://superawesomefuntime.com:8443 smells phishy). Thankfully, ELB makes for a great proxy to hide the hideousness of the port-based workaround.

Essentially, as solutions go, this one is stupid easy; for each domain that you need to handle SSL for create a load balancer. Many balancers to one or a pool of instances. That’s it. Here’s a sloppy diagram…

For this example, we’ll be serving superawesomefuntime.com and unicorns-unlimited.com:


elb-create-lb superawesome --headers --listener "lb-port=443,instance-port=8443,protocol=TCP" --listener "lb-port=80,instance-port=80,protocol=http" --availability-zones us-east-1c


elb-create-lb unicorns --headers --listener "lb-port=443,instance-port=8445,protocol=TCP" --listener "lb-port=80,instance-port=80,protocol=http" --availability-zones us-east-1c

Take note of the address which are returned to you as need that for the cname portion of the game. They’ll look something like this: superawesome-123456789.us-east-1.elb.amazonaws.com and unicorns-987654321.us-east-1.elb.amazonaws.com.

The next step would be to add your instance to those load balancers:


elb-register-instances-with-lb superawesome --instances i-12ab3c45


elb-register-instances-with-lb unicorns --instances i-12ab3c45

Over on the server set up your virtual hosts in a way that hopefully is as hasty as mine…


NameVirtualHost *:8443
<VirtualHost *:8443>
ServerName superawesomefuntime.com
ServerAlias *.superawesomefuntime.com
SSLEngine On
SSLCertificateFile /etc/ssl/superawesome.crt
SSLCertificateKeyFile /etc/ssl/superawesome.key
RequestHeader set X_FORWARDED_PROTO 'https'
## Directories, Includes, Rewrites, oh my...
</VirtualHost>


<VirtualHost *:8445>
ServerName unicorns-unlimited.com
ServerAlias *.unicorns-unlimited.com
SSLEngine On
SSLCertificateFile /etc/ssl/unicorn.crt
SSLCertificateKeyFile /etc/ssl/unicorn.key
RequestHeader set X_FORWARDED_PROTO 'https'
## Directories, Includes, Rewrites, oh my...
</VirtualHost>

Then all you need to do is set up the cname for each domain to point at the load balancer address, in this example the cnames would be for *.superawesomefuntime.com = superawesome-123456789.us-east-1.elb.amazonaws.com and *.unicorns-unlimited = unicorns-987654321.us-east-1.elb.amazonaws.com.

The only loose end is configuring your healthcheck but that is a deeply personal decision best left for you and your app to work out.

I’m in your servers making the ‘nets flow.

Tuesday, December 22nd, 2009

I'm in your servers making the 'nets flow.

Just watching the sun rise while tickling servers…