UPDATED April 2023:
*** DANGER, If you are not fluent using mysql/mariadb, Run away, as you do not want to be
*** a database administrator! Things have changed in the past few years with respect to how
*** home assistant uses the SQLite database, making SQLite perform better for the average user.
See what the developers have to say at the 2023.4 release party.
Here is the video at the correct timestamp:
I have gleaned most of the following information from the BurnsHA video regarding migrating Home assistant from sqlite3 to mariadb. I did add a few steps that were not mentioned in his video that were required for my migration.
Many thanks to all who have tread this water previously.
I am using a Raspberry pi 3 as the HA server running hassbian, and I have done the following steps to migrate from sqlite3 to mariadb:
# The first few steps here are typical install and configuration of mariadb.
sudo apt install mariadb-server mariadb-client
sudo apt install libmariadb-dev sqlite3
sudo mysql_secure_installation
I did the following 2 steps using phpmyadmin
Created a user hass with all permissions
Created a database hass
Stop the hass server process on your server.
sqlite3 home-assistant_v2.db .dump > hadump.sql
git clone https://github.com/athlite/sqlite3-to-mysql
copied sqlite3-to-mysql to same directory as hadump.sql
bash sqlite3-to-mysql hadump.sql > haimport.sql
mysql -u hass -p -h localhost hass < haimport.sql
## the above mysql statement imported about 55K records in both states and events, 360 into recorder_runs, as well as 6 records into schema_changes for my home-assistant with a 2 day history. Your mileage will vary.
mysql -u hass -p hass
## The back-quotes need to be converted into double quotes in the events and states table:
update events set event_data = REPLACE(event_data, '`', '"');
update states set attributes = REPLACE(attributes, '`', '"');
NOTE: the 2 updates above have a problem showing the backquote..
select max(run_id) from recorder_runs;
alter table recorder_runs modify column run_id INT NOT NULL AUTO_INCREMENT,
AUTO_INCREMENT=NNNNN; ## this is 1 more than the max above from table_recorder
alter table states drop foreign key states_ibfk_1;
select max(event_id) from events;
alter table events modify column event_id int(11) NOT NULL AUTO_INCREMENT,
AUTO_INCREMENT=NNNNN; ### this is 1 more than the max above from events
select max(state_id) from states;
alter table states modify column state_id int(11) NOT NULL AUTO_INCREMENT,
AUTO_INCREMENT=NNNNN; ###### This is 1 more than the max above from states
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 am not certain that the next step is required, but was in sqlite and not in mariadb.
alter table states add foreign key(event_id) references events (event_id);
#### Finally, modify configuration.yaml to include the following statements:
#### use the server ip address with mysql installed and userid, password, and database you created above
recorder:
db_url: mysql://hass:PASSWORD@localhost/hass
# db_url: !secret mariadb_url
### If you use !secret above, you will need to add the secret to secrets.yaml as follows:
mariadb_url: mysql://hass:PASSWORD@localhost/hass
start the hass server.
UPDATE 2022-08-21:
I am linking to a post from 08-21 that has updated instructions for migration:
UPDATE 2022-10-15: Linking to a post with refreshed instructions for direct mysql database loading. Note that there have been problems referenced using this method with databases over about 6GB, AND you most likely will need additional RAM for this update method based on comments.
