Rss Feed Tweeter button Facebook button Technorati button Reddit button Linkedin button Webonews button Delicious button Digg button Flickr button Stumbleupon button Newsvine button

A Waage Blog

Ruby, Rails, Life

How to Set up DB server on Amazon EC2 with data stored on EBS drive formatted with XFS

with 17 comments

Working with Amazon Web Services (AWS) such as EC2, S3, and EBS takes some time to get familiar with all the tools, acronyms, and especially paradigm of thinking about servers. Once you get into it, you quickly find much old (outdated) documentation, and you can easily find yourself spending hours searching the web for blog posts to help in your quest!

After much research, I decided to create a Mysql database server on a ‘instance-store’ small ec2 server, with all the precious Mysql data stored on a EBS volume attached to my server. This way, I could easily create snapshot backups of my databases for FAST backups. Here’s the procedure I decided on. It involves symlinking Mysql config files and data directories onto the EBS volume.

Another trick I used because I needed to migrate about 20 GiB’s of data to get started, was that I initially set up an “X-tra large” instance, with 10 GiB’s RAM to handle the data import. After the data was migrated and imported to my database, I simply terminated my X-Large instance and spun up a small instance connected to the same EBS volume! All the databases were preserved nicely and I did not have to waste money paying for an X-Large instance anymore. This exemplifies the value of thinking in the “cloud” mindset – where you can spin up and down servers in a matter of seconds! Hope this article helps someone else out there!

Great Reference: http://developer.amazonwebservices.com/connect/entry.jspa?externalID=1663

# Create small instance (I used the AMI for i.386 CentOS 5.4)

# Create EBS volume and attach it to the EC2 (as /dev/sdh in my example)

# Install mysql-server

yum install mysql-server

# Install xfsprogs so you can format EBS drive with XFS

yum install xfsprogs
modprobe xfs
mkfs.xfs /dev/sdh

# Add to your /etc/fstab so it mounts on boot

echo "/dev/sdh /vol xfs noatime 0 0" | sudo tee -a /etc/fstab
sudo mkdir -m 000 /vol
sudo mount /vol

# Create directories that will store persistant Mysql data

mkdir /vol/etc /vol/lib /vol/log

# Move the mysql config and data files to your newly mounted drive

mv /etc/my.cnf  /vol/etc/
mv /var/lib/mysql /vol/lib/
mv /var/log/mysqld.log /vol/log/

# Symlink the mysql directories so that mysql uses the mounted EBS drive

ln -s /vol/lib/mysql /var/lib/mysql
ln -s /vol/etc/my.cnf /etc/my.cnf
ln -s /vol/log/mysqld.log /var/log/mysqld.log

Now restart your Mysql server and check your logs if you have any errors!

service mysqld start

# If you have errors, watch the logs
tail -f /var/log/mysqld.log

Good luck !

Written by Andrew Waage

March 19th, 2010 at 3:56 pm

17 Responses to 'How to Set up DB server on Amazon EC2 with data stored on EBS drive formatted with XFS'

