--- title: "Building a Portable Database Server" publish_date: 2022-09-20 updated_date: 2023-05-02 author: "Alejandro Duarte" channel: - name: "Developer" url: "/resources/blog/channel/developer.md" tags: - name: "Docker" url: "/resources/blog/tag/docker.md" --- # Building a Portable Database Server If you are a Software Developer, DevOps Engineer, DBA, or Systems Administrator, you need a [Raspberry Pi](https://en.wikipedia.org/wiki/Raspberry_Pi) or an equivalent single-board mini-computer. Modern distributed cloud applications run on infrastructure that requires work on networking, security, provisioning, and monitoring. These are valuable skills that you can exercise by using affordable devices that emulate the real world more closely than virtual machines or Docker containers do. Here’s how [Alex Ellis](https://blog.alexellis.io/self-hosting-kubernetes-on-your-raspberry-pi) sums it up: *«Let me put this succinctly – the Raspberry Pi is a Linux server. If you write code, or “do DevOps” for a day-job, you have a use-case. If you use a computer on a daily basis, you have a use-case.»* Having a [Raspberry Pi](https://www.raspberrypi.com/products/raspberry-pi-zero-2-w/) is almost like having an Amazon [t2.nano](https://instances.vantage.sh/aws/ec2/t2.nano) EC2 instance. Just for fun here’s a comparison: | | Amazon t2.nano EC2 instance | Raspberry Pi Zero 2 W | |---|---|---| | Memory | 536 MB | 512 MB | | CPUs | 1 vCPU | 4 cores | | Clock speed | 3 GHz | 1 GHz | | CPU Architecture | x86 | ARM | This article covers a specific use case—configuring a portable database server that you can connect via WiFi or through USB Ethernet emulation. It covers the steps to set up a SQL database on a [Raspberry Pi Zero W 2](https://www.raspberrypi.com/products/raspberry-pi-zero-2-w/)—one of the smallest single-board computers available—but you can adapt the instructions to other models and brands like [Raspberry Pi 4](https://www.raspberrypi.com/products/raspberry-pi-4-model-b/), [Banana Pi BPI-M2 Zero](https://banana-pi.org/en/banana-pi-sbcs/1.html), or [Radxa Zero](https://wiki.radxa.com/Zero). During my experiments, I found that SQLite, TinyDB, and MariaDB are the best databases for Raspberry Pi devices and, in general, for [IoT](https://en.wikipedia.org/wiki/Internet_of_things) applications. They are compatible with Raspberry Pi hardware and don’t consume a lot of resources. They also support a variety of programming languages and tools. In this article, you’ll learn how to install the popular open-source [MariaDB database server](https://staging-mdb.com/products/community-server/) and how to connect to it remotely from your computer using [DBeaver](https://dbeaver.io/). Here is a list of items needed to turn your Raspberry Pi into a database server: - Wi-Fi network - MicroSD card - Raspberry Pi - USB Cable - Power supply ![MariaDB running on a Raspberry Pi Zero 2 W](https://staging-mdb.com/wp-content/uploads/2022/09/raspberry-pi-blog-img1.png) *MariaDB running on a Raspberry Pi Zero 2 W* ### Setting up Raspberry Pi You have to install an operating system on the microSD card. Try to get a fast card as this will make a huge impact on the performance of the database in particular and the device in general. 1\. On your working laptop or computer (as opposed to the Raspberry Pi), install the [Raspberry Pi Imager](https://www.raspberrypi.com/software/) software and install Raspberry Pi OS Lite (64-bit): ![Operating system selection](https://staging-mdb.com/wp-content/uploads/2022/09/raspberry-pi-blog-img2.png) *Operating system selection* 2\. Choose your storage device. Make sure to select the correct one (the microSD card). 3\. Click on the gear icon on the bottom right corner and configure the following: ![Gear icon](https://staging-mdb.com/wp-content/uploads/2022/09/raspberry-pi-blog-img3.png) - **Hostname**: You will use this to connect to the Pi through SSH. I used `zero01` but feel free to use any username you like. - **Enable SSH**: Make sure to check this. If you do not want to deal with SSH keys, select **Use password authentication** (this is what I did). - **Set username and password**: Specify a Linux username and password. You may use any combination. In this article, I used `user/password` but you should use something more secure. - **Configure wireless LAN**: Check this option and configure the connection to your WiFi network. Keep in mind that you’ll have to use a 2.4G network. 4\. Save the settings and click on **WRITE**. This will write a bootable Raspberry Pi OS on the card. ![Write option starts the installation process](https://staging-mdb.com/wp-content/uploads/2022/09/raspberry-pi-blog-img4.png) *Write option starts the installation process* The software asks if you are willing to continue and erase all the existing data on the device. Make sure you have backed up your files and then click on, **YES**. When the process finishes, plug the microSD card into the Pi, grab a micro USB cable, and connect it to a power supply. I used an iPhone charger. Use the right-most micro USB port on the Pi. The mini-computer should boot now. Be patient. The first boot can take some time. Even minutes. But subsequent boots will be faster. 5\. On your working computer open a terminal window and connect to the Pi via SSH. Use the username, hostname, and password that you configured during the installation process. Make sure your working computer is connected to the same LAN you configured for Raspberry Pi: `ssh user@zero01.local` You’ll be asked to add the Pi’s key to the list of known hosts. Answer “yes” and introduce the password that you configured. You are in! 6\. Before anything else, update your system: `sudo apt update` `sudo apt upgrade -y` **TIP**: If you don’t want to type `sudo` all the time, use `sudo su` before running the commands. Just remember to type `exit` after you are done. Be careful as you’ll gain *sudo* privileges. ### Installing Docker Time to install some useful software. A database. A MariaDB database. MariaDB is an open-source relational database that uses pluggable, purpose-built [storage engines](https://staging-mdb.com/docs/storage-engines/) to support workloads that previously required a variety of specialized databases. You can combine these storage engines in the same server and run cross-engine SQL queries (queries that join tables on different storage engines). You can install MariaDB directly on top of your operating system, but I recommend using [Docker](https://www.docker.com/). The run-time performance cost of running Docker containers is negligible. Take a look at [this IBM research paper](https://course.ece.cmu.edu/~ece845/sp18/docs/containers.pdf) that compares the performance of bare metal, KVM, and Docker containers. The conclusion: *«In general, Docker equals or exceeds KVM performance in every case we tested. Our results show that both KVM and Docker introduce negligible overhead for CPU and memory performance (except in extreme cases).»* Installing Docker is pretty simple, run the following commands on your Raspberry Pi: `sudo apt install docker.io -y` Check that Docker is running correctly: `sudo service docker status` You should see **active (running)** reported by the service: ![Docker service up and running](https://staging-mdb.com/wp-content/uploads/2022/09/raspberry-pi-blog-img5.png) *Docker service up and running* Press **Q** to quit the status reporting program. If the service reports **failed**—which actually happened to me during my experiments—try rebooting the Pi: `sudo reboot` ### Running a MariaDB database server on Docker To spin up a MariaDB instance run the following command (go ahead and try it, you can cleanly [remove](https://docs.docker.com/engine/reference/commandline/rm/) it later if you want): ``` sudo docker run --detach --net=host --name mariadb --restart unless-stopped --env MARIADB_ROOT_PASSWORD='password' mariadb:latest ``` We are passing several flags: - `--detach` runs the container in the background - `--net=host` makes the container use the host’s network (the container won’t get its own IP address which removes the need to explicitly map and expose ports) - `--name` gives the container a name that you can use later to operate the container - `--restart unless-stopped` makes Docker start the container when the Pi starts - `--env` is used to configure variables such as the root user password and other things - `mariadb:latest` refers to the [official MariaDB Docker image](https://hub.docker.com/_/mariadb) **TIP**: If you installed a 32-bit OS you can use the MariaDB Docker [image](https://hub.docker.com/r/linuxserver/mariadb) provided by the [linuxServer.io](https://www.linuxserver.io/) team. This image includes support for **linux/arm/v7** architectures. Just replace `mariadb:latest` with `linuxserver/mariadb:latest`. Check that the container is up and running: `sudo docker container ls` ![MariaDB server running on a Docker container](https://staging-mdb.com/wp-content/uploads/2022/09/raspberry-pi-blog-img6.png) *MariaDB server running on a Docker container* Connect to the database from within the container (we’ll see how to connect from outside later): `sudo docker exec -it mariadb bash` `mariadb -u root -p` This runs a new bash session inside the container from which you can execute Linux commands that run on the container. Once inside the container, we use the CLI `mariadb` client to connect to the database. For fun, try the following commands that show interesting facts and configurations about your new MariaDB database: `SHOW CONTRIBUTORS;` `SHOW PLUGINS;` `SHOW ENGINES;` Also try a “Hello, World” SQL query: ![Locally running SQL queries](https://staging-mdb.com/wp-content/uploads/2022/09/raspberry-pi-blog-img7.png) *Locally running SQL queries* Once you are done, quit the SQL client and exit the container: `quit` `exit` That was fun, but let’s try a different configuration. Stop and remove the `mariadb` container and its associated [volume](https://docs.docker.com/storage/volumes/): `sudo docker stop mariadb` `sudo docker container rm --volumes mariadb` Run a new container as follows: ``` sudo docker run --detach --net=host --name mariadb --restart unless-stopped --env MARIADB_ROOT_PASSWORD='password' --env MARIADB_ROOT_HOST='localhost' --env MARIADB_DATABASE='nation' --env MARIADB_USER='user' --env MARIADB_PASSWORD='password' mariadb:latest ``` This creates and runs a Docker container with a MariaDB database that: - has a root user that can connect only from `localhost` - has a database with the name `nation` (we’ll populate this database later) - has a user with access to the `nation` database This is a more secure database, except for the fact that it uses `password` as the password for both the root and `user` users (don’t do this in production!). ### Connecting to the database from a laptop using DBeaver Let’s connect to this database from your working laptop or computer using [DBeaver](https://dbeaver.io/). 1\. Install the software on your computer if you haven’t already and create a new connection to the containerized MariaDB database by selecting **Database > New Database Connection** in the menu. Select the **MariaDB** database option and fill in the connection details: ![Database connection configuration](https://staging-mdb.com/wp-content/uploads/2022/09/raspberry-pi-blog-img8.png) *Database connection configuration* 2\. Test the connection and then click on the **Finish** button. You are now connected to a portable database from your laptop! 3\. Try importing the database from the [https://www.mariadbtutorial.com](https://www.mariadbtutorial.com/) website. Here’s a [direct link](https://www.mariadbtutorial.com/wp-content/uploads/2019/10/nation.zip) to ZIP file that contains the script. Extract this file and open the SQL file in DBeaver. Select a data source by clicking on the little database icon in the toolbar and select the connection you just created: ![Selecting a database connection for SQL query execution](https://staging-mdb.com/wp-content/uploads/2022/09/raspberry-pi-blog-img9.png) *Selecting a database connection for SQL query execution* 4\. Select **SQL Editor > Execute SQL Script** in the menu. Once the script is completed, select **SQL Editor > New SQL Script** and run the following query (adapt it to show the languages in your country or any country you are interested in): ``` SELECT l.language FROM countries c JOIN country_languages cl ON cl.country_id = c.country_id JOIN languages l ON l.language_id = cl.language_id WHERE c.name = "Colombia"; ``` Run the query. I’ll let you figure out this by yourself (hint: use the vertical toolbar). Here’s an example result: ![SQL query results](https://staging-mdb.com/wp-content/uploads/2022/09/raspberry-pi-blog-img10.png) *SQL query results* Here are some questions that you can answer using SQL queries on this database: - What’s the country with the most number of languages? - How many countries does each continent have? - What’s the World population? ### Enabling USB Gadget Ethernet USB Gadget Ethernet allows you to connect your Raspberry Pi via USB to your computer and make it look as if it was connected to the network. This is useful when you want to connect to a different network and don’t want to hassle configuring the new network on the Pi. If you haven’t, SSH into the Pi: `ssh user@zero01.local` Edit the following file: `sudo nano /boot/config.txt` At the very end of the file, add the following line: `dtoverlay=dwc2` Save the file and exit the editor. Now edit the following file: `sudo nano /boot/cmdline.txt` After `rootwait` add the modules to load as shown here: `... rootwait modules-load=dwc2,g_ether` Save the file and exit the editor. Now power off the Pi: `sudo poweroff` Wait until the green LED turns off and disconnect the Pi from the power supply. Connect the micro USB end of the cable to the **second right-most port** on the Pi and the other end to your computer. Wait until the Pi boots and connect to it via SSH. Congrats! Now you have a truly portable MariaDB database server that you can carry around and use everywhere. Consider getting a nice case for the Pi. I can recommend [this one](https://flirc.tv/products/flirc-raspberrypizero) by Flirc: ![A portable MariaDB database server running on Raspberry Pi](https://staging-mdb.com/wp-content/uploads/2022/09/raspberry-pi-blog-img11.png) *A portable MariaDB database server running on Raspberry Pi* ### What’s next? Do you have another Pi laying around somewhere? They are scarce nowadays, give them some use! For example, try setting up [basic replication](https://www.youtube.com/watch?v=QKUtvt3_y6Y) or building something like this [32-core Raspberry Pi 4 cluster](https://dzone.com/articles/building-a-24-core-raspberry-pi-cluster-from-scrat). Then try consuming the database from [Java](https://staging-mdb.com/developers/pages/quickstart/tutorials/java), [JavaScript/Node.js](https://staging-mdb.com/developers/pages/quickstart/tutorials/javascript), [Python](https://staging-mdb.com/developers/pages/quickstart/tutorials/python), and [C++](https://staging-mdb.com/developers/pages/quickstart/tutorials/cpp) applications using the official [MariaDB connectors](https://staging-mdb.com/docs/connect/programming-languages/).