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.