Setting up a dedicated database server on Raspberry Pi

There is a certain gratification when you get a small “bare metal” minicomputer board to run whatever software you install on it. Maybe even your own app. Most (if not all) real-world applications I’ve implemented connect to a database in some way. It’s no secret that relational databases are the most popular option in mission-critical applications that require true ACID compliance. So installing a good performing SQL database in a Raspberry Pi is, to say the least, a fun exercise to do. Even though the Raspberry Pi can connect to the internet and use a database as a service (DBaaS) like SkySQL, smaller applications can benefit from having a local database only running on the same device.

In this article, I show you how to install and configure a MariaDB server on a Raspberry Pi 4 Model B with 8 GB of RAM that you can connect to your local network via WiFi or Ethernet. You can also use models with much less RAM memory.

By the way, I placed lots of photos and screenshots to make this article more enjoyable. I hope you like!

Before you start

Before you start, make sure you have:

  • a computer (duh!)
  • a Raspberry Pi (double-duh!)
  • an internet connection (don’t you say!)
  • a microSD card (I recommend at least 32 GB)
  • and a USB power supply for the Raspberry Pi (I used a phone charger)

Installing the Raspberry Pi operating system

A Raspberry Pi is a mini computer. So you need an operating system (OS) for that. There are different flavors of Linux you can use, but we’ll be using the optimized Raspberry Pi OS. The operating system is installed on a microSD card which you then plug into your Raspberry Pi:

You can use your work computer to download and install the Raspberry Pi operating system to the microSD card. You will most likely need a microSD to USB adapter or, if your computer supports it, an SD to microSD adapter. Either way, connect the microSD card to your computer and you’ll be good to go:

Download and install the Raspberry Pi Imager software on your computer. Launch the tool and click CHOOSE SE:

Click on Raspberry Pi operating system (other):

Select Raspberry Pi OS Lite (64 bit) or the 32-bit version depending on your model of Raspberry Pi. The Lite version of the operating system does not include any GUI, which is a better option when installing a database server or any software waiter. A GUI would use space in the storage device (the microSD card) and probably run additional processes that aren’t really needed and would also consume RAM. Then go for the Lite (headless) version:

Click the gear icon (settings), enter a hostname (I use raspi01), and enable SSH with password authentication:

Scroll down and set a username (mine is ft) and the password of your choice. Also enter your WiFi connection details:

Click on TO REGISTER then on CHOOSE STORAGE. Select the volume that corresponds to the microSD card plugged in:

Make sure you’ve selected the correct media (you don’t want to format your hard drive, do you?) and if everything looks ok, click YES:

You will be prompted to enter your password. Once the process is complete, click CONTINUE:

Extract the microSD card (now with fully operational Debian-based Raspberry Pi OS) and plug it into your Raspberry Pi:

Remote login via SSH

It’s time for the most fun part. Connect USB-C power to the Raspberry Pi. This turns on the device and boots the operating system. Take advantage of the small red (power indicator) and green (a program is running) LED:

Very cool. I’m excited for you as I write this! Give the device some time to boot up. Be patient. Depending on the Raspberry Pi model, this may take several minutes. In my case, when the green LED started blinking less or was completely off, I knew the minicomputer was ready and (hopefully) connected via WiFi to my local network.

To start talking with this little machine, you can use SSH, which attaches a terminal to the device. To do this, open a terminal or command prompt on your computer and run the following command:

ssh [email protected]

Use the username and hostname that you configured when installing the operating system. If all goes well, you should be prompted to add the host to the known hosts list. Type yes and enter your Raspberry Pi OS password. You are now :

Sweet! Now is a good time to update your Raspberry Pi. Run the following:

sudo apt update
sudo apt upgrade

Installing MariaDB Database Server

Let’s make your new server more usable by installing MariaDB on it:

sudo apt install mariadb-server

It can’t be easier than that! A SQL client is included with the server installation, so you can connect to the database and verify that it is working:

sudo mariadb

Try running a “Hello, World” query there:

Pleasant. Before exiting the client, create a new database (more on this later) and a user for it. It is good practice not to use the root user when consuming the database from your applications. Here is what you need to run:

CREATE DATABASE nation;

CREATE USER 'user'@'%' IDENTIFIED BY 'Password123!';

GRANT ALL PRIVILEGES ON nation.* TO 'user'@'%';

You can now quit the SQL client:

quit

We will remotely connect to the database using the above user later.

Securing the database server

You can use the database in its current state, but it’s always a good idea to secure the server. MariaDB includes a tool that facilitates this process:

sudo mariadb-secure-installation

You will be prompted with the root password which by default is blank, so just hit enter. The tool asks you questions as it goes. Feel free to configure your database server as you wish. For reference, here is how I answered the questions:

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

haven't set the root password yet, you should just press enter here.

 

Enter current password for root (enter for none):

OK, successfully used password, moving on...

 

Setting the root password or using the unix_socket ensures that nobody

can log into the MariaDB root user without the proper authorisation.

 

You already have your root account protected, so you can safely answer 'n'.

 

Switch to unix_socket authentication [Y/n] n

 ... skipping.

 

You already have your root account protected, so you can safely answer 'n'.

 

Change the 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!

Enabling remote database access

Since you probably want to connect to this database remotely from your work computer or even another Raspberry Pi, you will need to enable remote access. Edit the following file using nano Where vi:

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

Comment out the following line by adding a hash mark (#) character at the beginning as shown:

#bind-address = 127.0.0.1

That’s it. Save the changes, quit the text editor and exit the little machine by ending the SSH session:

exit

You are now back in your work computer. If you happen to have the MariaDB client installed on your computer, you can try the remote connection right away (skip this if you don’t have the tool, or install it):

mariadb -h raspi01.local -u user -p

If you see Welcome to MariaDB Monitoryour database is ready!

Connecting to the database using DBeaver

You most likely want to use a graphical SQL database client. In my opinion, the best free option is DBeaver. If not, download and install it.

Open DBeaver and click on the New database connection button:

Select MariaDB and click on Next >:

Enter the server host (in my case it is raspi01.local), the identifier (user) and the password. Click on Test the connection… to ensure that all connection details are correct:

You should see a message like this:

Click on OKAYand Finish. You should see the new remote connection in the Database browser:

Creating a demo database

The MariaDB Tutorial website (accessories for authors) has a nice demo database you can use to experiment with. Download and extract the ZIP file containing the SQL script. Select Case > File open… in DBeaver and open the nation.sql case.

Click it Current data source button:

Select the Raspberry Pi connection and click Select:

Click it Run SQL script button:

You now have a populated demo database to play with! In the Database browser, browse the tables and double-click one of them. For example, explore the Data tab after opening the tab countries table:

Isn’t it cool to see all this data coming from the little computer?

Execution of SQL queries

The point of having a database is to be able to run custom SQL queries. In the Database browserright click on nation and select SQL Editor > New SQL script. For example, try querying the languages ​​spoken in your country. Here is my result:

There are many interesting queries you can run, but I’ll leave those to you. For reference, here’s an ER diagram of the database I took from the MariaDB Tutorial website:

And after?

You might want to try ColumnStore next (get it here). Or maybe use one of the MariaDB connectors and build your own apps using Java, JavaScript/Node.js, Python, or C++.

Ah! One last thing. If you want to safely shut down your Raspberry Pi, SSH in and run the following:

sudo shutdown now

Happy querying and database coding!

Also posted here.

LOADING
. . . comments & After!

Maria H. Underwood