Restoring deleted ‘root’ user and password for MySQL

by

I have a quick and dirty way

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!

More Related Contents:

  1. Location of my.cnf file on macOS
  2. brew install mysql on macOS
  3. Setting the MySQL root user password on OS X
  4. MySQL user DB does not have password columns – Installing MySQL on OSX
  5. How do you uninstall MySQL from Mac OS X?
  6. Ruby on Rails 3 Can’t connect to local MySQL server through socket ‘/tmp/mysql.sock’ on OSX
  7. Error: Tablespace for table xxx exists. Please DISCARD the tablespace before IMPORT
  8. MySQL Install: ERROR: Failed to build gem native extension
  9. libmysqlclient15-dev on macs?
  10. Error #2002 Can’t connect to local MySQL server through socket ‘/Applications/MAMP/tmp/mysql/mysql.sock’ (2)
  11. rails + MySQL on OSX: Library not loaded: libmysqlclient.18.dylib
  12. Connect to Docker MySQL container from localhost?
  13. How do you stop MySQL on a Mac OS install?
  14. Reset MySQL root password using ALTER USER statement after install on Mac
  15. Does MySQL included with MAMP not include a config file?
  16. CakePHP Database connection “Mysql” is missing, or could not be created
  17. ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/tmp/mysql.sock’ [duplicate]
  18. PID error on mysql.server start?
  19. How to start MySQL server from command line on Mac OS Lion?
  20. How to auto-load MySQL on startup on OS X Yosemite / El Capitan
  21. OSX ld: library not found for -lssl
  22. MySQL data directory location
  23. How to get a ranking result for special record in mysql [closed]
  24. What is MySQL row order for “SELECT * FROM table_name;”?
  25. MySQL Like multiple values
  26. Error: select command denied to user ”@” for table ”
  27. Calculate the time difference between of two rows
  28. MySQL/Writing file error (Errcode 28)
  29. MySQL : Multiple row as comma separated single row
  30. Return a default value if single row is not found

Leave a Comment