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
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
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, '’, ‘"’);
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
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
db_url: mysql://hass:[email protected]/hass