In this post, we will 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 is licensed under GNU GPL, which has the condition that any project developed with it must be distributed under the same license. Otherwise, a commercial license from Oracle Corporation can be purchased.
In the next post, we will see MariaDB, a database derived from and compatible with MySQL, which should be your first choice as a database.
Having a database is essential in almost any development project. MySQL is one of the most used options in hobbyist 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 package list 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, making it insecure as it is.
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’s how easy it is to install and use MySQL on Raspberry Pi. In the next post, we will see how to install MariaDB, a MySQL-compatible alternative, which we will use in our projects.
