Since this post is a snapshot in time. I recommend that you download a copy of the book which is updated frequently to improve and expand the content.
---------------------------------------
Installing a Database on the Raspberry Pi
As mentioned earlier in the book, 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.
MySQL
From the command line run the following command;
The Raspberry Pi will advise you of the range of additional packages that will be installed at the same time. Agree to continue and the installation will proceed. This should take a few minutes or more (depending on the speed of your internet connection).
You will be prompted (twice) to enter a root password for your database. Note it down somewhere safe;
 |
| MySQL Installation |
Make this a reasonably good password. You won’t need it too much, so it’s reasonable to make it more secure.
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;
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;
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;
 |
| phpMyAdmin Installation |
Select ‘apache2’ and tab to ‘Ok’ to continue.
We will then be prompted to configure the database for use with phpMyAdmin;
 |
| phpMyAdmin Configuration |
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.
 |
| MySQL Administrative Password |
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.
 |
| phpMyAdmin Administrative Password |
I have used the same password as the MySQL root password in the past to save confusion, but over to you. Just make sure you note it down :-). Then tab to ‘Ok’ to continue (and confirm).
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;
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;
This should have everything running.
Now if we go to our browser on the Windows (or on the Raspberry Pi) desktop 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.
 |
| phpMyAdmin Web |
If you enter the username as ‘root’ and the MySQL root password that we set earlier, it will open up the phpMyAdmin interface.
 |
| phpMyAdmin Web 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 10.1.1.8).
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;
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;
Create users for the database
Our database has an administrative (root) user, but for our future purposes, we will want to add a couple more users to manage the security of the database in a responsible way.
If we click on the ‘Privileges’ tab in phpMyAdmin we can see the range of users that are already set up.
 |
| phpMyAdmin Users |
We will create an additional two users. One that can only read (select) data from our database and another that can put data into (insert) the database. Keeping these functions separate gives us some flexibility in how we share the data in the future. For example we could be reasonably comfortable providing the username and password to allow reading the data to a wide range of people, but we should really only allow our future scripts the ability to insert data into the database.
From the ‘Privileges’ tab, select ‘Add a new user’;
 |
| phpMyAdmin Login Information |
Enter a user name and password.
Then we scroll down a little and select the type of access we want the pi_select user to have in the ‘Global privileges’ section. For the pi_select user under ‘Data’ we want to tick ‘SELECT’.
 |
| phpMyAdmin SELECT user |
Then press the ‘Create User’ button and we’ve created a user.
For the second user with the ability to insert data (let’s call the user ‘pi_insert’), go through the same process, but tick the ‘SELECT’ and the ‘INSERT’ options for the data.
When complete we should be able to see that both of our users look similar to the following;
|
| phpMyAdmin INSERT user |
Create a database
When we read data from our sensors, we will record them in a database. MySQL is a database program, but we still need to set up a database inside that program. In fact while we will set up a database in this step, when we come to record and explore our data we will be dealing with a ‘table’ of data that will exist inside a database.
For the purposes of getting ourselves set up we need to create a database. If we go to the ‘Databases’ tab we can enter a name for a new database (here I’ve called one ‘measurements’) and click on ‘Create’.
 |
| phpMyAdmin New Database |
Congratulations! We’re set up and ready to go.
The post above (and heaps of other stuff) is in the book 'Raspberry Pi: Measure, Record, Explore' that can be downloaded for free (or donate if you really want to :-)).
No comments:
Post a Comment