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).

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...

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?!).

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).

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...