Allow remote MySQL connections

Support
1

Problem Description

Im trying to setup remote MySQL access to my server, however whenever I try, I get:

ERROR 2002 (HY000): Can’t connect to server on ‘IP ADDRESS’ (110)

Ive tried updating the iptables:
iptables -A INPUT -p tcp --dport 3306 -j ACCEPT
ip6tables -A INPUT -p tcp --dport 3306 -j ACCEPT
netfilter-persistent save
netfilter-persistent reload

And I can see they are set to ACCEPT when I run the following:
iptables -L -n -v | grep 3306
ip6tables -L -n -v | grep 3306

However when I run:
netstat -tuln | grep 3306

tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN

This is the only output - no IPv6 output.

Ive changed bind_address to :: in /etc/mysql/mariadb.conf.d/50-server.cnf

And still no luck… Any help appreciated.

Environment

  • Sympl Version: 12
  • Sympl Testing Version:
  • Debian Version: 12 (Bookworm)
  • Hardware Type: Dedicated Server
  • Hosted With: Whoever Bytemark got took over by
2

Hi Daza,

You probably don’t do it that way - there’s no encryption or anything on a MySQL TCP connection (passwords, queries, and results are all sent in the clear), so it would be rather trivial for someone to snoop the traffic, take over MariaDB and compromise the server.

Instead, you’ll want to tunnel the connection over WireGuard or SSH.

SSH is the simplest, and just a case of using something like:

ssh -N -L 13306:127.0.0.1:3306 sympl@<hostname>

This will use SSH keys if you have them, and once you’re connected, you can just connect to port 13306 locally and you’;ll be talking to the remote server.

You can then Ctrl-C out of that when you’re done, or use the -f switch to let it go into the background (but remember to kill that when you’re done).

If you want something more permanent, then WireGuard is fairly easy to set up, and would let you talk to the server as if it’s semi-local.

3

Very good point… Im learning C# at the moment and im a fairly competent self taught PHP developer.

We have a web based system currently (obviously) and I want to be able to access the database from C#. Ultimately our small team would install the exe file I create and then insert the Database credentials when prompted. This would be THIER own login details (MySQL users and permissions are already created)

They would then be able to access everything they have access to from the main database.

Just some thoughts on where I want to go with it. I was just trying to get the MySQL connection setup.

But I understand the security risks involved now youve mentioned it. Obviously there would be no command line for the users.

I guess I need to do alot more research into this then. But the last thing I want is to compromise the integrity

4

Thanks for this. I’ve long had my mariadb firewalled to only accept connections from my home network’s static IPv4 and IPv6 addresses, so nobody else could connect to my database server, but as you say, a snooper could still read data and passwords, which isn’t ideal!

I’ve just set up your SSH line to run when my PC boots and tried it on one site: dead easy to set up and works perfectly!