Don't know the password, not allowed to change it. What now?

So you say I need mysql root access for this?

So you say that the root password is unknown?

So you say that I'm not allowed to change the password and leave it changed because it may break something?

(Anyone who has worked with mysql for a fair amount of time may find this solution obvious. In retrospect, it is obvious to me too, but it wasn't at the time -- and it was an amusing situation to be in)

This was quite a strange situation. I needed to set up MediaWiki, and for that I need the mysql root password (to create tables and users). Which I didn't know.


And, this was a live server. Not in use that much, but if I change the root password, stuff can break. I wasn't sure which app, if any, needed the root pwd, and while I am quite fond of breaking things, I didn't want to take the chance.

At first, a simple solution strikes you, start mysqld with --skip-grant-tables (I had root access to the unix box). This gives you root privileges but you can't CREATE USER or GRANT. Those were the exact two commands I wanted. Useless.

Well, almost useless. I *could* change the root password from here. But I can't do it permanently. So how would I change it back? I need to know the original password, right?

Nope. Fortunately I can still UPDATE TABLE and SELECT. And the mysql.user table is conveniently available to me. There were two possible fixes here. The first was to simply create an entry in the table that represented the new user. This would work, however the INSERT command would have to be huge (the table has one column per privilege, which adds up to a lot). The second solution was trickier. Basically, using a SELECT command, I could get the password hash of the root user. Which means that I can change it back using a simple UPDATE TABLE when I get the chance! 

So, the final solution was:

1. Stop mysqld
2. Start mysqld --skip-grant-tables, then run mysql
3. SELECT password FROM mysql.user where user='root'; Copy down the password hash. Don't lose it.
4. UPDATE TABLE mysql.user SET password=PASSWORD('something') where user='root';
5. Stop mysqld
6. Start mysqld
7. Run mysql -u root -p, with password "something"
8. (creating a new root user) CREATE USER 'manish'@'localhost' IDENTIFIED BY 'my_password'; GRANT ALL PRIVILEGES ON *.* TO 'manish'@'localhost' WITH GRANT OPTION;
9. UPDATE TABLE mysql.user SET password='copied_hash_goes_here' where user='root';
10. Mock the root user for having been replaced.