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
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:
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.
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.
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).