Fixing Home Assistant DataBase Problems With MariaDB Addon

In this video I replace my overloaded Home Assistant Internal Database (SQLite) with MariaDB. MariaDB is an implementation of MySQL that can handle a lot more data a lot more efficiently than SQLite.

If you are having unknown slowdowns with your Home Assistant Instance, or if you are having the display of sensors disappear, this may well be the fix for you.

A good way to tell is for you to look at your ‘home-assistant_v2. db’ file. If it is over 800mb, you are a candidate to replace the internal database with MariaDB Addon or some other external database.

Update as of Home Assistant 2021.7 version. If using @ inside your db_url as I did below, the @ sign now needs to be represented as %40 . See HA Documentation for the breaking change related to recorder: [

If you have mariadb or an outside the standard database in use for recorder, there is a breaking change regarding the access string. ‘@’ needs to be replaced with ‘%40’. (See the recorder breaking change here:)[https://www.home-assistant.io/blog/2021/07/07/release-20217/#breaking-changes]

Episode: 26

Related website for this video: https://www.whatarewefixing.today/?p=240

Video related links:
UUID Generator website to generate a password:
https://www.uuidgenerator.net/
Home Assistant Recorder Integration Page:
https://www.home-assistant.io/integrations/recorder/

If you are interested in saving/exporting the SqLite data, here’s a Tutorial on how to do it.
https://www.srm.im/2017/06/11/home-assistant-migration-from-sqlite3-to-mariadb-mysql/

MariaDB Addon Configuration:

databases:
  - homeassistant
logins:
  - username: loginHere
    host: homeassistant
    password: yourPasswordHere  # I suggest using UUID number here for password
rights:
  - username: borusa
    host: homeassistant
    database: homeassistant
    grant: ALL PRIVILEGES ON

Home Assistant Recorder: Integration Configuration:

recorder:
  db_url: mysql://loginHere:yourPasswordHere%40core-mariadb/homeassistant?charset=utf8
  #  The below is the old format that was allowed, but is now depticated.  See above...
  # db_url: mysql://loginHere:yourPasswordHere@core-mariadb/homeassistant?charset=utf8
  purge_keep_days: 2
  purge_interval: 1
              # Everything is included by default.  
              # Ensure you are keeping only what you need to keep DB small
  include:    # Include everything you graph and will want to see later
    domains:
      - sensor
      - binary_sensor
      - switch
      - light
              # Everything is included by default.  
  exclude:    # Eliminate anything that you never graph or refer back to
    domains:
      - automation
      - updater
    entities:
      - sun.sun
      - sensor.time
      - sensor.date_time_iso # Don't record date and time
      - sensor.yr_symbol # Ignore this as well
      - group.weather # Don't save weather 

Contact Links:
What are we Fixing Today Homepage / Website:
https://www.WhatAreWeFixing.Today/
Channel Link URL: (WhatAreWeFixingToday)
https://bit.ly/WhatAreWeFixingTodaysYT
What are we Fixing Today Facebook page (Sir GoodEnough):
https://bit.ly/WhatAreWeFixingTodaybFB
What are we Fixing Today Twitter Account (Sir GoodEnough):
https://bit.ly/WhatAreWeFixingTodayTW
Discord Account: (Sir_Goodenough#9683)
https://discord.gg/Uhmhu3B

Please help support the channel:

Patreon: https://www.patreon.com/WhatAreWeFixingToday

Buy me Coffee: https://www.buymeacoffee.com/SirGoodenough
PayPal one-off donation link: https://www.paypal.me/SirGoodenough
Cash App $CASHTAG: https://cash.me/$SirGoodenough
Venmo cash link: https://venmo.com/SirGoodenough

If you would like to donate anything to this channel, please use this address:
C/O: Sirius GoodEnough
322 Buena Vista Ave.
Department: DYT
Waukesha, Wisconsin, 53188-3602

Link to this video on YouTube:
https://youtu.be/O9eHM3LiXCo