Complete installation Database MySQL & MariaDB in Raspberry Pi 3 B / B +

Among the most popular databases this MySQL has 2 types of licensing General public license and commercial license, it is considered the most popular open source database in the world is included in almost all hosting very used by WordPress, is also included as a component of development in LAMP and XAMMP.

Previously, make a tutorial to install the Grafana Dashboard in a Raspberry Pi, this time we will install MySQL in Raspberry Pi to integrate both in a functional application:

Recommended Tutorial: Grafana Installation in Raspberry Pi

Complete installation Grafana Dashboard in Raspberry Pi 3 B / B +





MySQL

It has been a great surprise to know that MySQL has a database management system called MariaDB. Continuing our tests with the Grafana Dashboard we require a robust database and we have decided to implement MySQL which we will install in our Raspberry Pi B,

More Information: MySQL Wikipedia


MariaDB

Since Oracle is the owner of MySQL, the MariaDB foundation was born and a large community of free software developers, MariaDB is a bifurcation, a variation of the original source code.

More information: MariaDB Wikipedia

Materials and where to buy them





Recommendations

 Raspbian Stretch Lite

To have the best performance of our Raspberry Pi, I recommend installing RASPBIAN STRETCH LITE, this version is the image has the minimum of pre-installed applications, no desktop or graphical interface, all configuration is done through command line, I recommend microSD 8GB .

SSH Client and Raspbian Installation

Since there are hundreds of tutorials on how to mount raspbian on the Internet, I will omit the step and place that tutorial hackernoon.com site to which I thank for the tutorial, it indicates the process of mounting the Operating System and additional mount the SSH client.

Note: The raspberry pi 3 B and / or B + has Wifi included.





MySQL Installation & configuration

Actually the method of installation of MySQL has varied, one of the biggest inconveniences of the configuration of the user “root” or administrator.

Perform many tests to create a password for this user that by default MySQL does not require passwords, but to connect MySQL with other applications such as Node-RED, Python and Grafana authentication is required by security issues.

Next we will install MySQL and perform the respective configuration creation of users and databases.

Update list of available packages

sudo apt update 

Update packages

sudo apt upgrade -y

Remove obsolete packages

sudo apt autoremove -y

Install MySQL database server

sudo apt-get install mysql-server -y

Verify status MySQL (MariaDB), it must be running

sudo /etc/init.d/mysql status




Create Database and new User

Login to MariaDB user from “root” user

press “Enter”, no password is required for the “root” user, we enter MariaDB to enter Query.

sudo mysql -u root -p -h localhost

Create new database “grafana_db”.

CREATE DATABASE grafana_db;

Use database “grafana_db”

USE grafana_db;

Create a new user “grafana_user” and assign the password “password”

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

Assign all the permissions to the user “grafana_user” on the database “grafana_db”.

GRANT ALL PRIVILEGES ON grafana_db.* TO 'grafana_user'@'localhost';
FLUSH PRIVILEGES;

exit MariaDB (MySQL)

quit

Reset mysql

sudo service mysql restart

Create table

I will advance the process a bit, for my future tests I will create a simple table to send data from an ESP32 + Node-RED, take this table as an example, adapt to your needs

This table consists of 4 columns, the table “esp32_dht11”

  1. dateandtime (TYPE Datetime or Time Format)
  2. sensor (TYPE Varchar or string of 32 characters)
  3. temperature (TYPE Double or floating with decimals)
  4. humidity (TYPE Double or floating with decimals)
CREATE TABLE esp32_dht11 (dateandtime DATETIME, sensor VARCHAR(32), temperature DOUBLE, humidity DOUBLE);

Note: this is a brief introduction, perhaps later explain a little in detail, but I recommend looking for tutorials on the internet about Query in MySQL, there should be thousands of tutorials.

Recommendation: For a more detailed explanation step by step I recommend watching the full video I  Installation Dashboard Grafana in Raspberry Pi 3 B or B+:PDAControl available on our  Youtube channel PDAControl.





MySQL and Grafana connection test

With a user “grafana_user” and a password “password” and the database “grafana_db” without any problem we can connect to Grafana, to graph grafana we obviously need data, but with this tutorial we should be able to connect our database “grafana_db”.

Note: Grafana and MySQL are both installed on the same Raspberry Pi.

More info MySQL+Grafana: docs.grafana.org/features/datasources/mysql/

Video Complete Installation





Conclusions

My first experiences with databases were SQLServer and MySQL (MariaDB) relational databases, there are other possibilities like MongoDB and InfluxDB Non-SQL databases.

As you have seen I like efficient systems and I try to use interfaces in the minimum, I prefer command line, in the case of Raspberry Pi it must be very recursive given the limited resources of RAM and storage space.

That’s why we will not install things like Apache php or phpmyadmin, I will show that it can only be with command line, we will also use Node-RED.

For my integration with Grafana I have initially selected MySQL, because I have been using it for a long time, there are other possibilities, I tried a bit of InfluxDB a few years ago, but I see that it is very focused on time series and I need to do other things that I do not know yet in that database.

If there are advantages and disadvantages between relational and non-SQL databases, we will not perform tests on other databases.

References




Leave a Reply