Preparar la base de datos MySQL o MariaDB

Configurar el servidor de bases de datos

Una vez instalado el conjunto de servicios LAMP, WAMP, MAMP o XAMPP como se explicaba en el primer artículo de la serie o simplemente después de instalar en la máquina que va a actuar de servidor la suma de aplicaciones que implementan los servicios correspondientes es necesario ajustar algunos aspectos de la configuración del servidor de bases de datos MySQL así como crear las bases de datos y las tablas que se van a utilizar en el servidor web para almacenar datos de los dispositivos de Internet de las cosas.

Si se utiliza un servidor público seguramente el proveedor habrá dispuesto algún tipo de panel para intermediar entre el usuario y la configuración «real» de la base de datos al objeto de reforzar la seguridad en la operación. De ser así habrá que remitirse a las instrucciones que este proveedor ofrezca. En el caso de ser un servidor real o virtual hospedado en las instalaciones de un tercero puede darse una circunstancia equiparable cuando el proveedor lo administre; si se ha contratado un servidor no-administrado (por el proveedor, administrado por el usuario) sí será de aplicación la información que sigue.

Como lo importante es conocer las instrucciones que deben darse al gestor de bases de datos y se entiende que se está usando como un medio para dar soporte a los dispositivos para la Internet de las cosas, que sería el objeto del proyecto, puede ser suficiente utilizar la consola ya que el trabajo sobre el propio servidor de bases de datos será ocasional. Si la parte de trabajo de gestión empieza a aumentar puede que merezca la pena, por ser más cómodo, utilizar un gestor en el propio servidor, como el popular phpMyAdmin (desde un navegador web) o localmente, aunque por supuesto accediendo al servidor de bases de datos, con un administrador como MySQL Workbench desde el que realizar en un avanzado GUI las operaciones que se van a explicar y muchas otras que quedan fuera de este artículo y que merecen toda una serie.

Además de las operaciones más o menos automáticas que pueden hacerse con MySQL Workbench sobre el servidor, las bases de datos o las tablas también es posible realizar tareas «manualmente» escribiendo y ejecutando script completos u órdenes independientes en SQL. Igual que ocurre con la herramienta MySQL Workbench, SQL requiere capítulo aparte y que será imprescindible conocer a fondo cuando la parte del trabajo web IoT que se realiza implique una atención más seria al backend de la que se explica aquí.

En cualquier caso, y desde luego para resolver lo necesario de la configuración la base de datos para IoT que se está tratando, se puede utilizar la consola.

Configurar root, el administrador principal de MySQL

Si el servidor de bases de datos está en una máquina propia, lo primero que hay que hacer es configurar los permisos administrador del servicio para poder crear, usando esos privilegios, otros usuarios (que a su vez podrán tenerlos) y, en su caso, las bases de datos y las tablas en las que almacenar la información que el sistema para Internet de las cosas necesita.

Es importante atender al proceso de instalación del conjunto de servicios o del servidor MySQL, según sea el proceso seguido, ya que, en muchos casos, el propio instalador se encargará de realizar este paso y otros y no será necesario repetirlos salvo cuando se desee modificar la configuración.

Para realizar estas tareas se suele trabajar con la consola de MySQL como administrador. Para acceder en este modo se usa la orden mysql -u root -p o sudo mysql -u root si se usa por primera vez y durante la instalación no se ha configurado el usuario root

En la orden mysql -u root -p se indica con la opción -u el nombre del usuario (root) y con la opción -p (de password) que debe solicitarse la clave al acceder a la consola MySQL. Para volver a la consola del sistema se puede usar la orden quit para salir

Como se ve en la captura de pantalla de arriba, antes de abandonar la consola de MySQL, para establecer los privilegios del usuario root se ha utilizado la orden:
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY '1234' WITH GRANT OPTION;
en la que:

• se otorgan todos los privilegios GRANT ALL PRIVILEGES
• en todas las tablas de todas las bases de datos ON *.*
• al usuario root accediendo desde la propia máquina que ejecuta el servidor MySQL TO 'root'@'localhost'
• usando como clave 1234 IDENTIFIED BY '1234'
• con permiso para autorizar a otros usuarios WITH GRANT OPTION

Para poder administrar la base de datos remotamente se deben otorgar privilegios al usuario correspondiente desde otras ubicaciones. En el ejemplo anterior se permitía que root accediera desde la máquina en la que se ejecuta el servidor de bases de datos (localhost) con 'root'@'localhost', si se utiliza 'root'@'%' se permitirá el acceso desde cualquier máquina.

Si se tienen privilegios de administrador en el sistema se puede volver a establecer la clave del usuario root de MySQL. Para hacerlo, es necesario parar el servicio (mysql o mysqld, dependiendo de la distribución), arrancarlo sin clave, establecer la nueva clave de root y reiniciarlo. Al acceder sin clave también es posible otorgar los privilegios descritos arriba.

