Restoring deleted 'root' user and password for MySQL

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';

5. exit from mysql
6. remove 'skip-grant-tables' from my.cnf under the [mysqld] section
7. 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!

Services Cloudtechtiq offers:

• Linux VPS Hosting
• Windows Dedicated Hosting
• SSL Certificates
• Managed AWS Cloud
• Microsoft Office 365
• Microsoft Azure