Best Practices for Running MySQL on EC2 with EBS

Follow the steps here http://aws.amazon.com/articles/1663?_encoding=UTF8&jiveRedirect=1 Mainly need to stop the mysql services first

sudo /etc/init.d/mysql stop
Then moving all mysql data related files to your EBS
sudo mkdir /vol/etc /vol/lib /vol/log
sudo mv /etc/mysql     /vol/etc/
sudo mv /var/lib/mysql /vol/lib/
sudo mv /var/log/mysql /vol/log/

sudo mkdir /etc/mysql
sudo mkdir /var/lib/mysql
sudo mkdir /var/log/mysql

echo "/vol/etc/mysql /etc/mysql     none bind" | sudo tee -a /etc/fstab
sudo mount /etc/mysql

echo "/vol/lib/mysql /var/lib/mysql none bind" | sudo tee -a /etc/fstab
sudo mount /var/lib/mysql

echo "/vol/log/mysql /var/log/mysql none bind" | sudo tee -a /etc/fstab
sudo mount /var/log/mysql
Then restart the MySQL server back
sudo /etc/init.d/mysql start

Automated the snapshot process

The automated process this article has more detail than previous. http://www.capsunlock.net/2009/10/deleting-old-ebs-snapshots.html

$codename=$(lsb_release -cs)
$echo "deb http://ppa.launchpad.net/alestic/ppa/ubuntu $codename main"| sudo tee /etc/apt/sources.list.d/alestic-ppa.list
$sudo apt-key adv --keyserver keyserver.ubuntu.com --recv-keys BE09C571
$sudo apt-get update
$sudo apt-get install -y ec2-consistent-snapshot
$sudo PERL_MM_USE_DEFAULT=1 cpan Net::Amazon::EC2 
You need to create mysql config file ~/.my.cnf
[client]
user=root
password=MYPASSWORD

and AWS access ~/.awssecret

aws_accesskey
aws_secretkey

Both files need to be set under permission chmod 600 read-only. Download TimKay’s AWS in order to delete previous snapshot

$sudo curl https://github.com/timkay/aws/raw/master/aws -o /usr/local/sbin/aws
$sudo chmod +x /usr/local/sbin/aws
Set up snapshot in cronjob, just run crontab –e to call out editing
0 0 * * * /usr/bin/ec2-consistent-snapshot --mysql --xfs-filesystem /ebs vol-XXXXXX --description "/EBS snapshot $(date +'%Y-%m-%d %H:%M:%S')" >> /mnt/backup.log 2>&1</pre>
# To keep the latest 20 snapshots.</pre>
0 2 * * * /usr/local/sbin/aws dsnap | grep vol-XXXXXXX | sort -r -k 5 | sed 1,20d | awk '{print "Deleting snapshot: " $2 " Dated:" $8}; system("/usr/local/sbin/aws delsnap " $2 )' >> /mnt/backup.log 2&1
 Reference for CronJob:
 http://www.unixgeeks.org/security/newbie/unix/cron-1.html
Advertisements

One thought on “Best Practices for Running MySQL on EC2 with EBS

Comments are closed.