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/

Related Posts

ADB Version Mismatch Error Solution in Genymotion – www.nestedif.com

February 10, 2020

February 10, 2020 3

adb server version (32) doesn’t match this client (36); killing… error: could not install *smartsocket* listener: Address already in use...

How To Install ADB & Fastboot in Windows with 15 Seconds ADB Installer

February 19, 2020

February 19, 2020 1

If you want to flash your Android smartphone or even want to root it. You need ADB (Android Development Bridge)...

[Hướng dẫn] Bài 1: Làm quen giao diện Adobe Premiere Pro CC 2015

May 17, 2020

May 17, 2020 38

Footage: Series hướng dẫn After Effect CC 2015 cơ bản đến nâng cao: Series hướng dẫn Premiere Pro CC 2015...

How to unlock an Android phone with a broken touch screen

March 2, 2020

March 2, 2020 20

Got an Android phone with a cracked screen? Can’t unlock the phone to download your photos and other data? If...

Cách Download Và Cài Đặt Adobe Premiere Pro CC 2020 Full Crack

March 8, 2020

March 8, 2020 38

▲Nếu các bạn thấy video này hay thì hãy bấm nút like và subcribe. Đừng quên share video này cho...

Thử mua ĐỐNG ĐỒ 0đ (Free), 1k, Sale 50% trên Shopee. Hướng dẫn săn hàng 1000đ | MUA HÀNG ONLINE

February 8, 2020

February 8, 2020 39

Mình có săn được đống đồ miễn phí và sale sốc 50% trên Shopee. Hôm nay mình sẽ mở hộp...

How to Install and Setup ADB Drivers in Windows 10

March 28, 2020

March 28, 2020 21

In this video, you will see how to Install and Setup ADB [Android Debug Bridge ] Drivers in Windows 10....

How To Install ADB & Fastboot in Mac OS X & Linux in Under 2 Minutes

February 26, 2020

February 26, 2020 1

Today you’ll learn how to install ADB & Fastboot in Mac or Linux in Under 2 minutes Commands: Install Command:...

100% Tested! Tutorial UBL Unlock Bootloader Semua Xiaomi | Tested Xiaomi Redmi Note 3 Pro

February 16, 2020

February 16, 2020 49

Jangan lupa kalau mau unlock bootloader,kondisi laptop/komputernya harus dalam keadaan terkoneksi internet Cara unlockbootloader xiaomi ini sangat mudah sekali ya...

Hướng dẫn cấu hình Gmail trên Outlook 2013, 2010, 2007

March 21, 2020

March 21, 2020 35

Hướng dẫn cấu hình đồng bộ Gmail trên Outlook trong Outlook 2007, Outlook 2010 và Outlook 2013, cài đặt Gmail...

Adobe Audition CC 2020 Crack Google Drive 🎼

May 4, 2020

May 4, 2020 1

Adobe Audition CC 2020 Crack Google Drive 🎼 * Crack only : or Adobe Audition CC 2020 12.0.0 32bit +...

OCZ ARC 100 240gb SSD Overview and Benchmarks

February 10, 2020

February 10, 2020 9

While the OCZ ARC 100 240gb fits firmly into mainstream pricing, the feature set looks far more enthusiast aimed. The...

How To Fix Ram Memory Problem and relocate full size of Ram

February 13, 2020

February 13, 2020 1

How To Fix Ram Memory Problem Earn Money Refer Link- CLIXSENCE REFER LINK– PLEASE SUBSCRIBE MY CHANNEL. PLEASE SHARE MY...

Hướng dẫn cách cài đặt gmail vào outlook 2007 2010 2013

April 6, 2020

April 6, 2020 3

Hướng dẫn cách cài đặt gmail vào outlook 2007 2010 2013 Để cài căt gmail vào outlook các bạn cần...

Gcam vs Stock Camera ft. Asus Zenfone max pro M2 | Gcam installation Gcam Night Sight

March 3, 2020

March 3, 2020 30

Gcam vs Stock Camera ft. Asus Zenfone max pro M2 Gcam installation Gcam Night Sight How To Install Google Camera...

Comments
  • greetings, It looks mostly good after performing the update, and home assistant does indeed connect. I did find a problem though with JSON Decoder errors indicating that for example, "Expecting property name enclosed in double quotes" . I noticed that the shell script sqlite3-to-mysql converts all double quotes to backquotes(`) for the conversion and the data in the tables events and states reflect this. I am thinking that to fix this, there needs to be something like the following to fix it:
    update events set event_data = REPLACE(event_data, '`', '"');
    update states set attributes = REPLACE(attributes, '`', '"');

    UPDATE:

    I found that the index current value needs to also be set for the states table. I needed to add the following to my instance:

    elect max(state_id) from states; (IT SHOULD RETURN A NUMBER)
    alter table states modify column state_id int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=183522; (NUMBER RETURNED PREVIOUSLY **INCREASED BY 1)

     would you want to add this to the instructions above?

    Regards

  • How can maintain the ON and OFF switch status in Hassbian? Once I restart my raspberry pi 3 then I lost all the lights / switch status. Is there any way to keep in as it was before restarting?

  • You didn't add the foreign key back. I see there is a typo in the original article, should be:

    ALTER TABLE states ADD CONSTRAINT states_ibfk_1 FOREIGN KEY (event_id) REFERENCES events (event_id);

  • Im having this error after the database is implemented "Error converting row to state: <homeassistant.components.recorder" any idea why?

  • Great video
    On hassbian (Strech) got following error:
    gpg: failed to start the dirmngr '/usr/bin/dirmngr': No such file or directory
    gpg: connecting dirmngr at '/tmp/apt-key-gpghome.wfF9asVRTy/S.dirmngr' failed: No such file or directory
    gpg: keyserver receive failed: No dirmngr

    Fixed it with:
    sudo apt remove gnupg
    sudo apt install –reinstall gnupg2
    sudo apt install dirmngr

  • Adrian, as always great tutorial. What are the advantages of using Maiadb (Mysql or Percona) , vs sqllite. Is there a significant advantage to doing the migration.

    As far as Im concerned, you can say , "Cause I say SO" and that is good enough for me.
    Thanks,
    LL

  • I looked over the documentation for MariaDB after watching your video. Would this allow HA to be configured for multiple logins for different users and have the UI customized per user? Or at least hide some things from a guest user? – and maybe a follow up tutorial to show how?

  • hey man thanks for the video, at the start of your videos can you give a brief explanation of what are the advantages of doing this and why somebody would do it? thanks again

Leave a Reply

Your email address will not be published. Required fields are marked *