Sam Starling
30 October 2010

Installing MySQL on an EC2 Micro Instance

I’ve been doing a fair bit of work with Amazon EC2 instances recently, for my final year project at university. If you’ve not come across them before, then it’s basically Amazon’s way of making web-scale computing easy to set up and access. And it’s even more interesting when you see that they’re bringing in a free usage tier for the Linux Micro instances from 1st November this year.

When you create a new instance, you can choose an OS. If you choose “Basic Amazon Linux” (like I did) then you’ll get a stripped-down version that doesn’t have some bits you might be used to (like apt-get). I had to install MySQL recently, so here’s how to do that on such a setup:

sudo yum install mysql
sudo yum install mysql-server
sudo yum install mysql-devel
sudo chgrp -R mysql /var/lib/mysql
sudo chmod -R 770 /var/lib/mysql
sudo service mysqld start

By this stage, you’ll have MySQL installed and the service started. The next step is to set a password for the root user:

/usr/bin/mysqladmin -u root password yourpasswordhere

If you only want to use MySQL internally, then you’re all done now. But if you want to access MySQL externally then you’ll need to follow a few extra steps.

Firstly, go to the AWS Management Console, and find the Security Group that you assigned to your instance when you first set it up. Add “MySQL” to the group using the dropdown, or manually add port 3306. Save your changes.

Finally, create a MySQL user which is able to connect from any host (identified by a percent sign below) by running the following SQL using mysql from your instance’s command line:

mysql> CREATE USER 'myuser'@'localhost' IDENTIFIED BY 
    -> 'yourpasswordhere';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'localhost'
    -> WITH GRANT OPTION;
mysql> CREATE USER 'myuser'@'%' IDENTIFIED BY 'yourpasswordhere';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'%'
    -> WITH GRANT OPTION;

And then you’re done, ready to connect from anywhere with that username and password.