LinuxQuestions.org
Welcome to the most active Linux Forum on the web.
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Newbie
User Name
Password
Linux - Newbie This Linux forum is for members that are new to Linux.
Just starting out and have a question? If it is not in the man pages or the how-to's this is the place!

Notices


Reply
  Search this Thread
Old 09-03-2015, 09:58 AM   #1
skimeer
Member
 
Registered: Jun 2007
Posts: 62
Blog Entries: 1

Rep: Reputation: 0
What's is difference between changing MySQL user password with and without -h option


I have observed that for normal (other than root) user changing password with and without -h option has different effect. If password is changed without -h, then the commands used with -h did not pick new password. They still uses old password.

Also I am not able to connect to MySQL instance using -h option with root user. I am using MySQL Ver 14.14 Distrib 5.6.21, for Linux (x86_64) using EditLine wrapper


I am trying below command,
Quote:
[test@test ~]$ mysql -uroot -ptest -h 192.168.209.117
Warning: Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'root'@'test' (using password: YES)
Output of "SHOW GRANTS"

Quote:
mysql> show grants;
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |
+----------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql>

Last edited by skimeer; 09-03-2015 at 10:24 AM.
 
Old 09-03-2015, 10:21 AM   #2
hortageno
Member
 
Registered: Aug 2015
Distribution: Ubuntu 14.04 LTS
Posts: 230

Rep: Reputation: 65
Quote:
Originally Posted by skimeer View Post
I have observed that for normal (other than root) user changing password with and without -h option has different effect. If password is changed without -h, then the commands used with -h did not pick new password. They still uses old password.

Also I am not able to connect to MySQL instance using -h option with root user. I am using MySQL Ver 14.14 Distrib 5.6.21, for Linux (x86_64) using EditLine wrapper


I am trying below command,


Output of "SHOW GRANTS"
Default host for -h is "localhost". You can have different permissions/passwords depending where you connect from/to.
 
Old 09-03-2015, 10:25 AM   #3
skimeer
Member
 
Registered: Jun 2007
Posts: 62
Blog Entries: 1

Original Poster
Rep: Reputation: 0
1.How to enable root user to connect through "-h 192.168.209.117" option.
2.Do I need to reset user password with and without -h option through mysqladmin command.
 
Old 09-03-2015, 10:40 AM   #4
hortageno
Member
 
Registered: Aug 2015
Distribution: Ubuntu 14.04 LTS
Posts: 230

Rep: Reputation: 65
Quote:
Originally Posted by skimeer View Post
1.How to enable root user to connect through "-h 192.168.209.117" option.
2.Do I need to reset user password with and without -h option through mysqladmin command.
I usually do such things with phpmyadmin, but you basically login with the account which works and change the password for user root@192.168.209.117 like this

Code:
SET PASSWORD FOR 'root'@'192.168.209.117' = PASSWORD('cleartext password');
https://dev.mysql.com/doc/refman/5.0...-password.html
 
Old 09-03-2015, 11:40 AM   #5
Habitual
LQ Addict
 
Registered: Jan 2011
Location: Youngstown, Ohio
Distribution: LM17.1/Xfce4.11.8
Posts: 7,158
Blog Entries: 10

Rep: Reputation: 1968Reputation: 1968Reputation: 1968Reputation: 1968Reputation: 1968Reputation: 1968Reputation: 1968Reputation: 1968Reputation: 1968Reputation: 1968Reputation: 1968
Quote:
Originally Posted by skimeer View Post
1.How to enable root user to connect through "-h 192.168.209.117" option.
Code:
GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.209.117' IDENTIFIED BY PASSWORD '<password>' WITH GRANT OPTION ; flush privileges; exit;

Last edited by Habitual; 09-03-2015 at 11:42 AM.
 
Old 09-03-2015, 02:19 PM   #6
skimeer
Member
 
Registered: Jun 2007
Posts: 62
Blog Entries: 1

Original Poster
Rep: Reputation: 0
Thanks for suggestions. I will try them out and update.
However my doubt is, does root user not allowed to login through Ip address from command line?
 
Old 09-03-2015, 02:27 PM   #7
hortageno
Member
 
Registered: Aug 2015
Distribution: Ubuntu 14.04 LTS
Posts: 230

Rep: Reputation: 65
What's is difference between changing MySQL user password with and without -h option

You didn't understand completely. You login without the -h option to change the user's permissions and password for the login with the IP.
 
