Unable to run mysql from command prompt - cmd

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Unable to run mysql from command prompt - cmd

Post by raghavan20 »

1. Hello, I am trying to run mysql from the command prompt using cmd and I get this error...

Code: Select all

C:\>mysql
ERROR 1045 (28000): Access denied for user 'ODBC'@'localhost' (using password: NO)
But if I run from the mysql client I can login to mysql using my root password...

2. Can any of tell me the command to list all the users in mysql server???
User avatar
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098
Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia

Re: Unable to run mysql from command prompt - cmd

Post by Chris Corbyn »

raghavan20 wrote:1. Hello, I am trying to run mysql from the command prompt using cmd and I get this error...

Code: Select all

C:\>mysql
ERROR 1045 (28000): Access denied for user 'ODBC'@'localhost' (using password: NO)
But if I run from the mysql client I can login to mysql using my root password...

2. Can any of tell me the command to list all the users in mysql server???
Running from command prompt like that is the MySQL client :?

Is the server running on the same machine as the client?

If you want to log in as root and it's password protected.

Code: Select all

C:\ > mysql -u root -p
password: *****
mysql > _
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post by raghavan20 »

I am able to login using the following

Code: Select all

mysql -u root -p rootPassword
For your first question, the mysql is running on my local machine at home....I do not why the error comes up when I type mysql...

can you tell me how to see all the users available???...thanks for help
User avatar
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098
Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia

Post by Chris Corbyn »

raghavan20 wrote:I am able to login using the following

Code: Select all

mysql -u root -p rootPassword
For your first question, the mysql is running on my local machine at home....I do not why the error comes up when I type mysql...

can you tell me how to see all the users available???...thanks for help
Log in as root then:

Code: Select all

SELECT User from mysql.user;
... and to see the servers they are bound to:

Code: Select all

SELECT host, user from mysql.user;
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post by raghavan20 »

This has got two roots....what are they??? what is a % in the host field???

Code: Select all

mysql>select host, user from mysql.user
    -> ;
+-----------+-----------------+
| host      | user            |
+-----------+-----------------+
| %         | dummy@localhost |
| %         | root            |
| localhost | francis         |
| localhost | raghavan        |
| localhost | root            |
+-----------+-----------------+
5 rows in set (0.00 sec)
User avatar
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098
Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia

Post by Chris Corbyn »

I don't mean to be rude but you dont search much do you?

% is a wildcard. It means that the given user had access from any IP address. You need a seperate user account for localhost.

Without the % root would not have access from anywhere other than localhost. But it's more secure to only allow connections from known IP addresses so you could drop that user and:

Code: Select all

GRANT ALL PRIVILEGES ON * TO 'root'@'212.67.XXX.XXX' IDENTIFIED BY 'some_password'
Where 212.67.XXX.XXX is the IP of the other machine.

MySQL has an excellent manual just like PHP you know.

http://dev.mysql.com/

EDIT | Corrected spelling
Last edited by Chris Corbyn on Mon Jan 02, 2006 6:04 pm, edited 1 time in total.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

If you add the password as an argument, there can't be a space between -p and rootpassword -> mysql -u root -prootpassword


Or, simply mysql -u root -p .. MySQL client will prompt you to enter the password (*** are displayed instead of the password)
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post by raghavan20 »

Thanks for the extra clarification timvw. I thought when we just enter mysql, mysql would prompt us to enter username and password but instead it just gives up error.
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

raghavan20 wrote:Thanks for the extra clarification timvw. I thought when we just enter mysql, mysql would prompt us to enter username and password but instead it just gives up error.
That's because by just typing in mysql you are trying to login using no username or password and thus you are denied access. You will only be prompted for a password if you ask to be prompted using -p.

Mac
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post by raghavan20 »

I am trying to change the root password...after I changed the root password from *somepassword* to *rootPassword* in mysql.user, it still uses my old password to log in to mysql client...can anybody tell me why???

Code: Select all

mysql> select user, host, password from mysql.user where user like 'root';
+------+-----------+--------------+
| user | host      | password     |
+------+-----------+--------------+
| root | localhost | rootPassword |
| root | %         | rootPassword |
+------+-----------+--------------+
2 rows in set (0.00 sec)
User avatar
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098
Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia

Post by Chris Corbyn »

raghavan20 wrote:I am trying to change the root password...after I changed the root password from *somepassword* to *rootPassword* in mysql.user, it still uses my old password to log in to mysql client...can anybody tell me why???

Code: Select all

mysql> select user, host, password from mysql.user where user like 'root';
+------+-----------+--------------+
| user | host      | password     |
+------+-----------+--------------+
| root | localhost | rootPassword |
| root | %         | rootPassword |
+------+-----------+--------------+
2 rows in set (0.00 sec)
How did you change it? It's just a simple command once you're logged in as root.

Code: Select all

set password=password('foobar');
;)
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post by raghavan20 »

Your command worked...
I tried something given in the mysql doc...like...

Code: Select all

update mysql.user
set password = 'rootPassword'
where
user = 'root'
and 
host = '%'
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

Err, if i'm not mistaken the meta data (in this case the mysql database) of a dbms should not be altered through regular queries... But it seems you have already experienced that ;))
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post by raghavan20 »

This seems to be the alternative way to do it...

Code: Select all

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('MyNewPassword');
User avatar
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098
Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia

Post by Chris Corbyn »

raghavan20 wrote:This seems to be the alternative way to do it...

Code: Select all

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('MyNewPassword');
That's what I meant.... you don't need the FOR clause if you're already logged in as that user.
Post Reply