This article explains how to set the initial root password of MySQL 8.0 in CentOS7 and how to reset the root password after forgetting it.

1. First login

If MySQL is just installed, it will generate a random password in the log file by default, we can use it to login and reset the password.

1
2
3
4

$ sudo grep 'temporary password' /var/log/mysqld.log
2019-11-22T16:40:10.133730Z 5 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: >KG6Ybt3%lgo
$ mysql -u root -p
Enter password:

To reset the password you can use the following command.

1

ALTER user 'root'@'localhost' IDENTIFIED BY '<Your new password>';

Note that the 'localhost' here may also be another parameter. You can check it with the following command.

1

select user, host, authentication_string, plugin from mysql.user;

2. Forgot the root password

If you forget the root password, it is more troublesome. First we stop the mysqld service.

1
2

systemctl stop mysqld.service 
systemctl status mysqld.service

systemctl status mysqld.service

Then we edit /etc/my.conf to make it skip the login password check.

1

echo skip-grant-tables >> /etc/my.conf

skip-grant-tables

Then we restart the mysqld service and log in, without using a password.

1
2
3

systemctl restart mysqld.service 
systemctl status mysqld.service 
mysql -u root

mysql

Here we can see that the user account information is stored in the user table in the mysql database.

1
2
3

show databases;
use mysql;
show tables;

user table

1

select * from user\G;

select * from user

1
2
3
4
5
6
7

select host, user, authentication_string, plugin from user;
-- host: The ip 'location' % where the user is allowed to log in indicates that remote access is possible.
-- user:The user name of the current database.
-- authentication_string: user passwords; the password field and password() function are deprecated in mysql 5.7.9 onwards.
-- plugin： Password encryption method.
update user set authentication_string='' where user='root';
select host, user, authentication_string, plugin from user;

mysql

Then we go ahead and edit /etc/my.conf to remove the line skip-grant-tables that we just added, and restart mysql.

Then we log in, and this time we still don’t need to enter a password.

Once we are logged in, we use this command to set a new password.

1

ALTER user 'root'@'localhost' IDENTIFIED BY '<Your new password>';

Be careful here that the new password is as complex as possible. It is best to include upper and lower case alphanumerics and symbols, otherwise you may be prompted for non-compliance.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

$ systemctl restart mysqld.service 
$ mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.18

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> use mysql
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
mysql> show tables;
ERROR 1046 (3D000): No database selected
mysql> ALTER user 'root'@'localhost' IDENTIFIED BY 'centos7';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
mysql> ALTER user 'root'@'localhost' IDENTIFIED BY 'MySQLNB8@2333';
Query OK, 0 rows affected (0.01 sec)

mysql>

At this point, log out and log back in to see that the new password we set is now in effect.