Reset and then restore mysql password

Question

I have linux root password. There's MySQL server on that machine. One of the site's ini was rewritten and now it can't reach the mysql database. I don't have mysql root to change the password for database user. The server owner is now in vacation, so I can't ask him to give me the mysql root. I can reset mysql root password, but I don't want.

I need:

1. Save current mysql root password hash somewhere
2. Reset it
3. Do the things as mysql root (reset database's user pw)
4. Restore the old root password back

How to do the points 1 and 4 ?

Answer 1

The common password reset procedure is performed by creating a init file that resets the root password. Then restarting the server with an option forcing it to run that script with root privileges as the root user.

The example script shows you resetting the root password, but there is no reason why you couldn't simply have the script reset the password of the user you want to reset.

So if you have the ability to restart the mysql server and specify an init file you are able to skip steps 1, 2, and 4 and simply run a script as root. There really is no reason that you need to touch the root account at all.

Answer 2

WARNING : Requires mysql restart for Steps 1-3

This can be done without...

• the use of another my.cnf
• editing the current my.cnf
• SUPER privilege

Once you login to the Linux Box, in order to create a temporary root for yourself with the password t3mpp@assw0rd, perform the following steps

STEP 01) service mysql restart --skip-networking --skip-grant-tables

STEP 02) At Linux prompt, type mysql and hit enter

STEP 03) Run this SQL Command

SELECT COUNT(1) RootLocalhostExists FROM mysql.user
WHERE user='root' AND host='localhost';

• If RootLocalhostExists is 0, stop here. Leave a comment in my answer.
• If RootLocalhostExists is 1, go on to STEP 04.

STEP 04) Perform these SQL commands

CREATE TABLE mysql.user2 LIKE mysql.user;
INSERT INTO mysql.user2 SELECT * FROM mysql.user
WHERE user='root' AND host='localhost';
UPDATE mysql.user SET PASSWORD=PASSWORD('t3mpp@assw0rd')
WHERE user='root' AND host='localhost';

STEP 05) service mysql restart

That will do your steps 1-3.

PASSWORD RESTORATION

To put back the original mysql root, do this (MySQL Restart Not Needed):

STEP 01) At Linux prompt, type mysql -uroot -pt3mpp@assw0rd and hit enter

STEP 02) Execute the Following SQL

DELETE FROM mysql.user WHERE user='root' AND host='localhost';
INSERT INTO mysql.user SELECT * FROM mysql.user2;
DROP TABLE mysql.user2;
FLUSH PRIVILEGES;
EXIT

That's it.

Give it a Try !!!

Answer 3

If your mysql server is running on debian (or ubuntu or similar) you can probably use the debian-sys-maint user rather than the mysql root user. Look in /etc/mysql/debian.cnf

It has SUPER privs and can change a user's password.