I was having issues logging into MySQL as root the other day. It took a while to figure things out. Ultimately, I had to follow the steps on two separate posts: http://rimuhosting.com/howto/mysqlinstall.jsp and http://bugs.mysql.com/bug.php?id=22118.
Here are the steps I took:
- Stop MySQL:
- Edit config:
sudo vim /etc/my.cnf
- Add line to section [mysqld]:
- Start MySQL:
- Reset Root Password:
mysql -e "update user set password = old_password('newpassword') where user = 'root'" mysql
- Kill MySQL:
kill `cat /var/run/mysqld/mysqld.pid`
- Create an init-file:
cat ~/mysql-init<<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:
- Restart MySQL:
- Login as root!
Part of this was also taken from the MySQL documentation for resetting a password, however, the contents of the file were changed to match one of the previously mentioned posts. If these steps don’t work, please visit the official documentation and give it a try again.
When you’re done following the above steps, please remove ‘skip-grant-tables’ from my.cnf. Thanks, Ian, for bringing this missed step to my attention!