Home Download Ubuntu

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 don't have permissions with the user '' (anonymous). Reinstalling mysql didn't do the job. So, let's 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

0 comments:

Post a Comment