Getting PHP and MySQL running on Amazon EC2

Posted: March 17th, 2011 | Author: | Filed under: AWS, Hosting, Tutorials | Tags: , , , , , | 29 Comments »

Do you know nothing about Amazon Web Services (AWS) or Linux server administration, but want to get a PHP/MySQL server set up on AWS? I was once like you, relying upon my web host to have PHP and MySQL installed and configured, so it was a bit daunting initially to work with AWS, but it’s actually rather simple. Read on and I’ll show you how to set up PHP and MySQL on one of Amazon’s free servers step by step. You can have a functioning site up and running within half an hour.

Amazon Web Services

First things first: Amazon Web Services has a ton of different products, but the one you want is Amazon Elastic Compute Cloud (EC2). Go there, and click “Sign Up for Amazon EC2”.

Once you’ve gotten that set up, go to the AWS Management Console, and click on “Instances” in the Navigation panel. An Instance is just a virtual server – so let’s create one! Click “Launch Instance” under “My Instances”, and select “Basic 64-bit Amazon Linux AMI”. On the Instance Details phase, select “Micro” (which is Free tier eligible). Continue until you need to enter the “Name” – if you don’t know what else to call it, just use “Web/DB server”.

Next you create a Key Pair – this will be the credentials you’ll use to SSH into the box. The instructions here should be fairly straightforward. Next is the Security Group, which will be used to specify the firewall used for your instance. Feel free to use the default Group for now. Continue to the Review phase and launch it!

You should now be able to SSH into your instance using your .pem file with ssh -i [FILE NAME].pem ec2-user@ec2-[IP ADDRESS].compute-1.amazonaws.com. Alright, we’ve got a server up and running! However, you may notice that this server has very little installed on it. which php? Nothing. which mysql? The same. Let’s install some software.

Configuring the Linux Server

Below I’ll show you how to set up PHP and MySQL on the server. I’ve separated PHP and MySQL so that it’s easier to adapt this to having two instances.

PHP

First, the basics for PHP: 

sudo yum install php-mysql php php-xml php-mcrypt php-mbstring php-cli mysql httpd

Press ‘y’ for each of the prompts that shows up. Note that you’re logged in as ec2-user, so you need to sudo all of these commands.

You should now be able to create and run a PHP test file. Next, let’s get MySQL up and running.

MySQL server

First, install and begin running the server: 

sudo yum install mysql-server
sudo /etc/init.d/mysqld start

Next, set the root password. I’ve found this password generator to be just dandy. 

mysqladmin -u root password '[PASSWORD]'

Now we set up two users for MySQL: the administrator, which you’ll use to create and modify tables; and the app user, which the app will use to query the DB (with more limited privileges). Log into MySQL as root (mysql -u root -p) and enter each of the following lines: 

CREATE DATABASE [DB NAME];
CREATE USER '[DB NAME]_admin'@'localhost' IDENTIFIED BY '[ADMIN PASSWORD]';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER ON [DB NAME].* TO '[DB NAME]_admin'@'localhost';
CREATE USER '[DB NAME]_user'@'localhost' IDENTIFIED BY '[USER PASSWORD]';
GRANT SELECT, INSERT, UPDATE, DELETE ON [DB NAME].* TO '[DB NAME]_user'@'localhost';

You may want to fine-tune your database settings further than this, but this is a good start.

Make it web-accessible

We now have PHP and MySQL running on the box, but cannot access it through a browser. We need to configure the web server and set up an Elastic IP.

Web Server

First, let’s create a test PHP file that will be accessed by the browser. Create directories so that you can put your file in /opt/app/current1. Make an index.php file that contains whatever you want.

If you want to FTP transfer files to your server, you’ll want to give the ec2-user permissions to modify files in your web directory: 

sudo chown ec2-user /opt/app/current

