MariaDB config inside it's own docker container - getting homeassistant docker connected

I’m fairly new to docker and having difficulty wrapping my head around how to get my homeassistant docker connected to a mariadb docker.

The mariadb docker is pulled by:

sudo docker pull mariadb

The mariadb docker instance has BRIDGE Networking with IP = 172.17.0.3 is started by:

sudo docker run --name homeassistantdb -e MYSQL_ROOT_PASSWORD=‘XXXXX’ -v /home/homeassist/mysql:/data -d mariadb:latest

(the volume would be where I want to eventually store the DB, but I’m not “there” yet so it can be ignored)

The home-assistant docket has HOST networking and is started by:

sudo docker run -d --name="home-assistant" -v /home/homeassist:/config -v /etc/localtime:/etc/localtime:ro --device=/dev/ttyACM0:/zwaveusbstick:rwm --net=host homeassistant/home-assistant:rc

configuration.yaml has:

recorder:
db_url: mysql://homeassistantantdb:‘XXXXX’@172.17.0.3/homeassistant

Bashed into mariadb docker to create SQL database using:

CREATE DATABASE homeassistant;
CREATE USER homeassistantdb IDENTIFIED BY 'XXXXX;
GRANT ALL PRIVILEGES ON homeassistant.* TO homeassistantdb;
FLUSH PRIVILEGES;

I am getting the following error in home-assistant.log:

2018-10-23 17:20:59 ERROR (Recorder) [homeassistant.components.recorder] Error during connection setup: (_mysql_exceptions.OperationalError) (1045, “Access denied for user ‘homeassistantantdb’@‘172.17.0.1’ (using password: YES)”) (Background on this error at: Error Messages — SQLAlchemy 2.0 Documentation) (retrying in 3 seconds)

What is really confusing me is why my Docker Bridge networking GATEWAY ip is showing up in the error log and not IP I am assigning in the db_url of the configuration.yaml file …

Any help on what I did wrong or what I need to do to configure this properly would be greatly appreciated. I am trying to add mariadb into my configuration in order to increase the responsiveness of the Logbook UI.

Just throwing out ideas here but could the homeassistant/home-assistant:rc docker image not contain a mysql client installation?

When I bash into the homeassistant docker container and run:

mysql -u root -p

I get:

bash: mysql: command not found

You created a user ‘homeassistant’, but connect with user ‘homeassistantantdb’?

Sorry my SQL USER creation code was a typo - it should have shown “homeassistantdb” . Here are the users from the DB:

MariaDB [(none)]> SELECT User FROM mysql.user;
±----------------------+
| User |
±----------------------+
| homeassistantdb |
| root |
| root |
±----------------------+
3 rows in set (0.000 sec)

Here are the databases:

±-------------------+
| Database |
±-------------------+
| homeassistant |
| information_schema |
| mysql |
| performance_schema |
±-------------------+

Does anybody have any more suggestions?

Please LMK and thanks!

install mysql client outside docker.

also no single quotes on password:

db_url: mysql://homeassistantantdb:[email protected]/homeassistant

Also try localhost since it’s bridged:

db_url: mysql://homeassistantantdb:XXXXX@localhost/homeassistant

How do I install mysql client onto a docker image without having to reinstall it every time I pull the latest RC?

Is there a way to start the start the HA Docker to have access to the hosts machine’s mySQL client?

No, you don’t have to install anything inside or outside of your Home assistant container.

This is my recorder definition:

recorder:
  purge_keep_days: 5
  purge_interval: 1
  db_url: mysql://user:[email protected]:3306/HASS?charset=utf8

As you can see, I’m using the host IP instead of the container IP as the latter can change when pulling and starting a new version. (My database is called hass)

These are my Docker compose files:

/opt/mariadb$ cat docker-compose.yml
version: '3.1'
services:
  db:
    image: mariadb
    restart: always
    volumes:
      - /opt/mariadb:/etc/mysql/conf.d
      - /opt/mariadb:/var/lib/mysql
    ports:
      - 3306:3306
    environment:
      - TZ=Europe/Stockholm
      - DB_HOST=http://localhost/
      - PGID=1000
      - PUID=1000
/opt/homeassistant$ cat docker-compose.yml         version: '3'
services:
  homeassistant:
    container_name: home-assistant
    image: homeassistant/home-assistant:latest
    volumes:
      - /opt/homeassistant:/config
      - /etc/localtime:/etc/localtime:ro
    devices:
      - /dev/ttyACM0:/dev/ttyACM0
      - /dev/ttyUSB0:/dev/ttyUSB0
    restart: always
    network_mode: host
2 Likes

Thank you. I will make the changes tonight.

Pardon the simple question, but are there instructions anywhere about how to create the database itself, or does your docker-compose file accomplish that without needing to create the DB through SQL code?

There are many guides on the web on how to create an SQL database but I don’t know of any that is specific to Home Assistant. That said, you’ll find most of the necessary commands in the first posts of this thread.

Nothing is done in the docker-compose file. On the first run you need to create a root user, that’s why you need to supply a root password in the docker run command (see post #1). Once the root user is created the password is not used anymore so it’s not needed in the compose file.

Very helpful, thank you. I didn’t understand that you needed to run those commands first prior to putting it into your docker-compose file. I’m still very much learning as I go.

For Raspberry Pi 2/3 I am using this image: https://hub.docker.com/r/yobasystems/alpine-mariadb

It is very light and starts up in seconds.

You can also set db name, user and pass on docker compose file and database is created at runtime if it doesn’t exists already already.

1 Like