Large homeassistant database files

I use WinSCP myself. That or the configurator editor that @danielperna84 created.

Downloaded WinSCP and tried. I dont understand.

Got it. I my permissions were not letting me see it.

1 Like

Sorry for the noob question but how did you fix the permissions?

I’m having the same issue:

pi@raspberrypi:~ $ cd /var/lib/mysql/
-bash: cd: /var/lib/mysql/: Permission denied

I used the:

sudo chmod -R ugo+rw /var/lib/mysql/

Just a follow up on this post:

So I finally got up the nerve to test out the fix for the large ibdata1 file size and moving to innodb_file_per_table, which is now the default for new MySQL installations.

I went back and first re-read this article a couple of times to make myself comfortable:

I followed EVERY instruction to the letter including doing a cold backup of the /var/lib/mysql dir and then db exports.

Backups and exports took a bit of time due to the file sizes involved; my ibdata1 file was well over 3.5gb. It was a little tense at first as I worried about dropping the HASS table even though I knew I had a few backups. But all in all it went quite smoothly and my ibdata1 file is a fraction of it’s former size and the performance on queries seems to be improved as well.

One thing I will say is in the restore directions, he has the following command:

Import all the database from the mysqldump backup that we took earlier.

mysql -u root -ptmppassword --all-databases < /backup/all-database.sql

And I found it had to be

mysql -u root -ptmppassword < /backup/all-database.sql

for it to work. Also, if it’s not clear; -ptmpassword option is -p followed by your password for the mysql user running the commands.

All in all I’m quite happy with the results as well as the 3gb + space savings!

I did this now because 0.42 introduces some changes to the db structure; specifically the indexing and I wanted to make the changes ahead of that update to take advantage of the structural improvements before updating.

If that help I’ve just setup a MySQL docker for my home-assistant.

The docker run command to create/start the MySQL container:

docker run \
--name mysql-hass \
-e MYSQL_DATABASE=hass_db \
-e MYSQL_USER=hassuser \
-e TZ=Europe/Berlin \
-p 3306:3306 \
-v /etc/localtime:/etc/localtime:ro \
-d mysql:8.0 --bind-address=

And the recorder in HASS configuration.yaml

  purge_days: 7
  db_url: mysql://hassuser:[email protected]/hass_db

Change 12345 with the password you’d like to use.

Home-Assistant also run in a Docker container with network --net=host, it should also work with a standard hass instance as long as they run on the same machine.


@touliloup Where are you running the Docker container? I tried installing the MySQL docker container on Synology NAS and was not quite successful.

@arsaboo on the same machine as Home-Assistant, an HP Gen8 running under CentOs 7.0.

What doesn’t work on you NAS? Does docker run correctly? Does the container start?

This is from my home assistant notes on when I setup MySQL on my synology nas:

I’ve installed a docker mysql container instead of relying on Home Assistant’s built in. I pulled sameersbn/mysql via ssh and used the line below to initialise:

  • docker run --net=host --restart=always --name mysql -d -v /volume1/docker/mysql/data:/var/lib/mysql -e 'DB_USER=hass' -e 'DB_PASS=12345' -e 'DB_NAME=homeassistant' sameersbn/MySQL

Then adding the following to my config yaml file:

`-	recorder:

purge_days: 6
db_url: ‘mysql://hass:[email protected]/homeassistant?charset=utf8’`

Looks like this is what I am looking for, but I get this error:

ash-4.3# docker run --net=host --restart=always --name mysql -d -v /volume1/docker/mysql/data:/var/lib/mysql -e 'DB_USER=hass' -e 'DB_PASS=12345' -e 'DB_NAME=homeassistant' sameersbn/MySQL
docker: Error parsing reference: "sameersbn/MySQL" is not a valid repository/tag

BTW, I downloaded the container from Docker GUI.

Here’s what worked for me (hopefully, it can help others):

  1. SSH into Synology NAS and pull the image using docker pull sameersbn/mysql:latest. After you login as admin, you may have to run sudo su if you keep getting permission errors.
  2. Make sure that the folder /volume1/docker/mysql/data exists (or create one, if it does not).
  3. Run, the command docker run --net=host --restart=always --name mysql -d -v /volume1/docker/mysql/data:/var/lib/mysql -e 'DB_USER=hass' -e 'DB_PASS=12345' -e 'DB_NAME=homeassistant' sameersbn/mysql:latest
  4. Add the following to the config (where is the IP address of my NAS):
  purge_days: 3
  db_url: mysql://hass:[email protected]/homeassistant?charset=utf8

Hi. How is your DB? Is inno_db file reduces after purges?

I haven’t really been checking that file specifically but it is staying around 200mb which is a major improvement over the 3gb it used to be. And I’m definitely happy with the performance improvement.

1 Like

How did you do that? Mine is very large and slow to call up in HA.

You could add that to your config:

  purge_days: 3

So that it only keep the data from the last 3 days.

Or you can exclude certain domains or sensors from being tracked. Do you need to keep track of time or date sensors? How about the sun sensor? Things like this add a LOT of useless data you’ll never track as well as the overhead penalties of poor performance.

That, plus moving to innodb_file_per_table helped enormously and both things were fairly easy to implement even with limited MySQL skills.

I got some alarms from unraid today and shows my log and db blowing up. I’ve added purge after 90 days to the config. I see that the db is storing the historal component, but how did the log file grow to be 12 gigs???

12G	/mnt/cache/appdata/home-assistant/home-assistant.log
3.9G	/mnt/cache/appdata/home-assistant/home-assistant_v2.db

I don’t think that home-assistant log have a maximal size or conservation period.
They’ll grow forever.
My home-assistant run in a docker container, it’s easy to limit the log size if you’re running in a docker container by using:

 --log-opt max-size=10m

On a HASSBIAN installation I followed the following instructions

changing the recorder to

  purge_days: 1
  db_url: mysql://hassuser:[email protected]/db_name?charset=utf8

I receive the following error

17-05-19 16:22:41 ERROR (Recorder) [homeassistant.components.recorder] Error during connection setup: (_mysql_exceptions.OperationalError) (1049, “Unknown database ‘db_name’”) (retrying in 3 seconds)