Raspberry Pi 3 Model B, hassbian from “HASSbian 1.1 - The Toy-box”
As described in https://home-assistant.io/getting-started/hassbian-installation/
Home Assistant 0.38.3; Path to configuration.yaml: /home/homeassistant/.homeassistant
These are the steps I used to get Mysql up and integrated into homeassistant. Collected together here for easy reference.
mysql-connector is from Oracle, the maintainer of Mysql; but the recorder component depends on mysqlclient. You might want mysql-connector for your Mysql python3 projects.
#####as user pi:
sudo apt-get install libmysqlclient-dev
sudo apt-get install python3-dev
sudo apt-get install mysql-client # Needed to access Mysql from command line.
# sudo pip3 install --upgrade mysql-connector
sudo pip3 install mysqlclientEnvironment variables are used for database parameters so this script can be publicly available. Either define the variables appropriately, or substitute text.
I decided to name my Mysql schema “homeassistant” use whatever suits you, but be consistent.
Create Mysql database on host machine; create empty schema “homeassistant.” Create your DBUser in Mysql, giving it all privileges for the “homeassistant” schema. I used MySQL Workbench running on a Mac to do these tasks.
has a good description of how to do this.
Create schema in mysql database: (The schema itself already exists since you created it in order to give your DBUser privileges on it. The CREATE SCHEMA below is just for insurance. You could run this script as some other mysql root user and give DBUser privileges on the schema after this is run.)
#!/bin/bash
mysql -u${DBUser} -p${DBPassword} -h${DBHost} homeassistant <<EOF
-- MySQL Workbench Synchronization
-- Generated: 2017-02-21 15:57
-- Model: homeassistant
-- Version: 1.0
-- Project: homeassistant
-- Author: Thomas Demay
/*
homeassistant recorder service creates tables with time objects of data type "DATETIME." Sqlite DATETIME type apparently includes micro sec. The corresponding mysql data type is DATETIME(6). For this reason, the tables are created before starting homeassistant with a mysql recorder.
Mysql TIMESTAMP(6) data type stores the time in the database as as UTC; converting on input and output to local time. Homeassistant stores all times in UTC; so mysql TIMESTAMP(6) gets very confused. Mysql DATETIME(6) does not do UTC conversions; so is what homeassistant expects.
*/
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
CREATE SCHEMA IF NOT EXISTS `homeassistant` DEFAULT CHARACTER SET utf8 ;
CREATE TABLE IF NOT EXISTS `homeassistant`.`recorder_runs` (
`run_id` INT(11) NOT NULL AUTO_INCREMENT ,
`start` DATETIME(6) NULL DEFAULT NULL,
`end` DATETIME(6) NULL DEFAULT NULL,
`closed_incorrect` TINYINT(4) NULL DEFAULT NULL,
`created` DATETIME(6) NULL DEFAULT NULL,
PRIMARY KEY (`run_id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;
CREATE TABLE IF NOT EXISTS `homeassistant`.`events` (
`event_id` INT(11) NOT NULL AUTO_INCREMENT ,
`event_type` VARCHAR(32) NULL DEFAULT NULL,
`event_data` TEXT NULL DEFAULT NULL,
`origin` VARCHAR(32) NULL DEFAULT NULL,
`time_fired` DATETIME(6) NULL DEFAULT NULL,
`created` DATETIME(6) NULL DEFAULT NULL,
INDEX `ix_events_time_fired` (`time_fired` ASC),
INDEX `ix_events_event_type` (`event_type` ASC),
PRIMARY KEY (`event_id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;
CREATE TABLE IF NOT EXISTS `homeassistant`.`states` (
`state_id` INT(11) NOT NULL AUTO_INCREMENT ,
`domain` VARCHAR(64) NULL DEFAULT NULL,
`entity_id` VARCHAR(255) NULL DEFAULT NULL,
`state` VARCHAR(255) NULL DEFAULT NULL,
`attributes` TEXT NULL DEFAULT NULL,
`event_id` INT(11) NULL DEFAULT NULL,
`last_changed` DATETIME(6) NULL DEFAULT NULL,
`last_updated` DATETIME(6) NULL DEFAULT NULL,
`created` DATETIME(6) NULL DEFAULT NULL,
PRIMARY KEY (`state_id`),
INDEX `event_id_idx` (`event_id` ASC),
INDEX `states__state_changes` (`last_changed` ASC, `last_updated` ASC, `entity_id` ASC),
INDEX `states__significant_changes` (`domain` ASC, `last_updated` ASC, `entity_id` ASC),
CONSTRAINT `event_id`
FOREIGN KEY (`event_id`)
REFERENCES `homeassistant`.`events` (`event_id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;
CREATE TABLE IF NOT EXISTS `homeassistant`.`schema_changes` (
`change_id` INT(11) NOT NULL AUTO_INCREMENT ,
`schema_version` INT(11) NULL DEFAULT NULL,
`changed` DATETIME(6) NULL DEFAULT NULL,
PRIMARY KEY (`change_id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;
SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
EOF#####Switch to homeassistant virtual environment:
(I created .profile and .bashrc files in /home/homeassistant to setup the environment as desired; the last step is to activate the virtual environment. In this case, the first two lines of the code below become:
“sudo su -l homeassistant”
If somebody knows that this is a bad idea, please let me know.)
#!/bin/bash
sudo su -s /bin/bash homeassistant
source /srv/homeassistant/bin/activate
pip3 install --upgrade mysqlclientedit configuration.yaml:
(Don’t forget the ?charset=utf8 specification on the db_url.)
recorder:
db_url: !secret recorder_db_url # recorder_db_url: mysql://[YOUR MYSQL USERNAME]:[YOUR MYSQL PASSWORD]@[YOUR MYSQL HOST]/homeassistant?charset=utf8
exclude:
domains:
- automation
- weblink
- updater
entities:
- sun.sun # Don't record sun data
- sensor.last_boot
- sensor.date#####Switch back to user pi;
stop homeassistant;
[optional] copy records from sqlite database to mysql database;
start homeassistant.
#!/bin/bash
exit # exit homeassistant user
echo "Begin stopping homeassistant @ $(date)"; sudo systemctl stop [email protected]; echo "Finished stopping homeassistant @ $(date)"
# Copying current Sqlite database to Mysql database is optional. If copying
# first delete any table entries that you created testing the Mysql database.
mysql -u${DBUser} -p${DBPassword} -h${DBHost} -e "delete from states; delete from schema_changes; delete from recorder_runs; delete from events;" homeassistant
# Extract data from sqlite database; massage to be mysql compatible, then
# feed into Mysql database. Do tables in this order so that events records
# exist before inserting states records that reference events.
sqlite3 /home/homeassistant/.homeassistant/home-assistant_v2.db <<EOF \
| sed 's/PRAGMA.*/SET FOREIGN_KEY_CHECKS=0; SET time_zone="+00:00";/g' \
| sed 's/TRANSACTION//' \
| sed 's/CREATE TABLE/CREATE TABLE IF NOT EXISTS/g' \
| sed 's/"\(\<.*\>\)" /`\1` /g' \
| sed 's/INSERT INTO/INSERT IGNORE INTO/g' \
| sed 's/CREATE INDEX.*//g' \
| tee schema_changes.sql \
| mysql -u${DBUser} -p${DBPassword} -h${DBHost} homeassistant &
.dump schema_changes
.exit
EOF
wait # copy operations run in background then wait; so order of operations is preserved.
sqlite3 /home/homeassistant/.homeassistant/home-assistant_v2.db <<EOF \
| sed 's/PRAGMA.*/SET FOREIGN_KEY_CHECKS=0; SET time_zone="+00:00";/g' \
| sed 's/TRANSACTION//' \
| sed 's/CREATE TABLE/CREATE TABLE IF NOT EXISTS/g' \
| sed 's/"\(\<.*\>\)" /`\1` /g' \
| sed 's/INSERT INTO/INSERT IGNORE INTO/g' \
| sed 's/CREATE INDEX.*//g' \
| tee recorder_runs.sql \
| mysql -u${DBUser} -p${DBPassword} -h${DBHost} homeassistant &
.dump recorder_runs
.exit
EOF
wait
sqlite3 /home/homeassistant/.homeassistant/home-assistant_v2.db <<EOF \
| sed 's/PRAGMA.*/SET FOREIGN_KEY_CHECKS=0; SET time_zone="+00:00";/g' \
| sed 's/TRANSACTION//' \
| sed 's/CREATE TABLE/CREATE TABLE IF NOT EXISTS/g' \
| sed 's/"\(\<.*\>\)" /`\1` /g' \
| sed 's/INSERT INTO/INSERT IGNORE INTO/g' \
| sed 's/CREATE INDEX.*//g' \
| tee events.sql \
| mysql -u${DBUser} -p${DBPassword} -h${DBHost} homeassistant &
.dump events
.exit
EOF
wait
sqlite3 /home/homeassistant/.homeassistant/home-assistant_v2.db <<EOF \
| sed 's/PRAGMA.*/SET FOREIGN_KEY_CHECKS=0; SET time_zone="+00:00";/g' \
| sed 's/TRANSACTION//' \
| sed 's/CREATE TABLE/CREATE TABLE IF NOT EXISTS/g' \
| sed 's/"\(\<.*\>\)" /`\1` /g' \
| sed 's/INSERT INTO/INSERT IGNORE INTO/g' \
| sed 's/CREATE INDEX.*//g' \
| tee states.sql \
| mysql -u${DBUser} -p${DBPassword} -h${DBHost} homeassistant &
.dump states
.exit
EOF
wait
echo "Begin starting homeassistant @ $(date)"; sudo systemctl start [email protected]; echo "Finished starting homeassistant @ $(date)"
