Installing a MySQL/MariaDB Server on Raspberry Pi

MySQL is the most popular open-source relational database management system. It is a good, proven solution for storing data and works well in both simple and more advanced applications. Its most important characteristic is that it prioritizes speed over compatibility with the SQL standard, which means MySQL can be somewhat limited in terms of functionality. For example, features such as stored procedures, triggers, views, and cursors were only added in version five—features that had already been standard in databases such as MsSQL or PostgreSQL for a long time. Today, however, it is not far behind competing solutions, while remaining simpler and more compatible. On this blog, I will try to cover topics related to this database because its modest platform requirements make it possible to run it on Raspberry. In this post, I will describe how to install the MariaDB fork and show an example of creating a database.

Installation

The installation is straightforward. First, as always, update the package list.

sudo apt-get update

Then install the required package.

sudo apt install mariadb-server

Configuration

Next, you should update the security settings. By default, the server can be accessed without providing any password. This should be changed, as leaving access unsecured is definitely bad practice. To do this, run the following command:

sudo mysql_secure_installation

Follow the installer's prompts. If you do not understand a question, press enter to choose the recommended value. The main purpose of these questions is to prevent unauthorized access to the server. One of the prompts will ask you to set a password for the root user. Make sure you remember it (or write it down), because we will need it later.

Let's check whether the configuration was successful. To do this, we will open the MySQL server command line. Enter the following command in the console and confirm it with the password you set earlier (the password will not be visible while you type it).

sudo mysql -u root -p

If everything worked correctly, you will see the MySQL server console like the one shown in the image below.

To leave the console, type "quit;" or "exit", or simply press CTRL+D.

Adding a database and user

Now that everything is configured, let's add a new database and user. First, open the server console just as we did in the previous step.

sudo mysql -u root -p

Now let's create a new database named "CleverBlogDatabase".

CREATE DATABASE CleverBlogDatabase;

Now let's add a new user named "CleverUser" with the password "CleverPassword".

CREATE USER 'CleverUser'@'localhost' IDENTIFIED BY 'CleverPassword';

The next step is to grant the user permissions to the database. This is necessary so the user can make changes to it, modify data, manage tables, and so on.

GRANT ALL PRIVILEGES ON CleverBlogDatabase.* TO 'CleverUser'@'localhost';

The final step is to save the changes using the "FLUSH" command. Without this operation, the newly created user will not receive the database permissions we granted.

FLUSH PRIVILEGES;

Remote access

To access the database from another computer on the network, you need to make configuration changes described in this article on the blog. For browsing databases, I recommend "MySQL Workbench"—it is available free of charge from the manufacturer's website.

Another option is to integrate phpMyAdmin, which lets you manage a MySQL database server through a web interface. It requires PHP to be installed on Raspberry. I may describe this on the blog at some point. For now, dear reader, you will need to look for other tutorials online.

Integration

To use the database with specific programming languages, you will need extensions that provide integration with them.

To integrate it with PHP, install

sudo apt install php-mysql

To integrate it with Python version 2, install

sudo pip install mysql-connector-python

To integrate it with Python version 3, install

sudo pip3 install mysql-connector-python

To integrate it with C#, you do not need to install anything in the operating system, but you do need to add the appropriate provider to your project. For ADO.NET, install the "MySql.Data" NuGet package, and for Entity Framework Core, install "Pomelo.EntityFrameworkCore.MySql".