I had setup a new Ubuntu server with MySQL and during the install I was sure I knew the password I set for the mysql root user.

So I then setup webmin and went to access mysqladmin and it failed. I then tried at the command line and it failed.

So I am stuck with a running but inaccessible mysql server.

This thread at Trap17 was the one that helped but it was missing a couple of key points.

I’ll repeat it here in case the link goes away in the future. If you are using Ubuntu without a root password you will need to prefix some of the following with the ‘sudo’ command so that they will run ok.

First stop mysql

/etc/init.d/mysql stop

Then run this command that basically says “run mysql without password control”

mysqld_safe –skip-grant-tables &

With my server this resulted in not returning to the command prompt. Press “Enter” to get the command prompt again.
Then open up mysql for a mysql prompt

mysql -u root -p

Which should result in a mysql> command prompt

From the original post this next command appears redundant on Ubuntu so skip this one. mysql client – mysql -u root

The next command is done at the mysql prompt and again I differ from the original post

use mysql;

Note the semi-colon to complete the command. If you do copy and paste the two lines successfully then skipping the semi-colon on the first line is ok, but I like most people will initially try to do it a line at a time so I think it is clearer to execute as a sequence.

The next command sets up your new password and you need to edit this line with your new password and your relevant user account name.

update user set password=PASSWORD(”NEW-ROOT-PASSWORD”) where User=’root’;

Ok, so now you possibly have an error. Depending on the way you held your lips you may or may not have the correct syntax copied to your command line. I am not sure if this is consistent with all browsers and all telnet clients but I use PuTTy and when I copy/paste the above from the original thread I get this

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

Note that the quote marks in the command were converted to a ‘.’ (period) character and when I hit the enter key I got an error

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ” at line 1

So if you check your syntax and make sure you have the quotes correctly placed then it will work fine.

Finish up by closing the mysql command line with ‘exit’ and stop / start mysql to get it to use passwords again.

/etc/init.d/mysql stop

/etc/init.d/mysql start

And that should get your account running again.