OpenHAB Persistence

Charts Are Cool

On this page

Install MySQL

OpenHAB supports several stores. We'll go with MySQL because it's simple and a lot of people know SQL. Installing it takes just a single line:

sudo apt-get install mysql-client mysql-server
This will take a few minutes, and at one point it will launch an 80s style interface where you enter a root password (twice).

When it's done installing, you can connect to the root DB (you'll need to enter the password you just set):

mysql -u root -p

With the following commands you check the list of default databases, add an "openhab" database, and then check again to make sure it was added. Notice you need to type a semicolon at the end each time. If you forget, just type it on the next line that comes up:

show databases;
create database openhab;
show databases;

Create a user, give him full access to the openhab DB (include the single quotes!) and then exit:

CREATE USER 'openhab'@'localhost' IDENTIFIED BY 'openhab';
GRANT ALL PRIVILEGES ON openhab.* TO 'openhab'@'localhost';
quit;

Tell OpenHAB What We Did

If you've been following guides on this site, you already have the persistence addon in the addons_repo folder. Copy the addon from addons_repo to addons (remember to replace 1.7.0 with the version you have):

sudo cp /opt/openhab/addons_repo/org.openhab.persistence.mysql-1.7.0.jar /opt/openhab/addons/org.openhab.persistence.mysql-1.7.0.jar

If you don't have the addons_repo folder, you can get the binding by downloading the addons zip from the OpenHAB downloads page.

Create a simple config file that says to log all values when they change:

sudo nano /opt/openhab/configurations/persistence/mysql.persist

Contents:

Strategies {
    default = everyChange
}

Items {
    * : strategy = default, restoreOnStartup
}

Now enable the mysql addon in the main config file:

sudo nano /opt/openhab/configurations/openhab.cfg

Look for the SQL Persistence Service section (you can use CTRL+W to search within a file) and set these 4 values. Don't forget to remove the # at the front to enable them:

mysql:url=jdbc:mysql://localhost:3306/openhab
mysql:user=openhab
mysql:password=openhab
mysql:reconnectCnt=1

And finally, restart OpenHAB:

sudo /etc/init.d/openhab restart

(if this command doesn't work, check the Start OpenHAB guide on how to enable it)

Charts!

When OpenHAB is back, check if you can see charts using a URL like this - modified for your Pi's IP address and your sensor's name (as specified in the items file):

http://192.168.1.80:8080/chart?items=MyFirstSensor&period=h&service=mysql

Now we can integrate them into the site (you don't need to restart OpenHAB when editing items or sitemaps)

sudo nano /opt/openhab/configurations/sitemaps/default.sitemap

Add this after the MyFirstSensor text item:

Chart item=MyFirstSensor period=3D refresh=10000

  • period: Valid values are h, 4h, 8h, 12h, D, 3D, W, 2W, M, 2M, 4M, Y
  • refresh: The refresh period of the image (in milliseconds)

OpenHAB Chart
This chart looks weird because I changed the Arduino sketch from Celsius to Fahrenheit to test values changing

Extra: Manage MySQL From Windows

If you want to view and manage the MySQL database from Windows (because you're curious), you can install MySQL Workbench - it's free. First step is to download it. Before you start your download there will be big buttons with Sign Up or Login, but there's also a small link at the bottom that says "No thanks, just start my download".

By default, the MySQL server on the Pi won't allow other computers to access it, so we need to enable that in the MySQL configuration:

sudo nano /etc/mysql/my.cnf
Look for the line that says "bind-address = 127.0.0.1" and comment it out by placing a # at the front.

For the second configuration part you need to go into MySQL again:

mysql -u root -p

Then give access to root from the entire local network (replace "192.168.1.%" below with your local network's IP range if it's different):

GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.1.%' IDENTIFIED BY 'typerootpasswordhere' WITH GRANT OPTION;
FLUSH PRIVILEGES;
quit;

And restart MySQL:

sudo /etc/init.d/mysql restart

Now you can start MySQL Workbench on Windows. Click on the + symbol next to "MySQL Connections" to add a connection to the MySQL server on your Pi:

MySQL Workbench New Connection

When you open this new connection, you'll find your tables in the bottom left. Right click and choose the first option to view the contents of a table. For more information, check out the MySQL YouTube channel.

MySQL Workbench Raspberry Pi Connection

Go Back

Guides