DEV Community

th3c0r
th3c0r

Posted on

Reset MySQL Root Password (Version 8 and above)

Today, I faced a problem while trying to reset the root password for a MySQL database. In order to help others who may be in a similar situation, I am sharing a tutorial that outlines the steps that worked for me.

While there are many resources available on this topic, I hope that this post will be a helpful addition.

To follow these steps, you will need access to the server using SSH.

Stop the MySQL process using the following command:

sudo service mysql stop
Enter fullscreen mode Exit fullscreen mode

Start the MySQL service with the --skip-grant-tables option, which allows you to connect to the MySQL server without a password:

sudo mysqld_safe --skip-grant-tables &
Enter fullscreen mode Exit fullscreen mode

If you encounter an error saying "mysqld_safe Directory '/var/run/mysqld' for UNIX socket file don't exists," try creating the directory and setting the correct ownership and permissions with these commands:

sudo mkdir /var/run/mysqld
sudo chown mysql:mysql /var/run/mysqld
sudo chmod 755 /var/run/mysqld
Enter fullscreen mode Exit fullscreen mode

Connect to the MySQL server as the root user:

mysql -u root
Enter fullscreen mode Exit fullscreen mode

Run the following MySQL commands to reset the root password:

UPDATE mysql.user SET authentication_string=null WHERE User='root';
FLUSH PRIVILEGES;
exit;
Enter fullscreen mode Exit fullscreen mode

Connect to the MySQL server again as the root user:

mysql -u root
Enter fullscreen mode Exit fullscreen mode

Set a new password for the root user using the ALTER USER command. Be sure to use a strong, unique password:

ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY '{mypassword}';
Enter fullscreen mode Exit fullscreen mode

Replace *{mypassword}** with your desired password.*

Stop the running mysqld process:

sudo killall -KILL mysql mysqld_safe mysqld
Enter fullscreen mode Exit fullscreen mode

Start the MySQL process normally and log in with your new password:

service mysql start
mysql -u root -p
Enter fullscreen mode Exit fullscreen mode

Hostinger image

Get n8n VPS hosting 3x cheaper than a cloud solution

Get fast, easy, secure n8n VPS hosting from $4.99/mo at Hostinger. Automate any workflow using a pre-installed n8n application and no-code customization.

Start now

Top comments (0)

Billboard image

Try REST API Generation for Snowflake

DevOps for Private APIs. Automate the building, securing, and documenting of internal/private REST APIs with built-in enterprise security on bare-metal, VMs, or containers.

  • Auto-generated live APIs mapped from Snowflake database schema
  • Interactive Swagger API documentation
  • Scripting engine to customize your API
  • Built-in role-based access control

Learn more

👋 Kindness is contagious

Please leave a ❤️ or a friendly comment on this post if you found it helpful!

Okay