Couldn’t connect remotely with mysql client (command line and phpMyAdmin) to the MariaDB server

I had a MariaDB server running on port 3306.

I created a local MariaDB account ‘tommy’ with:

CREATE USER ‘tommy’@’localhost’ IDENTIFIED BY ‘somepassword’

and granted all rights with:

GRANT ALL PRIVILEGES ON *.* TO ‘tommy’@’%’ WITH GRANT OPTION; flush privileges;

so that ‘tommy’ should be able to log on to the MariaDB server remotely.

But, no luck! I could log on to the MariaDB server locally with:

mysql -u tommy -p

But not from elsewhere. When trying to connect, the error message was:

ERROR 1045 (28000): Access denied for user 'tommy'@'clienthostname' (using password: YES)

I checked that the firewall was open on the MariaDB server with:

iptables -nL | grep 3306

which gave:

ACCEPT tcp — multiport dports 3306 /* 030 allow mysql from double7 */

Showing that incoming TCP connections to the MariaDB server 3306 are open.

The solution was finally to set the password for the user once more with:

select password (‘secretpassword’);

which will give the password hash should be a 41-digit hexadecimal number from the ‘secretpassword’, and finally setting the password with the 41-digit hexadecimal from the command above:

set password for ‘tommy’@’clienthostname’ = ‘*F89FFE84BFC48A876BC682C4C23ABA4BF64711A4’;

and voila:

[tommy@clienthostname ~]$ mysql -u tommy -h mariadbserverhostname -p
Enter password: secretpassword
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 3039
Server version: 5.5.68-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.