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.
In the next post we will see MariaDB, a database derived from MySQL and compatible with it, 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 home and Maker projects, due to its licensing and popularity.
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.
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
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';
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
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.