There is certain gratification when you get a little “naked” mini-computer board to run the software you install on it. Maybe even your own application. Most (if not all) of the real-world applications I have implemented connect in one way or another to a database. It’s not a secret that relational databases are the most popular option in mission-critical applications that require truly . So, installing a good performant 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 consume a Database as a Service (DBaaS) like SkySQL, smaller applications might benefit from having a local-only database running on the same device. ACID compliance In this article, I show you how to install and set up a MariaDB server on a with 8 GB of RAM that you can connect to your local network through WiFi or Ethernet. You can use models with much less RAM memory as well. Raspberry Pi 4 Model B By the way, I have placed plenty of photos and screenshots to make this article more enjoyable. Hope you like it! Before You Start Before you start, make sure you have: a computer (duh!) a (double-duh!) Raspberry Pi an Internet connection (don’t you say!) a microSD card (I recommend at least 32GB) and a USB power supply for the Raspberry Pi (I used a phone charger) Installing Raspberry Pi OS A Raspberry Pi is a mini-computer. So you need an operating system (OS) for it. There are different flavors of Linux that you can use, but we’ll use the optimized Raspberry Pi OS. The OS is installed on a microSD card that you later plug into your Raspberry Pi: You can use your working computer to download and install the Raspberry Pi OS on the microSD card. Most likely, you’ll need a microSD to USB adapter, or if your computer supports it, an SD adapter for microSD. Either way, connect the microSD card to your computer and you’ll be good to go: Download and install the on your computer. Start the tool and click on : Raspberry Pi Imager software CHOOSE OS Click on : Raspberry Pi OS (other) Select or the 32-bit version depending on your Raspberry Pi model. The Lite version of the OS doesn’t include any GUI which is a better option when installing a database server, or any server-software really. A GUI would use space in the storage device (the microSD card) and will probably run additional processes that are not truly needed and that would also consume RAM. So, go for the Lite ( ) version: Raspberry Pi OS Lite (64-bit) headless Click on the gear icon (settings), enter a hostname (I’m using ), and enable SSH with password authentication: raspi01 Scroll down and set a username (mine is ) and password of your preference. Also, enter the details of your WiFi connection: pi Click on and then on . Select the volume that corresponds to the plugged microSD card: SAVE CHOOSE STORAGE Double-check that you selected the correct media (you don’t want to format your hard drive, right?) and if everything looks okay, click on : YES You’ll be prompted to enter your password. Once the process is completed, click on : CONTINUE Extract the microSD card (now with the Debian-based Raspberry Pi OS fully-operational operating system in it) and plug it into your Raspberry Pi: Connecting Remotely Through SSH Time for the most fun part. Connect the USB-C power supply to the Raspberry Pi. This turns on the device and boots the OS. Enjoy the little red (power attached indicator) and green (a program is running) LEDs: So cool. I’m excited for you as I write this! Give the device some time to boot. Be patient. Depending on the Raspberry Pi model, this could take up to several minutes. In my case, when the green LED started to flash less or was completely off, I knew the mini-computer was ready and (hopefully) connected through WiFi to my local network. To start talking with this little machine, you can use , which attaches a terminal to the device. To do this, open a terminal or command prompt on your computer, and execute the following: SSH ssh pi@raspi01.local Use the username and hostname that you configured when you installed the OS. If everything is all right, you should be prompted to add the host to the list of known hosts. Type and enter your Raspberry Pi OS password. You are in now: yes Sweet! This is a good time to update your Raspberry Pi. Run the following: sudo apt update
sudo apt upgrade Installing the MariaDB Database Server Let’s make your new server more usable by installing MariaDB on it: sudo apt install mariadb-server It cannot get easier than that! A is included with the server installation, so you can connect to the database and check that it is working: SQL client sudo mariadb Try running a “Hello, World” kind of query there: Nice. Before we leave the client, create a new database (more on this later) and a user for it. It’s a good practice to not use the user when consuming the database from your applications. Here’s what you need to execute: root CREATE DATABASE nation;

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

