Instala MariaDB en un equipo independiente de Home Assistant y disfruta de sus ventajas.

Ya vimos cómo optimizar la base de datos sqlite que trae por defecto Home Assistant (sqlite), pero algunas veces esto no es suficiente y necesitamos instalar MariaDB, bien porque queremos guardar muchos días de información ,o porque tenemos muchos sensores. En cualquier caso nuestro sistema puede volverse lento debido al coste de las consultas.

Además, si ese fichero home-assistant-v2.db reside físicamente en una tarjeta MicroSD (lo usual en una primera instalación con Raspberry Pi), ya sabemos que tarde o temprano llegará a su límite de ciclos de lectura/escritura y nos dará problemas.

Nuestro plan será montar otro equipo con Ubuntu y que podremos usar mientras tenemos corriendo el servidor con MariaDB.

Si comparamos sqlite vs MariaDB, MariaDB va ganando popularidad entre la comunidad, y es que ofrece un servicio fácil de instalar y configurar, a la vez que tiene unas prestaciones muy buenas, pues básicamente es una variante de MySQL de código abierto.

Se que muchos me dirán que Docker se creó por algo, pero por el precio de algunos mini-pc’s, o incluso la Raspberry Pi 4, creo que tener un equipo específico nos da mucha flexibilidad como veremos sin mucha complejidad añadida.

Resumiendo, al final nos quedará por tanto un equipo (A) corriendo Home Assistant, y otro equipo (B) donde almacenaremos los datos :

Estructuraremos este post de igual manera: con acciones a realizar en el nuevo equipo (B), y actuaciones en el equipo donde quede Home Assistant (A).

Además, como veremos al final, será importante realizar copias de seguridad.

Actuaciones en el equipo a instalar MariaDB – (B)

Vamos en orden, partiendo de un equipo dedicado, por lo que perderemos todos los datos. Recordad guardar en otro medio cualquier información que os interese conservar pues la perderemos con una instalación limpia desde cero.

Instalación de Ubuntu

Como hemos comentado, vamos a partir de un ordenador para instalar Ubuntu, o bien con Ubuntu ya instalado.

Un fenomenal equipo para este propósito son los NUC. Hay muchos y con opciones muy variadas. En cualquier caso os podéis montar a la carta el equipo perfecto por poco dinero:

La forma de instalar Ubuntu no puede ser más fácil:

• Descargar la última imagen ISO de Ubuntu Desktop
• Crear un usb de arranque con la imagen anterior, por ejemplo con el programa Rufus desde cualquier otro ordenador.
• Arrancar desde USB y seguir los pasos para la instalación. Nos pedirá que creemos un usuario para administrar.
• En nuestro router de acceso a internet, fijad la IP de este nuevo equipo. La forma exacta depende del modelo de router, pero no es muy complicado. Anotad esta IP, pues más tarde nos hará falta.

---

Instalación de MariaDB

Una vez tenemos Ubuntu en nuestro equipo, instalamos el servidor de base de datos con la siguiente secuencia de comandos desde una ventana de terminal:

sudo apt update
sudo apt install mariadb-server
sudo mysql_secure_installation

El último script ( mysql_secure_installation ) es necesario para establecer seguridades, y nos irá preguntando lo que necestamos en cada caso. Cuando nos pregunte por el password de root debemos dar directamente a ENTER, pues todavía no hemos establecido ninguno, y llegado el paso que nos pregunte le diremos que no queremos establecerlo. El resto de preguntas podemos indicar todo con Y.

Nosotros queremos este equipo accesible dentro de nuestra LAN, y sobre todo desde el ordenador que hemos llamado equipo A. Por tanto, nos interesa que acepte conexiones no solo en local. Para conseguir esto, debemos modificar el fichero /etc/mysql/mariadb.conf.d/50-server.cnf usando el siguiente comando:

sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf

Buscaremos la línea donde establece la variable bind-address, donde pondremos bind-address = 0.0.0.0 , ya que por defecto vendrá con la dirección local 127.0.0.1. Salvamos y listo.

