Running MySQL on EC2 ephemeral storage while maintaining consistency

    

Note: This article is written based on Ubuntu Linux 9.10 Server (Karmic Koala), having previously set up MySQL to run on EBS based on Eric Hammond’s guide.
Same idea should still applies on other Linux distributions, especially newer versions of Ubuntu Linux.

Overview

Since the introduction of Elastic Block Storage (EBS) on Elastic Compute Cloud (EC2), it is recommended to run MySQL on EC2 with EBS for both consistency and easy backup.

If you are looking for tutorial on how to run MySQL on EBS, Eric Hammond has a guide on how to get that set up.

As I/O requests on EC2 is chargeable (at a rate of $0.10 per 1 million requests), it can get costly pretty quickly having MySQL running on EBS with the growth of database size. It got quite out of hand that at some point in time, for one of my instances, the I/O for EBS alone was costing 3x more than the compute time charges for the particular EC2 instance!

That was when I knew I cannot keep MySQL running on EBS for too long before it got me broke.

General Idea

The general idea is to have MySQL run on ephemeral storage of EC2, which comes in rather large volume and most importantly, I/O is not chargeable. Ephemeral storage, however, is known as such because it is not consistent. If an instance is terminated or crashes, data on ephemeral storage will be irrecoverable.

To overcome that and achieve consistency, we will have to rely on EBS. As I/O intensive operations for MySQL are mainly during sorting, especially during SELECT queries, the point will then be to have I/O heavy operations done on ephemeral storage while using EBS solely for maintaining consistency.

Here’s one way on how we can achieve that:

  1. Set up 2 MySQL servers with one being master and the other being slave, replicating the master.
  2. Master server runs on ephemeral storage. Applications will be connecting directly to the master.
  3. Replication slave runs on EBS.

You can either launch 2 EC2 instances, having master and slave on different instances, or for the this tutorial, we will be setting them up on a single EC2 instance for cost-saving purposes.

Guide is inspired from http://ubuntuforums.org/showthread.php?t=1154296 with improvements and adaptations for EC2.

Converting from single MySQL server to dual MySQL servers on a single EC2 instance

Assuming we already have a MySQL server with setup similar to Eric Hammond’s tutorial.
Data directory (lib): /vol/lib/

In order to allow mysqld_multi script to shutdown the MySQL server, we should run this SQL on the current MySQL installation before stopping it.

GRANT SHUTDOWN ON *.* TO 'multi_admin'@'localhost' IDENTIFIED BY 'SOME_SECURE_PASSWORD';

Stop MySQL

sudo /etc/init.d/mysql stop

Backup the current mysql init.d script and replacing with mysqld_multi.server

sudo mv /etc/init.d/mysql /etc/init.d/mysql_mono.server

sudo cp /usr/share/mysql/mysqld_multi.server /etc/init.d/mysql

Edit /etc/init.d/mysql, with any text editor like nano:

Find and replace

basedir=/usr/local/mysql
bindir=/usr/local/mysql/bin

with

basedir=/usr 
bindir=/usr/bin

Copy data from EBS-backed stroage at /vol/lib/mysql to ephemeral storage at /mnt/mysql/lib, leaving the data on EBS-backed storage to be used by the replication slave.

sudo mkdir /mnt/mysql
sudo cp -pr /vol/lib/mysql /mnt/mysql/lib
sudo mv /vol/lib /vol/lib-repli 

Edit /etc/fstab to change the mount configuration of /var/lib/mysql from /vol/lib/mysql to /mnt/mysql/lib:

sudo umount /var/lib/mysql

# Edit /etc/fstab with your favorite text editor and make the replacement as described above

# Save and remount
sudo mount /var/lib/mysql

Next, configuring my.cnf. Backing up existing one and editing /etc/mysql/my.cnf with text editor.

sudo cp -p /etc/mysql/my.cnf /etc/mysql/my.cnf.mono

