Mysql – Can’t login with Workbench

connectivityMySQLmysql-5.7

I use Ubuntu 16.04 and MySQL 5.7.16 and trying to connect from Windows10 with Workbench; My aim is to be able to login from any computer, anywhere — I have no intention to allow only one IP.

I get failed connect after filling credentials:

method    | TCP/IP
hostname  | IPv4
port      | 3306
username  | I use the MySQL root user (the one I used to login to PHPmyadmin with)
password  | MySQL root password.

The error is:

Is not allowed to connect to this MySQL server

enter image description here

[mysqld]
#
# * Basic Settings
#
user        = mysql
pid-file    = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
port        = 3306
basedir     = /usr
datadir     = /var/lib/mysql
tmpdir      = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address        = 0.0.0.0
# 127.0.0.1

Why it's not likely a firewall issue:

Given I use CSF-LFD as firewall software, I don't use the native UbuntuFireWall (UFW). Yet, I did allow port 3306 ongoing and outgoing in csf.conf, and restarted CSF via sudo csf -r.

netstat -plnt:

    tcp    0    0 0.0.0.0:3306    0.0.0.0:*    LISTEN -

My question:

Given these circumstances, what else can prevent my remote login?

Best Answer

In MySQL, a user contains 2 mandatory parts:

  • user name - like root, or sam
  • host allowed for this user to login, like - localhost, 10.30.10%, 10.% etc.

all these users are different and could have different credentials:

  • root@localhost
  • root@10.%
  • root@10.30.10.%

each from example list - separate user and have unique access list

So you have a choice:

  • simple - change wildcard for root, I would not recommend it

  • create another user with remote access:

    like:

    CREATE USER 'other_root'@'%' IDENTIFIED BY 'other_root_password';

GRANT ALL ON *.* TO 'other_root'@'%' WITH GRANT OPTION;

FLUSH PRIVILEGES;
Related Question