MySQL is a popular open-source relational database management system widely used with web applications like WordPress, Magento, and other CMS platforms. One common tool for managing MySQL databases via a web interface is phpMyAdmin.
phpMyAdmin typically uses the MySQL root user to log in with full administrative privileges. However, if you revoke or delete the root user, you may find yourself locked out of phpMyAdmin, unable to manage your databases. This article will explain what revoking the MySQL root user means, why it affects phpMyAdmin access, and how to restore root access safely.
Once the root account is revoked or deleted, phpMyAdmin cannot authenticate, resulting in login failures.
This issue was fixed by re-creating the root user through the MySQL command prompt with the following steps:
Step 1: Start MySQL in Safe Mode (Skip Authentication)
Safe mode allows you to bypass login permissions and access the MySQL shell directly. For Linux systems (via terminal):
# mysqld_safe --skip-grant-tables;
Step 2: Log in to MySQL Without a Password
Once MySQL is in safe mode, log in without credentials:
# mysql -u root
Step 3: Recreate Root User (if deleted)
If the root user is missing from the mysql.user table, insert it manually:
# INSERT INTO mysql.user (
Host, User, authentication_string, 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,
plugin
) 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','Y','','','','','0','0','0','0','mysql_native_password'
); #
Step 4: Set a New Password for Root
# ALTER USER 'root'@'localhost' IDENTIFIED BY 'new-password';
Or for older versions:
# UPDATE mysql.user SET authentication_string = PASSWORD('new-password') WHERE User='root';
Step 5: Reload Privileges
Finally, reload the MySQL privileges table:
# FLUSH PRIVILEGES;
Step 6: Restart MySQL Normally
Exit MySQL and stop the current safe-mode server:
# sudo systemctl stop mysql
Then, restart MySQL normally:
# sudo systemctl start mysql
Step 7: Test Login via phpMyAdmin
Open phpMyAdmin in your browser.
Use the username: root
Use the new password you just set.
If all steps were done correctly, you should now be able to log in.
Conclusion:
Losing access to phpMyAdmin due to revoking the MySQL root user can seem like a major issue, but it's fixable with careful steps. By starting MySQL in safe mode, recreating the user, setting a new password, and restoring privileges, you can regain access quickly.
Always be cautious while modifying user privileges in MySQL, and maintain regular backups of your user accounts and permissions to avoid such lockouts.
