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
sshfile. 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):
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.
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:
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:
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
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!