ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

Question

So at first I was getting this error when trying to create databases for use in phpmyadmin. ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)

I looked in several places and a buddy of mine told me to just attempt to re install mysql since I just re-installed the server yesterday.

So I decided to re install mysql, and now I can't even run the installer without getting this error: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

I had also got the 'root'@'localhost' error when trying to install a script with the database I created. This is the error I get when I had tried to install the script: Running Centos 6

PHP 5.3.3 (Script I am using is Ioncubed, and PHP 5.4, I don't need it).

EDIT:

If you are going to question about why don't I look around first, I have looked around! I ain't having luck finding others who had the type of issue as I did.

EDIT 2:

I successfully installed MySQL now. I felt like going back and giving it another go and the installer went fine. But I still get the root@localhost error provided in the image above. I already checked to make sure the config wasn't magically using "root"@"localhost".

Answer 1

Configuration of the database does use root@localhost, and that password was chosen when you installed it for the first time. It is not the system's root password. MySQL root and system's root are different accounts with possible different passwords.

Try to remember and try to guess it by using mysql -u root -p to try. If no luck, search Google for how to remove MySQL configuration and start anew.

Answer 2

MySQL treats differently "localhost" and "127.0.0.1"; the first is used to run a Unix socket connection on /var/lib/mysql/mysql.sock or whatever. So you need two different GRANT instructions in your mysql.user table (at least I do on OpenSuSE Linux; on e.g. Windows your mileage may vary):

(mysql connection as root)

GRANT ALL PRIVILEGES (*or privilege list*) ON yourdb.* TO user@localhost IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES (*or privilege list*) ON yourdb.* TO [email protected] IDENTIFIED BY 'password';
FLUSH PRIVILEGES;

Then, it is obvious that your script is indeed trying to install as "root@localhost". If you can't edit the script or the installer config, you can work through that by temporarily disabling root password:

USE mysql;
UPDATE user SET Password = NULL WHERE User='root' AND Host='localhost';
FLUSH PRIVILEGES;

...for greater safety, do not logout from mysql after doing this. After the install has completed, you will have to reset the root password:

GRANT ALL PRIVILEGES ON mysql.* TO root@localhost IDENTIFIED BY 'newpassword';
FLUSH PRIVILEGES;

This does not solve the problem of what does the script do after install. Chances are that it will still use root at localhost, and still use no password. So as soon as you reset the password, it will stop working. You need to find references to root and localhost (they may be specified separately) in the script's config: there must be something like

user = root
host = localhost
dbname = somedb

or maybe

'username' => 'root',
"hostaddr" => "localhost",
...

and change them. You can create a new user in MySQL by just issuing a new GRANT statement.

Answer 3

In normal cases, stack_overflow is helpful. However, in case of conflict of different version, password can not reset successfully. In this case, please re-install the mysql. That is what i meet!!

Here is the steps: 1) find the installed mysql
# rpm -qa|grep mysql

2) remove the mysql
#rpm -e 'the programe'

3)delete the lib&include
#rm -rf /usr/lib/mysql
#rm -rf /usr/include/mysql

4)delelte the mysql date or backup it.
#mv /var/lib/mysql ~/mysql_backup

5)reinstall mysql
#yum install mysql mysql-server

6)start mysql service and try it
#chkconfig mysqld on
#service mysqld start
#mysql -uroot -p
--in this step, if install succeed, the password should be null. just press enter and get into mysql database.