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.