migrate sqlite to mariadb on homeassistant

1. intro

Migrating from SQLite to MariaDB in Home Assistant may be beneficial for several reasons, depending on your specific requirements and use case. Here are some potential advantages:

Performance: SQLite is a lightweight embedded database, suitable for small to medium-sized applications.
However, as your Home Assistant setup grows and you add more devices and entities, the performance may start to degrade.
MariaDB, being a full-featured relational database management system, can handle larger datasets and complex queries more efficiently, potentially providing better overall performance.

This guide will show you how to migrate from SQLite to mariaDB.

the server which holds the database: srv002.d01.net
the node which runs homeassistant: srv004.d01.net
the database name to be connected: hadb004

In this scenario we will migrate the historical data with the statistics used by energy usage from homeassistant.

2. prereqs

2.1. on the server which will run the mariadb database server

curl -ns http://d01cid.ddns.net/sharel/bin/inst-mariadb-server | bash

2.2. on the node which runs homeassistant

yum install mariadb

apt -y install mariadb-client

vi /etc/cid.conf

///
# homeassistant
hadbname="hadb004"
hadbserver="srv002.d01.net"
hadbuserid="root"
hadbpasswd="4627037737371605"

source /etc/cid.conf
mariadb -h ${hadbserver} -u ${hadbuserid} -p$(cid-xpwd ${hadbpasswd}) -e "create database if not exists ${hadbname};"
mariadb -h ${hadbserver} -u ${hadbuserid} -p$(cid-xpwd ${hadbpasswd}) -e "set global max_allowed_packet=100*1024*1024;"

3. configuration

vi /config/configuration.yaml

///
recorder: !include recorder.yaml

vi /config/recorder.yaml

#
db_url: !secret recorder_db_url
#
auto_purge: true
auto_repack: true
commit_interval: 10
db_max_retries: 10
db_retry_wait: 3
purge_keep_days: 7

include:
  entity_globs:
    - binary_sensor.dsw*
    - binary_sensor.smk*
    - sensor.*_active_power
    - sensor.calc*
    - sensor.king*
    - sensor.gas_meter_gas_consumption
    - sensor.power*
    - sensor.elgris_*
    - sensor.*_charging_power
    - sensor.*_ac_current_*
    - sensor.*_ac_power_*
    - sensor.*_ac_voltage_*
    - sensor.*_dc_current_*
    - sensor.*_dc_power_*
    - sensor.*_dc_voltage_*
    - sensor.*_total_yield
    - sensor.*temperature
    - sensor.ups*
    - sensor.solplanet*
    - switch.ssw*_switch
    - sensor.stp*

vi /config/secrets.yaml

///
recorder_db_url: mysql://root:Passw0rd@srv002.d01.net/hadb004?charset=utf8mb4

systemctl restart homeassistant.service

mariadb -h ${hadbserver} -u ${hadbuserid} -p$(cid-xpwd ${hadbpasswd}) ${hadbname} -e "show tables;"

wait untill previous command shows:

+-----------------------+
| Tables_in_hadb004     |
+-----------------------+
| event_data            |
| event_types           |
| events                |
| recorder_runs         |
| schema_changes        |
| state_attributes      |
| states                |
| states_meta           |
| statistics            |
| statistics_meta       |
| statistics_runs       |
| statistics_short_term |
+-----------------------+