• parar el servicio sudo /etc/init.d/mysql stop
• lanzar MySQL sin clave y liberar la consola sudo mysqld_safe --skip-grant-tables &
• acceder como «root» mysql -u root
• cambiar la clave:
  • acceder a la base de datos «mysql»: USE mysql;
  • asignar a «root» la clave «1234»: UPDATE user SET authentication_string=PASSWORD("1234") WHERE user='root';
  • aplicar los nuevos privilegios: FLUSH PRIVILEGES;
  • salir de la consola de mysql y volver a la del sistema: quit

• reiniciar el servicio sudo /etc/init.d/mysql restart

Configurar el acceso remoto a la base de datos

Por razones de seguridad, la configuración inicial del gestor de bases de datos MySQL no permite el acceso remoto, para cambiarlo es necesario editar el documento de configuración, normalmente my.cnf, que normalmente se encuentra en la ruta /etc/mysql.

Si se utiliza MariaDB, que es ahora la versión «más libre», de MySQL, seguramente my.cnf será un enlace a /etc/alternatives/my.cnf que, a su vez, cargará la configuración, usando !includedir, de los documentos de la ruta /etc/mysql/mariadb.conf.d, seguramente del documento 50-server.cnf

El recurso que utiliza el servidor de bases de datos para no atender peticiones remotas consiste en «escuchar» solo las peticiones que se hagan desde cierta dirección IP que se expresa como valor del parámetro bind-address (que sustituye al clásico skip-networking) Cuando la dirección es 127.0.0.1 (localhost) sólo se atienden peticiones desde la máquina local. Si se comenta con # o si el valor es 0.0.0.0, se atiende a cualquier dirección. Para permitir accesos externos lo más seguro es indicar la dirección IP (que puede ser pública o privada) del equipo desde el que se accede, normalmente algo como bind-address = 192.168.1.74. En su caso, este equipo puede actuar como un túnel (redireccionando con SSH) o como una pasarela (con las tablas ip del núcleo) para no exponer el servidor fuera de una zona controlada de la red.

En la captura de pantalla anterior puede verse que se ha editado my.cnf con la configuración de MySQL usando la orden sudo nano /etc/mysql/my.cnf. En el caso de trabajar con MariaDB con la configuración habitual, la orden sería algo parecido a sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf. Como se decía más arriba, una vez que hay acceso con privilegios administrativos puede usarse un GUI como MySQL Workbench para la gestión habitual de las bases de datos.

Crear las bases de datos

Una vez configurado el servidor de bases de datos pueden crearse las bases de datos y las tablas en las que almacenar los datos IoT. Para hacerlo, se pueden usar órdenes SQL desde la consola o desde el GUI de un gestor.

La orden CREATE DATABASE temperaturas; crea la base de datos «temperaturas» Para evitar errores puede usarse la forma CREATE DATABASE IF NOT EXISTS base_datos_temperaturas; que crearía la base de datos «base_datos_temperaturas» sólo si no existe actualmente.

Crear las tablas

Como en diferentes bases de datos podrían existir tablas con igual nombre se puede hacer referencia a una de ellas precediendo su nombre por el de la base de datos y separando ambos por un punto, algo como base_datos.tabla Esta notación se puede extender también al nombre de los campos base.tabla.campo

Si se omite el nombre de la base de datos al operar sobre una tabla (en este caso, al crearla) se considera que se hace referencia a la última seleccionada. Para seleccionar expresamente una concreta se puede utilizar la orden USE e indicar su nombre USE base_datos_temperaturas;

Al crear las tablas en las bases de datos, en la misma operación, se suele añadir la información de los campos que las forman (aunque también es posible alterarlas posteriormente) Igual que se hace con la base de datos, se puede crear la tabla sólo si no existe añadiendo la cláusula IF NOT EXISTS o al contrario, asegurarse de borrar la tabla antes de crearla sólo si existe usando DROP TABLE IF EXISTS valor_temperaturas;

Para permitir nombres de tablas o bases de datos que pudieran entrar en conflicto con órdenes o procedimientos se puede utilizar el signo de acento grave, llamado a veces comilla invertida, para rodear el nombre y evitar conflictos o errores en las órdenes SQL.

Definir los campos

Los campos que se añaden al crear la tabla se indican encerrándolos entre paréntesis e indicando el tipo de datos que almacenan. Los tipos de datos que suelen usarse en una base de datos para almacenar información de dispositivos conectados a la Internet de las cosas son los enteros (INT o BIGINT) decimales (DECIMAL, DOUBLE o FLOAT) de tiempo (DATE, TIME, DATETIME o TIMESTAMP) y de texto (principalmente VARCHAR aunque existen otros como TEXT pero no se usan para IoT) Después del tipo de datos, y entre paréntesis, se añade la longitud del dato y en su caso el formato o la precisión.

Al añadir AUTO_INCREMENT a la definición de un campo se indica al gestor de bases de datos que, si no se indica otra cosa, al crear un nuevo registro debe almacenarse automáticamente el valor del último más el incremento correspondiente (que normalmente es 1) Suele incluirse en los campos que se utilizan como índices numéricos de los registros.

