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.