Reseteamos MariaDB para que acepte esta nueva configuración, bien reiniciando el propio equipo o mediante la consola. A mi personalmente me gusta verificar que todo queda bien tras un reinicio, pero aquí tienes el comando por si quieres desde consola:

systemctl restart mariadb

Para ver el estado en el que queda, ejecutaremos el siguiente comando. (Ctrl+C para salir ):

systemctl status mariadb

Nos debe quedar activo el servicio:

---

Configuración del servidor MariaDB

Ahora avanzamos y crearemos la base de datos ‘hassio‘ y el usuario ‘homeassistant’ en MariaDB (podéis usar los nombres que queráis, pero propcurad no poner el mismo para no liaros) y le daremos privilegios al usuario. Todo lo haremos desde el Shell de MariaDB, donde entraremos con el siguiente comando:

sudo mariadb

Nos aparecerá el prompt característico MariaDB [(none)]> , donde teclearemos los comandos SQL para realizarlo todo:

MariaDB [(none)]> create database hassio;
MariaDB [(none)]> CREATE USER 'homeassistant'@'%' IDENTIFIED BY 'homeassistant_password';
MariaDB [(none)]> GRANT ALL ON hassio.* TO 'homeassistant'@'%';
MariaDB [(none)]> FLUSH PRIVILEGES;
MariaDB [(none)]> quit

No es que sea obligatorio, pero intentad no usar el carácter ‘@’ en el nombre de la base de datos, en el nombre de usuario o en el password. Si lo usáis, a partir de la versión 2021.07 de HA tendréis que codificar ese carácter como %40 en la cadena de conexión… en definitiva, si no queremos líos innecesarios usad otro carácter en ese caso.

En la base de datos recién creada (‘hassio’) estarán entre otras cosas las tablas de eventos y estados donde enviaremos la información para que se almacene.

---

Actuaciones en el equipo con Home Assistant – (A)

Esta parte es un poco más fácil, pues solo tendremos que decirle a la integración recorder dónde enviar los datos.

Modificación de configuración recorder

En el fichero configuration.yaml (o en otro independiente si hemos usado include), en la sección recorder pondremos la siguiente línea de conexión:

recorder:
  db_url: mysql://homeassistant:homeassistant_password@ip_equipo_mariadb/hassio?charset=utf8mb4
  purge_keep_days: 365

En este caso, quiero que guarde 1 año de datos, pero aunque ahora tengamos más capacidad y velocidad en el nuevo equipo, es muy interesante usar exclude para evitar almacenar información que no nos hará falta. Por ejemplo:

recorder:
  db_url: mysql://homeassistant:homeassistant_password@ip_equipo_mariadb/hassio?charset=utf8mb4
  purge_keep_days: 365
  exclude:
    entities:
      - sun.sun

Recordad como siempre particularizar los códigos anteriores con vuestros datos, en concreto el nombre de usuario, su clave y la dirección ip del equipo donde hemos instalado MariaDB (la IP que apuntamos al principio al instalar Ubuntu).

Reiniciaremos HA, y si todo ha ido bien, lo primero que notaremos es que empezamos sin datos históricos, pero se irán generando a medida que se van llenando las tablas de eventos y estados.

---

A partir de aquí tenemos muchas opciones para jugar.

Podremos por ejemplo usar phpmyadmin para verificar estadísticas de uso y tamaños.

Si no tienes ya instalado phpmyadmin (viene incluido en xampp), en ubuntu solo tienes que introducir el siguiente comando en una terminal, y en las opciones que salgan elegir apache2 (seleccionar con la tecla espacio y saltar a OK con la tecla Tab):

sudo apt-get install phpmyadmin

O monitorizar las consultas y el flujo de datos:

También os animo a probar el cada vez más conocido HeidiSQL para bucear en la base de datos, ya que funciona de maravilla con MariaDB.

---

Copias de seguridad

Es importante planear los posibles problemas que podemos tener. HA tiene múltiples opciones para realizar copias de seguridad (la opción Backups (anteriormente llamada snapshots) por ejemplo, o también otras opciones los que uséis Docker). Todos estos casos están muy bien documentados en Internet.

