MySQL - resetting the root user's password

How do you reset the root user's password in MySQL? Learn a simple step-by-step guide and quickly regain control of your database!

If we forgot the password for the root user or another database user, there's a very simple way to reset it. We run all commands from the operating system's root account.

  • First we shut down the MySQL server: /etc/init.d/mysql stop
  • We start the database server in the background with the command: mysqld_safe --skip-grant-tables --skip-networking &
  • We log into the mysql database as root without providing a password: mysql -uroot mysql
  • Then in the mysql console:
  • mysql> update user set Password=PASSWORD('our_new_password') WHERE User='root';
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4  Changed: 4  Warnings: 0

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> quit;
Bye
  • We resume the background process with the command fg, and once the message appears: mysqld_safe --skip-grant-tables --skip-networking we use the key combination CTRL + C; this should appear:
  • ^C130504 21:32:04 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
  • We start the MySQL server in normal mode: /etc/init.d/mysql start
  • Finally, we can check the reset password: mysql -uroot -p