Error: Access denied for user 'root'@'localhost' (using password: YES)

Problem:

[ec2-user@ip-172-xx-xx-x~]$ mysql -u root -p

Error: Access denied for user 'root'@'localhost' (using password: YES)

Solution:

Step 1: Open the my.cnf file. This may be stored in:

[ec2-user@ip-172-xx-xx-x~]$ sudo vi /etc/my.cnf

/etc/my.cnf

/etc/mysql/my.cnf

If you’re not sure where it is, search your MySQL installation folder (e.g. on Windows or Mac) for the file.

Step 2: Add the word skip-grant-tables under the word [mysqld]. Your file may look like this:

[mysqld]

skip-grant-tables

Step 3: Restart the MySQL server.

[ec2-user@ip-172-xx-xx-x~]$ sudo systemctl restart mysqld.service

Step 4: Login to the root account:

[ec2-user@ip-172-xx-xx-x~]$ mysql -u root -p

Step 5: Flush the privileges, which tells the server to refresh the grant tables and apply your changes, with this command:

mysql> FLUSH PRIVILEGES;

Step 6: Set a new password for the account:

mysql>  ALTER USER 'root'@'localhost' IDENTIFIED BY 'your_new_password';

Substitute the word your_new_password with a new secure password that you want to use for the root account.

Step 7: Open the my.cnf file you opened in step 1, and remove the line about skip-grant-tables, and save the file.

Step 8: Restart the MySQL server again.

[ec2-user@ip-172-xx-xx-x~]$ sudo systemctl restart mysqld.service

Step 9: Log in to the root account again:

[ec2-user@ip-172-xx-xx-x~]$ mysql -u root -p

You should now be able to log in successfully with your new password and not get an error.