This solves the problem! Change your forgotten MySQL root user password

Hello!
I'm Inoue, the Persian cat at Beyond Shikoku office.

One day, I was trying to log into MySQL on my local computer, but I couldn't. I entered my password and got an error message

"how to change to a new password when I've forgotten my original one!"
This blog was born from my trial and error in trying to figure out

In this case, the steps I took were as follows:

① Check the initial password.
② Confirm that the password has been changed.
③ Log in to MySQL and set a new password.

Please watch until the end!

Where is the initial password?

To check the initial password, you need to look at "mysqld.log" under /var/log.
The temporary password is now output to "mysqld.log" starting with version 5.7.
*Currently, versions up to 8.0.33 are available.See here

To check the MySQL client version, enter the following command:

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

I found that my local MySQL version is 5.7 or higher!

[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?

Since it's difficult to find the initial password using `less`, we
'll use `grep` to extract strings containing `temporary password`.
From the execution result, we can see that the initial password for MySQL is the string "x=B-:.9iaqc?".
Now let's log into MySQL!
The command to log into MySQL is as follows:

mysql -u root -p

When I ran it, I was prompted to enter a password, so I entered "x=B-:.9iaqc?"

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

Huh??? This isn't how it should be...
No matter how many times I try, I keep getting an error message and can't access MySQL.
This means I can't change my MySQL password to a new one...
Maybe I changed my MySQL password before but can't remember it, so
I'll try a different method!

This solves the problem! Set a new password

Open "my.cnf" in vi and edit it

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

Enter "skip-grant-tables" under [mysqld] and save the file.
the previous blog post,"[For Linux Beginners] I Did It! Editing and Saving with vi Commands", please refer to
Restart MySQL.

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

Enter MySQL

[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>

Now that you can log in to MySQL, run the following to reflect the permissions and set a new password

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)

Now that you have changed the password to a new one, exit MySQL

mysql> quit

Open "my.cnf" again using vi and edit it.
This time, delete the "skip-grant-tables" entry you added earlier and save the file.
Also, restart MySQL.

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

Now let's log in to MySQL with the new password!

[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>

Success! I was able to log into MySQL with my new password!

By the way, here's how to check the MySQL version after entering MySQL

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

Also, here's how to check the MySQL server version.
To check the MySQL server version, you'll need the password you use to log into 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

Finally

the reason I couldn't log into MySQL with the default password
was because I had changed it previously and forgotten about it.
If you ever forget your MySQL password while testing in your local environment, try this!
MySQL is really confusing...
But I want to keep challenging myself without developing a negative attitude towards it!

Growing every day, moving forward every day.
I must update myself every single day!!!
Thank you for reading to the end.