Part 4: Scheduled Database Backups

The last topic I’d like to talk about is backups. While Synology offers backups for your regular files, I did not find a simple, builtin solution to backup your database(s). Luckily you are able to run scheduled scripts in your DSM and MySQL comes with a backup tool.

For the usage of this backup tool you first need to create MySQL user with access to the open database and with access from localhost. Still use a strong password, just to be sure 😉. Next, create a new folder for your backups. After this, open the Control Panel and select the Task Scheduler. Create a new task for root, choose any schedule (I run daily backups) and go to the last tab. Fill in the following command and save the task.

docker exec mysql /usr/bin/mysqldump -u openhab --password=asdf1234 openhab > "/volume1/Backups/database-openhab/backup-"`date +"%Y-%m-%d"`".sql"

Sources:

Trigger the task manually and check the content of your backup directory.

Open one of the SQL files and verify its content.

-- MySQL dump 10.13  Distrib 5.7.30, for Linux (x86_64)
[...]
--
-- Table structure for table `denonavrx2600havrx2600h_mainzone_inputsource_2`
--

DROP TABLE IF EXISTS `denonavrx2600havrx2600h_mainzone_inputsource_2`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `denonavrx2600havrx2600h_mainzone_inputsource_2` (
  `time` timestamp(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  `value` varchar(65500) DEFAULT NULL,
  PRIMARY KEY (`time`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

That’s it!

Conclusion

You installed openHAB on you Synology NAS using Docker, exposed the config editable while keeping the other data private, redirected the logs to Log Center and created a backup task for your Database. This will safe write accesses (even if your openHAB runs on another platform like a Raspberry Pi). I hope this post helped you in some way.

PS: You could even increase the security by not exposing your MySQL database at all. This only works when you don’t run openHAB in host mode (or, of course, on another host) and connect it to MySQL via Docker network. Currently I am using two bindings that requires Host network. I hope to get rid of this some day. For this reason, use a strong password for your database account!

Part 1: Use openHAB with Synology DSM
Part 2: MySQL persistence for openHAB
Part 3: Redirect openHAB logs to Log Center
Part 4: Scheduled database backups with DSM

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.