0

I have:

  • A local Windows 10 PC running MySQL Server 8.0.33 on port 33060.
  • A remote Ubuntu 20.04.6 LTS VPS running MySQL Server 8.0.33 on port 3306.

I want to create a reverse tunnel using PuTTY on my Windows PC so that my Ubuntu VPS is able to connect to my local MySQL instance running on port 33060.

For example, I'd like to connect via this command:

username@localhost:~$ mysql -h localhost -P 33060 -u test -ptest

Note: I've already created a MySQL test user on my Windows PC with the following settings:

Name Value
Login Name test
Authentication Type caching_sha2_password
Limit to Hosts Matching %
Password test
Account Limits Unlimited - 0/0/0/0
Administrative Roles Full Privileges - Everything is Checked

However this happens when trying to connect:

username@localhost:~$ mysql -h localhost -P 33060 -u test -ptest
ERROR 1045 (28000): Access denied for user 'test'@'localhost' (using password: YES)

username@localhost:~$ mysql -h 127.0.0.1 -P 33060 -u test -ptest
ERROR 2007 (HY000): Protocol mismatch; server version = 11, client version = 10

Note: If it makes any difference, AllowTcpForwarding is enabled in /etc/ssh/sshd_config on my Ubuntu VPS and the OpenSSH service was restarted via service ssh restart.

Here's my PuTTY configuration:

PuTTY configuration

I've tried all kinds of combinations of settings but my Ubuntu VPS can't connect to my Windows MySQL server.

For what it's worth, I was able to have my Ubuntu VPS successfully connect to PHP's built-in webserver running on my Windows PC with basically the same PuTTY settings (just different ports):

PS C:\test> php -S localhost:9876
[Tue May 23 23:10:50 2023] PHP 8.0.8 Development Server (http://localhost:9876) started
[Tue May 23 23:11:01 2023] [::1]:64617 Accepted
[Tue May 23 23:11:01 2023] [::1]:64617 [200]: GET /
[Tue May 23 23:11:01 2023] [::1]:64617 Closing

username@localhost:~$ curl localhost:9876
Hello World

But getting back to MySQL, is this a configuration issue with PuTTY, MySQL Server on Windows, or something else entirely?

Tyler
  • 101
  • 6

1 Answers1

0

What ended up solving the problem was changing the PuTTY settings to:

PuTTY settings

Apparently, port 33060 uses the X protocol, not the classic MySQL protocol.

From my testing, it seems changing the port makes a difference with the Ubuntu MySQL client (even though the Windows MySQL server port is exactly the same and SELECT @@global.protocol_version; shows 10).

After changing the PuTTY settings, I was then able to connect on my Ubuntu VPS with this command:

mysql -h 127.0.0.1 -P 3307 -u test -ptest

However, running the same command with localhost instead of 127.0.0.1 gives an Access Denied error. After some digging, I found this post which explains:

"In MySQL, the localhost keyword is reserved for connection using the MySQL socket and you should use the ip-address 127.0.0.1 for TCP connections to the MySQL network port on 127.0.0.1."

The ssh equivalent command (built into Windows and enabled in April 2018 update) is:

ssh -R 3307:localhost:33060 username@your_ip -i "C:\path\to\your\id_rsa" -o "StrictHostKeyChecking=no"

To create a persistent reverse tunnel as a Windows service, see this autossh guide.

There are a few oversights and errors in the guide though.

When installing cygwin, you'll need to install these packages:

  • cygrunsrv
  • openssh
  • autossh

Then allow your Windows account to Log On as a Service:

WinKey + R -> gpedit.msc

Computer Configuration -> Windows Settings -> Security Settings -> Local Policies -> User Rights Assignment -> Log on as a service -> Add your username.

To test your autossh connection in a cygwin instance:

autossh -M 20000 -R 3307:localhost:33060 username@your_ip -i "C:\path\to\your\id_rsa" -o "StrictHostKeyChecking=no"

If that works, open PowerShell as an admin (important) and run:

C:\cygwin64\bin\cygrunsrv.exe -I AutoSSH -p /usr/bin/autossh -a '-M 20000 -R 3307:localhost:33060 username@your_ip -i "C:\\path\\to\\your\\id_rsa" -o "StrictHostKeyChecking=no"' -e AUTOSSH_NTSERVICE=yes

Note the double backslashes.

Later, if the service doesn't start and you need to try again, run this command in the admin PowerShell to delete the service:

sc.exe delete AutoSSH

In cygwin you can find the AutoSSH log in /var/log/AutoSSH.log to diagnose errors.

Tyler
  • 101
  • 6