Connecting to MariaDB "docker"

HansRemmerswaal · February 1, 2023, 10:32am

I installed the Home Assistant Operating System on my Intel NUC, the one where I exclusively run Home Assistant Operating System. After the installation all worked fine.

Somewhere I read that with such installation on the background dockers are used. Is there somewhere some more documentation around this? How to connect to such docker?

Reason for asking;
I want do to a mass load of data in the MariaDB and for that I want to do this via command prompt. If my understanding is correct then the MariaDB is automatically installed in such a docker and somehow I need to access that “MariaDB” docker to get to the command prompt.

tmjpugh · February 1, 2023, 3:28pm

I dont see info on external connections in db docs but you can look there

There is info on external connections here

HansRemmerswaal · February 1, 2023, 5:22pm

I’m able to connect to the database via phpMyAdmin in HA and via HeidiSQL, that is not the problem.

But I want to move from SQLite to MariaDB, the data from SQLite I have exported but importing it via phpMyAdmin is not working due to the size. And via HeidiSQL I worked but took many days only to find out that I had to redo.

So I want to move the data to a file on my NUC and do the load directly via a local import in MariaDB. And for that it seems I need to have access to the command interface of MariaDB. And that one I can’t find.

mterry63 · February 1, 2023, 5:48pm

Assuming you have configured command line access to your HASS server, and you are comfortable using the linux command line you can access MariaDB like this:

sudo docker exec -i -t addon_core_mariadb bash

This will put you in the bash shell of the docker container you referenced above. From there just run mariadb:

mariadb
use homeassistant

If you use the Studio Code Server Add-on, it gives terminal access to Home Assistant and can be leveraged for the above commands, as well as supports a free MySQL extension that allows direct access to MariaDB from Visual Studio Code.

jammin · February 2, 2023, 12:24am

Thanks for this info.

I have the visual studio code extension and can start a terminal, but aren’t able to run any of the commands above.

Are there any additional steps required?

mterry63 · February 2, 2023, 12:38am

Sorry, I mispoke. You have to run those command outside of the docker container you are in using Studio Code Server, e.g. from the host OS. Have you setup SSH to the host itself?

CentralCommand · February 2, 2023, 12:39am

You can’t do this from the Studio Code server addon for multiple reasons:

It does not have nor need sudo (the addon runs as root within the container)
It does not have docker access

Unless the user above has made their own version of Studio Code Server with docker accesss, I’m not sure how they’re able to do that.

If you install the SSH & Web Terminal addon from community addons (not the one in official addons called Terminal & SSH) then that can be given docker access by turning off protection mode. Alternatively that addon has the mysql package preinstalled so you can do the import that way via CLI.

jammin · February 2, 2023, 1:50am

Awesome thank you, this is so much more useful that the standard SSH add-on, I didn’t know it existed.

HansRemmerswaal · February 2, 2023, 4:12pm

sudo command doesn’t seem to exist on the Home Assistant Operating System

I did the following installation:

Home Assistant Operating System: Minimal Operating System optimized to power Home Assistant. It comes with Supervisor to manage Home Assistant Core and Add-ons. Recommended installation method.

mterry63 · February 2, 2023, 4:38pm

Yes, that was noted above. You don’t need to use the sudo command in your instance. Drop that word and use the rest of the command.

I’m running Supervised on Debian 11 and SSH as a non-root user, so I shouldn’t have cut and pasted the command from my notes.

See @CentralCommand notes from above. Make sure protection mode is off in the SSH & Web Terminal addon.

CentralCommand · February 2, 2023, 4:46pm

You’re also using the wrong SSH add-on if you’re trying to use the docker CLI

Alternatively if you just want to use the mysql CLI to do the import you can do that with either SSH add-on. The community one comes with that package pre-installed. It can be added to the official SSH add-on (the one you’re currently using) with apk add mysql

HansRemmerswaal · February 2, 2023, 4:53pm

Almost there… so how do I start the CLI from here? Normally it should be something like…

mysql --user=user_name --password=your_password db_name

but it doesn’t recognize yet the mysql

HansRemmerswaal · February 2, 2023, 5:05pm

Getting closer
First time using linux…

CentralCommand · February 2, 2023, 5:11pm

I’m sorry my bad, I forgot two things:

mysql is the server, mysql-client is the CLI for talking to a server
For mariadb it works best to use their specific package, mariadb-client

Yes from the core ssh addon you have to do the connection details from scratch. The community addon has all this pre-configured, it does it here:

github.com

hassio-addons/addon-ssh/blob/afb2d82712d8537959dd22a24fd605b8e355e6c6/ssh/rootfs/etc/s6-overlay/s6-rc.d/init-mysql/run

#!/command/with-contenv bashio
# shellcheck shell=bash
# ==============================================================================
# Home Assistant Community Add-on: SSH & Web Terminal
# Pre-configures the MySQL clients, if the service is available
# ==============================================================================
declare host
declare password
declare port
declare username

if bashio::services.available "mysql"; then
  host=$(bashio::services "mysql" "host")
  password=$(bashio::services "mysql" "password")
  port=$(bashio::services "mysql" "port")
  username=$(bashio::services "mysql" "username")

  mkdir -p /root/.config
  {
    echo "[client]"

This file has been truncated. show original

So you’ll need to set up the cnf file in a similar way.

Or again, just use the community ssh addon as it comes pre-configured.

HansRemmerswaal · February 2, 2023, 5:36pm

Then better go for the community addon one. For some reason its stopping each time, but after changing the port from 22 to 23 it worked.

Thanks for your help! Now I can finally start loading my huge export from SQLite.

CentralCommand · February 2, 2023, 5:47pm

Do you still have the other ssh addon installed and running? If so then I assume its already bound to port 22 on the host, can’t have two containers bound to the same port.

HansRemmerswaal · February 2, 2023, 6:30pm

That was indeed the issue. Now back on port 22.