Search code examples
mysqlsshremote-accessubuntu-servernavicat

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

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
[email protected]'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:

Error message in Navicat

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

Error in MySQL Workbench using SSH

Btw here my settings for the connection:

Navi get connection settings

enter image description here

What am I missing here?

Solution

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