MySQL help wanted

[Richard Russell]

Does anybody here know how to setup a MySQL database for remote access? I can successfully connect to public databases such as 'ensembldb.ensembl.org' and 'mysql-rfam-public.ebi.ac.uk'; what I am now trying to do is to create my own database to which I can connect in the same way.

I have (I think) created the database at my web host, which can be accessed locally (for example from cPanel), but I haven't discovered how to make it accessible remotely via a URL like those above. Any help would be greatly appreciated.

Soruk

Check the mysqld.conf file for a "bind-address" directive, comment it out with a # and restart mysqld.

More details here - https://linuxize.com/post/mysql-remote-access/

BigEd

It's possible your web-hosting provider has a separate collection of database servers, and your database is on a server together with many others, and the machine it's on is not the machine which you use to serve your website. In which case, your provider might have provided a way for you to configure remote access to their database server, or might not. Or it might be public, but you don't presently know the name of the machine.

BigEd

I see in one of my notes-to-self that at one point, to allow remote access to a hosted database, I needed to "add an access control entry to the remote sql control panel." And to make it work I needed a public DNS name for the IP address I intended to connect from.

[Richard Russell]

Soruk wrote: »

Check the mysqld.conf file for a "bind-address" directive, comment it out with a # and restart mysqld.

Where am I likely to find that file (given that this is a shared server, not a local machine to which I have root access)? And is this going to be safe given that there are other MySQL databases which must not be disturbed (specifically those which support bbcbasic.co.uk/forum and lbbooster.com/forum)?

Soruk

Is your client failing to authenticate from your remote connection, or is it failing to establish a connection to port 3306?

[Richard Russell]

BigEd wrote: »

I needed to "add an access control entry to the remote sql control panel."

Where do I find the "remote SQL control panel"? The only access I currently have to the databases, that I am aware of, is via phpMyAdmin in cPanel.

And to make it work I needed a public DNS name for the IP address I intended to connect from.

I want this to be public, i.e. connectable to from anywhere (just like the two public databases I gave the URLs for earlier). The whole idea is to use it in a program demonstrating database access from BBC BASIC, that anybody can run (so the database will contain only temporary test data).

[Richard Russell]

Soruk wrote: »

Is your client failing to authenticate from your remote connection, or is it failing to establish a connection to port 3306?

It's failing to make a connection to port 3306. But it's probably more fundamental than that, since presumably (like the public databases I listed) I would need to connect to something like mysql.bbcbasic.co.uk:3306, not at the top level bbcbasic.co.uk:3306, and I don't even know how to link such a subdomain to the database!

BigEd

Note that there may not be a remote SQL control panel - a forum I help with migrated from one provider to another and we lost that feature and also lost remote access to SQL, so far as I could tell. A chat with your provider might clarify what's on offer and how to access it. (As I no longer have access to a setup with a remote control panel, I can't say much about how I got access to it, sorry.)

Soruk

Thinking about it, with quite important content on your DB server, setting up inbound connections to it from the outside world is probably not the most sensible option - one zero-day and it's toast. If I were to set up a static demo DB I would use a dedicated instance, with the content on a read-only volume (e.g. squashfs or a CD ISO9660 image).

[Richard Russell]

Soruk wrote: »

If I were to set up a static demo DB I would use a dedicated instance, with the content on a read-only volume (e.g. squashfs or a CD ISO9660 image).

The thing is, though, that a read-only DB doesn't let me demonstrate creating tables, updating the database etc. Anyway, if I want a static read-only database I've already got those two public ones available, which are perfectly suitable for the purpose.

So are you effectively saying that what I am hoping to achieve is impossible? That I can't set up public access to one database without at the same time compromising the two existing databases used for the forums? That seems a surprising limitation.

Soruk

In theory it should be fine, but that is making the assumption that no bug exists that could allow a privilege escalation and allow a malicious actor to damage other tables on your server

Demonstrating creating tables and adding content, yes, that makes sense you'd want a read-write setup but for your forums security I would still be suggesting a dedicated instance.

(I'm a sysadmin and it's stuff like this I have to think about every day)

[Richard Russell]

Soruk wrote: »

I would still be suggesting a dedicated instance.

