Hello @rpitera. What is the benefit to split one file on many. It’s not clear will these files reduce their size after data deleting. I guess not.
I’m still researching the answer to that myself. The dump and restore has it’s obvious advantage of reclaiming the space but I’m still trying to figure out what the advantage is going forward.
Initial research has led me to this in the MySQL docs, which does seem to list some clear advantages to this approach:
https://dev.mysql.com/doc/refman/5.5/en/innodb-multiple-tablespaces.html
To be clear, I am not doing anything until I read enough to feel comfortable in doing this and would probably do a full image backup before attempting anything. I’d strongly suggest others do the same.
FWIW, MySQL 5.6 enables innodb_file_per_table by default.
I have nothing in that dir. Hmmm
At all? You should at least see the db dirs. Maybe you installed it to another directory?
Here’s what mine looks like:
Yes I have nothing in there.
I did install Hass in Virtualenv maybe I did the same with mysql.
Is there a way to find it?
find / -iname '*ibdata1*'
Probably want to run that as:
sudo find / -iname '*ibdata1*'
Ah yes its there but I can not see it with Bitvise browser.
I knew it had to be; otherwise you’d be having some issues! 
Haha.
Just setting up samba to see if that will show me the contents of the folder.
Nope. Cant see it there either.
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 deniedI 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_ROOT_PASSWORD=12345 \
-e MYSQL_DATABASE=hass_db \
-e MYSQL_USER=hassuser \
-e MYSQL_PASSWORD=12345 \
-e TZ=Europe/Berlin \
-p 3306:3306 \
-v /etc/localtime:/etc/localtime:ro \
-d mysql:8.0 --bind-address=0.0.0.0
And the recorder in HASS configuration.yaml
recorder:
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.
4 Likes
@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?