Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I've installed a MySQL server in CentOS 6.2 machine

+-------------------------+---------------------+
| Variable_name           | Value               |
+-------------------------+---------------------+
| protocol_version        | 10                  |
| version                 | 5.1.61-log          |
| version_comment         | Source distribution |
| version_compile_machine | i386                |
| version_compile_os      | redhat-linux-gnu    |
+-------------------------+---------------------+

After the installation I executed this command:

/usr/bin/mysqladmin -u root password Pa$$w0rd

Now I can only access the server using the command:

mysql -uroot -pPa$$w0rd

But I'd like to do it using:

mysql -uroot -p

and after that, inform the password.

Everytime I try that I get an error:

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
share|improve this question
add comment

1 Answer

up vote 0 down vote accepted

Please run the following:

mysql -uroot -pPa$$w0rd -ANe"SELECT USER() ReqUserLogin,CURRENT_USER() AllowedUserLogin;"
mysql -uroot -pPa$$w0rd -ANe"SELECT user,host,password FROM mysql.user WHERE user='root'"

The first query will show you two things:

  • USER() reports how you attempted to authenticate in MySQL
  • CURRENT_USER() reports how you were allowed to authenticate in MySQL

The second query will show you all root users. It is possible only one root user got the password embedded and left the other without one.

Run this query to assign the same password to all root users:

UPDATE mysql.user A INNER JOIN mysql.user B USING (user)
SET A.password=B.password WHERE A.password='' AND B.password<>'';
FLUSH PRIVILEGES;

While you are at this point, you may as well get rid of anonymous users:

DELETE FROM mysql.user WHERE user=''; FLUSH PRIVILEGES;

and disable public access to test databases

DELETE FROM mysql.db WHERE SUBSTR(db,4) = 'test';
FLUSH PRIVILEGES;

I wrote about this before:

UPDATE 2012-06-13 15:58 EDT

I think I understand the problem. Look back at your original command to create the password:

/usr/bin/mysqladmin -u root password Pa$$w0rd

In Linux, $$ stands for something. Just run echo $$ and see:

[root@... ~]# echo $$
30837
[root@... ~]# ps -ef | grep 30837
root      2354 30837  0 15:50 pts/0    00:00:00 ps -ef
root      2355 30837  0 15:50 pts/0    00:00:00 grep 30837
root     30837 30835  0 14:49 pts/0    00:00:00 -bash
[root@... ~]#

You should have done this:

/usr/bin/mysqladmin -u root password 'Pa$$w0rd'

Here is a working theory. Run that command echo $$ (let's pretend $$ is 30837)

Try to login like this:

mysql -u root password -p"Pa$$w0rd"

If this does not work, try this:

mysql -u root password -pPa30387w0rd

If this last one works, you have the password with the number embedded.

Here is how to change it to Pa$$w0rd:

mysql -u root password -pPa$$w0rd -e"SET PASSWORD = PASSWORD('Pa$$w0rd');"

Give it a Try !!!

share|improve this answer
    
The first query returned root@localhost for the USER() and CURRENT_USER(). The second query return root for 3 different hosts: localhost, localhost.localdomain and 127.0.0.1. Only root@localhost had its password set. The other two were empty. I ran the command UPDATE mysql.user A INNER JOIN mysql.user B USING (user) SET A.password=B.password WHERE A.password='' AND B.password<>''; FLUSH PRIVILEGES; And after that the other two had passwords, but the issue continues. –  VinTem Jun 13 '12 at 19:42
    
I have an alternate suggestion. I updated my answer... –  RolandoMySQLDBA Jun 13 '12 at 19:57
    
Perfect, that was it!!! –  VinTem Jun 13 '12 at 20:19
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.