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.
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.
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:
- Set up 2 MySQL servers with one being master and the other being slave, replicating the master.
- Master server runs on ephemeral storage. Applications will be connecting directly to the master.
- 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):
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';
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
/etc/init.d/mysql, with any text editor like
Find and replace
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
/etc/fstab to change the mount configuration of
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
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
/etc/mysql/my.cnf, here are some things to change and take note of:
- Copy the whole block of configs in
[mysqld1] section, change the section name into
[mysqld2] and paste it right below
[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)
server-id = 1
log_bin = /var/log/mysql/mysql1-bin.log
[mysqld2], search and edit (uncomment if they are commented) the following:
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 (
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
/etc/mysql/my.cnf once again to configure replication.
[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.
[mysqld2], enter the same list of databases as above under
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.
GRANT REPLICATION SLAVE ON *.* TO 'replslave'@'localhost' IDENTIFIED BY 'YOUR_OWN_CHOSEN_SLAVE_PASSWORD';
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.
# 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;
SHOW SLAVE STATUS \G
If replication is set up properly, we should see these two lines indicating that it is running well:
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.
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 (email@example.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.