As requested by @jr3us, here is an updated script up till today. It might become obsolete as new versions are created and might not work for you! I offer absolute NO guarantee for any data lost!. You are warned.
# 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.
## āFixā the data and update tables:
mysql hass
update events set event_data = REPLACE(event_data, '`', '"');
update states set attributes = REPLACE(attributes, '`', '"');
update state_attributes set shared_attrs = REPLACE(shared_attrs, '`', '"');
update event_data set shared_data = REPLACE(shared_data, '`', '"');
# Remove Foreign Key Constraints
ALTER TABLE `states` DROP FOREIGN KEY `states_ibfk_1`;
ALTER TABLE `states` DROP FOREIGN KEY `states_ibfk_2`;
ALTER TABLE `states` DROP FOREIGN KEY `states_ibfk_3`;
ALTER TABLE `statistics_short_term` DROP FOREIGN KEY `statistics_short_term_ibfk_1`;
ALTER TABLE `statistics` DROP FOREIGN KEY `statistics_ibfk_1`;
ALTER TABLE `events` DROP FOREIGN KEY `events_ibfk_1`;
# THIS IS NOT A SCRIPT TO COPY AND PASTE, PLEASE, READ THE FOLLOWING TWO LINES AND EXECUTE EACH STATEMENT REPLACING THE 'N+1' WITH THE VALUE FROM PREVIOUS SELECT STATEMENT
# alter the primary key in the tables to use auto_increment, starting from the last run id +1.
# MANUALLY run the following 'SELECT' statements and replace 'N+1' with the output of the select statment adding 1 (e.g., 1+1=2).
select max(run_id) from recorder_runs;
alter table recorder_runs modify column run_id INT NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=N+1;
select max(event_id) from events;
alter table events modify column event_id int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=N+1;
select max(state_id) from states;
alter table states modify column state_id int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=N+1;
SELECT MAX(change_id) FROM schema_changes;
ALTER TABLE schema_changes MODIFY COLUMN change_id int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=N+1;
SELECT MAX(id) FROM statistics;
ALTER TABLE statistics MODIFY COLUMN id int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=N+1;
SELECT MAX(id) FROM statistics_meta;
ALTER TABLE statistics_meta MODIFY COLUMN id int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=N+1;
SELECT MAX(run_id) FROM statistics_runs;
ALTER TABLE statistics_runs MODIFY COLUMN run_id int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=N+1;
SELECT MAX(id) FROM statistics_short_term;
ALTER TABLE statistics_short_term MODIFY COLUMN id int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=N+1;
SELECT MAX(attributes_id) FROM state_attributes;
ALTER TABLE state_attributes MODIFY COLUMN attributes_id int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=N+1;
SELECT MAX(data_id) FROM event_data;
ALTER TABLE event_data MODIFY COLUMN data_id int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=N+1;
# Add back the foreign key constraints
ALTER TABLE `states` ADD CONSTRAINT `states_ibfk_1` FOREIGN KEY (`old_state_id`)REFERENCES `states`(`state_id`) ON DELETE RESTRICT ON UPDATE RESTRICT;
ALTER TABLE `states` ADD CONSTRAINT `states_ibfk_2` FOREIGN KEY (`event_id`)REFERENCES `events`(`event_id`) ON DELETE CASCADE ON UPDATE RESTRICT;
ALTER TABLE `states` ADD CONSTRAINT `states_ibfk_3` FOREIGN KEY (`attributes_id`)REFERENCES `state_attributes`(`attributes_id`);
ALTER TABLE `statistics` ADD CONSTRAINT `statistics_ibfk_1` FOREIGN KEY (`metadata_id`)REFERENCES `statistics_meta`(`id`) ON DELETE CASCADE ON UPDATE RESTRICT;
ALTER TABLE `statistics_short_term` ADD CONSTRAINT `statistics_short_term_ibfk_1` FOREIGN KEY (`metadata_id`) REFERENCES `statistics_meta`(`id`) ON DELETE CASCADE ON UPDATE RESTRICT;
ALTER TABLE `events` ADD CONSTRAINT `events_ibfk_1` FOREIGN KEY (`data_id`) REFERENCES `event_data` (`data_id`);
ALTER TABLE events MODIFY `time_fired` datetime(6), MODIFY `event_data` longtext;
ALTER TABLE states MODIFY `last_updated` datetime(6), MODIFY `last_changed` datetime(6), MODIFY `attributes`longtext;
ALTER TABLE statistics_short_term MODIFY `start` datetime(6), MODIFY `created` datetime(6), MODIFY `last_reset` datetime(6), MODIFY `mean` double, MODIFY `min` double, MODIFY `max` double, MODIFY `state` double, MODIFY `sum` double;
ALTER TABLE statistics MODIFY `start` datetime(6), MODIFY `created` datetime(6), MODIFY `last_reset` datetime(6), MODIFY `mean` double, MODIFY `min` double, MODIFY `max` double, MODIFY `state` double, MODIFY `sum` double;
Important note added Oct-5th 2022. I found out today, the database model created by the script sqlite3-to-mysql is not creating the same model created by HA when starting a new database. Iāve added 4 SQL sentences to the end of the script to modify the datatypes for 4 tables that caused some troubles later so I hope there will be no more issues like this to others as well.
Did you notice any problems that you care to mention for those interested in migrating as well?