Uso de MariaDB en el VPS del Máster de Industria 4.0 el curso 2023-2024

Acceso al terminal del VPS

• Para acceder al terminal remoto del VPS desde Windows, abrir el teminal (cmd) o el powershell y escribir:

ssh convidat@vps656540.ovh.net

La contraseña es benvingut

• Para acceder al terminal remoto del VPS desde Linux, abrir el teminal escribir:

ssh convidat@vps656540.ovh.net

y en caso de querer enviar cada 30 segundos un blank token para evitar la desconexión por time out:

ssh -o ServerAliveInterval=30 convidat@vps656540.ovh.net

Información sobre la instalación de la base de datos MariaDB

Este apartado es puramente informativo. En el VPS ya está instalada la base de datos.

Un tutorial de instalación de MariaDB en Debian 11

Pasos de la instalación desde el terminal:

sudo apt update
sudo apt install mariadb-server
sudo mysql_secure_installation (en este paso el sistema pregunta una contraseña para el usuario root)

Un tutorial de creación de usuarios con diferentes permisos (privilegios)

Desde el terminal del sistema operativo se pide entrar al terminal de MariaDB. La primera vez como usuario root:

mysql -u root -p
(el sistema pide la contraseña del usuario root)

Una vez dentro del terminal de MariaDB se crean los usuarios iot y convidat con sus respectivas contraseñas. Se crea la base de datos aula206, se otorgan todos los permisos al usuario iot y tan solo la lectura al usuario convidat:

CREATE USER 'iot'@'localhost' IDENTIFIED BY 'iot';
CREATE USER 'convidat'@'localhost' IDENTIFIED BY 'benvingut';

CREATE DATABASE aula206;
SHOW DATABASES;
USE aula206;

GRANT ALL PRIVILEGES ON aula206.* TO 'iot'@'localhost';
GRANT SELECT ON aula206.* TO 'convidat'@'localhost';
FLUSH PRIVILEGES;

Acceso a la base de datos desde el terminal del VPS

Para acceder a la base de datos hay tres usuarios con permisos diferentes:

- root: Tiene todos los permisos. Puede crear usuarios y bases de datos.

- iot: Tiene permisos de lectura y escritura sobre la base de datos aula206.

- convidat: Tiene permisos de lectura sobre la base de datos aula206.

Para acceder al terminal de MariaDB como usuario iot:

mysql -u iot -p
(el sistema pregunta por la contraseña de este usuario. Es la que sale en el apartado anterior después de IDENTIFIED BY)

Para acceder al terminal de MariaDB como usuario convidat:

mysql -u convidat -p
(el sistema pregunta por la contraseña de este usuario. Es la que sale en el apartado anterior después de IDENTIFIED BY)

Creación de tablas

Para crear tablas en la base de datos hace falta que el usuario tenga permisos de escritura a la base de datos (iot tiene permisos de escritura en la base de datos aula206. convidat no tiene permisos de escritura en la base de datos aula206).

Vamos a compartir la misma base de datos (aula206), los datos que vamos a introducir los diferenciaremos por el nombre de la tabla. Vamos a poner un prefijo identificativo al nombre de la tabla. Cambiad el número 28XX por el número de puerto que se os ha asignado.

mysql -u iot -p
(el sistema pregunta por la contraseña de este usuario. Es la que sale en el apartado anterior después de IDENTIFIED BY)
USE aula206;
CREATE TABLE m28XX_Temperatura (
 time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 value FLOAT,
 PRIMARY KEY (time));

value es el nombre del valor que vamos a introducir en la tabla. Se declara como FLOAT (número real. Permite decimales).

Borrado de tablas

Id con cuidado en el momento de borrar una tabla. El proceso borra todos los datos y la misma tabla. No es posible recuperar los datos de las tablas si no se ha hecho una copia de seguridad.

Para borrar tablas en la base de datos hace falta que el usuario tenga permisos de escritura a la base de datos (iot tiene permisos de escritura en la base de datos aula206. convidat no tiene permisos de escritura en la base de datos aula206).

mysql -u iot -p
(el sistema pregunta por la contraseña de este usuario. Es la que sale en el apartado anterior después de IDENTIFIED BY)
USE aula206;
DROP TABLE m28XX_Temperatura;

Inserción de datos

La inserción (también conocida por escritura) de datos tiene que hacerla un usuario con permisos suficientes (el usuario iot puede escribir).

Ejemplo de inserción del valor 23.4 como valor en el campo value.

insert into m28XX_Temperatura (value) values (23.4);

Lectura de datos

La lectura de datos tiene que hacerla un usuario con permisos suficientes (los usuarios iot y convidat pueden leer).

Documentación oficial de la orden SELECT.

Visualización de todos los datos de la tabla m28XX_Temperatura:

select * from m28XX_Temperatura;

Ejemplo de respuesta del terminal de MariaDB:

MariaDB [aula206]> select * from m28XX_Temperatura;
+---------------------+-------+
| time                | value |
+---------------------+-------+
| 2023-04-05 17:23:59 |  23.4 |
| 2023-04-05 17:28:29 |  23.7 |
+---------------------+-------+
2 rows in set (0.001 sec)

Getting Data from MariaDB

Creación de tablas vista

Para poder usar la base de datos en sistemas externos de graficación nos hará falta que el registro del momento de inserción del dato esté expresado en formato de la época UNIX. Ejemplo de visualización de una tabla vista con el valor del tiempo en formato Unix:

MariaDB [aula206]> select * from m28XX_Temperatura_v;
+------------+-------+
| time       | value |
+------------+-------+
| 1680708239 |  23.4 |
| 1680708509 |  23.7 |
+------------+-------+
2 rows in set (0.000 sec)

Creación de la tabla vista m28XX_Temperatura_v con la unidad de tiempo cambiada (tomando como origen la tabla m28XX_Temperatura):

CREATE VIEW m28XX_Temperatura_v AS select UNIX_TIMESTAMP(time) as time, value from m28XX_Temperatura;

Inserción y lectura de datos múltiples

Habitualmente nos hará falta introducir un grupo de datos al mismo tiempo. Por ejemplo, esta tabla contiene tres valores en el mismo momento del registro:

MariaDB [aula206]> select * from m28XX_T_RH_P_v;
+------------+--------+---------+---------+
| time       | valueT | valueRH | valueP  |
+------------+--------+---------+---------+
| 1680884504 |   23.4 |   45.64 | 1002.34 |
| 1680884734 |   23.7 |   45.21 | 1003.04 |
| 1680885724 |  17.89 |   44.32 | 1001.23 |
| 1680885726 |  19.03 |   40.65 | 1002.11 |
| 1680885728 |  15.43 |   56.78 | 1000.87 |
| 1680886069 |  19.03 |   40.65 | 1002.11 |
+------------+--------+---------+---------+
6 rows in set (0.000 sec)

• Creación de la tabla con datos múltiples (valueT, valueRH y valueP):

CREATE TABLE `m28XX_T_RH_P` (
 `time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 `valueT` FLOAT,
 `valueRH` FLOAT,
 `valueP` FLOAT,
 PRIMARY KEY (`time`))
ENGINE = InnoDB;

• Inserción de datos múltiples:

insert into m28XX_T_RH_P (valueT,valueRH,valueP) values (23.4,45.64,1002.34);

• Lectura de datos múltiples:

select * from m28XX_T_RH_P;

• Creación de la tabla vista:

CREATE VIEW m28XX_T_RH_P_v AS select UNIX_TIMESTAMP(time) as time, valueT, valueRH, valueP from m28XX_T_RH_P;