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.

WhereMyFriends.Be up and running! Finally, I can sleep

Let's make this post quick, since I would like to get some real sleep... WhereMyFriends.Be is finally up and running for good (at least so it seems...)!!!! Thanks VERY VERY VERY much to a Paul from Rackspace who helped me figure it out after several support staff members over the past 72 hours have just thrown me in several directions. Paul, you're a boss. Fortunate for me, he had just finished creating data structures for a very similar purpose dealing with the Facebook API and had a lot to offer. Basically he told me:
  • Facebook/MySQL likes short bursty queries over long ones
  • Ints are greater than strings
The latter really saved the day. The biggest change he helped me make was changing the oauth ids to bigints rather than the current text in SQL and also indexing this. This made everything load at SUCH a faster speed. 100X better than originally, and at least 1000X better than the slow crappy times! K maybe exaggerating a bit. But yeah. I believe this is our solution to keeping up a stable site for thousands of concurrent users. I will wake up in about 14 hours (longer if I'm lucky) and hope to see things going just as smoothly as I left them.