Web App Development

Setting up MySQL on AWS/Fedora

Warning: I'm not a sysadmin. While I got this working by following the steps below there are probably more secure and maintainable ways to achieve this.

Installing MySQL

MySQL can be installed quite simply using yum:

sudo yum install mysql -y
sudo yum install mysql-server -y

Then start the mysqld service to be able to access the database later:

sudo service mysqld start

Setting a user, a database and adding some dummy date

To run SQL queries against your RDBMS start mysql like this:

mysql -u root

Then run the following commands one by one to create a new user root with "pw" as the password and set up a database called "testdb":

set password for 'root'@'localhost'=password('pw');
create database testdb;
use testdb;
create table tbl (id int, value varchar(50));
insert into tbl (id, value) values (5,"hello world");
insert into tbl (id, value) values (11,"hi things");

Now we also have a table called "tbl" that we can fetch some data from.

If you want to access mysql again you'll enter the password of the user root. Run this command and enter the password when prompted:

mysql -u root -p

Accessing the database in PHP

First we need to install the php-mysql package to have access to the function used in the code further down:

sudo yum install php-mysql -y

Then open the test.php file:

sudo nano /var/www/html/test.php 

And enter this code:

$mysqli = new mysqli("localhost", "root", "pw", "testdb");
$query = $mysqli->query("select * from tbl");
while ($row = $query->fetch_array())
        echo "<br>";

When accessing test.php in the browser you should see this:

Array ( [0] => 5 [id] => 5 [1] => hello world [value] => hello world )
Array ( [0] => 11 [id] => 11 [1] => hi things [value] => hi things ) 

If it's not working try `sudo service httpd restart` or have a look at the logs in /etc/httpd/logs/error_log.


Follow me on Twitter