Bill Carney's picture

Now that v16 is on the horizon I finally got off my butt and migrated my v14 LAMP machine to v15. All went fine except for one minor issue, I can't connect to the database server using local GUI tools (Sequel Pro on the Mac for example) as I could when I was using version 14.  I looked through all my old notes and can't locate what, if anything, I did to make it accessible in 14.

The error message that Sequel Pro kicks off is this:

Unable to connect to host (snip).com, or the request timed out.

Be sure that the address is correct and that you have the necessary privileges, or try increasing the connection timeout (currently 10 seconds).

MySQL said: Lost connection to MySQL server at 'reading initial communication packet', system error: 0

I did a lot of googling and tried all sorts of things, like commenting out #bind-address = 127.0.0.1 in the config file and restarting mysql, all to no avail.

Auth.log tells me this

Nov 25 16:20:00 lamp sshd[808]: Accepted publickey for (snip) from (snip) port 54311 ssh2: RSA SHA256:(snip)
Nov 25 16:20:00 lamp sshd[808]: pam_unix(sshd:session): session opened for user (snip) by (uid=0)
Nov 25 16:20:00 lamp sshd[808]: refused local port forward: originator 127.0.0.1 port 54312, target 127.0.0.1 port 3306
Nov 25 16:20:00 lamp sshd[808]: Received disconnect from (snip) port 54311:11: disconnected by user
Nov 25 16:20:00 lamp sshd[808]: Disconnected from (snip) port 54311
Nov 25 16:20:00 lamp sshd[808]: pam_unix(sshd:session): session closed for user (snip)

which makes me think the server won't accept connections from the outside world. PhpMyAdmin works, as well as SSH'ing in and entering mysql from there.

Port 3306 is open in Amazon's security settings, FWTW.

Any idead on what to do here to fix things? 

Forum: 
Jeremy Davis's picture

First up, it's well worth noting that In Debian 9/Stretch (the basis of v15.x), Debian switched from MySQL to MariaDB. So whilst internally they don't work exactly the same, MariaDB is a "drop in replacement" for MySQL and you should not note any major differences. However, by default the (at least on Debian) MariaDB uses a different connection and authentication mechanism for the root account (apparently MySQL 5.7 have also adopted this new authentication mechanism too). From here on in, I'll continue to use "MySQL" as a generic term , meaning MariaDB specifically, when I refer

In a clean install, instead of a normal network connection, it uses a unix socket. By default, that means that you should no longer need a password to connect to your MySQL root user (so long as you are logged in as the root Linux user). However, TBH I'm not really sure how that works when you upgrade? Debian generally allows you to carry forth previous config when doing a dist-upgrade, so you may well have ended up with a combo of your old config and the new...

Regardless, if you have a look at this StackExchange answer (by me! :) you should hopefully be able to do some investigation and work out what is going on. You may also find this post (again by me - from while we were developing v15.0) of interest and value. As is noted in that thread, we ended up going with option 2. So essentially we created a new "root like" MySQL user and used that for the dual purposes of connecting via Adminer (the DB web UI we replaced PHPMyAdmin with at some point - perhaps v14.0?) and for remote connections. The other alternative would be to adjust your root account to use a password (and a normal network connection). Please keep in mind though, that you will also need to create a new system account with root privileges (and the password stored in plain text in /etc), otherwise neither the system nor TKLBAM will be able to get access to the DB.

If you need more specific guidance or have more questions, please ask.

Bill Carney's picture

I thought I was being smart when I migrated just my web-related files, and left the rest of the system as-is (you may recall about a year ago tklbam-backup broke and would leave the MySQL server hanging and I would have to manually service mysql restart for it to continue - was hoping by not touching the MariaDB stuff it would fix that):

tklbam-restore --limits="/var/www /var/www-logs /etc/apache2/sites-available /etc/apache2/sites-enabled /etc/cron.daily /etc/dehydrated/confconsole.domains.txt /var/lib/dehydrated" 52

but if I'm reading you correctly, I should have also --skip-database because by not skipping that it imported the old mysql.user table which wiped out the new root and Adminer accounts.  But then I'd have to manually recreate all the existing users and passwords, and then reconfig all the Wordpress sites with the new credentials, plus import the old tables...assuming I can get the GUI tools working, since phpMyAdmin has that 8MB limit and some of my DBs are much larger than that.

FWIW I spun up a quick vanilla v15 instance on my local machine, but still can't connect using GUI tools with that either.  I checked a couple tools to make sure it wasn't an issue with Sequel Pro.

Sorry if I'm sounding frustrated, but I am, the rest of the migration was super simple but I've wasted all day on this one aspect of it and it's a dealbreaker.  I'm off to get a beer.

Bill Carney's picture

I guess I don't understand what exactly I need to do here to enable remote access of the database.  I spun up a brand new unmodified VM on my local system.  I created a new system user in the root group, and a user all privileges in MariaDB.  If I use Terminal I can log in using SSH and then mysql -u username -p and get access to the database.

Using the same credentials in Sequel Pro, I can’t log in:

Unable to connect to host 172.16.82.219, or the request timed out.

