MySQL error 1045 (Access denied) is usually caused by incorrect username, password, or improper permission settings. 1. First, confirm whether the entered user name and password are correct, pay attention to case sensitivity, and check whether there are any spelling errors in the configuration file; 2. Make sure that the user permissions allow remote or local connections, you can view them through SELECT User, Host FROM mysql.user, and create a new user that allows remote connections and authorize them if necessary; 3. Check the bind-address settings in the MySQL configuration file to ensure that its binding address is consistent with the access requirements, such as 0.0.0.0 allows external connections; 4. Troubleshoot other factors, including the operating system user authentication method, whether MySQL service is running, whether the firewall or security group releases port 3306, and check it step by step in order to solve the problem.

MySQL error 1045 (Access denied) is one of the most common connection problems and usually occurs when you try to connect to the database. This problem may be caused by a variety of reasons, but in most cases it is related to username, password or permission settings.

Check whether the username and password are correct

This is the most common point of error. If the username or password you entered is incorrect, MySQL will return error 1045.

• Confirm that the username and password you are using is consistent with the account created in MySQL.
• Pay attention to case sensitivity: Passwords are case sensitive under some systems.
• If you are reading information from a configuration file, check for any typos or extra spaces.
• You can temporarily print out the account password in my.cnf or connection script for confirmation (available in the development environment, use it with caution in production).

For example:

 mysql -u root -p

If you prompt for access denied after entering your password, you can first confirm whether you entered the wrong password or whether you have switched users.

Whether user permissions allow remote connections (or local connections)

Sometimes, although you have entered the correct account password, you still cannot connect, which may be a problem with permission restrictions.

• By default, users of MySQL may only allow logging in from localhost.
• If you are connecting from a remote machine, you need to make sure that the user has permissions similar to 'user'@'%' or 'user'@'指定IP' .
• Use the following command to view user permissions:

 SELECT User, Host FROM mysql.user;

If you see 'root'@'localhost' , then this account cannot be connected from other machines.

Solution:

• Create a user that allows remote connections:

 CREATE USER 'newuser'@'%' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON *.* TO 'newuser'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;

Note: Opening 'user'@'%' will bring security risks, and it is recommended to limit the IP range according to actual needs.

Check if MySQL is bound to the correct address and port

Sometimes, even if the account is fine, the connection may fail due to network configuration.

• Check the MySQL configuration file (usually /etc/my.cnf or /etc/mysql/my.cnf ) and find the bind-address settings.
• If the bound 127.0.0.1 , it can only be accessed locally.
• Modify to 0.0.0.0 to accept external connections (provided that the firewall is also released).
• At the same time, confirm whether the port is open, the default is 3306.

Inspection method:

 netstat -tuln | grep 3306

If you do not see the listening status, it means that the service has not been launched or the configuration is incorrect.

Other common situations

Sometimes the problem may be more hidden, such as:

• Operating system user permission interference : Some distributions (such as Ubuntu) use the auth_socket plug-in to log in, which means that you do not need a password when connecting with -u root , but rely on system user authentication.

  The solution is to use a normal user instead, or modify the login method of the root user:

   ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'your_password';
  FLUSH PRIVILEGES;

• MySQL service is not running : You can use systemctl status mysql or service mysql status to check the service status.

• Firewall/Security Group Limitation : Pay special attention to whether the security group rules have been released on the 3306 port on the cloud server.

---

Basically, these common investigation directions. Although it is not complicated, it is easy to ignore a certain detail, such as the bind address is misaligned or the permissions are not refreshed. When you encounter problems, you can do it step by step, first confirm the account password, then check the permissions and network settings, and you can basically do it.

The above is the detailed content of Troubleshooting MySQL error 1045 (Access denied). For more information, please follow other related articles on the PHP Chinese website!