Installing MariaDB or Mysql on a Rapsberry Pi

In this guide we will install MariaDB or Mysql. Secure the installation and create our first database and user. Finally we’ll go through how to tweak the installation.

Installing MariaDB

Personally I strongly prefer MariaDB over MySQL. The performance is better then MySQL and it’s more open source. There are a few differences but nothing you will notice during normal daily use. Here is a guide If you’re interested in detailed differences between MariaDB and Mysql.

Let’s start by installing MariDB with the following command.

sudo apt install -y mariadb-server-10.3

Alternative, install mysql

If you don’t want to get out of your comfort zone use mysql. Keep in mind the rest of the setup steps might vary a little bit. Nothing you can’t figure out yourself 😉.

sudo apt install -y mysql-server

After it’s done check the version with mysql --version. It should be ‘mysql Ver 15.1 Distrib 10.3.25-MariaDB‘ or higher.

Securing the installation

The biggest thing we need is a root password. We will do that and some other things.

sudo mysql_secure_installation

This will give the following steps.

  1. Set a long safe password.
  2. Remove anonymous users Yes
  3. Disallow root login remotely Yes
  4. Remove test database and access to it Yes
  5. Reload privilege tables now Yes

Creating a database and user

To create a database we are going to login to MySQL with the following command.
Because we use sudo and root we will not need to enter a password.

sudo mysql -uroot

We are going to create a database and a user and connect them.

CREATE DATABASE raspimain_db;

Next we create a user, be sure to replace the password!

CREATE USER 'raspimain_user'@'localhost' IDENTIFIED BY '%%SAFE_PASSWORD%%';

Then we need to connect the user to that database.

GRANT ALL PRIVILEGES ON `raspimain_db`.* TO `raspimain_user`@`localhost`;

Next we 2 commands are pretty self explanatory.

FLUSH PRIVILEGES;
EXIT;

To test if it worked login in to mysql with that user.

mysql -u raspimain_user raspimain_db -p

This time use the password you used to create the user. Check if the raspimain_db is in the list of databases.

SHOW DATABASES;

If it is use EXIT; to exit the mysql promt.

Tweak a few settings

If you want to do custom changes I recommend doing them in a separate config file. Currently the only tweak I would want is to increase the max_allowed_packet. This allows bigger packets to be send to mysql. Some plugins recommend it.

So we open our new custom config: sudo nano /etc/mysql/mariadb.conf.d/99-custom-open-ip.cnf

And add the setting:

[mysqld]
max_allowed_packet=64M

After saving we reload mysql: sudo service mysql restart

And check the value with this command:
sudo mysql -uroot -e "SHOW VARIABLES LIKE 'max_allowed_packet';"

The output should look something like this:

+--------------------+----------+
| Variable_name      | Value    |
+--------------------+----------+
| max_allowed_packet | 67108864 |
+--------------------+----------+

Other settings can be added in the same way.

Open database to the outside

I separate my pi’s One runs php+nginx and the other runs Mysql. That does require you to allow connection to the mysql from the other Pi.
This requires a few extra steps.

When creating a user also add this:

CREATE USER 'raspimain_user'@'192.168.2.%' IDENTIFIED BY '%%SAFE_PASSWORD%%';
GRANT ALL PRIVILEGES ON `raspimain_db`.* TO `raspimain_user`@`192.168.2.%`;
FLUSH PRIVILEGES;

So you create a “second user” with the same name and password but allow access from the IP 192.168.2.*

Globally you still need to adjust one setting. In the /etc/mysql/mariadb.conf.d/99-custom-open-ip.cnf file add:

[mysqld]
skip-networking=0
skip-bind-address

And restart with sudo service mysql restart
For more details check the mariaDB page.