How to transfer MySQL (JDBC) persistence to your NAS

Tutorials & Examples
1

This tutorial should explain how to move the persistence running on the OH runtime system (in my case a raspberry pi) to your NAS (e.g. Synology).
The goal is to reduce the write access to the SD-card of the RPi to reduce the probability of SD-card corruption.
My assumption is, that your jdbc persistence is already running on your OH (here 2.1 stable).

  1. Install MariaDB on your NAS (please note: The standard port on synologies NAS’ mariaDB is 3307, not the MySQL default of 3306)
    This will also install all required sub components (e.g. phpmyadmin)
  2. Connect to your NAS via ssh
    ssh admin@youripadress (for synology admin is the standard admin and root might not be allowed to log in remotely via console)
  3. Verify the location of your MySQL executable instance – in my case it was mariaDB10: sudo find . -name mysql (from root folder)
  4. Connect locally to the MariaDB:
    /usr/local/mariadb10/bin/mysql -u root –p
  5. Proceed with this tutorial by @Christian_V point 2 at “Create a database for OpenHAB”
  6. Additionally for remote access:
    CREATE USER ‘openhab’@’%’ IDENTIFIED BY ‘yourpassword’;
    And
    GRANT ALL PRIVILEGES ON openhab.* TO ‘openhab’@’%’;
    (% means remote access from all IPs in your network)
    And
    flush privileges;
  7. Set your jdbc.cfg according to your setup:
    url=jdbc:mariadb://192.168.178.39:3307/openhab
    user=openhab
    password=xxxxxx
  8. After a restart of OH or reload of jdbc.cfg you should be able to see the items being generated in your mariaDB
  9. In case you would like to migrate your old DB data to your new DB, this could be easily done by phpMyAdmin (export / import) – I actually tried it with HeidiSQL which worked like a charm as well.
    Please note, that you should drop all tables generated during the test on your NAS first. Otherwise the item_IDs will be messed up when you import the DB structure from your OH persistence.
1 Like