Use putty or similar to ssh to the HA (Port 222). The session will not end then.
ssh to HA port 222, connection refused.
Also tried 2222, 22222.
I checked the add-on, changed it to port 22, but still connection refused (add-on is started).
Did you use the right username and password? Did you define them?
P.S.: I am using “Advanced SSH & Web Terminal” addon.
I found that my SSH add-on was different. So, I uninstalled it and installed this:
Same as yours @AleXSR700
Then I watched this video:
Enable SSH In Home Assistant - TUTORIAL 2022 - YouTube
Then I ssh from my Windows PC.
I had to again install python3, py3-pip, and mysql-to-sqlite3.
And the rest worked nicely!
I haven’t uninstalled MariaDB though.
1 Like
What was wrong on the first run, where you har to restart, i’m also failing with the connection the the DB.
I forgot to enable the MariaDB port 3306 to connect, you need to enable that on the MariaDB add-on.
Check this:
thanks 
that fixed it
Please let us know if you see any improvements and if anything was changed or lost in the migration. I haven’t done this yet, but would like to get on to the preferred db path.
No, I don’t see any kind of improvement in terms of speed or performance. This is because my database is still small (about 200MB) and my HA running on Proxmox VM using Intel n5105 mini-PC, which is more than enough.
I want to migrate back to SQLite3 because I believe “the less the better”. If I can cut down or reduce any kind of (unnecessary) component, I will do it. Especially after reading all the horror stories during HA updates.
I started HA just a few months ago, and I went straight to use MariaDB after reading and watching lots of videos telling that “You better use MariaDB for performance reason because SQLite3 is bad and slow”. But now, after the HA developers announced that they made big improvements on SQLite3, which were confirmed by many people, I wanted to move back as well.
About the data after the migration, nothing changed. All data are intact! Short term and long term statistics, whatever, they are not affected. As of now, HA is running the same as before
Hope this helps.
1 Like
So, considering that I only wanted to preserve my long term statistics for keeping the energy dashboard coherent, and that this energy dashboard is so buggy and messes the data so much whenever a change is made (such as adding a new monitored device where it will insert insanely high negative values): those stats were of poor use to start.
So all I did was comment the mariadb recorder configuration in configuration.yaml and restarted. It re-created the sqlite db and I reconfigured everything manually.
It’s been 3 days now, and all I can say is that HA with sqlite running locally is much slower than when using a remotely hosted mariaDB on a powerful linux box. When I first load the history graph, it takes several seconds for the graph to show ; until then it shows “Loading state history”.
When using the remotely hosted mariadb it’s almost always instantaneous.
only thing i had to do, to add the 3306 port online inside the MariaDB addon.
i went from 1.7gig db to 1.39gb db with now MySQL.
thanks for your post.
1 Like
this looks very simple indeed, but as always the devil is in the details…
is there no need for the db password? (you only mention <database_name> and <mariadb_user> )
my case if my db_url is
mysql://mariusthvdb:greatpassword@core-mariadb/homeassistant?charset=utf8mb4
what would that command_line become:
mysql2sqlite -f /config/home-assistant_v2.db -d homeassistant -u mariusthvdb -p -V --use-buffered-cursors
?
or, should I also replace the password greatpassword in that line (after the -p ?)
just to be sure: we can still restore the MariaDB when backed up regularly, or do we need special precausions for that?
The tool will prompt you for the password.
I am not sure to understand what you mean.
After the migration the MariaDB database won’t be used anymore so even if you back it up it will be outdated.
If you want to use MariaDB again just uncomment the MariaDB URL.
If you want to have updated data you can use SQLite3 to MySQL before starting HA again. I have not tested.
thanks! that’s what I needed to know.
will go ahead and try 
that is, if I understand this better:
the place to install the converter, when using HAOS, couldnt that be inside the docker? so.
ssh [email protected] -p 22222
docker exec -it homeassistant /bin/bash
this is my regular routine to run the py-spy’s or other commands that require shell access to the HA container (not the OS).
it would have the advantage the install is temporary, and gets cleaned up at next container update)
might I suggest you write up your post as a community guide? it seems it would benefit the whole community to have this as a top level post!
You can also replace the -p (password prompt) with --mysql-password PASSWORD. Mine is a complicated one and is hard to type on a command line
1 Like
somehow that seems indeed the easiest way to do, prepare that complete command, check it and then c&p.
I would have expected it to be
mysql2sqlite -f /config/home-assistant_v2.db -d homeassistant -u mariusthvdb -p greatpassword -V --use-buffered-cursors
so keeping the -p and not
mysql2sqlite -f /config/home-assistant_v2.db -d homeassistant -u mariusthvdb --mysql-password greatpassword -V --use-buffered-cursors
as you suggest. given the fact -d and -u are also still in the command…
Yes I thought it would but the docs say not
o well, I get;
2023-06-04 23:28:13 ERROR 2003: Can't connect to MySQL server on 'localhost:3306' (111 Connection refused)
2003: Can't connect to MySQL server on 'localhost:3306' (111 Connection refused)
after typing the correct password…
yes, had the 3306 port opened explicitly.
could it be it is because I didnt install a venv?
I had something similar and had to add host (-h)
mysql2sqlite -f /config/db2/home-assistant_v2.db -d homeassistant -u homeassistant -h 192.168.1.40 -P 3306 --mysql-password xxxxxxxxxxxxxxx -V --use-buffered-cursors
1 Like
aha, adding the ip address helped:
mysql2sqlite -f /config/home-assistant_v2.db -h 192.168.1.61 -d homeassistant -u mariusthvdb -p -V --use-buffered-cursors
MySQL password:
2023-06-05 09:52:48 INFO Transferring table event_data
100%|█████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████| 1/1 [00:00<00:00, 2.52it/s]
2023-06-05 09:52:49 INFO Transferring table event_types
100%|█████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████| 1/1 [00:00<00:00, 4.87it/s]
2023-06-05 09:52:49 INFO Transferring table events
0%| | 0/1 [00:00<?, ?it/s]
2023-06-05 09:52:50 ERROR SQLite transfer failed inserting data into table events: table events has no column named created
table events has no column named created
note I could use the -p
there s only the error on the table events.
update:
Ouch: all history gone from the frontend … no graphs, no energy dashboard.
quickly switch back to MariaDB…
1 Like