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 table 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