To set up the web server, httpd, we need to first modify its configuration file, located at /etc/httpd/conf/httpd.conf. Open it up with vim, emacs, or your favorite text editor, and go to the bottom of the file. Here you’ll see a small section on the VirtualHost (between <VirtualHost *:80> and </VirtualHost>). Uncomment it out and set DocumentRoot to /opt/app/current. Restart (or start) httpd: 

sudo /etc/init.d/httpd restart

Elastic IP and Security Groups

In the AWS Management Console, click on “Elastic IPs”, then “Allocate New Address” under “Addresses”. Once the address is created, click on it, then “Associate Address”. Select the instance and associate it.

Now click on “Security Groups” in the Navigation panel. Select the Security Group that you used for the instance (probably the default one). Under the “Inbound” tab, add an HTTP rule (port 80). Click “Apply Rule Changes”, and you should now be able to access your website! In your browser, go to http://ec2-[IP ADDRESS].compute-1.amazonaws.com/, where the IP address is the Elastic IP you made with periods replaced with hyphens.

Hello World! or Putting it all together

We now have all the pieces we need to access MySQL from PHP and serve that to the browser-accessible website. So let’s log into mysql and create a sample table: 

mysql -u [DB NAME]_admin -p
[type password]
mysql> use [DB NAME];
mysql> CREATE TABLE test (message VARCHAR(255));
mysql> INSERT INTO test (message) VALUES ('Hello world!');

Now modify your index.php file (/opt/app/current/index.php) to be the following: 

<?php
$conn =  new mysqli('localhost', '[DB NAME]_user', '[USER PASSWORD]', '[DB NAME]');
$result = $conn->query("SELECT message FROM test;");
$row = $result->fetch_assoc();
echo $row['message'];
?>

We now have a fully functioning PHP and MySQL server!

Taking it further

That’s it for the basics, but there’s so much more that you can do now.

PHPUnit

sudo pear upgrade
sudo yum install php-pear
sudo pear channel-discover pear.phpunit.de
sudo pear channel-discover pear.symfony-project.com
sudo pear channel-discover components.ez.no
sudo pear install phpunit/PHPUnit

phpMyAdmin

I’ve found it handy to set up an administration area for my sites using a different port on the same URL. Note that port 80 is the default for web traffic, but 8080 is also commonly used.

Create /opt/app/admin. Then, in httpd.conf, add the line Listen 8080 after Listen 80 and add another VirtualHost entry, using <VirtualHost *:8080> and pointing to the /opt/app/admin directory. Update your Security Group to allow traffic over port 8080. Make sure to restart Apache and you should now be able to access your admin folder through your browser at yourdomain.com:8080.

You can then download phpMyAdmin into /opt/app/admin/pma and unzip it. Using the [DB NAME]_admin user, you can now manage your databases there through your browser.

Using two Instances

It can be very beneficial to performance to separate the web server and the DB server. To do this, you’ll need to set up two Instances, one of which has the web server httpd running and an Elastic IP, and the other of which has the MySQL server mysqld running.

They can use the same Security Group, but you’ll have to add the MySQL rule (port 3066) for Inbound traffic to allow the servers to talk to each other.

On the web box, instead of using “localhost” as the MySQL host, use the Internal IP address of the MySQL box. On the DB box, set up your grant permissions to allow from anywhere in '%.ec2.internal' (or just from your IPs).