Be sure that the address is correct and that you have the necessary privileges, or try increasing the connection timeout (currently 10 seconds).

MySQL said: Lost connection to MySQL server at 'reading initial communication packet', system error: 0


In the GUI I also tried SSH’ing in as root w/password, and used just root and no password for the MySQL host. Same thing with the Adminer account/pw.  No joy.


What does one need to do to gain access remotely via a GUI-tool?

Jeremy Davis's picture

I actually replied yesterday but must have been distracted and neglected to hit "Save"... Doh!

Here's what I wrote:

First up, by default, only the standalone MySQL appliance accepts remote connections. I'm sorry that I neglected to explicitly note that in my initial post. That is how it has always been and for some reason I just assumed that you would remember that (but you probably changed that years ago and haven't thought about it since - so that probably wasn't reasonable on my behalf...).

So to allow remote connections, you first need to make MySQL listen for all hosts (by default it's bound to localhost). Then you need to have a user who is allowed to connect remotely. The MySQL doc page covers both configuring MySQL to listen for remote connections, plus how to create a new "root like" user who can connect remotely.

Yes, I certainly remember your backup woes with MySQL. And I also recall that we never did quite work out what was wrong there. I remember that I managed to get it working with a snapshot of (some of) your data, but you couldn't replicate my success.

It's a pain that you are again hitting issues. Having read through your more recent post, I would assume that it would probably be easier to adjust (or even recreate) a couple of users manually, than it would be to recreate your existing DB users (although I can't be 100% sure of that).

Personally, I am not particularly familiar with Adminer (or PHPMyAdmin). I often use them (Adminer more recently and PHPMyAdmin historically) to view data (it's much easier to view long row when you can scroll, rather than have it line wrapped in a terminal). However, for most operations, I find the commandline pretty straight forward (even more so now you don't need to muck around with the root password...).

mysql -e "SQL QUERY GOES HERE;"

