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
ACCEPT tcp — 192.168.1.1.0/0 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’;
[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.