Fix :: Unable to login at phpMyadmin after revoking MySQL root user

Problem Statement


I am not able to login into phpMyadmin after revoking root user.


Solution


This issue was fixed by re-creating the root user through MySQL command prompt with following steps:


  1. Start the MySQL with skip-grant-tables option

    mysqld_safe --skip-grant-tables;
  2. Login to MySQL command line and run the following command.

    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`, `ssl_type`, `ssl_cipher`, `x509_issuer`, `x509_subject`, `max_questions`, `max_updates`, `max_connections`, `max_user_connections`) 
    values('localhost','root','','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','','','','','0',' 0','0','0'); 
  3. Following command will reset the root password with password you write in new-password field.

    Update user set Password=PASSWORD('new-password') where user='root';
    flush privileges;

  • 0 Users Found This Useful

Was this answer helpful?

Related Articles

How to fix InnoDB database corruption?

Check the log files. If you find InnoDB database corruption, follow the below mentioned steps:...

How to Fix MySQL Error "The server quit without updating PID file"?

MySQL server plays a vital role for your Linux Server. It runs all of your website's databases,...

How to Optimize MySQL Database Using phpMyAdmin?

Database always grows in size due to high traffic or a large amount of content. This will lead to...

How to Repair a MySQL Database Through Linux and Windows Command Line?

This article describes how to repair MySQL database tables through Linux and Windows command...

How to check MYSQL Database size in phpMyAdmin and from command?

Sometimes it is required to check the MySQL Database size. You can check it from the phpMyAdmin...