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.
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.
Go across the hall and look at your Pi. Then use ssh to connect to it. All Linux and Mac OS systems come with ssh installed. If you are using Windows, try downloading PuTTY.
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.
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.
Use the following command to install the MySQL database management system. Do this even if MySQL is already installed.
…$ sudo apt-get install mysql-server
Set the password for the MySQL root user to be the same as the one you used to log into the system.
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.
Type a one-line command that will list all installed packages with names that start with the letters “emacs” and end with the letters “common”. You can do this only using dpkg-query. (We have to do query a lot in this course.)
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
mysql database with the
mysql program to look
at some of the administrative tables.
mysql> use mysql ; mysql> show tables ; mysql> select * from user ;
password attributes with the
Do you know why some people might spend $60 for this door mat? If not, take a look at a relevant Wikipedia page which even mentions MySQL as a special case. By the way, the T-shirts and coffee mugs are much cheaper.
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.)
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.
Do the following using your database administration superpowers. Do not use bad words for the names of any accounts, passwords, or databases. Later you will need to share these with your fellow classmates.
- Create an additional database user with a password.
- Create a database.
- Grant the new user complete access to that database.
To verify your work,
password attributes from the
table of the
Using your database in MySQL
While logged into your Pi, use the mysql program to connect to your new database as the new user and create a table within that database.
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
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 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
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.
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
If you execute the following command on the Pi, you will see that the
/var/www directory is only accessable by
…$ ls -ld /var/www
This is bad. There is a special group, called
for web adminstrators,
so let’s place all those files in the
group using the following magic command to allow members of
www-data (not to be confused with “group w”)
to placed files in the
…$ 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.
pi user is not in the
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 — PHP info</title> </head> <body> <header> <h1>CSCI 343 lab — PHP info</h1> </header> <article> <?php phpinfo() ; ?> </article> </body> </html>
Download the URL http://uncacsci-pi-X.cs.unca.edu/testout.php to see if PHP is working.