Restoring deleted 'root' user and password for MySQL

    Posted in Linux Servers on Nov 08, 2019

    linux-dedicated-seerver.jpg

    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!