Installing Mysql/MariaDB on a Rapsberry Pi

Here we will install MariaDB. The performance is better and it’s more open source. There are a few differences but nothing you will notice during normal daily use.

Installing mariadb:

sudo apt install -y mariadb-server-10.1

Alternative if you don’t want to get out of your comfort zone use mysql.
Keep in mind the rest of these steps might vary a bit.

sudo apt install -y mysql-server

After it’s done check the version with mysql --version. It should be 10.1.23-MariaDB or higher.

Securing the installation

The biggest thing e 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.