Fix :: Unable to login in 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

Fix - PHPMyAdmin Error You should upgrade to MySQL 5.5.0 or later

Problem statement Recently we upgraded phpMyAdmin version from 4.0.10.12 to 4.5.3.1 in our...

How do I connect MySQL database via MySQL Workbench?

MySQL Workbench is the excellent MySQL client tool used to connect to remote MySQL database from...

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

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

How to increase max_user_connections in MySQL from Linux VPS?

More often you receive Too many connections error when you attempt to connect MySQL server. This...

How to take a Backup and Restore of MySQL Databases via Command Line?

mysqldump is an easy and quick tool to create backup of MySQL databases. It creates a dump file...