Reset mysql root password

Reset mysql root password ubuntu server

Just for some weird reason you forget your root password, either you have changed it recently and just can not remember what you typed. Well do not give up just yet, there is a way out. To reset the password you would need physical access to the server, either via ssh or directory behind the server. A sudo account is required as well. Well let’s get our hands dirty now.

There are two major ways to go around this:

  1. Stoping and configuring Mysql
    • Mysql needs to be stoped before your proceed. Run the following command from your terminalsudo /etc/init.d/mysql stop
      sudo service mysql stop
    • Next we need to start MySQL in Safe Mode – That is to say, we will start MySQL but skip the User Privileges Table. Again, note that you will need to have sudo access for these commands so you don’t need to worry about any user being able to reset the MySQL Root Password:sudo mysqld_safe --skip-grant-tables &
    • The next thing to do is to login from the terminal as rootmysql -uroot
      mysql -u root

      • If login is successful then change the database to MySQL with the following commanduse mysql;
    • Reset the password by updating the user table with the sql belowupdate user set password=PASSWORD("mynewpassword") where user='root';Note: Replace “mynewpassord” with the new password you want to reset to
    • Now you have to flush mysql privileges so the changes are applied
      flush privileges;
    • You now need to exit mysql and stop and start the mysql service with the following commands respectively
      quit;sudo /etc/init.d/mysql stop
      sudo service mysql stopsudo /etc/init.d/mysql start
      sudo service mysql start
    • Test the new password by logging inmysql -u root -pNote: This method is not regarded as the securest way of resetting the password. However it works
  2. Reconfigure With dpkg-reconfigure
    This method is much more secured and faster.

    • From the terminal you find out the version of mysql that is installedapt-cache policy mysql-serverCheck for the line which shows the installed version among other information. e.g. for my install it’s:
      (From this i know that i have mysql-server-5.5 installed in my system.)
    • Now we use the dpkg command to reconfigure MySQLsudo dpkg-reconfigure mysql-server-*.*Note: Where mysql-server-*.* should be replaced by the version that you have. (for me it’d be mysql-server-5.5). This will stop the database daemon. A prompt will then appear where you’d have to enter your new password and confirm the reconfiguration.Type the new password in the window the shows up and repeat the password to confirm it
    • You can then log in with:mysql -u Root -pVoila.

Allan Barku

People are often unreasonable and self-centered. Forgive them anyway. If you are kind, people may accuse you of ulterior motives. Be kind anyway. If you are honest, people may cheat you. Be honest anyway. If you find happiness, people may be jealous. Be happy anyway.

1 comment

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

Follow us

Don't be shy, get in touch. We love meeting interesting people and making new friends.

Most discussed

%d bloggers like this: