How to Resolve MySQL Root Access Issues After Running "mysql_secure_installation"

[Helposoft Staff]

Here is a detailed guide on how to resolve the MySQL root access issues that you encountered after running mysql_secure_installation. This guide assumes you are working on a Linux-based system and MySQL server.

If you are facing errors like these, we have a step by step guide to fix it:

[root@server ~]# mysql -u root -p
Enter password: XXXX
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

OR

[root@31-172-80-125 ~]# mysql -u root
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)

OR

mysqli_sql_exception: Access denied for user 'root'@'localhost' (using password: YES)

OR

Waiting for “mysql” to start ……………failed.

Cpanel::Exception::Services::StartError Service Status
undefined status from Cpanel::ServiceManager::Services::Mysql

Service Error
(XID 2tsqbb) The “mysql” service failed to start.

Startup Log
Jul 25 16:02:51 server.5ium.com systemd[1]: Starting MariaDB 10.6.18 database server...
Jul 25 16:02:54 server.5ium.com systemd[1]: mariadb.service: Main process exited, code=exited, status=1/FAILURE
Jul 25 16:02:54 server.5ium.com systemd[1]: mariadb.service: Failed with result 'exit-code'.
Jul 25 16:02:54 server.5ium.com systemd[1]: Failed to start MariaDB 10.6.18 database server.

mysql has failed. Contact your system administrator if the service does not automagically recover.

Steps to Resolve the Issue:​

1. Uninstall MySQL​

   
   

   yum remove mysql-server mysql
   rm -rf /var/lib/mysql # Warning: This will delete att the database data, skip it if you want to keep the data.
   rm -rf /etc/my.cnf

   ​

2. Reinstall MySQL​

   
   

   yum install mysql-server
   systemctl start mysqld
   systemctl enable mysqld

   
   
3. Verify MySQL Service Status:Ensure that the MySQL service is running. Use the following command:
   
   

   systemctl status mysqld

   
   If it is not running, start it:
   
   

   systemctl start mysqld

   
   
4. Stop MySQL Service:Stop the MySQL service before making changes:
   
   

   systemctl stop mysqld

   
   
5. Start MySQL in Safe Mode with --skip-grant-tables:This mode allows you to bypass the grant tables and make changes to user permissions:
   
   

   mysqld_safe --skip-grant-tables &

   
   
6. Connect to MySQL Without a Password:You should be able to connect without a password:
   
   

   mysql -u root

   
   
7. Reset Root Password:Once connected, reset the root password and flush privileges:
   
   

   FLUSH PRIVILEGES;
   ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewPassword';
   FLUSH PRIVILEGES;
   EXIT;

   
   
   Replace NewPassword with a strong, secure password.
8. Reinstall MySQL/MariaDB (if necessary):
   If the problem persists, consider reinstalling MySQL/MariaDB:
   
   

   sudo yum reinstall mysql-server
   # or for Debian/Ubuntu systems
   sudo apt-get install --reinstall mysql-server

   
   
9. Stop the Safe Mode MySQL Server:If you are unable to shut down using mysqladmin, you may need to kill the MySQL process:
   
   

   pkill mysqld

   
   Then restart MySQL normally:
   
   

   systemctl start mysqld

   
   
10. Verify Access:Try logging in with the new password:
    
    

    mysql -u root -p

    
    
11. Check MySQL Logs for Errors:If issues persist, check the MySQL logs for any errors:
    
    

    cat /var/log/mysqld.log

    
    
12. Re-run mysql_secure_installation (Optional): After resolving access issues, you can re-run mysql_secure_installation if needed to apply security settings:
    
    

    mysql_secure_installation

    
    
13. Additional Troubleshooting:
    • Recheck MySQL Configuration: Ensure there are no conflicting settings in /etc/my.cnf or /etc/mysql/my.cnf.
    • Repair Tables:If you suspect table corruption, use:
      
      

      mysqlcheck --all-databases --repair

      
      
    • Reinstall MySQL: If all else fails, consider backing up data and reinstalling MySQL.

Summary:​

• Start MySQL with --skip-grant-tables.
• Reset root password.
• Restart MySQL normally.
• Verify access and check logs if needed.

By following these steps, you should be able to regain access to the MySQL root user and ensure your database is correctly configured.