Reset your MySql server password

Reset lost MySQL root password with a quick trick

Few days ago I learned an interesting trick that allows you to reset the password on a MySql server. This trick is pretty useful in case you have lost (or you haven’t ever had) the password of your MySql root user.

You just need to throw some commands at the console. Note that I am assuming you are using Ubuntu or some other Debian derivate. Otherwise the commands may change a bit (but not to much I believe).

Reset your MySql server password

1. Stop the mysql daemon

sudo /etc/init.d/mysql stopRun

2. Disable the security checks

mysqld_safe --skip-grant-tables &

The mysqld_safe command will essentially restarts your MySql server but with the option --skip-grant-tables it also disables the grant tables used for authentication. You might think that this way the MySql server will deny every access attempt, but it will do the very contrary: it will allow any! That’s in fact where the trick lies: this way we are able to login to MySql as root (even if we don’t know the password) and edit the MySql users table to reset the root user password.

Yeah, I didn’t think this was possible before discovering it. But it is, and, even if it feels “insecure”, it might be useful in cases like this.

3. Login as root

mysql -u root -p mysql

It will ask you for a password… Yes, you get it! You can enter whatever you want and it will always grant you access as root! The last argument (mysql) specifies you want to access the MySql internal database (where user credentials are stored).

4. Change the root password

You’re using the MySql shell now. Just run:

update user set password=PASSWORD("NEW-ROOT-PASSWORD") where User="root";
exit;

You need to change the "NEW-ROOT-PASSWORD" with a password of your choice (obviously).

5. Restart MySql

You’re back in your bash shell:

sudo service mysql restart

This will restart the MySql server and will enable the security checks again.

So that’s all! You can now login into your MySql server with the new password! And try to not lose it again, at least for a while ;)

Sharing is caring!

If you got value from this article, please consider sharing it with your friends and colleagues.

Found a typo or something that can be improved?

In the spirit of Open Source, you can contribute to this article by submitting a PR on GitHub.

You might also like

Cover picture for a blog post titled Using Let’s Encrypt and Certbot to automate the creation of certificates for OpenVPN

Using Let’s Encrypt and Certbot to automate the creation of certificates for OpenVPN

This post explains how to use Let's Encrypt and Certbot to automatically generate and renew SSL certificates for OpenVPN. It provides a complete Terraform setup as a practical example.

Calendar Icon

Cover picture for a blog post titled Gulp and FTP: update a website "on the fly"

Gulp and FTP: update a website "on the fly"

This tutorial explains how to use Gulp and vinyl-ftp to watch local files for changes and automatically upload updates to a website via FTP. Useful for quickly editing legacy sites only accessible through FTP.

Calendar Icon

Cover picture for a blog post titled Versioning and deploying a static website with Git, Flightplan and Nginx

Versioning and deploying a static website with Git, Flightplan and Nginx

This blog post provides a beginner's guide to managing versioning and deployment of static websites using Git for version control, Flightplan.js for automated deployment, and Nginx for serving. It outlines a simple yet complete workflow for implementing continuous delivery and rollbacks.

Calendar Icon

Cover picture for a blog post titled 8 invitations to try Keybase.io

8 invitations to try Keybase.io

Keybase.io is a new service that combines asymmetric cryptography with a social network. It allows users to easily share public keys and authenticate messages by linking keys to profiles on Twitter, GitHub, Reddit, etc. The service provides encrypted messaging and bitcoin wallet pairing to make adopting cryptography seamless.

Calendar Icon

Cover picture for a blog post titled Developing a web application with Lumen and MySql

Developing a web application with Lumen and MySql

This tutorial shows step-by-step how to bootstrap a Lumen project, configure MySQL, create migrations and models, seed the database, define routes and templates to build a fully working motivational quote web app in less than 30 minutes.

Calendar Icon

Cover picture for a blog post titled Simple echo server written in Go, dockerized!

Simple echo server written in Go, dockerized!

By writing a Dockerfile we can containerize a simple Go echo server app. The Dockerfile installs Go, copies the server code, exposes the port, and defines the command to run the app. Building the Dockerfile produces an image that can be run as a container. The containerized Go app can then be easily distributed and run anywhere Docker is installed.

Calendar Icon