How to solve mySQL Error: : ‘Access denied for user ‘root’@’localhost’?

How to solve mySQL Error: : ‘Access denied for user ‘root’@’localhost’?

Asked on October 27, 2018 in Database.
Add Comment


  • 8 Answer(s)

    This  error can be solved by following steps:

    1. Open & Edit /etc/my.cnf
    2. Add skip-grant-tables under [mysqld]
    3. Restart Mysql
    4. You should be able to login to mysql now using the below command mysql -u root -p
    5. Run mysql> flush privileges;
    6. Set new password by ALTER USER ‘root’@’localhost’ IDENTIFIED BY ‘NewPassword’;
    7. Go back to /etc/my.cnf and remove/comment skip-grant-tables
    8. Restart Mysql
    9. Now you will be able to login with the new password mysql -u root -p
    Answered on October 27, 2018.
    Add Comment

    Here is the best solution for the problem:

    sudo mysql
    ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'root';
    

     

    Answered on October 27, 2018.
    Add Comment

    Those who uses the Ubuntu/Debian
    we can use the below command to run as root (without any password)

    sudo /usr/bin/mysql --defaults-file=/etc/mysql/debian.cnf
    
    

     

    Answered on October 27, 2018.
    Add Comment

    Use the instructions for resetting the root password – but instead of resetting the root password, we’ll going to forcefully INSERT a record into the mysql.user table

    In the init file, use this instead

    INSERT INTO mysql.user (Host, User, Password) VALUES ('%', 'root', password('YOURPASSWORD'));
    GRANT ALL ON *.* TO 'root'@'%' WITH GRANT OPTION;
    Answered on January 14, 2019.
    Add Comment
    ~$ sudo /etc/init.d/mysql stop
    ~$ sudo mysqld_safe --skip-grant-tables &
    ~$ mysql -u root
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    
    Your MySQL connection id is 1 to server version: 4.1.15-Debian_1-log
    
    Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
    
    mysql>
    
    mysql> use mysql;
    
    mysql> update user set password=PASSWORD("root") where User='root';
    
    mysql> flush privileges;
    
    mysql> quit
    
    ~$ sudo /etc/init.d/mysql stop
    
    Stopping MySQL database server: mysqld
    
    STOPPING server from pid file /var/run/mysqld/mysqld.pid
    
    mysqld_safe[6186]: ended
    
    [1]+  Done                    mysqld_safe --skip-grant-tables
    
    ~$ sudo /etc/init.d/mysql start
    
    ~$ mysql -u root -p
    
    * MySQL Community Server 5.6.35 is started
    ~$ mysql -u root -p
    Enter password: 
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 1
    Server version: 5.6.35 MySQL Community Server (GPL)
    
    Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
    
    Oracle is a registered trademark of Oracle Corporation and/or its
    affiliates. Other names may be trademarks of their respective
    owners.
    
    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    
    mysql> 
    Answered on January 14, 2019.
    Add Comment
    1. Open & Edit /etc/my.cnf or /etc/mysql/my.cnf, depending on your distro.
    2. Add skip-grant-tables under [mysqld]
    3. Restart Mysql
    4. You should be able to login to mysql now using the below command mysql -u root -p
    5. Run mysql> flush privileges;
    6. Set new password by ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewPassword';
    7. Go back to /etc/my.cnf and remove/comment skip-grant-tables
    8. Restart Mysql
    9. Now you will be able to login with the new password mysql -u root -p
    Answered on February 20, 2019.
    Add Comment

    For new linux users this could be a daunting task. Let me update this with mysql 8(the latest version available right now is 8.0.12 as on 12-Sep-2018)

    1. Open “mysqld.cnf” configuration file at “/etc/mysql/mysql.conf.d/”.
    2. Add skip-grant-tables to the next line of [mysql] text and save.
    3. Restart mysql service as “sudo service mysql restart”. Now your mysql is free of any authentication.
    4. Connect to mysql client(also known as mysql-shell) as mysql -u root -p. There is no password to be keyed in as of now.
    5. run sql command flush privileges;
    6. Reset the password now as ALTER USER ‘root’@’localhost’ IDENTIFIED BY ‘MyNewPassword’;
    7. Now let’s get back to the normal state; remove that line “skip-grant-tables” from “mysqld.cnf” and restart service.

    That’s it.

    Answered on February 20, 2019.
    Add Comment

    For Ubuntu/Debian users

    Run the following to connect as root (without any password)

    sudo /usr/bin/mysql --defaults-file=/etc/mysql/debian.cnf
    Answered on February 20, 2019.
    Add Comment


  • Your Answer

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