Hassbian and MariaDB

Configuration
1

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:password@core-mariadb/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
2

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:password@localhost/homeassistant
3

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

4

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.

6 Likes
5

thank you. much appreciated

6

Yes, I am on stretch as well

7

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

8

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

9

thank you. I’ve installed it, created user as homeassistantuser. with this
db_url: mysql://homeassistantuser:password@localhost/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?

10

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

1 Like
11

Ok good to know and congrats!

12

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:password@localhost/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