Tutorial: How to Set Up Dedicated MySQL Database Server Using Amazon EC2

DISCLAIMER: This is my current setup. Users with other distros beware.

So in the spirit of learning about scalability (and also after running into some issues dealing with several databases running on different local development environments we are using) I decided it would be a good idea to set up an EC2 instance solely as a database server. This tutorial will use MySQL as the database of choice not necessarily because I think it's the best, but because it is what I'm used to (I know... I'm lazy). So here's what we need to do.

  1. Assume you have an EC2 instance running for this
  2. Install mysql, apache2, php5, and phpmyadmin
  3. Configure your MySQL
  4. Configure your EC2 instance

ASSUME YOU HAVE AN EC2 INSTANCE RUNNING FOR THIS

Done. 

(JK, kinda. Here's a tutorial for this. Also I currently use the AMI mentioned above in the disclaimer. BTW You will need to SSH into your EC2 instance. Amazon has a pretty good tutorial if you just right click your instance in the management console, and click Connect).

INSTALL MYSQL, APACHE2, PHP5, PHPMYADMIN

Alright, now assuming we are running a clean ec2 instance, here are some of the dependencies you'll need. But before you do anything, just make sure your packages are up to date with the following code in your terminal.

sudo apt-get update

Install Apache (ignore this if you don't plan on using phpmyadmin or another front-facing MySQL app)

sudo apt-get install apache2

That was easy.

Install MySQL

 

sudo apt-get install mysql-server

During the install it might ask you if you want to preconfigure this for Apache. I almost got confused here, but click the Space Button to select, then Tab to hit "OK."

Oh, also, make sure you fill in a password for root (the more secure the better...but write it down).

Install PHP (ignore if you don't need phpmyadmin)

sudo apt-get install php5 sudo apt-get install php5-mysql sudo apt-get install libapache2-mod-php5

To double check everything is working by going into /var/www and adding a php file and try loading it. You may need to restart the apache2 server to check this by doing:

sudo service apache2 restart

Install PhpMyAdmin (not required if you don't think you'll need this)

sudo apt-get install phpmyadmin

Boom! So far so good....

Now try going to the webpage by typing: http://your.ec2.public.dns/phpmyadmin

Cool if it worked. If not bummer :(. Sorry, try again? 

 

CONFIGURE YOUR MYSQL

These next parts are not for the weak....also on a more serious note, these instructions may be specific to my Meerkat Ubuntu server.

But ah yes, what you'll need to do to configure your MySQL is two-fold.

Allow access from an outside IP

You'll need to navigate to your my.cnf file by typing:

sudo emacs /etc/mysql/my.cnf

Then change bind-address from 127.0.0.1 to 0.0.0.0. For me this was on line 52 and should look like this.

bind-address                 = 0.0.0.0

Grant MySQL user permission access from outside IP

Note: replace root with the user you chose (default is root). Replace securepassword with your password. You can change % if you want, as it is, it will allow access from any ip address.

Now you're at the final step my friend...

 

CONFIGURE YOUR EC2 INSTANCE

All you'll really need to do here is edit the security group of your instance to allow for the MySQL default pport (3306), and the HTTP port (80, if you plan to use phpmyadmin or something like that). 

To do this:

>go to the AWS Management Console

>Navigate to your EC2 Instance, and locate the name under the "Security Group" column

>Click Security Groups on the left bar, and then click the name of the security group assigned to your instance

>Click Inbound on the bottom

>Under Create a rule use the dropdown menu to find MYSQL and HTTP and add those both one at a time

>Make sure to click Apply Rule Changes or it won't save.

 

YEEE. Now you shall be ready to go. This will allow you to have a dedicated EC2 instance for all your MySQL database requests. 

Here are some of the tutorials I used to get this info: installing it all and configuring MySQL.

Tutorial: Setting Up Apache/SSL to Serve HTTPS

DISCLAIMER: This is my current setup. Users with other distros beware.

I've just successfully figured out how to build trust in my website for FREE (during my 30 day trial)! I wanted to record it down for myself and hopefully relieve some of the frustration from others who may be trying to figure it out, but have no consolidated tutorial. Again - note the DISCLAIMER above. Along with that fact, this tutorial is basic and will only set it up so all pages are served via HTTPS - will figure out how to do the mix later...

Here are the basic steps we'll walk through to get this to work:

  1. Get an SSL certificate
  2. Install your SSL certificate
  3. Configure Apache to serve as HTTPS
  4. Hope for a miracle

Now... let's do this.

GET AN SSL CERTIFICATE

The first thing you'll have to do is choose where you want to get it from. I was directed to PositiveSSL from a trusted ex-Googler/friend and so that's what I went with. More specifically, because I was just playing around I decided to go with their free trial before I shelled out some ca$h-money. Here's where you'd go for that

PositiveSSL - Free SSL Certificate for 30 days

So now that we are at the page, you need to click through, then discover you'll need to copy/paste a Certificate Signing Request (CSR).

Get a CSR

I would personally make a folder to termporarily store this stuff:

mkdir ssl_stuff
cd ssl_stuff

Then type the following to generate both your private key (the .key file) and public CSR (the .csr file):

openssl req -nodes -newkey rsa:2048 -keyout myserver.key -out server.csr

It will then prompt you for some fields and most of them are optional...but why not:

Enter in your domain name in replacement of mydomain.com under 'Common Name.' It is also suggested that you make a backup of your myserver.key (or private key) just in case (I made mine myserver_backup.key).

Meow, copy paste the contents of your server.csr file back into the webpage and continue on to get your free certificate!

NOTE: You'll have to fill out a couple more annoying forms and hit complete!

Finally, you'll have to wait to get about 3 emails, the first will just be some rando confirmation, then a authentication email, then finally you'll get the email with the zip file of your SSL certificate!!!!!! Onward.

*Where I got my info : Comodo Support CSR Generation

 

INSTALL YOUR SSL CERTIFICATE

Boom! be proud you made it this far. First thing you'll have to do is unzip your SSL certificate, and use Filezilla (or your favorite FTP client) or FTP straight up from Terminal like a boss to move those files (preferably) into the same folder you kept that other ish in. So hopefully the following command will work inside the ssl_stuff folder:

ls   mydomain_com.crt mydomain_com.ca-bundle myserver.key  myserver_backup.key server.csr

Now you'll have to move the private key and certificates to the correct Apache folder. Depending on your distro it will still probably be in /etc/ssl/ somewhere under private and certs folders but here is what I had to do:

KK COOL. Now you're going to have to configure Apache to find and use the SSL Certificate.

You'll need to find where you have your VirtualHost stuff set up but mine was in /etc/apache2/sites-enabled/000-default. Here's what you'll have to add to the file somewhere inside the VirtualHost tags:

*Where I got my info: Comodo Support Ceritificate Installation

Now run the following to just confirm that it works.

sudo a2enmod ssl #enable SSLEngine etc. to work
sudo service apache2 restart

If everything works fine and dandy you're good! If no, it's probably a spelling error...shame on you. 

 

CONFIGURE APACHE TO SERVER AS HTTPS

Alrighty troll, we're almost there. We've got most of this set up. Now we just have a few last Apache configuration steps so stay with me.

Tell Apache to redirect all HTTP requests to HTTPS

Add the following to your Apache httpd.conf file:

And type the following in terminal to enable RewriteEngine (etc) to work and then to check for spelling errors:

sudo a2enmod rewrite
sudo service apache2 restart

Tell Apache to serve through the HTTPS port

The default port being used for your pages is probably 80, which you need to switch to 443. It's as simple as just replacing 80 with 443 in your 000-default file. So it should look something like this:

Open up the HTTPS (443) port on your instance

So navigate over to your AWS Management Console and click:

  • EC2 (on top toolbar)
  • Instances (left toolbar) >> then read what security group it is
  • Security Groups (left toolbar)
  • Name of Security Group your instance was assigned
  • Inbound (new loaded panel on bottom)

And now under 'Create new rule' selected 'HTTPS' and click Add Rule. Finally click Apply Rule Changes (DON'T forget this).

 

HOPE FOR A MIRACLE

Ah young padawan, your final task:

sudo service apache2 restart

YAY! Now if you try to navigate to any page, it should automagically load as an HTTPS url rather than HTTP and everything should work! If not then... well... let me know! I will definitely be much more helpful if your setup is the same as mine, but if not I will do my best!

 

FINAL_NOTE_1: If Chrome/whatever-browser is saying you have unsecure content but loads your SSL certificate, it is probably because some of the scripts (like JQuery or Webfont) you are loading are via HTTP request. Simply change those to HTTPS, re-open your browser, and it should all work!

FINAL_NOTE_2: Here are some other sites I used while making this:

My Amazon EC2 Instance

I've used a lot of tutorials on the web regarding installing/configuring/herp-derping my ec2 instance for my projects and have one thing to be consistent: there is no consistency...

So I've put it upon myself to try to be as consistent (or at least fully transparent) on the configuration/system I am running so that someone who happens to be using my exact setup can follow it step by step, and someone who is not can know right away that the reason my tutorials don't work might just be because of a difference in setups.

So let's get the details out right now:

aws image id - ami-cc405a5 [probably the most important thing to know]

os - Ubuntu Maverick Meerkat [tried upgrading to Natty, f'd everything up]

python - 2.6.6

HTTP server - Apache2

There is probably several more things I need to specify, but for now this is good. Please let me know if you want me to specify anything else, and I will add as I think of important things.