Migrate back from MariaDB to the default SQLite

This guide is a summary from the This forum topic.

Introduction

A database migration can be a bit scary, so I included many screenshots for comfort.
If you have tips to improve the writing, spelling or layout of this guide, send me a PM.

Background

With all the performance improvements to Home Assistant over the last year, the benefits of using MariaDB have somewhat declined. As a result, some people wanted to move back to the default SQLite database without losing too much information.

Scope

The scope of this guide is to migrate from MariaDB to the default SQLite database while preserving long term statistics such as shown in the energy dashboard.

If you don’t need to preserve history, simply disabled/remove the MariaDB addon and restart Home Assistant.

This guide is not intended to discuss the drawbacks and benefits of the various database types, potential benefits of a migration, or whether or not it is worth the effort. If you want to discuss these philosophies, please go elsewhere on this forum.

Limitations

Due to memory limitations of most Home Assistant instances, migration of large databases will fail when performed on the HA hardware.
You can prune the short term statistics (while preserving long term statistics) to perform these steps on the HA hardware (as described in this guide) or you can perform the same steps on a beefy computer with plenty of ram.
In all cases, your long term statistics are preserved which is the goal of this guide.

Summary

We’re going use the following steps:

  • Prepare the database
  • Setup SSH for remote access
  • Install mysql-to-sqlite3
  • Migrate the database
  • Check if everything is ok.

During the migration, Home Assistant itself will be down. The OS and other add-ons will stay up and running. The migration will be done remotely over SSH.

Your MariaDB will not be affected, so in case of a failure, you can go back to MariaDB and all your long term statistics will still be there.

If you get stuck somewhere, stop for a moment and read the entire guide carefully. Some things might make more sense if you know the whole picture.

Alternatives:

Instead of performing the migration on your Home Assistant systeem, you can also migrate on an external system with plenty of ram. This approach is not (yet) covered by this guide so you will need to adapt the provided steps yourself to your system of choice.

…

Still here? Let’s get started!

If you have more confidence than common sense, you don’t need a backup. I’ll wait here for the rest of of the people to go to settings > system > backups and kick off a full backup. We can continue with the preparations while the backup is being created.

1. Prepare MariaDB addon.

image

step 1.1.

Configuration > Options: Write down database, username and password.
You’re going to need this later
image

step 1.2.

Configuration > Network: Enable Show disabled ports and set The port to access the database engine to 3306.
image

2. Reduce database size.

step 2.1.

Developer tools > Services: Select Recorder: Purge with Days to keep to 1 and check the boxes on the left.
The options Repack and Apply filter are optional. They help to reduce the database size and chances of success but can take a long time. If you choose to select these options, its best to let it cook over night.


Note: this step will permanently delete your short term history. Your long term statistics will remain uneffected.

step 2.2.

Press Call service. It will turn green instantly, but the actual work is still running in the background.

step 2.3.

Wait a good while let it finish running in the background. In the meantime, you can continue up until step 5.

3. Prepare Terminal & SSH addon.

image

step 3.1.

Configuration > Options: set a Password.

step 3.2.

Configuration > Network: Enable β€œShow disabled ports” and set β€œSSH Port” to 22.

4. Prepare Putty

image

You can use any SSH agent you like. This guide assumed you are using a windows system, so we’re going to setup Putty.

step 4.1.

Download and install Putty using the link above.

step 4.2.

Connect to Host Name[email protected] and Port 22 (the port defined in step 3.2)
image

step 4.3.

A Security Alert will be shown if this is the first time you login. Select Accept.

step 4.4.

Login using the password you choose in step 3.1

Troubleshooting tips:

  • Replace [email protected] with it’s IP address. For example: [email protected].
  • Check the Terminal & SSH addon logs, restart the addon and try again.
  • If you get an error about encryption or cipher negotiation: Reinstall Putty

5. Installing mysql-to-sqlite3

We’re going to install some packages on Home Assistant. They don’t take a lot of space and will not cost any performance once we’re done. vi is already installed, so if you know how to use it, you don’t need nano.

step 5.1. Enter the following commands in the SSH terminal

