A different approach to change MySQL root password in Ubuntu Server
The other day I was having a problem with the MySQL server on my Ubuntu machine. After connecting to mysql server in php, I could not select a database. The error was: Access denied for user @localhost to database foo. I did specify the user being root, but it keept saying I dont have permissions with the user (anonymous). Reinstalling mysql didnt do the job. So, lets delete the user .
1. Stop the MySQL Server:
sudo /etc/init.d/mysql stop
or
sudo service mysql stop
2. Start the mysqld:
sudo mysqld --skip-grant-tables &
Press CTRL+C (^C) to be able to enter the new command.
3. Login to mysql server:
sudo mysql -u root mysql
4. Delete the user :
If you enter the following command:
SELECT USER(),CURRENT_USER();
you will see that the users are different, one being and the other root.
Delete the anonymous user:
DELETE FROM mysql.user WHERE user = ;
Update root password (optional, if you know it):
UPDATE user SET Password=PASSWORD(1234) WHERE User=root;
5. Stop mysql server instance:
ps aux | grep mysql
On the second column, you will find the PID (process id) of mysql server. Kill it:
kill 20233
6. Start the usual mysql server:
sudo service mysql start
Resources: ubuntu.flowconsult.at