Technology Is Not Dull: Installing MySQL on the Raspberry Pi

Introduction

MySQL is a Free and Open Source relational database owned by Oracle Corporation. Due to concerns about the future of MySQL, MySQL was forked as a community developed version called MariaDB.

MariaDB is intended to be a drop in replacement for MySQL and is increasingly being chosen as the default "MySQL" installation on Linux distributions. Debian, the upstream Linux version for Raspbian changed to use MariaDB recently, hence the references to MariaDB in the installation text.

There are many excellent examples of MySQL installation tutorials on the web, this is just one (how useful you find it is up to you). See the references section for more information and some examples.

Set Up

Update Raspbian installation

This is the standard update/upgrade process to ensure that the Raspberry Pi has the most up to date versions of all installed software before adding new software.

sudo apt-get update
sudo apt-get upgrade

Install MySQL from repository

MySQL is available from the Raspbian repository.

sudo apt-get install mysql-server

Setup and securing the database

Run the secure installation script:

sudo mysql_secure_installation

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY!

In order to log into MariaDB to secure it, we'll need the current
password for the root user. If you've just installed MariaDB, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.

Enter current password for root (enter for none):
OK, successfully used password, moving on...

Setting the root password ensures that nobody can log into the MariaDB
root user without the proper authorisation.

Set root password? [Y/n] y
New password:
Re-enter new password:
Password updated successfully!
Reloading privilege tables..
... Success!
By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB without having to have a user account created for
them. This is intended only for testing, and to make the installation
go a bit smoother. You should remove them before moving into a
production environment.

Remove anonymous users? [Y/n] Y
... Success!

Normally, root should only be allowed to connect from 'localhost'. This
ensures that someone cannot guess at the root password from the network.
Disallow root login remotely? [Y/n] Y
... Success!

By default, MariaDB comes with a database named 'test' that anyone can
access. This is also intended only for testing, and should be removed
before moving into a production environment.

Remove test database and access to it? [Y/n] Y
- Dropping test database...
... Success!
- Removing privileges on test database...
... Success!

Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.

Reload privilege tables now? [Y/n] Y
... Success!
Cleaning up...

All done! If you've completed all of the above steps, your MariaDB
installation should now be secure.

Thanks for using MariaDB!

That is the database set up.

First connection

From the command line.
sudo mysql -uroot -p
Enter the root password.

Once you are logged in, enter the following to create a database.
create database datastorage;
The ending semi-colon is the effective end of line for a command.
grant all privileges on datastorage.* TO 'root'@'localhost' IDENTIFIED BY '<root password>';

Create a local host data reader. This user can only access the database on the server itself.
create user 'localreader'@'localhost' identified by '<user password>'
grant select on datastorage.* to'localreader'@'localhost';

This user is allowed to access the database from anywhere, but only has select access.
create user 'datareader'@'%' identified by '<user password>;
grant select on datastorage.* to'datareader'@'%';

Subsequent Connections

Logging in follows the same process a s before, from the command line.
sudo mysql -uroot -p
Enter the root password

The console does not default to a specific database, so it is best to remember to set the current database (in this case datastorage created earlier).
MariaDB [(none)]> use datastorage
Database changed
MariaDB [datastorage]>

Creating tables

This example table is to record data sent from other machines, by other users.
The table has a primary key (id) which is an integer and auto increments. The created field is a DATETIME and defaults to the current time.

CREATE TABLE datavalues(
id INT NOT NULL AUTO_INCREMENT,
user VARCHAR(50) NOT NULL ,
title VARCHAR(20) NOT NULL,
value FLOAT NOT NULL,
created DATETIME NOT NULL DEFAULT NOW(),
PRIMARY KEY(id)
);