Mysqli_real_connect() errors can be frustrating to deal with, especially the “access denied for user” variety. This error usually appears when trying to connect to MySQL as the root user from PHP. There are a few common causes and solutions for this error that are good to know when debugging and resolving the issue.
Causes and Solutions for the mysqli_real_connect(): Error
Incorrect Root Password
One of the most common reasons for the “access denied for user ‘root’@’localhost'” error is an incorrect root password being used when trying to connect. Some things to check:
- Make sure the root password being used in the PHP connect code matches the actual current root password set in MySQL.
- If connecting remotely, ensure the root account is set to allow remote login (not just local socket connections).
- Check that the root user is not restricted to certain hosts only in the MySQL user table.
- Reset the root password if needed to match the password used by the application code.
Root Account is Restricted or Disabled
Another possibility is that the root account has been restricted or disabled entirely in the MySQL server. Some potential causes:
- The root account may have been set to only allow local socket connections rather than remote TCP connections. Update the user table to allow ‘%’ as the host if needed.
- The root account may have had all privileges revoked as a security measure. Check and restore required privileges.
- The root account may have been disabled by renaming it or setting the password hash to an invalid value. It will need to be re-enabled.
- Ubuntu and some other Linux distros disable the root account by default. The sudo mysql command or another user account will be needed.
Client IP Address is Blocked
For remote connections, the client host IP address being used may be blocked from accessing the MySQL server. Some possible checks:
- Validate the server HOSTS table does not block the remote IP address. Remove any unwanted restrictions.
- Check for firewall rules on the database server that may be blocking the client IP from access. Create exceptions to allow the address through.
- Make sure the bind-address parameter in MySQL config allows remote connections and is not limited only to local connections.
Insufficient User Account Privileges
Rather than connecting directly as root, another user account may be utilized that does not have full privileges granted. Some options to check:
- Review the specific user account privileges in MySQL and ensure all required permissions are granted.
- Switch back to using the root account for the initial connection if possible.
- Create a new user account with full privileges needed by the application.
Client Hostname is Not Recognized
If connecting remotely, MySQL may not recognize the host name used by the client application. Some tips:
- Try using the client machine’s IP address rather than hostname in the connect code.
- Make sure proper DNS records exist for the client host and are resolvable from the database server.
- Set the MySQL user account to allow connections from ‘%’ as the host rather than a specific hostname.
Incorrect Connection Parameters
Double-check that the correct MySQL hostname, username, password, database name, and port number are being used in the PHP connection code. An invalid parameter could cause the access denied error.
- Verify the MySQL hostname matches the server IP address or name.
- Make sure the connection port is 3306 by default or the configured port for MySQL.
- Check that a valid existing database name is being specified if provided.
- Review all code for hard-coded credentials and update as needed.
Character Encoding Mismatch
A mismatch in character set encoding between MySQL and the client application can also lead to authorization errors.
- Make sure the ‘character_set_client’ system variable in MySQL matches expectations.
- Set the ‘default-character-set’ option in MySQL config if needed.
- Specify the charset when connecting from client code, such as ‘utf8’.
Faulty MySQL Server
In some rare cases, the issue may be due to some fault in the MySQL server itself. Some longshots to try:
- Restart the MySQL service and try connecting again from scratch.
- Check for any relevant errors logged in the MySQL error log files.
- Consider upgrading MySQL to the latest stable version in case of a bug fix.
- Switch to another known good MySQL server instance if possible for comparison.
Coding Errors
Carefully inspect the PHP code that establishes the MySQL database connection for any potential flaws:
- Double-check that mysqli_real_connect is being used rather than mysql_connect which does not support authentication.
- Review that connection parameters are passed correctly and in the right order.
- Ensure mysqli_init is called before mysqli_real_connect.
- Look for any typos or bugs that could cause credentials to be invalid.
Conclusion
The “mysqli_real_connect(): (hy000/1045): access denied for user ‘root’@’localhost'” error can certainly be annoying to troubleshoot at times. However, methodically tracking down the potential causes outlined here should ultimately reveal the source of the problem in short order.
Careful attention to user permissions, credentials, IP addresses, hostnames, character encoding and application code when connecting to MySQL will typically uncover the reason for the access violation. With the proper analysis and diagnosis, resolution is usually within reach.