Notes

  1. /opt/app/current is a Rails convention that I enjoy. What you should do is put your releases in /opt/app/releases/[release #], then have /opt/app/current be a symlink to the current release.

    Another (much more) common standard is to put web-accessible code in /var/www/html. Feel free to put your HTML code wherever you want; just make sure to update httpd.conf appropriately (and restart Apache).

    ^ Back up

Thanks to Ryan Ausanka-Crues at Palomino Labs for help with this.

29 Comments on “Getting PHP and MySQL running on Amazon EC2”

  1. 1 D K said at 5:55 pm on June 6th, 2011:

    Thanks this was very helpful.

  2. 2 Victor said at 2:35 am on June 18th, 2011:

    Really well described. Thank you. I will see if this works for me. Sounds like a great alternative to maintaining web hosts.

  3. 3 Ron dakner said at 1:28 am on July 6th, 2011:

    Thanks for the step by step instructions. How long does it take to set up such structure?

  4. 4 Alex Korn said at 9:44 am on July 6th, 2011:

    Ron, it should take less than half an hour to get the basic structure set up. I’ve added that info to the top of the post.

  5. 5 Anders H said at 2:03 pm on August 9th, 2011:

    Thanks very much for the instructions, very useful!

    One question though; wouldn’t using elastic IPs for communication between the servers lead to extra data transfer costs that could be avoided by using internal IPs?

  6. 6 Alex Korn said at 8:27 pm on August 10th, 2011:

    Anders, good question. You should be using the internal IPs when using multiple servers. I’ve clarified this in the post.

  7. 7 SC said at 3:31 pm on September 9th, 2011:

    Thanks for the tutorial, got me up and running fast. Since this installs MySQL 5.1 to you know the command to install 5.5 or even 5.6 instead?

  8. 8 Alex Korn said at 3:36 pm on September 9th, 2011:

    SC: Use “yum install mysql55” (or mysql56).

  9. 9 SC said at 3:40 pm on September 9th, 2011:

    Wow Alex, thanks for the quick response.

  10. 10 Alex Korn said at 3:42 pm on September 9th, 2011:

    No problem 🙂

  11. 11 Andy said at 10:10 pm on October 2nd, 2011:

    Nice work. Got it working as per your guidelines above. Amazed I couldn’t find a guide like this at AWS…….

    Thanks again.
    Andy

  12. 12 David Covington said at 11:48 am on November 8th, 2011:

    Being a newcomer to AWS and PHP, I spent about 4 hours working through the above before it finally started working. The fifth argument to new mysqli() had to be removed since it expected a long, and I had to add
    $row = mysqli_fetch_row($result)
    echo $row[0];
    to get the desired output, as $result would not convert to string. Perhaps I installed PHP different from the above, or did something strange in putting the string in the test database.

  13. 13 Alex Korn said at 1:53 pm on November 8th, 2011:

    David, thanks for pointing out the error – that was definitely my mistake. Sorry about the frustration I’m sure that caused.

    I updated the post, but using the object-oriented style (versus procedural) and fetch_assoc() instead of fetch_row().

  14. 14 Julius said at 1:22 am on November 9th, 2011:

    Hi, thanks so much for providing this tutorial.

    I am stuck at the PHPmyAdmin setup. I’ve followed all the instruction to the detail and it still says error “The connection to the server was reset while the page was loading.”

    So you said we have to unzip everything into folder /opt/app/admin/pma.

    When I access from http
    http://[MyIP].compute-1.amazonaws.com:8080 , will it try to access the file /opt/app/admin/pma/index.php ?

    Please advise.

  15. 15 Alex Korn said at 7:00 am on November 11th, 2011:

    Julius, it sounds like either your httpd.conf isn’t correct, your Security Group isn’t allowing traffic over port 8080, or you haven’t unzipped PMA correctly.

    To make sure the issue isn’t with PMA, try just putting a simple index.html file in /opt/app/admin. Also make sure you’ve restarted Apache after editing httpd.conf.

  16. 16 Mark said at 8:32 am on November 23rd, 2011:

    Thanks – was great help. The only thing that i tripped up on was that i can only FTP files to the /home/ec2-user folder, and then sudo mv them to where I want them. I was trying to put them directly where i needed them in FileZilla. Not a problem when i know to do it

  17. 17 Alex Korn said at 6:27 pm on November 27th, 2011:

    Mark, you want to run
    sudo chown ec2-user /opt/app/current
    to give your ec2-user permissions to edit those files.

    I added a note about this in the post.

  18. 18 mynab said at 3:07 am on December 15th, 2011:

    Hi. Thanks for the procedure. Worked like a charm except that I cannot access my admin stuff on 8080. I updated the security group to add 8080 (HTTP*) and my httpd.conf with:

    DocumentRoot /opt/app/phpmyadmin
    ….

    Restarted httpd but to no avail. If I use the :80 host to access /opt/app/phpmyadmin it works like a charm. What am I missing?

    Thanks!

  19. 19 mynab said at 6:48 am on December 15th, 2011:

    OK. Do not know if I got it right but just adding Listen 8080 in httpd.conf fixed my issue.

  20. 20 Alex Korn said at 12:43 pm on December 17th, 2011:

    mynab, “Listen 8080” is correct. I’ve added that to the post.

  21. 21 Mike said at 6:31 pm on December 19th, 2011:

    Holy crap I can’t tell you how useful this was for me. I’ve been bumbling through the AWS web interface for about a month, and in a little over an hour I got my site up and running thanks to you.

    If you’re ever in New York City, I’ll buy you a drink, I owe you for this.

  22. 22 Alex Korn said at 8:44 pm on December 19th, 2011:

    Mike – awesome! Glad I could help.

    I love NYC. Rock on.

  23. 23 Bret Truchan said at 10:44 am on February 11th, 2012:

    Hi Alex,

    Great tutorial!

    It might be worth noting after your instructions to “Make an index.php file that contains whatever you want.” that the ownership of index.php should be set to ec2-user. Otherwise Apache can’t read the file.

    For example:

    sudo chown ec2-user /opt/app/current/index.php

    Also, I would love it if you could expand the instructions for using two instances. I tried it but ran into problems because I’m too newbie! Ha ha ha. For example, if you could explain the exact syntax to grant permissions, that would be great. And, if you could be a bit more detailed about how to set up the security groups and such, it would be a life saver.

    Thanks again!
    – Bret

  24. 24 Jorge Rodrigues said at 7:07 pm on February 22nd, 2012:

    Alex, I am trying execute this step “mysqladmin -u root password ‘[PASSWORD]'” but it’s not working. I’m receiving this message: “mysqladmin: connect to server at ‘localhost’ failed
    error: ‘Access denied for user ‘root’@’localhost’ (using password: NO)’
    ” Could you help me please?

  25. 25 Alex Korn said at 3:47 pm on February 23rd, 2012:

    Bret,

    As long as you’re running httpd as root (i.e. using sudo), Apache should be able to read the file without permission changes. However, I still recommend changing permissions for the entire /opt/app/current directory for FTP access.

    When I have some time I’ll try to write a follow-up with more precise directions on how to split this out into two Instances. In the meantime, best of luck!

  26. 26 Alex Korn said at 3:55 pm on February 23rd, 2012:

    Jorge,

    “mysqladmin -u root password ‘[PASSWORD]'” will only work when you have not yet set a root password for MySQL. It looks like you’ve already set a password.

    If you don’t remember what your password is, try logging into MySQL with an empty password (“mysql -u root -p”, [blank] to password prompt).

    If that doesn’t work, just uninstall (yum erase) and reinstall mysql-server.

  27. 27 Jorge Rodrigues said at 2:39 am on February 24th, 2012:

    Alex, tks a lot!

  28. 28 Dennis said at 7:10 am on May 9th, 2012:

    I have one question, would be great if someone could help me:

    I have followed the instructiosn above and allowed FTP with
    sudo chown ec2-user /opt/app/current

    But now I have two directories:
    One /opt/app/current
    and one /home/ec2-user/opt/app/current

    Via SFTP I can acces the second dir but not the original where httpd-conf is pointig to. Should I change the httpd.conf directories to the second or am I totally on teh wrong track?
    Thanks in advance

  29. 29 Alex Korn said at 5:36 pm on May 10th, 2012:

    Dennis, I would imagine that your SFTP program is uploading to “opt/app/current”, not “/opt/app/current” (note the leading slash). Without the leading slash, it will upload relative to the default directory, which for ec2-user, is “/home/ec2-user”.