MySQL is a relational database management system widely used in web applications, servers, and development projects.
On Raspberry Pi it lets us set up a local database to learn SQL, test applications, or store data from our projects.
MySQL is developed by Oracle and has a community edition and commercial editions. On Debian-based distributions, it is also common to find MariaDB, a compatible fork that is usually the option available in repositories.
In Raspberry Pi OS and other Debian-based distributions, MariaDB is usually the most common option from repositories. It is compatible with MySQL for most uses and, for Raspberry Pi projects, it is normally the alternative you will encounter first.
Having a database is essential in many development projects. MySQL/MariaDB is one of the most widely used options in hobbyist and maker projects because of its popularity, documentation, and ease of installation.
Install MySQL
Installing MySQL/MariaDB on Raspberry Pi is very simple. First, we make sure we have the package list updated.
sudo apt update
Next, we run the installation with the following command,
sudo apt install mysql-server
That is it, we have installed the database server. The installation may vary slightly depending on the Raspberry Pi OS version and whether the package resolves to MySQL or MariaDB, but the general idea is the same.
We fix this by completing the installation with the following script included in the installation.
sudo mysql_secure_installation
This will ask us a series of questions such as username and password.
To verify everything is working correctly, we run the command
systemctl status mysql.service
Testing MySQL
Let’s see a mini tutorial of some commands we can use from the MySQL CLI. Although normally we would 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 perform 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';
Replace ‘my_user’ with the desired username 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. To do this, we execute the following Query, where ‘my_table’ is the name of our table.
CREATE TABLE my_table
(int INT,
number DOUBLE,
date DATETIME,
text NVARCHAR(32));
That is how easy it is to install and use MySQL/MariaDB on Raspberry Pi. From here on we can use it from our applications, or connect with a graphical client to manage the databases more comfortably.
