MySQL version 8 - network access

Question

MySQL version 8 - network access

alancj (Programmer)

(OP)

23 Oct 19 22:13

I've run into something very strange. I'm unable to complete the set up of netorked access to a MySQL 8.0 database.

Here is why it is so strange:
1. I've had no trouble setting up versions 5.6 and 5.7. From either WorkBench or from our software.
2. Local access (using 'LocalHost') is no problem.
3. The access is for the "root" user. With full access rights set.
4. The 3306 port is allowed on all networked PC firewalls. And user control is turned off as well.
5. The IP addresses being used are valid and can be pinged.
6. On WorkBench, even though I specify "Store in vault" for the password, the password keeps being requested. And then we get "Access denied for user "root@development (using password YES)".

Which implies insufficient rights. Even though the root user has been granted full access from the MySQL command line - just in case.

I'm beginning to suspect there is something wrong with the MySQL setup. Anyone got any ideas?

Regards.

Alan

spamjim (Instructor) · Answer 1 · 2019-10-24 12:51:54

Quote (alancj)
2. Local access (using 'LocalHost') is no problem.

Have the privileges been granted to access from all IP addresses? The default is usually for just localhost, especially if "root".

It is not a best practice to allow a MySQL user to access from any IP address (wildcard). In MySQL you should lock down the IP address per user, when the user is created/configured. In MSSQL, you limit IP address access via firewall.

https://easyengine.io/tutorials/mysql/remote-acces...

alancj (Programmer) · Answer 2 · 2019-10-24 19:32:50

It's NOT a good idea (in this case) to specify the IP addresses to be used. As they can change and will need to be reconfigured. We've set things up so the server's ip address is static. And that is all we need. At least, until Version 8, it was. The MySQL configuration file is not locked to any specific IP addresses.

That is #bind-address = 127.0.0.1
#skip-networking are blank and commented out in my.ini

What doesn't make sense is that there was no such trouble with previous versions of MySQl. But, with V8.0, there is. And we've not changed anything in terms of permissions, firewall or anything else.

Alan

alancj (Programmer) · Answer 3 · 2019-10-24 20:37:46

The solution has been found. When the server was installed 'root' has bound to 'localhost'. I think this may be the default for MySQL 8.0 (someone correct me if wrong). So I did mysql> update user 'root' set host ='%';

Shutdown and restart the server.

Now it's possible to access the database as root, from anywhere.

spamjim (Instructor) · Answer 4 · 2019-10-28 17:05:15

Quote (alancj)
What doesn't make sense is that there was no such trouble with previous versions of MySQl.

Previous versions did not default to expose root access from anywhere...as this was incredibly reckless then and still is now. You or a predecessor probably tweaked this user access setting in previous versions as well.

I'm glad you finally found the solution that was described in the first response. bigsmile

Come Join Us!

Posting Guidelines

Jobs

MySQL version 8 - network access