Migrate Home Assistant sqlite3 to mariadb/mysql

.-README.md

Note

⚠️ The useful file is migrate_db.sh

(GITHUB gists do not allow us to manage the order easily)

.pre-commit-config.yaml

---
repos:
  - repo: https://github.com/pre-commit/pre-commit-hooks
    rev: v4.5.0
    hooks:
      - id: no-commit-to-branch
        args: [--branch, inactivemain]
      - id: check-yaml
        args: [--unsafe]
      - id: debug-statements
      - id: end-of-file-fixer
      - id: trailing-whitespace
      - id: check-json
      - id: mixed-line-ending
      - id: check-builtin-literals
      - id: check-ast
      - id: check-merge-conflict
      - id: check-executables-have-shebangs
      - id: check-shebang-scripts-are-executable
      - id: check-docstring-first
      - id: fix-byte-order-marker
      - id: check-case-conflict
      # - id: check-toml
  - repo: https://github.com/lovesegfault/beautysh.git
    rev: v6.2.1
    hooks:
      - id: beautysh
  - repo: https://github.com/shellcheck-py/shellcheck-py
    rev: v0.9.0.6
    hooks:
      - id: shellcheck
        args: [--shell, bash]
  - repo: https://github.com/adrienverge/yamllint.git
    rev: v1.33.0
    hooks:
      - id: yamllint
        args: [--no-warnings]
  - repo: https://github.com/codespell-project/codespell
    rev: v2.2.6
    hooks:
      - id: codespell
        args:
          - --ignore-words-list=hass
          - --skip="./.*"
          - --quiet-level=2
  - repo: https://github.com/executablebooks/mdformat
    rev: 0.7.17
    hooks:
      - id: mdformat
        name: Format Markdown
        entry: mdformat  # Executable to run, with fixed options
        language: python
        types: [markdown]
        args: [--wrap, '75', --number]

migrate_db.sh

#!/bin/bash -xv
# File: migrate_db.sh

# shellcheck disable=SC2050
if [ 0 == 1 ] ; then
    # Below is some sample code that you can put in another script before
    # calling this script so that this one can be a common script
    MYSQL=/cygdrive/c/wamp64/bin/mariadb/mariadb10.4.10/bin/mysql
    DBUSER=hass
    DBPASS=1234
    DBHOST=homeassistant.local
    #   Port of mysql server
    #   On HAOS - Host port defined in Add-On configuration
    DBPORT=4000
    DATABASE=homeassistant

    # When 1, install DB server software on current platform
    INSTALL_PACKETS=0
    # When 1, get the sqlite3_to_mysql repository (needed only once)
    GET_SQLITE3_TO_MYSQL=1
    # When 1, do actually migrate
    DRY_RUN=1
    # When 1, add ssh configuration for 'ssh ha-local'
    # Needed only once
    SETUP_SSHCONFIG=0

    # Include the script that does the work (including preserves variables)
    . ./migrate_db.sh
fi

MYSQL=${MYSQL:=mysql}
GREP=${GREP:=grep}
AWK=${AWK:=awk}
INSTALL_PACKETS=${INSTALL_PACKETS:=0}
GET_SQLITE3_TO_MYSQL=${GET_SQLITE3_TO_MYSQL:=0}
DRY_RUN=${DRY_RUN:=1}
SETUP_SSHCONFIG=${SETUP_SSHCONFIG:=0}

if [ 1 == "${INSTALL_PACKETS}" ] ; then
    sudo apt install mariadb-server mariadb-client
    sudo apt install libmariadb-dev sqlite3
    sudo mysql_secure_installation
fi

SQLITE3_TO_MYSQL=sqlite3-to-mysql
if [ 1 == "${GET_SQLITE3_TO_MYSQL}" ] ; then
    git clone https://github.com/athlite/sqlite3-to-mysql
fi

#Prepared db-url in secrets.yaml && db configuration in configuration.yaml.
#Created a user ${DBUSER} with all permissions
#Created a database ${DATABASE}

# The database continues running when ha is stopped,

