Jesse Lawson

buy me a coffee ☕ / GitHub / Mastodon

Jan 1, 0001 - MySQL sysadmin

Reset MySQL Root Password on Ubuntu

#Mysql #Password #Root

Do you know why there are so many tutorials online about resetting the MySQL root password? Because so many people are doing it wrong. This is a down-and-dirty, super quick way to reset your root password for MySQL.

This procedure is fast. I was able to shut down the server, update the root password, and then restart the server before I even noticed downtime in my WordPress dashboard. In order to make the most of it, close all of your other windows and follow these steps one after another.

Step 1: Turn off MySQL by killing the process.

Run `ps aux | grep mysqld` to find the location of ``. In most cases, it will be at `/var/run/mysqld/`.

Kill the process with the following:

kill `cat /var/run/mysqld/`

Verify that the process died by running `ps aux | grep mysqld` again. The only results should be for grep and cat.

Step 2: Restart MySQL in “skip grant tables” mode.

Run `mysqld_safe –skip-grant-tables &`. If you’re on a production server, consider adding `–skip-networking` because it will disable outside connections. I didn’t feel the need to do this, but I also only had to reset the root password on a development server (because whomever still uses ‘root’ as a login is just asking to be exploited).

You might get some errors about not being able to log errors — that’s fine.

Your shell might get stuck on a blank line — that’s fine, too. Just hit enter and you’ll be back in the shell.

****Verify that MySQL is running in skip grant tables mode by running `ps aux | grep mysqld` again. You should see the `skip-grant-tables option in the list of processes.

Step 3: Login to MySQL and change the root password.

Run `mysql -u root -p`. When prompted for a password, just hit enter.

At the `mysql>` prompt, type in `use mysql;`. Now you are using the mysql database (the one that stores user credentials).

Now issue the following command, changing out the password for whatever you want the password to be. *I recommend you copy and paste the command below into a text editor, change the password field, then copy and paste it back into the console. *

UPDATE user SET Password=PASSWORD('ChangeMe!') WHERE User='root'; FLUSH PRIVILEGES; exit;

Step 4: Repeat step 1 — kill the MySQL process.

Run kill `cat /var/run/mysqld/`.

**Step 5: **Restart the MySQL server in normal mode.

Run `service mysql start`.

**And that’s it! **Remember that skip-grant-tables is a highly insecure way of doing things only because it allows anyone to connect with and does not throttle their privileges. From the manual:

[skip-grant-tables] enables anyone to connect without a password and with all privileges. Because this is insecure, you might want to use --skip-grant-tables in conjunction with --skip-networking to prevent remote clients from connecting.