Making MySQL root user work under Ubuntu

Ubuntu does not come with a workable MySQL installation out of the box. I always found that:

– There is no default root password and there is no direct way to set one.

– The root MySQL user does not work without a password (by default), like some other distributions. This way, you can login with root account and set a password.

Steps to set a MySQL password for root:

– Stop MySQL

sudo service mysql stop

– Start MySQL in no-privileges mode

sudo /usr/sbin/mysqld --skip-grant-tables --skip-networking &

– Start MySQL client with root user

mysql -u root

– Execute the following command to reload all user privileges in MySQL

FLUSH PRIVILEGES;

– Now set the password for root user with the following command:

SET PASSWORD FOR root@'localhost' = PASSWORD('newpassword');

– Reload the privileges from the MySQL grant tables:

FLUSH PRIVILEGES;

All set now. Restart the MySQL service and you can use the root user account to manage the database and create other users with specific permissions.

sudo service mysql stop
sudo service mysql start

**The above commands were tested with Ubuntu 12.04 LTS.