Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Rhinorhino on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

MySQL version 8 - network access

Status
Not open for further replies.
alancj

alancj

Programmer
Joined
Jan 12, 2010
Messages
3
Location
NZ
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

 
Sort by date Sort by votes
alancj said:
2. Local access (using 'LocalHost') is no problem.
Click to expand...

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.

 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
alancj said:
What doesn't make sense is that there was no such trouble with previous versions of MySQl.
Click to expand...

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]
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

bharons
  • Locked
  • Question Question
filtering data with vfp 8
Replies
0
Views
331
bharons
bharons
E
  • Locked
  • Question Question
MySQL calculator tools
Replies
0
Views
684
EdZzy
E
lupidol
  • Locked
  • Question Question
Cannot create new table in mySQL
Replies
6
Views
1K
lupidol
lupidol
lupidol
  • Locked
  • Question Question
mysql cannot add rows with "UNION"
Replies
5
Views
1K
Chris Miller
Chris Miller
dylim
  • Locked
  • Question Question
SQL_CALC_FOUND_ROWS still runs in MySQL 8.0.29
Replies
2
Views
1K
dylim
dylim

Part and Inventory Search

Sponsor

Back
Top