(Note that when logged in as root, it will default to using the root account, so '-u' is not required; also if you don't change the root authentication mechanism, there isn't a password, so you also don't need '-p').

TBH, probably what I'd attempt would be to restore your tables from your backup, make sure that the server can listen remotely, and create a new user (or adjust an existing user) to be able to connect remotely (as per the docs).

Bill Carney's picture

OK, this is weird. I spun up a new LAMP VM locally, and literally cut and pasted the commands on this page: MySQL doc page.  No joy. I can't connect to this server's database remotely.

I double checked and the new_user account can log in via Terminal and manually executing MySQL. So it's like MySQL still isn't listening for remote connections.  50-server.cnf does have bind-address commented out.

I restored backup to a fresh VM (using the tklbam-restore command noted previously), and ran the commands from the MySQL doc page. That worked, I could connect remotely.

Is there a step missing on the MySQL doc page? Soemthing I may have done in the past, that is no longer noted there?

Bill Carney's picture

I checked ALL the files in the /etc/mysql directory, and the only difference is in the debian.cnf file.  On the "new", unreachable server the user is "root", whereas on the old server that I can connect to uses "debian-sys-maint".

Jeremy Davis's picture

Ok, so let's check that your server is globally listening on all interfaces:

netstat -tlnp | grep 3306

Next up, double check that the user account is allowed remote access:

mysql -e "select host, user, plugin, default_role from mysql.user;"

If those appear ok, then the only other thing that I could imagine would cause this is firewall rules (or some other sort of network filtering). To check that, you'll need to try connecting to the port from outside your server. I forget your OS, but assuming Windows, then I note that you can download netcat. Assuming that it provides the same interface as the Linux version, try this:

nmap REMOTE_IP_OR_FQDN -p 3306

(Where REMOTE_IP_OR_FQDN is the IP or domain name of the remote machine you're trying to connect to.)

If all of those appear ok (and please post back the output so I can double check) and you're still having issues connecting, then I'm not really sure... But let's cross that bridge if we get to it...

Jeremy Davis's picture

Sorry it looks like I jumped the gun a bit and didn't read your post properly...

So you got it working ok, but not after following the docs?! Did you definitely, restart MySQL after applying the update? FWIW, I wrote up that documentation when I realised that the old docs no longer worked and I recall testing it. Although perhaps something has changed slightly since then? (I know that there have been some backported patches applied to MariaDB).

But to make sure, I just launched a fresh Hub server (actually it was a WordPress server so I could kill 2 birds with one stone; but that should be irrelevant as our WP appliance is based on LAMP). I copy/pasted the commands directly from the doc page (and opened port 3306 on the firewall) and connected from my local machine and got this:

user@ninjux ~$ mysql -unew_user -pMyNewPassword -h wp01.jeremydavis.org
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 4
Server version: 10.1.38-MariaDB-0+deb9u1 Debian 9.8

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| wordpress          |
+--------------------+
4 rows in set (0.03 sec)

MariaDB [(none)]> 

FWIW, here's the output from the first 2 commands I asked you for (so you have something to compare against):

root@wordpress ~# netstat -tlnp | grep 3306
tcp6       0      0 :::3306                 :::*                    LISTEN      1260/mysqld
root@wordpress ~# mysql -e "select host, user, plugin, default_role from mysql.user;"
+-----------+-----------+-------------+--------------+
| host      | user      | plugin      | default_role |
+-----------+-----------+-------------+--------------+
| localhost | root      | unix_socket |              |
| %         | adminer   |             |              |
| localhost | wordpress |             |              |
| localhost | new_user  |             |              |
| %         | new_user  |             |              |
+-----------+-----------+-------------+--------------+

So beyond some firewall/network filtering/proxy/etc, the only other thing that occurs to me as a possibility is that perhaps there was another config file somewhere that has been inadvertently introduced which is overriding the settings? Obviously that shouldn't be the case on a clean install, but if you've been restoring stuff, then perhaps? (Although if you already had done a TKLBAM restore, then perhaps somewhere along the line something has crept in?!).

Bill Carney's picture

Output of netstat:

tcp6       0      0 :::3306                 :::*                    LISTEN      1238/mysqld

Output of mysql query (user name is new_user, I literally cut & pasted the code.

+-----------+-----------+-------------+--------------+

| host      | user      | plugin      | default_role |

+-----------+-----------+-------------+--------------+

| localhost | root      | unix_socket |              |

| %         | adminer   |             |              |

| localhost | new_user  |             |              |

| %         | new_user  |             |              |

+-----------+-----------+-------------+--------------+

Output of nmap (I run a Mac, downloaded the Mac version)

nmap 172.16.82.220 -p 3306

Starting Nmap 7.80 ( https://nmap.org ) at 2019-11-27 16:49 EST

Nmap scan report for 172.16.82.220

Host is up (0.097s latency).


PORT     STATE SERVICE

3306/tcp open  mysql


Nmap done: 1 IP address (1 host up) scanned in 0.39 seconds
Jeremy Davis's picture

Looks like our posts may have crossed paths. Anyway, as I noted it works for me. And looking at the output that you've posted, it looks like you should be able to log in as the new_user no problem?!

The user can connect remotely, MySQL is listening on all interfaces and port 3306 is accessible remotely?!? So I have no idea why it doesn't "just work"?! It certainly should (assuming no network filtering etc - although that should have showed up as a potential issue when running nmap).

Perhaps it's worth just installing a commandline MySQL client and double check connection via that? If you can connect successfully there, perhaps it's something up with your client? (Doesn't like MariaDB and/or the newer version of MySQL that MariaDB v10.1 is compatible with - MySQL 5.6/5.7).

Bill Carney's picture

My current server, the one live on the internet that I can connect to remotely, does not have port 3306 open. The remote GUI software has an option where I key in the MySQL host, user name, password, database, and port,  and then another section where I type in the SSH host, user, password/key, and port.

I can connect to that server without any issues.

I spun up a new LAMP server on the Hub, and ran the commands on the MySQL doc page. Using the above method, via SSH, I cannot connect.

I then opened up port 3306 on that instance's firewall.  I still can't connect using the above method. I can, however, connect using a standard unencyrypted connection.  Not optimal as port 3306 is open and the connection is unencrypted.

I tried this with two different software packages (Sequel Pro and Querious) to verify it's not a local software issue.

Bill Carney's picture

Still no joy trying to connect to MySQL over an SSH tunnel.  Here is the output of

service ssh status

Nov 30 01:49:40 lamp sshd[13857]: Accepted publickey for root from (my ip addr) port 59936 ssh2: RSA SHA256:(snip)
Nov 30 01:49:40 lamp sshd[13857]: pam_unix(sshd:session): session opened for user root by (uid=0)
Nov 30 01:49:40 lamp sshd[13857]: refused local port forward: originator 127.0.0.1 port 59937, target 127.0.0.1 port 3306
Nov 30 01:49:40 lamp sshd[13857]: Received disconnect from (my ip addr) port 59936:11: disconnected by user
Nov 30 01:49:44 lamp sshd[13873]: Accepted publickey for root from (my ip addr) port 59940 ssh2: RSA SHA256:(snip)
Nov 30 01:49:44 lamp sshd[13873]: pam_unix(sshd:session): session opened for user root by (uid=0)
Nov 30 01:49:44 lamp sshd[13873]: refused local port forward: originator 127.0.0.1 port 59941, target 127.0.0.1 port 3306
Nov 30 01:49:44 lamp sshd[13873]: Received disconnect from (my ip addr) port 59940:11: disconnected by user

Any idea why it's dropping the connection?

 

Bill Carney's picture

Edit /etc/ssh/sshd_config

Go to the bottom, in the section marked # SSH hardening recommended by lynis

Commented out AllowTcpForwarding no and added

AllowTcpForwarding yes

This now allows me to connect to the MySQL server via an SSH tunnel, and also allows me to keep port 3306 closed.

Jeremy Davis's picture

Sorry that I've been so slow to reply. I've been a bit ill.

Anyway glad that you sorted it out. FWIW it didn't occur to me that a remote DB management tool would be connecting via SSH. The only way I've ever connected to MySQL remotely has been via port 3306. It's perhaps worth noting that MySQL can use TLS too, although obviously if you can connect via SSH then that's probably still preferable...

Add new comment