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;
- Set up the Mari DB environment on the Synology NAS
- Create an empty database to receive the data
- Set up a account to enable Hassio to access the database
- 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;
- 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:password@your-nas-ip-address: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:password@SERVER_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.
Just this weekend I started looking for a way to free up some resources on my Pi and 
voilà 
