Featured image of post Guide to Recovering the MySQL 5.X Root User After Accidental Deletion

Guide to Recovering the MySQL 5.X Root User After Accidental Deletion

Note: This procedure is only applicable to version 5.x. It does not work for MySQL 8.x. If you've accidentally deleted the MySQL root user,...

Note: This procedure is only applicable to version 5.x. It does not work for MySQL 8.x.

If you have accidentally deleted the MySQL root user, you can follow these steps to attempt to recover it:

  1. Stop the MySQL service: You can use the following command to stop the MySQL service: sudo service mysql stop
  2. Start the MySQL service while skipping permission checks: Use the following command to start the MySQL service and bypass permission checks: sudo mysqld_safe --skip-grant-tables &
  3. Connect to the MySQL server: In a new terminal window, connect to the MySQL server using the following command: mysql -u root
  4. Access the MySQL system database: At the MySQL command prompt, execute the following command to use the MySQL system database: use mysql;
  5. Recreate the root user: Run the following SQL statement to recreate the root user and set a new password:
    1
    2
    3
    
    INSERT INTO user (Host, User, Password, Select_priv, Insert_priv, Update_priv, Delete_priv, Create_priv, Drop_priv, Reload_priv, Shutdown_priv, Process_priv, File_priv, Grant_priv, References_priv, Index_priv, Alter_priv, Show_db_priv, Super_priv, Create_tmp_table_priv, Lock_tables_priv, Execute_priv, Repl_slave_priv, Repl_client_priv, Create_view_priv, Show_view_priv, Create_routine_priv, Alter_routine_priv, Create_user_priv, Event_priv, Trigger_priv, Create_tablespace_priv) 
    VALUES ('localhost', 'root', PASSWORD('your_new_password'), 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y');
    FLUSH PRIVILEGES;
    
    Make sure to replace 'your_new_password' with the new password you wish to set.
  6. Exit MySQL and restart the MySQL service: At the MySQL command prompt, execute the following command to exit: exit; Then restart the MySQL service using this command: sudo service mysql restart
  7. Now, you should be able to log in as the MySQL root user using the new password.

Please note that these steps are only applicable if you have the appropriate permissions and access to the system. If you are unable to successfully recover the root user, consider seeking more advanced MySQL professional support or restoring from a backup.

Licensed under CC BY-NC-SA 4.0