How to Set or Reset MySQL Root Password

  • by

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 *