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]:
skip-grant-tables

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:

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

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!
References:
1) http://www.ipreferjim.com/2011/06/cant-login-to-mysql-as-root/

Unknown's avatar

Author: Abhilash

Hi, Iโ€™m Abhilash! A seasoned web developer with 15 years of experience specializing in Ruby and Ruby on Rails. Since 2010, Iโ€™ve built scalable, robust web applications and worked with frameworks like Angular, Sinatra, Laravel, Node.js, Vue and React. Passionate about clean, maintainable code and continuous learning, I share insights, tutorials, and experiences here. Letโ€™s explore the ever-evolving world of web development together!

Leave a comment