RPi Hassio + Synology NAS Maria DB For logging

Tags: #<Tag:0x00007f780feadf10>

I have managed to set up a Maria DB on my Synology NAS and consume logs from my Pi!
Its was fairly easy using the Synology packages and one config.yml change - although I still have to sort out the account settings. I will detail the steps shortly. I’m just so pleased I wanted to share this with the community right now!

I’m not a Linux guru or DBA so this may be in layman’s terms as far as possible. Just bear with me…

OK so here is the hardware details:

  • Raspberry Pi 3 B with 16G SD card
  • Hassio 64bit image
  • Hassio version 0.75.3
  • Synology NAS DS211j
  • Synology DSM DSM 6.2-23739 Update 2

Objective: Following my pi crashing on me probably due to a corrupted or over-written SD card I have been looking at how to store all the log data externally and preserve the life of my SD cards.
The plan is to set up a SQLite Maria DB on a Synology NAS and using the recorder function within Hassio to point the log data to the Maria DB. I should also note that my rpi and NAS both reside on my home network.

The plan has the following key steps;

  1. Set up the Mari DB environment on the Synology NAS
  2. Create an empty database to receive the data
  3. Set up a account to enable Hassio to access the database
  4. Redirect the logs to the external database

This will take no more than an hour of your time!

STEP 1 - Set up the Mari DB environment on the Synology NAS

Using the Synology Package Centre install Maria DB10 and the phpMyAdmin packages. I think that Maria DB is the database system and PHPAdmin is the management utility. This will take around 10/15 minutes depending on your network connection. Once this done the icons for these packages will be available in the Synology DSM GUI (desktop).
Once this is done we can move onto Step 2.

STEP 2 - Create an empty database to receive the data.