GRANT ALL PRIVILEGES ON nation.* TO 'user'@'%'; Now you can leave the SQL client: quit We’ll connect remotely 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 easies this process: sudo mariadb-secure-installation You’ll be prompted with the root password which by default is empty, so just press enter. The tool asks you questions as you go. Feel free to configure your database server as you wish. For reference, here’s 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 working computer or even another Raspberry Pi, you’ll have to enable remote access. Edit the following file using or : nano vi sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf Comment out the following line by adding a hash ( ) character at the beginning as shown: # #bind-address = 127.0.0.1 That’s it. Save the changes, exit the text editor, and leave the little machine by ending the SSH session: exit Now you are back into your working 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 , your database is ready! Welcome to the MariaDB monitor Connecting to the Database Using DBeaver Most likely, you want to use a . In my opinion, the best free option is . If you haven’t, go ahead and download it and install it. graphical SQL database client DBeaver Open DBeaver and click the button: New Database Connection Select and click on : MariaDB Next > Enter the server host (in my case it is ), the username ( ), and the password. Click on to make sure all the connection details are correct: raspi01.local user Test Connection … You should see a message like this: Click , and . You should see the new remote connection in the : OK Finish Database Navigator Creating a Demo Database The website (props to the authors) contains a pretty nice demo database that you can use for experimenting. and extract the ZIP file that contains the SQL script. Select > in DBeaver and open the file. MariaDB Tutorial Download File Open File... nation.sql Click the button: Active datasource Select the Raspberry Pi connection and click on : Select Click the button: Execute SQL Script Now you have a populated demo database that you can play with! In the , browse the tables and double-click on one of them. For example, explore the tab after opening the table: Database Navigator Data countries Isn’t it cool to see all that data coming from the little computer? Running SQL Queries The whole point of having a database is to be able to execute custom SQL queries. In the , right-click on and select > . Try for example querying the languages spoken in your country. Here’s my result: Database Navigator nation SQL Editor New SQL script There are many interesting queries you can run, but I’ll let you figure out those. For reference here’s an of the database that I took from the MariaDB Tutorial website: ER diagram What’s Next? You might want to try next (get it from ). Or Maybe use one of the and create your own applications using , , , or . ColumnStore here MariaDB connectors Java JavaScript/Node.js Python C++ Ah! One final thing. If you want to safely shut down your Raspberry Pi, SSH into it and execute the following: sudo shutdown now Happy database querying and coding! Also published . here

Naked

Customizing MariaDB Docker Images

9 Useful Interactive CLI Tools for Linux

Visit my personal Blog!

Nominated for 2022 - HackerNoon Contributor of the Year - Database

Nominated for 2022 - HackerNoon Contributor of the Year - Git

Nominated for 2022 - HackerNoon Contributor of the Year - Java

Nominated for 2022 - HackerNoon Contributor of the Year - Docker

Nominated for 2022 - HackerNoon Contributor of the Year - Sql

Nominated for 2022 - HackerNoon Contributor of the Year - Analytics

Too Long; Didn't Read

Setting Up a Dedicated Database Server on Raspberry Pi

Setting Up a Dedicated Database Server on Raspberry Pi

About Author

Comments

TOPICS

THIS ARTICLE WAS FEATURED IN

Related Stories

9 Useful Interactive CLI Tools for Linux

10 VSCode Extensions To Make Your Life Easier

10 Ways Stand Out as a Java Developer and Land that Dream Job

10 Tips For Junior Developers To Succeed in Code

10 Things Every Beginner Should Know When Learning Javascript And React

10 Reasons Why Front End Development Is a Great Career Choice

9 Useful Interactive CLI Tools for Linux

10 VSCode Extensions To Make Your Life Easier

10 Ways Stand Out as a Java Developer and Land that Dream Job

10 Tips For Junior Developers To Succeed in Code

10 Things Every Beginner Should Know When Learning Javascript And React

10 Reasons Why Front End Development Is a Great Career Choice

Light-Mode

Classic

Newspaper

Dark-Mode

Neon Noir

Minty

HN StartUps