How to convert Home Assistant database to MariaDB

This video is a tutorial on how to convert the default Home Assistant database to MariaDB.

Here are a few links to get you started…..

As always, make sure your system is up to date…

sudo apt-get update
sudo apt-get upgrade

Lets start installing everything…..

sudo apt-get install software-properties-common

sudo apt-key adv –recv-keys –keyserver hkp://keyserver.ubuntu.com:80 0xF1656F24C74CD1D8

sudo add-apt-repository ‘deb [arch=amd64,i386,ppc64el] xenial main’

Now lets run update again and then install MariaDB…

sudo apt-get update
sudo apt-get install mariadb-server

It will prompt you for a default admin password to use with MariaDB

Once thats done, lets install a few more pieces needed for this to work with HA….

sudo apt-get install libmariadb-client-lgpl-dev libssl-dev
pip3 install mysqlclient

In order to convert the existing db over we need to install sqlite3

sudo apt-get install sqlite3

Now we are ready to stop Home Assistant to start working with the db…
sudo systemctl stop home-assistant.service

First we need to do a dump of the db……

sqlite3 home-assistant_v2.db .dump (ANGLE BRACKET) sqlite3_dbfile_.dump.sql

Now we need to download the sqlite to mysql converter….

git clone
cd sqlite3-to-mysql/
sudo ./sqlite3-to-mysql ../sqlite3_dbfile_.dump.sql (ANGLE BRACKET) ../mysql_importme.sql

Now we are ready to create the db in Mariadb…

mysql -u -root -p
create database homeassistant;

grant all privileges on homeassistant.* to ‘hass’ identified by ‘PASSWORD’
exit

Now we are ready to import the converted db into our newly created db…..

mysql -u hass -p -h localhost homeassistant (ANGLE BRACKET) mysql_importme.sql

Once that is done importing, we need to make a few tweaks….

mysql -u hass -p
use homeassistant
select max(run_id) from recorder_runs;
(IT SHOULD RETURN A NUMBER)

alter table recorder_runs modify column run_id INT NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=106;(NUMBER RETURNED PREVIOUSLY **INCREASED BY 1)

alter table states drop foreign key states_ibfk_1;

select max(event_id) from events;
(IT SHOULD RETURN A NUMBER)

alter table events modify column event_id int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=183522; (NUMBER RETURNED PREVIOUSLY **INCREASED BY 1)

Now we can update configuration.yaml file…..

recorder:
db_url: mysql://hass:PASSWORD@localhost/homeassistant

Now that all the changes have been made, start HA back up….

sudo systemctl start home-assistant.service

That should be it, you should now be up and running on the mariadb database.

If you like the video, please subscribe to my channel. If you have any questions, hit me up in the comments below. Also, if there are any videos you would like me to put out here, let me know as well.

Nguồn:https://hdwallpaperslovely.com/