Page 1 of 1
Database external on Synology NAS or Provider
Posted: Fri 12 Feb 2021 2:15 pm
by HansR
I have MariaDB 10 on a synology NAS, name password port (3307) etc... is all OK.
I can access the database by PhpMyAdmin and defined the server to be '%'.
However, I still can not access the database through Cumulus which is running on my RPi.
I have a similar situation when working with the database on my provider account.
CMX says:
The logfile says:
Code: Select all
MySQL Create Table: Error encountered during MySQL operation.
Can't connect to MySQL server on 'ip-address' (10061): Connection refused
So anybody similar experiences? Do I have to do something with the port (and if so where)? Do I miss something obvious?
Re: Database on Synology NAS
Posted: Fri 12 Feb 2021 2:24 pm
by mcrossley
Have you granted access from your pi's hostname to the account you use to log in? By default only "localhost" is permitted.
Re: Database on Synology NAS
Posted: Fri 12 Feb 2021 2:50 pm
by sutne
Providers does not always allow remote sql-access:
viewtopic.php?p=144019#p144019
Re: Database on Synology NAS
Posted: Fri 12 Feb 2021 3:05 pm
by HansR
mcrossley wrote: ↑Fri 12 Feb 2021 2:24 pm
Have you granted access from your pi's hostname to the account you use to log in? By default only "localhost" is permitted.
I gave access to 'HansR'@'%' that should be enough isn't it?
Re: Database on Synology NAS
Posted: Fri 12 Feb 2021 3:06 pm
by freddie
sutne wrote: ↑Fri 12 Feb 2021 2:50 pm
Providers does not always allow remote sql-access
This is a MariaDB installation on Hans' LAN so the provider is not involved.
@HansR: have you commented out the "bind-address" line in your MariaDB configuration? Typically it is in /etc/mysql/mariadb.conf.d/50-server.cnf
Re: Database on Synology NAS
Posted: Fri 12 Feb 2021 3:08 pm
by HansR
Yes, I guess that is true so I asked them for a solution. But it could be that it is something more general so I asked here as well.
And see my previous answer to @mcrossley for how I defined the user.
@freddie: yes but @sutne refers to my remark about my installation on my provider site. So his response is relevant as well.
Re: Database on Synology NAS
Posted: Fri 12 Feb 2021 3:08 pm
by mcrossley
HansR wrote: ↑Fri 12 Feb 2021 3:05 pm
mcrossley wrote: ↑Fri 12 Feb 2021 2:24 pm
Have you granted access from your pi's hostname to the account you use to log in? By default only "localhost" is permitted.
I gave access to 'HansR'@'%' that should be enough isn't it?
And did you change the server binding from 127.0.0.1 ?
https://www.digitalocean.com/community/ ... s-to-mysql
Re: Database on Synology NAS
Posted: Fri 12 Feb 2021 3:25 pm
by HansR
And @sutne your solution requires a daily copy of the datafiles and a daily run of the import script.
I at most regard that as an intermediate solution because CMX can fill in the records in realtime so I go for the live db connection
Re: Database on Synology NAS
Posted: Fri 12 Feb 2021 3:47 pm
by HansR
mcrossley wrote: ↑Fri 12 Feb 2021 3:08 pm
HansR wrote: ↑Fri 12 Feb 2021 3:05 pm
mcrossley wrote: ↑Fri 12 Feb 2021 2:24 pm
Have you granted access from your pi's hostname to the account you use to log in? By default only "localhost" is permitted.
I gave access to 'HansR'@'%' that should be enough isn't it?
And did you change the server binding from 127.0.0.1 ?
https://www.digitalocean.com/community/ ... s-to-mysql
OK:
- On the Synology you go to and create the file my.cnf.
- Edit the file (very likely you have to use vi) and add the line
But then (it is really shamefully simple
) you click on MariaDB icon in the package centre (or on the desktop wherever you put it) and tick :
(see image). That does the trick.
Schermafbeelding 2021-02-12 164244.png
As the standard installation does not have the bind line in the default cnf files I am not sure the above my.cnf is required. Next time I would just try the tick of the TCP/IP connection and see what happens.
If that does not work you can always create the my.cnf
I'll get back on the remote server issue.
Re: Database on Synology NAS
Posted: Fri 12 Feb 2021 4:35 pm
by sutne
HansR wrote: ↑Fri 12 Feb 2021 3:25 pm
And @sutne your solution requires a daily copy of the datafiles and a daily run of the import script.
That is done by a cron job each night (same as the cumulusutils thrifty job
).
But I must admit the 1. of each month I have to change the name of the file to be imported in crontab.
Re: Database on Synology NAS
Posted: Fri 12 Feb 2021 4:58 pm
by HansR
sutne wrote: ↑Fri 12 Feb 2021 4:35 pm
That is done by a cron job each night (same as the cumulusutils thrifty job
).
But I must admit the 1. of each month I have to change the name of the file to be imported in crontab.
Well, CumulusUtils has the added functionality/data combination of course
But anyway, that name change is a bit awkward I think. I will try to get the info on the external database at the provider out as well such that everybody can make use of it. My guess is it is only the permittance of the port through the firwall(s) and the provider has to set MariaDB for the server-binding.We will see. It works fine now with the NAS so why not with the provider
(true, i can think of some arguments)
Should be great to have it all running without having to think about it every first of the month
Re: Database external on Synology NAS or Provider
Posted: Fri 12 Feb 2021 5:08 pm
by mcrossley
How are you uploading the file? If you use CMX Extra Files, then you could upload to the same remote filename all the time.
Re: Database external on Synology NAS or Provider
Posted: Fri 12 Feb 2021 8:54 pm
by HansR
btw... what happens if the database is out of the air for some time?
Does Cumulus catch up one way or another or is that the responsibility of the user to keep the database in sync with the logfiles?