Can’t login to mysql as a root?

One day when I tried to login to mysql it is not permitting me to login as a root. Then I found the following solution to get rid of from this issue.

First you stop the server:

$ sudo /etc/init.d/mysql stop

Edit the cnf file:

$ sudo vim /etc/my.cnf

> Add the following line to section [mysqld]:

Start Mysql:

$ sudo /etc/init.d/mysql start

Reset Root Password:

$ mysql -e "update user set password = old_password('newpassword') where user = 'root'" mysql

Kill MySQL:

$ sudo kill -9 PROCESS_ID

Create an init-file:

$ vim ~/mysql-init

> Add the following lines:

UPDATE mysql.user SET Grant_priv='Y', Super_priv='Y' WHERE User='root';
GRANT ALL ON *.* TO 'root'@'localhost';

Run MySQL with the init file:

$ mysqld_safe --init-file=~/mysql-init &

Remove the mysql-init file:

$ rm ~/mysql-init

Restart MySQL:

$ sudo /etc/init.d/mysql restart

Login as root!

Author: Abhilash

Hey! My name is Abhilash - A Ruby developer for years, specialised on web programming, working as a freelancer. Mainly working on Ruby On Rails platform since 2010. This blog is about Ruby, Ruby On Rails and other subjects that I work. You can contact me here: Abhilash AK

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s