Fair enough. In which case can you assist with how I can set up my existing database for private remote access, just from my own static IP address (which I've already added as a MySQL host, and is presumably safe)? That's still of value to me for testing purposes, even if I cannot make it available publicly.

Soruk

Since you have a static IP, you could set up a small MySQL instance on a Raspberry Pi, if you have an old Pi 1 gathering dust it would do the job, give it an internal static IP and forward port 3306 to it on your router.

Unfortunately I have no experience with these hosting provider control panels, every MySQL/MariaDB server I have built has been on a server, using the command line to set up and administer.

[Richard Russell]

Soruk wrote: »

Since you have a static IP, you could set up a small MySQL instance on a Raspberry Pi

Sounds like a plan, but one for another day.

Soruk

I can help build you an SD image if you like.

[Richard Russell]

Soruk wrote: »

I can help build you an SD image if you like.

As always, it's silly practical things that stop me doing it straightaway. I've got a spare Raspberry Pi, but nowhere with both a handy network connection (I'd prefer not to use WiFi for a server) and a convenient source of power. One or the other, but not both.

[Richard Russell]

Soruk wrote: »

Unfortunately I have no experience with these hosting provider control panels

I placed a support request with my hosting provider and they have enabled remote access - no idea exactly what they did - so I am now able to connect to bbcbasic.co.uk:3306 (although not yet to the database, but that's likely to be a problem with my BBC BASIC code because I am seeing responses from MySQL).

They didn't express any security concerns but I've currently set it so that it will only accept connections from my static IP address so hopefully that will reduce risks to an acceptable level (unless you consider otherwise).

I may yet want to install a Raspberry Pi server here to provide a database with public access, but the urgency for that is gone so I'll get back to you about that, as and when. Thanks for the help so far.

Soruk

I can confirm that my own IP was refused access.

Also, MySQL users can be configured to only be permitted access from specific IPs (or specify 'username'@'%' to allow access from any).

[Richard Russell]

Soruk wrote: »

Also, MySQL users can be configured to only be permitted access from specific IPs (or specify 'username'@'%' to allow access from any).

Yes, although a (much criticised) change to cPanel has removed that capability from phpMyAdmin so I'm not sure in practice I could configure a user that way.

[Richard Russell]

Richard_Russell wrote: »

I am now able to connect to bbcbasic.co.uk:3306 (although not yet to the database, but that's likely to be a problem with my BBC BASIC code because I am seeing responses from MySQL).

Indeed it was an issue with my understanding of the MySQL protocols. According to the docs, if the initial 'assumed' authorisation method is supported the server can proceed without further ado, and the public databases I had tried previously worked that way.

But with the version of MySQL running at bbcbasic.co.uk it asks to change the authorisation method - to the one I had already assumed! No idea why, but I've modified the BASIC code to accept that response and send the encrypted password again, and now it all works.

As you seem to be something of a MySQL guru (BigEd too), would you care to propose a sequence of operations that I could perform as a test/demonstration that my code, which I intend to release as a BBCSDL library, works properly (I expect it will work in BB4W too).

I'm thinking of something like create a table, populate it with some data, run some queries on it and then delete it.

[Richard Russell]

Richard_Russell wrote: »

As you seem to be something of a MySQL guru (BigEd too), would you care to propose a sequence of operations that I could perform as a test/demonstration

Unfortunately I have no experience of databases at all, other than performing a trivial query to test the old ODBCLIB library for BBC BASIC for Windows. Specifically, I know nothing about how to structure, create and populate tables. So I am completely out of my depth when it comes to testing my new mysqllib library.

If nobody here is able to help, can you suggest somebody who might?

[Richard Russell]

Soruk wrote: »

Since you have a static IP, you could set up a small MySQL instance on a Raspberry Pi, if you have an old Pi 1 gathering dust it would do the job, give it an internal static IP and forward port 3306 to it on your router.

I've found an old Pi 1, but I'm wondering whether it would be wise to publicise my static IP address in a BASIC program, which I would have to do for that solution to work. Do you consider that to be a security risk?

Soruk

I don't think it should be a security risk, just make sure you're not forwarding anything else in, just port 3306 to your Pi1 (which needs to have an internal static IP, either configured on your router so it's assigned by DHCP, or on the Pi itself.)

If you're making this public, I'd suggest in your code creating a random string, and using CREATE DATABASE <string> so users don't collide with each other on the off-chance more than one runs the demo at the same time. Of course, that means the demo user would have considerable permissions on the database server to be able to do this, another reason for using a dedicated machine so it can't interfere with anything critical.

[Richard Russell]

Soruk wrote: »

I don't think it should be a security risk, just make sure you're not forwarding anything else in, just port 3306 to your Pi1

Except that of course I am forwarding other ports! I need to in order to provide remote access to our Home Automation system, our Solar Panel/Battery system and our CCTV system. So if our IP address becomes more widely known they all provide targets for attackers, and because they are commercial products there may be known vulnerabilities that could be exploited.

So I have decided not to publicise our static IP address unnecessarily. Instead, for the purposes of demonstration I will rely solely on public MySQL databases. Needless to say they are read-only so I cannot illustrate creating or modifying data, but neither did I in the equivalent demonstration program for BB4W. But they are fine for interrogating the database in various ways.

If somebody was to show an interest in using my new library to create or modify a database (and they have a static IP address) I can add them to the ACL for bbcbasic.co.uk:3306 so they can experiment with that in relative safety.

Soruk

Sorry, should have been more clear. Only forwarding 3306 to your Pi, not any other ports to it. Other ports you have for other purposes are fine. (I have lots of ports forwarded).

Also, you don't have to announce the existence of a service on a particular IP for miscreants to find other things, there are constantly scans going on - I reguarly see hits for SSH on not only my machine, but every box at work with a public IP. (Those ones I tend to only configure sshd to listen on the internal private IP. Doesn't stop them from trying though, and where ssh needs to listen on the public IP I disable root login by password.) Another I see a lot of is 5600/udp (SIP), which is awkward as I also have my own VoIP server, so I restrict SIP traffic to the upstreams I use. IAX2, on the other hand, is practically ignored so I use that for my mobile client, also IAX2 is MUCH more NAT-friendly!

But yes, I appreciate your concerns. I also have solar and a CCTV setup.

[Richard Russell]

Soruk wrote: »

you don't have to announce the existence of a service on a particular IP for miscreants to find other things, there are constantly scans going on

Yes, but I think the weakness of that argument is that those speculative scans are 'un-targetted', they aren't specifically attacking me. Whereas if I publish my static IP address anybody who particularly wants to cause me inconvenience (or worse) has the opportunity to throw everything at that one specific address.

Another I see a lot of is 5600/udp (SIP), which is awkward as I also have my own VoIP server

My router handles VoIP internally (a VoIP-to-DECT bridge is built-in, which is very handy) so I don't need to forward any ports for that to work - although I am probably at the mercy of the quality of the software in the router to provide protection.