Skip to content

How to Set or Reset MySQL Root Password

Before MySQL 5.7

For the first time to reset root password before MySQL 5.7, you set the new password by mysqladmin:

  1. On Windows.
  2. C:Usersed>mysqladmin -u'root' password
    New password: ********
    Confirm new password: ********
  3. On Linux.
  4. [root@test ~]# mysqladmin -u root -p password
    New password: ********
    Confirm new password: ********

At a later time, you might want to reset the root password, you can do this with the same command line:

[root@test ~]# mysqladmin -u root -p password
Enter password: ********
New password: ********
Confirm new password: ********

From MySQL 5.7

Root's passwords may have already been assigned during installation, you have to find out and change it. Luckily, the official document mentioned about it.

A superuser account 'root'@'localhost is created. A password for the superuser is set and stored in the error log file. To reveal it, use the following command:

shell> sudo grep 'password' /var/log/mysqld.log

Change the root password as soon as possible by logging in with the generated, temporary password and set a custom password for the superuser account:

shell> mysql -uroot -p

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass4!';

Reset MySQL Root Password

So let's follow the instructions above.

[root@test ~]# grep 'password' /var/log/mysqld.log
2015-12-22T06:40:21.910652Z 1 [Note] A temporary password is generated for root@localhost: Dk4aQ.xghJzG

The passwords must contain at least one upper case letter, one lower case letter, one digit, and one special character, and that the total password length is at least 8 characters.

Please note that, MySQL 8.0 may not have a default password, you can just press Enter to skip it when prompting password interaction.

We use traditional mysqladmin to reset it.

[root@test ~]# mysqladmin -u root -p password
Enter password:
New password:
Confirm new password:

The default policy validate_password_policy for password is MEDIUM (=1), you can change it into STRONG (=2) in the configuration file to enforce higher security.

[root@test ~]# vi /etc/my.cnf
...
[mysqld]
...
validate_password_policy=2

Please note that, as for MySQL 8.0, the variable name of password policy is validate_password.policy. There's a tiny difference between both.

For more information about password policy, please also see MySQL 8.0 documentation:
6.5.3.2 Password Validation Options and Variables

Leave a Reply

Your email address will not be published. Required fields are marked *