Oops! Did you forget the root password for MySQL? Here is how to resolve this issue.

Where is a default password

In order to find out where a default password is, you need to refer to a path /var/log, and open mysqld.log.

Note that up until MySQL version 5.6, there was no temporary mysqld.log. However, from version 5.7, that log came out. If you use an older version (~ version 5.6), you might not be able to find the log. To find it out, check out the Mysql client version.

My local environment says that the version of MySQL is above 5.7.

[root@test-aws-harukainoue ~]# mysql --version
mysql  Ver 14.14 Distrib 5.7.25, for Linux (x86_64) using  EditLine wrapper

If you move to the path /var/log, make sure that you are in the right directory. To check it, try pwd command, and to see the log file you can use less command. However, it is hard to figure out where a default password is, so as you can combine grep with less command in order to extract the string of the temporary password. Below we found that the default password is "x=B-:.9iaqc?".

[root@test-aws-harukainoue log]# pwd
/var/log
[root@test-aws-harukainoue log]# less mysqld.log | grep "temporary password"
2019-04-08T02:32:37.248018Z 1 [Note] A temporary password is generated for root@localhost: x=B-:.9iaqc?

Now, we can log in to MySQL.

mysql -u root -p

Execute the command above, then right away you will be required to put a password here like the following. However, notice that there was an error, saying "Access denied for user 'root'@'localhost' (using password: YES)"

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

It was not clear why the error occurred at this time, and we decided to do another way in order to log in the MySQL.

Set up a new password

Try the vi command, and edit a conf file, my.cnf.

[root@test-aws-harukainoue etc]# vi /etc/my.cnf

After opening the conf file, try to write this phrase, skip-grant-tables in the below of [mysqld], and then overwrite the file with ":wq" after hitting the [ESC] button.

To update the change, restart the MySQL server.

[root@test-aws-harukainoue etc]# systemctl restart mysqld

If you successfully updated the change, then you should be able to log into MySQL.

Voila!! It worked.

[root@test-aws-harukainoue etc]# mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.25 MySQL Community Server (GPL)
 
Copyright (c) 2000, 2019, 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>

We finally logged in to MySQL, and at first, execute "flush privileges" in order to update the change in the permission. After that, set up a new password so that you typed the command "ALTER USER 'root'@'localhost' IDENTIFIED BY 'Newpassword';".

(*Do not forget a semi-colon. Without it, you cannot execute the command in MySQL)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
 
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'Newpassword';
Query OK, 0 rows affected (0.00 sec)

We set the new password, and exit the MySQL.

mysql> quit

We fixed the problem. This time, we will delete the skip-grant-tables which we added to the my.cnf above, and overwrite the conf file. Again, restart the mysqld to update the change.

[root@test-aws-harukainoue etc]# systemctl restart mysqld

Let us try to log in to MySQL with the new password we set in this article.

[root@test-aws-harukainoue etc]# mysql -u root -p
Enter password:Newpassword
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.25 MySQL Community Server (GPL)
 
Copyright (c) 2000, 2019, 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>

Login confirmed！

If you want to know which version of MySQL you installed into your server, you can execute the following command, "select version();"

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.25    |
+-----------+
1 row in set (0.00 sec)

In addition to the client mysql version, if you want to know which version of mysql server you have used, then execute the following command. This time, it is necessary to type the password which is used to log in to the mysql.

[root@test-aws-harukainoue ~]# mysqladmin -u root -p version
Enter password:Newpassword
mysqladmin  Ver 8.42 Distrib 5.7.25, for Linux on x86_64
Copyright (c) 2000, 2019, 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.
 
Server version          5.7.25
Protocol version        10
Connection              Localhost via UNIX socket
UNIX socket             /var/lib/mysql/mysql.sock
Uptime:                 21 min 2 sec
 
Threads: 1  Questions: 2  Slow queries: 0  Opens: 105  Flush tables: 1  Open tables: 98  Queries per second avg: 0.001

Summary

This time we couldn't log in to mysql with the default password. It is because of changing the default password and forgetting about the fact we did it.

If you forget the password even in your local environment, you can give it a try until you successfully reset it. Not giving up is a key to achieving good results from what you are not good at.

This blog post is translated form of the blog post written by Inoue in our Shikoku office.