apk add nano
apk add python3
apk add py3-pip
pip install mysql-to-sqlite3

The last command will give a yellow warning which you can ignore.

6. Migration time!


Remember to wait a good while let step 2 to finish running in the background`. I don’t know how long you’re supposed to wait, but grab a coffee and your towel just to be on the safe side.

If you haven’t waited long enough, the next step will fail and you need to go back to step 2 and purge again. The only thing you’ll lose is your precious time.

step 6.1.

Using the Putty SSH terminal you still have open, stop home assistant by typing the following command:

ha core stop

image

step 6.2.

Migrate your data from SQL to SQLite by typing the following command:

mysql2sqlite -f /config/home-assistant_v2.db -d <database> -u <username> -h homeassistant.local -P 3306 -p -V --use-buffered-cursors

Substitute <database> and <username> with the values you recorded in step 1.1

What it all means:
-f: Target database. This is the home assistant default database location.
-d: Source database. MariaDB database name you recorded in step 1.1.
-u: Username. MariaDB username you recorded in step 1.1.
-h: Host. The hostname or IP address of your home assistant. This option is not needed for all users. 
-P: Port. The MariaDB port you specified in step 1.2.
-p: Prompt for password
-V --user-buffered-cursors: Has something to do with the Matrix

This will prompt you for the MariaDB database password collected earlier.

When it’s done, it should look like this:

[core-ssh ~]$ mysql2sqlite -f /config/home-assistant_v2.db -d homeassistant -h homeassistant.local -P 3306  -u mariusthvdb -p -V --use-buffered-cursors
MySQL password: 
2023-06-05 16:48:32 INFO     Transferring table event_data
100%|β–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆ| 1/1 [00:00<00:00,  2.51it/s]
2023-06-05 16:48:33 INFO     Transferring table event_types
100%|β–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆ| 1/1 [00:00<00:00,  5.13it/s]
...
2023-06-05 16:54:10 INFO     Transferring table statistics_meta
100%|β–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆ| 1/1 [00:00<00:00,  5.13it/s]
2023-06-05 16:54:12 INFO     Transferring table statistics_runs
100%|β–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆ| 1/1 [00:00<00:00,  4.55it/s]
2023-06-05 16:54:12 INFO     Transferring table statistics_short_term
100%|β–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆ| 4/4 [00:17<00:00,  4.43s/it]
2023-06-05 16:54:42 INFO     Vacuuming created SQLite database file.
This might take a while.
2023-06-05 16:55:08 INFO     Done!
[core-ssh ~]$

step 6.3.

Update configuration.yaml by typing the following command:

nano /config/configuration.yaml

step 6.4.

Comment out the db_url line, causing home assistant to fall back to it’s default database

step 6.5.

Start home assistant back up by typing the following command:

ha core start

Troubleshooting:

If you get an error at the transfering table state, your database is still to big.

2023-06-06 13:57:48 INFO     Transferring table states
Killed

Do another purge and take a nap before trying again

If you get a corrupt database check out this post: Trying to migrate from MariaDB back to default SQLite. Migration completed but corrupted db

If you encountered (and solved) different issues, please add them to the guide. You can edit this post as if it is a wiki page.

7. Rejoice…

Congratulations. You made it.

The best way to check if your long term statistics is through the energy dashboard. Scroll back a few months and feast your eyes on all the glorious data you have painstakingly gathered.

You also might want to check settings > system > logs just to be sure. If there is a small inconsistency, it might be corrected on a reboot.

…or not

If you are not happy with the migration, all you need to do is uncomment the line in configurations.yaml and restart Home Assistant. You can do this from within Home Assistant if you want.
image
Your long term statistics will still be in there.

Final thoughs

:broom: You can stop the MariaDB add-on now. Just hold of a few days before deleting it.

:pray: Many thanks to @jyavenard for kicking off the discussion and @Nardol for providing the solution above and all the people who helped out with the various small issues to make this procedure work for most of us.

14 Likes

Thanks for the effort, I was looking to do this too and this seems the help I needed.

For step 2.1 I would add to tick the checkbox before Repack and make sure to toggle the switch on the right side. It’s correct in your screenshot, but better to mention it in the text as well :slight_smile:

Maybe you can also add that the repacking process can take hours. instead of a decent amount of time. Maybe wait something like 24 hours or so.

Finally it would be great if @bdraco can β€˜approve’ this message with his wisdom :laughing:

Thank you for your input. I made some updates. More info on whether or not repack is beneficial and how long one needs to wait would be nice to have.

Hi, just performed the migration thanks to this guide as well. :slight_smile:
Created an account here just to help out in case some other people got stuck on this point.

Had some issues due to a too big database, the system only has 4 GB.
What I did is performed the migration on another system (VM) with plenty of RAM available and the migration went through without a problem. (quick as well on a XEON) :wink:
After that I copied the new DBfile to the HA instance and started HA core again. All long term statistics still there :slight_smile:

1 Like

I added a note of the alternative approach to the guide. Originally, I considered this as well, but decided it was enough work to document the local migration.

Feel free to copy this guide and adapt it to a remote migration. I’ll link to it from here.
Or you can provide the steps below so I can link to your post.

Anyone else performed this migration lately without issues?

I’m running MariaDB now without issues but I want to migrate to the recommended default DB because of KISS :kissing_heart:
I don’t remember why I moved to MariaDB in first place :tipping_hand_man:

MariaDB used to be a lot faster, but it was not well suited for an SD card. Those who had an SSD could gain very significant performance boosts by using MariaDB.
The developers explained during a release party a few months back that the SQLite is now (after a ton of hard work) as fast as MariaDB and more stable. MariaDB is no longer recommended. That sparked this forum topic: https://community.home-assistant.io/t/migrating-from-mysql-mariadb-back-to-sqlite

As you can read in the topic, there has been quite some intrest. After the migration process was fully figured out, I created this guide to make it more accessible for a wider audience.

2 Likes

Thank you for this, all migrated back before moving to a new server. Thanks!

I have HA and MariaDB in separate docker containers, following along and changing the mysql2sqlite command goes fine, but:

2023-11-26 12:57:13 INFO     Transferring table states
  0%|                                                                                                                                                                                          | 0/12 [00:00<?, ?it/s]
2023-11-26 12:57:36 ERROR    SQLite transfer failed inserting data into table states: table states has no column named domain
table states has no column named domain

What am I overlooking?

Unfortunately, I have not seen this before.
Check the original topic since it has more trail and error information: https://community.home-assistant.io/t/migrating-from-mysql-mariadb-back-to-sqlite

I am getting something similar:

2023-11-26 16:02:22 ERROR    SQLite transfer failed inserting data into table events: table events has no column named created
table events has no column named created

Additional note for those of you get an error similar to β€œinserting data into table xxx no column” or similar, delete the previously failed /config/home_assistant_v2.db. The failure is caused by the prior failed migration, but the error is not obvious.

4 Likes

I want to revert to SQLite, and I don’t mind if my data is lost. Is it acceptable to simply remove the MariaDB addon and eliminate the configuration.yaml entry for MariaDB, allowing for the deployment of a new SQLite database?

Not 100% sure but pretty close sure…this will also mean loosing more, e.g. for my docker install I would have to reinstall each component/device from scratch
If you want a fresh install then check other posts how to best accomplish this

1 Like

I just migrated from a 2.3G MySql to a 1.6G sqlite, took 22mins…thanks again @AJediIAm @jyavenard @Nardol

1 Like

This is exactly what I did, and it worked perfectly fine. After a day or two, I had all of the historical data that I normally refer to.

1 Like

Thank you Dave. I just did that and it worked fine. I can see that there is a new DB. Is there a way to see if there is left over files from mariaDB ?

1 Like

When you deleted the addon, all of the files in that addon should have been deleted, including the database.

1 Like

Thanks for the instructions. If you have an installation that has been running with (a lot of) statistics since the beginning of statistics, 4GB of memory is not enough to do the migration. It runs out of memory on the statistics table.

10 days of state information was no problem (1000+ entities).

Did the migration with a vm on my laptop in the end, even with 8GB I was afraid I would run out of memory.

Running on SQLite now with HA Blue and USB SSD. I get the feeling that HA is running smoother now.