Old 09-03-2015, 02:35 PM   #8
skimeer
Member
 
Registered: Jun 2007
Posts: 62
Blog Entries: 1

Original Poster
Rep: Reputation: 0
Sorry but I am not getting you. I am trying to reset password in shell script through 'mysqladmin' command. If I use -h in mysqladmin, new password is effective only for 'mysql' commands used with -h.
I am connecting to mysql from that server itself.
 
Old 09-03-2015, 02:41 PM   #9
astrogeek
Moderator
 
Registered: Oct 2008
Distribution: Slackware [64]-X.{0|1|2|37|-current} ::12<=X<=14, FreeBSD_10{.0|.1|.2}
Posts: 3,874
Blog Entries: 1

Rep: Reputation: 1997Reputation: 1997Reputation: 1997Reputation: 1997Reputation: 1997Reputation: 1997Reputation: 1997Reputation: 1997Reputation: 1997Reputation: 1997Reputation: 1997
Quote:
Originally Posted by skimeer View Post
Thanks for suggestions. I will try them out and update.
However my doubt is, does root user not allowed to login through Ip address from command line?
Your MySQL users can login only accourdng to the GRANTs given them.

GRANTs for same name but different hosts (IP) are different users as far as MySQL is concerned. Changing password for one has no effect on any other.

And root@anywhere is just another MySQL user and carries no inherent privileges or restrictions - only what is in the GRANT table. Do not confuse a MySQL user named root with system root user - they are not related in any way.

Last edited by astrogeek; 09-03-2015 at 02:47 PM.
 
Old 09-03-2015, 02:44 PM   #10
Habitual
LQ Addict
 
Registered: Jan 2011
Location: Youngstown, Ohio
Distribution: LM17.1/Xfce4.11.8
Posts: 7,158
Blog Entries: 10

Rep: Reputation: 1968Reputation: 1968Reputation: 1968Reputation: 1968Reputation: 1968Reputation: 1968Reputation: 1968Reputation: 1968Reputation: 1968Reputation: 1968Reputation: 1968
Users cannot change their mysql passwords.
 
Old 09-04-2015, 08:26 AM   #11
skimeer
Member
 
Registered: Jun 2007
Posts: 62
Blog Entries: 1

Original Poster
Rep: Reputation: 0
Tried executing
Quote:
GRANT ALL PRIVILEGES ON *.* TO 'root'@'' IDENTIFIED BY 'XXXX' WITH GRANT OPTION;
Query is successful but there is no grants are not updated. Also access issue not fixed.

Quote:
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'XXXX' WITH GRANT OPTION; Query OK, 0 rows affected (0.00 sec) mysql>
 
Old 09-04-2015, 09:14 AM   #12
hortageno
Member
 
Registered: Aug 2015
Distribution: Ubuntu 14.04 LTS
Posts: 230

Rep: Reputation: 65
Quote:
Originally Posted by skimeer View Post
Tried executing

Query is successful but there is no grants are not updated. Also access issue not fixed.
The command(s) you used is not the same as in post 5. PASSWORD means literally PASSWORD whereas <password> means the password. Please try again exactly like written in post 5.
 
Old 09-04-2015, 09:29 AM   #13
skimeer
Member
 
Registered: Jun 2007
Posts: 62
Blog Entries: 1

Original Poster
Rep: Reputation: 0
That query not working.

Quote:
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.209.117' IDENTIFIED BY PASSWORD 'XXXX' WITH GRANT OPTION ; flush privileges; exit;
ERROR 1827 (HY000): The password hash doesn't have the expected format. Check if the correct password algorithm is being used with the PASSWORD() function.
Query OK, 0 rows affected (0.00 sec)

Bye
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off



Similar Threads
Thread Thread Starter Forum Replies Last Post
[SOLVED] Error while changing off mysql password jayakumar01 Linux - Server 1 01-10-2012 11:18 AM
mysql error while changing mysql password jsaravana87 Linux - Server 5 09-09-2011 12:57 AM
Mysql not starting after changing mysql admin password in fedora prastal Linux - Newbie 1 02-20-2010 08:26 PM
User friendly message to user on password changing tanveer Linux - General 2 02-24-2009 08:56 AM
screwed up mysql by changing password, help! JustinHoMi Linux - General 0 01-28-2002 05:31 PM


All times are GMT -5. The time now is 04:01 PM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration