Restoring deleted 'root' user and password for MySQL

    Posted in Linux Servers on Nov 08, 2019

    If you accidentally deleted the root user on my local setup MySQL running on OS linux. There are no other users created to get back into MySQL. I am sharing solution for the recover the root user. Get someone with SysAdmin rights and do the following:

    1. Add 'skip-grant-tables' to my.cnf under the [mysqld] section
    2. restart mysql
    3. type mysql with no password and hit enter
    4. Run This:
    DELETE FROM mysql.user 
    WHERE  user = 'root' 
          AND host = 'localhost'; 
    
    INSERT INTO mysql.user 
    SET user = 'root', 
        host = 'localhost', 
        password = Password('whatevernewpassword'), 
        Select_priv = 'y',
        Insert_priv = 'y',
        Update_priv = 'y',
        Delete_priv = 'y',
        Create_priv = 'y',
        Drop_priv = 'y',
        Reload_priv = 'y',
        Shutdown_priv = 'y',
        Process_priv = 'y',
        File_priv = 'y',
        Grant_priv = 'y',
        References_priv = 'y',
        Index_priv = 'y',
        Alter_priv = 'y',
        Show_db_priv = 'y',
        Super_priv = 'y',
        Create_tmp_table_priv = 'y',
        Lock_tables_priv = 'y',
        Execute_priv = 'y',
        Repl_slave_priv = 'y',
        Repl_client_priv = 'y',
        Create_view_priv = 'y',
        Show_view_priv = 'y',
        Create_routine_priv = 'y',
        Alter_routine_priv = 'y',
        Create_user_priv = 'y',
        Event_priv = 'y',
        Trigger_priv = 'y',
      Create_tablespace_priv = 'y';
    1. exit from mysql
    2. remove 'skip-grant-tables' from my.cnf under the [mysqld] section
    3. restart mysql

    That should be all!

    Summary:-

    In this article we have discussed how to reset the MariaDB / MySQL root password. As always, feel free to use the comment form below to drop us a note if you have any questions or feedback. We look forward to hearing from you!