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

2 thoughts on “OpenHAB & MySQL Persistence

  1. Tanks for your guide, I used it to install MySQL on my Raspberry Pi!

    However I had some problems with the commands in the guide:

    1. the command “mysql -user root -p” did throw the error “ERROR 1045 (28000): Access denied for user ‘ser’@’localhost’ (using password: YES)”
    I had to change it to “mysql -u root -p”

    2. the command “mysql -h services -u openhab_rw -p” throw the error “ERROR 2005 (HY000): Unknown MySQL server host ‘services’ (0)”
    I had to use “mysql -u openhab_rw -p”.
    Note: I created the user and granted permissions for the IP and localhost

    thanks!

    Like

    • Hi Terra,

      I’m glad it was helpful. I’m not sure what happened with the first issue you had, both commands look identical.

      The second command didn’t work since services was the hostname of the machine I installed mysql on, that step was to ensure you could logon from the machine hosting openHAB, looks like that didnt matter for you since it was all on the same host.

      Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s