Homeassistant - Restore database to your own server

Introduction:

I build my smart home with one of the The main reasons for this are simple: Statistics. Diagrams of all kinds, analyses and evaluations about my Creating life, is for me a very interesting area to me and to be able to represent my change graphically. Cost minimisation and comfort are of course also on my agenda. Um... to be able to implement or test all this (in the case of However, I need data. These data are Normal case in a SQLite3 database on the Homeassistant server itself saved.
Now I've read more often, this database likes to have errors and then only through a backup can be reset. In my case, all 24h backups created what in my worstcase means 23h data loss. In principle, for my frame as a private person, not bad, but But that's too much for me.
I would like to Database likes to outsource, this also has other advantages, as also if my home assistant should not work anymore, are my data always there. I can use the Homeasistant VM without problems delete and reset the data by an external DB Get server. Furthermore, foreign programs can also be applied to these Access and analyze data.
Last I can home-made problem solved by the outsourcing, namely Memory shortage. My Homeassistant instance has 32 GB of memory at the beginning and since I created new machines afterwards, is a magnification possible only with effort if I have the whole Move storage area. Without my database I save (after three ) already 4GB. I therefore show how the SQLite3 database migrated to a Maria DB server under Debian can be.

Export Homeassistant Database

First, the Homeasistant database must be exported. For numerous methods can be used, among other things the download database from the GUI with an addon, but I wanted to export the database, while Homeassistant not running, so I just shut down the server and mine Image (in qcow2 format) with a VM mounted and the file to a internal SMB drive copied to work on it. depending on Installation type, you can also use the Homeassistant service stop and navigate normally on your file system.
I use the Homeassistant OS version under Unraid as VM and had therefore simply stopped the VM and as in the Contribution shown to export the file.

Prepare database server

In my case, I use an “external” as a database server Server that also runs virtualized on my Unraid (with “external” I mean that not the same VM as the HASS machine is used).

I assume that a MariaDB server (or similar) was installed and a database with matching user how things are done, is wide on the Internet described, broken down to one sentence but would not be much more as “apt install mariadb-server”, adapting the network and then in MariaDB CLI ->

create database homeassistant;
create user 'homeassistant'@'%' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES on homeassistant.* to homeassistant@'%';

Normally, the MariaDB installation is locally bound and can not achieved by outside. I want to change that with me, because my home assistant later accessing the network.

To do this, I adapt the MariaDB Server config (file: /etc/mysql/mariadb.conf.d/50-server.cnf)

Here the section “[mysqld]” has to be adjusted, as we are Daemon Service want to say that not only on localhost but also to respond to the IP address.
The customized configuration line then looks as follows:

[mysqld]
...
bind-address            = 192.168.2.16

Then I restart the service by the command “sudo service mariadb restart”.

Transfer of data from Homeassistant

WARNING: How to read more often in different forums, there are currently problems with the Energy Dashboard when another database is used.
In my case, current prices are not currently more correctly calculated, otherwise everything works, why I I should like to ask the Commission to take a look at the problem. , Currently, a guess from the forum is that the data of the Energydashboards stored in another location, but not are correctly migrated. If the risk is too high, you should cancel! – or do not transfer the data, restart I don't think the problem is up.

We now use the database that we have exported from Homeassistant.

To transfer the data, I use a Python Script which better than the GitHub project worked. For installation can be easily the Python project will be used. Installable with

pip install sqlite3-to-mysql

after installation, the transmission can also take place.

sqlite3mysql -f <<SQLite Datenbank>> -d <<Database>> -u <<User>> -h <<Host>> -p -t -i DEFAULT

I had to assign a total of 8 GB of RAM to my VM, because the script otherwise went out of memory, that should be considered if you have larger databases. My was about 2.2GB large).

After the transfer, the table must be adjusted, so I have the Post
the procedure copied. – NO COPY PASTE SCRIPT! – READ!!!!! – otherwise you shoot the data!

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`);

After this has been completed, we will finally adjust Homeassistant and add to
the configuration.yaml:

recorder:
  db_url: mysql://<<user>>:<<passwort>>@<<host>>/<<Datenbank>>
  # db_url: !secret mariadb_url //alternative, hier werden die Daten in der "secrets.yaml" Datei gespeichert

If you use the 2nd version with the Secrets.yaml, you must insert the following line:

mariadb_url:  mysql://<<user>>:<<passwort>>@<<host>>/<<Datenbank>>

Lastly, Homeassistant is restarted again and now should Save new data on the MariaDB server. To this you can enter the following in the MariaDB CLI:

mysql > show processlist;

For the output table, under the column “db” should at least: a combination with the table ‘<

---

Back…