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:[email protected]/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:[email protected]/hass
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.