When editing /etc/mysql/my.cnf, here are some things to change and take note of:

  • Rename [mysql] to [mysqld1]
  • Copy the whole block of configs in [mysqld1] section, change the section name into [mysqld2] and paste it right below [mysqld1] section
  • For [mysqld1], search and edit (uncomment if they are commented) the following:
    (very few changes as we are keeping it as close as possible to the earlier mono configuration)

    [mysqld1]
    server-id = 1
    log_bin = /var/log/mysql/mysql1-bin.log
  • For [mysqld2], search and edit (uncomment if they are commented) the following:
    [mysqld2]
    pid-file = /var/run/mysqld2.pid
    socket = /var/run/mysqld2.sock
    port = 3307
    server-id = 2
    datadir = /vol/lib-repli/mysql
    log = /var/log/mysql/mysql2.log
    

Before we can start mysql, we will also need to update AppArmor to grant permissions to mysqld to update the files for mysqld2.
Backup, edit and add insert the following lines to /etc/apparmor.d/usr.sbin.mysqld before the last line with the closing curly bracket ( } ):

/vol/lib-repli/ r,
/vol/lib-repli/** rwk,
/var/run/mysqld/mysqld2.pid w,
/var/run/mysqld/mysqld2.sock w,

Restart AppArmor

sudo /etc/init.d/apparmor restart

And now start MySQL

sudo /etc/init.d/myql start

If everything is set up correctly, MySQL instances will silently run. You can verify that by invoking:

ps aux | grep mysql | grep -v grep

You should see 2 instances of MySQL running:
Master, with server ID 1, running on ephemeral storage, and on the default MySQL port 3306.
Slave, with server ID 2, running on EBS, and on port 3307.

Before starting up application that modifies/writes to MySQL, we should set up replication.

Setting up MySQL master-slave replication

Edit /etc/mysql/my.cnf once again to configure replication.

At [mysqld1], enter the list of databases you would like to replicate under binlog-do-db. If there are multiple databases, you can list them under multiple binlog-do-db. For the purpose of this tutorial, let’s assume we would like to set up replication for exampledb1 and exampledb2.

[mysqld1]
binlog-do-db=exampledb1
binlog-do-db=exampledb2

At [mysqld2], enter the same list of databases as above under replicate-do-db

[mysqld2]
replicate-do-db=exampledb1
replicate-do-db=exampledb2

Restart MySQL:

sudo /etc/init.d/mysql restart

Run these SQL queries on master (port 3306):

# Just to be sure that we are on master server. We should be getting 1.
SELECT @@server_id; 

GRANT REPLICATION SLAVE ON *.* TO 'replslave'@'localhost' IDENTIFIED BY 'YOUR_OWN_CHOSEN_SLAVE_PASSWORD';

FLUSH PRIVILEGES;
SHOW MASTER STATUS;
# Note down the results of this, we will need them as we set up replication slave

Run these SQL queries on slave (port 3307):

# Just to be sure that we are on slave server. We should be getting 2.
SELECT @@server_id; 

# Take note of MASTER_LOG_FILE and MASTER_LOG_POS
# You should be entering the values that we take down earlier from master server.
CHANGE MASTER TO MASTER_HOST='127.0.0.1', MASTER_USER='replslave', MASTER_PORT=3306, MASTER_PASSWORD='YOUR_OWN_CHOSEN_SLAVE_PASSWORD', MASTER_LOG_FILE='mysql1-bin.000001', MASTER_LOG_POS=106;

START SLAVE;
SHOW SLAVE STATUS \G

If replication is set up properly, we should see these two lines indicating that it is running well:

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

Complete!

Congratulations on successfully setting up MySQL master-slave replication on the same EC2 instance.

You can now run your MySQL-based applications. There should not be any changes needed for your applications to run as the master MySQL server is running default port and default socket.

Now you do not have to worry about I/O heavy operations on MySQL raising your cloud computing bills and at the same time having the peace of mind that your MySQL data is safely being replicated on a consistent EBS-backed storage.

For further safeguard, you can even take EBS snapshots from time to time.

Feedback

As this tutorial is written by attempting to retrace the steps I have taken for my setup, I may have missed some pointers.
Let me know in the comments below or email me (chua@uzyn.com) if you have any questions or suggestions, or just to let me know that you have successfully set it up by following this tutorial.

If you are into cloud computing, do consider subscribing to the blog. Thanks.

This entry was posted in Cloud computing and tagged , , , , , , , . Bookmark the permalink. Post a comment or leave a trackback: Trackback URL.

13 Comments

  1. Posted December 14, 2010 at 01:16 | Permalink

    How to run MySQL on EC2 ephemeral storage while maintaining consistency http://bit.ly/dEfLot #aws #mysql

    This comment was originally posted onTwitter

  2. Posted December 14, 2010 at 14:03 | Permalink

    How to run MySQL on AWS EC2 ephemeral storage while maintaining consistency – http://bit.ly/h5ffRx – [Hacker News FH]

    This comment was originally posted onTwitter

  3. Posted December 14, 2010 at 14:17 | Permalink

    How to run MySQL on AWS EC2 ephemeral storage while maintaining consistency: http://bit.ly/gs18mS

    This comment was originally posted onTwitter

  4. Posted March 15, 2013 at 04:48 | Permalink

    I for all time emailed this website post page to all
    my contacts, since if like to read it next my contacts will
    too.

  5. Posted March 20, 2013 at 03:30 | Permalink

    Today, while I was at work, my cousin stole my iPad
    and tested to see if it can survive a 25 foot drop, just so she can
    be a youtube sensation. My iPad is now broken and she has 83 views.
    I know this is completely off topic but I had to share it with someone!

  6. Posted March 20, 2013 at 17:07 | Permalink

    Іt’s going to be ending of mine day, however before finish I am reading this fantastic paragraph to improve my know-how.

  7. Posted April 4, 2013 at 14:00 | Permalink

    The ѕοcks are made from 85% rayon bamboo, 10%
    nylon and 5% elastane. They design although no different from yours, but similar caused numerous women there often recommend the repeatedly shout, looks even years constant of the paragraph red bottom replica christian louboutin still oodles charm. Golden Globe Awards 2013: Jessica Alba shows off her hourglass figure in orange Oscar de la Renta dress.

  8. Posted April 10, 2013 at 11:58 | Permalink

    ” Au contraire, my friend, ‘it ain’t necessarily so’, to borrow a phrase from the musical ‘Porgy and Bess’. t even touch it, and just told him the correct lyrics. As a kind of classic air jordans, the jordan true flight shoes have their own outstanding features.

  9. Posted May 5, 2013 at 08:53 | Permalink

    This is a very good tip especially to those fresh to the blogosphere.
    Short but very precise information… Appreciate your sharing this one.
    A must read article!

  10. Posted June 10, 2014 at 00:45 | Permalink

    I visited many web pages but the audio quality for audio songs current at this website is really fabulous.

  11. Posted June 10, 2014 at 02:46 | Permalink

    I am now not certain where you’re getting your info, but great topic.
    I needs to spend a while learning more or figuring out more.
    Thank you for fantastic information I used to be looking for this info for my mission.

  12. Posted July 22, 2014 at 01:44 | Permalink

    My spouse and I stumbled over here from a different web page and thought
    I might as well check things out. I like what I see so now i am following you.
    Look forward to finding out about your web page for a second time.

    Here is my blog post :: more info

  13. Posted September 26, 2014 at 16:40 | Permalink

    Hello there! This is kind of off topic but I need some advice from an established blog.
    Is it tough to set up your own blog? I’m not very techincal but I can figure things
    out pretty quick. I’m thinking about creating my own but I’m not sure where to start.

    Do you have any points or suggestions? Thank you

    my weeb blog: site (Sheree)

3 Trackbacks

  1. [...] did see a suggestion for running two MySQL servers on an instance, with a master running on the ephemeral disk (which we would also RAID) and a slave storing changes [...]

  2. [...] so a theoretical maximum of 128 MB/s), while the ephemeral volumes have no such problem.I did see a suggestion for running two MySQL servers on an instance, with a master running on the ephemeral disk (which we would also RAID) and a slave storing changes [...]

Post a Comment

Your email is never published nor shared. Required fields are marked *

*
*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Additional comments powered byBackType