It's me, it's my blog...
Debian

How to Reset MySQL Root User Permissions and Password in Linux

Sometime ago, I tampered with my MySQL root permission; I was unable to grant permission to new users, which kept me wondering why I am the root user or is there any other person? :/

In this post, I will show you have to restore your MySQL root permission which might have been tampered, and also resetting your forgotten password. Lets start with resetting the password.

Recovery Steps

  • Stop all instance of mysql on the system
    $ sudo systemctl stop mysql
  • Start MySQL safe instance with this command
    $ sudo mysqld_safe --skip-grant-tables 

    and then execute

    $ sudo mysql

    You should have something like this:

    Welcome to the MariaDB monitor.  Commands end with ; or \g.
    Your MariaDB connection id is 2
    Server version: 10.1.21-MariaDB-5+b1 Debian 9.0
    
    Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
    
    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    
    MariaDB [(none)]>

 

Resetting MySQL Root Password

Now now reset the root password by executing this query

USE mysql;
UPDATE user SET password=PASSWORD( 'new_password' ) WHERE User='root';

Then flush privileges by executing

FLUSH PRIVILEGES;

Restore Permissions

If you also lost your root permission, this step is for restoring it back

  1. Still in same the mysql console, use same starting step of resetting password and make sure you execute this query
    FLUSH PRIVILEGES;
  2. Then execute this to restore permission for root
    GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;

     

  3. Exit the console by typing
    EXIT

Now kill all instance of MySQL safe and start the real MySQL

$ sudo killall mysqld
$ sudo systemctl start mysql

Login into your MySQL and all fixed 🙂

Leave a Reply

Your email address will not be published. Required fields are marked *