[HOW TO] Reset a MySQL root password

lc-root | March 25, 2019 | How To, Manuals | No Comments

To access the MySQL or MariaDB database, you need to enter a username and password. During installation, a root user account is automatically created, which is an analog of superuser in Linux for MySQL. It can perform all actions with all databases, as well as create and delete other users.

Usually, the password is not set for it, and you can set it manually after the installation is completed. But if you forgot your password or it was set automatically, but you don’t know it, then you may need to reset it. This can be done in several ways. In this article we will analyze how MySQL root password reset is performed.

In the article we will use Ubuntu and MariaDB, but this instruction is also suitable for other distributions. For commands with systemctl for Mariadb, you should use mariadb, and for MySQL, use mysql or mysqld.

Reset password using command (if logged in)
Reset password using init-file
Reset password using skip-grant-tables
Conclusion

Reset password using command (if logged in)

mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('newpassword');

Reset password using init-file

When you start the MySQL service, you can specify the file from which the sql commands will be executed immediately after launch. The address of this file is indicated by the option –init-file. But first, let’s create a file that will modify our user’s file:

$ vi /home/sergiy/init-file.txt

UPDATE mysql.user SET password=password('NEWPASSWORD') WHERE user='root';

Then stop the service if it is running:

$ sudo systemctl stop mysql

$ sudo systemctl stop mariadb

Now it the time to execute our file:

$ sudo mysqld --user=mysql --init-file=/home/sergiy/init-file.txt --console

Wait a minute for everything to work as it should, and then stop the process. In the console, the command will report started as proccess and the PID of the process; we need this PID to stop it. For example:

$ sudo kill -TERM 5356

Next, start mysql in the usual way and try to log in with your new password:

$ sudo systemctl start mariadb

$ sudo mysql -u root -p

[adinserter block=”6″]

Reset password using skip-grant-tables

In addition, there is another way to reset the MySQL password. We can start the service with the –skip-grant-tables option. In this mode, the program skips downloading user data and then you can log in without entering a password. Similarly, you first need to stop the service:

$ sudo systemctl stop mariadb

Then start mysql manually using this command:

$ sudo mysqld --user=mysql --skip-grant-tables

Log into the mysql management console:

$ mysql -u root

Since we loaded without user privilege tables, then this tables need to be loaded now:

FLUSH PRIVILEGES;

Now you can change the password for the root user:

UPDATE mysql.user SET password=password('NEWPASSWORD') WHERE user='root';

Then close the mysql client console:

exit

Kill the manually started service in the same way as in the previous paragraph:

$ sudo kill -TERM 5356

Next, start mysql in the usual way and try to log in with your new password:

$ sudo systemctl start mariadb

$ sudo mysql -u root -p

But, it should be noted that for the latest versions of MariaDB this is no longer necessary, since the program supports authorization without a password if the database user name and the account name in the system is the same. Therefore, if you want to log in as root to the MySQL console, it is enough to run the client through sudo.

Conclusion

In this article, we looked at how to reset Ubuntu MySQL password. The developers are constantly changing something in the code, so in the latest versions ALTER USER or SET PASSWORD command may not work. Therefore, we use the universal UPDATE command. I hope this information was helpful to you.