sshmariadbredhatssh-tunnel

MariaDB Over SSH Tunnel Lost Connection


I have two RHEL9 servers. I turned off SELinux on both using "sudo setenforce 0" to make sure that has nothing to do with this issue. I am on server 'admin' and I am trying to connect to mariadb on 'webs' through SSH port forwarding. This is what I get:

$ ssh -f -L 3306:127.0.0.1:3306 webs sleep 60
$ mysql -u root -p -h 127.0.0.1
(enter password)
ERROR 2013 (HY00): Lost connection to MySQL server at 'handshake: reading initial communication packet', system error: 11

On both, I verified they allow TCP forwarding because I feel that is required:

$ sudo sshd -T | grep -i 'forward'
x11forwarding no
allowtcpforwarding yes
allowagentforwarding yes
disableforwarding no
allowstreamlocalforwarding yes

If I am on webs, I can connect to MariaDB without trouble:

$ mysql -u root -p -h 127.0.0.1

That works. I can do it on localhost as well. But, from admin, I get the handshake error.

Attempting to identify the problem has been very difficult because:

  1. This is no MySQL. It is MariaDB.
  2. This is Red Hat (RHEL9), not Ubuntu Therefore, I've gone through the first 10 pages of results on both Google and Bing and nothing has provided any help. Along the lines, MariaDB formed an issue where I can no longer "select * from mysql.user", which is another impossible to find a solution for problem.

I am happy to troubleshoot, but step 1 is finding the logs. Where are they? What is logged where? Nothing gets recorded to /var/log on either side except the SSH login to webs in /var/log/secure. I looked at /var/log/mariadb/* files. Nothing there. Without any notation in a log file or a description of what exactly error 11 is, I've spent days uninstalling MariaDB, reinstalling Mariadb, uninstalling Mariadb, reinstalling MariaDB, etc...

Final note: I am not allowed to open a firewall port into webs for admin to connect directly. All connections between servers must go over SSH. Not my choice. No need to make snide remarks about it.


Solution

  • After a great deal of headache, the problem is solved. The directory /var/lib/mysql was owned by root. MariaDB uses that directory to store data. One of the things it stores is a login attempt. I changed the owner to mysql:

    sudo chown mysql:mysql /var/lib/mysql
    

    Now, I can login.

    I found it by troubleshooting login hosts. If I logged in from localhost (on the local host, of course), it worked. If I logged in on 127.0.0.1 either through a tunnel or from the localhost, it failed. Knowing that, I could monitor file access and I was able to pick out the access error that was not logged anywhere.

    Now, even knowing the problem, I can't find anything about this when searching the web. So, I'm going to reiterate again to try and make search engines notice that this page exists:

    Using MariaDB through 127.0.0.1 fails with the error:

    ERROR 2013 (HY00): Lost connection to MySQL server at 'handshake: reading initial communication packet', system error: 11

    If root is the owner of /var/lib/mysql, that error will occur. Change the ownership to mysql.