This tutorial provides steps to reset or change the root password of the MySQL server in case the user forgot the password. It assumes that you have access to the system to stop and start the MySQL server. This tutorial is specific to MySQL 8.0 and above, though it should work out of the box for older versions of MySQL including MySQL 5.7. We can reset the root password in two ways as mentioned below.
Notes: You may also follow the MySQL tutorials - How To Install MySQL 8 on Ubuntu, How To Completely Remove MySQL From Ubuntu, and Learn Basic SQL Queries Using MySQL.
Update Password
We can simply update the root password of MySQL in case we already know it. It can be done using the commands as shown below. The ALTER command is the preferred one since it works on MySQL 5.7 and MySQL 8, though you can follow any of the commands.
# Login to MySQL mysql -uroot -p # OR mysql -u root -p
# MySQL - 5.7.5 and earlier
UPDATE mysql.user SET password=PASSWORD('password') WHERE user='root';
# MySQL - 5.7.6 and newer UPDATE mysql.user SET authentication_string=PASSWORD("password") where user='root'; # OR SET PASSWORD FOR 'root'@'localhost' = PASSWORD("password");
# MySQL - 5.7, 8 ALTER USER 'root'@'localhost' IDENTIFIED BY '<password>'; # OR ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '<password>';
# MySQL - 8 ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY '<password>';
# Flush
FLUSH PRIVILEGES;
# Disconnect
quit;
In case you have forgotten your root password, you can follow either Process A or Process B as mentioned below.
Process A - Secure
In this process, we will stop and start the MySQL server to use the init script to change the root password.
Step 1: Stop the Server
We have to stop the server as the first step of this process. It can be done using the commands as shown below.
# Using init sudo /etc/init.d/mysqld stop # OR sudo /etc/init.d/mysql stop
# Using service
sudo service mysql stop
# Using systemd sudo systemctl stop mysqld.service # OR sudo systemctl stop mysql
Step 2: Create the Init File
Now create the init file and add the command to update the root password as shown below.
# Create Init File - Use your preferred editor sudo nano <path to init file>init-file.txt
# Add the query to update password # MySQL - 5.7, 8 ALTER USER 'root'@'localhost' IDENTIFIED BY '<password>'; # OR ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '<password>';
# MySQL - 8 ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY '<password>';
Step 3: Start MySQL Server
Now start the MySQL server using the init file as shown below.
# Start the server sudo mysqld --init-file=<path to init file>init-file.txt & # OR sudo mysqld_safe --init-file=<path to init file>init-file.txt &
It might throw a bunch of errors depending on your server installation.
You might get the error having a message - mysqld_safe Directory '/var/run/
# Stop the server
# Make directory sudo mkdir -p /var/run/mysqld
# Change owner sudo chown mysql:mysql /var/run/mysqld
# Start with init file sudo mysqld_safe --init-file=<path to init file>init-file.txt &
You might get the error having message - ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/
# Start MySQL Server normally - Ubuntu sudo service mysql start
# Navigate to sock directory cd /var/run
# Take backup - sock sudo cp -rp ./mysqld ./mysqld.bak
# Stop the server normally - Ubuntu sudo service mysql stop
# Restore the sock sudo mv ./mysqld.bak ./mysqld
# Start MySQL in unsafe mode sudo mysqld_safe --skip-grant-tables &
Step 4: Stop and Start the MySQL server
Now stop and start the MySQL server using the regular commands as shown below.
# Using init sudo /etc/init.d/mysqld stop sudo /etc/init.d/mysqld start # OR sudo /etc/init.d/mysql stop sudo /etc/init.d/mysql start
# Using service sudo service mysql stop sudo service mysql start
# Using systemd sudo systemctl stop mysqld.service sudo systemctl start mysqld.service # OR sudo systemctl stop mysql sudo systemctl start mysql
Terminate the existing processes if required. Use only if the above commands do not work to stop and start the server.
# Find the processes ps aux | grep mysqld ps aux | grep mysql
# Kill the processes sudo killall mysqld sudo killall mysql
Step 5: Test Password
Finally, test the new password using the command as shown below.
# Test new password mysql -u root -p
Make sure to delete the init file after testing your new password. In case it does not work out, you can follow the Process B.
Process B - Less Secure
In this process, we will stop and then start the MySQL server without requiring any password to log in.
Step 1: Stop the Server
We have to stop the currently running MySQL server as the first step to complete this process. It can be done using the commands as shown below.
# Using init sudo /etc/init.d/mysqld stop # OR sudo /etc/init.d/mysql stop
# Using service sudo service mysql stop
# Using systemd sudo systemctl stop mysqld.service # OR sudo systemctl stop mysql
Step 2: Start MySQL without password
Now start MySQL server with password disabled using the command as shown below. Make sure to add
# Start without password sudo mysqld_safe --skip-grant-tables &
You might get the error having a message - mysqld_safe Directory '/var/run/
# Stop the server
# Make directory
sudo mkdir -p /var/run/mysqld
# Change owner
sudo chown mysql:mysql /var/run/mysqld
# Start without password
sudo mysqld_safe --skip-grant-tables &
You might get the error having message - ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/
# Start MySQL Server sudo service mysql start
# Navigate to sock directory cd /var/run
# Tack backup - sock sudo cp -rp ./mysqld ./mysqld.bak
# Stop the server sudo service mysql stop
# Restore the sock
sudo mv ./mysqld.bak ./mysqld
# Start MySQL in unsafe mode sudo mysqld_safe --skip-grant-tables &
Step 3: Connect to MySQL
Now open another terminal or connect to the server via another shell to connect the client.
# Connect Directly mysql
# Connect as Root mysql -uroot # OR mysql -u root
Step 4: Change password
In this step, change the root password using the commands as shown below. You can also refer to the section Update Password of this tutorial to use other commands to change the password.
# Change Password
# MySQL - 5.7, 8
ALTER USER 'root'@'localhost' IDENTIFIED BY '<password>'; # OR ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '<password>';
# MySQL - 8
ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY '<password>';
# Flush
FLUSH PRIVILEGES;
# Disconnect quit;
Step 5: Stop and Start the MySQL server
Now stop and start the MySQL server using the regular commands as shown below.
# Using init sudo /etc/init.d/mysqld stop sudo /etc/init.d/mysqld start # OR sudo /etc/init.d/mysql stop sudo /etc/init.d/mysql start
# Using service sudo service mysql stop sudo service mysql start
# Using systemd
sudo systemctl stop mysqld.service
sudo systemctl start mysqld.service # OR sudo systemctl stop mysql sudo systemctl start mysql
Terminate the existing processes if required. Use only if the above commands do not work to stop and start the server.
# Find the processes ps aux | grep mysqld ps aux | grep mysql
# Kill the processes
sudo killall mysqld sudo killall mysql
Step 6: Test Password
Finally, test the new password using the command as shown below.
# Test new password mysql -u root -p
Summary
This is how we can reset or change the password of root user or any other user in case we have forgotten the password.
