Reset Root Password Of MySQL
It provides steps to reset or change the root password of the MySQL server. You must have access to the system to stop and start the MySQL server.
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.
