Migration BDD SQLite vers MariaDB

Dans la continuité de mes déboires de base de données Home Assistant ICI
Je vous propose une méthode que j’ai utilisé lors de mes recherches pour faire faire un régime important à ma BDD.

Je vous rappelle que j’avais une base de données de 7,5Go en SQLite la base par défaut de HA. Au travers de lecture de-ci de-là en français en anglais, en diagonale parfois aussi, j’ai vu que MariaDB 1/10ème moins gourmand en taille que SQLite !

Ayant réduit ma base à 700Mo je me dis merveilleux ça encore une division pour 10 pour 70Mo :scream: Je pense que j’ai lu un peu vite, mais je ne retrouve pas l’article pour le relire sérieusement et comprendre ce 1/10ème. (J’espère que nos experts m’expliquerons à l’ici de ce mini tuto)

Pour moi c’est un réduction de 10% de la taille de la base SQLite tout au plus- mais c’est déjà ça. Ensuite cela peut préparer aussi le terrain pour une future externalisation de la base de données sur un autre support (Docker ou VM selon vos choix, envie et installation déjà en place)

Ma source Migrating home assistant database from sqlite to mariadb.. ONLY if you are VERY familiar with Database administration - #126 by eddiewebb - Share your Projects! - Home Assistant Community

J’ai testé la solution sur une VM Proxmox spécialement montée pour ça avec le script que bcp connaisse pour aller plus vite. J’aurais pu utiliser le template de VM que j’avais fait avec @barto_95 et @fredarro mais je n’y avais pas pensé.

J’ai injecté le Backup full de ma prod afin d’avoir la même base de données

Solution

La solution utilisée permet la migration rapide de la base de données SQLite par défaut de nos HA vers MariaDB/MySQL. Je préconise tout de même de partir d’un base pas trop grosse pour mettre les chance de son coté et ne pas attendre trop longtemps non plus.

Aucun transfert de fichier n’est nécessaire pour traiter la base en dehors de HA, quelques lignes de commande dans le terminal et le tour est joué.

Les prérequis

  • installer et activer l’addon MariaDB
  • Installer et activer l’addon Terminal & SSH

Étapes de la migration de Maria DB

  1. Arrêter le service d’enregistreur à partir des outils de développement
    « Outils de développement » > « Services » > « Recorder Disable » > Appuyer sur le « Appeler le service »
  2. Démarrer l’addon MariaDB si c’est pas déjà fait
  3. Démarrez l’addon Terminal & SSH
  4. Exécutez les commandes ci-dessous pour installer python
  5. Exécuter les commande nécessaire à la migration de la base de données

Après la migration (réussie bien entendu)

  • Configurez HA pour utiliser le nouveau mariadb comme enregistreur dans configuration.yaml

Commandes pour faire la migration

Installation des pré-requis dans le terminal SSH

apk add python3
apk add py3-pip
pip3 install sqlite3-to-mysql

Démarrage de la migration (changer PASSWORD, DATABASE_NAME et DATABASE_USER)

sqlite3mysql \
--sqlite-file /config/home-assistant_v2.db \
--mysql-database DATABASE_NAME \
--mysql-host core-mariadb \
--mysql-user DATABASE_USER \
--mysql-password 'YOURPASSWORD'

Ces éléments sont ceux présent dans la configuration de l’addon MariaDB
DATABASE_NAME = homeassistant
DATABASE_USER = homeassistant
YOURPASSWORD = Votre mot de passe

(optionnel, include --chunk INTEGER pour exécuter la migration par blocs d’enregistrements. Cela peut aider les grande bases de données surtout si on a peu de ressources machine.)

Allez lancement de la migration à 21h36 fin de la migration à 21h56 soit 20 minutes pour 700/800Mo. 20 minutes pendant lesquels il n’y aurait pas enregistrement dans la base et on comprends pourquoi non :wink: ?

Arguments pour le script et plus d’informations ici → sqlite3-to-mysql · PyPI

Maintenant que la base est migrée, il faut configurer HA pour utiliser la nouvelle base de données MariaDB comme enregistreur au niveau de votre fichier configuration.yaml
Au niveau de votre recorder il faut ajouter la ligne en début :

db_url: !secret db_mysql

Et dans le fichier secret il faut mettre

db_mysql: mysql://user:mot_de_passe@coremariadb/homeassistant?charset=utf8mb4

J’ai installé l’addon phpMyAdmin enfin, et une fois démarré je suis aller sur la partie compte utilisateur.
Vous y trouverez donc votre nom de user indiqué dans MariaDB. Vous le sélectionnez et vous aller tomber sur cette écran :

Cliquez sur Privilèges globaux → Tout cocher

Puis tout en bas appuyer sur Exécuter

Voilà on touche à la fin tout doit repartir comme avant ni vue ni connue et je ne crois pas avoir oublié d’étape :crazy_face: dans tout ça.

