Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.
Moderator: General Moderators
raghavan20
DevNet Resident
Posts: 1451 Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:
Post
by raghavan20 » Mon Jan 02, 2006 9:43 am
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???
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098 Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia
Post
by Chris Corbyn » Mon Jan 02, 2006 9:51 am
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 > _
raghavan20
DevNet Resident
Posts: 1451 Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:
Post
by raghavan20 » Mon Jan 02, 2006 10:08 am
I am able to login using the following
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
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098 Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia
Post
by Chris Corbyn » Mon Jan 02, 2006 10:15 am
raghavan20 wrote: I am able to login using the following
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:
... and to see the servers they are bound to:
Code: Select all
SELECT host, user from mysql.user;
raghavan20
DevNet Resident
Posts: 1451 Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:
Post
by raghavan20 » Mon Jan 02, 2006 10:23 am
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)
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098 Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia
Post
by Chris Corbyn » Mon Jan 02, 2006 1:27 pm
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 » Mon Jan 02, 2006 2:06 pm
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)
raghavan20
DevNet Resident
Posts: 1451 Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:
Post
by raghavan20 » Tue Jan 03, 2006 4:15 am
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.
twigletmac
Her Royal Site Adminness
Posts: 5371 Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK
Post
by twigletmac » Tue Jan 03, 2006 7:18 am
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
raghavan20
DevNet Resident
Posts: 1451 Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:
Post
by raghavan20 » Tue Jan 03, 2006 4:08 pm
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)
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098 Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia
Post
by Chris Corbyn » Tue Jan 03, 2006 4:59 pm
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.
raghavan20
DevNet Resident
Posts: 1451 Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:
Post
by raghavan20 » Tue Jan 03, 2006 6:56 pm
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 » Tue Jan 03, 2006 8:28 pm
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
)
raghavan20
DevNet Resident
Posts: 1451 Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:
Post
by raghavan20 » Wed Jan 04, 2006 4:39 am
This seems to be the alternative way to do it...
Code: Select all
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('MyNewPassword');
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098 Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia
Post
by Chris Corbyn » Wed Jan 04, 2006 6:46 am
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.