Nosotros ampliaremos esta información indicando una manera fácil de realizar copias de seguridad de nuestros datos almacenados, pues siempre podremos empezar desde cero esta guía y usar nuestro fichero backup para recuperar todos esos datos.

Vamos a optar por una exportación lógica con el comando mysqldump (exportamos los comandos SQL necesarios junto con los datos), en contraposición a una física (copia de ficheros).

Esto como todo es cuestión de gustos, pero creo que un fichero .sql es un formato mucho más abierto que podremos importar en otras versiones o incluso en otras bases de datos.

Además, en una copia física al no realiarse simultáneamente la copia de todos los ficheros, tendríamos que realizar un paso más preparándola para no tener inconsistencias.

Con el volcado lógico nos ahorramos esos problemas, pero por contra el resultado ocupará más espacio, pero esto lo podremos solucionar comprimiendo el fichero generado.

Vamos a dar todos los privilegios al usuario homeassistant para poder hacer un backup de todas las bases de datos. Otra opción sería crear un usuario específico para backups, pero vamos a dejar las cosas lo más simples posibles:

sudo mariadb

MariaDB > GRANT ALL privileges ON *.* TO 'homeassistant'@'localhost' IDENTIFIED BY 'homeassistant_password;

Realizaremos el volcado (preguntará el password del usuario homeassistant) con mysqldump. Nosotros hemos optado por un backup de todas las bases de datos, pero se podría particularizar solo para la que nos interesa, e incluso solo una tabla concreta:

mysqldump --all-databases --single-transaction --quick --lock-tables=false > $(date +%Y%m%dT%H%M%S).MariaDB_Backup.sql -u homeassistant -p

Creará el fichero en la carpeta donde hayamos ejecutado el comando, con la fecha y hora incluido en el propio nombre del fichero para luego identificar bien.

Para restaurar la copia sobre un servidor ya creado y con el usuario homeassistant con todos los privilegios usaremos:

mysql -u homeassistant -p < nombre_del_fichero.sql

---

Automatizamos la generación de backup de MariaDB Server

Vamos a ir un paso más e intentar generar de manera automática dichos ficheros de backup que podremos copiar en algún medio externo o subir a una nube. Como siempre, particularizad para vuestro caso concreto los comandos.

Crearemos previamente las carpetas /home/tu_usuario/script (donde guardaremos el script) y /home/tu_usuario/backup (donde guardaremos los ficheros generados)

El fichero bash lo ponemos en la carpeta /home/tu_usuario/script , y lo llamamos por ejemplo mysql-backup.sh:

dias=15

usuario=homeassistant
clave=homeassistant_password

carpeta=/home/tu_usuario/backup

sqlfile=$carpeta/$(date +%Y%m%dT%H%M%S).MariaDB_Backup.sql
zipfile=$carpeta/$(date +%Y%m%dT%H%M%S).MariaDB_Backup.zip 

sudo mysqldump -u $usuario -p$clave --all-databases > $sqlfile 

zip $zipfile $sqlfile 

rm $sqlfile 

find $carpeta -mtime +$dias -delete

echo 'Backup MariaDB realizado'

Se entiende, no? Vamos a guardar los últimos 15 días de copias en la carpeta /home/tu_usuario/backup. Además nos dejará únicamente el fichero comprimido.

Y lo probamos:

bash /home/tu_usuario/script/mysql-backup.sh

Comprobaremos que en /home/tu_usuario/backup se ha creado un fichero comprimido con el backup:

---

Ya solo nos queda disfrutar de la velocidad y tranquilidad que nos dará nuestra nueva configuración.

En otros posts siempre he defendido que una de las mayores aportaciones de tener Home Assistant instalado (aparte de disfrutar cacharreando) es poder explotar todo el potencial que tienen estos datos que generamos, así que os animo a curiosear con algunos análisis que podemos realizar:

IFTTT con Home Assistant. Guarda datos en Google Drive

Cómo realizar un análisis de consumo de energía con Home Assistant.

Cómo usar el sensor Bayesian en Home Assistant.