Après 48h mes deux instances HA (une sous SQLite et l’autre sous MariaDB ont une BDD identique en taille) donc je ne sais vraiment pas si je vais franchir le cap en production pour le moment.

NB :

  • je ne suis pas responsable des manipulations que vous faites,
  • Mon contexte n’est pas le votre donc je ne peux pas dire si cela se passera aussi bien pour vous que pour moi.
  • Je n’ai pas non plus les compétences pour vous dépanner sur vos installations donc faites des sauvegardes de vos données et de vos HA avant de vous lancer

Ma prochaine étape :
Suivre les travaux démarrer et certainement pas loin d’être finalisé de @SNoof

Suivi de prêt pas les très actifs @Pulpy-luke, @Golfvert et @Neuvidor

3 « J'aime »

Salut

La structure de sqlite est très basique et ne contient pratiquement que des données, donc c’est techniquement difficile de reduire la taille de la base par 10 :wink:

Je viens tout juste d’écrire que tu me réponds à la vitesse de l’éclaire :rofl:
Trop fort @Pulpy-Luke

Sinon oui je me doute bien que j’ai du lire en travers après ma journée de ski :hot_face: mais bon ça fait des anecdotes, on en parlera au coin du feu l’hiver prochain :wink:

Si c’était pour économiser de la place, je pense que vu la structure basique de la base de données de HA, c’est loupé :slight_smile:

Avis perso, sauf besoin très particulier (garder un historique ad vitam aeternam indépendant de l’outil de domotique), mariadb n’a guère d’intérêt comparé à SQLlite.

Par contre influxdb avec la gestion des buckets et les graphiques via Grafana, là, c’est plus utile.

Et dans le même genre qu’influxdb, il y a aussi prometheus Prometheus - Home Assistant qui est aussi interfacé avec Grafana.
Différence majeure, c’est prometheus qui vient interroger HA pour récupérer les stats et pas HA qui pousse vers influxdb. Dans certains cas, ça peut être utile…

Perso c’est tout l’intérêt de passer sur MariaDB.
Une migration de HA d’un système à un autre est accéléré, les sauvegardes de HA sont plus petites.

InfluxDB je ne vois pas l’intérêt pour des températures et de l’électricité. En plus leur modèle de DB est un poil trop complexe pour perdre du temps dessus. Un bon vieux SQL sur une base relationnelle c’est quand même plus abordable pour la domotique.

Je suis d’accord avec toi @golfvert c’est loupé
N’étant pas un expert de la BDD j’ai testé et prouver par le test ce que tu dis d’où pour le moment mon envie limitée de bascule de ma production.

Mon besoin est assez basique au final enfin je pense c’est garder de l’histoire sur les consommations diverses, ma production solaire, température, humidité (typologie de la maison) etc…
Et ça oui je vais passer par une solution tiers comme influxDB pour démarrer car j’ai déjà un VM avec mais je ne traite pas encore de données je les stocke juste pour le moment.
Prometheus j’ai vu que cela existait mais pas plus.

Pour ce qui est d’envoyer les données vers influxDB en passant par node red et en interrogeant le brocker directement sans passer par HA cela doit se faire il me semble

@AlexHass tu commence à me perdre :slight_smile:
Je comprends l’externalisation de MariaDB
Le reste me semble un peu loin de mes compétence à lire comme ça

Il est vrai que je préfère avoir les éléments en dehors de HA (node red, influxDB, Z2M, Brocker, Zwavejs etc…) mais là je suis pas à l’aise encore

Dans mon expérience, quand la BDD sqlite est plantée, difficile d’en récupérer qqchse. Quand mariadb est corrompu, il y a encore des techniques pour récupérer des données.
(En tous cas, dans mon expérience je n’ai pas pu récuperer des données de BDDs sqlite3 plantée, et j’ai pu le faire pour mysql/mariadb).

Par ailleurs - on peut aussi dupliquer une BDD mariadb en temps réel (maitre/esclave, galera).

Aussi sqlite3 c’est pas l’idéal poure faire des requêtes depuis plusieurs processus - surtout en écriture - alors qu’avec mariadb il n’y a pas de souci à part modifier les mêmes choses en même temps.

1 « J'aime »

Honnêtement, j’ai tous les composants sur un unique Raspberry, des conneries j’en en faites, mais avec une sauvegarde full tous les soirs conservée avec un roulement de 7 jours, plus les 4 derniers lundi, on arrive toujours à récupérer son système.

2 « J'aime »

Tout ça c’est vrai mais il y a quand même 1 point de départ :
10 j de rétention, 1 écriture toutes les 5 secondes… Ça casse pas 3 pattes à un canard
Donc tout ça, c’est valable dans le monde de l’entreprise mais pour de la domotique personnelle c’est overkill.
Une bonne politique de backup ça va plus vite et c’est moins compliqué

2 « J'aime »

Bonjour, j’ai un probleme apres la migration j’ai l’impression que les données soit enregistrer à la meme heure, voici un exemple :


Une idée ?
Merci