Hassbian and MariaDB

Good evening,

I am trying to setup MariaDB with Hassbian and am currently struggling with the configuration.

I installed MariaDb following this:

sudo curl -o /opt/hassbian/suites/install_mariadb.sh https://raw .githubusercontent.com/home-assistant/hassbian-scripts/dev/package/opt/hassbian/ suites/install_mariadb.sh && sudo hassbian-config install mariadb

The system indicates that:

No database or database user is created during this setup and will need to be created manually.

Consequently I followed these steps:

$ sudo mysql -u root -p
$ CREATE DATABASE homeassistant;
$ CREATE USER ‘homeassistantuser’ IDENTIFIED BY ‘password’;
$ GRANT ALL PRIVILEGES ON homeassistant.* TO ‘homeassistantuser’;
$ FLUSH PRIVILEGES; $ exit

And added following information in my configuration.yaml

recorder:
  purge_interval: 2
  purge_keep_days: 30
  db_url: mysql://homeassistantuser:[email protected]/homeassistant

But I get an error message that recorder is not working (obviously I adapted homeassistantuser and password to my liking).

Any idea what I did wrong?

1 Like

Ok,

I found my mistake - I had to replace “core-mariaDB” by “localhost”.

therefore the correct entry in configuration.yaml is the following:

recorder:
  purge_interval: 2
  purge_keep_days: 30
  db_url: mysql://homeassistantuser:[email protected]/homeassistant

Hi are you running hassbian stretch? Want to know if I can use your installation procedure

FWIW, I just got mariadb working in homeassistant on stretch. I didn’t use @digieurope’s curl request, but I did use his mysql configuration steps:

Here were my steps:

Logged in as homeassistant user AND activated virtualenv (for the pip3 installation):

  1. Back up database

    # "Backup" of database
    mkdir db-bkup
    mv home-assistant_v2.db db-bkup
    
  2. Add recorder configuration pre-emptively:

    # configuration.yaml
    recorder:
      purge_keep_days: 14
      purge_interval: 2
      db_url: !secret mariadb_connection_url
    
  3. Add connection url to secrets:

    # secrets.yaml
    mariadb_connection_url: mysql://<some-username>:<some-password>@localhost/homeassistant 
    
  4. Logged into ‘pi’ user

  5. Install mariadb

    sudo apt-get update
    sudo apt-get install mysql-server
    sudo apt-get install libmariadbclient-dev libssl-dev
    
  6. Log into mysql client (you’ll be prompted for password, I made one up):

    sudo mysql -u root -p
    
  7. Configure mysql database as per @digieurope’s instructions:

    MariaDB [(none)]> CREATE DATABASE homeassistant;
    MariaDB [(none)]> CREATE USER ‘<some_user>’ IDENTIFIED BY ‘<some_password>’;
    MariaDB [(none)]> GRANT ALL PRIVILEGES ON homeassistant.* TO ‘<some_user>’;
    MariaDB [(none)]> FLUSH PRIVILEGES;
    MariaDB [(none)]> exit
    
  8. Restart home assistant

Worked like a charm for me!

WARNING: I wrote out these steps from memory, and YMMV!! So be careful and back up anything you’re worried about losing.

5 Likes

thank you. much appreciated

Yes, I am on stretch as well

thank you very much. have you noticed a significant improvement in restart/speed?

Definitely faster than the standard mysql - and you avoid the issue of a bloating database.

thank you. I’ve installed it, created user as homeassistantuser. with this
db_url: mysql://homeassistantuser:[email protected]/homeassistant in config,

but getting this error
2018-01-17 16:50:41 ERROR (Recorder) [homeassistant.components.recorder] Error during connection setup: No module named ‘MySQLdb’ (retrying in 3 seconds)

in the localhost space do i put the rpi ip address?

ok, got it working, there’s a piece missing.
it needs to be installed in the virtual environment
after your bit

$ sudo systemctl stop [email protected]
$ sudo su -s /bin/bash homeassistant
$ source /srv/homeassistant/bin/activate
pip3 install mysqlclient
exit

then create the dbase.

thank you

Ok good to know and congrats!

Hi I’m trying to read the monthly consumption sensor on my POW sonoff with tasmota. I installed MariaDB add-on in HASSIO.
When I reboot, I can not see the sensor. Could you help me?
I attach the script and the sensor configuration
Thank’s!

{
“databases”: [
“homeassistant”
],
“logins”: [
{
“username”: “hass”,
“host”: “homeassistant”,
“password”: “mypassword”
},
{
“username”: “nico”,
“host”: “%”,
“password”: “testpassword”
}
],
“rights”: [
{
“username”: “nico”,
“host”: “homeassistant”,
“database”: “homeassistant”,
“grant”: “ALL PRIVILEGES ON”
},
{
“username”: “nico”,
“host”: “%”,
“database”: “homeassistant”,
“grant”: “ALL PRIVILEGES ON”
}
]
}

######################

  • platform: mqtt
    name: “Energy Yesterday”
    state_topic: “tele/sonoff_pow_1/SENSOR”
    value_template: ‘{{ value_json[“ENERGY”][“Yesterday”] }}’
    qos: 1
    unit_of_measurement : “kWh”

    sonoff_pow_1_yesterday_cost:
    friendly_name: Yesterday Cost
    entity_id: sensor.energy_yesterday
    unit_of_measurement: “€”
    value_template: >-
    kWh
    {{ states.sensor.energy_yesterday.state | multiply(0.1897) | round(2) }}

    sensor:

    • platform: sql
      mdb_url: mysql://nico:[email protected]/homeassistant
      queries:
      • name: Average Energy Consumption
        query: >
        SELECT ROUND(AVG(avg_per_day),2) ‘value’
        FROM (
        SELECT AVG(state) AS avg_per_day
        FROM homeassistant.states
        WHERE entity_id = ‘sensor.energy_yesterday’
        AND state != ‘unknown’
        AND state != ‘’
        AND created > NOW() - INTERVAL 1 MONTH
        homeassistant (created)
        ) avgs;
        column: ‘value’
        unit_of_measurement: kWh

###########################

config.yaml

config:
recorder:
purge_interval: 2
purge_keep_days: 30
mdb_url: !secret mariadb_connection_url