OpenHAB & MySQL Persistence

Persistence allows OpenHAB to save data across service reboots as well as provide historical data which is used in graphing.  The following steps are how I configured MySQL with OpenHAB.  The servers are on two separate virtual machines, however that is not a requirement.

 

Login to MySQL shell

mysql –user root -p

Create database

CREATE DATABASE openhab;

Verify database was created

SHOW DATABASES;

Create user for OpenHAB

CREATE USER ‘openhab_rw’@’OpenHABIPAddress‘ IDENTIFIED BY ‘Password‘;

Assign permissions for user to access openHAB database only

GRANT ALL PRIVILEGES ON openhab.* to ‘openhab_rw’@’OpenHABIPAddress‘ identified by ‘Password‘;

Update all permission modifications

FLUSH PRIVILEGES;

Verify the ability to login from openhab machine, substituting services below for the hostname you installed MySQL on

mysql -h services -u openhab_rw -p

Modify 2 locations in openhab.cfg

# The name of the default persistence service to use
persistence:default=mysql
############################ SQL Persistence Service ##################################
# the database url like ‘jdbc:mysql://<host>:<port>/<database>’ (without quotes)
mysql:url=jdbc:mysql://MYSQLIPADDRESS/openhab

# the database user
mysql:user=openhab_rw

# the database password
mysql:password=PASSWORD

# the reconnection counter
mysql:reconnectCnt=1

# the connection timeout (in seconds)
#mysql:waitTimeout=

Install persistence

sudo apt-get install openhab-addon-persistence-mysql

 

Create base persist file to log every change as it happens /etc/openhab/persistence/mysql.persist

Strategies {
default = everyChange
}

Items {
* : strategy = everyChange, restoreOnStartup
}

 

Restart OpenHAB and enjoy your data logging

 

 

Sources

https://github.com/openhab/openhab/wiki/MySQL-Persistence

Advertisement