Easy remote sql access

Hi

I’m a complete n00b when it comes to Linux firewall.

Is there not an super easy way to allow external access to the sql server running on my system?

I’m migrating a few servers to Virtualmin and I have some huge (500+ mb each) databases, that would take forever to upload in phpmyadmin (and would also not be allowed unless I change the max upload size).

On my old server I used Sequel Pro to remotely access it, but I can’t get it working in Virtualmin.

It would be nice with a simple “allow remote access” setting you could turn on and off for such jobs :slight_smile:

There is indeed a super easy way to use Virtualmin to permit remote users to connect to MySQL - just set the listening address to ‘any’. There is also a super easy command which can be run to transfer your > 500 MB databases from one MySQL server to another, but I shall tell you about that later in this message; first, here is the answer to your question.

To get MySQL to accept connections from everyone on the net - in Webmin → Servers → MySQL Database Server click the MySQL server options icon. Find MySQL server listening address and set it to any, like so:

You must also configure your firewall to permit access to 3306, the port which MySQL uses - in Webmin → Networking → FirewallD click Add allowed service and choose MySQL, like so:


Oh, dont forget to apply configuration :arrow_heading_up:

Pro Tip: here is how you can transfer a database from a remote MySQL server to the Virtualmin server via the CLI

mysqldump -h remote.tld -u remotedbusername -premotedbpassword remotedbname | mysql -h localhost -u virtualminuser -pvirtualmindbpassword virtualmindbname

You could use this to transfer a database of any size very quickly indeed, even without doing everything described earlier in this message to allow external access to the SQL server running on Virtualmin.

Sweet

Totally missed the FirewallID :slight_smile: and completely forgot about the CLI commands. My skills has been handicapped by using macOS server for too long :smiley:

Thanks for the help.

Don’t forget that once you are finished your migration to reduce the access again. It wouldn’t be sensible to leave your database exposed to anyone in the world, which this solution does.

Yes of course :smiley:

I get this error when applying the FirewallID configuration. I don’t use ip6

### Failed to apply configuration : Error: COMMAND_FAILED: '/usr/sbin/ip6tables-restore -w -n' failed: ip6tables-restore v1.8.2 (nf_tables): line 4: RULE_REPLACE failed (No such file or directory): rule in chain OUTPUT

Hi,
Another way would be to tunnel the connection through SSH, which is probably already allowed on your firewall.

ssh -L3306:localhost:3306 ip-of-server

Then you can use your sql client to connect to the server via localhost:3306.

Nic

Ahh yeah

Never thought about that :slight_smile:

Just to add to the already good responses:

MySQL and MariaDB now support SSL/TLS for remote connections…it can be a little tricky to setup, but highly recommended if you’ll be leaving it open (but you shouldn’t leave it open if you don’t have to). This gives (roughly) the same benefits as running over an ssh tunnel without quite as much fragility/complexity.

You can dump/restore databases from within the Webmin GUI, if you prefer not to hit the command line. Installing Webmin by itself is very non-intrusive, so you could install it on the original server without hurting any existing services. Virtualmin, on the other hand, is extremely disruptive if you run the install script…never do that on a production server.

Webmin can also work with remote databases. So you can use the MySQL module to do stuff on a remote database…you can clone the MySQL module to manage two different database servers from one Webmin instance (just be careful not to get confused about which one you’re working with, and make sure you don’t mess with stuff Virtualmin configures in the primary MySQL module instance). You can rename cloned modules, so maybe call it “Remote MySQL” or whatever, if you do that.

Virtualmin can also work with remote databases (with some minor limitations).

2 Likes

This topic was automatically closed 4 days after the last reply. New replies are no longer allowed.