Amazon Web Services RDS provides everything you need to launch and use MySQL in the Cloud. However, for some applications, a dedicated database server may be overkill and too costly. In this article, I detail how to install MySQL on any Amazon Linux instance. In keeping with our ‘everything must be scripted’ philosophy, you will find ‘everything scripted’.

First, on the AWS instance, we ‘sudo su’. Then we run the yum commands to install MySQL Server and the MySQL command line tools, change some file permissions, set auto start on boot and then start the server:

# get root power
sudo su
# install mysql server and tools
yum -y install mysql mysql-server
# change permissions
chgrp -R mysql /var/lib/mysql
chmod -R 770 /var/lib/mysql
# set mysql to start on boot
chkconfig --levels 235 mysqld on
# start mysql
service mysqld start

Next, we change the root password (here 0123456789 but you should use your own):

mysqladmin -u root password 0123456789

Here’s how to create a new user and grant full privileges. First we connect to MySQL:

mysql --user=root --password=0123456789

This will give us a direct sql command line. Type the following to create the user tester with password tester1234:

create user 'tester'@'localhost' identified by 'tester1234';
grant all privileges on *.* to 'tester'@'localhost' with grant option;

This would allow the tester user to connect only from localhost (which is fine if you are only connecting to the database from eg PHP running on the same server). But if you want other servers to be able to connect, or you want to connect up to the database with MySQL Administrator or Query Browser from outside the Cloud, you will need the following:

create user 'tester'@'%' identified by 'tester1234';
grant all privileges on *.* to 'tester'@'%' with grant option;

Now tester can connect from any IP address. Don’t forget to open up the required ports in your AWS Security Group if you need access from anywhere not on the actual server. For connections from outside your AWS VPC, you should only allow access from your own IP address.

Say you have a database script you want to run, called db.sql and found in /home/ec2-user, you can do so with:

cd /home/ec2-user
mysql --host=localhost --user=tester --password=tester1234 --execute="source db.sql"

For more sql examples and PHP integration, please check Chapter 11 – MySQL Database in AWS Scripted. For how to automate the upload and execution of scripts, please check Chapter 13 – Uploading a New Database in AWS Scripted.