How to install MySQL on Raspberry Pi

In this post we are going to see how to install a MySQL database on a Debian-based distribution like Raspbian on Raspberry Pi.

MySQL is an open source database management system, considered the most widely used open source database in the world

It has a GNU GPL license, which has the condition that any project developed with it has to be distributed under the same license. Otherwise, a commercial license from Oracle Corporation can be purchased.

Having a database is essential in almost any development project. MySQL is one of the most used options in home and Maker projects, due to its licensing and popularity.

Install MySQL

Installing MySQL on Raspberry Pi is very simple. First, we make sure we have the list of packages updated.

sudo apt update

Next, we run the installation with the following command,

sudo apt install mysql-server

That’s it, we have installed MySQL. The installation does not ask for user data or password, being insecure as it is.

We solve this by finishing the installation with the following script that is included in the installation.

sudo mysql_secure_installation

Which will ask us a series of questions such as the username and password.

To verify that everything is working correctly, we execute the command

systemctl status mysql.service

Testing MySQL

Let’s see a mini tutorial of some of the commands that we can use from the MySQL CLI. Although it is normal to use one of the many available SQL clients to perform these actions.

To access the MySQL CLI we use the following command.

sudo mysql -u root -p -h localhost

Create a new user

One of the first actions we should take in a database is to create and properly manage user permissions. To create a new user we execute the following command.

CREATE USER 'my_user'@'localhost' IDENTIFIED BY 'my_password';

Replacing ‘my_user’ with the username you want and ‘my_password’ with the password.

Create a new database

To create a new database we use the command, replacing ‘my_db’ with the name of the database you want.

CREATE DATABASE my_db;

Next, we grant permission to the user we created earlier by executing,

GRANT ALL PRIVILEGES ON my_db.* TO 'my_user'@'localhost';
FLUSH PRIVILEGES;

Finally, we restart MySQL to apply the changes.

sudo service mysql restart

Create a table

The next common task is to create one or more tables. Let’s create an example table with fields of different types to illustrate it. To do this, we execute the following Query, with ‘my_table’ being the name of our table.

CREATE TABLE my_table
   (int INT,
    number DOUBLE,
    date DATETIME, 
    text NVARCHAR(32));

That’s how easy it is to install and use MySQL on Raspberry Pi. In the next post we will see how to install MariaDB, an alternative compatible with MySQL, which we will use in our projects.