This week I run into the following issue when trying to connect to my new MySQL server installation
mysql -u root ERROR 1045 (28000): Access denied for user 'root'@'localhost'
This was somehow strange since it was a local installation, and I did not remember entering a default password for the root user. I was able to solve this issue by following @user2977819's comment. The solution is basically resetting the root user's password.
- Stop MySQL service
[root ~]# service mysql stop mysql stop/waiting
Start mysql without any privileges using the following option. This option enables anyone to connect without a password and with all privileges, and disables account-management statements such as ALTER USER and SET PASSWORD
[root ~]# mysqld_safe --skip-grant-tables &
If you also get an error such as
mysqld_safe Directory '/var/run/mysqld' for UNIX socket file don't exists.
Create the directory with
At this moment, the terminal will seem to halt. Let that be, and open a new terminal with root privileges for next steps.
Enter the mysql command prompt.
[root ~]# mysql -u root mysql>
Fix the permission setting of the root user
mysql> USE mysql; Database changed mysql> TRUNCATE TABLE user; Query OK, 0 rows affected (0.00 sec) mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.01 sec) mysql> GRANT ALL PRIVILEGES ON *.* TO root@localhost IDENTIFIED BY 'newPassword' WITH GRANT OPTION; Query OK, 0 rows affected (0.01 sec)
*if you don`t want any password or rather an empty password
mysql> GRANT ALL PRIVILEGES ON *.* TO root@localhost IDENTIFIED BY '' WITH GRANT OPTION; Query OK, 0 rows affected (0.01 sec)* mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.00 sec)
Confirm the results:
mysql> SELECT host, user FROM user; +-----------+------+ | host | user | +-----------+------+ | localhost | root | +-----------+------+ 1 row in set (0.00 sec)
Exit the shell and restart mysql in normal mode.
mysql> quit; [root ~]# kill -KILL [PID of mysqld] (usually /var/run/mysqld/mysqld.pid) or [root ~]# killall mysqld [root ~]# service mysql start
Now you can successfully login as root user with the password you set
[root ~]# mysql -u root -pnewPassword mysql>