Setting up Apache, PHP and MySQL

1. Web Server

To explore the data we will be collecting, we need to set up a web server that will present web pages to other computers within the network (remembering that this is not intended to be connected to the internet, just inside your home network).

Run the following command;

sudo apt-get install apache2 apache2-utils

Once complete we need to restart our web server with the following command;

sudo service apache2 restart

We can now test our web server from our laptops by opening up a web browser and type in the IP address of our Pi into the URL bar at the top. Hopefully we will see…

2. Installing PHP

To install PHP on Raspbian Stretch you’ll need to run the following command:

sudo apt-get install php

Your Pi will advise you of the range of additional packages that will be installed at the same time (to support those we’re installing (these additional packages are dependencies)). Agree to continue and the installation will proceed. Once complete we will test the PHP installation by putting a file in our web documents folder /var/www/html Create a new file index.php

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

and give it the following script:

<?php
phpinfo();
?>

Tweak permissions

The default installation of the Apache web server has the location of the files that make up our web server owned by the ‘root’ user. This means that if we want to edit them we need to do so with the permissions of the root user. This can be easily achieved by typing something like:

sudo nano /var/www/index.html

but if we want to be able to use an editor from our GUI desktop, we will need the permissions to be set to allow the ‘pi’ user to edit them without having to invoke sudo.

We’re going to do this by making a separate group (‘www-data’ will be its name) the owners of the /var/www directory (where our web files will live) then we will add the ‘pi’ user to the ‘www-data’ group.

We start by making the ‘www-data’ group and user the owner of the /var/www directory with the following command;

sudo chown www-data:www-data /var/www

Then we allow the ‘www-data’ group permission to write to the directory:

sudo chmod 775 /var/www

Then we add the ‘pi’ user to the ‘www-data’group;

sudo usermod -a -G www-data pi

This change in permissions are best enacted by rebooting the Raspberry Pi;

sudo reboot

Now the ‘pi’ user has the ability to edit files in the /var/www directory without problem. By looking typing your PI’s IP address along with a /index.php you should see the following web page:

MySQL Database

We will use a MySQL database to store the information that we collect. We will install MySQL in a couple of steps and then we will install the database administration tool phpMyAdmin to make our lives easier.

From the command line run the following command;

sudo apt-get install mysql-server

You will be prompted (twice) to enter a root password for your database. Note it down somewhere safe.
NOTE: For Raspbian Stretch it’s common to NOT get the password prompt. We’ll fix that later.

Once this installation is complete, we will install a couple more packages that we will use in the future when we integrate PHP and Python with MySQL. To do this enter the following from the command line;

sudo apt-get install mysql-client php-mysql python-mysqldb

Agree to the installed packages and the installation will proceed fairly quickly.

That’s it! MySQL server installed. However, it’s not configured for use, so we will install phpMyAdmin to help us out.

phpMyAdmin

phpMyAdmin²⁸ is free software written in PHP to carry out administration of a MySQL database installation.

To begin installation run the following from the command line;

sudo apt-get install phpmyadmin

Agree to the installed packages and the installation will proceed. You will receive a prompt to ask what sort of web server we are using:

Select ‘apache2’ and tab to ‘Ok’ to continue.

We will then be prompted to configure the database for use with phpMyAdmin. We want the program to look after it for us, so select ‘Yes’ and continue. We will then be prompted for the password for the administrative account for the MySQL database.

This is the root password for MySQL that we set up earlier. Enter it and tab to ‘Ok’ to continue.

We will then be prompted for a password for phpMyAdmin to access MySQL.

The installation should conclude soon.

Once finished, we need to edit the Apache web server configuration to access phpMyAdmin. To

do this execute the following command from the command line;

sudo nano /etc/apache2/apache2.conf

Get to the bottom of the file by pressing ctrl-v a few times and there we want to add the line;

Include /etc/phpmyadmin/apache.conf

Save the file and then restart Apache2;

Now if we go to our browser on our laptops (or on the Pi) and enter the IP address followed by /phpmyadmin (in the case of our example 10.1.1.8/phpmyadmin) it should start up phpMyAdmin in the browser.

If you enter the username as ‘root’ and the MySQL root password that we set earlier, it will open up the phpMyAdmin interface.

Allow access to the database remotely

It might seem a little strange to say that we want to allow access to the database remotely since this would appear to be part of the grand plan all along. But there are different types of access and in particular there may be a need for a remote computer to access the database directly.

This direct access occurs when (for example) a web server on a different computer to the Raspberry Pi wants to use the data. In this situation it would need to request access to the database over the network by referencing the host computer that the database was on (in this case we have specified that it is on the computer at the IP address 192.168.1.74).

By default the Raspberry Pi’s Operating System is set up to deny that access and if this is something that you want to allow this is what you will need to do.

On the Raspberry Pi we need to edit the configuration file ‘my.cnf’ in the directory /etc/mysql/. We do this with the following command;

sudo nano /etc/mysql/my.cnf

Scroll down the file a short way till we find the section [mysqld]. Here we need to edit the line that reads something similar to;

bind-address = 127.0.0.1

This line is telling MySQL to only listen to commands that come from the ‘localhost’ network (127.0.0.1). Essentially only the Raspberry Pi itself. We can change this by inserting a ‘#’ in front of the line which will turn the line into a comment instead of a configuration option. So change it to look like this;

# bind-address = 127.0.0.1

Once we have saved the file, we need to restart the MySQL service with the following command;

sudo service mysql restart