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
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
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.
And that should get your account running again.