Can’t login to MySQL as root :(

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:

  1. Stop MySQL:
    /etc/init.d/mysql stop
  2. Edit config:
    sudo vim /etc/my.cnf
  3. Add line to section [mysqld]:
    skip-grant-tables
  4. Start MySQL:
    /etc/init.d/mysql start
  5. Reset Root Password:
    mysql -e "update user set password = old_password('newpassword') where user = 'root'" mysql
  6. Kill MySQL:
    kill `cat /var/run/mysqld/mysqld.pid`
  7. 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
    
  8. Run MySQL with the init file:
    mysqld_safe --init-file=~/mysql-init &
  9. Remove the mysql-init file:
    rm ~/mysql-init
  10. Restart MySQL:
    /etc/init.d/mysql restart
  11. 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.

**IMPORTANT**
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!