MySQL 8.0.20 via SSH

Question

DBeaver 7.1.0 on Windows 10 trying to connect to MySQL 8.0.20 on Ubuntu 20.04 using SSH. I also tried other Windows MySQL GUI clients, with the identical results, which leads me to believe the problem is with MySQL configuration (or maybe with my local Java installation?).

The error message is:

Communications link failure The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server. Can not read response from server. Expected to read 4 bytes, read 0 bytes before connection was unexpectedly lost. 

MySQL version information:

# mysql --version mysql Ver 8.0.20-0ubuntu0.20.04.1 for Linux on x86_64 ((Ubuntu)) # mysqld --version /usr/sbin/mysqld Ver 8.0.20-0ubuntu0.20.04.1 for Linux on x86_64 ((Ubuntu)) 

From Windows command line, I can ssh to my server and connect to my database without issue:

FROM WINDOWS COMMAND LINE: ssh [email protected] FOLLOWED BY THE FOLLOWING ON THE SERVER: $ mysql -u admin -p 

This is a fresh MySQL install without any config changes. The only thing I did was create a dummy database ("main") and a new database user ("admin") and grant the user access to the database.

CREATE DATABASE main; CREATE USER 'admin'@'localhost' IDENTIFIED BY 'XXX_PASSWORD_REDACTED_XXX'; GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost'; FLUSH PRIVILEGES; SELECT user,plugin,host FROM mysql.user WHERE user='admin'; +-------+-----------------------+-----------+ | user | plugin | host | +-------+-----------------------+-----------+ | admin | caching_sha2_password | localhost | +-------+-----------------------+-----------+ 1 row in set (0.00 sec) 

In DBeaver, clicking on "Test tunnel configuration" results in success, so SSH tunnel is fine. But clicking on "Test connection" results in an error. See below for the error along with screenshots of my config. Note that I tried using the "MySQL 8+ driver" as well as the "MySQL driver". I also tried using both JSch and SSHJ with the same result. I also tried connecting using 127.0.0.1 instead of localhost, with the same result. I also tried changing the user's plugin from caching_sha2_password to mysql_native_password, with the same result.

MySQL bind-address is the default:

# grep -Irs bind /etc/mysql mysql.conf.d/mysqld.cnf:bind-address = 127.0.0.1 

IPTables is as follows:

# iptables -L -v -n Chain INPUT (policy DROP 328 packets, 18119 bytes) pkts bytes target prot opt in out source destination 11517 76M ACCEPT all -- * * 0.0.0.0/0 0.0.0.0/0 ctstate RELATED,ESTABLISHED 141 32322 ACCEPT tcp -- * * 0.0.0.0/0 0.0.0.0/0 tcp dpt:22 10 492 ACCEPT tcp -- * * 0.0.0.0/0 0.0.0.0/0 tcp dpt:80 10 576 ACCEPT tcp -- * * 0.0.0.0/0 0.0.0.0/0 tcp dpt:443 Chain FORWARD (policy ACCEPT 0 packets, 0 bytes) pkts bytes target prot opt in out source destination Chain OUTPUT (policy ACCEPT 6069 packets, 647K bytes) pkts bytes target prot opt in out source destination 

Would appreciate any ideas on how to debug further!

Screenshots below:

DBeaver MySQL 8+ Driver DBeaver SSH DBeaver Error Message

Answer 1

Ugh...never mind...figured it out. I forgot to allow loopback connections. Fixed by running: sudo iptables -A INPUT -i lo -j ACCEPT sudo iptables -A OUTPUT -o lo -j ACCEPT

Answer 2

I got this same error, but it turned out that the remote MySQL server was not listening on 127.0.0.1:3306, but was listening on an internal IP address on another network adapter.

You may want to check the output of ss -tnlp to check which IP has the server.

If you find MySQL is listening on an internal IP instead of loopback and the SSH server allows TCP forwarding, you can still connect from DBeaver using SSH forwarding by opening up "Advanced settings" under the SSH tab and setting "Local host:" to 127.0.0.1, "Local port:" to 3306, "Remote host:" to the internal IP of the database server, and "Remote port:" to MySQL's listening port on the server (normally 3306).