Database external on Synology NAS or Provider

[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:

Code: Select all

Parse error.

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?

[mcrossley]

Have you granted access from your pi's hostname to the account you use to log in? By default only "localhost" is permitted.

[sutne]

Providers does not always allow remote sql-access:
viewtopic.php?p=144019#p144019

[HansR]

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?

[freddie]

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

[HansR]

Providers does not always allow remote sql-access:
viewtopic.php?p=144019#p144019

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.

[mcrossley]

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

[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

[HansR]

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:

1. On the Synology you go to

   Code: Select all

   /var/packages/MariaDB10/etc

   and create the file my.cnf.
2. Edit the file (very likely you have to use vi) and add the line

   Code: Select all

   bind-address = *

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 :

Code: Select all

Enable TCP/IP Connection

(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.

[sutne]

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.

[HansR]

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

[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.

[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?