Thanks. If I install mariadB via the synology packages and i browse to 192.168.1.111:3306, it will show a webpage with some info on it. I naively thought installing mariadB via docker will be the same.
I will install phymyadmin to see if It can connect to the mariadB.
from searching the web, it seems phpmyadmin is widely used so I thought I will give that a go. But thanks for your suggestion I will also explore mysql client.
If all you are doing is connecting to home assistant there are only half a dozen commands ot type (if that). Don’t fear the CLI
Thanks for the prompt, I have been meaning to move to a proper database for a while, and have bugger all else to do in the lockdown [1], and what would Saturday be without breaking HA.
[1] OK eat too much, drink too much, binge watch TV, binge read and play with HA.
I am going to document this before I forget what I did.
My setup
nuc running ubuntu 18.04
home assistant core 0.107.7 via docker.
portainer 12.
Aim
move from sqlite to a proper database using MariaDB
Important to know:
MariaDB is a “drop in” replacement for the older MySQL project. That is why you will see references to mysql below.
Backup your existing database
The number of times I see “just delete your database file and start again” seems to show that people don’t much value their data in home assistant. Kinda counter-intuitive. Nevertheless if you want to do so, simply copy the home-assistant_v2.db somewhere else.
Deploy MariaDB in Docker via Portainer
In portainer navigate to App Templates, scroll down to MariaDB and click it.
In the next page leave everything as default except fill in root password. Fill that in with something you can remember. Important this becomes the password for the root MariaDB user, it has nothing to do with the root system user of your computer.
Click “Deploy the Container”.
MariaDB should now be running in a container. It uses a bridged network and it’s IP address will be reported by portainer. Mine is 172.17.0.2.
Setting up MariaDB
SSH into your operating system, ie in my case ubuntu 18.04.
Make sure you have the mysql client installed
sudo apt install mysql-client
Connect to mysql
mysql -h 172.17.0.2 -u root -p
You will be prompted for that root database password you set up when you deployed MariaDB in portainer. Your prompt should change to mysql>. You are now the root (or master) user of MariaDB.
You now need to set up a user to access MariaDB from home assistant, a database for home assistant to use, and some passwords/permissions. The exact name of the user and the name of the database can be anything, but I used stuff that was easy to type. Don’t forget the semicolon at the end of each line when using mysql/MariaDB. Also the single quotes are important.
mysql> CREATE DATABASE hadb;
mysql> CREATE USER 'ha' IDENTIFIED BY 'some_password';
mysql> GRANT ALL PRIVILEGES ON hadb.* TO 'ha';
mysql> FLUSH PRIVILEGES;
mysql> type ctrl-d or exit
Now we need to configure home assistant to use the MariaDB database instead of sqlite. Edit your configuration.yaml to include the following:
Is there a way to link to the container rather than using the IP address? I am of the understanding that this IP address may be changed by docker at any time?
p.s. By the way I follow your method above to specify the IP address so far things seem ok but on the log file I am seeing this:
A mariadb error won’t be coming off your router. This Setup of recorder is taking over 10 seconds. does not come from your router, it comes from home assistant.
FYI, I’ve discovered my “Aborted Connection” was related to my UDM (Ultimate Dream Machine) “Endpoint Scanner”, part of Unifi’s Threat Management system that looks for suspicious open ports.
I have been meaning to come back to this thread to point out that when I reboot that machine, the mariadb docker container gets a different IP address and I need to xhange the secrets file. I am phasing out that machine in favour of a supervised setup. I also rarely reboot it.