#
if [ 1 == "${SETUP_SSHCONFIG}" ] ; then
    # for simplicity set up an alias to access your host using ssh.
    # I use `ha-local` and I can do `ssh ha-local`
    cat >> ~/.ssh/ssh_config << EOS
Host ha-local
    HostName homeassistant.local
    User root
    Compression no
    StrictHostKeyChecking no
EOS
fi




SQLITE_DBFILENAME=home-assistant_v2.db
DUMP_FILENAME=hadump.sql
IMPORT_FILENAME=haimport.sql.gz

# rsync -av ha-local:/config/${SQLITE_DBFILENAME} ${SQLITE_DBFILENAME}
COPYDB=0
if [ ${COPYDB} = 1 ] ; then
    scp ha-local:/config/${SQLITE_DBFILENAME} ${SQLITE_DBFILENAME}
    sqlite3 home-assistant_v2.db .dump > ${DUMP_FILENAME}
    $SQLITE3_TO_MYSQL/$SQLITE3_TO_MYSQL ${DUMP_FILENAME} | gzip > $IMPORT_FILENAME
fi

#Stop the hass server process on your server.
# ssh ha-local ha core stop
# integrate the database
# update the configuration (uncomment line with the new db_url)

if [ 1 != "${DRY_RUN}" ] ; then
    ssh ha-local sed -i "'"'s/# \(db_url:.*ha_db_url\)/\1/;'"'" /config/configuration.yaml
fi

if [ 1 == "${DROP_DB}" ] ; then
    echo "DROP DB/GET TABLES"
    # shellcheck disable=SC2016
    TABLES=$("${MYSQL}" -u "${DBUSER}" -p"$DBPASS" -h "$DBHOST" -P "$DBPORT" "$DATABASE" \
            -e "SHOW TABLES" \
            | "$AWK" '{ print $1}' \
        | "$GREP" -v '^Tables' )

    echo "DROP DB/GOT TABLES $TABLES"

    for t in $TABLES
    do
        echo "DROP $t table from $DATABASE database..."
        "${MYSQL}" -u "${DBUSER}" -p"$DBPASS" -h "$DBHOST" -P "$DBPORT" "$DATABASE" \
            -e "SET FOREIGN_KEY_CHECKS=0;drop table $t;"
    done
fi

gunzip -c "$IMPORT_FILENAME" | "${MYSQL}" --default-character-set utf8mb4 -u "${DBUSER}" -p"$DBPASS" -h "$DBHOST" -P "$DBPORT" "$DATABASE"

I didn't use your script directly, but it served well as a blueprint!
Just one addition though: I had emojis 🙌 as part of the migrated data, which led to an error:

ERROR 1366 (22007) at line 2235: Incorrect string value: '\xF0\x9F\x99\x8C`,...' for column `homeassistant`.`state_attributes`.`shared_attrs` at row 1

Changing the last line like this:

gunzip -c $IMPORT_FILENAME | ${MYSQL} --default-character-set utf8mb4 -u ${DBUSER} -p"$DBPASS" -h $DBHOST -P $DBPORT $DATABASE

fixed it

@oliverrahner Great, thanks for sharing back!
The whole idea of my gists is to try to extend the benefit of my efforts ;-).

I've updated the script and also the pre-commit setup while I was at it.

Hi,

I get : ERROR 1005 (HY000) at line 1: Can't create table homeassistant.statistics (errno: 150 "Foreign key constraint is incorrectly formed")

using: cat home-assistant_v2.import.sql | mysql --default-character-set utf8mb4 -h 192.168.x.xxx -u root -ppassword homeassistant

any fix on this I can do?

I am a bit to busy to reproduce it (and maybe I do not have data that allows that).
Apparently the issue happens for the reported table, so you can look in the sql file to find the CREATE TABLE statement for statistics and check how foreign keys are set. Generally they are set in separate statements, but the does not seem to be the case here.

The sqlite3-to-mysql may need some finetuning - that repo has not been updated since 7 years. I just fork it so that any changes can be pushed there. If there are any, I'll update my fork and reference my fork in the script;