MariaDB on docker (not addon)

Configuration
1

Hi all,

I tried without success to setup MariaDB as my db for HA.

This is what I’ve done (using this link):

Create a DB in MARIADB using console:

mysql -u root -p
CREATE DATABASE home_assistant;
CREATE USER ‘hass_user’ IDENTIFIED BY ‘xxxxxxxxxxxx’;
GRANT ALL PRIVILEGES ON home_assistant. TO ‘hass_user’@‘localhost’ IDENTIFIED BY ‘xxxxxxxxxxxxxx’;*
FLUSH PRIVILEGES;
exit

then added this in config:

recorder:
  purge_interval: 1
  db_url: !secret recorder_db_url
  purge_keep_days: 7
  include:
    domains:
    - sensor
    - climate
    - binary_sensor
    - light
    - timer
    # entities:
      # - sensor.consumo
  exclude:
    domains:
    - automation
    - script

where recorder_db_url is this:

recorder_db_url: ‘mysql://hass_user:[email protected]:3306/home_assistant?charset=utf8’

I checked for validation and reboot.

Record and other components (history, logbook) are no longer working.

This is the error in log:

Error during connection setup: (MySQLdb._exceptions.OperationalError) (1044, “Access denied for user ‘hass_user’@‘%’ to database ‘home_assistant’”) (Background on this error at: Error Messages — SQLAlchemy 2.0 Documentation) (retrying in 3 seconds)

Can someone please help me?

Thank you in advance!

2

Try

recorder_db_url: mysql://hass_user:[email protected]/home_assistant?charset=utf8

3

I tried to remove ’ ’ but nothing changed at next reboot.

4

Did you remove the :3306 too ?

5

Yes. And the error is the same in log.

6

One more thing to try:

GRANT ALL PRIVILEGES ON home_assistant.* TO ‘hass_user’;
FLUSH PRIVILEGES;

7

I can’t:

MariaDB [(none)]> GRANT ALL PRIVILEGES ON home_assistant.* TO ‘hass_user’;
ERROR 1133 (28000): Can’t find any matching row in the user table

8

Then try again

mysql> CREATE USER ‘ha’ IDENTIFIED BY ‘some_password’;
mysql> GRANT ALL PRIVILEGES ON home_assistant.* TO ‘ha’;
mysql> FLUSH PRIVILEGES;

and change your connectionstring accordingly.

9

Great! It worked!

Below full commands for the noobs like me out there:

mysql -u root -p
CREATE DATABASE home_assistant;
CREATE USER ‘ha’ IDENTIFIED BY ‘some_password’;
GRANT ALL PRIVILEGES ON home_assistant.* TO ‘ha’;
FLUSH PRIVILEGES;
exit

2 Likes