How to set up MySQL on Amazon EC2 micro spot instance

Notes for setting up a cheap, low-cost MySQL database server on an Amazon EC2 t1.micro spot instance. This can be useful for situations where your MySQL instance does not need 100% availability, e.g. for a cheap, test MySQL server.

Cheap MySQL Server Pricing

At this time, the price for a spot instance is $0.006/hour, which works out to $4.32/month. Add another dollar or so for some Gigabytes of EBS storage. On the free tier, you won't even pay for the EBS storage initially.

Amazon now supports Micro Instances on Amazon RDS for about $19/month. With a reserved instance, that can be as low as $10-14/month. If you do not have time to do server management, that is a better option than what is outlined here.

Cheap MySQL Server setup instructions

1. Request an Amazon Spot Instance from EC2 Console > Instances > Spot Requests using the default Amazon Linux AMI.
2. Make sure to add MySQL to the firewall rules
3. (Recommended) Request and assign an Elastic IP address to the new instance so that the instance will have a fixed, public IP address.
4. Create and download the .pem key file.
5. Use the SecureCRT Key for Amazon EC2 instructions to create a .pub key file for SecureCRT.
6. Use the Putty Key for Amazon EC2 instructions to create a .ppk key file for Putty/Plink.
7. Use Putty or SecureCRT to connect to the machine using the public-key authentication method and run the following commands. You must log in as ec2-user, not root.

# Update all packages
sudo yum update

# Install mysql client 
sudo yum install mysql

# Create a data directory for MySQL (this step may not be needed, 
#   or you may need to modify the /etc/my.cnf datadir setting
#   if you want to store the data somewhere else. Beware that 
#   data may be lost if it is not stored in the right place 
#   on Amazon EC2!)
sudo mkdir /var/lib/mysql

# Install mysql server
sudo yum install mysql-server

# Configure my.cnf settings using "nano" text editor
# For example, you can add a line like max_allowed_packet=16M (the default is 1M)
nano /etc/my.cnf

# Start mysql server
sudo /etc/init.d/mysqld start

# Set root password for MySQL (Choose a strong one if the 
#   MySQL TCP port is going to be left open.)
mysqladmin -u root password '123456789'

# Configure mysql to run on startup
sudo chkconfig mysqld on

1. Run putty.exe and connect to the Amazon server once just to save the server signature (otherwise, you'll get an error when connecting via HeidiSQL tunnel)
2. Open HeidiSQL and set up a TCP/IP tunnel session
3. Use the .ppk key file created above.

Now you'll be logged into MySQL as root using HeidiSQL, and you can create your databases and users from a user-friendly GUI.

If the spot instance price goes above your bid, the instance will automatically be turned off in increments of one hour. When the spot price goes back down, the instance will automatically be restored. If you assigned an Elastic IP, that IP will automatically be reassigned. Make sure to configure MySQL to run on startup as described above, otherwise, it will not restart automatically when the instance is restored.

If you own a domain, you can add a DNS entry to point a sub-domain to the IP address of the server. Most domain registrars allow you to do this via their control panel. By doing that, you can access the instance using a specific, user-friendly domain.

• MySQL SSH Tunnel Notes