Subscribe to comments with RSS or TrackBack to 'How to Set up DB server on Amazon EC2 with data stored on EBS drive formatted with XFS'.

  1. this is a really well written blog. very useful. thanks for posting!!

    =)

    i’d like to get to know you to exchange some knowledge. email me!

    Lar

    19 Mar 10 at 8:07 pm

  2. great post!
    I’m new to this, so I appreciate if you could explain a little bit about some of the steps:
    # Create EBS volume and attach it to the EC2 (as /dev/sdh in my example)

    # Symlink the mysql directories so that mysql uses the mounted EBS drive –> i thought you’re still using storage from the instance with backup from EBS? how to set up so EBS will take the snapshot from the S3

    does this mean we’ll have two mysql running? on S3 and EBS?
    Thanks. sorry for the beginner questions

    dion

    27 Aug 10 at 7:22 am

  3. @dion, No problem – hope this helps clear up your questions:

    # Create EBS volume and attach it to the EC2 (as /dev/sdh in my example)
    - If you are a beginner, the easiest way to do this is to go directly through Amazon’s Ec2 Interface. Simply log into your EC2 account, and create a new EBS volume.
    Upon creating the EBS volume, you will be asked for a mount point to mount the volume. In my example, I chose “/dev/sdh”.
    After you create the EBS volume in Amazon’s web interface, you must “attach” it to your Ec2 instance. This also can be done in Amazon’s web portal.

    # Symlink the mysql directories so that mysql uses the mounted EBS drive –> i thought you’re still using storage from the instance with backup from EBS? how to set up so EBS will take the snapshot from the S3
    - The EBS drive “appears” as part of your EBS filesystem once you have mounted it. So, it’s just like as if you had plugged in an external USB drive to your Ec2 instance.
    In this step, all I did is to set up the single instance of MySQL (that is running on Ec2 instance) to point all its directories to the EBS volume, rather than the default filesystem.
    This way Mysql is storing all data files and config files on the EBS volume, where it can be backed up easily to S3.

    Hope this helps to clear up your questions!
    Amazon web services might be a little confusing at first, but it will start to make sense once you poke around a bit.
    Good luck !

    Qug

    27 Aug 10 at 7:49 am

  4. Nice blog…

    I am very new to Amazon. I’ve created an instance for my server. Now I need to setup mysql for data storage.

    I dont know about the EBS, S3 and RDS. I just gone through the documents for storage. Which is the best technique for database and how to start with it.

    faris

    3 Sep 10 at 2:09 am

  5. @faris – Thanks for stopping by.
    S3 is not really meant as a filesystem, so its best use in your purpose is to store persistent backups of your database (either mysql dumps, or EBS snapshots).
    I’ve never used RDS, but it looks like a pretty cool service that might be good for your needs. Since I am used to setting up my own Mysql servers, I prefer to do the setup myself. Also, if you use RDS, be aware of the higher costs associated, simply because it is a dedicated DB instance. You may be ok with running a DB server on the same instance was your web server.

    That’s why I chose to attach an EBS volume to my server instance, and store all Mysql data on this EBS instance. EBS allows you to create quick snapshots for backup of your DB server.

    Good luck!

    Qug

    3 Sep 10 at 10:44 am

  6. Thanks a lot for your information!!!..

    I’ve some simple questions.
    1) How can I browse the EBS machine data from my putty.
    2) I have choose public AMI Ubuntu 9.10 Karmic Alestic, ubuntu@ EBS boot. I am just confused, when I choose this AMI it has EBS and ebs instance. So I just confused what is the difference between instance store ans EBS instance type.

    Thanks and regards,
    Faris

    faris

    5 Sep 10 at 5:34 am

  7. Great doc, thanks!

    Ritesh

    1 Mar 11 at 8:21 am

  8. Very helpful. You saved me a lot of time. Thank you very much.

    Vivek

    23 May 11 at 7:56 pm

  9. This is much appreciated apart from Amazon using a symlink for the EBS volume as xvdf rather than sdf and a reinstall it was pretty smooth sailing but learn’t a load on the way.

    David Jensen

    28 Dec 11 at 4:18 am

  10. [...] How to Set up DB server on Amazon EC2 with data stored on EBS drive formatted with XFS [...]

  11. [...] How to Set up DB server on Amazon EC2 with data stored on EBS drive formatted with XFS [...]

  12. Thanks! Really good and fast approach to move mysql data to ebs!

    Scriptico

    23 Feb 12 at 12:46 pm

  13. I tried this but i keep getting this error in the log.

    120303 03:11:20 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
    120303 3:11:20 [Warning] Can’t create test file /var/lib/mysql/ip-10-245-185-246.lower-test
    120303 3:11:20 [Warning] Can’t create test file /var/lib/mysql/ip-10-245-185-246.lower-test
    /usr/libexec/mysqld: Can’t change dir to ‘/var/lib/mysql/’ (Errcode: 13)
    120303 3:11:20 [ERROR] Aborting

    kunal

    3 Mar 12 at 1:52 am

  14. Hi Having some issues with permissions on the /vol/lib/mysql folder. THey are set to mysql:mysql, but mysql fails to start with a permission denied:

    Can’t start server : Bind on unix socket: Permission denied
    120315 0:11:48 [ERROR] Do you already have another mysqld server running on socket: /var/lib/mysql/mysql.sock ?

    I’ve tried changing all the permssions, even to 777 just in case, but with no joy. What’s weird is that after a change and a failed start, the permissions are reset again. It seems only mysql can own /vol/lib on EBS?

    Any idea really appreciated!

    Mike

    Mike

    14 Mar 12 at 5:22 pm

  15. try setting selinux off by executing below:
    /usr/sbin/setenforce 0

    rohit

    23 Jan 13 at 6:09 am

  16. This does not work!
    ES2 with ubuntu 13.x
    and mysql fails to start. Must be something to do with AppArmor not allowing to write test files in the dir

    kevin

    14 Feb 14 at 10:43 am

  17. The symlinks do not appear to work for me for some reason.
    When I move the files to the ebs volume as you suggest and symlink to them, mysql will not start. No error messages are written to the logs to explain why. It just will not start.

    Alex Ryan

    10 Oct 14 at 10:58 am

Leave a Reply