The Raspberry Pi is one of the most popular general purpose computers in modern times. Since its release in 2002, the single board computer has seen massive growth in areas such as robotics and has thousands of projects that have been created thanks to its affordability and flexible I/O options.

While there are many interesting applications for the Pi (which I plan to explore in the future), I will be looking into installing, running, and connecting to a local network MariaDB server instance.

Note: I will be using the Raspberry Pi 3 Model B+ running the Raspbian Stretch OS. Refer to your specific documentation for compatibility with different generations/operating systems.

Up and Running

If you have connected your Raspberry Pi to a monitor and keyboard, you are ready to go. If you are like me and did not bother to buy a monitor and keyboard or would rather connect to your Pi remotely, you have many options to choose from. My preference is to connect via SSH and can be done so quite easily:

“For headless setup, SSH can be enabled by placing a file named ssh, without any extension, onto the boot partition of the SD card from another computer. When the Pi boots, it looks for the ssh file. If it is found, SSH is enabled and the file is deleted. The content of the file does not matter; it could contain text, or nothing at all.”

Once enabled, you can connect to the Pi (assuming it is already connected to the network) using the internal IP address (on Mac):

ssh pi@198.162.X.X

By default, the username is pi and the password is raspberry. For Windows, you can connect with an SSH client like PuTTY. You should now be connected to your Raspberry Pi.

Installation

Before installing MariaDB, make sure that your Raspberry Pi has the latest repository updates, then go ahead and upgrade:

sudo apt-get update && sudo apt-get upgrade

When it is finished, installing MariaDB is simple:

sudo apt-get install mariadb-server

You can also check to see that it is installed and running

# If installed, it should give an output
which mysql

# Check to make sure it is active
sudo /etc/init.d/mysql status

Using Sample Data

Now that you are up and running, we need to create a database and grant access to a user other than root.

Creating a test database

A good sample database to get started with is the datacharmer/test_db on GitHub. Let’s download and unzip the file:

# Go to the Downloads folder
cd Downloads

# Download the file
curl -LOk https://github.com/datacharmer/test_db/archive/master.zip

# Unzip
unzip -a master.zip

# Create the database
cd test_db-master
sudo mysql < employees.sql

Once the database has been created, you can login as root:

# Enter a root password when prompted
sudo mysql -u root -p

From here, you can enter any valid SQL command and see the output. For now, we want to see that the employees database is present

mysql> SHOW DATABASES;

You should see something like this:

Screenshot of employees database

Adding a new user

Now you need to create a user with access to the employees database. Ideally, you would want to create a user with only the permissions needed to get the job done.

mysql> GRANT SELECT,UPDATE,INSERT,DELETE,CREATE,DROP
	-> ON employees.*
	-> TO 'user'@'example.host.com'
	-> IDENTIFIED BY 'password';

However, this can be restricting, especially when still in development or if your IP address will change often. In this case, you can choose more open permissions:

mysql> GRANT ALL
	-> ON employees.*
    -> TO 'user'@'%'
    -> IDENTIFIED BY 'password';

We are now in business. We can connect to our MariaDB server instance on the Raspberry Pi using our newly created username and password. The downside is that we must always SSH into our Pi to access our database.

Establishing a Remote Connection

If we want to access our database outside of localhost, we will need to update the config file to listen on other interfaces.

First, let’s exit and stop the service:

mysql> exit

followed by:

sudo /etc/init.d/mysql stop

Now you can edit the configuration file for MariaDB:

sudo nano /etc/mysql/mariadb.conf.d/50-server.conf

look for a line called bind-address and change the value from 127.0.0.1 to 0.0.0.0 to bind to all interfaces. This should allow you to connect to the server remotely on the same network.

Let’s SSH back in and fire up the server to test our connection:

sudo /etc/init.d/mysql start

Once we confirm that the server is up and running, we can exit the SSH connection and try connecting to the server from the network. Make sure you have MariaDB installed on the machine you are trying to connect from, then you can run:

mysql -u user -p -h 192.168.X.X

If everything is working properly, you will be prompted for the user password you created earlier. Afterwards, you should be able to query your database over the network!

Wrapping Things Up

Now that we have our MariaDB server running on our Raspberry Pi and can access it directly on the network, you can start building an awesome application for your server. Use it as a local database at home, spin up a web server and share your content with the world, or anything else you can think of! Keep hacking, friends!