Share this Post
MySQL is a free, open-source and widely popular database used by millions of companies and individuals alike, across the globe.
With many features, it has a variety of applications such as data warehousing, logging applications, e-commerce, and other web services.
If you've forgotten your MySQL root password or want to change it, then it's important for you to know how to set/reset the MySQL root password.
In this tutorial, we will show you exactly how to set, reset and update MySQL password in Linux.
Update or Change the MySQL root Password
Step by step 👞
A complete guide on changing your password 🦮
There are several ways to change your MySQL root password. In this section, we will show you how to change the MySQL root password with examples of each method.
#1 Using the Secure Installation Script 🛡️
The simple and easiest way to change the MySQL root password with the mysql_secure_installation script.
Open your terminal and run the script as shown below:
mysql_secure_installation
You will be asked to enter your current root password as shown below:
Enter current password for root (enter for none):
Provide your root password and hit Enter to continue:
Change the root password? [Y/n] Y
New password:
Re-enter new password:
Type Y and hit Enter to change the root password. Next, answer all the questions as shown below to complete the process.
Remove anonymous users? [Y/n] Y
Disallow root login remotely? [Y/n] Y
Remove test database and access to it? [Y/n] Y
Reload privilege tables now? [Y/n] Y
#2 Command Line: mysqladmin 👮
Another way to change your MySQL root password is to use mysqladmin command line tool as shown below:
mysqladmin -u root -p password your-new-password
You will be asked to provide your current root password to change the password.
#3 Update the User Table Inside the Database 🏦
You can also change the MySQL root password by updating the user table inside the MySQL database.
To do so, first log in to your MySQL shell with your current root password:
mysql -u root -p
Provide your current root password when prompt then run the following command to change your root password:
MariaDB [(none)]> SET PASSWORD FOR 'root'@'localhost' = PASSWORD("newpassword");
Next, reload the privileges and exit from the MySQL shell with the following command:
MariaDB [(none)]> flush privileges;
MariaDB [(none)]> exit;
Recover Lost MySQL Root Password
How to Get It Back? 🔑
Guide on How to Recover Your Password 🔒
In order to recover your MySQL root password, first you will need to stop the MySQL service as shown below:
service mysql stop
Or
/etc/init.d/mysql stop
Next, start the MySQL service with the --skip-grant-tables:
mysqld_safe --skip-grant-tables &
The above command will allow you to log in to your MySQL server without a password.
Next, log in to MySQL with root user as shown below:
mysql -u root
Once logged in, change the database to MySQL and reset your root password with the following command:
MariaDB [(none)]> use mysql;
MariaDB [(none)]> update user set password=PASSWORD("rootnewpassword") where User='root';
Next, reload the privileges and exit from the MySQL shell with the following command:
MariaDB [(none)]> flush privileges;
MariaDB [(none)]> exit;
Next, stop the MySQL service with the following command:
service mysql stop
Next, start the MySQL service again with the following command:
service mysql start
Now, try to login with your new password as shown below:
mysql -u root -prootnewpassword
That's it for now. I hope you can now easily set, update and recover your MySQL root password.
