Running MySQL on Amazon EC2 with Elastic Block Store (another take)

Summary:

I have been following ‘Eric Hammod’ article on how to setup MySQL so that, it survives EC2 instance termination or restarts.  That article is one of best article but it’s too much for my requirements. So I came up with some more simple steps to host MySQL files on EBS volume. Many parts of Eric’s article still applies like how to create EBS volume and how to create snapshot of EBS.

How it is different from Eric’s version.

  1. Eric’s article is solving this problem by mounting original locations as mirror of EBS locations. In case of restart, you will have to fix fstab file. In my case, you will have to fix my.cnf file. I rather not touch fstab file.
  2. My purpose, just to do enough, to save my MySQL data files in case of my instance crashes/restarts. Hence I don’t care of configuring log files location.
  3. I am using elastic fox plugin for creating EBS volume etc. Able to avoid ec2 tools. (if you planning to do lots of work, better spend some time setting up tools and get familiarized.)

Pre-requisites for readers

  • You are familiar with Amazon EC2 images and how to play with them.
  • You already have installed MySQL and it is successfully running.

How to configure MySQL to use data files on EBS.

I have created 10G EBS volume and mounted that volume to /mnt/jframeworks. Attach volume to instance (as /dev/sdf). I used Elasticfox firefox plugin. Using amazon ec2 tools was getting too much time consuming.

Let’s create a new user also, make his home on EBS only, to start with. Adding new user is not necessary for this purpose. But I think, it is good idea to create new user and use that user for all new changes starting from fresh EC2 image.

sudo useradd -d /mnt/jframeworks/home/<newuser> -m <newuser>
sudo passwd <newuser>
sudo usermod -a -G admin <newuser> -s /bin/bash
sudo usermod -s /bin/bash <newuser> (may not be needed)
sudo mkfs -t ext3 /dev/sdf

(Formatting as ext3. Being linux noob, not sure, why xfs filesystem as mentioned in Eric’s article.)

sudo mkdir /mnt/jframeworks
sudo mount /dev/sdf /mnt/jframeworks

First create required data directory on mounted EBS volume

mkdir /mnt/jframeworks/var
mkdir /mnt/jframeworks/var/lib

Copy MySQL libs to EBS directory

cp -a /var/lib/mysql /mnt/jframeworks/var/lib/

Note Remember to use “-a” flag while copying.

MySQL settings are stored in my.cnf file, available at /etc/mysql.

Hence change /etc/mysql/my.cnf file to change data directory to new location

In this example, here it looks like ->

datadir = /mnt/jframeworks/var/lib/mysql

AppArmor is new tool, which is being used in ubuntu by default. Hence lets fix that too.

sudo vi /etc/apparmor.d/*mysqld, replicate lines /var/lib/mysql** with new data dir location

Now finally, lets restart MySQL and AppArmor, to make changes in effect.

sudo /etc/init.d/apparmor restart
sudo /etc/init.d/mysqld restart