Reset Root Password Of MySQL

By bhagwatchouhan
Reset Root Password Of MySQL

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/mysqld' for UNIX socket file don't exists. We have to create the mysqld directory and make mysql as the owner using the commands as shown below.

# 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/mysqld/mysqld.sock'. In such a case, follow the below-mentioned commands to resolve it.

# 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 ampersand (&) at the end of this command. It also enables --skip-networking automatically to prevent remote connections.

# Start without password
sudo mysqld_safe --skip-grant-tables &

You might get the error having a message - mysqld_safe Directory '/var/run/mysqld' for UNIX socket file don't exists. We have to create the mysqld directory and make mysql as the owner using the commands as shown below.

# 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/mysqld/mysqld.sock'. In such case, follow the below-mentioned commands for Ubuntu to resolve it.

# 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.

Share this blog:

Profile picture for user bhagwatchouhan
bhagwatchouhan