CSCI 343 — Database Administration

Goal

The goal of this lab is to demonstrate another résumé-extending skill, in particular, installing and administering a network MySQL database.

Admittedly much of this lab is about system and web server administration, but that is something you should know. What you will do here is similar to what you would do to set up a database and web server in the cloud.

Your computer

The Department of Computer Science owns a few Raspberry Pi systems that are connected to the “playpen” VLAN. We may not have enough for everyone in the class to use, so there may need to be a little sharing.

The computers have names similar to uncacsci-pi-X.cs.unca.edu where X is a single letter. In this lab you’ll use ssh to connect to you Pi using the account pi. You’ll be told the name of your Pi and the password to the account in lab.

…$ ssh pi@uncacsci-pi-X

By the way, ssh and sftp are often used to control servers in the cloud, so these are programs worth knowing.

Getting your own Raspberry Pi

The Raspberry Pi computer costs $35 but requires a few “accessories” to be using. However, for about $100 you can obtain a pretty good system. Take a look at the web pages for the Raspberry Pi Foundation or just do an internet search to see how to get started. Several CSCI majors already have one, so you can find some locals to talk with about the system.

Installing MySQL

Start by typing the following command to determine if you have a web server, database, or PHP installed on your Pi. They probably aren’t there.

…$ dpkg-query -l | egrep "httpd|php|mysql"

The egrep command performs a regular expression search on a file. Be sure you are a regular expression expert before you graduate.

Being a system administrator

The sudo command allows users listed in /etc/sudoers to performed logged system administration commands.

…$ sudo apt-get install mysql-server

MySQL should now be running. However, use the following command to make sure this is the case.

…$ ps gaux | grep mysql

Now take a quick look at the documentation for the five programs you have just used.

• apt-get
• dpkg-query
• egrep
• ps
• sudo

Getting started with my MySQL

MySQL administrators use the regular mysql to get their jobs done; however, they do need to log in using the administrator account.

[…]$ mysql -p -u root

The administrative information is kept in a special database call mysql. Use the mysql database with the mysql program to look at some of the administrative tables.

mysql> use mysql ;
mysql> show tables ;
mysql> select * from user ;

Accounts and databases in MySQL

At this point, you need to create an ordinary MySQL user and database and grant the user access to the database.

Here are some links to the documentation of how this is done. (Real database administrators read documentation.)

• CREATE USER
• CREATE DATABASE
• GRANT

Part of your job for this lab is to be able to figure out how to create users and database from the documentation, but here is one question you need be able to answer between going much further.

• What is the difference between the 'adamsjq'@'localhost' and 'adamsjq'@'%' account names? You may want to look at Specifying Account Names to determine the answer.

To verify your work, SELECT the user, host and password attributes from the user table of the mysql database.

Using your database in MySQL

The network MySQL

Now try to connect to your database from a remote computer, that is, any computer but your own Pi. I suggest you use the workstation you are sitting at in RRO 223. The command looks something like the following. By the way, this will probably fail. We will show you how to fix it in just a minute.

…$ mysql -u username -h uncacsci-pi-X.cs.unca.edu -p database

If it failed, fixing this one requires a little networking magic.

Execute the following command on your Pi to see which IP (Internet Protocol) interface your Pi is listening to.

…$ netstat --ip -l

The output of this command should inform you that your Pi database server is only listening at 127.0.0.1, its local port.

The problem is that the default configuration for MySQL, stored in the /etc/mysql/my.cnf configuration file, does not allow for remote connections. Use the following command to look at the configuration file. Watch for a line containing 127.0.0.1, your home address.

…$ more /etc/mysql/my.cnf

What you have to do is change that 127.0.0.1 to 0.0.0.0, a special address that accepts both local and network connections. We could talk about network interface bindings now, but that sounds like a topic for a different course. You could edit this file with nano and make the change, but here is a one-liner from the command line that will do the trick.

…$  sudo sed -i 's/127.0.0.1/0.0.0.0/' /etc/mysql/my.cnf

The i and the s specify that an in-place substitution is to be performed on the file.

Now you must restart the MySQL server using the service command to make it read the new configuration file.

…$  sudo service mysql restart
…$  netstat --ip -l

Try the remote connection again and see if it now works.

…$ mysql -u username -h uncacsci-pi-X.cs.unca.edu -p database

Now connect to the MySQL database of someone else. They will have to tell you the user, password and database names to use. Insert some values into their database. You will need to use the SHOW TABLES and SHOW COLUMNS command to figure out what they are storing in their database.

By the way, in the real world you may need to get the network administration to remove a firewall block.

Getting PHP to work

Now we need to install a web server with PHP support. Try the following apt-get command for this one.

…$ sudo apt-get install lighttpd php5-common php5-cgi php5 php5-mysql php-pear

Because apt-get installs needed dependencies, you probably could have omitted a few of those packages, but better safe than sorry.

You can try connecting to http://uncacsci-pi-X.cs.unca.edu/ to see if you Pi’s web server is working, or you can just type the command wget http://localhost on your Pi.

Enabling PHP

In any case, you need to type two special commands to enable PHP and restart the web server.

…$ sudo lighty-enable-mod fastcgi-php
…$ sudo service lighttpd restart

One more system administration task

The files you are serving from your Pi are, by default, located in the /var/www directory. If you execute the following command on the Pi, you will see that the /var/www directory is only accessable by the root user.

…$ ls -ld /var/www

This is bad. There is a special group, called www-data for web adminstrators, so let’s place all those files in the www-data group using the following magic command to allow members of group www-data (not to be confused with “group w”) to placed files in the /var/www directory.

…$ sudo chown www-data:www-data /var/www
…$ sudo chmod g+w /var/www

Now try out that command to check out permissions on the /var/www directory.

Unfortunately there is still one small problem. The pi user is not in the www-data group. However, you can change that with a single command.

…$ sudo adduser pi www-data

Unfortunately there is still one small problem. You need to log in and out of your Pi to reinitialize the group settings.

One final test

Glad to have you back Use the following sequence of commands to copy a PHP test file to your Pi. The file renaming is needed to make sure that the PHP file is not executed as a PHP file.

…$ cd /var/www
…$ wget http://www.cs.unca.edu/brock/classes/Spring2014/csci343/labs/testout.php.zip
…$ unzip testout.php.zip

All that ziping is necessary to prevent the web server on www.cs.unca.edu from running the PHP file rather than just copying it. Here is what that uncompressed PHP file you copied should look like. You could just cut-and-paste this into your Pi.

<!doctype html>
<html lang="en">
<head>
<meta charset="utf-8"/>
<title>CSCI 343 lab &mdash; PHP info</title>
</head>
<body>
<header>
<h1>CSCI 343 lab &mdash; PHP info</h1>
</header>
<article>
<?php
phpinfo() ;
?>
</article>
</body>
</html>