Connection refused by MySQL server?

'Eya there. Just right off the bat, this is my first stint into working with anything to do with servers, Linux, and MySQL, so bear with me.

I've compiled a server software package on Ubuntu, installed MySQL, and am attempting to connect to my server's MySQL databases with HeidiSQL. The ports are open, and my desktop is not refused by the server, but by the MySQL database running on the server. The following error is produced when I try to connect:

Host 'Not-Sharing The IP-Because-Security-Reasons' is not allowed to connect to this MySQL server'

How do I resolve this?

The users in MySQL and MariaDB are bound to an ip address from where they may come from. If you open a console on the server, you might be able to connect to it with the right password. Then, if you fire this query, you should watch out which "Host" the user has you want to use:

SELECT Host, User FROM mysql.user;

For me, this shows me such a result:

MariaDB [test3]> select Host, User from mysql.user;
+--------------+------+
| Host         | User |
+--------------+------+
| %.mshome.net | root |
| 127.0.0.1    | root |
| ::1          | root |
| localhost    | root |
+--------------+------+
4 rows in set (0.000 sec)

If I wanted to connect with user "root" from a non-local IP address, I could manipulate the root@127.0.0.1 user:

UPDATE mysql.user SET Host='%' WHERE Host='127.0.0.1' AND User='root';

That "%" means, you can connect from everywhere. Think about security issues. You can also use "192.168.%" for example.

However, after updating your user, you need to refresh MySQL's privileges:

FLUSH PRIVILEGES;

I sincerely thank you for the in-depth reply. It's a great help.

So the first issue, is that when I connect with the user "trinity" (granted sudo privileges in Ubuntu), the terminal refuses to start MySQL even when I type in the root password. But if I change to the root Ubuntu user, it works just fine.

Anywho, with root, I get this from the terminal:

mysql> SELECT Host, User from mysql.user
    -> ;
+-----------+------------------+
| Host      | User             |
+-----------+------------------+
| localhost | debian-sys-maint |
| localhost | mysql.session    |
| localhost | mysql.sys        |
| localhost | root             |
+-----------+------------------+
4 rows in set (0.00 sec)

Running the command to allow anyone to connect to root passes fine:

mysql> UPDATE mysql.user SET Host='%' WHERE Host='127.0.0.1' AND User='root';
Query OK, 0 rows affected (0.01 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

But HeidiSQL throws the same error.

That UPDATE changed no user:

Rows matched: 0

Reason is, the WHERE filter searches for rows with Host='127.0.0.1', of which you have none. Instead, there are four with localhost. So, you need to run:

UPDATE mysql.user SET Host='%' WHERE Host='localhost' AND User='root';
FLUSH PRIVILEGES;

Ah, there we go. Now it changes a row (haven't added data to the MySQL database yet).

Now when I try to connect with HeidiSQL, the error is similar, but different:

Access denied for user 'root'@'host##-###-##-###.range86-164.btcentralplus.com'

Changed the numbers to # for obvious reasons. So it's no longer saying that the connection is refused, but that access is denied.

That looks as if you entered a wrong password now.

There are two passwords, one for the root user for Ubuntu, and another for the root MySQL user. I've tried both, and they throw the error. But if I use NoMachine to access the terminal and enter MySQL, passing the same password works fine.

Here is a screenshot of the settings: https://i.imgur.com/NsVyFPb.png

Recheck if the UPDATE succeeded:

SELECT Host, User from mysql.user;

Maybe also post the privileges of that user here:

SHOW GRANTS FOR 'root'@'%';

And did you really run FLUSH PRIVILEGES after the UPDATE?

Alternatively, you could create a fresh user:

CREATE USER 'new_root'@'%' IDENTIFIED BY 'your-pAssw0rd';
GRANT ALL PRIVILEGES ON *.* TO 'new_root'@'%' IDENTIFIED BY 'your-pAssw0rd';
FLUSH PRIVILEGES;

Sorry for not reporting back. What I had to do, was create a new MYSQL root user and grant them privileges, and connect through that. Connecting remotely to the original root user would throw that error.