null 💻 notes

Reset MySQL Root Password on Ubuntu

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 `mysql.pid`. In most cases, it will be at `/var/run/mysqld/mysqld.pid`.

Kill the process with the following:

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

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/mysqld.pid`.

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