Can't connect with remote user to MySQL (hosted on Ubuntu VPS)

Question

I am trying to setup a remote user for my MySQL db so I can access it from any IP. The MySQL server is hosted on a Ubuntu VPS server.

I already created a remote user as you can see here:

login as: root
root@xx.xx.xx's password:
Welcome to Ubuntu 16.04.3 LTS (GNU/Linux 4.4.0-108-generic x86_64)

 * Documentation:  https://help.ubuntu.com
 * Management:     https://landscape.canonical.com
 * Support:        https://ubuntu.com/advantage

  Get cloud support with Ubuntu Advantage Cloud Guest:
    http://www.ubuntu.com/business/services/cloud

82 packages can be updated.
42 updates are security updates.


Last login: Fri Mar  2 19:47:15 2018 from xxxxxxx
root@vpsxxxxxx:~# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 5.7.21-0ubuntu0.16.04.1 (Ubuntu)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> SHOW variables WHERE Variable_name = 'hostname' OR Variable_name =     'port';
+---------------+-----------+
| Variable_name | Value     |
+---------------+-----------+
| hostname      | vps520749 |
| port          | 3306      |
+---------------+-----------+
2 rows in set (0.01 sec)

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

mysql>

But once I try to login with the user user via Navicat or MySQL Workbench I get this error:

Even if I try to connect to the SQL server over SSH I still get an error:

Btw here my settings for the connection:

What am I missing here?

Answer 1

You might have a possible problem with remote privileges. If that is the case, open MySQL on the command line and enter this in:

GRANT ALL PRIVILEGES
ON my_database_name.*
TO 'user'@'%'
IDENTIFIED BY 'new_remote_password';
FLUSH PRIVILEGES;

'%' is a wildcard, so the TO line says that a username 'user' with any IP address has access. But they'll need the password new_remote_password. And as you can see, the ON line limits this access to one database, my_database_name.

And there are some obvious security risks by doing this, so make sure you know what you are doing in that regard.

Here are some other possible fixes:

Change a line or two in the config file. Typically the config file is at /etc/mysql/my.cnf or /etc/mysql/mysql.conf.d/mysqld.cnf on Ubuntu. Get rid of or comment out the lines that say

bind-address = 127.0.0.1  

and

skip-networking 

You'll want to restart MySQL then, too, with sudo service mysql restart on the command line.

If those don't work, it could be a firewall or port problem, as suggested in another answer.

Answer 2

Perhaps a firewall setting is not allowing access to port 3306? You can check this from the command prompt to see if it is a network problem or an application issue by connecting to the port with a utility such as netcat (nc).

For example, from the machine running the Navicat/MySQLWorkbench, connecting to the database server with up address 192.168.0.100:

user@server1:~> nc -zvn 192.168.0.100 3306
nc: connect to 192.168.0.100 port 3306 (tcp) failed: Connection refused
user@server1:~>
-----
user@server1:~> nc -zvn 192.168.0.100 3306
Connection to 192.168.0.100 3306 port [tcp/*] succeeded!
user@server1:~>

To connect by the server's hostname, leave out the -n option: