Set MySQL Server "root" Password on CentOS

MySQL Tutorials - Herong's Tutorial Examples

∟Set MySQL Server "root" Password on CentOS

This section provides a tutorial example on how to set or reset 'root' password, in case it has no password or you can not remember the password, for MySQL 8.0 server on CentOS 8 systems.

When MySQL database server is installed on CentOS, it will create a "root" user name to access the database contents. This "root" user has all types permissions to access all database objects. But it has no password by default.

So before adding any data to your MySQL database, you should set a password for this "root" user to protect your data. Here what I did on my MySQL 8 server on my CentOS 8 computer.

1. Connect to MySQL 8 server with the "root" user without any password:

herong$ mysql -u root -p
Enter password: <Enter>

...
mysql>

2. Checking the current password of "root". I see that the "root" use has not password:

mysql> select user, host, plugin, authentication_string from mysql.user;
+------------------+-----------+-----------------------+------------------------
| user             | host      | plugin                | authentication_string
+------------------+-----------+-----------------------+------------------------
| mysql.infoschema | localhost | caching_sha2_password | $A$005$THIS...
| mysql.session    | localhost | caching_sha2_password | $A$005$THIS...
| mysql.sys        | localhost | caching_sha2_password | $A$005$THIS...
| root             | localhost | mysql_native_password |
+------------------+-----------+-----------------------+------------------------

3. Set a new password for the current login user, "root".

mysql> set password = 'TopSecret';

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

+------------------+-----------+-----------------------+------------------------
| user             | host      | plugin                | authentication_string
+------------------+-----------+-----------------------+------------------------
| mysql.infoschema | localhost | caching_sha2_password | $A$005$THIS...
| mysql.session    | localhost | caching_sha2_password | $A$005$THIS...
| mysql.sys        | localhost | caching_sha2_password | $A$005$THIS...
| root             | localhost | mysql_native_password | *8E4FBDE7FB...
+------------------+-----------+-----------------------+------------------------

4. Disconnect and connect agin with the new password:

mysql> exit;
Bye

herong$ mysql -u root -p
Enter password: TopSecret<Enter>
...
mysql>

Cool. I have added a password to the "root" user on my MySQL 8 server.

Note that there are other ways to set or reset the "root" password:

Using "mysqladmin -u root -p password new_password" command.
Using "alter user root identified by 'new_password';" MySQL command.
Using "update mysql.user set authentication_string = '' where user='root';" SQL statement to update the user table directly, which is not recommended.

If you lost the "root" password, there is only one way to recover it by running MySQL server without password protection:

herong$ sudo service mysqld stop

herong$ sudo mysqld --skip-grant-tables &

herong$ mysql -u root
mysql> set password = 'TopSecret';
mysql> quit;

herong$ killall mysqld

herong$ sudo service mysqld start