?
Solved

Lost user connectivity when MySQL is binded with IP address.

Posted on 2016-09-05
5
Medium Priority
?
31 Views
Last Modified: 2016-09-11
Hello,

I have used bind-address option in MySQL config file which stopped my connectivity to mysql instance on specific user. I have figured it out that the user is created with host as "localhost". I'm able to fix this issue by updating the user Host field with binded ip or using wildcard '%' (any ip).

I'm trying to understand is there is any possible security issue using wild card '%' to allow on any IP or it is recommended to bind with specific IP?

Also, what is recommended approach for high privileged user accounts like root & mysql.sys?

Thank you
0
Comment
Question by:Siva Dasari
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 83

Accepted Solution

by:
Dave Baldwin earned 2000 total points
ID: 41785269
'root' is normally restricted to one of versions of 'localhost', '127.0.0.1' (IPv4 format), or '::1' (IPv6 format).  That requires the 'root' user to be logged into the machine that the server is on.
0
 
LVL 79

Expert Comment

by:arnold
ID: 41785271
Adding to Dave's point, you can create other users who can be accessing from any (%) source while restricting what they can do on the system.
0
 

Author Comment

by:Siva Dasari
ID: 41785322
I tried to do the following as per your comments but ended with an error, could you please let me know if i'm missing something here ...

mysql> UPDATE mysql.user SET HOST="127.0.0.1" WHERE user="root";
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.02 sec)
mysql> exit
Bye

[siva@mysql ~]$ mysql -u root -p
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
[siva@mysql ~]$

Open in new window

0
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 41785345
Please read the  --bind-address section on this page: http://dev.mysql.com/doc/refman/5.7/en/server-options.html   The default value is '*' which allows the server to connect on any IP address or hostname.  It is probably better to use '*' and restrict access in the 'privileges' where you can limit users to specific IP addresses without blocking the entire server.
0
 
LVL 79

Expert Comment

by:arnold
ID: 41785395
Changing the host, does not set a password, which seems that root might not have a password
mysql -u root
See if it loggs you in.
select * from mysql.user where User='root';

Also, usually the host us localhost, versus the equivalent 127.0.0.1.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Creating and Managing Databases with phpMyAdmin in cPanel.
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

801 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question