Page 1 of 1

Unable to run mysql from command prompt - cmd

Posted: Mon Jan 02, 2006 9:43 am
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???

Re: Unable to run mysql from command prompt - cmd

Posted: Mon Jan 02, 2006 9:51 am
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 > _

Posted: Mon Jan 02, 2006 10:08 am
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

Posted: Mon Jan 02, 2006 10:15 am
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;

Posted: Mon Jan 02, 2006 10:23 am
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)

Posted: Mon Jan 02, 2006 1:27 pm
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

Posted: Mon Jan 02, 2006 2:06 pm
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)

Posted: Tue Jan 03, 2006 4:15 am
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.

Posted: Tue Jan 03, 2006 7:18 am
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

Posted: Tue Jan 03, 2006 4:08 pm
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)

Posted: Tue Jan 03, 2006 4:59 pm
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');
;)

Posted: Tue Jan 03, 2006 6:56 pm
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 = '%'

Posted: Tue Jan 03, 2006 8:28 pm
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 ;))

Posted: Wed Jan 04, 2006 4:39 am
by raghavan20
This seems to be the alternative way to do it...

Code: Select all

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('MyNewPassword');

Posted: Wed Jan 04, 2006 6:46 am
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.