This solves it! Change the forgotten MySQL root user password

Hello!
This is Inoue, a Persian cat from Beyond Shikoku Office.

One day, I casually tried to access MySQL in my local environment, but it wouldn't work. What happened? Even if I enter the password, an error is returned.

``Is it possible to change your password to a new one even if you have forgotten it?'' ”
This blog was born as a result of trial and error!

At this time, the steps I took were as follows.

① Check the initial password
② Check that the password has been changed
③ Enter MySQL and set a new password

Please watch till the end!

Where is the initial password? ?

To check the initial password, you need to check "mysqld.log" under /var/log.
Starting from version 5.7, temporary passwords are output to "mysqld.log".
*Currently, version 8.0.33 is released. Click here for details

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 MySQL version is 5.7 or higher in my local environment!

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

When looking at less, it is difficult to find the initial password, so
use "grep" to extract the string containing the [temporary password].
From the execution results, you can see that the initial password for MySQL is the string "x=B-:.9iaqc?".
Let's dive into MySQL!
The command to enter MySQL is as follows.

mysql -u root -p

When I executed it, I was asked to enter the password, so I entered "x=B-:.9iaqc?".

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

Huh? ? ? It wasn't supposed to be like this. . .
No matter how many times I try, an error is displayed and I cannot enter MySQL.
With this, you cannot change the MySQL password to a new one. . .
Maybe you've changed your MySQL password before but can't remember it, so
try another method!

This solves it! Set 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 overwrite and save.
For detailed instructions on how to edit and save vi, see the previous blog [For Linux Beginners] Done! Please refer to
Editing and saving with vi command 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 enter MySQL, execute 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 your password to a new one, exit MySQL.

mysql> quit

Open "my.cnf" in vi again and edit it.
Next, delete "skip-grant-tables" that was added earlier and overwrite it.
Also restart MySQL.

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

Let's enter MySQL with the newly set 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 enter MySQL with a new password!

By the way, here is how to check the MySQL version after entering MySQL.

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

By the way, here's how to check the MySQL Server version.
If you are checking with the MySQL Server version, you will need the password to enter 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 enter MySQL with the initial password
was because I had changed the password before and had forgotten about it.
I forgot my MySQL password while testing in my local environment! Please try it when you have a chance.
I really don't understand MySQL. . .
I want to continue to take on challenges without feeling like I'm not good at it!

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