To create the database we need to use phpMyAdmin so in the Synology GUI. Click on the phpMyAdmin icon and it will bring up a warning message that is asking you to reset the password. I cannot recall the exact the wording but I do recall thinking “that’s odd I haven’t actually set any password to change yet”. to clear this hurdle there are two possible routes;

  1. in the username field field type “root” (it may be prefilled with “Admin” so delete this) and clear the password field so its blank. Press Ok and this may bring you to the next screen. (I say may because I didn’t try this route, I only read about it later!
    2 Go back to your DSM GUI and click MariaDB 10 icon. This will bring up a screen that prompts you to change the root password. If you click these buttons and set a new password lets say “password” you can then return to step 1 above and enter “root”/“password”. Phew!

So now you should have a busy looking screen within phpMyAdmin. On the left hand side there is panel with folder tree structure. At the top is a label “NEW”. Click this.
A new screen will appear.
In the window under Create Database type in the name of the new database you are wanting to create. Lets say “harepository”. I’m not sure if this can be mixed case but I stuck with just lower case. I think underscores are permitted.
In the window next to the database name it will say “Collation”. Select this window and from the drop down menu select option “utf8_bin”
Press create and after a few seconds your database has been created. In the left hand panel your new database will be listed in the folder tree. So onto the next step…

STEP 3 - Set up a account to enable Hassio to access the database

In the left hand panel your new database will be listed in the folder tree. Select the label of this - for example “harepository”. The main panel will refresh and a tab will appear at the top “User Accounts”.
AT this point I need to provide a warning. To get this running I have kept this very simple/default and therefore probably not adhering to best security practice. Especially if you have remote access to your NAS (I dont). I do need some assistance from those that know more about DB accounts to correct this so any feedback will be appreciated. But for the time being lets just get the connection up and running. Moving on…hers the settings in each line of windows:
User Name - Use Text Field - root
Host Name - Any Host - %
Password- Use Text Field - use password for root

For good measure I also set Global Priviledges - Check All. I dont know what this does but in for a penny and all that - see the warning above.
When this is done press GO and the hassio user account will be created.
Before we leave the NAS for minute or two, take look at the tree structure in the left hand panel again. You’ll see your new data base but unlike the others in the list it has minus sign against it and not a plus. This is because the database that we have created is just a shell - it has no structure, properties or data. This will all be created automatically when Hassio connects to it. So reember this view and if it changes that will be good a sign.

Now we need to leave the NAS and open up your Hassio config.yml file because we are now on the final step.

STEP 4. Redirect the logs to the external database.

In your YAML file check that you do not have any lines like this;
recorder:

If not then paste this in;

# Redirect output to Synology NAS Maria DB
recorder:
db_url: mysql://hass:[email protected]:3307/harepository?charset=utf8

(there’s two spaces in front of the d in the last line)

and save it.

If you use MariaDB 10 you need to add port 3307 to the SERVER_IP, e.g., mysql://user:[email protected]_IP:3307/DB_NAME?charset=utf8.
So in our example this may look like something this:
db_url: mysql://hass:[email protected]:3307/harepository?charset=utf8
I’ve made up a user name as it seem that the database will let anybody in with root permissions - really not secure.

To avoid any confusion the recoder parameter is detailed here:

OK, so with that all done you need to reload your config.yml and restart hassio.
Once this is done and its back up and running, log onto hassio and check the logs on the information screen. If its all OK there should be no sqllite errors messages.

Next, flip back to your NAS. Refresh your phpMyadmin screen and now there should be cross next to your database. If so, this is is good, the database structure is being loaded.
Highlight eh database in the panel.
Start doing some stuff with hassio such as turning things on and off etc and after a while the phpMyadmin panel in the NAS will start to indicate that eh database is beginning to be populated with more lines and rows. You can explore the features of phpMyAdmin and find out how to view the contents of the logs. But don’t pres the DROP option as I think this may delete tables.

So that’s it. Now you can collate tons of data and look to use Grafana or something to analyse it all, and hopefully your SD card will no longer get hammered.

15 Likes

@bungo63
What great timing! :smiley: Just this weekend I started looking for a way to free up some resources on my Pi and :tada: voilà :tada: this great tutorial appeared!

Thanks for taking the time to write this up and share with the community! I had always been afraid to try setting something like this up but by following your instructions it worked on the first try!

I followed pretty much everything except for two items:

  1. I created a separate username/password for the database and chose ‘Grant all privileges on database’ instead of the root user and Global privileges option.
  2. For the recorder entry, I leveraged secrets.yaml for the actual URL string and referenced it in configuration.yaml with:
recorder:
  # Redirect output to Synology NAS MariaDB 10
  db_url: !secret mariadb_url

So far everything seems to be working great! The Pi feels snappier in general, the History/Logbook pages load much faster, and the system load as shown by the systemmonitor sensor is significantly lower (5-10% vs 25-30% avg.).

Thanks again very much for sharing this awesome tutorial! :grin:

I had been struggling to get it setup the two times I tried, but I admit, it didn’t put much effort into looking into why it didn’t work, but will sit down when I have a free moment, and work through this, and sure I will get it work, was on a list of things to do. Will report back.

Thank you for the feedback. I’m glad you found it useful. When I finally get my set up stable again (I’m having a lot of problems with all the upgrades recently) I will look into using something like Grafana to use the data that is being stored on the Synology Maria DB.

I’ve never used !Secrets and this is something else I wanted to get around to eventually.

I will revisit the DB username and password as per your example. I know that using root is a really bad approach but I don’t know much about databases so I thought if I “went large” to get it working I could then start to trim things down later. I also secretly hoped that someone would respond with a suggestion :slight_smile:

Hi, is this still working for you?
I set purge_keep_day to 7.
But after 7 days nothing is purged and my recorder stops working.
I have to delete or emtpy the whole DB manually on my NAS. Then the recorder starts working again.

Just made it and work fine for the moment , my db start to grow i will let growing for 3 days i set 3 days in my setting…

Ah, thank you @bungo63 !

I was just trying this with Maria on my Synology, and I was having no luck, because the recorder component documentation said I should use:

mysql+pymysql://user:[email protected]_IP/DB_NAME?charset=utf8

Getting rid of the pymysql bit and adding the port number got everything working. I’ll try and update the docs.

This worked perfectly on my DS218 with one small exception noted below… HUGE THANKS! I immediately saw faster response in Hassio even just navigating between screens and clicking on the logbook or history immediately brings them up.

The only thing I had issue with was trying to launch phpMyAdmin from the Synology screen. I figured out I had to use this URL… http://your_nas_ip/phpMyAdmin/ but all else was perfect, very easy to follow instructions.

Another note the database file on your Pi can be deleted afterward… /config/home-assistant_v2.db is no longer needed.

Thank you bungo63 for the excellent post! My hassio/homeassistant performance was poor (opening history etc), even when running it on ESXi 6.5 and SSD datastore. So I followed your guide and got mariadb10 running on my Synology DS415+. The performance was excellent.
However my Synology volume utilization increased from 1% to a constant of 25-30% because homeassistant was writing to the db.
In the end I installed the MariaDB add-on package in hassio (https://www.home-assistant.io/addons/mariadb/), and pointed my recorder to it.
Currently its running well, but I will wait and see once more data has accumulated. Maybe the default sqlite database was just not up to the job.

what happens if the database is unavailable? Automation will still continue to work ?

It’s a good question! I’m interesting in too.

HA and automations will continue to work, but nothing will be stored in recorder database. automations might be reset on reboot to whatever was its last known state in recorder (unless you set initial states).

do you have some suggestion about how to purge events on the mariadb on the synology?

I get the following error:

Component error: db_url - Integration ‘db_url’ not found.

Anyone recognize this (I am a real newbie)

Thanks in advance

You’re probably missing a “recorder:” line above that. db_url is a parameter, not the integration. “Recorder:” is the integration.

recorder:
  db_url: mysql://hass:[email protected]:3307/halog?charset=utf8
  purge_interval: 1
  purge_keep_days: 10

I know this is a little dated, but this worked great and seems to have made the RPi 3 little faster. My QNAP NAS has a Mariadb built into it. Just needed to start it up, install phpMyAdmin and follow your instructions. I also excluded a lot of stuff that just don’t need to be logged (IMO). The history and logbook are now a lot faster than they were - I used to avoid the history and logbook because I’d have to wait too long - not any more! And my RPi CPU is lower (not running a Mariadb server) and the free memory is consistently higher.

Thanks for the post!!

Note that all the Mariadb servers I’ve seen use port 3306 instead of 3307.

@Peter_Verhey - did you get this figured out?

There is a significant performance increase by switching from sqlite to Mariadb. But remember that the add-on completely runs on your HA system (Rpi?). That will continue to write data to the SD card, which reduces the life of the card and takes effort from your RPi to run the Mariadb server.

Ha there, thank you.
I have rolled back the configuration because performance was very poor…

@bungo63

Just followed your steps. All good. Can confirm it works with:

  • Raspberry Pi 4 with 16G SD card
  • Hassio 64bit image
  • Hassio version 0.103.5
  • Synology NAS DS216j
  • Synology 6.2-23739 Update 2

Great stuff - Thanks! :+1:

I kept getting this error host 192.168.1.12 is not allowed to connect to this mariadb server

I fixed it by configuring MariaDB to allow remote connections. It was a pain in the butt because I kept restarting to see if my changes fixed anything. I ended up using MySQL Workbench to test my changes by connecting to the MariaDB on the Synology box

This is the command i use to enable remote connection

GRANT ALL PRIVILEGES ON *.* TO [email protected] IDENTIFIED BY ‘user_password‘ WITH GRANT OPTION;