Haven’t actually done this, but I have migrated to MariaDB and I see no reason that there would be any conflict between two separate database installations with what you are looking to do.
The only impact I can see is that you may run low on memory.
If you are running 2 databases that are active, you may have problems. Experimentation on another Rpi would be advisable, and checking on memory usage on your test Rpi.
My Rpi 3 running hassbian and mariadb is currently using 900 MB of ram, so you may want to split the functionality between a couple of Rpis.
Regards
Just in case some HA users come here to find the easiest way to change from Sqlite to MariaDB:
It sounds all very complicated in the postings here, but it is actually VERY simple!
You do NOT need to migrate any existing data. The data in the HA DB is purged after 10 days anyway.
All you need to do is…
- Install the MariaDB add-on (Supervisor menu).
- Follow the instructions (Documentation tab and Configuration tab of the add-on – you have to update one single line in the add-on configuration and add two lines to your HA config).
- Start the add-on (check the add-on log to see whether all went well)
- Restart HA
This is all done within less than 15 minutes.
After that all your state histories are empty and start filling with new data.
You old database file is still in your config folder and can be deleted (after some time).
UPDATE 2021/09:
This was written about a year ago. It is NOT entirely true anymore since HA 2021.8 and the appearance of statistical sensors. The statistical history data of such sensors is NOT purged after 10 days. You will lose this valuable long term data, if you follow my advice above!
9 Likes
I started with home assistant recently and since my database grew rapidly, I needed to migrate to MariaDB too. Thanks for the instructions. They were of a lot of help. I have 3 remarks:
- Today I upgraded my installation and it ended up with errors, because AUTOINCREMENT on schema_changes is not set. Solution:
SELECT MAX(change_id) FROM schema_changes;
ALTER TABLE schema_changes MODIFY COLUMN change_id int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=NNNNN; ###### This is 1 more than the max above from schema_changes;
- I’m running on Synology and for some reason Synology uses port 3307 instead of 3306 for MariaDB 10! Took me an hour to find the reason for the connection error…
db_url: mysql://hass:PASSWORD@localhost:3307/hass
Solved my problem
- I’ve stumbled upon some info stating that you shouldn’t put passwords in configuration.yaml. Something with secrets.yaml. I’ve not yet figured out how to do that for MariaDB-password.
Thank you, I added the statements to update the schema_changes table.
As far as the password issue… I didn’t give the hass db user any additional permissions, and I also only expose my HA to my home network.
There might be a way to allow a connection to mariadb using some other connection method. I know mysql uses a ‘secret’ that is installed on the requesting side and I use that for another database.
you might also be able to use the .my.cnf to define the password for the hass user on your HA machine.
1 Like
I am not sure if you can use a template here I just moved the entire URL to secrets.yaml
db_url: !secret mariadb_url
sounds like a reasonable modification. I’ll add a comment to my post indicating same.
Not real from 2021.8.
From that version, HA has support for statistics, stored into database… So, if you delete db, you lose all statistics.
True. But my posting is one year old. This was way before long term stats became a part of HA.
1 Like
Of course. But before than somebody deletes his database and loses his history… i would put a flag on this 
If you read the steps in my original instructions, there is nothing deleted… Rather, the database is ‘dumped’ in sqlite to an sql file to recreate the database in mariadb, hence backed up during migration.
So from that point of view, the original information is still valid.
Regards.
I have old .db file and for a week I moved to MariaDB.
Is it possible to import old data without removing current data?
My answer was to Jpsy, not you. Jpsy said in old post “Nothing to migrate, delete and boom”. Today DB mantains ALSO energy dashbard history.
Am now a bit lost: what SHOULD be the right way to move the .db to MariaDB including the long term statistics?
Here is a video on youtube that should help you. Pretty simple now with home assistant OS.
I did this migration before HASS OS was around.
There are others available on youtube, i just did a quick search there.
Getting MariaDB up and running is indeed the easy part. The challenge is to maintain the historical data (ie: the actual migration of data from sqlite → MariaDB)
is it possible ?
Thanks for this guide. It seems to work also for migrating long-term statistics (such as energy management), however it seems that after the migration these values are no longer updated. I guess something similar with AUTO_INCREMENT should be done for the statistics, statistics_meta and statistics_run tables (more information here)? Any idea on how to alter these tables to restore the functionality?
1 Like
At the time I did the migration in the original post, the long term statistics you mention were not available in HACS yet. as you mentioned, you would probably need to do the same steps for the referenced tables and I would imagine would be similar to the steps for turning off auto index, making note of the current index values, then copy the data, update the auto increment value, and turn auto increment back on.
phpmyadmin will be your friend when figuring out the auto increment structure for the new tables.
I‘m looking for a bullet-proof, up to date (meaning HA 2021.12 release) guide on how to migrate all data from SQLite to MariaDB/MySQL database.
This one here is really the best (because: the only one!) I could find after searching the whole forum. Unfortunately seems to be outdated as stated in the previous comments. Maybe someone can guide on an up to date, proven guide - or at least add the missing modification steps for the „newer“ tables.
1 Like