The following tutorial is an example how I got MySQL persistence working in my environment.
-
Install “MySQL Persistence” via PaperUI
-
Install and configure MySQL:
sudo apt-get install mysql-server
Here, it will ask for a root password, saying that “While it is not necessary, it is suggested.” Of which it is always a good idea to include a root password. Make sure, of course, to remember this password as well.
Start the MySQL commandline as root
sudo mysql -u root -p
Create a database for OpenHAB
CREATE DATABASE OpenHAB;
Create a user for OpenHAB
CREATE USER 'openhab'@'localhost' IDENTIFIED BY 'YOURPASSWORD';
Grant the user permissions for the database
GRANT ALL PRIVILEGES ON OpenHAB.* TO 'openhab'@'localhost';
Quit the Mysql command prompt
quit
- Restarted openHAB Service and found or created (don’t know anymore) config file under
/etc/openhab2/services$
My mysql.cfg looks like the following (shotened to the important sections):
# the database url like 'jdbc:mysql://<host>:<port>/<database>' (without quotes)
url=jdbc:mysql://127.0.0.1:3306/OpenHAB
# the database user
user=openhab
# the database password
password=YOURPASSWORD
- Create a persistence file under
/etc/openhab2/persistence$
Mine is called “mysql.persist” and it looks something like this:
// persistence strategies have a name and a definition and are referred to in the "Items" section
Strategies {
// if no strategy is specified for an item entry below, the default list will be used
everyMinute : "0 * * * * ?"
every5Minutes : "0 */5 * * * ?"
everyHour : "0 0 * * * ?"
everyDay : "0 0 0 * * ?"
default = everyChange
}
/*
* Each line in this section defines for which item(s) which strategy(ies) should be applied.
* You can list single items, use "*" for all items or "groupitem*" for all members of a group
* item (excl. the group item itself).
*/
Items {
// persist all items once a day and on every change and restore them from the db at startup
* : strategy = everyChange, everyDay, restoreOnStartup
// additionally, persist all temperature and weather values every hour
gTemperatur* : strategy = every5Minutes, restoreOnStartup
I know the persistence file is not optimized and is logging too many values - this is work in progress.
So this works for me and I can login to the MySQL Instance and see the values logged to the tables.
My feedback, corrections or additions welcome. Hope this helps.
Cheers
Christian
