Reset MySQL root password if you forgot it #mysql

Just had a need to reset the mysql root login password for a server, did a bit of Googling and found out this is how you do it (I work on Ubuntu so you may have to tinker with the lines slightly depending on your distribution):

  1. Stop the current MySQL instance from running:
    /etc/init.d/mysql stop
  2. Run mysql with –skip-grant-tables
    /usr/bin/mysqld_safe –basedir=/usr –datadir=/var/lib/mysql –user=mysql –pid-file=/var/run/mysqld/mysqld.pid –socket=/var/run/mysqld/mysqld.sock –port=3306 –skip-grant-tables &
  3. Log into mysql, straight into the mysql database
    mysql -u root mysql
  4. Reset the root password.
    UPDATE user SET Password=PASSWORD(‘newrootpassword’) WHERE User=’root’;
  5. Flush privileges
    flush privileges;
  6. Shut down the new running mysql.
    /etc/init.d/mysql stop
  7. Start up mysql as usual.
    /etc/init.d/mysql start

Et voila, you now have a new root password without needing to know the old one!

Comments

comments

Bookmark the permalink.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.