1

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?

  • ok, I will not put this as answer for now, but You must check root user credentials - for 99% it restricted by localhost. start from - select host, user.User from mysql.user where user like 'root' ; then You if I right - You can create superuser similar with root (better than add % to the root) – a_vlad Dec 22 '16 at 9:19
  • The config file fragment that you posted clearly states that your MySQL daemon only listens on localhost. You need to bind it to another IP address that's reachable from remote computers, in addition to allowing remote logins as suggested by @a_vlad – mustaccio Dec 22 '16 at 16:07
  • I gues the 0.0.0.0 stands for localhost. Well, I don't want it for a specific IP as I might work with dynamic IPs, how can you make that aside of allowing remote logins? – JohnDoea Dec 22 '16 at 16:22
1

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;
    
1

Any user (even root) by default can access only from localhost. You must allow to connect from remote machines.

SSH login to your machine with the relevant webserver and MySQL, and execute this script directly on the CLI terminal:

CREATE USER 'root'@'%' IDENTIFIED BY 'password';
GRANT ALL ON *.* TO 'root'@'%' WITH GRANT OPTION;

Given you have MySQL installed, it should work on MySQL directly, without any previous actions.

TO 'root'@'%' - this part gives access for every remote IP. Change % to whatever IP you want to give access only to that IP.

See 14.7.1.4 GRANT Syntax.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.