MySQL Server on Raspberry Pi

In this post we will cover all the needed steps to install and configure MySQL Server on Raspberry Pi.

Before starting you will need a Raspberry Pi with it’s OS installed. If you don’t have it, you can follow this post where I cover a full installation of Ubuntu Server 20 for Raspberry Pi.

Requirements

Here is a list of the things you will need to follow this guide:

• Raspberry Pi.
• Ubuntu 20 installed.
• SSH or direct connection to the machine.

Just remember that, if you want, you can aproach this by using a PC or VM with Ubuntu 20, which is the essential part.

Download and Install MySQL Server

Downloading MySQL Server is quite simple, just type:

sudo apt install mysql-server

Just wait til it finishes.

Now, let’s check if it’s up and running:

sudo service mysql status

We sould get an output like this, telling us is active (running):

● mysql.service - MySQL Community Server
      Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
      Active: active (running) since Fri 2021-04-16 17:44:13 UTC; 1 day 2h ago
    Main PID: 11319 (mysqld)
      Status: "Server is operational"
       Tasks: 42 (limit: 9257)
      CGroup: /system.slice/mysql.service
              └─11319 /usr/sbin/mysqld

Creating a new Database and User

Now, lets’ make our own database and user, to be able to acces. First of all, logging into Mysql:

sudo mysql -u root

To create a user that’s only allowed to acces from one IP, use the following command, replacing localhost with other IP if you want, if not, only local access is allowed:

CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'password';

I alwais will recommend to bind a user to an IP if it’s possible, but if yo uwant to allow a user access from every IP, we can use the following command:

CREATE USER 'myuser' IDENTIDFIED BY 'password';

Creating a new database is as follows:

CREATE DATABASE mydatabase;

The following SQL will add privileges over the new database to the new user. Remember to user @’ip/localhost’ after the user name if you created the user IP limmited:

GRANT ALL PRIVILEGES ON mydatabase TO 'myuser';
FLUSH PRIVILEGES;

Configure MySQL Server to remote access

By default, MySQL Server only allows connections from localhost, so our new user, if created the non IP dependant way, isn’t able to connect. We can change it by editing this file:

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

In this file, we must find the following line, and change the IP value on there to our preference. We can use 0.0.0.0 if we want to allow access to any IP, but if you want only one (avoid undesirable IPs to connect), just type it there. If only localhost access is going to be done in here, don’t change it:

# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address            = 127.0.0.1

Now, it’s only about restarting MySQL Server:

sudo service mysql restart

Use the DB connection software of your preference, and connect to your new database!