How to reset root or any user's password in MySQL

Submitted by gvso on Tue, 02/28/2017 - 23:48

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.

  1. Stop MySQL service
    [root ~]# service mysql stop   
    mysql stop/waiting
  2. 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

    mkdir /var/run/mysqld

 

At this moment, the terminal will seem to halt. Let that be, and open a new terminal with root privileges for next steps.

  1. Enter the mysql command prompt. 

    [root ~]# mysql -u root
    mysql> 
  2. 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)
  1. 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
  2. Now you can successfully login as root user with the password you set

     [root ~]# mysql -u root -pnewPassword 
     mysql> 
Tags