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.