El código NOT NULL indica que un campo de un registro no puede quedar vacío, que debe almacenar algún valor.

Al usar DEFAULT en la creación de un campo de una tabla se indica a MySQL un valor por defecto para el campo siempre que al añadir un nuevo registro o modificarlo no se indique otro expresamente. Un valor frecuentemente usado por defecto, en este caso para monitorizar valores de la IoT, es CURRENT_TIMESTAMP que permite grabar automáticamente la hora actual en un campo al crear un nuevo registro, por lo que un dato queda automáticamente identificado en el tiempo.

Para optimizar el rendimiento de la base de datos es conveniente incluir al final de la definición de los campos de la tabla uno que se utilice como índice con la cláusula PRIMARY KEY (ID) siendo ID el campo elegido como índice.

Al final de la definición de una tabla se puede indicar el motor de bases de datos con el que se gestiona como valor del parámetro ENGINE con el formato ENGINE=InnoDB; siendo InnoDB el motor elegido en este caso.

Crear bases de datos, tablas y configurar campos en una sesión SQL

En el código del siguiente ejemplo se reúnen las órdenes, los pasos que habría que dar, para crear una base de datos con una tabla mínima.

Crear copias de respaldo de las bases de datos

Una vez configurado el servidor y creadas las base de datos y sus tablas estaría todo listo para almacenar la información obtenida por los nodos IoT. Aunque el objetivo de esta serie de artículos no incluye el mantenimiento, hay una cuestión administrativa crítica que es importante atender, se trata de las copias de seguridad. Desde la mayoría de los gestores, incluyendo phpMyAdmin y MySQL Workbench, pueden hacerse copias de seguridad y restaurarlas pero es común incluirlas en algún script que las automatice (seguramente junto a la configuración y muchos otros documentos) para lo que es más cómodo usar la herramienta MySQLDump, un cliente que suele ser parte de una instalación convencional de MySQL, o alguna alternativa como MySQL Data Dumper que incluye la orden mydumper para realizar la copia y myloader para restaurarla

MySQLDump

El formato de la orden mysqldump es muy sencillo, sólo hay que indicar el nombre de usuario user, la contraseña password y la base de datos que se desea copiar o --all-databases para hacer una copia de seguridad de todas las bases de datos como en el siguiente ejemplo:

mysqldump --opt --user=pelaez --password=1234 --all-databases > copia.sql

El ejemplo anterior genera el documento copia.sql que contiene las órdenes SQL (es decir, texto) necesarias para reconstruir todas las bases de datos. Al guardarse en formato texto, la copia ocupa mucho más de lo imprescindible por lo que suele también comprimirse para ahorrar espacio en el dispositivo en el que se almacene.

La copia de seguridad generada con la orden del ejemplo se podría restaurar si por un error se perdiera la información con la orden de abajo que ejecuta las operaciones SQL del documento copia.sql

mysql --user=pelaez --password=1234 < copia.sql

MySQL Data Dumper

Si bien con las operaciones anteriores sería suficiente, es muy común usar MySQL Data Dumper, ya casi un estándar por su rendimiento aunque no forme parte de las herramientas base que se incluyen con MySQL

Las siguientes son las opciones de mydumper más usadas que, lógicamente, deben escribirse como parte de la misma orden, separadas por espacios, aunque en este texto se hayan separado en líneas para facilitar la lectura:

--host polaridad.es «polaridad.es» es el nombre del servidor de bases de datos
--port 3306 «3306» es el número del puerto en el que escucha el servidor (por defecto es el 3306)
--user pelaez «pelaez» es el nombre del usuario con el que se accede a la base de datos
--password 1234 «1234» es la clave del usuario con el que se accede a la base de datos
--database temperaturas «temperaturas» es el nombre de la base de datos que se respalda
--no-locks no bloquear la base de datos durante la copia (por defecto se bloquea)
--build-empty-files copiar también tablas vacías
--compress-input comprimir la entrada
--compress comprimir la salida (la copia de seguridad resultante)

Para restaurar la copia de seguridad obtenida con mydumper se usa myloader que se usa principalmente con las siguientes opciones, que deben incluirse en la misma orden separadas por espacios:

--host polaridad.es «polaridad.es» es el nombre del servidor de bases de datos
--port 3306 «3306» es el número del puerto en el que escucha el servidor (por defecto es el 3306)
--user pelaez «pelaez» es el nombre del usuario con el que se accede a la base de datos
--password 1234 «1234» es la clave del usuario con el que se accede a la base de datos
--directory=copia_iot «copia_iot» es la carpeta en la que se encuentra la copia generada con mydumper
--overwrite-tables sobrescribir las tablas aunque ya existan
--verbose 3 «3» es grado de información del proceso (más alto, más mensajes)
-C usar compresión en el flujo de datos

En el próximo (y último) artículo de esta serie se explica cómo usar el lenguaje de programación PHP para almacenar la información obtenida por los dispositivos conectados a la Internet de las cosas en las bases de datos de las que se ha hablado en este texto.