我从BurnsHA视频中收集了以下关于将Home助手从sqlite3迁移到mariadb的信息。19463331伟德国际我确实添加了一些在他的视频中没有提到的步骤,这是我迁移所需的。
非常感谢先前踩过这种水的所有人。
我使用Raspberry pi 3作为HA服务器运行hassbian,我已经完成以下步骤从sqlite3迁移到mariadb:
这里的前几个步骤是mariadb的典型安装和配置。sudo apt install libmariadb- server mariadb-client sudo apt install libmariadb-dev sqlite3Sqlite3 home-assistant_v2.db .dump > hadump。SQL git clone https://github.com/athlite/sqlite3-to-mysql将sqlite3-to-mysql复制到与hadump相同的目录。SQL bash sqlite3-to-mysql hadump。sql > haimport。SQL mysql -u hass -p -h localhost hass < haimport。上面的mysql语句在状态和事件中导入了大约55K条记录,在recorder_runs中导入了360条记录,在schema_changes中导入了6条记录,这是我的home-assistant的2天历史。你的里程会有所不同。update events set event_data = REPLACE(event_data, ' ', ' ', ' ' ');update状态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 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:PASSWORD@localhost/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:PASSWORD@localhost/hass
启动Hass服务器。